pandas 读取excel、一次性写入多个sheet、原有文件追加sheet

本文全部操作只需要引入pandas包即可

import pandas as pd

一、生成excel文件

首先构造pandas的DataFrame数据：

data = pd.DataFrame(
    {"col1":[1, 2, 3], 
     "col2":[4, 5, 6], 
     "col3":[7, 8, 9]
     }
    )

数据如下：

In [01]:data
Out[01]: 
   col1  col2  col3
0     1     4     7
1     2     5     8
2     3     6     9

这跟excel的行列展示极为相似。
然后就是使用pandas的to_excel方法生成excel文件并将该内容写入该excel文件：

data.to_excel("excel 样例.xlsx", index=False)

因为index的选项为False，所以没有将索引写入，结果如下：
同时还可以指定sheet名称、是否展示列名等其他操作，具体可见官网参数说明

二、读取excel文件

使用pandas 的read_excel方法

data = pd.read_excel("excel 样例.xlsx")

结果与上面创建的结果一样

In [01]:data
Out[01]: 
   col1  col2  col3
0     1     4     7
1     2     5     8
2     3     6     9

这是最简单的读取excel，同样可以参考官网的参数说明来进行参数设置，指定sheet_name，表头等其他操作。
excel数据里经常会有数据说明、合并项等不需要读取或影响读取数据的单元格，比如下面的样例数据：
那么就要进行表头的设置、初始行的设置、sheet的选择：

data1 = pd.read_excel("excel 样例 1.xlsx", sheet_name="数据", header=2, usecols="B:E")

使用sheet_name参数指定sheet名称，使用header指定第2行为表头（从第0行开始），使用usecols指定列对应的位置，结果与上面的数据结果一致。

In [01]:data
Out[01]: 
   col1  col2  col3
0     1     4     7
1     2     5     8
2     3     6     9

三、一次性插入多个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")

写入的结果如下：

注意此操作会将原文件内容覆盖掉，如想追加数据请看下章节

四、追加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")
    

结果如下：

最后，还有个原sheet追加内容我没有写入，不写的原因是因为这样的需求太少了，如果实在是有这个需求我也可以通过读取该sheet内容然后使用pd.concat合并数据最后再写入。