-
2020-04-23 23:17: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的方式;生成的新表:
更多相关内容 -
Excel表格匹配合并
2021-06-02 15:45:52在日常的工作中,免不了存在多个表格根据相同数据匹配合并的情况,很多人会因为复杂的公式导致匹配失败或错误。接下来,我将用一个简单的方式完成这一个任务。 1、打开网址www.excelutil.com 2、 ...在日常的工作中,免不了存在多个表格根据相同数据匹配合并的情况,很多人会因为复杂的公式导致匹配失败或错误。接下来,我将用一个简单的方式完成这一个任务。
1、打开网址www.excelutil.com
2、选择匹配合并
3、上传左文件和右文件
这两个文件是用来进行匹配合并的源文件。
4、选择匹配条件的关键字
根据需要匹配的内容,选择相应的关键字,这样就可以通过关键字将两个表格的数据关联到一起。
5、拣选匹配内容
根据关键字将匹配内容关联出来之后,我们可以看到数据的匹配情况:
a、黑色数字表示当你导出文件,如果以左文件的内容参照进行导出,则会导出多少条数据;
b、绿色数字表示,如果你以左文件参照进行导出,那么右文件中有多少条数据是与左文件匹配成功的;
c、红色数字表示,如果你以左文件作为参照进行导出,右文件中匹配成功的数据中有存在多少条数据有重复的内容。
当存在匹配时有重复数据的情况,则默认获取第一条匹配成功的数据内容。
以上就是两个表格匹配合并的操作流程,如果想要进行匹配操作的,赶紧试一下吧!
-
不加班的秘密——按标签自动匹配数据,Excel多表收集利器
2021-08-31 15:54:50我们会发现分散在多个工作表中的数据被合并在一起了,而且根据每列的标签自动匹配。 第五步:这时只需要点击【Close & Load】就可以大功告成了。 然后得到的数据如下图所示: 最后,你会发现,汇总表的数据能自动...下图为某企业不同月份的销售数据,分别放置在不同的工作表中。现需要将所有表格中
的数据合并到一个工作表中,如果快速实现?
第一步:点击【Data】-【Get Data】-【From File】-【From Workbook】然后选取文件所在位置。
第二步:勾选【Select multiple items】然后勾选需要合并的数据表,点击【Edit】。
第三步:勾选择表1【Jan】,点击【Combine】-【Append Queries】。
第四步:点击【Three or more tables】将要追加的表选中后点击【Add】到右侧列表中。
我们会发现分散在多个工作表中的数据被合并在一起了,而且根据每列的标签自动匹配。
第五步:这时只需要点击【Close & Load】就可以大功告成了。
然后得到的数据如下图所示:
最后,你会发现,汇总表的数据能自动根据B-E列的字段名称自动匹配。如果对原始分表的数据进行修改或添加,然后点击【Save】-【Refresh All 】,即可实现快速收集和更改。
-
Python自动化办公:27行代码实现将多个Excel表格内容批量汇总合并到一个表格
2022-04-13 15:24:27这么好用的技能,必须分享给大家,话不多说,咱们直接开始! 准备工作 咱们需要先准备表格数据,会爬虫的兄弟可以自己爬一点,不会的,可以找我直接拿数据。 电脑端左侧获取,手机端最最最下方获取 表格内数据 我...序言
老板最近越来越过分了,快下班了发给我几百个表格让我把内容合并到一个表格内去。
还好我会Python,分分钟就搞定了,这要是换个不会Python的,不得加班到第二天天亮去了~
这么好用的技能,必须分享给大家,话不多说,咱们直接开始!准备工作
咱们需要先准备表格数据,会爬虫的兄弟可以自己爬一点,不会的,可以找我直接拿数据。
电脑端左侧获取,手机端最最最下方获取
表格内数据
我这里只做展示,所以只用了五个表,咱们今天是将市级合并为省级。
本文思路
- 将当前文件夹下所有的 excel 汇总到 广东省.xlsx
- 添加一个新的字段 城市,字段内容为商铺所在城市,这个字段放在最前面;
- 星级为 star_0 的数据全部不要
- 只要一条数据中有三个字段为空字段,整条数据都不需要;
- 将价格中的 ¥ 符号去掉
代码实现
全部代码都分享给大家,咱不喜欢藏着掖着。
import glob import openpyxl workbook = openpyxl.Workbook() sheet_total = workbook.active sheet_total.append(['城市', '门店名称', '星级', '星级得分', '点评总数', '人均消费', '口味', '环境', '服务', '链接网址', '分类', '商圈', '详细地址', '推荐菜']) def count_none(line): """返回空内容的数据""" count = 0 for d in line: if not d: count += 1 return count filenames = glob.glob('*/*.xlsx') for filename in filenames: # print(filename) city = filename.split('.')[0].split('\\')[-1] workbook_temp = openpyxl.load_workbook(filename) sheet = workbook_temp.active for row in sheet.iter_rows(min_row=2, min_col=1, max_col=sheet.max_column, max_row=sheet.max_row): row_data = [col.value for col in row] if row_data[1] == 'star_0': continue # 定义一个方法判断空字段的数量 if count_none(row_data) >= 3: continue # 去掉平均价格中的 ¥ if row_data[4]: row_data[4] = row_data[4].strip('¥') row_data.insert(0, city) # print(row_data) sheet_total.append(row_data) # break # 调试只处理一个 workbook.save('广东省.xlsx')
效果
还是刚出炉的,非常新鲜。
这我做了筛选,不然全是显示一个地方了。
可以看到,数据成功的合并到一个表格去了。
喜欢的小伙伴记得点赞收藏呀~
关注我分享更多技术干货
直接拿走代码等于白嫖,点赞收藏才是真情…
你们的支持是我更新的动力! -
excel各类型表格汇总
2013-12-19 18:36:44本项功能可以根据关键列合并两个工作表的列,并且不要求两个工作表的行按照顺序一一对应,程序可以根据关键列的值自动匹配对应行,然后把两个工作表中的行拼接成一行。 6)提取两工作表共有的行 本项功能可以将2个... -
VLOOKUP函数将一个excel表格的数据匹配到另一个表中
2017-09-13 16:48:00sklearn实战-乳腺癌细胞数据挖掘 (博主亲自录制视频) ...以上就是VLOOKUP函数将一个excel表格的数据匹配到另一个表中方法介绍,希望能对大家有所帮助! python风控评分卡建模和风控常识 ... -
pandas自动化实战小任务1--对excel表格分 供应商 付款年月 进行汇总(涉及根据即有相同也有不同的index的...
2020-12-29 21:18:54inplace=True) moneies.append(temp) 汇总, 看不懂的请点击:方法9,字典拆分 dic = {**moneies[0].to_dict(), **moneies[1].to_dict(), **moneies[2].to_dict(), **moneies[3].to_dict()} total = DataFrame(dic) ... -
Python 自动整理 Excel 表格
2019-11-27 08:20:00作者:TED 来源:TEDxPY相信有不少朋友日常工作会用到 Excel 处理各式表格文件,更有甚者可能要花大把时间来做繁琐耗时的表格整理工作。最近有朋... -
如何自动按名字抽取多个 Excel 数据后汇总到一起
2021-01-11 16:07:22某文件目录里有多个企业资产负债表,其中一个如下图所示: 现在需要从这些资产负债表中提取某些数据进行财务分析,目标表如下图所示: 第1列是需要提取的数据名称,各资产负债表中的数据分别依次提取到后面的... -
自动建立excel工作表名称汇总目录并超链接工作表
2013-07-29 14:52:15自动建立excel已有工作表名称汇总目录并超链接所有工作表 -
Python自动化处理Excel报表,我的工作更轻松了!
2020-11-30 04:10:03没有转义的Excel!Excel无处不在,即使我们使用了Python之类强大的辅助工具,依然无法摆脱Excel。...使用Python自动化Excel报表你还在每天做着没完没了的Excel报表吗?你还在为不同的客户做着相同的事情吗?让Python... -
自动化办公之excel教程(2):各种数据的输入,自动填充数据,表格操作小技巧
2020-09-14 14:10:47我选择格式,结果 传送门链接: 自动化办公之excel教程(1):工作薄,工作表,单元格基本操作 自动化办公之excel教程(3):数据编辑操作,表格的美化操作,应用表格样式和单元格样式,制作报销汇总单 自动化办公... -
excel匹配_Excel教程:明明看到1了,为什么查找不到?
2020-10-22 05:50:2602 “单元格匹配” 在实际的工作中,表格往往复杂多样,在进行选择替换时会遇到各种各样的问题。 如下图所示的区域3表格中,我要将部门列的“销售”改为“销售部”。 按替换快捷键Ctrl+H,在弹出的对话框中,“查找... -
自动化测试Excel记录表格
2019-12-01 15:49:57自动化测试Excel记录表格 import openpyxl from openpyxl.workbook import Workbook from openpyxl.styles import Font import os import time import imghdr import logging logger = logging.getLogger("log") ... -
能不能用python,根据姓名,自动匹配后导入我的表里面
2021-09-26 09:26:46最近在学习python,因为操作问题想问问python有没有这种操作方式~ 我的工作:每个月都要汇总60个科室的绩效表格,共计500个人。... 我想的操作:能不能用python,根据姓名,自动匹配后导入我的表里面。 -
java对比两个excel或者一张excel和数据库的一张表的数据是否相同,两张excel表格匹配相同的数据库
2021-04-23 22:33:09excel自动从数据库匹配相应的数据vlookup函数:vlookup(lookup_value,table_array,col_index_num,[range_lookup]) 第一个:lookup_value表示你想据哪个值来查找你想要的数据,比如:可以根据值a可以找到b,那么... -
VBA多条件选择及自动填表及计算汇报
2022-02-11 12:11:38这里再选择J列使用公式的行替换#value为VA,不然后面再次循环J列会报数据TYPE不匹配的问题。 '1.计算G列和I列不同值的个数,要求VALUE() =VALUE()值为FALSE的个数 Cells.Select Application.CutCopyMode = False ... -
EXCEL数据报表完成财务表格汇总数据查询
2020-03-19 14:17:20excel财务报表条件匹配,数据汇总项目要求主要运用的excel公式具体实现时的思路按条件汇总思路解决困难项标签汇总,用标签汇总其他数据遇到的操作失误 项目要求 接手一个excel项目,为一家公司完成财务报表里的具体... -
Excel(或WPS)使用vlookup匹配并合并表的方法与实例
2020-12-08 07:57:58如下表格,要求将前两个表格中的数学和英语成绩汇总到总成绩单里。可以使用vlookup函数快速的实现。操作步骤如下:1.在“总成绩单”表的B2中输入“=vl”,可以快速找到vlookup函数2.填写参数第一个参数是要比对的值A2... -
python整理excel数据-Python 自动整理 Excel 表格
2020-11-01 12:06:38最近有朋友问可否编程来减轻表格整理工作量,今儿我们就通过实例来实现 Python 对表格的自动化整理。首先我们有这么一份数据表 source.csv:我们要做的是从上表中提取数据,来生成一份符合以下要求的表格:按照以下... -
字典写入excel_使用Python扫描邮件/填写Excel表格实现办公自动化
2020-10-21 01:34:36关键词:Python, 邮件扫描, yaml配置文件,邮件附件编解码,Excel自动处理一 、前言 作为一只二级市场资管狗,平时少不了向各相关方汇报产品数据,不论领导、风控部门同事、市场人员或者客户,经常会要求我们按照... -
excel乘法公式怎么输入_你加班3小时做的Excel表格,我只需1分钟!(建议收藏)
2020-10-22 05:45:55VLOOKUP查询调用精确匹配 二、VLOOKUP函数模糊匹配,返回你需要的区间数据 比如根据考核等级确定奖金比例,对于这种在区间范围内匹配调用数据,就要用到VLOOKUP函数的模糊匹配了,这个功能完全可以替代掉IF函数的... -
excel VBA自动化 - 多个工作簿自动合并到一个工作簿
2022-03-31 10:26:51多个excel表自动合并到同一表格,该段vba代码的逐句拆解 -
EXCEL函数使用——TEXTJOIN()符合条件的多个单元格内容汇总到一个单元格中
2021-07-26 10:21:11TEXYTJOIN(),这是wps独有的函数,主要作用是将符合条件的多个单元格内容汇总到一个单元格中去。(excel中没找到类似的函数,如有,欢迎留言指导。) 关于汇总的方法平时也经常接触,但大多都是汇总数值为主,或者... -
用Python写一个Excel汇总和比对小程序
2021-11-15 10:52:18读入数据总结欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右... -
pandas 表格操作
2021-01-05 15:25:03pandas 表格操作 假设有一张表格,为它赋值为data,data里面有一个列索引为"order_time",类型为字符串 一:时间处理 注:以下的操作都是针对时间类型来处理的 to_datetime 将其它类型转换为时间类型 # 将字符串... -
一文了解什么是倾向得分匹配PSM?
2020-12-22 01:54:48倾向得分匹配,在医学研究及计量经济领域都有广泛应用,其目的在于减少数据偏差和混杂因素的干扰。很多统计软件都支持PSM,如SPSS和stata。SPSSAU最新版本也提供了倾向得分匹配。下面我们就一起来了解下,究竟什么是... -
Python自动化应用案例:一键生成工厂物料采购订单(精益办公案例之三)
2021-12-22 23:08:20本文描述一个Excel办公自动化的实践示例:模拟生产企业在获取客户订单后,根据相关的数据和信息,通过编程的方式(Python语言),快速建立采购订单的过程。这种精益办公的方式将大幅精简在实际工作中的数据重复输入... -
57个机器人流程自动化应用场景:RPA深入指南[2019更新]
2019-03-01 12:59:01之前我们解释过机器人过程自动化。RPA有一个奇特的名字,市场上超过50个解决方案提供商, 但与我交谈过的人很少能回答可以应用RPA的确切领域。因此,我们准备了所有RPA用例/应用领域的最完整列表,并将它们分为5个大...