Re: [pyxl] Writing to existing Excel file without overwriting existing charts, formulas etc.

[Chris Withers]

On 17/05/2013 16:18, Paul Blelloch wrote:
> It's not clear to me if I can use xlwt

You're looking for xlutils. Specifically xlutils.copy, but...

> to write data into an existing
> worksheet without modifying the existing formatting,, charts, or any
> other formulas that might be in the worksheet.

...xlrd doesn't support formulae and neither xlrd nor xlwt support
charts, so you're out of luck.

Sorry it's not better news,

Chris

PS: Try pandas and ipython, you may find you don't end up needing the
xls files at all, with luck ;-)

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[Paul Blelloch]

I am using pandas, but unfortunately I need to write to Excel just because that's the deliverable at the end of the day.  I don't know anything about how hard it is to write into an Excel worksheet without overwriting the entire sheet, but that's what I need to do.  I'm trying to move from Matlab to Python, and this is one of the major stumbling blocks that I'm running into.  The xlswrite function in Matlab just writes data into an existing worksheet if it exists, or creates a new sheet if it doesn't.  That means that I can set up a very complex workbook (usually a bunch of them) with all the formulas and charts that I need then use Matlab to dump in whatever data I have.  When I have a new set of results I simply rerun my Matlab code and all the Workbooks update.  The only workaround that I can think of from Python is to dump the data into either a separate Workbook or even a .csv file and then have my formatted Workbook link to the data Workbook.  That's a mess though, because I now have linked Workbooks, and my experience is that causes all kinds of problems with broken links when I try to move those Workbooks around.

Are there any plans to add something equivalent to the Matlab xlswrite function that writes data to a worksheet without wiping it out?

THANKS, Paul

On Friday, May 17, 2013 8:18:41 AM UTC-7, Paul Blelloch wrote:

It's not clear to me if I can use xlwt to write data into an existing worksheet without modifying the existing formatting,, charts, or any other formulas that might be in the worksheet.  I'm used to doing this using the xlswrite function in Matlab, but in my initial attempts to use xlwt it appears that it overwrites the original worksheet when I do a save.

[Tony Roberts]

Hi,

one thing you could try is using the microsoft access database engine to connect to an existing workbook file. It lets you read and modify data in a workbook as if it were a database using an odbc driver. I've used this in the past to do exactly what you're talking about (also using pandas). I setup a number of named ranges and then inserted data into those from pandas using sql statements.

These links should help get you started if you want to try this approach.

http://www.connectionstrings.com/excel

http://www.connectionstrings.com/providers/ace-oledb-12-0

cheers,

Tony

--
You received this message because you are subscribed to the Google Groups "python-excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to python-excel...@googlegroups.com.
To post to this group, send an email to python...@googlegroups.com.
Visit this group at http://groups.google.com/group/python-excel?hl=en-GB.
For more options, visit https://groups.google.com/groups/opt_out.