python打开操作写入excel操作

python写的一个读取，操作，写入excel的框架

1、读取：从文件夹中批量读入多个需要进行相同操作的excel文件，把每一列都生成字典里的一个成员
key：每一列的第一行，也就是标题，所以对于数据要求是需要有标题的
value：除第一行外的这一列生成的列表

2、操作：对上面生成的字典进行操作，传入字典，具体操作自定义。（本文以两列数据，一列作为x轴，一列作为y轴曲线拟合，并计算曲线与x轴的面积）

3、写入：生成的数据写入excel

import xlrd
import xlwt
from pathlib import Path
import numpy as np
from scipy.integrate import trapz


def read_xls_as_dict(file_name):       #读取
    workbook = xlrd.open_workbook("{0}".format(file_name))
    sheets = workbook.sheet_names()
    worksheet = workbook.sheet_by_name(sheets[0])
    fatherList = list()
    for i in range(worksheet.ncols):
        List = list(worksheet.col_values(i))
        fatherList.append(List)
    dict_fatherList = {}
    length_fatherList = len(fatherList)
    for index in range(length_fatherList):
        List = list()
        for j in range(len(fatherList[index])):
            if j < 1:
                continue
            List.append(fatherList[index][j])
        dict_fatherList[fatherList[index][0]] = List
    return dict_fatherList


def calculate(List1, List2):
    x = np.array(List1)
    y = np.array(List2)
    area = trapz(y, x)
    return area

def class_to_calculator(dictTocal):     #操作
    dict_result = {}
    List_keys = list(dictTocal.keys())
    List_values = list(dictTocal.values())
    for i in range(len(List_values)):
        if i < 1:
            continue
        result = calculate(List_values[0], List_values[i])
        dict_result["{0}and{1}".format(List_keys[0], List_keys[i])] = result
    return dict_result


def wirte_xls(path, dict):             #把结果写入
    row = 0
    col = 0
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet("result")
    for key in dict.keys():
        sheet.write(row, col, key)
        sheet.write(row, col + 1, dict[key])
        row += 1
    workbook.save(path)

if __name__ == '__main__':
    file_path = 'Your filePath'
    path = Path(file_path)
    for file in path.iterdir():
        file_name = file_path + file.name
        dict_fatherList = read_xls_as_dict(file_name)
        dict_result = class_to_calculator(dict_fatherList)
        wirte_xls("{0}result.xls".format(file_name), dict_result)