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