copy of excel files and styles

[vav]

Hi!

I want to copy exiting excel file into new one and then change some
cells values. Problem is - style.
I don't want to change it. I want preserve style, that was copied.

suggestion:
I looked into xlwt/Row.py and have rewritten write function:

def write(self, col, label, style=Style.default_style):
self.__adjust_height(style)
style_index = self.__parent_wb.add_style(style)
self.write2(col, label, style_index)

here is the rest of original write funciton:
def write2(self, col, label, style_index):
self.__adjust_bound_col_idx(col)
if isinstance(label, basestring):
if len(label) > 0:
self.insert_cell(col,
StrCell(self.__idx, col, style_index,
self.__parent_wb.add_str(label))
)
else:
self.insert_cell(col, BlankCell(self.__idx, col,
style_index))
elif isinstance(label, bool): # bool is subclass of int; test
bool first
self.insert_cell(col, BooleanCell(self.__idx, col,
style_index, label))
elif isinstance(label, (float, int, long, Decimal)):
self.insert_cell(col, NumberCell(self.__idx, col,
style_index, label))
elif isinstance(label, (dt.datetime, dt.date, dt.time)):
date_number = self.__excel_date_dt(label)
self.insert_cell(col, NumberCell(self.__idx, col,
style_index, date_number))
elif label is None:
self.insert_cell(col, BlankCell(self.__idx, col,
style_index))
elif isinstance(label, ExcelFormula.Formula):
self.__parent_wb.add_sheet_reference(label)
self.insert_cell(col, FormulaCell(self.__idx, col,
style_index, label))
else:
raise Exception("Unexpected data type %r" % type(label))

this gives me ability to to write into cell without changing its
style.
Is there a better way to do it?

[vav]

Hi!

I forgot to mention how I would use this new function:

xf_index = -1
#get formating of a column!
if ws.cols.has_key(col):
xf_index = ws.cols[col]._xf_index
#get cell formating
if xf_index == -1:
if ws.rows.has_key(row) and ws.rows[row]._Row__cells.has_key(col):
xf_index = ws.rows[start]._Row__cells[col].xf_idx
if xf_index == -1:
ws.write(row, col, value)
else:
ws.rows[row].write2(col, value, xf_index)

So, as you see, I get index of style in illigal way, so, this part
will also require additional clean up.

[Chris Withers]

vav wrote:
> I want to copy exiting excel file into new one and then change some
> cells values.

xlutils.copy is your friend:

download:
http://pypi.python.org/pypi/xlutils

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

cheers,

Chris

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

[vav]

Hi Chris,

Thanks for your reply.

xlutils.copy is not my friend at stage of writing.
It was a friend, when I were coping original workbook to a new one.
And at that moment original formating was preserved.
But than I've used worksheet.write and formating was lost.

With my code I save original formating and use column formating if it
is exists. The only problem is that this function is not in a library.

[John Machin]

On 13/04/2010 6:50 AM, vav wrote:
> Hi!

Hi vav,

>
> I forgot to mention how I would use this new function:
>
> xf_index = -1
> #get formating of a column!
> if ws.cols.has_key(col):
> xf_index = ws.cols[col]._xf_index
> #get cell formating
> if xf_index == -1:
> if ws.rows.has_key(row) and ws.rows[row]._Row__cells.has_key(col):
> xf_index = ws.rows[start]._Row__cells[col].xf_idx

What is "start"? Don't you mean "row"?

> if xf_index == -1:
> ws.write(row, col, value)
> else:
> ws.rows[row].write2(col, value, xf_index)
>
> So, as you see, I get index of style in illigal way, so, this part
> will also require additional clean up.

You're not wrong about the need for cleanup.

``dict.has_key(key)`` was superseded by ``key in dict`` back in Python 2.2.

Secondly, you seem to be doing things backwards; should you not be
checking first whether the cell exists, and only going to the column
default when that fails? What about default row formatting?

Thirdly (and better than key in dict), as you intend to overwrite a
cell's value, it is reasonable to presume that the cell exists, so you
can use exceptions, for better speed and legibility.

I presume that what you'd really like is for xlwt to support a special
style argument that means "use the existing style if any"; correct?

In any case, please *discuss* ... trying to infer what you want from
scraps of unexplained code spread over two messages is somewhat
difficult ...

Cheers,
John

[vav]

Hi John,

>What is "start"? Don't you mean "row"?

Yes, it is. Sorry.

Thanks for tips of coding "the python way" I'll make a mental note
about 'key in dict'.

As for the second note - You are right, I should.

Third: For my task as well as in general cell could be empy (never had
a value)
I look at original excel file as a template with headers, formating,
etc. I take this template and fill it with values.

>I presume that what you'd really like is for xlwt to support a special
>style argument that means "use the existing style if any"; correct?

I think this is what I ment. But I would like to rephrase: 'keep
original formating'. So even if cell itself has no formating of its
own, use column formating if it is available.

Btw, I didn't find 'row formating' similar to 'column formating'. It
mean that I can't color every other line. Or I missed a spot?

Thanks
Andy.

[Chris Withers]

vav wrote:
> With my code I save original formating and use column formating if it
> is exists. The only problem is that this function is not in a library.

How about looking at patching xlwt so that it leaves an existing
formatting in place rather than doing your own thing?

[vav]

Hi Chris,

> How about looking at patching xlwt so that it leaves an existing
> formatting in place rather than doing your own thing?

As I understand, You suggest to me to implement John's version. I am
Ok with it.
I even agree to do it :-)

The only problem: I don't know how... yet.

Taking a pouse in discussion to figure out the deployment process.

Andrey.

[Yaroslav Ogloblin]

Good evening, Andrey.

Could you help me? I faced the same problem as you. I need to create xls files by template xls file. You could then, 4 years ago, to solve the problem? If so, how?

четверг, 15 апреля 2010 г., 0:04:33 UTC+3 пользователь vav написал: