how to keep the style and format when editing a xlsx file?

[jufe...@gmail.com]

hi:

   I have a xlsx file and it has some styles (colors) and also I set format for the cells in a column.

   Now I am trying to use python to modify the data of the xlsx file, for example: change A12 from '1' to '2'

   but after I save the file, all the format and style are lost.

   Is there any solution to keep the style and format? I just wish to modify some data.

Thanks.

   

[Charlie Clark]

Cell formatting should be preserved by openpyxl. If this is not happening
then it could be a bug. Can you supply a sample Excel file and script
you've used to process it.

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

[sander...@hotmail.com]

I was about to ask the exact same question. Sometimes my script leaves the formatting intact, but sometimes it removes everything, leaving only data. I've been trying to repro it all day, but so far it seems totally random.

Don't wish to hijack the thread, but since it's the same topic I'll supply some files. The file_without_formatting.xlsx is the result of the script. The file with formatting is the file before running the script. The openpyxl version I'm running is 2.3.3.

Thanks
Sander

[jufe...@gmail.com]

hi:

   Is any special usage for openpyxl to keep the format?

  I used it as below:

from openpyxl import load_workbook

wb = load_workbook(filename)

ws = wb.get_sheet_by_name("cases")

c = ws.cell(row = 2, column = 4)

c.value = '11'

wb.save(filename)

在 2016年3月4日星期五 UTC+8下午6:57:26，Charlie Clark写道：

[Charlie Clark]

Am .03.2016, 02:13 Uhr, schrieb <jufe...@gmail.com>:

> Is any special usage for openpyxl to keep the format?

No. The only thing that might be an issue is the use of style templates.
These should be preserved but I haven't done a lot of testing.

[Charlie Clark]

Am .03.2016, 21:01 Uhr, schrieb <sander...@hotmail.com>:

> I was about to ask the exact same question. Sometimes my script leaves
> the formatting intact, but sometimes it removes everything, leaving only
> data.

> I've been trying to repro it all day, but so far it seems totally random.

> Don't wish to hijack the thread, but since it's the same topic I'll
> supply some files. The file_without_formatting.xlsx is the result of the
> script.
> The file with formatting is the file before running the script. The
> openpyxl version I'm running is 2.3.3.

It doesn't look like the same issue to me: the file roundtrips fine
keeping the formatting and protection intact.

The script you supplied, however, seems to be doing something different as
it wants to load different workbooks. Styles won't be copied from one
workbook to another.

FWIW lines like these feel like a kick in the stomach:

while str(sheet.cell(row=i,column=1).value).lower() != uzb:
i+=1
for r in range(1,10):
sheet.cell(row=i,column=x).value =
wb1['Picken'].cell(row=13,column=y).value


You're probably much better off working with and looping over ranges of
cells. Would make the code much easier to understand.

[sander...@hotmail.com]

Sorry about that, I'll try to improve that bit. However, I'm not trying to copy any formatting. Like the OP, I'm simply copying a few values from wb1 to wb2, that's all. And yet the script still removes all of wb2's formatting without any good reason. Are you saying my obscure function might be the sole cause?

Thanks for the feedback.
Sander

[Charlie Clark]

Am .03.2016, 19:21 Uhr, schrieb <sander...@hotmail.com>:

> Sorry about that, I'll try to improve that bit. However, I'm not trying
> to
> copy any formatting. Like the OP, I'm simply copying a few values from
> wb1
> to wb2, that's all. And yet the script still removes all of wb2's
> formatting without any good reason. Are you saying my obscure function
> might be the sole cause?

Well, I simply can't execute your script locally as it seems to want to
read two different files.

When I take the original file and save it under a different name the
formatting is preserved.

Please provide the "minimal working example" of the problem.

[sander...@hotmail.com]

Okay. The script loops through the cells BY13-CG13 of file1, while copying the values to B38-J38 of file 2.

The first time I run this script, everything goes fine. But then, it I manually remove the values from B38-H38, save the file, and run the script again, it does paste the values correctly but it removes all of the formatting.
Thanks again.

[sander...@hotmail.com]

edit: Small typo. When I talk in the previous post about manually removing the values from B38-H38, I meant B38-J38. When debugging I keep running the script, and then deleting all the values that were just added so I can run it again. That's when the problem occurs.

[Charlie Clark]

Am .03.2016, 07:50 Uhr, schrieb <sander...@hotmail.com>:

> Okay. The script loops through the cells BY13-CG13 of file1, while
> copying> the values to B38-J38 of file 2.

This is best done like this:
ws1 = wb1['Picken']
ws2 = wb2['Rotterdam']

for src_row, target_row in zip(ws1["BY13":"CG13"], ws2["B38":"J38"]):
for src_cell, target_cell in zip(src_row, target_row):
target_cell.value = src_cell.value

> The first time I run this script, everything goes fine. But then, it I
> manually remove the values from B38-H38, save the file, and run the
> script again, it does paste the values correctly but it removes all of
> the
> formatting.

What do you mean by "manually formatting"? If you're editing the file in
some kind of program then why do you think that openpyxl is dropping
formatting? In any case, I can't reproduce that here. It's also worth
noting that Excel thinks "file1.xlsx" is broken.

[sander...@hotmail.com]

