def export(self, description, rows, options): import ooolib doc = ooolib.Calc() doc.set_meta('title', _(u"CrunchyFrog Data Export")) doc.set_meta('description', options.get("query", "")) doc.set_meta('creator', pwd.getpwuid(os.getuid())[4].split(",", 1)[0]) doc.set_cell_property('bold', True) for i in range(len(description)): doc.set_cell_value(i + 1, 1, "string", description[i][0]) doc.set_cell_property('bold', False) for i in range(len(rows)): for j in range(len(rows[i])): value = rows[i][j] if value == None: continue if type(value) == types.FloatType: otype = "float" elif type(value) == types.IntType: otype = "int" elif type(value) == types.BooleanType: otype = "boolean" else: otype = "string" doc.set_cell_value(j + 1, i + 2, otype, value) doc.save(options["filename"])
def test_cell_border(self): # create odt document thin_value = '0.001in solid #ff0000' bold_value = '0.01in solid #00ff00' filename = os.path.join(self.dirname, "test.ods") doc = ooolib.Calc("test_cell") doc.set_cell_property('border', thin_value) doc.set_cell_value(2, 2, "string", "Name") doc.set_cell_property('border', bold_value) doc.set_cell_value(2, 4, "string", "Value") doc.save(filename) # check created document handle = zipfile.ZipFile(filename) xdoc = etree.parse(BufferIO(handle.read('content.xml'))) style_definition = xdoc.xpath( '//style:table-cell-properties[@fo:border="%s"]/../@style:name' % thin_value, namespaces=self.namespaces) cell_style = xdoc.xpath('//text:p[text()="Name"]/../@table:style-name', namespaces=self.namespaces) self.assertEqual(cell_style, style_definition) style_definition = xdoc.xpath( '//style:table-cell-properties[@fo:border="%s"]/../@style:name' % bold_value, namespaces=self.namespaces) cell_style = xdoc.xpath( '//text:p[text()="Value"]/../@table:style-name', namespaces=self.namespaces) self.assertEqual(cell_style, style_definition)
def test_default_cell_formula_value(self): doc = ooolib.Calc("test_cell") doc.set_cell_value(1, 1, "float", 1) doc.set_cell_value(2, 1, "float", 2) doc.set_cell_value(3, 1, "formula", "=A1+B1") self.assertEqual(doc.sheets[0].sheet_values[(3, 1)]['formula_value'], "0")
def _gen_table_from_ods(tb_fname, vhdl_file): doc = ooolib.Calc(opendoc=tb_fname) doc.set_sheet_index(0) (cols, rows) = doc.get_sheet_dimensions() f = open(vhdl_file, 'r') buf = f.read() f.close() vf = vhdlfile.vhdlfile(buf) pdic = vf.get_ports_dic() vect_dic = {} vect_mult = [] vsize = 0 clk_source = _get_clock_sources(buf, pdic) #Get Vector Size for port in pdic: if pdic[port][1] in ("in", "IN") and port not in clk_source: vsize += pdic[port][2] #Get mult for port in pdic: if pdic[port][1] in ("in", "IN") and port not in clk_source: low = vsize - 1 - (pdic[port][2] - 1) vect_dic[port] = (vsize - 1, low) vect_mult.append(pow(2, low)) vsize = low #Get Data from spreadsheet an save to text file f = open(tb_fname.split(".")[0] + ".tbl", "w") for row in range(2, rows + 1): data = 0 for col in range(1, cols + 1): intr = doc.get_cell_value(col, row) data += int(intr[1]) * vect_mult[col - 1] f.write( ` data ` + "\n") f.close()
def __init__(self, datafile, sheet_index=0, skip_lines=None): if not has_ooolib: raise NotImplementedError("The ooolib library is not available") super(ODSImportedFile, self).__init__(datafile, sheet_index, skip_lines) book = ooolib.Calc(opendoc=self.file_path) book.set_sheet_index(sheet_index) self.current_sheet = book (self._ncols, self._nrows) = self.current_sheet.get_sheet_dimensions()
def test_clean_formula_apos(self): doc = ooolib.Calc('Žluťoučký kůň') sheet = doc.sheets[0] data = sheet.clean_formula( "=UNICODE('Žluťoučký kůň příšerně úpěl ďábelské ódy.')") self.assertEqual( data, "oooc:=UNICODE('Žluťoučký kůň příšerně úpěl ďábelské ódy.')" )
def test_clean_formula_quot(self): doc = ooolib.Calc('Žluťoučký kůň') sheet = doc.sheets[0] data = sheet.clean_formula( '=UNICODE("Žluťoučký kůň příšerně úpěl ďábelské ódy.")') self.assertEqual( data, "oooc:=UNICODE("Žluťoučký kůň příšerně úpěl ďábelské ódy.")" )
def _ods_file(vhdl_file, recreate_file=False): tb_name = vhdl_file.split(".")[0] if os.path.isfile(tb_name + ".ods") and recreate_file == False: _gen_table_from_ods(tb_name + ".ods", vhdl_file) else: f = open(vhdl_file, 'r') buf = f.read() f.close() vf = vhdlfile.vhdlfile(buf) modname = vf.get_name() pdic = vf.get_ports_dic() cname = [] ctype = [] doc = ooolib.Calc("TB") i = 1 clk_source = _get_clock_sources(buf, pdic) for port in pdic: if pdic[port][1] in ("in", "IN") and port not in clk_source: doc.set_cell_value(i, 1, "string", port + '(' + ` pdic[port][2] ` + ')') i += 1 #Get Vector Size genclk, period, clk_dic = _get_genclk(clk_source) vsize = 0 for port in pdic: if pdic[port][1] in ("in", "IN") and port not in clk_source: vsize += pdic[port][2] tb_f = tb_file.replace("<Name>", "tb_" + modname) tb_f = tb_f.replace("<max>", ` vsize `) tb_f = tb_f.replace("<max-1>", ` vsize - 1 `) component = vf.get_component_def() signals = vf.gen_signals_code(clk_dic) instance = vf.gen_instance("UTT0") tb_f = tb_f.replace("<component>", component) tb_f = tb_f.replace("<signals>", signals + period) tb_f = tb_f.replace("<instance>", instance) tb_f = tb_f.replace("<file_name>", tb_name + ".tbl") tb_f = tb_f.replace("<clock_gen>", genclk) tb_f = tb_f.replace("<PERIOD>", "100 ns") data = "" for port in pdic: if pdic[port][1] in ("in", "IN") and port not in clk_source: low = vsize - 1 - (pdic[port][2] - 1) if vsize - 1 == low: data += "\t" + port + " <= data_s(" + ` low ` + ");\n" else: data += "\t" + port + " <= data_s(" + ` vsize - 1 ` + " downto " + ` low ` + ");\n" vsize = low tb_f = tb_f.replace("<data>", data) f = open("tb_" + tb_name + ".vhd", 'w') f.write(tb_f) f.close() doc.save(tb_name + ".ods") print "Edit the Test Bench Source File and rerun this command"
def create(self, tables, pdic): doc = ooolib.Calc() sheets = [] for table in tables: sheet = ooolib.CalcSheet(table) i = 1 for port in tables[table]: sheet.set_sheet_value((i, 1), "string", port + '[' + ` pdic[port][2] ` + ']') i += 1 sheets.append(sheet) doc.sheets = sheets doc.save(self.file_name)
def save_ods(): # Create your document doc = ooolib.Calc() col = 1 row = 2 doc.set_cell_property('bold', True) doc.set_row_property(row, 'height', '16.5pt') for heading, width in [ ('Country', '73pt'), ('Operator', '77pt'), ('Type of measure*', '355pt'), ('', '355pt'), ('Description of the measure', '148pt'), ('Objective', ''), ('Method of implementation (if applicable)', ''), ('Number of subscribers having a subscription where this measure is implemented', ''), ('How is the user informed?', '148pt'), ('Can the user activate/deactivate the measure? How?', '148pt'), ('Protection of business secret', '239pt') ]: if width: doc.set_column_property(col, 'width', width) doc.set_cell_value(col, row, "string", heading) col += 1 doc.set_cell_property('bold', False) row = 3 for v in Violation.objects.filter(activationid='').exclude( state__in=['closed', 'ooscope', 'duplicate']).annotate( total=Count('confirmation')): if v.total > 0 or v.state == 'verified': doc.set_row_property(row, 'height', '16.5pt') doc.set_cell_property('wrap-option', 'wrap') doc.set_cell_value(1, row, "string", v.country) doc.set_cell_value(2, row, "string", v.operator) doc.set_cell_value(3, row, "string", "%s %s" % (v.type, v.resource_name)) doc.set_cell_value( 5, row, "string", "%s\n\n%s" % (v.editorial, unescape(v.comment_set.get().comment))) doc.set_cell_value( 9, row, "string", "%s %s" % ("Contractual" if v.contractual else "", unescape(v.contract_excerpt))) doc.set_cell_value( 10, row, "string", "can update to a different dataplan" if v.loophole else "") doc.set_cell_value(12, row, "string", v.media) row += 1 #(v.state, v.country, v.operator, v.contract, v.resource, v.resource_name, v.type, v.media, v.temporary, v.contractual, v.contract_excerpt, v.loophole, v.editorial,v.comment_set.get().comment) # Save the document to the file you want to create doc.save("/tmp/ec_berec_tm_questionnaire.ods")
def test_cell_padding(self): # create odt document filename = os.path.join(self.dirname, "test.ods") doc = ooolib.Calc("test_cell") doc.set_cell_property('padding-left', '0.1in') doc.set_cell_value(2, 2, "string", "Left") doc.set_cell_property('padding-left', False) doc.set_cell_property('padding-right', '0.2in') doc.set_cell_value(3, 2, "string", "Right") doc.set_cell_property('padding-right', False) doc.set_cell_property('padding', '0.3in') doc.set_cell_value(4, 2, "string", "Full") doc.set_cell_property('padding', False) doc.set_cell_value(5, 2, "string", "No-padding") doc.save(filename) # check created document handle = zipfile.ZipFile(filename) xdoc = etree.parse(BufferIO(handle.read('content.xml'))) style_definition = xdoc.xpath( '//style:table-cell-properties[@fo:padding-left="0.1in"]/../@style:name', namespaces=self.namespaces) cell_style = xdoc.xpath('//text:p[text()="Left"]/../@table:style-name', namespaces=self.namespaces) self.assertEqual(cell_style, style_definition) style_definition = xdoc.xpath( '//style:table-cell-properties[@fo:padding-right="0.2in"]/../@style:name', namespaces=self.namespaces) cell_style = xdoc.xpath( '//text:p[text()="Right"]/../@table:style-name', namespaces=self.namespaces) self.assertEqual(cell_style, style_definition) style_definition = xdoc.xpath( '//style:table-cell-properties[@fo:padding="0.3in"]/../@style:name', namespaces=self.namespaces) cell_style = xdoc.xpath('//text:p[text()="Full"]/../@table:style-name', namespaces=self.namespaces) self.assertEqual(cell_style, style_definition) cell_style = xdoc.xpath( '//text:p[text()="No-padding"]/../@table:style-name', namespaces=self.namespaces) self.assertEqual(cell_style, [])
def ODSDictReader(f, sheet_index=0): row_dicts = [] doc = ooolib.Calc() doc.load(f) doc.set_sheet_index(sheet_index) Nc, Nr = doc.get_sheet_dimensions() titles = [doc.get_cell_value(c+1, 1)[1] for c in xrange(Nc)] for r in xrange(1, Nr): dict = {} for c in xrange(Nc): v = doc.get_cell_value(c+1, r+1) if v: dict[titles[c]] = v[1] else: dict[titles[c]] = None row_dicts.append(dict) return row_dicts
def test_cell_hyphenate(self): # create odt document filename = os.path.join(self.dirname, "test.ods") doc = ooolib.Calc("test_cell") doc.set_cell_property('hyphenate', True) doc.set_cell_value(2, 2, "string", "Name") doc.save(filename) # check created document handle = zipfile.ZipFile(filename) xdoc = etree.parse(BufferIO(handle.read('content.xml'))) style_definition = xdoc.xpath( '//style:text-properties[@fo:hyphenate="true"]/../@style:name', namespaces=self.namespaces) cell_style = xdoc.xpath('//text:p[text()="Name"]/../@table:style-name', namespaces=self.namespaces) self.assertEqual(cell_style, style_definition)
def add_sheet(self, prefix, tables, pdic): doc = ooolib.Calc(opendoc=self.file_name) sheets = doc.sheets for sheet in sheets: sheet_name = sheet.get_name() if len(sheet_name.split('-')) == 2: new_name = sheet_name else: new_name = '%s-%s' % (prefix, sheet_name) sheet.set_name(new_name) doc.sheets = sheets for table in tables: doc.new_sheet(table) i = 1 for port in tables[table]: doc.set_cell_value(i, 1, "string", port + '[' + ` pdic[port][2] ` + ']') i += 1 doc.save(self.file_name)
def get_table(self, tables, pdic, tb_name): doc = ooolib.Calc(opendoc=self.file_name) dst_dir = os.path.dirname(os.path.abspath(self.file_name)) sheets = {} for sheet in doc.sheets: sheets[sheet.get_name()] = sheet for table in tables: sheet = sheets[table] ports = tables[table] vector_mult = get_vector_conf(ports, pdic)[0] file_name = '%s/%s_%s_%s.tbl' % (dst_dir, tb_name, 'ods', table) f = open(file_name, 'w') (cols, rows) = sheet.get_sheet_dimensions() for row in range(2, rows + 1): data = 0 for col in range(1, cols + 1): intr = sheet.get_sheet_value(col, row)['value'] data += int(intr[1]) * vector_mult[col - 1] f.write( ` data ` + "\n") f.close()
def sheet(self): sheet = ooolib.Calc() row = 1 for order in self.order_set: for kit in order.kit_set: sheet.set_cell_value( 1, row, "string", "%s %s" % (order.last_name, order.first_name)) if order.phone: sheet.set_cell_value(2, row, "string", "%s, %s" % (order.email, order.phone)) else: sheet.set_cell_value(2, row, "string", order.email) sheet.set_cell_value(3, row, "string", "%s %s" % (kit.last_name, kit.first_name)) sheet.set_cell_value(4, row, "string", kit.release_date or "") sheet.set_cell_value(8, row, "string", kit.exchange_type) row += 1 file = StringIO.StringIO() sheet.save(file) return file.getvalue()
def readOds(): global odsFile doc = ooolib.Calc(opendoc=odsFile) doc.set_sheet_index(0) (cols, rows) = doc.get_sheet_dimensions() ## make sure ODS data matches column list for i in range(cols): d = doc.get_cell_value(i + 1, 2) if d is not None: d = d[1] if d != columns[i][0]: raise Exception( 'Column %d in ODS (%s) does not match template (%s)' % (i + 1, d, columns[i][0])) if cols != len(columns): raise Exception( 'Expected number of columns (%d) does not match number of columns in ODS (%d)' % (cols, len(columns))) data = [] for row in range(3, rows + 1): if doc.get_cell_value(1, row) == ('string', '__end__'): break data.append([]) for col in range(1, cols + 1): d = doc.get_cell_value(col, row) if isinstance(d, tuple): if d[0] == 'string': d = d[1] elif d[0] == 'float': d = float(d[1]) else: raise Exception("unknown cell data type: %s" % d[0]) data[-1].append(d) #print data[-1][-1], #print return data
#!/usr/bin/python import sys sys.path.append('..') import ooolib # Create the document doc = ooolib.Calc() # Set Column Width doc.set_column_property(1, 'width', '0.5in') doc.set_column_property(2, 'width', '1.0in') doc.set_column_property(3, 'width', '1.5in') # Set Row Height doc.set_row_property(1, 'height', '0.5in') doc.set_row_property(2, 'height', '1.0in') doc.set_row_property(3, 'height', '1.5in') # Fill in Cell Data doc.set_cell_value(1, 1, "string", "0.5in x 0.5in") doc.set_cell_value(2, 2, "string", "1.0in x 1.0in") doc.set_cell_value(3, 3, "string", "1.5in x 1.5in") # Write out the document doc.save("calc-example04.ods")
def test_sheet_name(self): doc = ooolib.Calc('Žluťoučký kůň') self.assertEqual(doc.sheets[0].sheet_name, 'Žluťoučký kůň')
#!/usr/bin/python import sys sys.path.append('..') import ooolib # Create the document doc = ooolib.Calc("First") # By default you start on Sheet1. This has an index of 0 in ooolib. doc.set_cell_value(1, 1, "string", "We start on \"Sheet1\"") # Create a new sheet by passing the title. You will automatically # move to that sheet. doc.new_sheet("Second") doc.set_cell_value(1, 1, "string", "I'm on Sheet2") # Create another one doc.new_sheet("Sheet3") doc.set_cell_value(1, 1, "string", "This is Sheet3") # Move back to the first sheet doc.set_sheet_index(0) doc.set_cell_value(1, 2, "string", "Sheet1's index is 0") # Move back to the second sheet doc.set_sheet_index(1) doc.set_cell_value(1, 2, "string", "Sheet2's index is 1") # Write out the document doc.save("calc-example02.ods")
def setUpClass(cls): path = os.path.join(os.path.dirname(__file__), "fixtures/test-cells.ods") cls.doc = ooolib.Calc(opendoc=path)
def test_sheet_datatype_and_value(self): doc = ooolib.Calc('Žluťoučký kůň') doc.set_cell_value(2, 2, 'šílený', 'čížek') self.assertEqual(doc.get_cell_value(2, 2), ('string', 'čížek'))
def test_annotation(self): doc = ooolib.Calc('Test') doc.set_cell_value(1, 1, 'annotation', 'foo') self.assertEqual(doc.get_cell_annotation(1, 1), ('annotation', 'foo'))
def test_link(self): doc = ooolib.Calc('Test') doc.set_cell_value(1, 1, 'link', ('url', 'label')) self.assertEqual(doc.get_cell_links(1, 1), [('url', 'label')])
def test_string(self): doc = ooolib.Calc('Test') doc.set_cell_value(1, 1, 'string', 'text') self.assertEqual(doc.get_cell_value(1, 1), ('string', 'text'))
#!/usr/bin/python import sys sys.path.append('..') import ooolib # See if there is a document to open on the command line if len(sys.argv) != 2: print "Usage:\n\t%s FILENAME.ods\n" % sys.argv[0] sys.exit(0) # Use opendoc to select the document to edit. doc = ooolib.Calc(opendoc=sys.argv[1]) # Now that the document has been opened and the data loaded, # we can add to the document, or display information from the # document. # First we display current contents print "Current Data" print " Meta Values" metalist = [ 'creator', 'editor', 'title', 'subject', 'description', 'user1name', 'user2name', 'user3name', 'user4name', 'user1value', 'user2value', 'user3value', 'user4value', 'keyword' ] for metaname in metalist: value = doc.get_meta_value(metaname) print " %s=%s" % (metaname, value) print " Content Values"
def test_sheet_datatype_and_value_object(self): doc = ooolib.Calc('Žluťoučký kůň') doc.set_cell_value(2, 2, 'string', Bird('čížek')) self.assertEqual(doc.get_cell_value(2, 2), ('string', 'bird čížek'))