Python批量合并同一文件夹内所有子文件夹下的Excel文件内所有Sheet数据

上一篇 / 下一篇  2021-11-17 13:23:46

  一、前言
  大家好,我是Python进阶者。前一阵子给大家分享了Python自动化文章:手把手教你利用Python轻松拆分Excel为多个CSV文件,手把手教你4种方法用Python批量实现多Excel多Sheet合并,而后在Python进阶交流群里边有个叫【扮猫】的粉丝遇到一个问题,她有很多个Excel表格,而且多个excel里多个sheet,现在需要对这些Excel文件进行合并。
  用上面链接对应的方法进行合并,发现只能够合并Sheet,其他的就合并不了,这确实是个问题。
  诚然,一个一个打开复制粘贴固然可行,但是该方法费时费力,还容易出错,几个文件还可以手动处理,要是几十个甚至上百个,你就抓瞎了,不过这问题对Python来说,so easy,一起来看看吧!
  二、项目目标
  用Python实现多Excel、多Sheet的合并处理,针对现实中的切确需求,使用Python批量合并同一文件夹内所有子文件夹下的Excel文件内所有Sheet数据,这个需求在现实生活中还是挺常见的,所有比较实用。
  三、项目准备
  软件:PyCharm
  需要的库:pandas,os,glob
  四、项目分析
  1)如何选择要合并的Excel文件?
  利用os和glob,获取所有要合并的Excel文件。
  2)如何选择要合并的Sheet?
  利用pandas库进行Excel读取,获取要合并的Sheet名。
  3)如何合并?
  利用pandas库,对所有Sheet名逐一循环打开,通过concat()函数进行数据追加合并即可。
  4)如何保存文件?
  利用to_excel保存数据,得到最终合并后的目标文件。
  五、项目实现
  这里提供4种方法给大家,一个比一个简洁,其中后面三个方法都是来自【小小明大佬】提供的,确实太强了。
  1、方法一
  这个方法是来自【王宁】大佬的分享,代码确实有点多,不过也是手把手教程,非常详细,也有注释,详情可以戳这篇文章:文科生自学Python-批量汇总同一路径内所有Excel文件内所有Sheet数据-基础知识1.41,代码如下:
  # -*- coding: utf-8 -*- 
  import pandas as pd 
  import datetime 
  import os 
   
   
  # define a starting point of time 
  start = datetime.datetime.now() 
   
   
  def Set_Work_Path(x): 
      try: 
          os.chdir(x) 
          route = os.getcwd() 
          print(route) 
          return route 
      except Exception: 
          print("No Result") 
   
   
  work_path = r"E:\\PythonCrawler\\python_crawler-master\\MergeExcelSheet\\file\\" 
  Set_Work_Path(work_path) 
   
   
  # define a function to get all the xlsx file names after deleting old file if there. 
  def Get_Dedicated_4Letter_File_List(x): 
      path = os.getcwd() 
      old_name = path + os.sep + "汇总数据" + ".xlsx"  # dim a txt name 
      if os.path.exists(old_name): 
          os.remove(old_name) 
      files = os.listdir(path)  # print(files) #check all files name in the path 
      current_list = [] 
      for i in range(0, len(files), 1): 
          try: 
              if files[i][-4:] == x and files[i][:4] != "汇总数据": 
                  current_list.append(files[i]) 
          except Exception: 
              pass 
      return current_list 
   
   
  Current_Excel_list = Get_Dedicated_4Letter_File_List("xlsx") 
  print(Current_Excel_list) 
   
   
  # define a function to read all sheets one by one in excel file 
  def Get_All_Sheets_Excel(x): 
      file = pd.ExcelFile(x) 
      list_sht_name = file.sheet_names  # get list of sheets' names 
      print(list_sht_name) 
      list_sht_data = []  # get all sheet data sets into a list 
      for i in range(0, len(list_sht_name), 1): 
          list_sht_data.append(pd.read_excel(x, header=0, sheet_name=list_sht_name[i], index_col=None)) 
      # merge all data sets together 
      df = pd.concat(list_sht_data) 
      # delete blank data 
      df.dropna(axis=0, how="all", inplace=True) 
      print(df) 
      return df 
   
   
  # define a list to get all data from sheets from different excel files 
  data_list = [] 
  for i in range(0, len(Current_Excel_list), 1): 
      # print(Current_Excel_list[i]) 
      data_list.append(Get_All_Sheets_Excel(Current_Excel_list[i])) 
  data = pd.concat(data_list) 
  data.dropna(axis=0, how="all", inplace=True) 
  print(data) 
   
  # save the data into excel file 
  writer = pd.ExcelWriter("王宁大佬的汇总数据.xlsx") 
  data.to_excel(writer, encoding="utf_8_sig", sheet_name="DATA", index=False) 
  # get the target pivot datasets 
  writer.save() 
   
  end = datetime.datetime.now() 
  run_time = round((end-start).total_seconds()/60, 2) 
  show = "程序运行消耗时间为: %s 分钟" % run_time+",搞定!" 
  print(show) 
  上面这个代码对原始数据要求比较苛刻,前提条件:所有数据都是规范的数据源且字段名和数据结构是一样的。这样看来,还是有些受限的。不过不要慌,接下来【小小明大佬】的这三个方法,就没有这个限制,下面一起来看看吧!
  2、方法二
  下面这个代码是基于【小小明大佬】提供的单Sheet表合并代码改进所得到的,关键点在于将sheet_name=None这个参数带上,代表获取Excel文件中的所有sheet表,其返回的是一个字典,所有在后面遍历的时候,是以字典的形式进行取值的,之后在15行的地方,需要注意使用的是extend()方法进行追加,如果使用append()方法,得到的就只有最后一个表格的合并结果,这个坑小编亲自踩过,感兴趣的小伙伴也可以踩下坑。
  # -*- coding: utf-8 -*- 
  import os 
  import pandas as pd 
  result = [] 
  path = r"E:\\PythonCrawler\\python_crawler-master\\MergeExcelSheet\\testfile\\file" 
  for root, dirs, files in os.walk(path, topdown=False): 
      for name in files: 
          if name.endswith(".xls") or name.endswith(".xlsx"): 
              df = pd.read_excel(os.path.join(root, name), sheet_name=None) 
              result.append(df) 
   
  data_list = [] 
  for data in result: 
      # print(data.values()) 
      data_list.extend(data.values())  # 注意这里是extend()函数而不是append()函数 
   
  df = pd.concat(data_list) 
  df.to_excel("testfile所有表合并.xlsx", index=False) 
  print("合并完成!") 
  3、方法三
  下面这个代码是【小小明大佬】手撸的一个代码,使用了列表append()方法,效率虽说会低一些,但是处理上百上千个文件,仍然不在话下。
  需要注意的是代码中的第6行和第7行,获取文件路径,其中**代表的是文件夹下的子文件递归。另外就是.xls*了,这个是正则写法,表示的是既可以处理xls格式,也可以处理xlsx格式的Excel文件,真是妙哉!
  # -*- coding: utf-8 -*- 
  import glob 
  import pandas as pd 
  path = "E:\\PythonCrawler\\python_crawler-master\\MergeExcelSheet\\file\\" 
  data = [] 
  for excel_file in glob.glob(f'{path}/**/[!~]*.xls*'): 
  # for excel_file in glob.glob(f'{path}/[!~]*.xlsx'): 
      excel = pd.ExcelFile(excel_file) 
      for sheet_name in excel.sheet_names: 
          df = excel.parse(sheet_name) 
          data.append(df) 
  # print(data) 
   
  df = pd.concat(data, ignore_index=True) 
  df.to_excel("小小明提供的代码(合并多表)--glob和pandas库列表append方法--所有表合并.xlsx", index=False) 
  print("合并完成!") 
  4、方法四
  下面这个代码是【小小明大佬】手撸的另外一个代码,使用了sheet_name=None和列表extend()方法,将sheet_name=None这个参数带上,代表获取Excel文件中的所有sheet表,其返回的是一个字典,所有在后面遍历的时候,是以字典的形式进行取值的,效率比前面的方法都要高一些。
  需要注意的是代码中的第6行和第7行,获取文件路径,其中**代表的是文件夹下的子文件递归。另外就是.xls*了,这个是正则写法,表示的是既可以处理xls格式,也可以处理xlsx格式的Excel文件,真是妙哉!
  # -*- coding: utf-8 -*- 
  import glob 
  import pandas as pd 
  path = r"E:\PythonCrawler\python_crawler-master\MergeExcelSheet\file" 
  data = [] 
  # for excel_file in glob.glob(f'{path}/**/[!~]*.xlsx'): 
  for excel_file in glob.glob(f'{path}/[!~]*.xlsx'): 
      dfs = pd.read_excel(excel_file, sheet_name=None).values() 
      data.extend(dfs) 
  # print(data) 
   
  df = pd.concat(data, ignore_index=True) 
  df.to_excel("小小明提供的代码(合并多表)--glob和pandas库列表extend方法--简洁--所有表合并.xlsx", index=False) 
  print("合并完成!") 
  六、效果展示
  1、处理前Excel数据:
  2、运行进度提示:
  3、合并后的结果:
  七、总结
  本文从实际工作出发,基于Python编程,介绍了4种方法,实现批量合并同一文件夹内所有子文件夹下的Excel文件内所有Sheet数据,为大家减少了很多复制粘贴的麻烦,省时省力,还不容易出错。代码不多,循环追加有点绕,想想也就明白了,大家一起学习进步。

  51Testing测试行业大调查,不但会掉落免费的课程和独家测试资料礼包,更有惊喜礼物等着你,什么京东购物卡、创意音响、大容量充电宝啦······
  最重要的是能够预测行业发展趋势,要不要点击链接参与一下~
  链接:http://vote.51testing.com/


TAG: 软件开发 Python

 

评分:0

我来说两句

Open Toolbar