python处理Excel - xlrd xlwr openpyxl

python处理Excel - xlrd xlwr openpyxl

1 xlrd和xlwt

Todo: 使用xlrd和xlwt读写Excel文件的方法和示例代码，待续。。。

参考链接：

Creating Microsoft Excel Spreadsheets with Python and xlwt：https://www.blog.pythonlibrary.org/2014/03/24/creating-microsoft-excel-spreadsheets-with-python-and-xlwt/

 

前期使用xlrd和xlwt读写excel表格，现写入excel时出现问题：

ValueError: row index was 65536, not allowed by .xls format

xlwt只能处理.xls格式的Excel，即2003之前的版本，Excel2003只能支持65535行数据，实际应用超出该范围，因此抛出错误[1]。

2 openpyxl

openpyxl: A Python library to read/write Excel 2010 xlsx/xlsm files

Sample code:

from openpyxl import Workbook
wb = Workbook()

# grab the active worksheet
ws = wb.active

# Data can be assigned directly to cells
ws['A1'] = 42

# Rows can also be appended
ws.append([1, 2, 3])

# Python types will automatically be converted
import datetime
ws['A2'] = datetime.datetime.now()

# Save the file
wb.save("sample.xlsx")

openpyxl tutorial: https://openpyxl.readthedocs.io/en/stable/tutorial.html

核心代码：

# Create a workbook

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

# Playing with data
## Accessing one cell
ws['A4'] = 4
c = ws['A4']
d = ws.cell(row=4, column=2, value=10)
for x in range(1,101):
    for y in range(1,101):
        ws.cell(x, y, value)
## Accessing many cells
cell_range = ws['A1':'C2']

# Saving to a file
wb.save('balances.xlsx')

---

参考链接：

[1] ValueError: row index was 65536, not allowed by .xls format: https://stackoverflow.com/questions/45741670/valueerror-row-index-was-65536-not-allowed-by-xls-format