Python Excel

xlrd

示例：

import os
import xlrd

rootPath = os.path.abspath(os.path.dirname(__file__))
filename = "BatchImport.xls"
file_path = os.path.join(rootPath,filename)
print(f"excel文件路径: {file_path}")

excel = xlrd.open_workbook(file_path)
table = excel.sheets()[0]
data = []
data.extend(table.row_values(1))
print(f"读取到的数据：{data}")

结果：

excel文件路径: C:\Users\Administrator\Desktop\BatchImport.xls
读取到的数据：[1.0, '测试客户19', '测试用水地址19', '柜台现金', '混合用水', '智能水表', '三川', 'DN15-5', 15.0, '用户', '城镇居民', '', '', '', '', '水表地址10', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'dma']

xlwt

示例：

import os
import xlwt

rootPath = os.path.abspath(os.path.dirname(__file__))
filename = "test_xlwt.xls"
file_path = os.path.join(rootPath,filename)
print(f"excel文件路径: {file_path}")

excel = xlwt.Workbook()
table=excel.add_sheet('test',cell_overwrite_ok=True)
table.write(0,0,"test write in.")
excel.save(file_path)

print(os.path.exists(file_path)) #测试文件是否存在

结果：

excel文件路径: C:\Users\Administrator\Desktop\test_xlwt.xls
True

openpyxl

示例1：

import os
from openpyxl import Workbook

rootPath = os.path.abspath(os.path.dirname(__file__))
filename = "test_openpyxl.xls"
file_path = os.path.join(rootPath, filename)
print(f"excel文件路径: {file_path}")
wtdata = ["序号", "名称", '地址', 'type']

wb = Workbook()
ws1 = wb.active
ws1.title = "test"
ws1.append(wtdata)
wb.save(filename=file_path)

print(os.path.exists(file_path))  # 测试文件是否存在

结果：

excel文件路径: C:\Users\Administrator\Desktop\test_openpyxl.xls
True

示例2：

from openpyxl import load_workbook

filename = "test_openpyxl.xlsx"
wb = load_workbook(filename = filename)
ws = wb.active
for row in ws.values:
   for value in row:
     print(value)

结果：

序号
名称
地址
type