CODEWITHSUNDEEP
pythonexcel
programmerwiz32
programmerwiz32

Reputation: 559

applying formulas when writing numbers to excel

import xlsxwriter
import csv

wb = xlsxwriter.Workbook('output.xlsx',
              { 'strings_to_numbers': True})
ws = wb.add_worksheet( 'output')
row = col = 2
ws.write_number(row,col,float(5.71))
ws.write_formula('D3','=C3*0.5')
wb.close()

Hello, I am trying to do some formulas on excel,I can't seem to implement the functions on the cells (in this case D3), However,when I change the number in excel (manually) the function is applied.

I don't have Errors when I run, but the formula aren't being Applied directly, I have to manually enter them (in excel) for the function to be applied

How can I solve this?

Upvotes: 1

Views: 67

Answers (3)

jmcnamara
jmcnamara

Reputation: 41574

I don't have Errors when I run, but the formula aren't being Applied directly,

For what it is worth I don't see this issue. When I run your program I get the following output with the expected result:

enter image description here

There can be issues displaying formula results with non-Excel applications, which is explained in the Working with Formulas - Formula Results section of the XlsxWriter documentation. However, these issues shouldn't happen with Excel.

Upvotes: 0

Zciurus
Zciurus

Reputation: 838

If you can't find a way to solve it in xlsxwriter, I suggest you try out openpyxl. It operates in a very similar way and solves your problem

import openpyxl

path = "\path\to\your\file.xlsx"
workbook = openpyxl.load_workbook(path)
worksheet =  workbook["name of your sheet"]

cell1 = worksheet("C3")
cell1.value = 5.71

cell2 = worksheet("D3")
cell2.value = "=C3*0.5"

workbook.save(path)

Upvotes: 1

Ralf
Ralf

Reputation: 16505

You can pass a optional value to the method .write_formula(), so that the value is shown when opening the workbook; not every program calculates the formulas when opening the workbook, some only calculate when one of the involved cells is modified.

Citing the docs I linked above:

If required, it is also possible to specify the calculated result of the formula using the optional value parameter. This is occasionally necessary when working with non-Excel applications that don’t calculate the result of the formula

You could try this:

# we need to pass a cell format to 'write_formula()' to pass a calculated value
cell_format1 = wb.add_format()

row = col = 2
v = float(5.71)
ws.write_number(row, col, v)
ws.write_formula('D3', '=C3*0.5', cell_format1, v)

Upvotes: 1

Related Questions