openpyxl

Insert row into Excel spreadsheet using openpyxl in Python

by

== Updated to a fully functional version, based on feedback here: groups.google.com/forum/#!topic/openpyxl-users/wHGecdQg3Iw. == As the others have pointed out, openpyxl does not provide this functionality, but I have extended the Worksheet class as follows to implement inserting rows. Hope this proves useful to others. def insert_rows(self, row_idx, cnt, above=False, copy_style=True, fill_formulae=True): “””Inserts new (empty) rows … Read more

Is there a way to auto-adjust Excel column widths with pandas.ExcelWriter?

by

Inspired by user6178746’s answer, I have the following: # Given a dict of dataframes, for example: # dfs = {‘gadgets’: df_gadgets, ‘widgets’: df_widgets} writer = pd.ExcelWriter(filename, engine=”xlsxwriter”) for sheetname, df in dfs.items(): # loop through `dict` of dataframes df.to_excel(writer, sheet_name=sheetname) # send df to writer worksheet = writer.sheets[sheetname] # pull worksheet object for idx, col … Read more

Password Protecting Excel file using Python

by

Here’s a workaround I use. It generates a VBS script and calls it from within your python script. def set_password(excel_file_path, pw): from pathlib import Path excel_file_path = Path(excel_file_path) vbs_script = \ f”””‘ Save with password required upon opening Set excel_object = CreateObject(“Excel.Application”) Set workbook = excel_object.Workbooks.Open(“{excel_file_path}”) excel_object.DisplayAlerts = False excel_object.Visible = False workbook.SaveAs “{excel_file_path}”,, “{pw}” … Read more

Copy whole worksheet with openpyxl

by

Version 2.4 will allow you to do this: copy_worksheet >>> source = wb.active >>> target = wb.copy_worksheet(source) For older ones you can probably copy the source code from here UPDATE: You can’t simply graft this code into older versions of the library

copy cell style openpyxl

by

As of openpyxl 2.5.4, python 3.4: (subtle changes over the older version below) new_sheet = workbook.create_sheet(sheetName) default_sheet = workbook[‘default’] from copy import copy for row in default_sheet.rows: for cell in row: new_cell = new_sheet.cell(row=cell.row, column=cell.col_idx, value= cell.value) if cell.has_style: new_cell.font = copy(cell.font) new_cell.border = copy(cell.border) new_cell.fill = copy(cell.fill) new_cell.number_format = copy(cell.number_format) new_cell.protection = copy(cell.protection) new_cell.alignment … Read more

How to concatenate three excels files xlsx using python?

by

Here’s a pandas-based approach. (It’s using openpyxl behind the scenes.) import pandas as pd # filenames excel_names = [“xlsx1.xlsx”, “xlsx2.xlsx”, “xlsx3.xlsx”] # read them in excels = [pd.ExcelFile(name) for name in excel_names] # turn them into dataframes frames = [x.parse(x.sheet_names[0], header=None,index_col=None) for x in excels] # delete the first row for all frames except the … Read more

tech