1、安装xlwings和pandas
pip install xlwings
pip install pandas
2、单个文件处理
import osimport xlwings as xwimport pandas as pdfile_path=r'文件名'file_list=os.listdir(file_path)app=xw.App(visible=False,add_book=False)workbook=app.books.open(file_path)workbook_sheet=workbook.sheetsfor sheet in workbook_sheet: values=sheet.range('A1').expand('table') data=values.options(pd.DataFrame).value #获取行数和列数 rc=sheet.range(1, 1).expand().shape row=rc[0] column=rc[1] sheet.cells(row+1,column).value=data.max()["列名"] sheet.cells(row+1,column-1).value="最大值" sheet.cells(row+2,column).value=data.min()["列名"] sheet.cells(row+2,column-1).value="最小值"workbook.save()workbook.close()app.quit()
3、获取目录中多个文件批量处理
import osimport xlwings as xwimport pandas as pdfile_path=r'文件目录'file_list=os.listdir(file_path)app=xw.App(visible=False,add_book=False)for i in file_list: if i.startswith('~$'): continue file_paths_operation=os.path.join(file_path,i) workbook=app.books.open(file_paths_operation) workbook_sheet=workbook.sheets for sheet in workbook_sheet: values=sheet.range('A1').expand('table') data=values.options(pd.DataFrame).value #获取行数和列数 rc=sheet.range(1, 1).expand().shape row=rc[0] column=rc[1] sheet.cells(row+1,column).value=data.max()["列名"] sheet.cells(row+1,column-1).value="最大值" sheet.cells(row+2,column).value=data.min()["列名"] sheet.cells(row+2,column-1).value="最小值" workbook.save() workbook.close()app.quit()
