影刀RPA新手教程：财务报表自动汇总完全指南——多Excel合并数据透视与自动发邮件

影刀RPA新手教程财务报表自动汇总完全指南——多Excel合并数据透视与自动发邮件作者林焱我第一次用影刀RPA做财务报表汇总是要把15个子公司的Excel报表合并成一份集团总表然后生成数据透视表最后通过邮件发给财务部。手动做要花2天用影刀RPA写了个流程40分钟全部搞定。这篇文章把我做财务报表自动化踩过的坑全部告诉你。一、认识影刀与安装配置去影刀RPA官网下载安装包安装时勾选所有插件。安装完成后打开软件界面分为三块左边是指令面板中间是流程编辑画布右边是属性配置面板。做财务报表自动化要用到Excel分类下的指令这个指令在社区版里就有不需要升级到付费版。但如果你的流程指令数会超过100条建议直接用创业版不然写到一半发现指令数不够用就很尴尬。新建应用时选择Windows应用因为我们要操作本地的Excel文件。给应用起名叫做财务报表自动汇总器方便以后在应用列表里找到它。二、元素定位四合一找准Excel里的每一个单元格Excel自动化最难的不是读写数据而是定位到具体的工作表、单元格、按钮这些元素。影刀RPA提供了四种定位方式。元素捕获点击指令面板的捕获元素鼠标变成十字准星移到Excel的任意位置点击。我第一次捕获Excel里的单元格发现捕获到的是整个Excel窗口不是里面的单元格。后来才知道要等鼠标移动到单元格边缘出现绿色边框时再点击才能准确捕获到单元格元素。XPath语法6种最常用写法//*[text()保存] 匹配文字等于保存的任何元素 //button[idsaveButton] 匹配id为saveButton的button元素 //div[contains(class,excel-cell)] 匹配class包含excel-cell的div元素 //input[typetext and namecellValue] 多条件匹配  //*[starts-with(id,cell_)] id以cell_开头的元素 //table//tr[position()1]/td[1] 表格中从第二行开始的第一列CSS选择器语法8种最常用写法#saveButton id选择器 .excel-cell .cell-value 层级选择器 input[typetext] 属性选择器button:first-child 伪类选择器tr:nth-child(odd)奇数行选择器 *[class^cell_] 属性开头匹配 *[class$_value] 属性结尾匹配 *[class*button] 属性包含匹配XPath和CSS选型指南XPath能向上遍历父节点CSS只能向下遍历。如果你要定位的元素没有唯一标识但它的父元素有用XPath的…可以很方便地到父元素再找兄弟元素。XPath支持text()函数直接匹配文字内容CSS不支持。比如Excel里有很多个保存按钮用XPath的text()可以精确定位到某一个。但CSS选择器的执行速度比XPath快。如果你要操作的元素有明确的id或class用CSS选择器性能更好。正则表达式3个最常用场景\d 匹配一个或多个数字提取报表编号中的数字 [\u4e00-\u9fa5] 匹配中文字符提取报表中的公司名称 第\d季度 非贪婪匹配提取第X季度格式的文本三、变量与数据类型财务数据存储的核心做财务报表时最常用的变量类型是字符串存储公司名称、科目名称、数字存储金额、数量、列表存储多个报表数据、字典存储科目映射关系。字符串操作# 字符串拼接生成报表文件名report_filename财务报表_company_name_quarter.xlsx# 字符串替换清理科目名称clean_accountaccount_name.replace( ,)# 移除空格clean_accountclean_account.replace(调整,)# 移除调整标记# 字符串格式化report_title{}公司{}季度财务报表.format(company_name,quarter)# 或者用f-stringreport_titlef{company_name}公司{quarter}季度财务报表数字操作# 金额计算单位元revenue5000000# 收入cost3000000# 成本profitrevenue-cost# 利润# 金额格式化添加千分位分隔符formatted_revenue{:,}.format(revenue)# 输出5,000,000# 四舍五入保留两位小数profit_rateround(profit/revenue*100,2)# 利润率保留2位小数列表操作# 从Excel读取的多个子公司报表数据report_data[[子公司A,5000000,3000000,2000000],[子公司B,8000000,5000000,3000000],[子公司C,3000000,2000000,1000000]]# 遍历列表生成合并报表foriteminreport_data:companyitem[0]revenueitem[1]costitem[2]profititem[3]# 写入合并报表...字典操作存储科目映射关系# 科目名称映射字典account_map{1001:库存现金,1002:银行存款,1122:应收账款,2202:应付账款}# 键不存在时的两种处理方案# 方案1用get方法提供默认值account_nameaccount_map.get(1001,未知科目)# 方案2先判断key是否存在if1001inaccount_map:account_nameaccount_map[1001]else:account_name未知科目JSON数据处理从HTTP接口获取财务数据importjsonimportrequests# 发送HTTP请求获取财务数据responserequests.get(https://your-finance-system.com/api/reports)response_jsonjson.loads(response.text)# 操作JSON数据foriteminresponse_json[data]:companyitem[company]revenueitem[revenue]# 写入Excel...# JSON转文本存储用于日志记录json_textjson.dumps(response_json,ensure_asciiFalse,indent2)四、流程控制让报表汇总按顺序执行批量处理财务报表最核心的是循环和判断。ForEach列表循环用来遍历多个子公司的Excel报表文件。If条件判断用来检查报表数据完整性。For次数循环当你知道要处理多少份报表时用这个。比如要合并15个子公司的报表设置循环次数为15。拼多多店群自动化报活动上架相似元素循环当你要操作Excel里面的多个相同格式的区域时用这个。比如每个子公司的报表格式都一样用相似元素循环一次性全部找到并读取。ForEach列表循环最常用# 从文件夹读取的Excel报表文件列表report_files[子公司A_2024Q1.xlsx,子公司B_2024Q1.xlsx,子公司C_2024Q1.xlsx]# ForEach循环遍历forreport_fileinreport_files:# 打开Excel报表excel.open(report_file)# 读取报表数据dataexcel.read_all()# 写入合并报表# 关闭Excel报表excel.close()While条件循环当你不知道要处理多少份报表时用。比如从一个不断有新报表上传的文件夹里取文件来处理只要文件夹里还有Excel文件就继续循环。无限循环慎用。我曾经写了一个无限循环忘记设退出条件影刀RPA跑了整整一夜第二天早上发现电脑卡死生成了几千份重复的报表。现在我在无限循环里一定加一个计数器和退出条件。If条件判断# 检查报表必要字段是否完整ifcompanyandrevenueandcost:# 字段完整生成报表generate_report()else:# 字段不完整记录错误日志print(报表数据不完整companystr(company))Try-Catch异常处理报表生成过程中可能遇到各种意外——Excel模板文件被占用、磁盘空间不足、文件名包含非法字符。用Try-Catch包住核心代码出错了也不会中断整个流程。try:# 尝试打开Excel报表excel.open(财务报表模板.xlsx)# 执行数据写入操作excel.write_cell(A1,company_name)# 保存文件excel.save_as(output_path)exceptExceptionase:# 出错了记录日志但不中断流程print(生成报表失败str(e))五、财务报表自动汇总实战案例我要带你做一个真实可用的项目多Excel合并与数据透视表生成器。需求是从一个文件夹读取多个子公司的Excel报表然后自动合并成一份集团总表并生成数据透视表。Excel报表格式每个子公司的报表格式相同公司名称科目代码科目名称借方金额贷方金额日期子公司A1001库存现金5000002024-01-01子公司A1002银行存款20000002024-01-01合并后报表格式集团总表公司名称科目代码科目名称借方金额贷方金额日期数据来源子公司A1001库存现金5000002024-01-01手工录入子公司B1002银行存款20000002024-01-01系统导入影刀RPA流程设计第一步用文件循环指令遍历文件夹里的所有Excel文件。勾选仅匹配扩展名填写.xlsx,.xls。第二步在循环内部用启动Excel指令打开当前遍历到的Excel文件。第三步用读取Excel内容指令把数据读到变量report_data里。第四步用启动Excel指令打开集团总表合并后的报表。第五步用写入Excel内容指令把report_data写入集团总表。第六步用关闭Excel指令关闭所有打开的Excel文件。第七步用Excel-创建数据透视表指令基于集团总表的数据创建数据透视表。真实报错处理报错1Can not convert Array to String无法将数组转换为字符串原因Excel读取的某一列数据包含合并单元格读出来是个数组不是单个值。解决在读取Excel内容后用列表转换为文本指令分隔符用逗号把数组转成字符串。报错2文件已被另一个进程使用原因上一次循环没关闭Excel文件这一次要打开同一个文件时被占用。解决每次循环结束前用关闭Excel指令关闭所有Excel文件。或者用Try-Catch包住打开文件的指令出错时先关闭所有Excel进程再重试。报错3SaveAs方法失败原因保存路径包含Windows非法字符/ \ : * ? |。解决保存前用文本替换指令把非法字符替换成下划线或横线。# 清理文件名中的非法字符importre clean_filenamere.sub(r[\\/*?:|],_,original_filename)报错4数据透视表字段无效原因创建数据透视表时指定的行字段、列字段、值字段在数据源中不存在。解决在创建数据透视表前先用Excel-获取所有列名指令获取数据源的所有列名然后检查要使用的字段是否在列名列表中。六、数据透视表让财务数据会说话数据透视表是Excel里最强大的数据分析工具。影刀RPA可以自动创建数据透视表让你的财务数据自动汇总、分类、计算。创建数据透视表用Excel-创建数据透视表指令指定数据源范围和透视表放置位置。然后设置行字段、列字段、值字段。# 创建数据透视表在影刀RPA的Excel-创建数据透视表指令里配置# 数据源Sheet1的A1:F1000# 透视表位置Sheet2的A1# 行字段公司名称、科目名称# 列字段无# 值字段借方金额求和、贷方金额求和设置数据透视表字段用Excel-设置数据透视表字段指令可以动态修改数据透视表的行字段、列字段、值字段、筛选字段。# 动态修改值字段的计算方式# 默认是求和可以改成计数、平均值、最大值、最小值等# 在影刀RPA的Excel-设置数据透视表字段指令里配置刷新数据透视表当数据源发生变化时数据透视表不会自动更新。用Excel-刷新数据透视表指令可以手动刷新。# 刷新所有数据透视表# 在影刀RPA的Excel-刷新数据透视表指令里选择刷新所有透视表七、多Excel合并让分散的数据集中起来除了合并多个子公司的报表有时还需要合并同一个月的多张 sheet 页或者合并多个工作簿的相同格式的数据。合并多个工作簿用文件循环指令遍历文件夹里的所有Excel文件然后用Excel-读取指令读取每个文件的数据最后用Excel-写入指令写入合并后的文件。合并多个工作表用Excel-获取所有工作表名称指令获取当前工作簿的所有工作表名称然后用ForEach循环遍历每个工作表读取数据并合并。# 合并当前工作簿的所有工作表sheet_namesexcel.get_sheet_names()forsheet_nameinsheet_names:# 切换到工作表excel.switch_sheet(sheet_name)# 读取数据dataexcel.read_all()# 写入合并表...合并时的常见坑坑1每个Excel文件的列顺序不一样。有的文件公司名称在第2列有的在第1列。解决办法是在读取前先用Excel-获取所有列名指令获取列名然后根据列名而不是列序号来读取数据。坑2每个Excel文件的sheet名称不一样。有的叫Sheet1有的叫数据。解决办法是指定读取第一个Sheet不管它叫什么名字。坑3数据格式不一致。有的文件金额是数字格式有的是文本格式带¥符号。解决办法是在读取后统一做数据清洗把文本格式的金额转换成数字格式。# 清洗金额数据移除¥符号和逗号importredefclean_amount(amount_str):ifisinstance(amount_str,str):# 移除¥符号和逗号clean_strre.sub(r[¥,],,amount_str)# 转换成floatreturnfloat(clean_str)else:returnfloat(amount_str)# 应用到每一行数据forrowindata:row[3]clean_amount(row[3])# 借方金额row[4]clean_amount(row[4])# 贷方金额八、自动发邮件让报表自动送达财务报表生成完之后通常需要通过邮件发送给财务总监、各子公司财务负责人等相关人员。影刀RPA可以自动发送邮件并把生成的报表作为附件。邮件配置用发送邮件指令填写SMTP服务器信息、发件人邮箱、收件人邮箱、主题、正文、附件等。# 邮件配置在影刀RPA的发送邮件指令里填写# SMTP服务器smtp.qq.comQQ邮箱# 端口465SSL加密# 发件人your_emailqq.com# 授权码在QQ邮箱设置里获取不是邮箱密码# 收件人finance_directorcompany.com财务总监# 抄送subsidiary_financecompany.com各子公司财务负责人# 主题{}季度财务报表 - 请查收# 正文尊敬的领导附件为{}季度财务报表请查收并审阅。如有疑问请随时联系我。# 附件D:/报表/财务报表_{quarter}.xlsx邮件发送的常见坑坑1授权码填写错误。很多人把邮箱密码填到授权码字段里导致发送失败。解决办法是到邮箱设置里获取正确的授权码QQ邮箱、163邮箱、126邮箱都需要手动开启SMTP服务并获取授权码。坑2收件人邮箱格式错误。如果有多个收件人要用分号;分隔不能用逗号。解决办法是用文本分割指令把收件人列表转换成用分号分隔的字符串。坑3附件路径错误。附件路径必须是绝对路径不能用相对路径。解决办法是用文件路径拼接指令把相对路径转换成绝对路径。importos# 相对路径转绝对路径relative_path./报表/财务报表.xlsxabsolute_pathos.path.abspath(relative_path)# 多个收件人用分号分隔recipients[user1company.com,user2company.com]recipients_str;.join(recipients)九、数据处理Excel公式在财务中的应用财务报表中经常需要用Excel公式来做计算。影刀RPA可以在写入数据的同时写入Excel公式让报表自动计算。常用财务公式SUM函数求和SUM(D2:D100)计算D2到D100单元格的总和比如借方金额合计。IF函数条件判断IF(D2E2, 盈利, 亏损)如果借方金额大于贷方金额显示盈利否则显示亏损。VLOOKUP函数查找匹配VLOOKUP(A2, 科目映射表!A:B, 2, FALSE) [video(video-rlkPgdC7-1783008146010)(type-csdn)(url-https://live.csdn.net/v/embed/526817)(image-https://v-blog.csdnimg.cn/asset/1d3c3709da119dd8c13ab01e9b282520/cover/Cover0.jpg)(title-TEMU店群矩阵自动化运营核价报活动)]在科目映射表工作表中查找A2单元格的值返回对应的科目名称。SUMIF函数条件求和SUMIF(A:A, 子公司A, D:D)对A列等于子公司A的所有行求D列的总和。数据透视表相关公式GETPIVOTDATA(借方金额, 数据透视表!A1, 公司名称, 子公司A)从数据透视表中提取子公司A的借方金额合计。在影刀RPA中写入公式用Excel-写入单元格指令在写入内容前加一个等号影刀RPA会自动识别为公式。# 写入SUM公式excel.write_cell(D101,SUM(D2:D100))# 写入IF公式excel.write_cell(G2,IF(D2E2, 盈利, 亏损))# 写入VLOOKUP公式注意引号要用双引号或者直接写英文引号excel.write_cell(C2,VLOOKUP(A2, 科目映射表!A:B, 2, FALSE))十、进阶技能Python协同处理Excel影刀RPA内置了Python环境可以直接调用Python的第三方库来处理Excel。最常用的是openpyxl库处理xlsx格式和xlrd/xlwt库处理xls格式。安装openpyxl库在影刀RPA的Python-执行代码指令里先用pip安装库importsubprocess subprocess.check_call([pip,install,openpyxl])用openpyxl合并多个Excel文件fromopenpyxlimportload_workbook,Workbook# 创建一个新的工作簿合并后的报表merged_wbWorkbook()merged_wsmerged_wb.active merged_ws.title合并报表# 写入表头headers[公司名称,科目代码,科目名称,借方金额,贷方金额,日期]merged_ws.append(headers)# 遍历所有子公司的报表report_files[子公司A.xlsx,子公司B.xlsx,子公司C.xlsx]forreport_fileinreport_files:# 打开子公司报表wbload_workbook(report_file)wswb.active# 读取数据跳过表头forrowinws.iter_rows(min_row2,values_onlyTrue):# 写入合并报表merged_ws.append(row)# 保存合并后的报表merged_wb.save(合并报表.xlsx)用openpyxl创建数据透视表很遗憾openpyxl库不支持创建数据透视表。要实现这个功能可以调用win32com库需要安装Microsoft Excel软件。importwin32com.clientaswin32# 启动Excelexcelwin32.Dispatch(Excel.Application)excel.VisibleTrue# 可视化运行# 打开工作簿wbexcel.Workbooks.Open(合并报表.xlsx)wswb.ActiveSheet# 创建数据透视表缓存pivot_cachewb.PivotCaches.Create(SourceType1,# xlDatabaseSourceDataws.UsedRange)# 创建数据透视表pivot_tablepivot_cache.CreatePivotTable(TableDestinationSheet2!R1C1,TableName数据透视表)# 设置行字段pivot_table.PivotFields(公司名称).Orientation1# xlRowFieldpivot_table.PivotFields(科目名称).Orientation1# xlRowField# 设置值字段pivot_table.AddDataField(pivot_table.PivotFields(借方金额))pivot_table.AddDataField(pivot_table.PivotFields(贷方金额))# 保存并关闭wb.Save()wb.Close()excel.Quit()十一、系统联动飞书通知与定时任务财务报表生成完并发送邮件后通常需要通知相关人员。影刀RPA可以对接飞书自动发送消息通知。飞书消息通知配置用飞书-发送消息指令填写应用App ID和App Secret然后填写接收人的open_id和消息内容。财务报表生成完成通知 共合并报表 15 份 生成数据透视表 3 个 保存路径D:/报表/ 生成时间2024-01-01 10:30:00飞书多维表格记录财务数据每次生成报表后把关键财务数据写到飞书多维表格里方便后续跟踪和分析。用飞书多维表格-添加记录指令填写表格ID和字段值。定时任务配置如果要每月1号自动生成上个月的财务报表用定时任务指令。设置触发方式为按CRON表达式比如0 9 1 * *表示每月1号早上9点执行。# CRON表达式格式分 时 日 月 周# 每月1号早上9点执行cron_expression0 9 1 * *# 每周一早上9点执行cron_expression0 9 * * 1# 每天早上9点执行cron_expression0 9 * * *定时任务的常见坑坑1CRON表达式写错。比如想设置每月1号执行写成了1 9 * * *这会让程序每天9点01分执行而不是每月1号执行。坑2时区设置错误。影刀RPA的定时任务默认使用北京时间Asia/Shanghai如果你的公司在其他时区需要手动修改时区设置。坑3任务执行失败没有通知。如果定时任务执行失败默认不会发送通知。解决办法是在流程开头和结尾加上飞书-发送消息指令执行成功或失败时都发送通知。十二、工程化与规范当你写的流程超过50条指令时一定要做子流程封装。我把财务报表汇总流程拆成了5个子流程读取Excel配置、合并多个Excel文件、创建数据透视表、发送邮件通知、记录执行日志。每个子流程负责一个独立的功能主流程只需要按顺序调用这5个子流程。子流程封装与参数传递子流程可以定义输入参数和输出参数。比如合并Excel文件子流程输入参数是folder_path文件夹路径和file_pattern文件匹配模式输出参数是merged_file_path合并后的文件路径和success是否成功。调试技巧我第一次写完财务报表汇总流程运行时在第五条指令就报错了。后来学会了打断点调试在可能出错的指令前点一下行号出现橙色圆点就是断点。运行到断点时会暂停这时可以在下方的调试变量面板看到所有变量的值。命名规范指令的命名要能看懂是干什么的。比如不要叫指令1、“指令2”要叫启动Excel读取配置文件、“打开集团总表”、“写入合并数据”、“创建数据透视表”。变量名也要有意义不要叫a、b、c要叫report_folder_path、merged_file_path、pivot_table_sheet_name。版本选择社区版免费但有指令数限制最多100条指令适合学习和个人使用。创业版和企业版没有指令数限制支持调度API、多人协作、版本管理。如果你要做的财务报表汇总流程指令数会超过100条建议直接上创业版。常见报错速查表报错信息原因解决方法AttributeError: ‘NoneType’ object has no attribute ‘get_active_sheet’Excel对象为None没有成功启动Excel检查Excel文件路径是否正确文件是否被占用Can not convert Array to StringExcel某单元格是数组不是单个值用列表转换为文本指令转换SaveAs方法失败文件名包含Windows非法字符替换掉/?文件已被另一个进程使用Excel文件被占用未关闭每次循环结束前关闭Excel文件KeyError: ‘公司名称’字典里没有’公司名称’这个键用dict.get(‘公司名称’,默认值)方式安全取值数据透视表字段无效指定的字段在数据源中不存在先获取所有列名检查字段是否存在SMTPAuthenticationError邮箱授权码错误到邮箱设置里重新获取授权码#影刀RPA #财务报表 #Excel合并 #数据透视表 #RPA教程 #新手入门 #自动化办公 #财务报表自动化 #邮件自动发送作者林焱