python 读写excel

概要

通过pandas操作excel示例，读取sheet1，加工后写入sheet2
环境：pycharm+python3.8

代码示例

import pandas as pd
import os


def mockdata(excelfile):
    if not os.path.exists(excelfile):
        print(f"{excelfile} not exists.")
        return

    df = pd.read_excel(excelfile, sheet_name='Sheet1', header=0)
    datalist = []
    for ind, row in df.iterrows():
        item={'C':float(row['A'])+1,'D':float(row['B'])+1}
        datalist.append(item)
    df_new = pd.DataFrame(datalist, columns=['C','D']);
    with pd.ExcelWriter(excelfile, mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
        df_new.to_excel(writer, sheet_name='Sheet2', index=False, header=True)
    print(excelfile+" is finish.")


if __name__ == '__main__':
    mockdata("data/20240813.xlsx")

示例解读

1.通过read_excel读取excel到pd，sheet1包含列A和B

2.在数据行上分别+1，写入sheet2，sheet2包含列C和D

小结

具体业务结合实际，可以通过pd做分析