Trouble with formulas

[akud...@gmail.com]

Hi there!

I'm using the openpyxl because of it's ability to keep cell's format, which is really important for my task. But I've found some trouble with formulas. Look here:

There is a template of excel file, which must be modified. After all the things had been done, I realized, that all the formulas from a template are wiped out.

I've followed some advises from this theme https://groups.google.com/forum/#!topic/openpyxl-users/MN45JfNJyDE

I've tried to write formulas again, but there is the big problem. After using the set_value_explicit(), I've opened excel document and an error message was occured: 'the are some data unable to read. Try to repair document?' - or some kind of this in my translation variant. Option 'yes' will delete this formula, option 'no' will leave the document unable to open.

So, I can write formulas, but I can't open the document after.

Does someone know how to make it?

My aim is to copy formulas or write them again using openpyxl. I can't use xlwt+xlrd because utils (copy func) haven't been ported on the python 3.

Andrey.

[Charlie Clark]

Am 10.09.2013, 20:22 Uhr, schrieb <akud...@gmail.com>:

> So, I can write formulas, but I can't open the document after.
> Does someone know how to make it?
> My aim is to copy formulas or write them again using openpyxl. I can't
> use
> xlwt+xlrd because utils (copy func) haven't been ported on the python 3.

I implemented support for reading formulae in my fork:

https://bitbucket.org/charlie_x/openpyxl-ccr

I'm not sure if it will ever get merged upstream because opinions are
divided as to which value (and there can currently only be one, Excel
itself uses a cache) a cell should have: the formula or the value
calculated by the formula.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[akud...@gmail.com]

вторник, 10 сентября 2013 г., 21:48:55 UTC+3 пользователь Charlie Clark написал:

Thanks for your answer! I've installed your module, but there is an another problem now:

>>> from openpyxl.reader.excel import load_workbook

>>> wb = load_workbook(filename = r'e://reg/template.xlsx')

Traceback (most recent call last):

  File "<pyshell#6>", line 1, in <module>

    wb = load_workbook(filename = r'e://reg/template.xlsx')

  File "build\bdist.win-amd64\egg\openpyxl\reader\excel.py", line 126, in load_workbook

    _load_workbook(wb, archive, filename, use_iterators)

  File "build\bdist.win-amd64\egg\openpyxl\reader\excel.py", line 169, in _load_workbook

    new_ws = read_worksheet(archive.read(worksheet_path), wb, sheet_name, string_table, style_table)

  File "build\bdist.win-amd64\egg\openpyxl\reader\worksheet.py", line 228, in read_worksheet

    fast_parse(ws, xml_source, string_table, style_table)

  File "build\bdist.win-amd64\egg\openpyxl\reader\worksheet.py", line 139, in fast_parse

    value = "=" + formula

TypeError: Can't convert 'bytes' object to str implicitly

What should I do?

Andrey 

[Charlie Clark]

Am 10.09.2013, 21:25 Uhr, schrieb <akud...@gmail.com>:

> in fast_parse
> value = "=" + formula
> TypeError: Can't convert 'bytes' object to str implicitly
> What should I do?

Hi Andrey,

it looks like the formula is being treated by Python as binary data (this
is what the bytes type in Python 3 is for) so it will need explicit
casting. I didn't come across in tests but then there are quite a few
parts of openpyxl which don't seem to play nicely with Python 3 (the
StringIO stuff comes to mind).

You could try to replace the line with:

value = "=%s" % formula

or otherwise explicit convert to a str
value = "=" + str(formula)

Let me know if either of those work. The best thing to do would be to open
an issue on my fork and supply a test file because I'd really like to know
why the formula is being treated as binary data.

[akud...@gmail.com]

среда, 11 сентября 2013 г., 12:22:00 UTC+3 пользователь Charlie Clark написал:

Am 10.09.2013, 21:25 Uhr, schrieb <akud...@gmail.com>:

> in fast_parse
>     value = "=" + formula
> TypeError: Can't convert 'bytes' object to str implicitly
> What should I do?

Hi Andrey,

it looks like the formula is being treated by Python as binary data (this  
is what the bytes type in Python 3 is for) so it will need explicit  
casting. I didn't come across in tests but then there are quite a few  
parts of openpyxl which don't seem to play nicely with Python 3 (the  
StringIO stuff comes to mind).

You could try to replace the line with:

value = "=%s" % formula

or otherwise explicit convert to a str
value = "=" + str(formula)

Let me know if either of those work. The best thing to do would be to open  
an issue on my fork and supply a test file because I'd really like to know  
why the formula is being treated as binary data.

Charlie
--

Hi again!

This string

value = "=" + str(formula)

make skript works without errors, BUT there appears the same error, as  when using the set_value_explicit(), i.e.  'the are some data unable to read. Try to repair document?'. Maybe there is some troble with readding hard formulas or with formulas written on enouther language (Russian is used in this workbook, writting simple formulas like "SUM" is ok). I append the document, which I am working with and the script I am using.

Thanks for your help!

Andrey

[Charlie Clark]

Am 12.09.2013, 19:16 Uhr, schrieb <akud...@gmail.com>:

> Hi again!
> This string
> value = "=" + str(formula)

> make skript works without errors, BUT there appears the same error, as
> when using the set_value_explicit(), i.e. 'the are some data unable to
> read. Try to repair document?'. Maybe there is some troble with readding
> hard formulas or with formulas written on enouther language (Russian is
> used in this workbook, writting simple formulas like "SUM" is ok). I
> append
> the document, which I am working with and the script I am using.

The error is probably unrelated to the formula. I get it with some files
with some of my chart improvements (interestingly only in Windows, Office
for Mac seems happy enough, which tells us a bit about the codebase, I
think!) but the errors are rarely fatal. This is one reason why I'd like
to add validation for all the generated XML.

I would suggest that you try creating a file and adding parts from your
file to try and isolate what stuff is causing the problem. If some of your
strings are being incorrectly identified as binary then I suspect that
would be the problem. It would be really helpful to have a sample file to
work with.