Excel formula formats from Python to Excel

user_5166034 0
Viewed 178

I have a problem exporting Pandas data to excel: I want, for example to export a data like this

= 1+2+3

Now, to do this, I tried two ways:

d = p.DataFrame({'test1': ["=1+2+3"], 'test2': ["'=1+2+3"]})
d.to_excel('test3.xlsx')

Excel dispays the column this way:

test1 test2
6     '=1+2+3

Fo test2, I though excel would ignore the ', and consider the formula as a text, what I want. But it displays it. Do you know how could I do?

Thanks for help

pandas excel python
2 Answers

I suggest using openpyxl library (which comes with pandas module).

Here is a small example:

from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active

# If you want to open an exisiting file you can use instead: 
# wb = load_workbook(r'test3.xlsx')

# Add value to cell
ws['A1'] = "test1"
ws['B1'] = "test2"
ws['C1'] = "Total"

# Add formula to cell
ws['A2'] = "=2+3+4"
ws['B2'] = "=1+2+3"
ws['C2'] = "=SUM(A2:B2)"

# To add the Formula string to a cell just change the data_type of that cell
ws['D2'] = "=SUM(A2:B2)"
ws['D2'].data_type = 's'

# Save file
wb.save(r'./test3.xlsx')

# Close
wb.close()

Here's a guide on how to append a panda dataframe to an excel sheet using Openpyxl

user_149338700

Try using xlsxwriter

It provides an option to stop Excel from evaluating the formula string, so the formula string will appear as it is. Here is a simple example.

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx', {'strings_to_formulas': False,}) #Specify not to convert string to formulas

worksheet = workbook.add_worksheet()
worksheet.write_string('A1', "= {} + {} + {}".format(1, 2, 3))


workbook.close()

Here is the output:

Output: Formula string (text)

Check the usage of xlswriter with pandas here

user_133658500
Related