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