Python Xlsxwriter 简明教程
Python XlsxWriter - Working with Pandas
Pandas 是一个流行的 Python 库，用于数据处理和分析。我们可以使用 XlsWriter 将 Pandas dataframes 写入 Excel 工作表。
Pandas is a popular Python library for data manipulation and analysis. We can use XlsWriter for writing Pandas dataframes into an Excel worksheet.
要了解本节中描述的功能，我们需要在已安装 XlsxWriter 的相同环境中安装 Pandas 库。
To learn the features described in this section, we need to install Pandas library in the same environment in which XlsxWriter has been installed.
pip3 install pandasUsing XlsxWriter with Pandas
让我们从一个简单的示例开始。首先，根据整数列表中的数据创建一个 Pandas 数据框。然后使用 XlsxWriter 作为引擎来创建 Pandas Excel 写入器。借助此引擎对象，我们可以将数据框对象写入 Excel 工作表。
Let us start with a simple example. First, create a Pandas dataframe from the data from a list of integers. Then use XlsxWriter as the engine to create a Pandas Excel writer. With the help of this engine object, we can write the dataframe object to Excel worksheet.
Adding Charts to Pandas Dataframe
就像我们获取 Workbook 类的对象，然后通过调用其 add_worksheet() 方法获取 Worksheet 对象一样，写入器对象也可以用来获取这些对象。获取后，可以使用 XlsxWriter 方法来添加图表、数据表等。
Just as we obtain an object of Workbook class, and then a Worksheet object by calling its add_worksheet() method, the writer object can also be used to fetch these objects. Once we get them, the XlsxWriter methods to add chart, data table etc. can be employed.
在此示例中，我们设置 Pandas 数据框并获取其维度（或形状）。
In this example, we set up a Pandas dataframe and obtain its dimension (or shape).
import pandas as pd
df = pd.DataFrame({'Data': [105, 60, 35, 90, 15, 30, 75]})
writer = pd.ExcelWriter('hello.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
(max_row, max_col) = df.shape从写入器创建工作簿和工作表对象。
The workbook and worksheet objects are created from the writer.
workbook = writer.book
worksheet = writer.sheets['Sheet1']其余的事情很简单。图表对象已添加到我们之前执行的操作中。
Rest of things are easy. The chart object is added as we have done earlier.
chart = workbook.add_chart({'type': 'column'})
chart.add_series({'values': ['Sheet1', 1, 1, max_row, 1]})
worksheet.insert_chart(1, 3, chart)
writer.save()Example
以下代码使用 Pandas 数据框编写一个 Excel 工作簿，并且 XlsxWriter 准备好了一个柱形图。
The following code uses Pandas dataframe to write an Excel workbook and a column chart is prepared by XlsxWriter.
import pandas as pd
df = pd.DataFrame({'Data': [105, 60, 35, 90, 15, 30, 75]})
writer = pd.ExcelWriter('hello.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
(max_row, max_col) = df.shape
workbook = writer.book
worksheet = writer.sheets['Sheet1']
chart = workbook.add_chart({'type': 'column'})
chart.add_series({'values': ['Sheet1', 1, 1, max_row, 1]})
worksheet.insert_chart(1, 3, chart)
writer.save()
Writing Dataframe to Excel Table
类似地，可以将数据框写入到 Excel 表对象中。此处的 data 数据框是从 Python 字典中派生的，其中键是数据框列标头。每个键有一个列表作为值，而该值反过来成为每列的值。
Similarly, the dataframe can be written to Excel table object. The dataframe here is derived from a Python dictionary, where the keys are dataframe column headers. Each key has list as a value which in turn becomes values of each column.
import pandas as pd
df = pd.DataFrame({
'Name': ['Namrata','Ravi','Kiran','Karishma'],
'Percent': [73.33, 70, 75, 65.5],
'RollNo': [1, 2,3,4]})
df = df[['RollNo', 'Name', 'Percent']]
(max_row, max_col) = df.shape使用 xlsxwriter 引擎将数据框写入到工作表 (sheet1) 中
Use xlsxwriter engine to write the dataframe to a worksheet (sheet1)
writer = pd.ExcelWriter('hello.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)以下行提供工作簿和工作表对象。
Following lines give Workbook and Worksheet objects.
workbook = writer.book
worksheet = writer.sheets['Sheet1']工作表中的数据借助 add_table() 方法转换为表。
Data in the worksheet is converted to Table with the help of add_table() method.
column_settings = [{'header': column} for column in df.columns]
worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})
writer.save()Example
下面的完整代码可以将 pandas 数据框写入到 Excel 表中。
Below is the complete code to write pandas dataframe to Excel table.
import pandas as pd
df = pd.DataFrame({
'Name': ['Namrata','Ravi','Kiran','Karishma'],
'Percent': [73.33, 70, 75, 65.5],
'RollNo': [1, 2,3,4]
})
df = df[['RollNo', 'Name', 'Percent']]
(max_row, max_col) = df.shape
writer = pd.ExcelWriter('hello.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']
column_settings = [{'header': column} for column in df.columns]
worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})
writer.save()