CODEWITHSUNDEEP
pythonexcelpandasnumpyxlsxwriter
masterofpuppets
masterofpuppets

Reputation: 139

python: xlsxwriter adding dataframe + formula to excel file

I found to different methods to write dataframes and formulas to an excel file.

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()

One works for adding the dataframe the other for the formula. Problem: Method 2 deletes what was written to the file with Method 1. How can I combine them?

Upvotes: 2

Views: 2405

Answers (1)

jmcnamara
jmcnamara

Reputation: 41574

Here is one way to do it if you want to combine the two actions into one:

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()

Output:

enter image description here

See the Working with Python Pandas and XlsxWriter section of the XlsxWriter docs.

Note, I corrected the range of the formula to avoid a circular reference.

Upvotes: 1

Related Questions