CODEWITHSUNDEEP
pythonexcelexcel-formulapython-3.5
Harshit Trivedi
Harshit Trivedi

Reputation: 782

XlsxWriter write_formula() not working always show Zero (0)

I have work with xlsxwriter and I try to print simple formula with a print of formula into the sheet 

import xlsxwriter
workbook = xlsxwriter.Workbook('filename1.xlsx')
format_val=workbook.add_format()
worksheet = workbook.add_worksheet()
worksheet.write(0,1,5)
worksheet.write(1,1,2)
worksheet.write_formula(3,0, '=SUM(B1:B2)')
workbook.close()
csvf = StringIO(import_file.read().decode())

Here the Image of how to show and when I press = than output is print 7

But output also will be Zero (0). I know that XlsxWriter doesn’t calculate the result of a formula and instead stores the value 0 as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened.

I have tried this. It is working for me but it's not a proper thing when I try with Upper Case is not working: ~

num_formate=format_val.set_num_format('0')
worksheet.write_formula(5,1, '=2+1',num_formate,4)

But when it's open How to show calculate value?

Upvotes: 4

Views: 2165

Answers (2)

toryano0820
toryano0820

Reputation: 41

Passing empty string to value arg works for me.

# # xlsxwriter/worksheet.py
#
# class Worksheet(xmlwriter.XMLwriter):
# ...
#     def write_formula(self, row, col, formula, cell_format=None, value=0)

worksheet.write_formula(3, 0, '=SUM(B1:B2)', None, '')

Upvotes: 2

Maryam Bahrami
Maryam Bahrami

Reputation: 1104

If you use pandas.ExcelWriter with 'xlsxwriter' engine, and .save() it, then it the formula is as you expected. For your case:

import pandas as pd
writer = pd.ExcelWriter('filename1.xlsx', engine='xlsxwriter')
workbook  = writer.book
format_val=workbook.add_format()
worksheet = workbook.add_worksheet()
worksheet.write(0,1,5)
worksheet.write(1,1,2)
worksheet.write_formula(3,0, '=SUM(B1:B2)')
writer.save()

However, this write formula in the forth row and first column (A4). If you need to be in the third row and second column (B3), you should use:

worksheet.write_formula(2,1, '=SUM(B1:B2)')

Upvotes: 1

Related Questions