将多个exc表格汇总于一个表格中

分两步：

一：合并多个exc至一个表中多个sheet

/// <summary>
/// 多个表格合并
/// </summary>
/// <returns></returns>
public void MulCreateExcelFile()
{
Application app = new Application();
Workbook result = app.Workbooks.Add();
object missing = System.Reflection.Missing.Value;
Workbook wb1;
Workbook wb2;
wb1 = app.Workbooks.Open(Path.GetFullPath("C:\\Users\\admin_20200812085652_9999999999999999999.xlsx"), missing, true, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing);
wb2 = app.Workbooks.Open(Path.GetFullPath("C:\\Users\\admin_20200812085210_123.xlsx"), missing, true, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing);
foreach (Worksheet each in wb1.Sheets)
{
each.Copy(result.Worksheets[1]);
}
foreach (Worksheet each in wb2.Sheets)
{
each.Copy(result.Worksheets[1]);
}
wb1.Close();
wb2.Close();
result.SaveAs(@"C:\Users\Desktop\试验专用\result.xlsx");
app.Quit();
}

 

二：手动利用office宏进行合并多个sheet：

1.新建一个总表sheet
2.右击“查看代码”
3.复制粘贴以下代码：

Sub 合并当前工作簿下的所有工作表()
Application.ScreenUpdating = False
For j = 1 To Sheets.Count
If Sheets(j).Name <> ActiveSheet.Name Then
X = Range("A65536").End(xlUp).Row + 1
Sheets(j).UsedRange.Copy Cells(X, 1)
End If
Next
Range("B1").Select
Application.ScreenUpdating = True
MsgBox "当前工作簿下的全部工作表已经合并完毕！", vbInformation, "提示"
End Sub、

4.点击工具栏上面的“运行”下的“运行子过程/用户窗体”，提示完成之后就可以把宏计算界面关闭了