附代码PythonExcel合并单元格（OpenPyXL）Pandas.DataFramegroupby样式保存xlsx

孙夏

文章目录相关文献Excel合并单元格并居中Pandas.DataFramegroupby样式保存Excel作者：小猪快跑基础数学&计算数学，从事优化领域5年+，主要研究方向：MIP求解器、整数规划、随机规划、智能优化算法如有错误，欢迎指正。如有更好的算法，也欢迎交流！！！——@小猪快跑相关文献openpyxl-APythonlibrarytoread/writeExcel2010xlsx/xlsmfiles—openpyxl3.1.2documentationExcel合并单元格并居中如果我们想合并单元格A1:B1并居中：fromopenpyxl.stylesimportAlignmentfromopenpyxlimportload_workbook#读取Excel文件wb=load_workbook("excel.xlsx")#选择表名为Sheet1的表ws=wb["Sheet1"]#合并单元格ws.merge_cells('A1:B1')#单元格居中ws['A1'].alignment=Alignment(horizontal="center",vertical="center")#保存Excelwb.save("excel.xlsx")12345678910111213Pandas.DataFramegroupby样式保存Excel假设原始表格是这样的abcA10A11A22B23B24C35X36X37然后想类似groupby([‘a’,‘b’])样式保存成ExcelabcA10122B234C35X367importnumpyasnpfromopenpyxlimportWorkbookfromopenpyxl.stylesimportAlignmentfromopenpyxl.utils.dataframeimportdataframe_to_rowsimportpandasaspdfromopenpyxl.worksheet.cell_rangeimportCellRangefromopenpyxl.worksheet.mergeimportMergedCellRangedefmerge_cells(df,key,output_path=None):"""key列去重并合并单元格并居中Args:dfataFrame输入表key:（多个）列名output_path:保存路径Returns:Workbook工作簿"""wb=Workbook()#创建工作簿ws=wb.active#获取第一个工作表#把key列调整到最前面，并进行排序col=keyifisinstance(key,list)else[key]set_col=set(col)columns=[*col,*(iforiindf.columnsifinotinset_col)]_df=df[columns]_df.sort_values(key,inplace=True)#将每行数据写入工作表中forrowindataframe_to_rows(_df,index=False,header=True):ws.append(row)align=Alignment(horizontal="center",vertical="center")#居中样式idx={-1,_df.shape[0]-1}fori,_inenumerate(col):c=_df[_].valuesidx.update(np.where(c[1:]!=c[:-1])[0])sorted_idx=sorted(idx)forstart,endinzip(sorted_idx[:-1],sorted_idx[1:]):#OpenPyXL序号从1开始，所以行序号需要+2ws.merge_cells(start_row=start+3,end_row=end+2,start_column=i+1,end_column=i+1)#仅需对合并单元格后的一个cell居中即可ws.cell(start+3,i+1).alignment=alignifoutput_path:#保存writer中的数据至excelwb.save(output_path)returnwbdefmerge_cells_big_data(df,key,output_path=None):"""合并数据过大导致原本函数性能过低key列去重并合并单元格并居中Args:dfataFrame输入表key:（多个）列名output_path:保存路径Returns:Workbook工作簿"""wb=Workbook()#创建工作簿ws=wb.active#获取第一个工作表#把key列调整到最前面，并进行排序col=keyifisinstance(key,list)else[key]set_col=set(col)columns=[*col,*(iforiindf.columnsifinotinset_col)]_df=df[columns]_df.sort_values(key,inplace=True)#将每行数据写入工作表中forrowindataframe_to_rows(_df,index=False,header=True):ws.append(row)align=Alignment(horizontal="center",vertical="center")#居中样式merged_cells=[]idx={-1,_df.shape[0]-1}fori,_inenumerate(col):c=_df[_].valuesidx.update(np.where(c[1:]!=c[:-1])[0])sorted_idx=sorted(idx)forstart,endinzip(sorted_idx[:-1],sorted_idx[1:]):#OpenPyXL序号从1开始，所以行序号需要+2cr=CellRange(min_col=i+1,min_row=start+3,max_col=i+1,max_row=end+2)range_string=cr.coordmcr=MergedCellRange(ws,range_string)merged_cells.append(mcr)ws._clean_merge_range(mcr)#仅需对合并单元格后的一个cell居中即可ws.cell(start+3,i+1).alignment=alignws.merged_cells=merged_cellsifoutput_path:#保存writer中的数据至excelwb.save(output_path)returnwbif__name__=='__main__':df=pd.DataFrame({"a":["A","A","A","B","B","C","X","X"],"b":[1,1,2,2,2,3,3,3],"c":range(8),})df.to_csv('input.csv',index=False)wb=merge_cells_big_data(df,['a','b'],'out.xlsx')123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117