python遍历excel_python遍历文件读取并写结果到excel

简单demo，记录一下方便以后使用

# -*- coding: utf-8 -*-

# encoding:utf-8

import os

import xlsxwriter

source_file_path = 'C:\\Users\\yuhao\\Desktop\\demo'

excel_path = source_file_path + '\\demo.xlsx'

wb2007 = xlsxwriter.Workbook(excel_path)

format_top1 = wb2007.add_format(

{'border': 2, 'bold': True, 'align': 'center', 'font_size': 12, 'top': 2, 'left': 2, 'right': 2,

'valign': 'vcenter'}) # 第一行标题

format_top = wb2007.add_format(

{'border': 2, 'bold': True, 'text_wrap': 1, 'align': 'center', 'font_size': 11, 'valign': 'vcenter'}) # 第二行标题

format_other = wb2007.add_format({'border': 1, 'valign': 'vcenter', 'text_wrap': 1, 'align': 'center'}) # 正文

# 遍历文件夹

def walk_file(file):

for root, dirs, files in os.walk(file):

# root 表示当前正在访问的文件夹路径

# dirs 表示该文件夹下的子目录名list

# files 表示该文件夹下的文件list

for f in files:

file_path = os.path.join(root, f)

print(file_path)

c = 0

# key : hour , value : 接收机列表

rec_time = {}

day = ''

with open(file_path, 'r', encoding='utf-8') as f1:

lines = f1.readlines()

for line in lines:

if line.find('xxx') != -1:

temp = line.replace(' ', '')

temp = temp.split(' ')

day = temp[0]

hour = temp[1].split(':')[0]

rec = temp[2]

c += 1

if rec_time.get(hour) is None:

rec_time[hour] = []

rec_time[hour].append(rec)

if rec_time:

write_excel(day, rec_time)

def write_excel(day, rec_time):

global wb2007

worksheet2007 = wb2007.add_worksheet(day)

worksheet2007.write(0, 0, '小时', format_top1)

worksheet2007.write(0, 1, '数量', format_top1)

worksheet2007.write(0, 2, 'ID', format_top1)

m = 1

for k, v in rec_time.items():

print(str(day) + '\t' + str(k) + '\t' + str(len(v)))

worksheet2007.write(m, 0, k, format_other)

worksheet2007.write(m, 1, len(v), format_other)

worksheet2007.write(m, 2, ''.join(v), format_other)

m += 1

walk_file(source_file_path)

wb2007.close()