多个Excel数据整合

一.描述

将多个Excel中的数据整合到一个Excel中

二.代码参考

import os
import glob
import openpyxl

file_path = 'C:\\Users\\only\\python\\excel\\test'

xlsx_files = glob.glob(os.path.join(file_path, '*.xlsx'))
# sorted(xlsx_files, key=str.lower)
# print(xlsx_files)

wb = openpyxl.load_workbook(xlsx_files[0])
ws = wb.active
ws.title = 'merged result'

for filename in xlsx_files[1:]:
    workbook = openpyxl.load_workbook(filename)
    sheet = workbook.active
    for row in sheet.iter_rows(min_row=2):
        values = [cell.value for cell in row]
        ws.append(values)

wb.save('merged_form.xlsx')
print('合并完毕')

三.openpyxl基础

import openpyxl

# cell对象属性
# cell.row 行
# cell.column 列
# cell.value 值
# cell.cordinate 坐标


def process_sheet(sheet):
    sum_column = sheet.max_column + 1
    avg_column = sheet.max_column + 2
    # 此处可选择按行或者按列进行遍历，iter_rows iter_cols
    for row in sheet.iter_rows(min_row=2, min_col=3):
        score = [cell.value for cell in row]
        # print(score)
        sum_score = sum(score)
        avg_score = sum_score/len(score)
        # print(sum_score, avg_score)
        # print(row[0].row)
        sheet.cell(row=row[0].row, column=sum_column).value = sum_score
        sheet.cell(row=row[0].row, column=avg_column).value = avg_score
    sheet.cell(row=1, column=sum_column).value = 'sum'
    sheet.cell(row=1, column=avg_column).value = 'avg'


def main():
    wb = openpyxl.load_workbook('score.xlsx')
    # workbook对象属性
    # print(wb.worksheets)
    # print(wb.active)
    # print(wb.read_only)
    # print(wb.encoding)
    # print(wb.properties)
    sheet = wb.get_sheet_by_name('student')
    # worksheet对象属性
    # print(sheet.title)
    # print(sheet.dimensions)
    # print(sheet.max_row)
    # print(sheet.min_row)
    # print(sheet.max_column)
    # print(sheet.min_column)
    # print(sheet.rows)
    # print(sheet.columns)
    # print(sheet.values)
    # print(sheet.iter_rows)
    # print(sheet.iter_cols)
    process_sheet(sheet)
    wb.save('new_score.xlsx')




if __name__ == '__main__':
    main()