xlwt & xlcopy - not full copy?

[Gregory Guthrie]

I was planning (hoping) to be able to update a spreadsheet by opening for read, doing a copy, and then writing (updating) the new data cells and saving.

However I just read in the Simplistix Excel-Python document that xlcopy does not copy formulae or names. This is a significant flaw for me, since it would seem to collapse all the calculation structure from the spreadsheet.

Am I missing something here? or is there another better approach?

I would suppose (guess?) that one could manually read and copy forumlae and all, but I had presumed that was what xlcopy woudl do.

Thanks for any/all clarification (sorry for still not understanding what seems like an FAQ!).

[Chris Withers]

On 05/01/2013 04:18, Gregory Guthrie wrote:
> However I just read in the Simplistix Excel-Python document that xlcopy
> does not copy formulae or names. This is a significant flaw for me,
> since it would seem to collapse all the calculation structure from the
> spreadsheet.
>
> Am I missing something here? or is there another better approach?

The problem, as I remember it, is that xlrd doesn't read formulae or
names. As a result, xlutils.copy has nothing to copy.

cheers,

Chris

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

[Chris Withers]

On 07/01/2013 13:50, Gregory Guthrie wrote:
> Thanks.
> It certainly seems to be true, but limiting. What you get is not a copy of a spreadsheet, but an unformatted flat copy of the data in it.

That's not true, unless you're opening a .xlsx.

In it's default form, xlutils.copy will copy formatting, but not
formulae with .xls files...

[Gregory Guthrie]

Yes, thanks - I was using .xlsx which I think is the current standard format for excel.

[John Yeung]

On Fri, Jan 4, 2013 at 11:18 PM, Gregory Guthrie <grgu...@gmail.com> wrote:
> I just read in the Simplistix Excel-Python document that xlcopy does
> not copy formulae or names. This is a significant flaw for me, since it
> would seem to collapse all the calculation structure from the spreadsheet.
>
> Am I missing something here? or is there another better approach?

If you have to preserve formulas, right now xlrd isn't going to cut
it. You will be better off using Microsoft's programming tools (such
VBScript) to manipulate a running instance of Excel. If you must
stick to Python, try pywin32, which allows something similar to the
VBScript route, but with Python instead of Visual Basic.

One thing to understand is that it's not as easy as you might think to
extract formulas from an Excel file. Here are some links which may
illuminate matters. Note that these all pertain to .xls, and some of
the issues might not still apply to .xlsx (though I imagine many or
most still do).

http://stackoverflow.com/questions/4690423/get-formula-from-excel-cell-with-python-xlrd
https://groups.google.com/forum/?fromgroups=#!topic/python-excel/ZS5PsC5A6iQ
https://groups.google.com/forum/?fromgroups=#!topic/python-excel/CV0z13A1hbc

John Y.

[Gregory Guthrie]

Thanks for the info & Refs.

I would think that all of this would be easier now with the newer formats which have everything in XML.
However the real complexity is in the details, and certainly the creators & maintainers of these packages are the real experts!

Still disappointed though, it would have made my task easier!  :-)

[Chris Withers]

On 08/01/2013 02:51, Gregory Guthrie wrote:
>
> Still disappointed though, it would have made my task easier! :-)

You can always offer to fund the work needed to make things behave as
you expect ;-)