def _create_cell(self, worksheet: WriteOnlyWorksheet, data: dict) -> WriteOnlyCell:
cell = WriteOnlyCell(ws=worksheet, value=data.get('value'))
cell.column = data.get('column')
cell.row = data.get('row')
cell.font = self._create_cell_font(data.get('font'))
cell.alignment = self._dict_to_object(data.get('alignment'), Alignment)
cell.border = self._create_cell_borders(data.get('border'))
return cell
def write_dataframe_values_to_excel(worksheet, dataframe, start_row=None, wrap_text=True):
"""Write DataFrame values to Excel worksheet
:param worksheet: target Excel worksheet
:type worksheet: openpyxl.worksheet.worksheet.Worksheet
:param dataframe: source dataframe
:type dataframe: pandas.DataFrame
:param start_row: start row index for writing data
:param wrap_text: wrap text in cell
:type wrap_text: boolean
:return: None
"""
# init styles for values
border_side = Side(border_style='thin', color='000000')
border_style = Border(left=border_side, right=border_side, top=border_side, bottom=border_side)
alignment = Alignment(wrap_text=wrap_text)
for row_idx, row_data in enumerate(dataframe.itertuples()):
values_list = []
row_cells = []
for val in row_data:
# value in dataframe may be a tuple in case of existing multiindex and it cannot be correctly written to
# excel worksheet; so convert the tuple into a list and merge it with values list
if isinstance(val, tuple):
values_list += list(val)
else:
values_list.append(val)
for col_idx, val in enumerate(values_list):
cell = WriteOnlyCell(worksheet)
cell.border = border_style
if isinstance(val, Iterable) and not isinstance(val, string_types):
cell.value = ', '.join([str(v) for v in val])
else:
cell.value = None if isnull(val) else val
cell.alignment = alignment
row_cells.append(cell)
if start_row is None:
worksheet.append(row_cells)
else:
worksheet.insert_rows(start_row, amount=1)
for col_idx, cell in enumerate(row_cells):
new_cell = worksheet.cell(row=start_row, column=col_idx+1)
new_cell.value = cell.value
new_cell.border = border_style
new_cell.alignment = alignment
start_row += 1
