python读写excel的一些技巧
链接：https://zhuanlan.zhihu.com/p/347197043
来源：知乎
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
python处理excel的库很多，例如xlrd/xlwt/openpyxl/xlsxwriter等。每个库都有一定的局限性,pandas处理excel是基于这些库的，所以集大成者。
个人还是比较喜欢用pandas, 开箱即用。
首先得导入包
import pandas as pd
一、生成excel文件
pandas生成excel文件非常简单，只需要一行代码就能搞定。
data = pd.DataFrame(
{"col1":[1, 2, 3],
"col2":[4, 5, 6],
"col3":[7, 8, 9]
}
)
这跟excel的行列展示极为相似。
然后就是使用pandas的to_excel方法生成excel文件并将该内容写入该excel文件：
data.to_excel("excel.xlsx", index=False)
当然如果需要显示更多，可以查看函数的用法。
二、读取excel文件
使用pandas 的读取excel也非常简单，直接调用read_excel方法
data = pd.read_excel("excel.xlsx")
结果与上面创建的结果一样
col1 col2 col3
0 1 4 7
1 2 5 8
2 3 6 9
这是最简单的读取excel，同样可以参考官网的参数说明来进行参数设置，指定sheet_name，表头等其他操作。
三、一次性插入多个sheet数据
将DataFrame数据写进excel文件中使用的还是文章开头的to_excel方法，但是需要添加引擎writer，如下所示：
data = pd.DataFrame(
{"col1":[1, 2, 3],
"col2":[4, 5, 6],
"col3":[7, 8, 9]
}
)
writer = pd.ExcelWriter("excel 样例.xlsx")
data.to_excel(writer, sheet_name="这是第一个sheet")
data.to_excel(writer, sheet_name="这是第二个sheet")
data.to_excel(writer, sheet_name="这是第三个sheet")
writer.save()
writer.close()
使用pd.ExcelWriter生成writer，然后就可将数据写入该excel文件了，但是写完之后必须要writer.save()和writer.close()，否则数据仍然只在数据流中，并没保存到excel文件中，或者使用with as 魔术方法，这样就会在数据写入完后自动保存并关闭句柄：
with pd.ExcelWriter("excel 样例.xlsx") as writer:
data.to_excel(writer, sheet_name="这是第一个sheet")
data.to_excel(writer, sheet_name="这是第二个sheet")
data.to_excel(writer, sheet_name="这是第三个sheet")
四、 Pandas不覆盖现有sheet在Excel中写入数据
在平常把pandas写入Excel的时候，用到的是 df.to_excel('文件名.xlsx', sheet_name='sheet1'语句，示例如下：
A = np.array([[1,2,3],[4,5,6]])
df = pd.DataFrame(A)
df.to_excel('test_excel.xlsx',sheet_name='A')
只需要三行语句就可以搞定
但是，如果需要把两个DataFrame数据写入Excel文件中的不同sheet中，使用这种方法就有问题了：
A = np.array([[1,2,3],[4,5,6]])
B = np.array([[10, 20, 30], [40, 50, 60]])
df1 = pd.DataFrame(A)
df2 = pd.DataFrame(B)
df1.to_excel('test_excel.xlsx',sheet_name='AAA')
df2.to_excel('test_excel.xlsx',sheet_name='BBB')
执行以上程序之后，打开 “test_excel.xlsx” ，可以看到表格中只有名字为“BBB”的sheet保存下来了，而名字为“AAA”的sheet被覆盖掉了。
其实被覆盖的原因很好理解，程序在执行第二条写入语句的时候，默认以前的数据是没有用的，先清空这个Excel文件里的数据。
解决方法：
利用Pandas包中的ExcelWriter()方法增加一个公共句柄，在写入新的数据之时保留原来写入的数据，等到把所有的数据都写进去之后关闭这个句柄。示例如下：
writer = pd.ExcelWriter('test_excel.xlsx')
A = np.array([[1,2,3],[4,5,6]])
B = np.array([[10, 20, 30], [40, 50, 60]])
df1 = pd.DataFrame(A)
df2 = pd.DataFrame(B)
df1.to_excel(writer,sheet_name='AAA')
df2.to_excel(writer,sheet_name='BBB')
writer.close()
这样，两个sheet就都写入这个Excel中了。
五、Pandas读取Excel的不同sheet中的数据
在读取有多个sheet的Excel时，如果不指定sheet名字，那么read_excel 函数默认读取第一个sheet中的数据。
d1 = pd.read_excel('test_excel.xlsx') #默认属性下读取
print("d1:\n",d1)
d2 = pd.read_excel('test_excel.xlsx',sheet_name = 'AAA') #指定sheet名读取
print("dd1:\n",d2)
d3 = pd.read_excel('test_excel.xlsx',sheet_name = 'BBB') #指定sheet名读取
print("d3:\n",d3)
如果我们想一条代码读取excel中的所有值时，可以将“sheet_name”属性指定为None，这样会得到一个字典变量，字典的key就是sheet名，value就是对应sheet里的数据。
例如以下代码：
dd1 = pd.read_excel('test_excel.xlsx')
print("dd1:\n",dd1)
dd2 = pd.read_excel('test_excel.xlsx',None)
print("dd2:\n",dd2)
六、 追加sheet内容
按照官网的示例使用writer = pd.ExcelWriter("excel 样例.xlsx", mode='a')就能插入sheet，而不是覆盖原文件，然而我进行该操作之后就报错了：
In [1]:writer = pd.ExcelWriter("excel 样例.xlsx", mode='a')
Traceback (most recent call last):
File "<ipython-input-75-8f1e772ce767>", line 1, in <module>
writer = pd.ExcelWriter("excel 样例.xlsx", mode='a')
File "D:\anaconda\lib\site-packages\pandas\io\excel\_xlsxwriter.py", line 177, in __init__
raise ValueError("Append mode is not supported with xlsxwriter!")
ValueError: Append mode is not supported with xlsxwriter!
原因是现在常用的写入excel模块是openpyxl和xlsxwriter，pd.ExcelWriter方法默认是xlsxwriter，但是xlsxwriter不支持append操作。因此我们只需要更改模块就行：
data = pd.DataFrame(
{"col1":[1, 2, 3],
"col2":[4, 5, 6],
"col3":[7, 8, 9]
}
)
with pd.ExcelWriter("excel 样例.xlsx", mode='a', engine='openpyxl') as writer:
data.to_excel(writer, sheet_name="这是追加的第1个sheet")
data.to_excel(writer, sheet_name="这是追加的第2个sheet")
掌握了这些技巧，在平时应用中，操作起来得心应手。
浙公网安备 33010602011771号