Can't Update an Excel file and keep cells format.

[Ptistek]

Hi,
I'd have to update an existing excel sheet which contains cells with
different background,font colour, font size etc...

Here are few info I got from the web.
From web page https://secure.simplistix.co.uk/svn/xlutils/trunk/xlutils/docs/styles.txt
:
Once you have a Book object, you can extract the relevent style
information from it as follows:

>>> from xlutils.styles import Styles
>>> s = Styles(book)

You can now look up style information about any cell:

>>> sheet = book.sheet_by_name('Sheet1')

A1_style = s[sheet.cell(0,0)]

Most of the interesting information is in the font definition for the
style:

>>> A1_font = book.font_list[A1_style.xf.font_index]

I also took a look to http://www.simplistix.co.uk/presentations/python_excel_08/python_excel_08.pdf
especially page 4/7 ...
*******************************
Here is part of my code

So i first start to read my book with
>>book = xlrd.open_workbook(filename = options.inputfile[0], formatting_info = True)

then my sheet
>>sh = book.sheet_by_index(0)

then I extract the relevent style information as explained on the
webpage mentionned above
>>s = Styles(book)

I would like to save my updated sheet to a new sheet in a new book .
(Or is there a way to only update existing sheet ?)

>>NewDoc = xlwt.Workbook()
>>Newsheet = NewDoc.add_sheet("UpdatedSheet")

I'll proceed for following line in a loop for all cells, but here is
the example for 1 cell :
At **comment I noticed that CellStyle instance has only attribute
"name" and "xf"

>>CellStyle = xlwt.XFStyle()
>>Cellfont = xlwt.Font()
>>CellStyle = s[sh.cell(0,0)]
>>Cellfont = book.font_list[CellStyle.xf.font_index]
**comment
>>CellStyle.font = Cellfont
>>Newsheet.write( 0, 0, "TST", CellStyle )

At this point i get following error :
Traceback (most recent call last):
File "<pyshell#24>", line 1, in <module>
Newsheet.write( 0, 0, "TST", CellStyle )
File "C:\Python25\lib\site-packages\xlwt\Worksheet.py", line 992, in
write
self.row(r).write(c, label, style)
File "C:\Python25\lib\site-packages\xlwt\Row.py", line 211, in write
style_index = self.__parent_wb.add_style(style)
File "C:\Python25\lib\site-packages\xlwt\Workbook.py", line 293, in
add_style
return self.__styles.add(style)
File "C:\Python25\lib\site-packages\xlwt\Style.py", line 90, in add
return self._add_style(style)[1]
File "C:\Python25\lib\site-packages\xlwt\Style.py", line 93, in
_add_style
num_format_str = style.num_format_str
AttributeError: CellStyle instance has no attribute 'num_format_str'

Does somebody see what i'm doing wrong, or what i forget and could
explain it to me ?
thanks for your time

[Chris Withers]

Ptistek wrote:
> From web page https://secure.simplistix.co.uk/svn/xlutils/trunk/xlutils/docs/styles.txt
> :

Funny how you found the above but not this:

https://secure.simplistix.co.uk/svn/xlutils/trunk/xlutils/docs/save.txt

> Does somebody see what i'm doing wrong, or what i forget and could
> explain it to me ?

I'm afraid you haven't really explained what it is that you're trying to
do...

My guess is that you want to do something like the following, which will
make John M frown, until xlwt supports writing to cells more than once:

from xlrd import open_workbook
from xlutils.save import save
wb = open_workbook(os.path.join(test_files,'testall.xls'))
sheet = wb.sheet_by_name('your sheet')
cell = sheet.cell(0,0)
sheet.put_cell(0,0,cell.ctype,'Changed',cell.xf_index)
save(wb,'new.xls')

However, if you tell us what it is that you're trying to do, we may be
able to advise of a better way.

cheers,

Chris

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

[Ptistek]

Hi,
Thanks for you quick answer.

Hopefully following examples give more light to my previous
explanation.

I have as input.
http://spreadsheets.google.com/ccc?key=pVoZTP8S9wyZMqM4JWPOyWQ

