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'))
