重庆分公司,新征程启航
为企业提供网站建设、域名注册、服务器等服务
python中怎么利用openpyxl 新增sheet,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。
专注于为中小企业提供网站制作、成都网站建设服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业清流免费做网站提供优质的服务。我们立足成都,凝聚了一批互联网行业人才,有力地推动了数千家企业的稳健成长,帮助中小企业通过网站建设实现规模扩充和转变。
任务背景:
某excel 文件中有 21个 结构相同的sheet 需要汇总
利用openpyxl得到sheet 列表
from openpyxl import load_workbookworkbook = load_workbook(filename = "xxxxdata.xlsx")sheet_list = workbook.sheetnames
得到了sheet 列表,再利用pandas,读取每一个sheet 后追加到一起,
import pandas as pd xls = pd.ExcelFile('xxxxxdata.xlsx')df1 = pd.read_excel(xls, '6.1',header=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[['采购姓名', '上架产品数量', '销售总金额', '连云港', '徐淮临日盐', '扬州']] df_each['日期'] = each_sheet df1 = df1.append(df_each)
将df1 在原文件新增sheet,不覆盖原有的sheet
import openpyxl
import pandas as pd
writer = pd.ExcelWriter('xxxxdata.xlsx',engine='openpyxl')
book = openpyxl.load_workbook(writer.path)
writer.book = book
df1.to_excel(writer, "新sheet名",index=False)
writer.save()
关于在excel中新增sheet不覆盖,stackflow还有更好的答案,测试成功
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[sheet_name].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()# 原文链接:https://blog.csdn.net/sdaujz/article/details/102080900
看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注创新互联行业资讯频道,感谢您对创新互联的支持。