Python读写excel

本文描述内容主要包括：
（1）openpyxl模块的安装；
（2）以openpyxl模块读写excel文件；
（3）在写示例的过程中遇到的问题。

1.openpyxl安装

打开terminal，执行pip install openpyxl命令。

2.Python读写excel

2.1 读取excel

2.1.1 excel文件内容

（1）新建1.xlsx文件。Sheet1内容：

（2）Sheet2内容：

2.1.2 excel读取

import openpyxl#读取Excel文件
excel = openpyxl.load_workbook("1.xlsx")
sheet1 = excel['Sheet1']
#获取单元格数据
value = sheet1['A1'].value
print("A1:"+value)
value = sheet1['B2'].value
print("B2:"+value)sheet2 = excel['Sheet2']
sheet2A2 = sheet2['A2'].value
sheet2B2 = sheet2['B2'].value
sheet2C2 = sheet2['C2'].value
print(f"A2:{sheet2A2},B2:{sheet2B2},C2:{sheet2C2}")
#修改单元格数据
sheet2['C2'].value = 9
excel.save("1.xlsx")

执行程序后：

A1:a1
B2:我不好
A2:西红柿,B2:红色,C2:10

2.2 写入excel

2.1.1 创建工作簿

workbook = openpyxl.Workbook()

2.1.2 获取默认工作表

sheet = workbook.active

2.1.3单元格写入

sheet['A1'] = "原值"
sheet['B1'] = "平方"
sheet['C1'] = "立方"
sheet['D1'] = "四次方"

2.1.4每行写入

for i in range(1,10):sheet.append([i,i**2,i**3,i**4])
workbook.save("0.xlsx")

执行程序后：

3.问题记录

3.1 问题1：openpyxl.utils.exceptions.InvalidFileException: openpyxl does not support the old .xls file format

（1）问题描述：

Traceback (most recent call last):File "/home/tarena/PycharmProjects/爬虫/.idea/excelLearn.py", line 4, in <module>excel = openpyxl.load_workbook("0.xls")File "/home/tarena/.local/lib/python3.6/site-packages/openpyxl/reader/excel.py", line 347, in load_workbookdata_only, keep_links, rich_text)File "/home/tarena/.local/lib/python3.6/site-packages/openpyxl/reader/excel.py", line 123, in __init__self.archive = _validate_archive(fn)File "/home/tarena/.local/lib/python3.6/site-packages/openpyxl/reader/excel.py", line 93, in _validate_archiveraise InvalidFileException(msg)
openpyxl.utils.exceptions.InvalidFileException: openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format.

（2）问题解析：openpyxl模块无法读取*.xls格式的老式Excel文件，只认*.xlsx格式的Excel文件
（3）问题解决方法：创建*.xlsx格式的Excel文件，重新保存当前的XX.xls文件为新的XX.xlsx文件，但不可直接重命名修改。

3.2 问题2：raise IOError(“File contains no valid workbook part”)

（1）问题描述：

Traceback (most recent call last):File "/home/tarena/PycharmProjects/爬虫/.idea/excelLearn.py", line 4, in <module>excel = openpyxl.load_workbook("0.xlsx")File "/home/tarena/.local/lib/python3.6/site-packages/openpyxl/reader/excel.py", line 348, in load_workbookreader.read()File "/home/tarena/.local/lib/python3.6/site-packages/openpyxl/reader/excel.py", line 293, in readself.read_workbook()File "/home/tarena/.local/lib/python3.6/site-packages/openpyxl/reader/excel.py", line 151, in read_workbookwb_part = _find_workbook_part(self.package)File "/home/tarena/.local/lib/python3.6/site-packages/openpyxl/reader/excel.py", line 112, in _find_workbook_partraise IOError("File contains no valid workbook part")
OSError: File contains no valid workbook part

（2）问题解析：文件包含无效的工作簿，不可直接重命名修改当前的XX.xls文件为新的XX.xlsx文件。
（3）问题解决办法：
重新创建*.xlsx格式的Excel文件，并拷贝源数据到此文件中即可。

3.3 问题3：‘Worksheet sheet1 does not exist.’

（1）问题描述：

Traceback (most recent call last):File "/home/tarena/PycharmProjects/爬虫/.idea/excelLearn.py", line 5, in <module>sheet = excel['sheet1']File "/home/tarena/.local/lib/python3.6/site-packages/openpyxl/workbook/workbook.py", line 287, in __getitem__raise KeyError("Worksheet {0} does not exist.".format(key))
KeyError: 'Worksheet sheet1 does not exist.'

（2）问题解析：工作栏sheet1不存在，程序无法在xx.xlsx中找到sheet1。
（3）问题解决办法：sheet1需要于xx.xlsx文件中的sheet名称保持一致。在我的文件中包含三个sheet，分别为"Sheet1"、“Sheet2”、“Sheet3”，所以将"sheet1"改为“Sheet1”即可。