Pandas操作Excel表格---初级操作

读取表格

import pandas as pd

def get_excel_info(path, sheet="Sheet1", field="index", cols=[]):
    """
    :param path: 文件地址
    :param sheet: 选择要读取的分页名字，默认是Sheet1
    :param field: 值的下标字段
    :param cols: 选择读取的标题名
    :return: 带下标的json返回值
    """
    data = pd.read_excel(path, sheet_name=sheet, header=0, keep_default_na=False, usecols=cols)
    data = eval(data.to_json(orient="index", force_ascii=False))
    data1 = list(data.values())
    data2 = list(data)

    for i in range(len(data1)):
        data1[i][field] = data2[i]

    return data1

修改表格具体值

import openpyxl
    
def put(self):
        req = reqparse.RequestParser()
        req.add_argument("index")  # 下标
        req.add_argument("name")
        req.add_argument("yitu", default="{}")
        args = req.parse_args()

        for i in eval(args["yitu"]).values():
            value = str(i)

        index = int(args["index"]) + 2

        data = pd.read_excel(f"path/tasks/{args['name']}", header=0, keep_default_na=False)
        data_index = data.columns.get_loc("意图") # 获取具体标题的下标

        workbook = openpyxl.load_workbook(f"path/tasks/{args['name']}")
        worksheet = workbook["Sheet1"]

        worksheet.cell(row=index, column=data_index + 1).value = value
        workbook.save(f"path/tasks/{args['name']}")

        return jsonify({"code": 20000, "message": "ok"})

增加新值

pandas增加新值，会直接覆盖原文件数据，新建表格

def post(self):
        req = reqparse.RequestParser()
        req.add_argument("name")  # 文件名
        args = req.parse_args()

        data = get_excel_info(f"path/tasks/{args['name']}", cols=["groupid", "状态"])

        data = pd.DataFrame(data)

        # 循环迭代数据行并添加到工作表中
        res = [{"phone_id": x["groupid"].to_list(),
                "状态": x["状态"].to_list()} for k, x in
               data.groupby("groupid")]

        output_data = [{'phone_id': data['phone_id'][i],
                        '状态': data['状态'][i]} for data in res for i in
                       range(len(data['phone_id']))]

        df = pd.DataFrame(output_data)
        df = df.drop_duplicates(subset="phone_id", keep="first")  # 去重
        df.to_excel("path/handle/借款失败进度.xlsx", index=False)

openpyxl不会覆盖原数据，是在原有值的基础上追加数据

def add_to_data(ex_path_name, data, sheetname):
    """
    :param ex_path_name: 文件地址
    :param data: [[1, 2, 3], [4, 5, 6]]
    :param sheetname: 分页名
    :return:
    """

    wb = openpyxl.load_workbook(ex_path_name)
    ws = wb[sheetname]

    for x in data:
        ws.append(x)

    wb.save(ex_path_name)