Python办公自动化实践1:从多个excel表中提取数据并汇总到一个工作表页中,表格,抽取,sheet

Python办公自动化实践1:从多个excel表中提取数据并汇总到一个工作表页中,表格,抽取,sheet

发表时间:2020-04-26

问题:从当前目录或子目录中查询符合条件的excel表格,并从这些excel表格中抽取符合条件的行汇总到1个excel的sheet页中。
所有excel表格名字为:交付清单1、交付清单2,交付清单3……,格式也一样,样式如下:
 将类似多个excle表中,抽取序号中的N行(上图是1~4行)汇总到指定excel的sheet页中。
该表格在DataFrame中的显示方式如下:
在这里插入图片描述
一、希望得到的汇总表格为:
1、列头是:“系统名称、用途 、CPU 内存、操作系统、内网IP、 互联网IP 、交付时间 、到期时间”
注意:列头(columns header)是合并格。
2、删除掉A列,删除最后的空行
3、将交付时间,到期时间从datetime类型转变为字符串类型; #第三方模块datetime
4、将汇总表格中所有的单元格 字体,大小,字体是否据中等 全部调为一致;#第三方模块xlsxwriter

二、具体实现步骤如下:
1、查询符合条件的excel表; #第三方模块os
2、用pandas读取符合条件的表;
3、对读入后的excel表(dataframe)进行裁剪;
4、将多个excel表格汇总到单一excel的sheet页中;
5、将最终的sheet页再进行单元格格式调整,再重新输出新的sheet页;
该脚本涉及第三方模块有:pandas,os,datetime,xlsxwriter。

三、代码展现
1、查询符合条件的excel表
a、通过os.walk查询当前目录及子目录中的文件
b、在这些文件中筛选匹配“交付清单”的文件
c、将匹配文件和绝对路径“连接后”存放到列表中;

import numpy as np 
import pandas as pd
import xlsxwriter 
import os
from pandas import Series,DataFrame
from datetime import date,timedelta,datetime

path=r'D:\cloud_files'
path_out=r'D:\cloud_files\vmachines_list.xlsx'

substr='交付清单'
file_list=[]
for path_name,dirs,filename in os.walk(path):
    for files in filename:
        if files.find(substr)!=-1:
            file_list.append(os.path.join(path_name,files))

该段重点:
a、path_name 存放文件所在的绝对路径,filename 存放文件名;
b、files.find(substr) 匹配substr字符串的文件,若不匹配返回-1,
若匹配,返回该字符串在文件名中第一次匹配成功的位置。
c、os.path.join(path_name,files)将绝对路径与匹配的文件结合起来,再存放到file_list列表中。

file_list列表中的数据:

['D:\\cloud_files\\~$交付清单01.xlsx', 'D:\\cloud_files\\交付清单01.xlsx', 'D:\\cloud_files\\交付清单02.xlsx', 'D:\\cloud_files\\交付清单03.xlsx']

在交付清单01.xlsx 是打开的情况下,提交该段程序,将会出现~$交付清单01.xlsx。
表示该表被进程占用。

2、用pandas读取符合条件的表
a、通过pandas读取在file_list中的excel表格;
b、并将结果写入到vm_tmplist 临时列表中。

vm_tmplist=[]
for item in file_list:
    if '~$' not in item:
        temp=pd.read_excel(item,sheet_name='交付清单',skiprows=2,usecols='B:J')
        vm_tmplist.append(temp)

该段重点:
a、跳过excel中的前两行,并定义B到J列区域;

3、对读入后的excel表(dataframe)进行裁剪
a、选择合适的列头;
b、删除掉A列,删除最后的空行
c、将交付时间,到期时间从datetime类型转变为字符串类型;
转换的原因是xlsxwriter不支持对时间类型,index,columns类型的单元格进行格式化。
官网原文:It isn’t possible to format any cells that already have a format
such as the index or headers or any cells that contain dates or datetimes .

