0

I am writing a python script to add a new sheet in a xls file, and I am using xlrd, xlutils.copy and xlwt to do it. Here is what my code looks like :

wb=xlwt.Workbook()
rb=xlrd.open_workbook(MY_FILE_PATH, formatting_info=True)           
wb = copy(rb)   
sht1 = wb.add_sheet('newSheet')
-- add some data
wb.save(MY_FILE_PATH)

The thing is, the formats for some cells which I didn't touch in the existing sheets (you can see I only add a new sheet) get changed. To be specific, I have two changes:

  • Some cells which originally have format as date (which by default have format as yyyy/m/d)now have format as customized (and format string as m/d/yy).

  • I lose all foramts I set in the conditional formatting.

Could someone tell me how can I preserve the format in the cells that I don't need to modify? I am using python 3.5.5 64 bits on windows and xlrd version 1.1.0, xlutils 2.0.0, xlwt 1.3.0 . Thank you very much!

update:

I did more test by changing the last call wb.save(MY_FILE_PATH) to wb.save(MY_FILE_PATH_2), i.e., I saved the file by a new name. I can see that only after the save call the file get changed (the original MY_FILE_PATH remained the same in this case). And actually the size of the newly-saved file was smaller than the original one, even though the new file had a sheet added. This suggests that in the save call some formatting information was lost. At least from what I can see the conditional formatting was lost which reduced the size (I assume the change of date format doesn't affect the file size too much).

tete
  • 4,859
  • 11
  • 50
  • 81
  • did you try opening it with excel and libre office calc to see if it is coming from excel or it is your code – ThunderHorn Aug 08 '18 at 12:12
  • @KostadinSlavov in which step you mean? When I opened the file before the `open_workbook` call the file was untouched and as desired. And next call `copy` made an individual copy of the original file in the memory and all operations after thant until the last line `save` were made to this copy, not to original file directly (in another word this file was not changed when I opened it in Excel and checked), and only after the `save` call, the changes I described were added and saved to the file in the disk. – tete Aug 09 '18 at 01:07
  • It sounds like the modules you're using are reading in the entire sheet but missing the conditional formatting. That would explain my they are not correctly writing them back out again. Have you considered trying xlsxwriter instead? See https://xlsxwriter.readthedocs.io/ – David Gaertner Aug 09 '18 at 01:32
  • @DavidGaertner Does it also work with `xls` format? Because I need to deal with legacy spreadsheets in old format. When I searched online I see that `openpyxl` only works with `xlsx`, and to my understanding from your link so does `xlsxwriter`? – tete Aug 09 '18 at 02:47
  • The xls format is an interesting twist. I wonder if reading using xlrd and writing use xlsxwriter is an option. Also, I found this answer, and I hope it helps: https://stackoverflow.com/questions/16448882/conditionally-formatting-text-strings-from-excel-using-python – David Gaertner Aug 09 '18 at 04:31
  • @DavidGaertner In the comment section of the market answer on your link it says that the `conditional formatting` is lost. And @Murli in mentioned in his answer (and some people mentioned in some other questions) that it is because `xlrd` doesn't read the `conditional formatting` info. So I guess importing from `xlrd` and wirting using `openpyxl` (or `xlsxwriter`) won't work either. I guess I'd be better off using other language / library such as C# – tete Aug 09 '18 at 09:16

1 Answers1

1

Looks like xlrd doesn't support conditional formatting yet.

You can check out the error logs by passing verbosity=1 to open_workbook function.

rb=xlrd.open_workbook(MY_FILE_PATH, formatting_info=True, verbosity=1)

Alternatively, openpyxl seems to have support for "Conditional Formatting". Can check this package instead.

Murli
  • 728
  • 5
  • 15
  • Thank you for pointing out the way to check the error log. Now I see the log contains may entries of `*** WARNING: Ignoring CONDFMT (conditional formatting) record`. And since `openpyxl` doesn't seem to work with `xls` files, I guess I have to give up python and try to find other tools to do the job – tete Aug 09 '18 at 09:22