openpyxl auto formating "." to "," after saving on german system

[to.tobi...@gmail.com]

Hello,

I'm using openpyxl to import data from text-files to my Xlsx-File of Excel 2010 on a german Windows 7 system.

Everything I write to my sheets is done with following function (example):

   mainSheet.cell (column = EQUAL_COL, row = curRow).set_value_explicit (value = data,  data_type = Cell.TYPE_STRING)

The data at first run ist stored as e.g. "1.22" in Cell "A1" and saved. The next run another Cell "A2" is written with value "1.33" and saved. If I open the excel file after the second run, the data from Cell "A1" is converted to "1,22".

Is it possible to prevent the conversion from "." to "," of my "text-values"?
What I don't want to use is the excel-option for decimal-seperator, because this settings are global and I need a local-solution for one worksheet.

Thanks for your help.

Regards,

Tobias

[Charlie Clark]

Hi Tobias,

can you clarify more what you mean by "first" and "second" runs?

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

[to.tobi...@gmail.com]

With running I mean, starting my python script, which open a workbook from disk, open desired worksheet,  modify some data and save the progress to same workbook on the filesystem.
The script will check if data stored at different sheets need to be updated. So first "run" would be updating all cells of all sheets in the workbook (all data is stored with "." as decimal-separator). In the second run the script know a update for "sheet2" is available. It will open "sheet2", update the cells and save afterwards (all data of "sheet2" is stored with "." as decimal-separator).
If I open the excel-file, data of "sheet2" is with "." and data of all other sheets is with "," as decimal-separator.

Regards,
Tobias

[Charlie Clark]

When you say "open", you mean in Excel? Is this a fair summary:

* files created by Excel with numbers are read by openpyxl with their
values subsequently stored as strings to enforce "." as the decimal
separator

* Excel reads such files and correctly treats values as strings

* openpyxl coerces the strings to numbers when the file is reopened and
saves them as numbers unless they are otherwise specified individually

The problem you are experiencing is that when editing an Excel file,
openpyxl must read in (and convert) all the data in order to save it. Type
inference kicks in here as .set_explicit_value() only applies when saving
data. I don't think I can think of a solution for this. Decimal separators
are localisation attributes and it is, in my opinion, wrong to use strings
to try and work round them. Type inference is built into the way openpyxl
handles all cells and is generally very useful.

Unless Excel supports this localisation attribute on a per file basis then
you're best option is to have your update script process all the cells in
all the sheets.

[to.tobi...@gmail.com]

