2020-03-25 Excel常见格式相互转换与合并操作

首先，这些操作也许不是最佳的办法，但都是经过实操能够实现的。本科一个学院院长说的好，老外搞研究不一定会寻求最好的办法，只要这个办法能够实现那就够了，就可以继续往下走，我们毕竟不是专门研究方法学的，也不可以寻求流程优化，更多时候只要能解决问题就足够了，不需要走捷径，至少不用刻意追求最短距离。
我这里的文字多来自实际的科研生活需求，这里比如我们研究泛癌中的差异基因，以转录组为例，想探究一下不同癌种共有的差异基因或特有的癌基因，以作进一步的机制挖掘或biomarker开发。那么，当我们得到一堆基因之后，可能是多个excel文件，这些excel文件具有相同的表头，因此需要合并它们为一个文件。再比如，同一单位中多个员工汇报工作时交上来的表格，领导想进行简单的统计分析，如看一下最大值、最小值、中位数、众数等等，也会用到这种场景。

1.把多个csv或xls合并成单个表头相同的多行文件

首先，如果你收集到的是csv格式的文件，是最好的，xls格式也可以，把它们放到一个文件夹，然后新建一个文本文件，编辑内容为“Copy *.csv combine.csv”，保存文件名为"合并.bat"（即批处理文件），如果是xls文件则改写为“Copy *.xls combine.xls”（实践证明文件名如果设置为中文则不识别，所以用了“combine”），双击bat文件即可快速合并。

那么，如果收集到的不是csv和xls格式，而是xlsx，那也没关系，可以通过格式转换为csv。我一开始想的是批量转换文件名，结果发现，转了之后没有用，说明不能通过单纯修改后缀名的方法来解决。

2.关于把xlsx转换为csv格式，这里介绍一种方法——

同样把所有文件放到一个新建文件夹中（保险起见最好是英文路径），同时在文件夹中新建一个excel文件，打开该文件，按ALT+F11键或右击sheet1工作表标签调出vb界面，“插入”》“模块”，粘贴以下代码：

Sub SaveToCSVs()
    Dim fDir As String
    Dim wB As Workbook
    Dim wS As Worksheet
    Dim fPath As String
    Dim sPath As String
    fPath = "C:\Users\ms-off1\Desktop\temp\"
    sPath = "C:\Users\ms-off1\Desktop\temp\"
    fDir = Dir(fPath)
    Do While (fDir <> "")
        If Right(fDir, 4) = ".xls" Or Right(fDir, 5) = ".xlsx" Then
            On Error Resume Next
            Set wB = Workbooks.Open(fPath & fDir)
            'MsgBox (wB.Name)
            For Each wS In wB.Sheets
                wS.SaveAs sPath & wB.Name & ".csv", xlCSV
            Next wS
            wB.Close False
            Set wB = Nothing
        End If
        fDir = Dir
        On Error GoTo 0
    Loop
End Sub

其中，fPath 定义了存放Excel文件的路径，sPath定义了csv文件的输出位置，根据个人情况进行修改，不要忘记路径最后的左斜线。上面的代码也可以实现从xls到csv格式的转换。

到这里再重复上面的操作即可完成多个文件合并，故事就讲完了。不过既然折腾，那就多折腾一波：

3.怎么反过来把csv批量转换成xlsx呢？

同样的操作，输入下面代码：

Sub CAVToXLSX()
    Dim fDir As String
    Dim wB As Workbook
    Dim wS As Worksheet
    Dim fPath As String
    Dim sPath As String
    fPath = "C:\Users\Micro\Desktop\source\"
    sPath = "C:\Users\Micro\Desktop\target\"
    fDir = Dir(fPath)
    Do While (fDir <> "")
        If Right(fDir, 4) = ".csv" Or Right(fDir, 5) = ".csv" Then
            On Error Resume Next
            Set wB = Workbooks.Open(fPath & fDir)
            'MsgBox (wB.Name)
            For Each wS In wB.Sheets
                wS.SaveAs sPath & wB.Name & ".xlsx" _
                , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            Next wS
            wB.Close False
            Set wB = Nothing
        End If
        fDir = Dir
        On Error GoTo 0
    Loop
End Sub