After some checking I'd update col E with " X "
=> I would
http://spreadsheets.google.com/ccc?key=pVoZTP8S9wyZ90XGgLYPGIw&t=5467221215420085562&guest

And I get
http://spreadsheets.google.com/ccc?key=pVoZTP8S9wyZa1DvTNyUYBQ
As you can see cells width, background etc.. aren't kept.

Rgds
Raphael

On Nov 24, 10:34 am, Chris Withers <ch...@simplistix.co.uk> wrote:
> Ptistek wrote:
> > From web pagehttps://secure.simplistix.co.uk/svn/xlutils/trunk/xlutils/docs/styles...
> > :

[Chris Withers]

Ptistek wrote:
> I have as input.
> http://spreadsheets.google.com/ccc?key=pVoZTP8S9wyZMqM4JWPOyWQ
>
> After some checking I'd update col E with " X "
> => I would
> http://spreadsheets.google.com/ccc?key=pVoZTP8S9wyZ90XGgLYPGIw&t=5467221215420085562&guest
>
> And I get
> http://spreadsheets.google.com/ccc?key=pVoZTP8S9wyZa1DvTNyUYBQ
> As you can see cells width, background etc.. aren't kept.

None of the above is helpful given that I don't have access to any of
those spreadsheets.

It's even less helpful given that you haven't included the code you're
trying to run and don't appear to have done anything with the
suggestions I've provided.

[Ptistek]

Hi,
Sorry for my links.
I've tried with a colleague and It worked..... anyway

Yes I have try your suggestion. And result is same as what i got
earlier :
so Content of cells is correctly updated.
But format,size,background color of initial cell is lost.

Here is my code :
def main():
parser = OptionParser()
parser.add_option("-i", "--inputfile".....)
parser.add_option("-u", "--UsedIDfile"........)
parser.add_option("-c", "--Crosscolumn"........)

(options, args) = parser.parse_args()

if (options.UsedIDfile == None):
parser.print_help()
return 2

if (options.inputfile == None):
parser.print_help()
return 2
if (options.crosscolumn == None):
parser.print_help()
return 2

################################################################################################
# Get Used IDs
################################################################################################
f = open(options.UsedIDfile[0], 'r')
UsedIds = f.readlines()
########################################################################

book = xlrd.open_workbook(filename = options.inputfile[0],
formatting_info = True)

