分享合并多个 excel 文件的方法，支持两种格式

[yasea]

最开始在网上找的代码只支持一种格式的 EXCEL 合并，经过本菜鸟的优化以后，可以同时对两种格式的 excel 合并，忍不住分享一波。欢迎 V2er 老铁们拍砖！

代码

import pandas as pd
import os,re
from xml.sax import ContentHandler, parse
# 解析 xml 格式的 excel 文件
class ExcelHandler(ContentHandler):
    def __init__(self):
        self.chars = [  ]
        self.cells = [  ]
        self.rows = [  ]
        self.tables = [  ]
    def characters(self, content):
        self.chars.append(content)
    def startElement(self, name, atts):
        if name=="Cell":
            self.chars = [  ]
        elif name=="Row":
            self.cells=[  ]
        elif name=="Table":
            self.rows = [  ]
    def endElement(self, name):
        if name=="Cell":
            self.cells.append(''.join(self.chars))
        elif name=="Row":
            self.rows.append(self.cells)
        elif name=="Table":
            self.tables.append(self.rows)

# 替换 XML 中的非法字符 
def clearContent(srcfile):
    outfile = srcfile + "-output.xls"
    if os.path.isfile(srcfile):
        fin  = open(srcfile, 'r',  encoding='UTF-8', errors='ignore')
        fout = open(outfile, "wt", encoding='UTF-8',errors='ignore')
        reg  = re.compile("\<\Data.*\>(.*)\<\/Data\>") # 正则-取出需要处理的字符串          
        for line in fin:
            if i == 0 and line.find("xml version") < 1:
                print(srcfile, "不是 xml 格式!")
                return False
            if line.find('<Data ss:Type="String">') > 0:
                row = reg.findall(line) 
                if len(row) >0 and row[0] != "":
                    aline = '<Cell ss:StyleID="s_title" ><Data ss:Type="String">%s</Data></Cell>'% (row[0].replace("&"," ").replace(">"," ").replace("<"," "))
                    fout.write("\n" + aline)
                else:
                    fout.write(line)
            else:
                fout.write(line)
        fin.close()
        fout.close()
        os.remove(srcfile)
        os.rename(outfile, srcfile)
        return True
# 存放 EXCEL 原始文件的目录        
src_dir = "D:\\Develop\\Python\\关单原始数据表格"
 
filename_excel = []
frames = []
for root, dirs, files in os.walk(src_dir):
    for file in files:
        filename_excel.append(os.path.join(root,file))

new_list = []
excelHandler = ExcelHandler()  
for xls_file in filename_excel: 
    print("读取文件.........",xls_file)
    try:
        dataframe = pd.read_excel(xls_file, converters={'报关单号':str}) # 可设定指定列的格式
        new_list.append(dataframe)
    except:
        print("不是标准格式的 EXCEL ，尝试 XML 读取............")        
        clearContent(xls_file)             
        parse(xls_file, excelHandler)
        df = pd.DataFrame(excelHandler.tables[0][4:], columns = excelHandler.tables[0][3])
        df.columns = excelHandler.tables[0][0] 
        new_list.append(df)               
# 将合并的结果写入文件
dfnew = pd.concat(new_list)
dfnew.to_excel("D:\\output\\alldata.xlsx",index=False)

[ziding]

excel 的话，用 openxl 更香

[kaishi123]

感谢分享
excel 的话，用 vb 如何

[sniperhgy]

谢谢楼主的分享。不过用 xml 操作，还是不如用 excel 相关的 package 方便。
处理 excel 的话，VBA 更方便一些，毕竟是一家的东西。

[yasea]

@ziding 谢谢分享，空了研究下。

@kaishi123
@sniperhgy VB 当然也没毛病，用 python 的好处是不用打开 EXCEL 文件，而且可以转成 csv ，针对 excel 文件比较大且比较多的情况，VB 有点吃力。

[sniperhgy]

@yasea 楼主现在的代码里面，其实是对 XML 的操作，所以无论用什么语言，都可以。之所以说用 VBA 处理 excel 文件方便，是因为里面关于 workbook ，sheet ，row ，column 之类的都已经封装成对象了，然后还有很多原生 function 。至于说文件很大的话，我用 VBA 处理过 50M 的文件，如果是简单的行列之间的处理，速度不会特别慢。如果是那种比较偏向数据分析类的应用，excel 自己自带的一些功能都没办法实现，比如透视表，其实是有行数要求的，再或者是 filter 那种东西，也是有行数要求的。说白了就是，如果 excel 自己的 function 都没法支持，用其他第三方的语言也照样没用。

[yasea]

@sniperhgy 感谢拍砖，用 VBS 试过几十万行数据的合并，还是很好用的。
我的应用场景是合并可能几百万行数据，最终生成 CSV 文件，再直接导入数据库进行分析，所以没有用 VBS 。