同样要注意修改文件路径。
关于转换xlsx到csv 文件和转换csv到xlsx文件，对应的也有人提供了python代码，现在我还不会，姑且不用。

4.下面的小技巧是关于多个excel文件的合并，这种需求不是很多，可能有时候领导想看一批数据，又不想一个一个打开的时候，就用到啦。

这种情况下，你拿到很多个excel文件，也可能是csv文件，需要快速合成为一个文件，每个csv变成合成后文件中的一个sheet，sheet名为原来的文件名。同样可以利用VB编写的宏来实现，前面的操作都一样（把所有文件放到一个新建文件夹，在里面新建excel文件，打开excel文件，调出vb窗口并新建模块）代码如下：

'功能：把多个excel工作簿的第一个sheet工作表合并到一个excel工作簿的多个sheet工作表，新工作表的名称等于原工作簿的名称

Sub Books2Sheets()
    '定义对话框变量
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    '新建一个工作簿
    Dim newwb As Workbook
    Set newwb = Workbooks.Add
    With fd
        If .Show = -1 Then
            '定义单个文件变量
            Dim vrtSelectedItem As Variant
            '定义循环变量
            Dim i As Integer
            i = 1
            '开始文件检索
            For Each vrtSelectedItem In .SelectedItems
            '打开被合并工作簿
            Dim tempwb As Workbook
            Set tempwb = Workbooks.Open(vrtSelectedItem)
            '复制工作表
            tempwb.Worksheets(1).Copy Before:=newwb.Worksheets(i)
            '把新工作簿的工作表名字改成被复制工作簿文件名，这儿应用于xls文件，即Excel97-2003的文件，如果是Excel2007，需要改成xlsx
            newwb.Worksheets(i).Name = VBA.Replace(tempwb.Name, ".xls", "")
            '关闭被合并工作簿
            tempwb.Close SaveChanges:=False
            i = i + 1
            Next vrtSelectedItem
        End If
    End With
    Set fd = Nothing
End Sub

这时候点击“运行”按钮，或者按F5运行的时候回弹出窗口让你选择文件，你应选择要合并的多个文件，而不是新建的excel文件，就可以了。你有几个文件，就会闪几下，最后全部合并完成（如果有十来个还好，如果上百个，最好还是每10个或20个合并成一个吧，不然就算合并了一个个的点sheet也会很费劲）

5.既然多个文件可以合并成多sheet的单个文件，那么反过来，如果一个excel文件有多个sheet，也可以拆分成多个单独的文件，每个文件只有一个sheet，文件名为sheet名，

用以下代码（----拆分后格式为xlsx）：

Sub SplitWorkbook()
    Dim workbookPath As String
    workbookPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each wSheet In ThisWorkbook.Sheets
        wSheet.Copy
        Application.ActiveWorkbook.SaveAs Filename:=workbookPath & "\" & wSheet.Name & ".xlsx"
        Application.ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

或者

Private Sub 分拆工作表()
       Dim sht As Worksheet
       Dim MyBook As Workbook
       Set MyBook = ActiveWorkbook
       For Each sht In MyBook.Sheets
           sht.Copy
           ActiveWorkbook.SaveAs Filename:=MyBook.Path & "\" & sht.Name, FileFormat:=xlOpenXMLWorkbook     '将工作簿另存为xlsx格式
           ActiveWorkbook.Close
       Next
       MsgBox "文件已经被分拆完毕!"

   End Sub

以上参考：
https://jingyan.baidu.com/article/3a2f7c2ea4809866afd611ed.html
https://jingyan.baidu.com/article/ca2d939d767a16eb6c31ce85.html
https://www.51wikihow.com/excel/how-to-split-workbooks-into-separate-files-in-excel.html
https://www.zhihu.com/question/39234324
https://blog.csdn.net/Gipsy_Danger/article/details/80142396
https://www.sohu.com/a/276904119_367376

6.此外，一开始提到的批量修改文件名，

这个给你可能更常用，方法也很简单，用excel完成：
在C盘新建一个temp文件夹（以后都在该文件夹下完成，养成好习惯），在里边新建一个名为“rename”的excel文件，内容格式为：

image.png

再新建一个名为“ren”的bat文件，里面内容格式为（注意没了表头）：