标签:Python与Excel,Xlsxwriter
在本文中,我们将使用Python创建高保真的Excel电子表格。“高保真”意味着Python生成的Excel电子表格看起来像是由人创建的真实Excel文件一样,包含值、公式、不同的格式以及图表。
这是本系列的第1部分,这里将使用Python创建一个包含公式的Excel电子表格。
你可能已经熟悉,将某些数据转储到Excel文件中的更简单方法是使用pandas库:pd.to_Excel()。确实是这样的,但只得到一个只有值的Excel文件,没有公式,没有格式,等等。
xlsxwriter库允许我们创建这样一个高保真的Excel文件。它是一个写入Excel文件的强大库。然而,顾名思义,它只写入Excel文件,而不读取现有文件。
xlsxwriter也是pandas采用的Excel writer引擎之一。可以肯定地说,如果pandas依赖于这个库,那么使用它更方便。
与以往一样,首先安装该库:
pip install xlsxwriter
熟悉xlsxwriter
让我们先建立一个Excel文件,里面有电子表格。
import xlsxwriter
wb =xlsxwriter.Workbook(r'D:\hf_excel.xlsx')
wb.add_worksheet()
wb.add_worksheet('input')
wb.add_worksheet()
wb.close()
Workbook()构造函数将在参数指定的文件夹位置创建一个Excel文件。方法add_sheet()在该Excel文件中创建一个新的工作表/选项卡。默认情况下,如果没有将任何参数传递到add_worksheet(),则这些工作表将命名为“Sheet1”、“Sheet2”等,就像你预料到的Excel的行为一样。
我们可以通过向方法中传递字符串值来指定工作表名称。注意:虽然没有名为“Sheet2”的选项卡,但当执行第3个add_worksheet()方法时,会创建一个名为“Sheet3”的选项卡。
.close()方法很重要!如果不包括此行,Excel文件将不会保存。
引用单元格和单元格区域
可以使用“A1”或(行、列)符号来引用Excel中的单元格。由于Python索引从0开始,因此(0,0)表示“A1”,而(1,1)实际上表示“B2”。
“A1”符号更容易让人阅读,而且这也是在使用Excel公式时所需要的,比如C10=A1*2。然而,(行、列)表示法更易于编程。为了方便起见,xlsxwriter提供了一个方法xl_rowcol_to_cell(),可以轻松地将(行、列)表示法转换为“A1”表示法。注意下面有关如何导入该方法的代码。
你可能已经猜到了还有其他几个函数用于类似的目的。
xl_cell_to_rowcol()的作用正好相反,它将“A1”符号转换为(0,0)。
xl_col_to_name()将整数列编号转换为列字母。同样,注意索引以0开始。
xl_range()将(行、列)表示法转换为区域表示法,如“A1:C10”。它有4个参数:(开始行、开始列、结束行、结束列),只有整数值是有效参数。
xl_range_abs()与上述方法类似,但它返回绝对引用,即当我们需要“$”符号来引用单元格时。
如何获取工作表
因为xlsxwriter无法读取现有的Excel文件,所以使用.get_worksheet()方法是没有意义的。也就是说,这样一个方法实际上是存在的,它是Workbook.get_worksheet_by_name(),传入一个选项卡名称作为参数,将得到工作表对象。
将原始数据(硬编码值)写入Excel
现在,已经熟悉了我们的“Excel”环境,让我们创建文件。我们将使用相同的文件名,因此前面的示例文件将被覆盖。注意,当Excel文件被覆盖时,不会收到消息/警告,因此如果要保留文件,请小心使用名称。
import xlsxwriter
wb =xlsxwriter.Workbook(r'D:\hf_excel.xlsx')
ws_1 = wb.add_worksheet('input')
ws_1.write(0,0,"helloexcel")
wb.close()
在上面的代码块中,看是如何将Workbook.add_worksheet()赋值给变量的。add_worksheet()方法返回一个Worksheet对象ws_1,它是Excel文件中的第一个实际选项卡。同样,因为我们只写入Excel文件,所以可以将选项卡赋值给变量/对象,并在以后随时引用它。因此,Workbook.get_worksheet_by_name()不那么有用。
Worksheet.write()首先获取一组两个整数值,这是单元格的(行、列)表示法,在上面的示例中,我们将消息“hello excel”写入单元格A1。
记住保存并关闭工作簿。在Excel中打开它,将在“input”工作表的A1单元格中看到“hello Excel”,如下图5所示。
创建公式并链接到单元格
使用xlsxwriter库编写Excel公式非常简单,我们只需在字符串中写入完全相同的Excel公式,然后.write()写入文件。
import xlsxwriter
wb =xlsxwriter.Workbook(r'D:\hf_excel.xlsx')
ws_1 = wb.add_worksheet('input')
ws_1.write(0,0,1) # 将值1写入单元格A1
ws_1.write(1,0,2) # 将值2写入单元格A2
ws_1.write(2,0,3) # 将值3写入单元格A3
ws_1.write(3,0,"=SUM(A1:A3)")# 在单元格A4中写入Exel公式=SUM(A1:A3)
wb.close()
结果如下图6所示。