python 求excel每列平均值并追加写入到最后一行读取修改表格

古城微笑少年丶 2023-01-23 10:57 7阅读 0赞

![20210714172319371.png][]

# -*- encoding: utf-8 -*-
    # 读取excel表格
    import xlrd
    from xlutils.copy import copy
    
    ## 第一步，读取
    # excel表格在哪里
    filename = "./students.xls"
    workbook = xlrd.open_workbook(filename)
    sheetnames = workbook.sheet_names()
    sheetwork = workbook.sheet_by_name(sheetnames[0])
    
    # 行数
    nrows = sheetwork.nrows
    # 列数
    ncols = sheetwork.ncols
    print("nrows", nrows)
    print("ncols", ncols)
    
    allData_list = []
    for i in range(nrows):
        new_row_list = []
        for j in range(ncols):
            data = sheetwork.cell_value(i, j)
            new_row_list.append(data)
        allData_list.append(new_row_list)
    
    print(allData_list)
    
    
    # 第二步，求平均
    # 首先得知道你从哪里开始计数
    # 参数初始化
    start_r_idx = 1
    start_c_idx = 1
    
    # 求每列平均
    all_avg_per_col = {}
    
    for m_col in range(start_c_idx, ncols):
        one_col_sum = 0
        for m_row in range(start_r_idx, nrows):
            one_col_sum += allData_list[m_row][m_col]
        one_col_avg = one_col_sum / (nrows - start_r_idx)
        print(one_col_avg)
        all_avg_per_col[m_col] = one_col_avg
    
    print(all_avg_per_col)
    
    # 第三步，输出保存
    
    
    # 找到需要更该的xls
    # 对数据表格进行复制
    old_content = copy(workbook)
    # 定位到Sheet1表
    ws = old_content.get_sheet(0)
    # 在sheet1表中写入内容
    ws.write(nrows, 0, 'avg')
    for m_col in range(start_c_idx, ncols):
        """对excel进行修改/添加内容"""
        ws.write(nrows, m_col, all_avg_per_col[m_col])
    
    # 对修改后的内容进行保存
    old_content.save(filename)

# ![20210714194510143.png][] #

[https://www.cnblogs.com/Teachertao/p/10426265.html][https_www.cnblogs.com_Teachertao_p_10426265.html]

# [https://www.bilibili.com/video/av838723734][https_www.bilibili.com_video_av838723734] #

[20210714172319371.png]: /images/20221004/50e1004d5be54679bc8758affcd689ec.png
[20210714194510143.png]: /images/20221004/bf0928add6714d17a41312ed0b3d5f26.png
[https_www.cnblogs.com_Teachertao_p_10426265.html]: https://www.cnblogs.com/Teachertao/p/10426265.html
[https_www.bilibili.com_video_av838723734]: https://www.bilibili.com/video/av838723734