Yes I mean opening it via Excel.
Thank you for your correct and complete summary (Sadly I couldn't do this by myself).

I understand how usefull the localisation is in most secnarios, but not in my special scenario.
The values stored in the excel file are version-numbers, which vary from "1.1" to something like "1.15.28.0.40"

I could use another reprensentation for the ".", but i can't image a different separator with same intuitive readability.

If i can't find another solution, I will write each cell as string via set_explicit_value () each time the workbook is openend.

[Charlie Clark]

Am 31.10.2013, 13:50 Uhr, schrieb <to.tobi...@gmail.com>:

> Yes I mean opening it via Excel.
> Thank you for your correct and complete summary (Sadly I couldn't do this
> by myself).
> I understand how usefull the localisation is in most secnarios, but not
> in
> my special scenario.
> The values stored in the excel file are version-numbers, which vary from
> "1.1" to something like "1.15.28.0.40"

hm, that sounds like you may need a custom number format.

> I could use another reprensentation for the ".", but i can't image a
> different separator with same intuitive readability.

It's an interesting question, the "." as a separator is less noisy than a
comma.

> If i can't find another solution, I will write each cell as string via
> set_explicit_value () each time the workbook is openend.

Try a custom number format and avoid forcing the data type every time.
This really is an Excel issue rather than openpyxl.

[to.tobi...@gmail.com]

Am Donnerstag, 31. Oktober 2013 14:02:35 UTC+1 schrieb Charlie Clark:

Am 31.10.2013, 13:50 Uhr, schrieb <to.tobi...@gmail.com>:

> Try a custom number format and avoid forcing the data type every time.  
> This really is an Excel issue rather than openpyxl.

I allready tried to tinker with the number format, but can't see a solution how to accomplish this.
But thanks for your help!

[Charlie Clark]

Am 31.10.2013, 15:33 Uhr, schrieb <to.tobi...@gmail.com>:

> I allready tried to tinker with the number format, but can't see a
> solution
> how to accomplish this.
> But thanks for your help!

Well, I'm not an expert on them either but maybe you could provide a
small, sample file? I already tried the help in Excel for Mac (German) and
it's not very helpful, but in theory you should be able use something like
IP address formatting.

[to.tobi...@gmail.com]

it's like plain data with no special format. You can paste it to your excel worksheet. You'll notice that longer version numbers has still ".", because they are detected as text.

Sheet1:

a1	1,15
a2	1,15
a3	1,77
a4	1,3
a5	1,28
a6	1,11
a7	1,6
a8	1,7
a9	1,11
a10	1,2
a11	1,28

Sheet2:

a	1.79.1.59
b	1,41
c	1,2
d	1,3
e	1,8
f	1,6
g	1,3
h	1.62.4.4

[Charlie Clark]

Right. I understand you now. If the cells are formatted as text then Excel
will respect them but openpyxl prioritises type inference unless you set
workbooks no to:

wb = Workbook(guess_types=False)

Try that.

[to.tobi...@gmail.com]

Thank you Charlie for your solution. After I updated my version of openpyxl to 1.8.0 it worked!

I noticed increased runtime by let's say about 5 times, if I iterate over my contents in workbook and reading cell-values and writing new ones. Is this caused by the new changes in the library?   

[to.tobi...@gmail.com]

Simple time measurement for iterating over filecontents of workbook and copy some cells:

Lib-Version 1.6.2: 1.77699995041 Seconds
Lib-Version 1.8.0 (guess_types = False): 81.0659999847 Seconds
Lib-Version 1.8.0 (guess_types = True): 81.3450000286 Seconds

[Charlie Clark]

Am 04.11.2013, 08:53 Uhr, schrieb <to.tobi...@gmail.com>:

> Thank you Charlie for your solution. After I updated my version of
> openpyxl
> to 1.8.0 it worked!

Glad to know it worked Please note that 1.8 is still work in progress!

> I noticed increased runtime by let's say about 5 times, if I iterate over
> my contents in workbook and reading cell-values and writing new ones. Is
> this caused by the new changes in the library?

Thanks for the timings, I suspect I must have missed out a library. 1.7
should be about the same speed as 1.6.2 and supports "guess_types" but 1.8
does support using lxml (currently only for Python 2.6 & Python 2.7) if it
is installed.

Can you run your timings with lxml installed and with 1.7?

[Charlie Clark]

FWIW I'd accidentally removed using cElementTree if it's around which
would probably explain the slowdown. It's back in now.

[to.tobi...@gmail.com]

So I did some other measurements.

Version 1.7.0 without lxml:                   82.5980000496 Seconds
Version 1.7.0 with lxml:                        81.8789999485 Seconds
Version 1.8.0(tip release) without lxml:  84.7990000248 Seconds
Version 1.8.0(tip release) with lxml:       82.3270001411 Seconds

[to.tobi...@gmail.com]

How do I check openpyxl really used lxml?

[Charlie Clark]

Am 07.11.2013, 09:30 Uhr, schrieb <to.tobi...@gmail.com>:

> How do I check openpyxl really used lxml?

Well, you can check what openpyxl.LML is

You can check implementations:

from openpyxl.shared.xmltools import Element

from lxml.etree import Element as l
from xml.etree.ElementTree import Element as py
from xml.etree.cElementTree import Element as c

You can then check to see which implementation you have:

Element == l and "Using lxml"
Element == c and "Using cElementTree"
Element == py and "Using python ElementTree"

[Charlie Clark]

hm, I can only hope that something is wrong with your install as those
times are unacceptable. The only change in your code is that you're
opening workbooks with guess_types=False, right?

[to.tobi...@gmail.com]

This is right. And I measure only the part of program with heavy openpyxl usage. I could send you a cProfile run (http://docs.python.org/2/library/profile.html), if it would help to identify the problem.


Code:

from openpyxl.shared.xmltools import Element as e

from lxml.etree import Element as l
from xml.etree.ElementTree import Element as py
from xml.etree.cElementTree import Element as c

print str (e) + 'l:' + str (l) + ', Py:' + str (py) + ', C:' + str(c)

will produce:

 <class 'xml.etree.ElementTree.Element'>l:<built-in function Element>, Py:<class 'xml.etree.ElementTree.Element'>, C:<built-in function Element>

I am not sure how to interpret the results for "l" and "c" libraries. And I'm not sure why lxml is not used.

[Charlie Clark]

Am 07.11.2013, 10:50 Uhr, schrieb <to.tobi...@gmail.com>:

> This is right. And I measure only the part of program with heavy openpyxl
> usage. I could send you a cProfile run
> (http://docs.python.org/2/library/profile.html), if it would help to
> identify the problem.

I'll have to admit that I haven't much experience with profiling but send
it anyway.

> Code:
>
>> from openpyxl.shared.xmltools import Element as e
>>
>> from lxml.etree import Element as l
>> from xml.etree.ElementTree import Element as py
>> from xml.etree.cElementTree import Element as c
>>
>>
>> print str (e) + 'l:' + str (l) + ', Py:' + str (py) + ', C:' + str(c)
> will produce:
>
>> <class 'xml.etree.ElementTree.Element'>l:<built-in function Element>,
>> Py:<class 'xml.etree.ElementTree.Element'>, C:<built-in function
>> Element>
>>
> I am not sure how to interpret the results for "l" and "c" libraries. And
> I'm not sure why lxml is not used.

If I read this correctly, on your system it's always the Python
implementation that is being used. I suppose this could be caused by stale
versions somewhere. I'll add a test to see that the "fastest" version is
always used and see what Travis / Bamboo make of it.

As far as openpyxl is concerned there isn't much difference between using
cell.value = x and cell.set_explicit_value(x, data_type) # new method name
in 1.8 branch.

[to.tobi...@gmail.com]

Any idea for fixing this stale versions?

I hope everything is right with converting.... With profiling it took 122.115 Seconds

ncalls	tottime	percall	cumtime	percall	filename:lineno(function)
1	0,000	0,000	122,115	122,115	<string>:1(<module>)
54	0,000	0,000	0,000	0,000	ProjectList_Updater.py:287(get_column_letter)
19792	0,048	0,000	0,440	0,000	ProjectList_Updater.py:422(rowContainsData)
726	0,001	0,000	0,002	0,000	ProjectList_Updater.py:447(checkListIsEqual)
4	0,068	0,017	19,018	4,755	ProjectList_Updater.py:464(checkEqualityOfRows)
46	0,193	0,004	97,745	2,125	ProjectList_Updater.py:522(getDictOfLibCs)
4	0,004	0,001	1,541	0,385	ProjectList_Updater.py:552(getListOfLibCs)
1	0,090	0,090	122,115	122,115	ProjectList_Updater.py:584(updateCells)
4	0,103	0,026	3,220	0,805	ProjectList_Updater.py:715(setStyleOfTable)
1	0,000	0,000	0,000	0,000	__init__.py:49(normalize_encoding)
1	0,000	0,000	0,001	0,001	__init__.py:71(search_function)
131812	0,364	0,000	0,541	0,000	cell.py:115(get_column_letter)
556	0,003	0,000	0,004	0,000	cell.py:184(__init__)
9320	0,007	0,000	0,010	0,000	cell.py:198(encoding)
9320	0,027	0,000	0,050	0,000	cell.py:205(check_string)
9320	0,025	0,000	0,075	0,000	cell.py:235(set_value_explicit)
111294	0,100	0,000	117,604	0,001	cell.py:332(_get_value)
111294	0,088	0,000	0,175	0,000	cell.py:379(has_style)
206187	0,188	0,000	119,535	0,001	cell.py:384(style)
317481	0,195	0,000	0,195	0,000	cell.py:394(get_coordinate)
111294	0,239	0,000	11,750	0,001	cell.py:425(is_date)
556	0,002	0,000	0,003	0,000	cell.py:56(coordinate_from_string)
10658	0,016	0,000	0,034	0,000	cell.py:83(column_index_from_string)
21316	0,009	0,000	0,009	0,000	cell.py:99(<genexpr>)
1	0,000	0,000	0,000	0,000	codecs.py:1034(make_identity_dict)
1	0,000	0,000	0,000	0,000	codecs.py:77(__new__)
15898725/334710	33,163	0,000	115,001	0,000	copy.py:145(deepcopy)
3858886	0,438	0,000	0,438	0,000	copy.py:198(_deepcopy_atomic)
3235530/669420	17,036	0,000	101,762	0,000	copy.py:253(_deepcopy_tuple)
3235530/669420	6,889	0,000	93,838	0,000	0,000 copy,py:253(_deepcopy_dict)
11947711	8,445	0,000	11,314	0,000	copy,py:267(_keep_alive)
1617765/334710	11,670	0,000	109,470	0,000	0,000 copy,py:306(_reconstruct)
1617765	0,849	0,000	1,463	0,000	copy_reg.py:92(__newobj__)
3	0,000	0,000	0,000	0,000	copy_reg.py:95(_slotnames)
92	0,000	0,000	0,000	0,000	cp850.py:11(encode)
1	0,000	0,000	0,000	0,000	cp850.py:17(IncrementalEncoder)
1	0,000	0,000	0,000	0,000	cp850.py:21(IncrementalDecoder)
1	0,000	0,000	0,000	0,000	cp850.py:25(StreamWriter)
1	0,000	0,000	0,000	0,000	cp850.py:28(StreamReader)
1	0,000	0,000	0,000	0,000	cp850.py:3(<module>)
1	0,000	0,000	0,000	0,000	cp850.py:33(getregentry)
1	0,000	0,000	0,000	0,000	cp850.py:9(Codec)
556	0,001	0,000	0,001	0,000	date_time.py:71(__init__)
19792	0,018	0,000	0,077	0,000	re.py:139(search)
19792	0,023	0,000	0,031	0,000	re.py:226(_compile)
1	0,000	0,000	0,000	0,000	sre_compile.py:33(_compile)
1	0,000	0,000	0,000	0,000	sre_compile.py:362(_compile_info)
2	0,000	0,000	0,000	0,000	sre_compile.py:475(isstring)
1	0,000	0,000	0,000	0,000	sre_compile.py:481(_code)
1	0,000	0,000	0,000	0,000	sre_compile.py:496(compile)
2	0,000	0,000	0,000	0,000	sre_parse.py:139(append)
1	0,000	0,000	0,000	0,000	sre_parse.py:141(getwidth)
1	0,000	0,000	0,000	0,000	sre_parse.py:179(__init__)
5	0,000	0,000	0,000	0,000	sre_parse.py:183(__next)
1	0,000	0,000	0,000	0,000	sre_parse.py:196(match)
4	0,000	0,000	0,000	0,000	sre_parse.py:202(get)
1	0,000	0,000	0,000	0,000	sre_parse.py:258(_escape)
1	0,000	0,000	0,000	0,000	sre_parse.py:302(_parse_sub)
1	0,000	0,000	0,000	0,000	sre_parse.py:380(_parse)
1	0,000	0,000	0,000	0,000	sre_parse.py:676(parse)
1	0,000	0,000	0,000	0,000	sre_parse.py:68(__init__)
1	0,000	0,000	0,000	0,000	sre_parse.py:91(__init__)
55785	0,136	0,000	0,212	0,000	style.py:136(__init__)
557850	0,731	0,000	1,096	0,000	style.py:165(__init__)
55785	0,377	0,000	1,473	0,000	style.py:191(__init__)
55785	0,074	0,000	0,074	0,000	style.py:229(__init__)
55785	0,057	0,000	0,057	0,000	style.py:332(__init__)
109293	0,320	0,000	0,351	0,000	style.py:362(is_date_format)
55785	0,049	0,000	0,049	0,000	style.py:384(__init__)
55785	0,376	0,000	2,471	0,000	style.py:400(__init__)
55785	1,586	0,000	119,058	0,002	style.py:410(copy)
725205	0,494	0,000	0,494	0,000	style.py:69(__init__)
55785	0,177	0,000	0,230	0,000	style.py:93(__init__)
556	0,000	0,000	0,000	0,000	workbook.py:115(excel_base_date)
50	0,001	0,000	0,002	0,000	workbook.py:163(get_sheet_by_name)
8	0,000	0,000	0,000	0,000	worksheet.py:313(__init__)
556	0,000	0,000	0,000	0,000	worksheet.py:474(parent)
9320	0,003	0,000	0,003	0,000	worksheet.py:478(encoding)
1187	0,000	0,000	0,000	0,000	worksheet.py:515(_get_title)
131812	0,292	0,000	0,935	0,000	worksheet.py:566(cell)
131812	0,094	0,000	0,102	0,000	worksheet.py:603(_get_cell)
108	0,001	0,000	0,003	0,000	worksheet.py:615(get_highest_row)
770	0,006	0,000	0,041	0,000	worksheet.py:625(get_highest_column)
206187	0,179	0,000	119,238	0,001	worksheet.py:719(get_style)
1	0,001	0,001	0,001	0,001	{__import__}
92	0,000	0,000	0,000	0,000	{_codecs.charmap_encode}
1	0,000	0,000	0,000	0,000	{_sre.compile}
10658	0,005	0,000	0,014	0,000	{all}
109293	0,031	0,000	0,031	0,000	{any}
1617766	0,614	0,000	0,614	0,000	{built-in method __new__ of type object at 0x000000001E29D0E0}
131866	0,047	0,000	0,047	0,000	{chr}
131866	0,066	0,000	0,066	0,000	{divmod}
3235530	2,046	0,000	2,046	0,000	{getattr}
1617769	1,983	0,000	1,983	0,000	{hasattr}
36269971	3,134	0,000	3,134	0,000	{id}
4862623	1,223	0,000	1,223	0,000	{isinstance}
1617765	0,611	0,000	0,611	0,000	{issubclass}
6501692	0,779	0,000	0,779	0,000	{len}
875	0,002	0,000	0,002	0,000	{max}
1617765	1,217	0,000	12,166	0,000	{method '__reduce_ex__' of 'object' objects}
16607541	2,693	0,000	2,693	0,000	{method 'append' of 'list' objects}
726	0,001	0,000	0,001	0,000	{method 'count' of 'list' objects}
1	0,000	0,000	0,000	0,000	{method 'disable' of '_lsprof.Profiler' objects}
29483998	4,014	0,000	4,014	0,000	{method 'get' of 'dict' objects}
3	0,000	0,000	0,000	0,000	{method 'get' of 'dictproxy' objects}
556	0,000	0,000	0,000	0,000	{method 'groups' of '_sre.SRE_Match' objects}
9026	0,071	0,000	0,071	0,000	{method 'index' of 'list' objects}
1	0,000	0,000	0,000	0,000	{method 'items' of 'dict' objects}
4853295	3,646	0,000	3,646	0,000	{method 'iteritems' of 'dict' objects}
131867	0,022	0,000	0,022	0,000	{method 'join' of 'str' objects}
105	0,001	0,000	0,001	0,000	{method 'keys' of 'dict' objects}
556	0,001	0,000	0,001	0,000	{method 'match' of '_sre.SRE_Pattern' objects}
1	0,000	0,000	0,000	0,000	{method 'replace' of 'str' objects}
9320	0,008	0,000	0,008	0,000	{method 'replace' of 'unicode' objects}
131866	0,012	0,000	0,012	0,000	{method 'reverse' of 'list' objects}
19792	0,028	0,000	0,028	0,000	{method 'search' of '_sre.SRE_Pattern' objects}
1	0,000	0,000	0,000	0,000	{method 'split' of 'str' objects}
10	0,000	0,000	0,000	0,000	{method 'startswith' of 'str' objects}
1	0,000	0,000	0,000	0,000	{method 'translate' of 'str' objects}
1617766	0,774	0,000	0,774	0,000	{method 'update' of 'dict' objects}
11770	0,002	0,000	0,002	0,000	{method 'upper' of 'str' objects}
393	0,001	0,000	0,001	0,000	{method 'write' of 'file' objects}
2	0,000	0,000	0,000	0,000	{min}
10659	0,001	0,000	0,001	0,000	{ord}
3235531	1,667	0,000	1,667	0,000	{range}
3737595	1,207	0,000	1,207	0,000	{setattr}

[Charlie Clark]

Am 07.11.2013, 11:34 Uhr, schrieb <to.tobi...@gmail.com>:

> Any idea for fixing this stale versions?

Delete any .pyc or .pyo files where an .py file of the same name exists.
Best to set up a separate virtualenv.

[Charlie Clark]

Am 07.11.2013, 11:34 Uhr, schrieb <to.tobi...@gmail.com>:

> I hope everything is right with converting.... With profiling it took
> 122.115 Seconds

As I said I'm far from expert on this but it looks to me like it spends a
lot of time in copying which may well be related to the changes in
handling styles that Adam did.

If I'm write a profile with 1.6.2 won't have lines like this:

codecs.py:77(__new__) 15898725/334710 33,163 0,000 115,001 0,000
copy.py:145(deepcopy) 3858886 0,438 0,000 0,438 0,000
copy.py:198(_deepcopy_atomic) 3235530/669420 17,036 0,000 101,762 0,000
copy.py:253(_deepcopy_tuple) 3235530/669420 6,889 0,000 93,838 0,000 0,000
copy,py:253(_deepcopy_dict) 11947711 8,445 0,000 11,314 0,000
copy,py:267(_keep_alive) 1617765/334710 11,670 0,000 109,470 0,000 0,000
copy,py:306(_reconstruct) 1617765 0,849 0,000 1,463 0,000
copy_reg.py:92(__newobj__) 3 0,000 0,000 0,000 0,000
copy_reg.py:95(_slotnames) 92 0,000 0,000 0,000 0,000

[to.tobi...@gmail.com]

If i have a look on the profile the main problem is at getDictofLibCs() function with 97 Seconds. It must be interacting with copy.py (deep, atomic, ...), which has a runtime of ~78 Seconds.

But this function is very simple:

def getDictOfLibCs (sheet):
   dictData = dict ()
  
   # collect the data sheet
   for row in xrange (sheet.get_highest_row ()):
     
      if (None != sheet.cell (column = COL_ZERO, row = row).value):
         dictData [sheet.cell (column = COL_ZERO, row = row).value] = sheet.cell (column = COL_ONE, row = row).value
      else:
         #reached end of data
         break
     
   return dictData

I think a big part of functionality from openpyxl has changed, which caused this problem.
If i have a look on the profile of Lib-Version 1.6.2 wasn't mentioned at all and the function above had a runtime of 0.022 Seconds.

In addition i forced xmltools to use the lxml imports of ElementTree with no big difference in runtime.

[Charlie Clark]

Am 07.11.2013, 12:47 Uhr, schrieb <to.tobi...@gmail.com>:

> If i have a look on the profile the main problem is at getDictofLibCs()
> function with 97 Seconds. It must be interacting with copy.py (deep,
> atomic, ...), which has a runtime of ~78 Seconds.

Where is this in the source? I can't find it.

[to.tobi...@gmail.com]

Sorry, this is code from my python-script. I just wanted to state thats not the problem of my implementation.

[Charlie Clark]

Am 07.11.2013, 13:03 Uhr, schrieb <to.tobi...@gmail.com>:

> Sorry, this is code from my python-script. I just wanted to state thats
> not
> the problem of my implementation.

Oh, good. I am planning to add some simple benchmark scripts so that we
can keep an eye on things in the future.