Python xlwings pandas获取Excel中某列的数据的最大值和最小值

1、安装xlwings和pandas

pip install xlwings
pip install pandas

2、单个文件处理

import os
import xlwings as xw
import pandas as pd
file_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.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()

3、获取目录中多个文件批量处理

import os
import xlwings as xw
import pandas as pd
file_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()