用NetOffice实现将多个Excel合并为一个Excel文件
声明：本文为原创内容，转载请注明出处。
最近在项目上遇到一个问题，就是用户从SAP中下载的报表多达十几个，而他们要求要在一个Excel文件中。在SAP中开发的报表都是用DOI导出，一个报表对应于一个Excel文件。DOI导出Excel速度本来也不快，写数据是一个个格子填充的，如果一张报表的数据量大的话，导出也要几分钟，如果将10几张报表的取值和导出合到一起，那么资源和时间的消耗是相当大。所以唯有让用户将10多张报表分别导出，之后做一个小工具让他们将10多张Excel合并成一个文件。
以前对Office的开发接触很少，一开始是考虑用微软的MOI做的，但是考虑到程序发布，格式兼容的问题，就选用了开源的NetOffice开发个小工具。
NetOffice的使用和MOI非常相似，而且是原生的.NET程序集，格式兼容性好。
用NetOffice实现多个Excel合并的逻辑比较简单。首先在项目中添加程序集引用：
合并Excel类的基本的实现代码如下：
1 using System;
2 using ExcelCombineExpress.Properties;
3 using NetOffice.ExcelApi.Enums;
4 using Excel = NetOffice.ExcelApi;
5 using System.Windows.Forms;
6 using System.Reflection;
7
8 namespace ExcelCombineExpress.Core
9 {
10 public class CombineClass
11 {
12 //Excels Files to combine
13 public ListBox.ObjectCollection Items { get; set; }
14
15 //Excel Path
16 public string Path { get; set; }
17
18 //Excel File Name
19 public string FileName { get; set; }
20
21 //Excel File Extension
22 public string Extension { get; set; }
23
24 /// <summary>
25 /// Combine Excel Files
26 /// </summary>
27 public void Combine()
28 {
29 if (Items!=null)
30 {
31
32 //Initialization
33 //Factory.Initialize();
34
35 //Create an excel file and close excel warning message display..
36 Excel.Application app=new Excel.Application {DisplayAlerts = false, EnableEvents = false};
37 //Create an workbook.
38 Excel.Workbook newbook = app.Workbooks.Add();
39
40 try
41 {
42 //Loop to Combile
43 for (int i = Items.Count-1; i >= 0; i--)
44 {
45 //Open the old excel
46 Excel.Workbook oldbook = app.Workbooks.Open(Items[i].ToString());
47
48 //Copy to combine
49 foreach (Excel.Worksheet sheet in oldbook.Worksheets)
50 {
51 if (!IsSheetEmpty(sheet))//if the sheet contains data,then combine
52 {
53 sheet.Copy(newbook.Worksheets[1],Missing.Value);
54
56 }
57 }
58
59 //After Combine, close the workbook.
60 oldbook.Close();
61
62 }
63
64 //Delete Excel empty sheets.
65 foreach (Excel.Worksheet sheet in newbook.Worksheets)
66 {
67 if (IsSheetEmpty(sheet))
68 {
69 DeleteSheet(sheet);
70 }
71 }
72
73 //After Combine,Save File
74 newbook.SaveAs(string.Format("{0}\\{1}{2}", Path, FileName, Extension), GetExcelFormat(Extension), Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlExclusive);
75 //newbook.SaveAs(string.Format("{0}\\{1}{2}", Path, FileName, Extension),GetExcelFormat(app));
76
77 MessageBox.Show(Resources.Str_Combine_Success, Resources.Str_Information,
78 MessageBoxButtons.OK, MessageBoxIcon.Information);
79 }
80 catch (Exception e)
81 {
82 MessageBox.Show(string.Format("{0}{1}", Resources.Str_File_Combine_Failed, e.Message), Resources.Str_Error, MessageBoxButtons.OK, MessageBoxIcon.Error);
83 }
84 finally
85 {
86 //Close Workbooks and quit excel application
87 newbook.Close();
88 app.Quit();
89 app.Dispose();
90 }
91
92 }
93 else
94 {
95 MessageBox.Show(Resources.Str_Select_file_first, Resources.Str_Error, MessageBoxButtons.OK, MessageBoxIcon.Error);
96 }
97 }
98
99 /// <summary>
100 /// check if the excel sheet is empty
101 /// </summary>
102 /// <param name="worksheet">worksheet</param>
103 /// <returns>true or false</returns>
104 private bool IsSheetEmpty(Excel.Worksheet worksheet)
105 {
106 Excel.Range range = worksheet.UsedRange;
107 if (range.Count<=1)
108 {
109 return true;
110 }
111 return false;
112 }
113
114 /// <summary>
115 /// delete worksheet
116 /// </summary>
117 /// <param name="sheet">sheet name</param>
118 private void DeleteSheet(Excel.Worksheet sheet)
119 {
120 try
121 {
122 //Excel.Worksheet tmpsheet = (Excel.Worksheet)book.Worksheets[sheet];
123
124 sheet.Delete();
125 }
126 catch (Exception e)
127 {
128 MessageBox.Show(e.Message, Resources.Str_Error, MessageBoxButtons.OK, MessageBoxIcon.Error);
129 }
130 }
131
132 /// <summary>
133 /// Get Excel Format Number
134 /// </summary>
135 /// <param name="extension">Extension</param>
136 /// <returns></returns>
137 private int GetExcelFormat(string extension)
138 {
139 int formatnum;
140 if (Equals(extension, ".xlsx"))
141 {
142 formatnum= 51;//Office 2007/2010
143 }
144 else
145 {
146 formatnum= -4143;//Office 2003 and below
147 }
148 return formatnum;
149 }
150
151 }
3 using NetOffice.ExcelApi.Enums;
4 using Excel = NetOffice.ExcelApi;
5 using System.Windows.Forms;
6 using System.Reflection;
7
8 namespace ExcelCombineExpress.Core
9 {
10 public class CombineClass
11 {
12 //Excels Files to combine
13 public ListBox.ObjectCollection Items { get; set; }
14
15 //Excel Path
16 public string Path { get; set; }
17
18 //Excel File Name
19 public string FileName { get; set; }
20
21 //Excel File Extension
22 public string Extension { get; set; }
23
24 /// <summary>
25 /// Combine Excel Files
26 /// </summary>
27 public void Combine()
28 {
29 if (Items!=null)
30 {
31
32 //Initialization
33 //Factory.Initialize();
34
35 //Create an excel file and close excel warning message display..
36 Excel.Application app=new Excel.Application {DisplayAlerts = false, EnableEvents = false};
37 //Create an workbook.
38 Excel.Workbook newbook = app.Workbooks.Add();
39
40 try
41 {
42 //Loop to Combile
43 for (int i = Items.Count-1; i >= 0; i--)
44 {
45 //Open the old excel
46 Excel.Workbook oldbook = app.Workbooks.Open(Items[i].ToString());
47
48 //Copy to combine
49 foreach (Excel.Worksheet sheet in oldbook.Worksheets)
50 {
51 if (!IsSheetEmpty(sheet))//if the sheet contains data,then combine
52 {
53 sheet.Copy(newbook.Worksheets[1],Missing.Value);
54
56 }
57 }
58
59 //After Combine, close the workbook.
60 oldbook.Close();
61
62 }
63
64 //Delete Excel empty sheets.
65 foreach (Excel.Worksheet sheet in newbook.Worksheets)
66 {
67 if (IsSheetEmpty(sheet))
68 {
69 DeleteSheet(sheet);
70 }
71 }
72
73 //After Combine,Save File
74 newbook.SaveAs(string.Format("{0}\\{1}{2}", Path, FileName, Extension), GetExcelFormat(Extension), Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlExclusive);
75 //newbook.SaveAs(string.Format("{0}\\{1}{2}", Path, FileName, Extension),GetExcelFormat(app));
76
77 MessageBox.Show(Resources.Str_Combine_Success, Resources.Str_Information,
78 MessageBoxButtons.OK, MessageBoxIcon.Information);
79 }
80 catch (Exception e)
81 {
82 MessageBox.Show(string.Format("{0}{1}", Resources.Str_File_Combine_Failed, e.Message), Resources.Str_Error, MessageBoxButtons.OK, MessageBoxIcon.Error);
83 }
84 finally
85 {
86 //Close Workbooks and quit excel application
87 newbook.Close();
88 app.Quit();
89 app.Dispose();
90 }
91
92 }
93 else
94 {
95 MessageBox.Show(Resources.Str_Select_file_first, Resources.Str_Error, MessageBoxButtons.OK, MessageBoxIcon.Error);
96 }
97 }
98
99 /// <summary>
100 /// check if the excel sheet is empty
101 /// </summary>
102 /// <param name="worksheet">worksheet</param>
103 /// <returns>true or false</returns>
104 private bool IsSheetEmpty(Excel.Worksheet worksheet)
105 {
106 Excel.Range range = worksheet.UsedRange;
107 if (range.Count<=1)
108 {
109 return true;
110 }
111 return false;
112 }
113
114 /// <summary>
115 /// delete worksheet
116 /// </summary>
117 /// <param name="sheet">sheet name</param>
118 private void DeleteSheet(Excel.Worksheet sheet)
119 {
120 try
121 {
122 //Excel.Worksheet tmpsheet = (Excel.Worksheet)book.Worksheets[sheet];
123
124 sheet.Delete();
125 }
126 catch (Exception e)
127 {
128 MessageBox.Show(e.Message, Resources.Str_Error, MessageBoxButtons.OK, MessageBoxIcon.Error);
129 }
130 }
131
132 /// <summary>
133 /// Get Excel Format Number
134 /// </summary>
135 /// <param name="extension">Extension</param>
136 /// <returns></returns>
137 private int GetExcelFormat(string extension)
138 {
139 int formatnum;
140 if (Equals(extension, ".xlsx"))
141 {
142 formatnum= 51;//Office 2007/2010
143 }
144 else
145 {
146 formatnum= -4143;//Office 2003 and below
147 }
148 return formatnum;
149 }
150
151 }
152 }
可以看出NetOffice的使用和MOI十分相似，使用起来十分简单，最重要的是它是一个原生的.NET程序集，而且还是开源的。个人觉得就是帮助文档少了一些。官方项目地 址：http://netoffice.codeplex.com/