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).
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.