simple formula write not working

Issue #109 resolved
Terry Brown
created an issue

This simple test case isn't working for me, and I can't tell whether it should or not:

{{{

!python

from openpyxl.workbook import Workbook wb = Workbook() dest_filename = r'xp/empty_book.xlsx' ws = wb.worksheets[0] ws.cell('a1').value = 42 ws.cell('b1').value = '=a1+10' wb.save(filename = dest_filename) }}}

Excel 2007 (running on XP SP3) reports 'Excel found unreadable content in 'empty_book.xlsx'. Do you want to recover...

Recovery leaves b1 blank. Everything else seems to be working (making large sheets etc), but anything that sets _data_type to 'f' (strings starting with "=", set_value_explicit type='f') has the above result.

I'm using openpyxl 1.5.6 in Ubuntu 11.10 Python 2.7

Comments (12)

  1. Mr Vadtec

    I too have run into this issue. I have a worksheet that has 7 fields that all use formulas, but when I open this with Excel 2007 on Win7 64-bit, I get the same error as above. This greatly reduces the usefulness of openpyxl. Since I started using openpyxl today (quite literally) I have no idea where to look in the code as of yet. Maybe this second bug report will bring this bug some attention.

  2. Mr Vadtec

    I saved the .xlsx file locally, changed it to a .zip and opened the files within, and I found what is causing the error. This error seems to be getting thrown for the last formula in the worksheet.

    To check this, I removed the = from the value for the last formula. When I next opened the generated spreadsheet, the second to last formula in the worksheet threw the same error. I repeated this for every formula in the worksheet until all the formulas were in effect disabled.

    I created the same spreadsheet in Excel (same machine I'm opening the generated spreadsheet on) and viewed the XML for the same formulas. What I noticed was the difference of the "t" parameter. In the generated XML, the t parameter is "f", whereas in the XML generated by Excel itself the t parameter is "str".

    I mucked around with the code a bit and changed values in various places. All I ended up with was funky spreadsheets. Go figure...

    It appears there is a bug with the XML being output for formulas related to the "t" parameter to the "c" tag. Maybe this will shed some light on the issue.

  3. Mr Vadtec

    After mucking with this for another 4 hours, I finally came up with a simple solution.

    In writer/worksheet.py, in function write_worksheet_data(), line 183 (at the time of this writing), I changed the following:

    attributes['t'] = cell.data_type

    to: if cell.data_type == 'f': attributes['t'] = 's' else: attributes['t'] = cell.data_type

    I don't know if this will be considered hack-ish or not, but it fixed the issue with these formulas not wanting to load due to parser errors.

    Hopefully this gets fixed/implemented soon.

  4. Eric Gazoni

    Fix issue #109: simple formula write not working

    This is based on the observation that for cells of number and formula types, the "t" attribute of a cell is optional for OpenOffice, and is actually harmful for MS Excel 2007 to interpret formula cells. Therefore once we remove that when cell's data type is number or formula, the issue is resolved. I have tested the code provided in the issue post, and verified that the generated excel file is OK both for OpenOffice and MS Excel.

    → <<cset 1d433dd4f45e>>

  5. Bob Pool

    I am still getting the same errors described above. I end up with the sheet I create, plus a "Recovered Sheet". I am using Excel 2010.

    import sys
    sys.path.append(r'Q:\programs\Python\AddIns\openpyxl-1.6.2-py2.7.egg')
    import openpyxl as xl
    
    output_filename = r"c:\test.xlsx"
    
    wb = xl.Workbook()
    ws = wb.create_sheet()
    ws.title = "test"
    
    ws.cell(row = 0, column = 0).value = "1"
    ws.cell(row = 1, column = 0).value = "2"
    ws.cell(row = 2, column = 0).value = "=SUM(A1:A2)"
    
    wb.add_sheet(ws)
    wb.save(output_filename)
    

    I have also tried using set_value_explicit option

    ws.cell(row = 2, column = 0).set_value_explicit( value = "=SUM(A1:A2)", data_type = 'f')
    
  6. Mushoshin

    It appears this problem still exists as of openpyxl-2.4.8 in python 3.6, for excel 2013 (I could not check for other Excel versions). I wonder if it is not related to formulas.py content, as it only contains formulae names in English, while localized versions of Excel all contains their respective formulae names' translation. Else, I can't see, because the code in the writer above completely changed for what I have been able to investigate. Also, when opened with libre office 5, formulae are here but throw an error "Err :508", while Excel merely destroy these to be able to open the file. It is awesomely frustrating, because Openpyxl is great, but that it is not working with formulae is a pity. I can circumvent this by replacing "=" in formulae with a substitute that I then replace in the file, but this is a bit of a problem. Don't hesitate to ask me for more infos, I am willing to help and improve this aspect of the module. Thanks

  7. CharlieC

    @Mushoshin please create a separate issue with a sample file and sample code. Whatever you're experiencing is not related to this issue and has nothing to do with localisation of formulae: these are always stored in English in OOXML. See the documentation for the list of supported formulae.

  8. Log in to comment