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