ooolib, reading writing a spread sheet and keep formatting

[News123]

Hi,

I'm making first attempts to modify a few cells of an openoffice
spreadsheet.

I thought I'll start with ooolib as it seems rather simple.

( linux / open office 3.1 / ooolib-python-0.0.16 )

My first test was just to open a spread sheet


import ooolib
doc = ooolib.Calc()
doc.load("doc1.ods") # load calc file
doc.set_sheet_index(0) # select first sheet
doc_dims = doc.get_sheet_dimensions() # get sheet dimensions
print "dims",doc_dims # and print them out
doc.save("doc2.ods") # save 'unmodfified' file


Extracting data works perfectly.

However the saved file lost all its formating and keeps only the data.

What am I doing wrong?

My goal is to just change one cell value, but to keep all the formatting
(at least of the other cells)


thanks for any hints


N

 

[Kushal Kumaran]

Hi,

I'm making first attempts to modify a few cells of an openoffice
spreadsheet.

Try the xlrd and xlwt modules, and the documentation at
http://www.python-excel.org/

 

[News123]

Try the xlrd and xlwt modules, and the documentation at
http://www.python-excel.org/

Thanks Kumar,

I installed xlrd, xlwt and xlsutils

Now I'm stuck with exactly the same probem, that I had with ooolib.
The newly written file lost all formating and replaced formulas with the
calculated values.


Does anyone have a simple example, which reads a file, modifies one cell
and writes out WITHOUT oss of formatting / formulas?


from xlrd import open_workbook
from xlutils.copy import copy

rb = open_workbook('doc1.xls')
print "WB with %d sheets" % rb.nsheets
wb = copy(rb)
wb.save("doc2.xls") # file is created, but ALL formattng is lost and
formulas are now diplayed as text

 

[News123]

Try the xlrd and xlwt modules, and the documentation at
http://www.python-excel.org/

Thanks Kumar,

I installed xlrd, xlwt and xlsutils

Now I'm stuck with exactly the same probem, that I had with ooolib.
The newly written file lost all formating and replaced formulas with the
calculated values.


Does anyone have a simple example, which reads a file, modifies one cell
and writes out WITHOUT oss of formatting / formulas?


from xlrd import open_workbook
from xlutils.copy import copy

rb = open_workbook('doc1.xls')
print "WB with %d sheets" % rb.nsheets
wb = copy(rb)
wb.save("doc2.xls") # file is created, but ALL formattng is lost and
formulas are now diplayed as text

 

[News123]

I installed xlrd, xlwt and xlsutils

Now I'm stuck with exactly the same probem, that I had with ooolib.
The newly written file lost all formating and replaced formulas with the
calculated values.

Well the xlsutils documentation states clearly:

It is important to note that some things won't be copied:
Formulae
•
Names
•
anything ignored by xlrd
•

So probably I can't use xlrd / xlwt to perform my desired task. :-(
(Or it least I don't know how)

For extracting data or creating xls from scratch t seems rather nice though


bye


N

 

[cjw]

Try the xlrd and xlwt modules, and the documentation at
http://www.python-excel.org/

The tutorial here is beautifully done:

http://python-excel.googlegroups.co...xpV4u3aa4iAIyYQIqbG9naPgh6o8ccLBvP6Chud5KMzIQ

Does it fly with .ods files?

Colin W.

 

[News123]

The tutorial here is beautifully done:

http://python-excel.googlegroups.co...xpV4u3aa4iAIyYQIqbG9naPgh6o8ccLBvP6Chud5KMzIQ

I agree, Reading the documentation you get very quickly an overview of
what can be done.

Does it fly with .ods files?

It works only with xls, but as OpenOffice can read and write xls this
wouldn't be a problem for me.

However:

I'd like to read in a spreadsheet, perform only minor modifications and
write it back with the exact formatting. this is unfortunately not working.




N

 

[Lie Ryan]

cjw wrote:
However:

I'd like to read in a spreadsheet, perform only minor modifications and
write it back with the exact formatting. this is unfortunately not working.

Do you know that Python is one of OpenOffice's macro language? Python
macro have the full access to UNO that OpenOffice itself uses internally.

 

[Chris Withers]

from xlrd import open_workbook
from xlutils.copy import copy

rb = open_workbook('doc1.xls')
open_workbook('doc1.xls',formatting_info=True)

print "WB with %d sheets" % rb.nsheets
wb = copy(rb)
wb.save("doc2.xls") # file is created, but ALL formattng is lost and
formulas are now diplayed as text

cheers,

Chris

 

[Chris Withers]

Hi Chris,




I'll try, but the doc mentioned explicitely, that formulas will be lost.
I'll keep you updated.

Yup, for them to be preserved, someone needs to either provide the
patches or stump up the cash to get the features implemented.

cheers,

Chris

 

[News123]

Hi Chris,

open_workbook('doc1.xls',formatting_info=True)

I'll try, but the doc mentioned explicitely, that formulas will be lost.
I'll keep you updated.

 

[News123]

Hi Chris,

open_workbook('doc1.xls',formatting_info=True)

I'll try, but the doc mentioned explicitely, that formulas will be lost.
I'll keep you updated.

 

[News123]

Hi Chris,

Yup, for them to be preserved, someone needs to either provide the
patches or stump up the cash to get the features implemented.

Thanks for your clarification.
You want to say, that there is a newer version or patches, which will
treat formulas?


Might be interestng.

Currently Iswitched to the uno module, which is a little bulky, but
generates for my case the correct docs on any system with OO installed.


N