python:xlsxwriter将数据框+公式添加到excel文件

Question

我发现了将数据框和公式写入 excel 文件的不同方法。

import pandas as pd
import numpy as np
import xlsxwriter

# Method 1
writer = pd.ExcelWriter('example.xlsx', engine='xlsxwriter')
A = pd.DataFrame(np.array([[1,2,3],[4,5,6],[7,8,9]]))
A.to_excel(writer , sheet_name='Sheet1')
writer.save()

# Method 2
workbook = xlsxwriter.Workbook('example.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write_formula('B5' , '=_xlfn.STDEV.S(B3:B5)')
workbook.close()

一个用于添加数据框，另一个用于公式。问题:方法 2 删除了使用方法 1 写入文件的内容。如何合并它们？

Answer 1

如果您想将这两个操作合二为一，这是一种方法:

import pandas as pd
import numpy as np
import xlsxwriter

# Method 1
writer = pd.ExcelWriter('example.xlsx', engine='xlsxwriter')
A = pd.DataFrame(np.array([[1,2,3],[4,5,6],[7,8,9]]))
A.to_excel(writer , sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Write the formula.
worksheet.write_formula('B5' , '=_xlfn.STDEV.S(B2:B4)')

# Or Create a new worksheet and add the formula there.
worksheet = workbook.add_worksheet()
worksheet.write_formula('B5' , '=_xlfn.STDEV.S(Sheet1!B2:B4)')

writer.save()

输出 :

见 Working with Python Pandas and XlsxWriter XlsxWriter 文档的部分。
请注意，我更正了公式的范围以避免循环引用。