LibreOffice Calc not computing functions (showing function, not output)

agwn · June 9, 2022, 6:27pm

Found this thread looking searching for functions not computing. Thanks for the info on the cached values. Will dig into my generator software and see if there is anything I can do to disable this.

karolus · June 9, 2022, 7:43pm

import xlsxwriter

# Create a workbook and add a worksheet.

with xlsxwriter.Workbook('Expenses01.xlsx') as workbook:
    worksheet = workbook.add_worksheet('Tab33')

    # Some data we want to write to the worksheet.
    items = ('Rent','Gas','Food','Gym','Total')
    values = (1000, 100, 300, 50, '=sum(B1:B4)' )

    for c, data in enumerate((items, values)):
        worksheet.write_column(0, c, data=data)

mikekaganski · June 10, 2022, 7:40am

github.com/jmcnamara/XlsxWriter

Bug: writing formulas also writes a dummy "last calculated result" (v element) equal to 0

opened 07:38AM - 10 Jun 22 UTC

mikekaganski

bug

### Current behavior Using the "Create a simple XLSX file" tutorial [ https://x…lsxwriter.readthedocs.io/tutorial01.html ], the resulting XLSX file contains this markup: ```xml ... <row r="5" spans="1:2"> ... <c r="B5"> <f>SUM(B1:B4)</f> <v>0</v> </c> </row> ... ``` The `v` element there makes the XLSX open wrong in software that makes use of the "last calculated result" OOXML feature, e.g. LibreOffice, or MS Excel in the "locked" mode (e.g., when the file is downloaded from Internet to a Windows system, the file is marked "blocked", and opens in MS Excel without recalculations). Namely, the software making use of such last calculated result shows 0 in the formula cell, instead of the correct result. ### Expected behavior There should be no `v` element for formula cells generated by XlsxWriter. ECMA-376 Part 1 sect. 18.3.1.96 "v (Cell Value)" describes the semantics of the `v` for formula case. Further, `c` element, that has `CT_Cell` content model, is defined in §A.2 as: ```xsd <xsd:complexType name="CT_Cell"> <xsd:sequence> <xsd:element name="f" type="CT_CellFormula" minOccurs="0" maxOccurs="1"/> <xsd:element name="v" type="s:ST_Xstring" minOccurs="0" maxOccurs="1"/> <xsd:element name="is" type="CT_Rst" minOccurs="0" maxOccurs="1"/> <xsd:element name="extLst" minOccurs="0" type="CT_ExtensionList"/> </xsd:sequence> ... </xsd:complexType> ``` explicitly specifying that `v` is optional. ### Sample code to reproduce ```markdown import xlsxwriter # Create a workbook and add a worksheet. workbook = xlsxwriter.Workbook('Expenses01.xlsx') worksheet = workbook.add_worksheet() # Some data we want to write to the worksheet. expenses = ( ['Rent', 1000], ['Gas', 100], ['Food', 300], ['Gym', 50], ) # Start from the first cell. Rows and columns are zero indexed. row = 0 col = 0 # Iterate over the data and write it out row by row. for item, cost in (expenses): worksheet.write(row, col, item) worksheet.write(row, col + 1, cost) row += 1 # Write a total using a formula. worksheet.write(row, 0, 'Total') worksheet.write(row, 1, '=SUM(B1:B4)') workbook.close() ``` ### Environment _No response_ ### Any other information https://ask.libreoffice.org/t/libreoffice-calc-not-computing-functions-showing-function-not-output/17418/36 ### OpenOffice and LibreOffice users - [X] I have tested the output file with Excel.

erAck · June 10, 2022, 10:09am

That in a comment says “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”. Which here is <workbook> child

  <calcPr calcId="124519" fullCalcOnLoad="1"/>

Calc so far does not evaluate that and probably could.

Update 2022-08-08T16:27+02:00
This is tdf#144819.

mikekaganski · June 10, 2022, 10:10am

… which then could allow us to add “Follow document setting” to the “recalculate on load” option - and make it the default