由于python中用pandas处理EXCEL文档速度很慢，当有大量excel文件需要处理时，可先用excel自带的VBA工具将目录内的excel文档批量转化为CSV（逗号分隔符文件），可以大幅增加pandas处理数据的速度。具体操作如下：

1. 新建一个excel文档（带宏）
2. 按alt+F11打开VBA编辑器
3. 双击左侧的Sheet1，打开一个空白代码窗口
4. 粘贴如下代码：

Sub WorkbooksSaveAsCsvToFolder()

'UpdatebyExtendoffice20181031

Dim xObjWB As Workbook

Dim xObjWS As Worksheet

Dim xStrEFPath As String

Dim xStrEFFile As String

Dim xObjFD As FileDialog

Dim xObjSFD As FileDialog

Dim xStrSPath As String

Dim xStrCSVFName As String

 

    Application.ScreenUpdating = False

    Application.EnableEvents = False

    Application.Calculation = xlCalculationManual

    On Error Resume Next

Set xObjFD = Application.FileDialog(msoFileDialogFolderPicker)

    xObjFD.AllowMultiSelect = False

    xObjFD.Title = "Kutools for Excel - Select a folder which contains Excel files"

    If xObjFD.Show <> -1 Then Exit Sub

    xStrEFPath = xObjFD.SelectedItems(1) & ""

 

    Set xObjSFD = Application.FileDialog(msoFileDialogFolderPicker)

 

    xObjSFD.AllowMultiSelect = False

    xObjSFD.Title = "Kutools for Excel - Select a folder to locate CSV files"

    If xObjSFD.Show <> -1 Then Exit Sub

    xStrSPath = xObjSFD.SelectedItems(1) & ""

 

    xStrEFFile = Dir(xStrEFPath & "*.xls*")

 

    Do While xStrEFFile <> ""

        Set xObjWB = Workbooks.Open(Filename:=xStrEFPath & xStrEFFile)

        xStrCSVFName = xStrSPath & Left(xStrEFFile, InStr(1, xStrEFFile, ".") - 1) & ".csv"

        xObjWB.SaveAs Filename:=xStrCSVFName, FileFormat:=xlCSV

        xObjWB.Close savechanges:=False

        xStrEFFile = Dir

  Loop

    Application.Calculation = xlCalculationAutomatic

    Application.EnableEvents = True

    Application.ScreenUpdating = True

End Sub

6. 点击菜单栏 运行->运行子过程，或按F5执行。