Python writing (xlwt) to an existing Excel Sheet, drops charts and formatting
<p>Am using python to automate some tasks and ultimately write to an existing spreadsheet. Am using the xlwt, xlrd and xlutils modules. </p>

<p>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.</p>

<p>Thank you</p>

<p>Sample code</p>

<pre><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')
</code></pre>

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

<p>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 <a href="https://www.xlwings.org/" rel="nofollow"><strong>xlwings</strong></a>, which works on Windows or Mac.</p>

python excel xlrd xlwt xlutils