def create_excel_table_from_df(df: pd.DataFrame, sheet_: Worksheet, row_ini: 1,
table_name):
"""Crea tabla de Excel en la hoja indicada a partir de un pandas DataFrame.
Parametros:
df: pandas DataFrame
row_ini: fila inicial, por default 1
sheet_: Worksheet object openpyxl
table_name: nombre de la tabla"""
col_last = get_excel_style(1, df.shape[1])[:-1]
# Crear tabla de Excel
tabla_excel = Table(
displayName=table_name,
ref=f"A{row_ini}:{col_last}{df.shape[0] + row_ini}") # nombre y tamaño
# declarar estilos a la tabla
style = TableStyleInfo(name="TableStyleMedium2", showRowStripes=False)
# asignar el estilo
tabla_excel.tableStyleInfo = style
# agregar tabla a la hoja
sheet_.add_table(tabla_excel)
def get_excel_formatting(
worksheet: Worksheet,
excel_writer: ExcelWriter,
) -> NoReturn:
columns = ['A', 'B']
column_a = worksheet['A:A']
column_b = worksheet['B:B']
tab = Table(displayName='Table1',
ref=f'A1:B{len(excel_writer.values) + 1}')
style = TableStyleInfo(name='TableStyleLight20', showRowStripes=True)
tab.tableStyleInfo = style
worksheet.add_table(tab)
for cell in column_a:
cell.number_format = FORMAT_PERCENT
for cell in column_b:
cell.number_format = FORMAT_CURRENCY_REAL
for column in columns:
worksheet.column_dimensions[column].width = 22
def to_table_format(sheet: worksheet.Worksheet, num):
"""
Description: Format's a Excel sheet into a table format.
Parameters
----------
sheet: worksheet.Worksheet A excel sheet
"""
end_col_char = ord('A') + sheet.max_column - 1
end_col_char = chr(end_col_char)
end_row_char = sheet.max_row
tab = Table(displayName=f"Table{num}",
ref=f"A1:{end_col_char}{end_row_char}")
style = TableStyleInfo(name="TableStyleMedium2",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False)
tab.tableStyleInfo = style
sheet.add_table(tab)
sheet.sheet_view.showGridLines = False
def fill_excel_sheet(self,
worksheet: Worksheet,
csv_list: list,
name: str = None,
title: str = None,
description: str = None) -> None:
"""
This method adds an additional sheet to the given workbook
:return:
"""
start_row = 1
name = name if name is not None else self._name
title = title if title is not None else self.title
description = description if description is not None else self.description
worksheet.title = name
if description:
csv_list.insert(0, [])
csv_list.insert(0, [description])
start_row += 2
if title:
csv_list.insert(0, [])
csv_list.insert(0, [title])
start_row += 2
for row in csv_list:
try:
worksheet.append(row)
except IllegalCharacterError:
print("ignoring row due to illegal character: {}".format(row),
file=sys.stderr)
except ValueError:
raise ValueError("cannot add row to sheet '{}': {}".format(
self._name, row))
dimension = worksheet.calculate_dimension()
dimension = "A{}:{}".format(start_row, dimension.split(":")[-1])
table = Table(displayName=self._name.replace(" ", ""), ref=dimension)
style = TableStyleInfo(name="TableStyleLight8")
table.tableStyleInfo = style
worksheet.add_table(table)
