problems with xlutils.copy
bimbogino
I have to generate an excel document using an existing file as a
"model".
I made a test with xlutils.copy but I had problems:
1 - the model has a cell with a green background: the background in
the copy is not the same tint of green of the original.
2 - when I write in a cell in the copy, the original cell style is
lost.
This is my source code:
#!/usr/bin/env python
from xlrd import open_workbook
from xlutils.copy import copy
wb = open_workbook('model.xls', formatting_info=1)
cwb = copy(wb)
sh = cwb.get_sheet(0)
for r in range(10):
sh.row(3+r).write(0, r+1)
sh.row(3+r).write(1, "row %d" % (r+1))
cwb.save('output.xls')
thank for your help
Chris Withers
> 1 - the model has a cell with a green background: the background in
> the copy is not the same tint of green of the original.
That's interesting. If you can post a 1-cell spreadsheet demonstrating
this, we can take a look...
> 2 - when I write in a cell in the copy, the original cell style is
> lost.
Yes, when you write to a cell, the formatting *and* value are
overwritten. It would be nice to be able to have the default value to
the style parameter of the write methods mean "leave as currently is"
rather than "use the default style"... I suspect this change would need
funding...
In the meantime, just create a new style that matches the existing one
using easy_xf and you'll be fine. I'd suggest reading the section on
writing excel files with xlwt in the tutorial on
http://www.python-excel.org.
cheers,
Chris
--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk
Nick
Python part as I discovered that
the same xls file looks different in modern Excel and OOo scalc. As I
understand this issue is about
Excel "themed" colors - when setting non standard color (associated
with current excel color theme)
to cell, back- or foreground, we can see it in Excel only. OOo scalc
shows only "standard" 56 colors,
it's obvious that scalc doesn't support color themes and though uses
closes match.
At least I believe it does this way. :-)
The next question I'd like to raise is about xlutils.copy and page
margins/headers/footers. When I'm copying
workbook with xlutils.copy I expect to see it exactly the same as
original also in print preview, but instead
there are new default headers, new margins and what is really
unexpected - the data is center-aligned on
page (left aligned in original doc). Fixing all this by hand is a bit
of pain and in result I still don't have exactly_the_same
look in print preview. Is there any plans for xlutils.copy to copy all
that in the future?
And the same question about formulas - can we expect xlutils.copy to
copy formulas instead of calculated values as well?
Thanks!
Chris Withers
> The next question I'd like to raise is about xlutils.copy and page
> margins/headers/footers. When I'm copying
> workbook with xlutils.copy I expect to see it exactly the same as
> original also in print preview, but instead
> there are new default headers, new margins and what is really
> unexpected - the data is center-aligned on
> page (left aligned in original doc). Fixing all this by hand is a bit
> of pain and in result I still don't have exactly_the_same
> look in print preview. Is there any plans for xlutils.copy to copy all
> that in the future?
Does xlrd read these attributes? Can xlwt write them? If the answer to
both is yes, then it's an xlutils feature request and you need to bug
(and preferably find funding for) me.
If the answer to either is "no", then they need to be working first.
Bugging John, or failing that me, would be needed...
> And the same question about formulas - can we expect xlutils.copy to
> copy formulas instead of calculated values as well?
That's a definite "not until xlrd supports reading them"... I believe
John has some experimental code for this, but I don't know how far it is
from being ready for the prime time...