用Python语言实现对EXCEL文件的读写和追加

       Python语言对EXCEL文件的读写，根据EXCEL文件的版本不同分为两种方法。第一种方法是基于EXCEL03版的读写方法，第二种是基于EXCEL07版的读写方法。这两种方法，笔者感觉到最明显的区别是，EXCEL03中列数最多只支持256列数据，而EXCEL07中在列数上会达到上万条。所以，自认为还是有必要将这两种方法都粘贴出来。

       第一种是基于EXCEL03版的读写方法代码如下：

import xlrd
import xlwt


def write03Excel(path):
    book = xlwt.Workbook()
    sheet1 = book.add_sheet("2003测试表")
    value = [["1,1", "1,2", "1,3"],
             ["2,1", "2,2", "2,3"],
             ["3,1","3,2","3,3"]]
    for i in range(0, len(value)):
        for j in range(0, len(value[i])):
            sheet1.write(i, j, value[i][j])
    book.save(path)
    print("写入数据成功！")


def read03Excel(path):
    book = xlrd.open_workbook(path)
    sheet1 = book.sheet_names()
    worksheet = book.sheet_by_name(sheet1[0])
    for i in range(0, worksheet.nrows):
        row = worksheet.row(i)
        for j in range(0, worksheet.ncols):
            print(worksheet.cell_value(i, j), "\t", end="")
        print()

第二中是基于EXCEL07版的读写方法，代码如下：

import openpyxl

def write07Excel(path):
    book = openpyxl.Workbook()
    sheet1 = book.active
    sheet1.title = 'Sheet1'

    value = [["1,1", "1,2", "1,3"],
             ["2,1", "2,2", "2,3"],
             ["3,1","3,2","3,3"]]
    for i in range(0, len(value)):
        for j in range(0, len(value[i])):
            sheet1.cell(row=i+1, column=j+1, value=str(value[i][j]))

    book.save(path)
    print("写入数据成功！")


def read07Excel(path):
    book = openpyxl.load_workbook(path)
    sheet1 = book.get_sheet_by_name('Sheet1')

    for row in sheet1.rows:
        for cell in row:
            print(cell.value, "\t", end="")
        print()

       基于EXCEL03版的文件追加方法代码如下：

import xlwt
import xlrd
from xlutils.copy import copy


def excelwrite(filename,value):         
    workbook = xlrd.open_workbook(filename) 
    sheet = workbook.sheet_by_index(0)  
    rowNum = sheet.nrows    
    colNum = sheet.ncols    
    newbook = copy(workbook)    
    newsheet = newbook.get_sheet(0) # 在末尾增加新行
    for i in range(0,len(value)):
        newsheet.write(rowNum, i, value[i]) # 覆盖保存  
    newbook.save(filename)

       基于EXCEL07版的文件追加方法代码如下：

import openpyxl

def add07Excel(path,value):
    wb = openpyxl.load_workbook(path)
    ws = wb['Sheet1']
    #for x in data:
    ws.append(value)
    wb.save(path)
    print("写入成功")

本程序的源码下载地址为：https://github.com/XiaoYaoNet/EXCEL