def xlsx_sheet(self, doc):
doc.createSheet(self.title)
doc.setColumnsWidth(self.columnSizes_for_xlsx())
if self.hh is not None:
doc.overwrite(self.__getFirstContentCoord().addRow(-1), [self.hh], doc.stOrange)
if self.vh is not None:
for i, header in enumerate(self.vh):
doc.overwrite(self.__getFirstContentCoord().addColumn(-1).addRow(i), header, doc.stGreen, alignment="left")
if self.__mustFillA1()==True:
doc.overwrite("A1", " ", doc.stOrange)
if self.numDataRows()>0:
for number, row in enumerate(self.data):
for letter, field in enumerate(row):
doc.overwrite(self.__getFirstContentCoord().addRow(number).addColumn(letter), field, style=doc.stWhite)
#Fills horizontal total
if self.ht_definition is not None:
for letter, definition in enumerate(self.ht_definition):
class_=self.__object_to_formula_classname(self.data[0][letter])
if self.__is_total_key(definition):
doc.overwrite_formula(self.__getFirstContentCoord().addRow(self.numDataRows()).addColumn(letter), self.__calculate_horizontal_total("xlsx", letter), class_, style=doc.stGrayLight)
else:
doc.overwrite(self.__getFirstContentCoord().addRow(self.numDataRows()).addColumn(letter), self.__calculate_horizontal_total("xlsx", letter), style=doc.stGrayLight)
#Fills vertical total
if self.vt_definition is not None:
for number, definition in enumerate(self.vt_definition):
class_=self.__object_to_formula_classname(self.data[0][self.numDataColumns()-1])
value=self.__calculate_vertical_total("xlsx", number)
if self.__is_total_key(definition):
doc.overwrite_formula(Coord("A1").addRow(number).addColumn(self.numDataColumns()), value, class_, style=doc.stGrayLight)
else:
doc.overwrite(Coord("A1").addRow(number).addColumn(self.numDataColumns()), value, style=doc.stGrayLight)
self.__setFreezeAndSelect(doc)
def overwriteTotalsVertical(self,
coord,
list_of_totals,
list_of_styles=None,
column_from="B",
column_to=None,
list_of_decimals=2):
coord = Coord.assertCoord(coord)
for i, total in enumerate(list_of_totals):
coord_total = coord.addRowCopy(i)
coord_total_from = Coord(column_from + coord_total.number)
if column_to is None:
coord_total_to = coord_total.addColumnCopy(-1) # row above
else:
coord_total = Coord(column_to + coord_total.number)
if list_of_styles is None:
style = self.stGrayLight
else:
style = list_of_styles[i]
if list_of_decimals.__class__.__name__ == "int":
decimals = 2
else:
decimals = list_of_decimals[i]
self.overwrite(
coord_total,
generate_formula_total_string(total, coord_total_from,
coord_total_to), style, decimals)
def ods_sheet(self, doc):
s=doc.createSheet(self.title)
s.setColumnsWidth(self.columnSizes_for_ods())
if self.hh is not None:
s.add(self.__getFirstContentCoord().addRow(-1), [self.hh], "OrangeCenter")
if self.vh is not None :
for i, header in enumerate(self.vh):
s.add(self.__getFirstContentCoord().addColumn(-1).addRow(i), header, "GreenLeft")
if self.__mustFillA1()==True:
s.add("A1", "", "OrangeCenter")
if self.numDataRows()>0: #Only must be executed with data
for number, row in enumerate(self.data):
for letter, field in enumerate(row):
s.add(self.__getFirstContentCoord().addRow(number).addColumn(letter), field)
#Fills horizontal
if self.ht_definition is not None:
for letter, definition in enumerate(self.ht_definition):
value= self.__calculate_horizontal_total("ods", letter)
style=guess_ods_style("GrayLight", self.data[self.ht_index_from][letter])
s.add(Coord("A1").addRow(self.numDataRows()+1).addColumn(letter), value, style )
#Fills vertical
if self.vt_definition is not None:
for number, definition in enumerate(self.vt_definition):
value=self.__calculate_vertical_total("ods", number)
style=guess_ods_style("GrayLight", self.data[self.vt_index_from][letter])
s.add(Coord("A1").addRow(number).addColumn(self.numDataColumns()), value, style)
self.__setFreezeAndSelect(s)
return s
def overwriteTotalsHorizontal(self,
coord,
list_of_totals,
list_of_styles=None,
row_from="2",
row_to=None,
list_of_decimals=2):
coord = Coord.assertCoord(coord)
for letter, total in enumerate(list_of_totals):
coord_total = coord.addColumnCopy(letter)
coord_total_from = Coord(coord_total.letter + row_from)
if row_to is None:
coord_total_to = coord_total.addRowCopy(-1) # row above
else:
coord_total = Coord(coord_total.letter + row_to)
if list_of_styles is None:
style = self.stGrayLight
else:
style = list_of_styles[letter]
if list_of_decimals.__class__.__name__ == "int":
decimals = 2
else:
decimals = list_of_decimals[letter]
self.overwrite(
coord_total,
generate_formula_total_string(total, coord_total_from,
coord_total_to), style, decimals)
def freezeAndSelect(self,
freeze_coord,
selected_coord,
topleftcell_coord=None):
if topleftcell_coord == None:
topleftcell_coord = topLeftCellNone(freeze_coord, selected_coord)
freeze_coord = Coord.assertCoord(freeze_coord)
selected_coord = Coord.assertCoord(selected_coord)
topleftcell_coord = Coord.assertCoord(topleftcell_coord)
self.ws_current.freeze_panes = self.ws_current[freeze_coord.string()]
sheet = self.ws_current.views.sheetView[0]
if freeze_coord.letterIndex() > 0 and freeze_coord.numberIndex(
) > 0: #Freeze C3 WORKS
sheet.selection[2].activeCell = selected_coord.string()
sheet.selection[2].sqref = selected_coord.string()
sheet.pane.activePane = 'bottomRight'
sheet.pane.topLeftCell = topleftcell_coord.string()
elif freeze_coord.letterIndex(
) == 0 and freeze_coord.numberIndex() > 0: #Freeze A3 WORKS
topLeftCellFirst = topleftcell_coord.letter + "1"
sheet.topLeftCell = topLeftCellFirst
sheet.view = "normal"
sheet.pane.xSplit = "0"
sheet.selection[0].pane = "bottomLeft"
sheet.selection[0].activeCell = selected_coord.string()
sheet.selection[0].sqref = selected_coord.string()
sheet.selection[0].state = "frozen"
sel = list(sheet.selection)
sel.insert(
0,
openpyxl.worksheet.worksheet.Selection(
pane="topLeft",
activeCell=topLeftCellFirst,
sqref=topLeftCellFirst))
sheet.selection = sel
sheet.pane.topLeftCell = topleftcell_coord.string()
elif freeze_coord.letterIndex() > 0 and freeze_coord.numberIndex(
) == 0: #Freeze C1 WORKS
#Comparing output with a officegenerator good xlsx
topLeftCellFirst = "A" + topleftcell_coord.number
sheet.topLeftCell = topLeftCellFirst
sheet.pane.ySplit = "0"
sheet.selection[0].activeCell = selected_coord.string()
sheet.selection[0].sqref = selected_coord.string()
sel = list(sheet.selection)
sel.insert(
0,
openpyxl.worksheet.worksheet.Selection(
pane="topLeft",
activeCell=topLeftCellFirst,
sqref=topLeftCellFirst))
sheet.selection = sel
sheet.pane.topLeftCell = topleftcell_coord.string()
elif freeze_coord.letterIndex() == 0 and freeze_coord.numberIndex(
) == 0: #Freeze A1 WORKS
sheet.selection[0].activeCell = selected_coord.string()
sheet.selection[0].sqref = selected_coord.string()
sheet.selection[0].pane = 'topLeft'
sheet.topLeftCell = topleftcell_coord.string()
def __getFirstContentCoord(self):
#firstcontentletter and firstcontentnumber
if self.hh is None and self.vh is not None:
return Coord("B1")
elif self.hh is not None and self.vh is None:
return Coord("A2")
elif self.hh is not None and self.vh is not None:
return Coord("B2")
elif self.hh is None and self.vh is None:
return Coord("A1")
def test_Coord_methods(self):
s = "Z1"
self.assertEqual(Coord(s).addColumn().string(), "AA1")
self.assertEqual(Coord(s).addColumn(-25).string(), "A1")
self.assertEqual(Coord(s).addRow(25).string(), "Z26")
self.assertEqual(Coord(s).letterIndex(), 25)
self.assertEqual(Coord(s).letterPosition(), 26)
self.assertEqual(Coord(s).numberIndex(), 0)
self.assertEqual(Coord(s).numberPosition(), 1)
self.assertEqual(Coord(s).string(), "Z1")
self.assertEqual(str(Coord(s)), "Coord <Z1>")
def overwrite(self, coord, result, style=None, decimals=2, alignment=None):
coord = Coord.assertCoord(coord)
if result.__class__ == list: #Una lista
for i, row in enumerate(result):
if row.__class__ in (list, ): #Una lista de varias columnas
for j, column in enumerate(row):
self.__setCell(
Coord(coord.string()).addRow(i).addColumn(j),
result[i][j], style, decimals, alignment)
else: #Una lista de una columna
self.__setCell(
Coord(coord.string()).addRow(i), result[i], style,
decimals, alignment)
else: #Un solo valor
self.__setCell(coord, result, style, decimals, alignment)
def overwrite_formula(self,
coord,
value,
resultclass=None,
style=None,
decimals=2,
alignment=None):
if isFormula(value) == False:
print(_("This is not a formula. You can't use overwrite_formula"))
return
if value.__class__ == list:
print("Adding formula list is not allowed")
return
coord = Coord.assertCoord(coord)
cell = self.getCell(self.ws_current_id, coord)
self.__setValue(cell, value, style, decimals, alignment)
self.__setBorder(cell, style)
self.__setAlignment(cell, value, style, alignment)
self.__setFormulaNumberFormat(cell, value, resultclass, style,
decimals)
if style != None:
cell.fill = openpyxl.styles.PatternFill("solid", fgColor=style)
bold = False if style == self.stWhite else True
cell.font = openpyxl.styles.Font(name='Arial', size=10, bold=bold)
def getRowValues(self, sheet_index, row_number, skip_left=0, skip_right=0):
r = []
for column in range(skip_left,
self.columnNumber(sheet_index) - skip_right):
r.append(
self.getCellValue(sheet_index,
Coord("A" + row_number).addColumn(column)))
return r
def getColumnValues(self,
sheet_index,
column_letter,
skip_up=0,
skip_down=0):
r = []
for row in range(skip_up, self.rowNumber(sheet_index) - skip_down):
r.append(
self.getCellValue(sheet_index,
Coord(column_letter + "1").addRow(row)))
return r
def values(self, sheet_index, skip_up=0, skip_down=0):
r = []
for row in range(skip_up, self.rowNumber(sheet_index) - skip_down):
tmprow = []
for column in range(self.columnNumber(sheet_index)):
tmprow.append(
self.getCellValue(
sheet_index,
Coord("A1").addRow(row).addColumn(column)))
r.append(tmprow)
return r
def __setCell(self, coord, value, style=None, decimals=2, alignment=None):
coord = Coord.assertCoord(coord)
cell = self.getCell(self.ws_current_id, coord.string())
self.__setValue(cell, value, style, decimals, alignment)
self.__setBorder(cell, style)
self.__setAlignment(cell, value, style, alignment)
self.__setNumberFormat(cell, value, style, decimals)
if style != None:
cell.fill = openpyxl.styles.PatternFill("solid", fgColor=style)
bold = False if style == self.stWhite else True
cell.font = openpyxl.styles.Font(name='Arial', size=10, bold=bold)
def test_Coord_methods_in_the_limit(self):
s = "Z1"
self.assertEqual(Coord(s).addColumn(-26).string(), "A1")
self.assertEqual(Coord(s).addColumn(-2600).string(), "A1")
self.assertEqual(Coord(s).addRow(-1).string(), "Z1")
def getCell(self, sheet_index, coord):
self.setCurrentSheet(sheet_index)
coord = Coord.assertCoord(coord)
return self.ws_current[coord.string()]
def cell(self, coord):
coord = Coord.assertCoord(coord)
return self.ws_current[coord.string()]
def demo_ods():
start = datetime.now()
doc = ODS_Write("officegenerator.ods")
doc.setMetadata(
_("OfficeGenerator ODS example"), _("Demo with ODS_Write class"),
"Turulomio",
_("This file have been generated with OfficeGenerator-{}. You can see OfficeGenerator main page in http://github.com/turulomio/officegenerator"
).format(__version__), "officegenerator demo files")
s1 = doc.createSheet("Example")
s1.add("A1", [["Title", "Value"]], "OrangeCenter")
s1.add("A2", "Percentage", "YellowLeft")
s1.add("A4", "Suma", "WhiteCenter")
s1.add("B2", Percentage(12, 56), "WhitePercentage")
s1.add("B3", Percentage(12, 21), "WhitePercentage")
s1.add("B4", "=sum(B2:B3)", "WhitePercentage")
s1.add("B6", 100.26, "WhiteDecimal6")
s1.add("B7", 101, "WhiteInteger")
s1.freezeAndSelect("A2", "A30", "A5")
#Manual cell
cell = OdfCell("B10", "Celda con OdfCell", "YellowCenter")
cell.setComment("Comentario")
cell.setSpanning(2, 2)
s1.addCell(cell)
#Better way
s1.addMerged("E10:F11", "Celda con Merged", "GrayDarkCenter")
s1.setComment("E10", _("This is a comment"))
#Using lists in arr
s1.add("A13", [["Una fila"] * 3], "Orange")
s1.add("A15", [[12.3] * 3, [12.3] * 3])
sf1 = doc.createSheet("Freeze A1")
for letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ":
for number in range(1, 200):
sf1.add(letter + str(number), letter + str(number), "YellowLeft")
sf1.freezeAndSelect("A1", "Z199", "U180")
sf2 = doc.createSheet("Freeze A3")
for letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ":
for number in range(1, 200):
sf2.add(letter + str(number), letter + str(number), "YellowLeft")
sf2.freezeAndSelect("A3", "Z199", "M171")
sf3 = doc.createSheet("Freeze C1")
for letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ":
for number in range(1, 200):
sf3.add(letter + str(number), letter + str(number), "YellowLeft")
sf3.freezeAndSelect("C1", "Z199", "Q168")
sf4 = doc.createSheet("Freeze C3")
for letter in "ABCDEFG":
for number in range(1, 200):
sf4.add(letter + str(number), letter + str(number), "YellowLeft")
sf4.freezeAndSelect("C3", "G199", "Q168")
sf1 = doc.createSheet("Freeze A1 None")
for letter in "ABCDEFG":
for number in range(1, 200):
sf1.add(letter + str(number), letter + str(number), "YellowLeft")
sf1.freezeAndSelect("A1", "G199")
sf2 = doc.createSheet("Freeze A3 None")
for letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ":
for number in range(1, 15):
sf2.add(letter + str(number), letter + str(number), "YellowLeft")
sf2.freezeAndSelect("A3", "Z14")
sf3 = doc.createSheet("Freeze C1 None")
for letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ":
for number in range(1, 15):
sf3.add(letter + str(number), letter + str(number), "YellowLeft")
sf3.freezeAndSelect("C1", "Z14")
sf4 = doc.createSheet("Freeze C3 None")
for letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ":
for number in range(1, 200):
sf4.add(letter + str(number), letter + str(number), "YellowLeft")
sf4.freezeAndSelect("C3", "Z199")
s6 = doc.createSheet("Format number")
s6.setColumnsWidth([
ColumnWidthODS.L, ColumnWidthODS.Datetime, ColumnWidthODS.Date,
ColumnWidthODS.L, ColumnWidthODS.L, ColumnWidthODS.L, ColumnWidthODS.L,
ColumnWidthODS.XL, ColumnWidthODS.L, ColumnWidthODS.L, ColumnWidthODS.L
])
s6.add("A1", _("Style name"), "OrangeCenter")
s6.add("B1", _("Date and time"), "OrangeCenter")
s6.add("C1", _("Date"), "OrangeCenter")
s6.add("D1", _("Integer"), "OrangeCenter")
s6.add("E1", _("Euros"), "OrangeCenter")
s6.add("F1", _("Dollars"), "OrangeCenter")
s6.add("G1", _("Percentage"), "OrangeCenter")
s6.add("H1", _("Number with 2 decimals"), "OrangeCenter")
s6.add("I1", _("Number with 6 decimals"), "OrangeCenter")
s6.add("J1", _("Time"), "OrangeCenter")
s6.add("K1", _("Boolean"), "OrangeCenter")
for row, color in enumerate(doc.colors.arr):
s6.add(Coord("A2").addRow(row), color.name, color.name + "Left")
s6.add(
Coord("B2").addRow(row), datetime.now(), color.name + "Datetime")
s6.add(Coord("C2").addRow(row), date.today(), color.name + "Date")
s6.add(
Coord("D2").addRow(row),
pow(-1, row) * -10000000, color.name + "Integer")
s6.add(
Coord("E2").addRow(row), Currency(pow(-1, row) * 12.56, "EUR"),
color.name + "EUR")
s6.add(
Coord("F2").addRow(row), Currency(pow(-1, row) * 12345.56, "USD"),
color.name + "USD")
s6.add(
Coord("G2").addRow(row), Percentage(pow(-1, row) * 1, 3),
color.name + "Percentage")
s6.add(
Coord("H2").addRow(row),
pow(-1, row) * 123456789.121212, color.name + "Decimal2")
s6.add(
Coord("I2").addRow(row),
pow(-1, row) * -12.121212, color.name + "Decimal6")
s6.add(
Coord("J2").addRow(row),
(datetime.now() + timedelta(seconds=3600 * 12 * row)).time(),
color.name + "Time")
s6.add(Coord("K2").addRow(row), bool(row % 2), color.name + "Left")
s6.setComment("B2", _("This is a comment"))
#Merge cells
s6.addMerged("B13:F14",
_("This cell is going to be merged with B13 a F14"),
"GreenCenter")
s6.addMerged(
"B18:G18",
_("This cell is going to be merged and aligned desde B18 a G18"),
"YellowRight")
s6.freezeAndSelect("A11", "B11", "A11") #Default values
#Cells with formula with diferent styles and number formats
s6.addMerged(
"A20:D20",
_("These cells show formulas with diferent styles and number formats"),
"Green")
s6.add("E20", "=2+3.3", "NormalDecimal6")
s6.add("F20", "=2+3.3", "YellowEUR")
s6.add("G20", "=2+3.3", "GreenDatetime")
s6.add("H20", "=0.9/23", "WhitePercentage")
s7 = doc.createSheet("Add totals example")
s7.add("A1", _("Style name"), "OrangeCenter")
s7.add("B1", _("Date and time"), "OrangeCenter")
s7.add("C1", _("Date"), "OrangeCenter")
s7.add("D1", _("Integer"), "OrangeCenter")
s7.add("E1", _("Euros"), "OrangeCenter")
s7.add("F1", _("Dollars"), "OrangeCenter")
s7.add("G1", _("Percentage"), "OrangeCenter")
s7.add("H1", _("Number with 2 decimals"), "OrangeCenter")
s7.add("I1", _("Number with 6 decimals"), "OrangeCenter")
s7.add("J1", _("Time"), "OrangeCenter")
s7.add("K1", _("Boolean"), "OrangeCenter")
for row, color in enumerate(doc.colors.arr):
s7.add(Coord("A2").addRow(row), color.name, color.name + "Left")
s7.add(Coord("B2").addRow(row), 1, color.name + "Integer")
s7.add(Coord("C2").addRow(row), 1, color.name + "Integer")
s7.add(Coord("D2").addRow(row), 1, color.name + "Integer")
s7.add(Coord("E2").addRow(row), 1, color.name + "Integer")
s7.add(Coord("F2").addRow(row), 1, color.name + "Integer")
s7.add(Coord("G2").addRow(row), 1, color.name + "Integer")
s7.add(Coord("H2").addRow(row), 1, color.name + "Integer")
s7.add(Coord("I2").addRow(row), 1, color.name + "Integer")
s7.add(Coord("J2").addRow(row), 1, color.name + "Integer")
s7.add(Coord("K2").addRow(row), 1, color.name + "Integer")
s7.addTotalsHorizontal("A11", [
"Total", "#SUM", "#SUM", "#SUM", "#AVG", "#MEDIAN", "#SUM", "#AVG",
"#MEDIAN", "#SUM", "#SUM"
])
s7.addTotalsVertical("L1", [
"Total", "#SUM", "#AVG", "#MEDIAN", "#SUM", "#AVG", "#MEDIAN", "#SUM",
"#SUM", "#SUM", "#SUM"
])
s7.freezeAndSelect("B2")
doc.setActiveSheet(s6)
doc.save()
return "demo_ods took {}".format(datetime.now() - start)
def demo_xlsx():
start = datetime.now()
xlsx = XLSX_Write("officegenerator.xlsx")
xlsx.setCurrentSheet(0)
xlsx.setSheetName(_("Styles"))
xlsx.setColumnsWidth([20, 20, 20, 20, 20, 20, 20, 20])
xlsx.overwrite("A1",
_("Style name"),
style=xlsx.stOrange,
alignment="center")
xlsx.overwrite("B1",
_("Date and time"),
style=xlsx.stOrange,
alignment="center")
xlsx.overwrite("C1", _("Date"), style=xlsx.stOrange, alignment="center")
xlsx.overwrite("D1", _("Integer"), style=xlsx.stOrange, alignment="center")
xlsx.overwrite("E1", _("Euros"), style=xlsx.stOrange, alignment="center")
xlsx.overwrite("F1",
_("Percentage"),
style=xlsx.stOrange,
alignment="center")
xlsx.overwrite("G1",
_("Number with 2 decimals"),
style=xlsx.stOrange,
alignment="center")
xlsx.overwrite("H1",
_("Number with 6 decimals"),
style=xlsx.stOrange,
alignment="center")
xlsx.overwrite("I1", _("Time"), style=xlsx.stOrange, alignment="center")
xlsx.overwrite("J1", _("Boolean"), style=xlsx.stOrange, alignment="center")
for row, style in enumerate([
xlsx.stOrange, xlsx.stGreen, xlsx.stGrayLight, xlsx.stYellow,
xlsx.stGrayDark, xlsx.stWhite, None
]):
xlsx.overwrite(Coord("A2").addRow(row),
xlsx.styleName(style),
style=style)
xlsx.overwrite(Coord("B2").addRow(row), datetime.now(), style=style)
xlsx.overwrite(Coord("C2").addRow(row), date.today(), style=style)
xlsx.overwrite(Coord("D2").addRow(row),
pow(-1, row) * -10000000,
style=style)
xlsx.overwrite(Coord("E2").addRow(row),
Currency(pow(-1, row) * 12.56, "EUR"),
style=style)
xlsx.overwrite(Coord("F2").addRow(row),
Percentage(1, 3),
style=style,
decimals=row + 1)
xlsx.overwrite(Coord("G2").addRow(row),
pow(-1, row) * 12.121212,
style=style,
decimals=2)
xlsx.overwrite(Coord("H2").addRow(row),
pow(-1, row) * -12.121212,
style=style,
decimals=6)
xlsx.overwrite(Coord("I2").addRow(row),
datetime.now().time(),
style=style,
decimals=6)
xlsx.overwrite(Coord("J2").addRow(row), True, style=style, decimals=6)
xlsx.setComment("B2", _("This is a comment"))
##To write a custom cell
cell = xlsx.wb.active['B12']
cell.font = openpyxl.styles.Font(name='Arial',
size=16,
bold=True,
color='00FF0000') #Red
cell.value = _("This is a custom cell")
#Merge cells
xlsx.overwrite_and_merge(
"A13:C14",
_("This cell is going to be merged with B13 and C13"),
style=xlsx.stOrange)
xlsx.overwrite_and_merge("A18:G18",
_("This cell is going to be merged and aligned"),
style=xlsx.stGrayDark,
alignment="right")
xlsx.overwrite("A16", [[_("This are booleans"), False, True]],
style=xlsx.stWhite,
alignment='right')
xlsx.overwrite("D16",
_("These are formulas returning booleans"),
style=xlsx.stGreen)
xlsx.overwrite("A20", [["Una fila"] * 3], style=xlsx.stGrayDark)
xlsx.overwrite_and_merge(
"E13:G13",
_("This sheet max rows are {} and max columns {}").format(
xlsx.rowNumber(xlsx.ws_current_id),
xlsx.columnNumber(xlsx.ws_current_id)),
style=xlsx.stYellow,
alignment="center")
#Named cells
xlsx.overwrite_and_merge("A23:B23",
_("Cell B23 has a name 'Amount"),
style=xlsx.stWhite)
xlsx.overwrite("C23", 5, style=xlsx.stWhite)
xlsx.setCellName("$C$23", "Amount")
xlsx.overwrite_and_merge("A24:B24",
_("Cell B24 has a name 'Price"),
style=xlsx.stWhite)
xlsx.overwrite("C24", Currency(10, 'EUR'), style=xlsx.stWhite)
xlsx.setCellName("$C$24", "Price")
xlsx.overwrite_and_merge("A25:B25",
_("Cell B25 has a product with names"),
style=xlsx.stWhite)
xlsx.overwrite_formula("C25",
"=Amount*Price",
"€",
style=xlsx.stWhite,
alignment='right')
xlsx.freezeAndSelect("A9", "B11", "A9")
#To text split and cur position
xlsx.createSheet("Freeze A1 None")
for letter in "ABCDEFGHIJKLMNOPQ":
for number in range(1, 100):
xlsx.overwrite(letter + str(number),
letter + str(number),
style=xlsx.stYellow)
xlsx.freezeAndSelect("A1", "Z199")
#To text split and cur position
xlsx.createSheet("Freeze A3 None")
for letter in "ABCDEFGHIJKLMNOPQ":
for number in range(1, 100):
xlsx.overwrite(letter + str(number),
letter + str(number),
style=xlsx.stYellow)
xlsx.freezeAndSelect("A3", "Z199")
#To text split and cur position
xlsx.createSheet("Freeze C1 None")
for letter in "ABCDEFGHIJKLMNOPQ":
for number in range(1, 100):
xlsx.overwrite(letter + str(number),
letter + str(number),
style=xlsx.stYellow)
xlsx.freezeAndSelect("C1", "Z199")
#To text split and cur position
xlsx.createSheet("Freeze C3 None")
for letter in "ABCDEFGHIJKLMNOPQ":
for number in range(1, 100):
xlsx.overwrite(letter + str(number),
letter + str(number),
style=xlsx.stYellow)
xlsx.freezeAndSelect("C3", "Z199")
xlsx.createSheet("Add totals example")
xlsx.overwrite("A1",
_("Style name"),
style=xlsx.stOrange,
alignment="center")
xlsx.overwrite("B1",
_("Date and time"),
style=xlsx.stOrange,
alignment="center")
xlsx.overwrite("C1", _("Date"), style=xlsx.stOrange, alignment="center")
xlsx.overwrite("D1", _("Integer"), style=xlsx.stOrange, alignment="center")
xlsx.overwrite("E1", _("Euros"), style=xlsx.stOrange, alignment="center")
xlsx.overwrite("F1",
_("Percentage"),
style=xlsx.stOrange,
alignment="center")
xlsx.overwrite("G1",
_("Number with 2 decimals"),
style=xlsx.stOrange,
alignment="center")
xlsx.overwrite("H1",
_("Number with 6 decimals"),
style=xlsx.stOrange,
alignment="center")
xlsx.overwrite("I1", _("Time"), style=xlsx.stOrange, alignment="center")
xlsx.overwrite("J1", _("Boolean"), style=xlsx.stOrange, alignment="center")
xlsx.overwrite("K1", _("Boolean"), style=xlsx.stOrange, alignment="center")
for row, style in enumerate([
xlsx.stOrange, xlsx.stGreen, xlsx.stGrayLight, xlsx.stYellow,
xlsx.stGrayDark, xlsx.stWhite, None
]):
xlsx.overwrite(Coord("A2").addRow(row),
xlsx.styleName(style),
style=style)
xlsx.overwrite(Coord("B2").addRow(row), 1, style=style)
xlsx.overwrite(Coord("C2").addRow(row), 1, style=style)
xlsx.overwrite(Coord("D2").addRow(row), 1, style=style)
xlsx.overwrite(Coord("E2").addRow(row), 1, style=style)
xlsx.overwrite(Coord("F2").addRow(row),
1,
style=style,
decimals=row + 1)
xlsx.overwrite(Coord("G2").addRow(row), 1, style=style, decimals=2)
xlsx.overwrite(Coord("H2").addRow(row), 1, style=style, decimals=6)
xlsx.overwrite(Coord("I2").addRow(row), 1, style=style, decimals=6)
xlsx.overwrite(Coord("J2").addRow(row), 1, style=style, decimals=6)
xlsx.overwrite(Coord("K2").addRow(row), 1, style=style, decimals=6)
xlsx.overwriteTotalsHorizontal("A9", [
"Total", "#SUM", "#SUM", "#SUM", "#AVG", "#MEDIAN", "#SUM", "#AVG",
"#MEDIAN", "#SUM", "#SUM"
])
xlsx.overwriteTotalsVertical("L1", [
"Total", "#SUM", "#AVG", "#MEDIAN", "#SUM", "#AVG", "#MEDIAN", "#SUM",
"#SUM"
])
xlsx.setColorScale("L2:L8")
xlsx.freezeAndSelect("B2")
xlsx.save()
return "demo_xlsx took {}".format(datetime.now() - start)
def demo_xlsx():
xlsx = OpenPyXL("officegenerator.xlsx")
xlsx.setCurrentSheet(0)
xlsx.setSheetName(_("Styles"))
xlsx.setColumnsWidth([20, 20, 20, 20, 20, 20, 20, 20])
xlsx.overwrite("A1",
_("Style name"),
style=xlsx.stOrange,
alignment="center")
xlsx.overwrite("B1",
_("Date and time"),
style=xlsx.stOrange,
alignment="center")
xlsx.overwrite("C1", _("Date"), style=xlsx.stOrange, alignment="center")
xlsx.overwrite("D1", _("Integer"), style=xlsx.stOrange, alignment="center")
xlsx.overwrite("E1", _("Euros"), style=xlsx.stOrange, alignment="center")
xlsx.overwrite("F1",
_("Percentage"),
style=xlsx.stOrange,
alignment="center")
xlsx.overwrite("G1",
_("Number with 2 decimals"),
style=xlsx.stOrange,
alignment="center")
xlsx.overwrite("H1",
_("Number with 6 decimals"),
style=xlsx.stOrange,
alignment="center")
for row, style in enumerate([
xlsx.stOrange, xlsx.stGreen, xlsx.stGrayLight, xlsx.stYellow,
xlsx.stGrayDark, xlsx.stWhite, None
]):
xlsx.overwrite(Coord("A2").addRow(row),
xlsx.styleName(style),
style=style)
xlsx.overwrite(Coord("B2").addRow(row),
datetime.datetime.now(),
style=style)
xlsx.overwrite(Coord("C2").addRow(row),
datetime.date.today(),
style=style)
xlsx.overwrite(Coord("D2").addRow(row),
pow(-1, row) * -10000000,
style=style)
xlsx.overwrite(Coord("E2").addRow(row),
Currency(pow(-1, row) * 12.56, "EUR"),
style=style)
xlsx.overwrite(Coord("F2").addRow(row),
Percentage(1, 3),
style=style,
decimals=row + 1)
xlsx.overwrite(Coord("G2").addRow(row),
pow(-1, row) * 12.121212,
style=style,
decimals=2)
xlsx.overwrite(Coord("H2").addRow(row),
pow(-1, row) * -12.121212,
style=style,
decimals=6)
xlsx.setComment("B2", _("This is a comment"))
##To write a custom cell
cell = xlsx.wb.active['B12']
cell.font = openpyxl.styles.Font(name='Arial',
size=16,
bold=True,
color=openpyxl.styles.colors.RED)
cell.value = _("This is a custom cell")
#Merge cells
xlsx.overwrite_and_merge(
"A13:C14",
_("This cell is going to be merged with B13 and C13"),
style=xlsx.stOrange)
xlsx.overwrite_and_merge("A18:G18",
_("This cell is going to be merged and aligned"),
style=xlsx.stGrayDark,
alignment="right")
xlsx.overwrite("A20", [["Una fila"] * 3], style=xlsx.stGrayDark)
xlsx.overwrite_and_merge(
"E13:G13",
_("This sheet max rows are {} and max columns {}").format(
xlsx.max_rows(), xlsx.max_columns()),
style=xlsx.stYellow,
alignment="center")
#Named cells
xlsx.overwrite_and_merge("A23:B23",
_("Cell B23 has a name 'Amount"),
style=xlsx.stWhite)
xlsx.overwrite("C23", 5, style=xlsx.stWhite)
xlsx.setCellName("$C$23", "Amount")
xlsx.overwrite_and_merge("A24:B24",
_("Cell B24 has a name 'Price"),
style=xlsx.stWhite)
xlsx.overwrite("C24", Currency(10, 'EUR'), style=xlsx.stWhite)
xlsx.setCellName("$C$24", "Price")
xlsx.overwrite_and_merge("A25:B25",
_("Cell B25 has a product with names"),
style=xlsx.stWhite)
xlsx.overwrite_formula("C25",
"=Amount*Price",
"€",
style=xlsx.stWhite,
alignment='right')
xlsx.freezeAndSelect("A9", "B11", "A9")
#To text split and cur position
xlsx.createSheet("Freeze A1")
for letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ":
for number in range(1, 200):
xlsx.overwrite(letter + str(number),
letter + str(number),
style=xlsx.stYellow)
xlsx.freezeAndSelect("A1", "Z199", "I168")
#To text split and cur position
xlsx.createSheet("Freeze A3")
for letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ":
for number in range(1, 200):
xlsx.overwrite(letter + str(number),
letter + str(number),
style=xlsx.stYellow)
xlsx.freezeAndSelect("A3", "Z199", "I168")
#To text split and cur position
xlsx.createSheet("Freeze C1")
for letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ":
for number in range(1, 200):
xlsx.overwrite(letter + str(number),
letter + str(number),
style=xlsx.stYellow)
xlsx.freezeAndSelect("C1", "Z199", "I168")
#To text split and cur position
xlsx.createSheet("Freeze C3")
for letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ":
for number in range(1, 200):
xlsx.overwrite(letter + str(number),
letter + str(number),
style=xlsx.stYellow)
xlsx.freezeAndSelect("C3", "Z199", "I168")
xlsx.save()
#To text split and cur position
xlsx.createSheet("Freeze A1 None")
for letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ":
for number in range(1, 200):
xlsx.overwrite(letter + str(number),
letter + str(number),
style=xlsx.stYellow)
xlsx.freezeAndSelect("A1", "Z199")
#To text split and cur position
xlsx.createSheet("Freeze A3 None")
for letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ":
for number in range(1, 200):
xlsx.overwrite(letter + str(number),
letter + str(number),
style=xlsx.stYellow)
xlsx.freezeAndSelect("A3", "Z199")
#To text split and cur position
xlsx.createSheet("Freeze C1 None")
for letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ":
for number in range(1, 200):
xlsx.overwrite(letter + str(number),
letter + str(number),
style=xlsx.stYellow)
xlsx.freezeAndSelect("C1", "Z199")
#To text split and cur position
xlsx.createSheet("Freeze C3 None")
for letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ":
for number in range(1, 200):
xlsx.overwrite(letter + str(number),
letter + str(number),
style=xlsx.stYellow)
xlsx.freezeAndSelect("C3", "Z199")
xlsx.save()