if (book.nsheets != 1):
error_out("excel file \"%s\"should have only one
sheet" % options.inputfile[0])

#get All the data from the sheet
sh = book.sheet_by_index(0)
inputlines = [ [ item.strip() for item in sh.row_values(x) ] for x
in xrange(sh.nrows) ]


#get Sheet names
sheetNames = book.sheet_names()

s = Styles(book)


####################################################################################################
# Compare USed IDs with Excel Sheet IDs and put cross in column:
options.crosscolumn
####################################################################################################

lines = []

for line in inputlines:
if line[0] == '':
break
else:
for UsedId in UsedIds:
if line[options.crosscolumn] == ' ':
if line[0]== UsedtId:
line[options.crosscolumn]='x'

lines.append(line)


################################################################################################
# Save New file to input file
#
# !! Here comes my problem !!
#
################################################################################################

#Open new workbook
NewDoc = xlwt.Workbook()
#Add a New worksheet
Newsheet = NewDoc.add_sheet(sheetNames[0])

##****

CellStyle = xlwt.XFStyle()
Cellfont = xlwt.Font()

for row_num, row_values in enumerate( lines ):
for col, value in enumerate( row_values ):
CellStyle = s[sh.cell(row_num,col)]
Cellfont = book.font_list
[CellStyle.xf.font_index]
CellStyle.font = Cellfont
Newsheet.write( row_num, col, value,
CellStyle )
NewDoc.save(options.inputfile[0])
##****

## For Trial I replaced without more success ##*** code with the
code from your post (updated with my needs)

## for row_num, row_values in enumerate( lines ):
## for col, value in enumerate( row_values ):
## cell = sheet.cell(row_num,row_values)
## sheet.put_cell
(row_num,row_values,cell.ctype, value, cell.xf_index)
##
## save(wb,options.inputfile[0])

Raphael

On Nov 24, 11:31 am, Chris Withers <ch...@simplistix.co.uk> wrote:
> Ptistek wrote:
> > I have as input.
> >http://spreadsheets.google.com/ccc?key=pVoZTP8S9wyZMqM4JWPOyWQ
>
> > After some checking I'd update col E with " X "
> > => I would

> >http://spreadsheets.google.com/ccc?key=pVoZTP8S9wyZ90XGgLYPGIw&t=5467...

[Ptistek]

Hi Again,
I tested more with the code you gave me.
It now works
Instead of

wb = open_workbook(os.path.join(test_files,'testall.xls'))

I did
wb = xlrd.open_workbook(****, formatting_info = True)

And then As you proposed me.

sheet = wb.sheet_by_name('your sheet')
cell = sheet.cell(0,0)
sheet.put_cell(0,0,cell.ctype,'Changed',cell.xf_index)
save(wb,'new.xls')

Thanks for you help and your time.
Rgds
Raphael

[John Machin]

On 24/11/2008 22:01, Ptistek wrote:
> Hi,
> Sorry for my links.
> I've tried with a colleague and It worked..... anyway
>
> Yes I have try your suggestion. And result is same as what i got
> earlier :
> so Content of cells is correctly updated.
> But format,size,background color of initial cell is lost.
>
> Here is my code :

Raphael: Not all of it, you've omitted the imports.

> def main():
> parser = OptionParser()

[big snip]

>
> s = Styles(book)

This "Styles" is evidently obtained by
from xlutils.styles import Styles
These droids are not the ones that you are looking for.

Chris, I strongly suggest that xlutils.styles get some documentation
that tells folk exactly what it does, and emphasises that an
xlutils.styles.CellStyle object corresponds to an Excel STYLE record
(which would have been better named STYLENAME or NAMEDSTYLE) whereas an
xlwt.Style.XFStyle object corresponds to an Excel XF record (which would
have better been called STYLE).

[another big snip]

>
> #Open new workbook
> NewDoc = xlwt.Workbook()
> #Add a New worksheet
> Newsheet = NewDoc.add_sheet(sheetNames[0])
>
> ##****
> CellStyle = xlwt.XFStyle()
> Cellfont = xlwt.Font()

The above two statements are redundant; the values are replaced below.

>
> for row_num, row_values in enumerate( lines ):
> for col, value in enumerate( row_values ):
> CellStyle = s[sh.cell(row_num,col)]

The meaningfully named s being of course a collection of
xlutils.styles.CellStyle objects ...

> Cellfont = book.font_list
> [CellStyle.xf.font_index]
> CellStyle.font = Cellfont

CellStyle is an xlutils.styles.CellStyle object but is being poked as
though it were an xlwt.Style.XFStyle object.

Aside: Changing the contents of an XFStyle object after it has been used
in a Worksheet.write() or similar will achieve the desired effect only
rarely and accidentally. All cells written with that object will be
formatted with the *same* bunch of values, namely what's in the object
at the time that Workbook.save() is called ... or possibly a striped
effect if you are using Sheet.flush_row_data().

> Newsheet.write( row_num, col, value,
> CellStyle )

... and Cellstyle has been given only 1 (font) of the 6 attributes
expected in an xlwt.Style.XFStyle object so (as mentioned on Raphael's
first message) it falls over with:

AttributeError: CellStyle instance has no attribute 'num_format_str'

HTH,
John

[Ptistek]

Thanks for all those Helpfull clarifications.
On my side it now works as I expected.

[Chris Withers]

John Machin wrote:
> Chris, I strongly suggest that xlutils.styles get some documentation
> that tells folk exactly what it does, and emphasises that an
> xlutils.styles.CellStyle object corresponds to an Excel STYLE record
> (which would have been better named STYLENAME or NAMEDSTYLE) whereas an
> xlwt.Style.XFStyle object corresponds to an Excel XF record (which would
> have better been called STYLE).

Done:
https://secure.simplistix.co.uk/svn/xlutils/trunk/xlutils/docs/styles.txt

cheers,