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 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 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 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 __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 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 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()]
