def getSheet( file, sheetIndex ):
doc = opendocument.load( file )
try:
spreadsheet = doc.spreadsheet
except NameError:
sys.stderr.write("Error: file is not a spreadsheet\n")
return (None, None)
sheets = spreadsheet.getElementsByType( Table )
if sheetIndex > len( sheets ):
sys.stderr.write( "Error: spreadsheet has only %d sheets; requested invalid sheet %d\n" % (len( sheets ), sheetIndex + 1) )
return (None, None)
sheet = sheets[sheetIndex]
return (doc, sheet)
def load(self, filename):
""" load a table in ods format """
# read the file
doc = load(filename)
# get table style
self.table.direction = "ltr"
try:
stylename = doc.getElementsByType(Table)[0].getAttribute("stylename")
style = doc.getStyleByName(stylename)
tps = style.getElementsByType(TableProperties)
for tp in tps:
if tp.getAttribute("writingmode") == "rl-tb":
self.table.direction = "rtl"
except:
pass
# loop on all rows and columns
i = 0
for row in doc.getElementsByType(TableRow):
j = 1
i += 1
for cell in row.getElementsByType(TableCell):
# creat a new cell
c = SodsCell()
try:
numbercolumnsrepeated = int(cell.getAttribute("numbercolumnsrepeated"))
except:
numbercolumnsrepeated = 1
c.text = self.getCellText(cell)
c.value_type = cell.getAttribute("valuetype")
# FIXME: no percentage support, convert to float
if c.value_type == "percentage":
c.value_type = "float"
c.formula = cell.getAttribute("formula")
if c.formula:
c.formula = self.cleanFormula(c.formula[3:])
c.date_value = cell.getAttribute("datevalue")
c.value = cell.getAttribute("value")
stylename = cell.getAttribute("stylename")
if stylename:
style = doc.getStyleByName(stylename)
# parse data style
# this only work on patch'd version of odfpy
# file:opendocument.py, function:build_caches
# -if element.qname == (STYLENS, u'style')):
# +if element.qname in ((STYLENS, u'style'), (NUMBERNS,u'number-style')):
datastylename = style.getAttribute("datastylename")
if datastylename:
datastyle = doc.getStyleByName(datastylename)
if datastyle:
p = datastyle.getElementsByType(Number)[0]
if p:
decimalplaces = p.getAttribute("decimalplaces")
grouping = p.getAttribute("grouping")
if decimalplaces == "2" and grouping == "true":
c.format = "#,##0.00"
else:
c.format = ""
for p in style.getElementsByType(TextProperties):
c.font_family = p.getAttribute("fontfamily")
if not c.font_family:
c.font_family = "Arial"
c.font_size = self.translateToPt(p.getAttribute("fontsize"))
c.color = p.getAttribute("color")
if not c.color:
c.color = "#000000"
for p in style.getElementsByType(TableCellProperties):
c.background_color = p.getAttribute("backgroundcolor")
if not c.background_color or c.background_color == "transparent":
c.background_color = "default"
border = self.translateBorderToPt(p.getAttribute("border"))
if border == "none":
c.border_top = self.translateBorderToPt(p.getAttribute("bordertop"))
c.border_bottom = self.translateBorderToPt(p.getAttribute("borderbottom"))
c.border_left = self.translateBorderToPt(p.getAttribute("borderleft"))
c.border_right = self.translateBorderToPt(p.getAttribute("borderright"))
else:
c.border_top = border
c.border_bottom = border
c.border_left = border
c.border_right = border
for p in style.getElementsByType(Map):
c.condition = p.getAttribute("condition")
if c.condition:
c.condition = self.cleanFormula(c.condition)
applystylename = p.getAttribute("applystylename")
applystyle = doc.getStyleByName(applystylename)
for cp in applystyle.getElementsByType(TextProperties):
c.condition_color = cp.getAttribute("color")
for cp in applystyle.getElementsByType(TableCellProperties):
c.condition_background_color = cp.getAttribute("backgroundcolor")
# check for sodsOds formulas (starting with !)
if len(c.text) > 0 and c.text[0] == "!":
c.formula = c.text
c.value_type = "float"
# insert cell to table
while numbercolumnsrepeated > 0:
self.table.setCellAt(i, j, c)
j += 1
numbercolumnsrepeated -= 1