def trim_frame(df):
    df.columns=np.concatenate([df.columns[:3],df.iloc[0,3:5],df.columns[5:]])
    df=df.dropna(subset=['系统名称'])
    df=df.loc[(df['序号'].isin(range(1,10,1)))]
    df['交付时间']=pd.to_datetime(df['交付时间'],errors='coerce').dt.strftime('%Y-%m-%d')
    df['到期时间']=pd.to_datetime(df['到期时间'],errors='coerce').dt.strftime('%Y-%m-%d')
    return df
    
for value in vm_tmplist:
    df=trim_frame(value)
    vm_trimlist.append(df)

本段重点:
a、将前三列(0,1,2)的columns(列名)与第0行的第3、4列及columns第5列至最后一列合并;
通过numpy.concatenate函数合并
b、将“系统名称”列中空格(NaN)所在的行去掉;
c、选取“序号”列中数字为1~10所在的行;
d、将“交付时间”,“到期时间”两列datetime类型数据转换为string。
e、将“修剪好的”表存入vm_trimlist列表中;

4、将多个excel表格汇总到单一excel的sheet页中
a、调用xlsxwriter引擎;
b、从vm_list列表中选取“修剪好”的数据,按顺序写入到“虚机清单”sheet页中;
c、保存写好后的数据.writer.save()

def trimDfs_to_Excel(df_list, sheets, path_out):
    writer = pd.ExcelWriter(path_out,engine='xlsxwriter')   
    row = 0
    for dataframe in df_list:
        dataframe.to_excel(writer,sheet_name=sheets,startrow=row,startcol=0,index=False) 
        row = row + len(dataframe.index) + 1
    writer.save()
    
trimDfs_to_Excel(vm_trimlist,'虚机清单',path_out)

本段重点:
a、从vm_trimlist中读取数据(注:df_list是形参)
b、定义row=0,开始写的行startrow=row。
第2张表写入时,启始位置是第1张表的长度len(dataframe.index) 加1(第一行是从0开始的,所以加1,避免第2张表第一行冲掉第一张表最后一行)
c、to_excel(index=False)是防止将索引(index)写入excel表内。

5、将最终的sheet页再进行单元格格式调整,重新输出新的sheet页;
a、重新读取输出的表;
b、读取后,将该表删掉;因为xlsxwriter不能对原表进行修改;
c、对单元格及列头(columns header)进行格式化;
d、重新输出新表

def formatExcel(df,path):

    df=df.loc[df['序号'].isin(range(1,10,1))]
    os.remove(path)
    df.reset_index(drop=True,inplace=True)
    
    for i in df.index:
        df['序号'].at[i]=i+1

    writer = pd.ExcelWriter(path,engine='xlsxwriter')
    df.to_excel(writer,sheet_name='虚机清单',index=False)
    
    workbook=writer.book
    worksheet=writer.sheets['虚机清单']
    fmt_cell={'bold':False,'font_name':'微软雅黑','font_size':9,'align':'center','valign':'vcenter','border':0,'num_format':'#,##0'}
    fmt_header={'bold':True,'font_name':'微软雅黑','font_size':10,'align':'center','valign':'vcenter','border':0}

    cell_format=workbook.add_format(fmt_cell)
    header_format=workbook.add_format(fmt_header)

    worksheet.set_column('A:I',15,cell_format)

    for colx,value in enumerate(df.columns.values):
        worksheet.write(0,colx,value,header_format)

    writer.save()
    
formatExcel(pd.read_excel(path_out),path_out)
print ('Done!')

该段重点:
a、对序号列重新赋值新的数字,df[‘序号’].at[i]=i+1;
b、定义单元格(cell)格式;
c、定义列头的格式,通过worksheet.write的方式;

生成的新表:
在这里插入图片描述

  • 5
    点赞
  • 0
    评论
  • 31
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值