Shulou(Shulou.com)06/02 Report--

Python how to use openpyxl to add sheet, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Task background:

There are 21 sheet with the same structure in an excel file that need to be summarized.

Using openpyxl to get sheet list

From openpyxl import load_workbookworkbook = load_workbook (filename = "xxxxdata.xlsx") sheet_list = workbook.sheetnames

Get the sheet list, then use pandas to read each sheet and append it together

Import pandas as pd xls = pd.ExcelFile ('xxxxxdata.xlsx') df1 = pd.read_excel (xls,' 6. 1 coach headerbread 1) sheet_list_left = ['6. 2, 6. 3, 6. 4, 6. 5, 6. 7, 6. 8, 6. 9, 6. 10, 6. 11, 6. 12, 6. 14, 6. 15' '6.16', '6.18', '6.17', '6.19', '6.21'] for each_sheet in sheet_list_left: print (each_sheet) df_each= pd.read_excel (xls,each_sheet,header=1) df_each= df_each [['purchase name', 'quantity of products on shelves', 'total sales amount', 'Lianyungang','Xu Huai Linri Salt' 'Yangzhou']] df_each ['date'] = each_sheet df1 = df1.append (df_each)

Add df1 to the original file by adding sheet, without overwriting the original sheet

Import openpyxlimport pandas as pdwriter = pd.ExcelWriter ('xxxxdata.xlsx',engine='openpyxl') book = openpyxl.load_workbook (writer.path) writer.book = bookdf1.to_excel (writer, "New sheet name", index=False) writer.save ()

Stackflow has a better answer about adding sheet not covered in excel. The test is successful.

Import pandas as pdfrom openpyxl import load_workbookdef append_df_to_excel (filename, df, sheet_name='Sheet1', startrow=None, truncate_sheet=False, * * to_excel_kwargs): "" Append a DataFrame [df] to existing Excel file [filename] into [sheet_name] Sheet. If [filename] doesn't exist, then this function will create it. Parameters: filename: File path or existing ExcelWriter (Example:'/ path/to/file.xlsx') df: dataframe to save to workbook sheet_name: Name of sheet which will contain DataFrame. (default: 'Sheet1') startrow: upper left cell row to dump data frame. Per default (startrow=None) calculate the last row in the existing DF and write to the next row... Truncate_sheet: truncate (remove and recreate) [sheet_name] before writing DataFrame to Excel file to_excel_kwargs: arguments which will be passed to `DataFrame.to_excel () `[can be dictionary] Returns: None "" # from openpyxl import load_workbook # import pandas as pd # ignore [engine] parameter if it was passed if 'engine' in to_excel_kwargs : to_excel_kwargs.pop ('engine') writer = pd.ExcelWriter (filename Engine='openpyxl') # Python 2.x: define [FileNotFoundError] exception if it doesn't exist try: FileNotFoundError except NameError: FileNotFoundError = IOError try: # try to open an existing workbook writer.book = load_workbook (filename) # get the last row in the existing Excel sheet # if it was not specified explicitly if startrow is None and sheet_name in writer.book.sheetnames: Startrow = writer.book.max _ row # truncate sheet if truncate_sheet and sheet_name in writer.book.sheetnames: # index of [sheet_name] sheet idx = writer.book.sheetnames.index (sheet_name) # remove [sheet_name] writer.book.remove (writer.book.worksheets [IDX]) # create an empty sheet [sheet _ name] using old index writer.book.create_sheet (sheet_name Idx) # copy existing sheets writer.sheets = {ws.title:ws for ws in writer.book.worksheets} except FileNotFoundError: # file does not exist yet, we will create it pass if startrow is None: startrow= 0 # write out the new sheet df.to_excel (writer, sheet_name, startrow=startrow * * to_excel_kwargs) # save the workbook writer.save () # original text link: https://blog.csdn.net/sdaujz/article/details/102080900

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.