With "manually removing", I simply meant opening the file with LibreOffice and removing the data that my script had just added to it. But I see now what's going on:

I added "file2(result).xlsx" to my previous post to show you what the file looked like after having used openpyxl. When I open that file myself in LibreOffice, all formatting is gone. I just opened that same file in Excel, and to my surprise the formatting is still there. I use Excel at work, but LibreOffice at home. Looks like openpyxl does something to the file that LibreOffice doesn't like. Is this a known issue with LibreOffice?

Thanks for the advice on the loop.

[Charlie Clark]

Am .03.2016, 19:29 Uhr, schrieb <sander...@hotmail.com>:

> With "manually removing", I simply meant opening the file with
> LibreOffice and removing the data that my script had just added to it.
> But I see now
> what's going on:

Sorry, but this is not a sensible way to analyse the problem and very
frustrating for me.

> I added "file2(result).xlsx" to my previous post to show you what the
> file looked like after having used openpyxl. When I open that file
> myself in
> LibreOffice, all formatting is gone. I just opened that same file in
> Excel, and to my surprise the formatting is still there. I use Excel at
> work, but LibreOffice at home. Looks like openpyxl does something to the
> file that
> LibreOffice doesn't like. Is this a known issue with LibreOffice?

I've no idea. We work very hard on following the official OOXML
specification rather than worrying about what particular applications do.
At the end of the day, however, for most people Excel >= 2010 is what
matters. There have been some issues with LibreOffice posted to the list
in the past, some of which have been resolved by updating to a newer
version of LibreOffice.

[sander...@hotmail.com]

On Sunday, March 13, 2016 at 7:39:17 PM UTC+1, Charlie Clark wrote:

Am .03.2016, 19:29 Uhr, schrieb <sander...@hotmail.com>:

Sorry, but this is not a sensible way to analyse the problem and very  
frustrating for me.

Yes I agree. I just thought I'd mention it, since the issue arose only after having edited the file in LibreOffice. Anyway I'm glad the formatting was preserved under Excel. That's what the end users will be using.
About the advice on the loop, could you please help me understand why my earlier version (below) was inferior? If the ranges have to be open to change based on different input, would you advise a different method from the one you suggested?

for i in range(0,9):
    wb2['Sheet'].cell(row=38,column=x).value = wb1['Sheet'].cell(row=13,column=y).value
    x+=1
    y+=1

Sander

[Charlie Clark]

Am .03.2016, 21:55 Uhr, schrieb <sander...@hotmail.com>:

Yes I agree. I just thought I'd mention it, since the issue arose only
> after having edited the file in LibreOffice. Anyway I'm glad the
> formatting was preserved under Excel. That's what the end users will be
> using.

Then that's what you should use in your toolchain.

> About the advice on the loop, could you please help me understand why my
> earlier version (below) was inferior? If the ranges have to be open to
> change based on different input, would you advise a different method from
> the one you suggested?
>
>
> for i in range(0,9):
> wb2['Sheet'].cell(row=38,column=x).value =
> wb1['Sheet'].cell(row=13,column=y).value
> x+=1
> y+=1

1) legibility: you have a two worksheet and cell look ups in a single
statement. The following is easier to read (and faster):

value = ws1.cell(row=13,column=y).value
ws2.cell(row=38, column=x, value=value)

But it's even nicer without individual cell lookups.

2) maintainability: you're relying manual counters rather than the ranges
themselves. This is very brittle, likely to break, be a PITA to test and
maintain, and slow.

In your earlier code it looked like you had some code that searched.
Nested zip() isn't particularly beautiful – you could also work with some
kind of offsets – but it takes the range definitions out of the loop.

x1 = 13
x2 = x1 + 25

for row in ws1.iter_rows(…):
for c1 in row:
ws2.cell(row=x2, col=c1.col, value=c1.value)

2.4 will make programmatic and ad hoc access of rows and columns easier:

r1 = ws1[13]
r2 = ws2[38]

for c1, c2 in zip(r1, r2):
c2.value = c1.value

What's not to like?

[sander...@hotmail.com]

Nice, when is that coming out?

Still have a question though. Within either the iter_rows() or the zip() brackets I need to specify a range, like: ws['A1':'C1']. However I need these ranges to be flexible rather than hard coded into the script. This is what the ws.cell(row=x, column=y) format allows me to do, but of course that only works on single cells. Is there any way to reference a range of multiple cells, other than the "ws['A1':'C1']" format?

Thanks for the explanation!

[Charlie Clark]

Am .03.2016, 21:24 Uhr, schrieb <sander...@hotmail.com>:

> Nice, when is that coming out?

"When it's ready"

> Still have a question though. Within either the iter_rows() or the zip()
> brackets I need to specify a range, like: ws['A1':'C1'].

Well, you can do this dynamically if you're prepared to convert to
coordinates. But the future syntax will be cleaner:

* slicing with A1-style coordinates primarily for ad-hoc stuff
* iter_rows() and iter_cols() for programmatic access

> However I need
> these ranges to be flexible rather than hard coded into the script. This
> is> what the ws.cell(row=x, column=y) format allows me to do, but of
> course
> that only works on single cells. Is there any way to reference a range of
> multiple cells, other than the "ws['A1':'C1']" format?

ws.get_squared_range(…)