Python 遍历文件夹下所有文件内容及写入Excel

需求：统计文件夹下所有日志内容，并将数据导出Excel

#!/usr/bin/env python
# encoding: utf-8

import os
import re
from openpyxl import Workbook
from openpyxl.styles import Alignment
from openpyxl.styles import Font
import time

# 遍历指定目录，显示目录下的所有文件名
def eachFile():
    path = os.getcwd()
    files = os.listdir(path)
    print('文件:', files)
    datas = {}
    data_appid = {}
    for file in files:
        if not file.startswith('log.'):
            # print(file, 'is not log file')
            continue

        data_file = {}
        with open(file, 'r') as logfile:
            lines = logfile.readlines()
            for line in lines:
                result = re.split(r'[\t]', line)
                body_bytes = int(result[6])
                body_gb = round(body_bytes/1024/1024/1024, 5)
                status_http = int(result[7])
                cmd = result[11]
                device_info = result[14]
                appid = result[15]
                if (status_http == 200) and ((cmd == 'Upload') or (cmd == 'UploadSlice')) and (len(appid) != 0):
                    if (((device_info.find('#iOS#') != -1) or (device_info.find('#android#') != -1)) and device_info.endswith('###')):
                        # 单个文件数据
                        if appid in data_file:
                            data_file[appid] = data_file[appid] + body_gb
                        else:
                            data_file[appid] = body_gb
                        # 所有文件数据
                        if appid in data_appid:
                            data_appid[appid] = data_appid[appid] + body_gb
                        else:
                            data_appid[appid] = body_gb

                        print('AppId:', appid, ' Size:', body_gb, ' Cmd:', cmd, ' DeviceInfo:', device_info)
                            
        if len(data_file) != 0:
            logfile.close()                
            # print('\n\nfile appid is:\n\n', data_file)
            # 使用sum()函数求和
            file_gb = sum(value for value in data_file.values() if isinstance(value, float))
            print('\n\n')
            print(file,'文件大小是:', file_gb, 'GB')
            datas[file] = file_gb

    if len(datas) != 0:                
        print('\n\nfile is:\n\n', datas)
        # 使用sum()函数求和
        total_gb = sum(value for value in datas.values() if isinstance(value, float))
        print('\n\n文件总大小是:', total_gb, 'GB')

        if len(data_appid) != 0:                
            print('\n\nappid is:\n\n', data_appid)
            bizs = readBizFile()
            saveExcel(data_appid, total_gb, bizs)


def saveExcel(data, size, bizs):
    # 新建一个工作簿
    wb = Workbook()
    # 选择一个工作表
    ws = wb.active
    ws.title = 'wos-sdk-log'

    ws.column_dimensions['A'].width = 20
    ws.column_dimensions['B'].width = 25
    ws.column_dimensions['C'].width = 25
    ws.column_dimensions['D'].width = 15
    ws.column_dimensions['E'].width = 30

    # 设置第1行高度为30
    ws.row_dimensions[1].height = 30

    font = Font(
        name='微软雅黑',  # 字体
        size=14,         # 字体大小
        color='000000',  # 字体颜色，用16进制rgb表示
        bold=True,       # 是否加粗，True/False
        italic=False,    # 是否斜体，True/False
        strike=None,     # 是否使用删除线，True/False
        underline=None,  # 下划线, 可选'singleAccounting', 'double', 'single', 'doubleAccounting'
    )

    font_body = Font(
        name='微软雅黑',  # 字体
        size=12,         # 字体大小
        color='000000',  # 字体颜色，用16进制rgb表示
        bold=False,      # 是否加粗，True/False
        italic=False,    # 是否斜体，True/False
        strike=None,     # 是否使用删除线，True/False
        underline=None,  # 下划线, 可选'singleAccounting', 'double', 'single', 'doubleAccounting'
    )

    align = Alignment(horizontal='center', vertical='center')

    titles = { 'A1' : 'AppId', 'B1': 'Name', 'C1': 'BizName', 'D1': 'Size(GB)', 'E1': '描述'}
    
    for k, v in titles.items():
        ws[k] = v
        ws[k].font = font
        ws[k].alignment = align


    # 第一列
    keys = list(data.keys())
    keys_len = len(keys)
    for i in range(0, keys_len):
        idx = 2 + i
        # 第一行高度
        ws.row_dimensions[idx].height = 25
        key = keys[i]
        ws['A' + str(idx)] = key
        ws['A' + str(idx)].font = font_body

        data_biz = bizs[key]
        len_data_biz = len(data_biz)

        if len_data_biz == 0:
            continue

        # name
        if len_data_biz > 1:
            biz_name = data_biz[1]
            ws['B' + str(idx)] = biz_name
            ws['B' + str(idx)].font = font_body

        # biz_name
        if len_data_biz > 2:
            biz_name = data_biz[2]
            ws['C' + str(idx)] = biz_name
            ws['C' + str(idx)].font = font_body

        # 描述
        if len_data_biz > 5:
            biz_name = data_biz[5]
            ws['E' + str(idx)] = biz_name
            ws['E' + str(idx)].font = font_body    
    
    # Size
    values = list(data.values())
    for i in range(0, len(values)):
        idx = 2 + i
        ws['D' + str(idx)] = values[i]
        ws['D' + str(idx)].font = font
        # ws['D' + str(idx)].alignment = align

    # 最后一行
    ws.append(['', '', '总大小:', size])
    last_len = 2 + keys_len
    ws.row_dimensions[last_len].height = 30
    ws['C' + str(last_len)].font = font
    ws['D' + str(last_len)].font = font
    ws['C' + str(last_len)].alignment = align
    # ws['D' + str(last_len)].alignment = align
    
    datetime = time.strftime("%Y.%m.%d.%H.%M", time.localtime())
    # 保存工作簿
    wb.save('wos-log-%s.xlsx' %(datetime))
    

def readBizFile():
    data_biz = {}

    with open('wos.txt', 'r') as logfile:
        lines = logfile.readlines()
        for line in lines:
            result = re.split(r'[\t]', line)
            data_biz[result[0]] = result
         
    return data_biz         


if __name__ == '__main__':
    eachFile()