xlwt write() using xlrd XF objects

[Ervin Hegedüs]

Hello there,

I would like to extend an XLS file as I open and read it with xlrd,
then writes its contents with xlwt.
It must to reproduce all of that formats: width and height of cells,
alignments and so on.

But when I write the new file, it contains a new column as first
column, so I can't copy it with xlutils (or is it possible?)

I try to use this code:

wsheet.write(
row_index,
(col_index+1),
rsheet.cell(row_index, col_index).value,
rbook.xf_list[rsheet.cell(row_index, col_index).xf_index]
)

but it doesn't work.

(whseet is the destination, rsheet and rbook is the source)

What is the simplest way to do that?

Thank you:

a.

[John Machin]

On 10/08/2010 8:34 AM, Ervin Heged�s wrote:
> Hello there,

Hi Ervin,

>
> I would like to extend an XLS file as I open and read it with xlrd,
> then writes its contents with xlwt.
> It must to reproduce all of that formats: width and height of cells,
> alignments and so on.
>
> But when I write the new file, it contains a new column as first
> column, so I can't copy it with xlutils (or is it possible?)
>
> I try to use this code:
>
> wsheet.write(
> row_index,
> (col_index+1),
> rsheet.cell(row_index, col_index).value,
> rbook.xf_list[rsheet.cell(row_index, col_index).xf_index]
> )
>
> but it doesn't work.

That won't work because an xlrd XF object is not the same as an xlwt
XFStyle object.

Doing what you want to do is not possible at the moment. xlwt.filter
does most of the format preservation, but it appears to be
difficult/impossible to tell it that you want to insert columns, for
example.

One approach to this is to expose methods or module-level functions that
provide a conversion from xlrd objects to xlwt objects.

Note that "width of cells" is really "width of columns" and "height of
cells" is really "height of rows". Here I'm trialling preserving only
the XF info, not the column and row info.

I've uploaded a file getxlwtstylelist.py to the files area of the
google-group. This contains a function to extract a mapping from xlrd
xf_index to xlwt XFStyle object out of an xlrd Book instance. Its code
is about 95% of the xlutils.filter BaseWriter.workbook method. Below is
code to demonstrate its use:

=== script ervin_demo.py ===
import xlrd, xlwt
from getxlwtstylelist import get_xlwt_style_list

rbook = xlrd.open_workbook('ervin_demo_in.xls', formatting_info=1)
style_list = get_xlwt_style_list(rbook)

wbook = xlwt.Workbook(style_compression=2)
wsheet = wbook.add_sheet('ervin1')
rsheet = rbook.sheet_by_index(0)

def copy_block_of_cells(rd_sheet, wt_sheet, style_list, rlo=0, rhi=None,
rshift=0, clo=0, chi=None, cshift=0):
if rhi is None: rhi = rd_sheet.nrows
if chi is None: chi = rd_sheet.ncols
for row_index in xrange(rlo, rhi):
for col_index in xrange(clo, chi):
cell = rd_sheet.cell(row_index, col_index)
wt_sheet.write(
row_index + rshift,
col_index + cshift,
cell.value,
style_list[cell.xf_index],
)

copy_block_of_cells(rsheet, wsheet, style_list, cshift=1)

# insert code to write new first column

wbook.save('ervin_demo_out.xls')
=== end of script ===

Comments welcome ...

Cheers,
John

[Ervin Hegedüs]

Hello John,

fist, thank you for your help again.

On Wed, Aug 11, 2010 at 5:23 AM, John Machin <sjma...@lexicon.net> wrote:

> On 10/08/2010 8:34 AM, Ervin Hegedüs wrote:
>>
>> But when I write the new file, it contains a new column as first
>> column, so I can't copy it with xlutils (or is it possible?)
>>
>> I try to use this code:
>>
>> wsheet.write(
>>    row_index,
>>    (col_index+1),
>>    rsheet.cell(row_index, col_index).value,
>>    rbook.xf_list[rsheet.cell(row_index, col_index).xf_index]
>> )
>>
>> but it doesn't work.
>
> That won't work because an xlrd XF object is not the same as an xlwt XFStyle
> object.

yes, when I sent those mail I remembered those objects are different...

> Doing what you want to do is not possible at the moment. xlwt.filter does
> most of the format preservation, but it appears to be difficult/impossible
> to tell it that you want to insert columns, for example.

ok,

> One approach to this is to expose methods or module-level functions that
>  provide a conversion from xlrd objects to xlwt objects.

yes, I did it also :)

> Note that "width of cells" is really "width of columns" and "height of
> cells" is really "height of rows". Here I'm trialling preserving only the XF
> info, not the column and row info.

you're right, thanks,

> I've uploaded a file getxlwtstylelist.py to the files area of the
> google-group. This contains a function to extract a mapping from xlrd
> xf_index to xlwt XFStyle object out of an xlrd Book instance. Its code is
> about 95% of the xlutils.filter BaseWriter.workbook method. Below is code to
> demonstrate its use:

thank you for your script - as I wrote above I made it almost
likewise. I don't need most of attributes, I just copy'd what I need,
but the method is same.

> Comments welcome ...

I have no comments :), thank you.

Just a question: why is the limit for number of XF_Style? (sometimes
I've got the exception about this...)

thanks:

a.