Python writing (xlwt) to an existing Excel Sheet, drops charts and formatting

Question

Am using python to automate some tasks and ultimately write to an existing spreadsheet. Am using the xlwt, xlrd and xlutils modules.

So the way I set it up is to open the file, make a copy, write to it and then save it back to the same file. When I do the last step, all excel formatting such as comments and charts are dropped. Is there a way around that? I think it has something to do with excel objects.

Thank you

Sample code

import xlwt
import os
import xlrd, xlutils
from xlrd import open_workbook
from xlutils.copy import copy

style1 = xlwt.easyxf('font: name Calibri, color-index black, bold off;   alignment : horizontal center', num_format_str ='###0')

script_dir = os.path.dirname('_file_')
Scn1 = os.path.join(script_dir, "\sample\Outlet.OUT")

WSM_1V = []
infile = open (Scn1, "r")
for line in infile.readlines(): 
WSM_1V.append(line [-10:-1]) 
infile.close()

Existing_xls = xlrd.open_workbook(r'\test\test2.xls', formatting_info=True, on_demand=True)
wb = xlutils.copy.copy(Existing_xls)
ws = wb.get_sheet(10)

for i,e in enumerate(WSM_1V,1):
   ws.write (i,0, float(e),style1)


wb.save('test2.xls')

http://stackoverflow.com/questions/3723793/preserving-styles-using-pythons-xlrd-xlwt-and-xlutils-copy — David Zemens, Oct 03 '16 at 20:55
@TimWilliams: In this case, it's not difficult to answer. Code is almost completely irrelevant. If you know the packages involved, you know that what OP is asking is simply not supported by those packages. — John Y, Oct 03 '16 at 21:04

John Y · Answer 1 · 2016-10-03T21:25:53.487

1

Using those packages, there is no way around losing the comments and charts, as well as many other workbook features. The xlrd package simply does not read them, and the xlwt package simply does not write them. xlutils is just a bridge between the other two packages; it can't read anything that xlrd can't read, and it can't write anything that xlwt can't write.

To achieve what you want to achieve, probably your best option is to automate a running instance of Excel; the best Python package for doing that is xlwings, which works on Windows or Mac.

edited Oct 03 '16 at 21:25

answered Oct 03 '16 at 21:13

John Y

14,123
2
48
72

Thanks for your insight, that's what I suspected but wasn't sure. Will give xlwings a try. – HAMIN Oct 04 '16 at 14:05

score 0 · Answer 2 · answered Oct 03 '16 at 21:13

Could you do this with win32com?

from win32com import client

...

xl = client.Dispatch("Excel.Application")
wb = xl.Workbooks.Open(r'\test\test2.xls')
ws = wb.Worksheets[10]

for i,e in enumerate(WSM_1V,1):
   ws.Cells[i][0].Value = float(e)


wb.save
wb.Close()
xl.Quit()
xl = None

Python writing (xlwt) to an existing Excel Sheet, drops charts and formatting

2 Answers2