一个Excel的帮助类——ExcelHelper

近日突发奇想，封装一个Excel的帮助类，好让日后做一些Excel操作时方便一点，至少导入导出会方便点吧。不过在封装过程中发现自己太差劲了，问题多多，搞这么百来行代码花了很长时间，于是写篇日志，记录一下这个ExcelHelper，也顺便记录一下封装过程中遇到的一些问题。

整个Helper中包括了读和写两部分，读是利用ADO.NET的OleDB进行读，与查询SQL Server很相像，查询语句是这种形式

SELECT * FROM [Sheet1$A1:A10]

“$”符号后面可以加一个范围，表明要读取哪一部分，如果不加的话就表明全表读取了。
下面则是读那部分的方法，一个是通用的查询，另一个则是导入

 1         /// <summary>
 2         /// 执行SQL查询一个Excel文档的内容 
 3         /// </summary>
 4         /// <param name="fileName">Excel文件名</param>
 5         /// <param name="cmdText">要执行的SQL 区域选择用 [Sheet1$A1:C7]形式</param>
 6         /// <param name="paramters">查询参数</param>
 7         /// <returns>查询结果</returns>
 8         public static DataTable ExecuteReader(string fileName, string cmdText, params OleDbParameter[] paramters)
 9         {
10             string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
11             OleDbConnection cn = null;
12             OleDbCommand cmd = new OleDbCommand();
13             OleDbDataReader reader = null;
14             DataTable table = new DataTable();
15             try
16             {
17                 cn = new OleDbConnection(strCon);
18                 PrepareCommand(cmd, cn, cmdText, paramters);
19                 reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
20                 table.Load(reader);
21                 cmd.Parameters.Clear();
22             }
23             finally
24             {
25                 if (reader != null)
26                 {
27                     reader.Close();
28                     reader.Dispose();
29                 }
30                 if (cn != null)
31                 {
32                     cn.Close();
33                     cn.Dispose();
34                 }
35             }
36             return table;
37         }

 1         /// <summary>
 2         /// 把Excel的某个工作表导入到DataTable中
 3         /// </summary>
 4         /// <param name="fileName">文件完整路径</param>
 5         /// <param name="sheetName">工作表名</param>
 6         /// <param name="HasHeader">是否存在表头。若是会把工作表第一行转成DataTable的列名</param>
 7         /// <returns>导入后的DataTable</returns>
 8         public static DataTable ImportExcel(string fileName, string sheetName, bool HasHeader)
 9         {
10             DataTable table = ExecuteReader(fileName, "select * from [" + sheetName + "]", null);
11 
12             if (HasHeader)
13             {
14                 for (int i = 0; i < table.Columns.Count; i++)
15                     table.Columns[i].ColumnName = table.Rows[0][i].ToString();
16                 table.Rows.RemoveAt(0);
17             }
18 
19             return table;
20         }

其实导入也是调用了通用查询的方法，不过加了一点表头的处理罢了。

写则是利用了一个COM组件，需要添加一个dll应用，Microsoft.Office.Interop.Excel.dll。原本也想用ADO.NET的，但是那写INSERT 和 UPDATE的SQL我确实写不好，老抛异常，于是放弃了，改用COM，不过用COM好像还挺直观的。

下面也粘两段代码，一个是编辑Excel文件的（其实就涵盖了原计划中的UPDATE和INSERT的操作而已，对于整行的删除，整列的删除，合并单元格等操作，鄙人还没做到。或许以后会补充上去。）另一个是导出到Excel文件。在这部分中重命名了Microsoft.Office.Interop.Excel这个明明空间，鄙人参考网友把它重命名为Excel了。

 1         /// <summary>
 2         /// 编辑一个Excel文档
 3         /// </summary>
 4         /// <param name="fileName">Excel文件名</param>
 5         /// <param name="table">要编辑的内容</param>
 6         /// <param name="startCell">目标单元格位置</param>
 7         public static void EditExcel(string fileName, DataTable table, string startCell)
 8         {
 9             Tuple<int, int> cell = ConvertCell(startCell);
10 
11             object missing = System.Reflection.Missing.Value;
12             Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
13             app.Application.Workbooks.Add(true);
14 
15             Excel.Workbook book = null;
16             Excel.Worksheet sheet = null;
17             bool existFile=IsExistFile(fileName);
18             if (existFile)
19             {
20                 book=app.Workbooks.Open(fileName, 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true); 
21             }
22             else {
23                 book = (Excel.Workbook)app.ActiveWorkbook;
24             }
25             sheet = (Excel.Worksheet)book.ActiveSheet;
26 
27             for (int i = 0,ei=cell.Item1; i < table.Rows.Count; i++,ei++)
28                 for (int j = 0, ej = cell.Item2; j < table.Columns.Count; j++, ej++)
29                     sheet.Cells[ei, ej] = table.Rows[i][j];
30            
31 
32             if(existFile) book.Save();
33             else book.SaveCopyAs(fileName);
34             //关闭文件
35             book.Close(false, missing, missing);
36             //退出excel
37             app.Quit();
38 
39         }

 1         /// <summary>
 2         /// 把DataTable导出到一个Excel文件
 3         /// </summary>
 4         /// <param name="fileName">Excel文件名</param>
 5         /// <param name="table">要导出的DataTable</param>
 6         /// <param name="AddHeader">是否要增加表头</param>
 7         public static void ExportExcel(string fileName, DataTable table, bool AddHeader)
 8         {
 9 
10             object missing = System.Reflection.Missing.Value;
11             Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
12             app.Application.Workbooks.Add(true);
13             Excel.Workbook book = (Excel.Workbook)app.ActiveWorkbook;
14             Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet;
15 
16             if (AddHeader)
17                 for (int i = 0; i < table.Columns.Count; i++)
18                     sheet.Cells[1, i+1] = table.Columns[i].ColumnName;
19 
20             for (int i = 0, ei = AddHeader ? 2 : 1; i < table.Rows.Count; i++, ei++)
21                 for (int j = 0; j < table.Columns.Count; j++)
22                     sheet.Cells[ei, j+1] = table.Rows[i][j];
23 
24 
25             string path = fileName.Substring(0, fileName.LastIndexOf('\\') + 1).Trim('\\');
26             if (!Directory.Exists(path))
27                 Directory.CreateDirectory(path);
28 
29             book.SaveCopyAs(fileName);
30             //关闭文件
31             book.Close(false, missing, missing);
32             //退出excel
33             app.Quit();
34         }

在这部分中遇到两个问题：

第一个是调用ApplicationClass()这个构造函数时，编译会不同过，错误信息是“无法嵌入互操作类型”，只要把Microsoft.Office.Interop.Excel.dll的嵌入互操作类型属性设为False就行了。

第二是给交错数组sheet[,]赋值时，如果两个下标中任意一个用了0的话，运行时会抛出COMException的，异常信息是 Exception from HRESULT: 0x800A03EC 这是由于Excel的工作表第0行或第0列是Excel工作表的条标尺，如下图红框框住的两条。

因此不可赋值。在循环遍历时，循环变量则要从1开始。

下面则把整个ExcelHelper的代码粘出来，欢迎大家批评指正。

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Data.OleDb;
  6 using System.Data;
  7 using System.IO;
  8 using Microsoft.Office.Interop;
  9 using Excel = Microsoft.Office.Interop.Excel;
 10 using System.Text.RegularExpressions;
 11 
 12 namespace MyHelpers.Helpers
 13 {
 14     public class ExcelHelper
 15     {
 16         #region 公共方法
 17 
 18         #region 导入
 19 
 20         /// <summary>
 21         /// 把Excel的某个工作表导入到DataTable中
 22         /// </summary>
 23         /// <param name="fileName">文件完整路径</param>
 24         /// <param name="sheetName">工作表名</param>
 25         /// <param name="HasHeader">是否存在表头。若是会把工作表第一行转成DataTable的列名</param>
 26         /// <returns>导入后的DataTable</returns>
 27         public static DataTable ImportExcel(string fileName, string sheetName, bool HasHeader)
 28         {
 29             DataTable table = ExecuteReader(fileName, "select * from [" + sheetName + "]", null);
 30 
 31             if (HasHeader)
 32             {
 33                 for (int i = 0; i < table.Columns.Count; i++)
 34                     table.Columns[i].ColumnName = table.Rows[0][i].ToString();
 35                 table.Rows.RemoveAt(0);
 36             }
 37 
 38             return table;
 39         }
 40 
 41         /// <summary>
 42         /// 把Excel的Sheet工作表导入到DataTable中
 43         /// </summary>
 44         /// <param name="fileName">文件完整路径</param>
 45         /// <param name="HasHeader">是否存在表头。若是会把工作表第一行转成DataTable的列名</param>
 46         /// <returns>导入后的DataTable</returns>
 47         public static DataTable ImportExcel(string fileName, bool HasHeader)
 48         {
 49             return ImportExcel(fileName, "sheet1$", HasHeader);
 50         }
 51 
 52         /// <summary>
 53         /// 把Excel的某个工作表导入到DataTable中
 54         /// </summary>
 55         /// <param name="fileName">文件完整路径</param>
 56         /// <returns></returns>
 57         public static DataTable ImportExcel(string fileName)
 58         {
 59             return ImportExcel(fileName, true);
 60         }
 61 
 62         #endregion
 63 
 64         #region 导出
 65 
 66         /// <summary>
 67         /// 把DataTable导出到一个Excel文件，并增加表头
 68         /// </summary>
 69         /// <param name="fileName">Excel文件名</param>
 70         /// <param name="table">要导出的DataTable</param>
 71         public static void ExportExcel(string fileName, DataTable table)
 72         {
 73             ExportExcel(fileName, table, true);
 74         }
 75 
 76         /// <summary>
 77         /// 把DataTable导出到一个Excel文件
 78         /// </summary>
 79         /// <param name="fileName">Excel文件名</param>
 80         /// <param name="table">要导出的DataTable</param>
 81         /// <param name="AddHeader">是否要增加表头</param>
 82         public static void ExportExcel(string fileName, DataTable table, bool AddHeader)
 83         {
 84 
 85             object missing = System.Reflection.Missing.Value;
 86             Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
 87             app.Application.Workbooks.Add(true);
 88             Excel.Workbook book = (Excel.Workbook)app.ActiveWorkbook;
 89             Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet;
 90 
 91             if (AddHeader)
 92                 for (int i = 0; i < table.Columns.Count; i++)
 93                     sheet.Cells[1, i+1] = table.Columns[i].ColumnName;
 94 
 95             for (int i = 0, ei = AddHeader ? 2 : 1; i < table.Rows.Count; i++, ei++)
 96                 for (int j = 0; j < table.Columns.Count; j++)
 97                     sheet.Cells[ei, j+1] = table.Rows[i][j];
 98 
 99 
100             string path = fileName.Substring(0, fileName.LastIndexOf('\\') + 1).Trim('\\');
101             if (!Directory.Exists(path))
102                 Directory.CreateDirectory(path);
103 
104             book.SaveCopyAs(fileName);
105             //关闭文件
106             book.Close(false, missing, missing);
107             //退出excel
108             app.Quit();
109         }
110 
111         #endregion
112 
113         #region 通用读取
114 
115         /// <summary>
116         /// 执行SQL查询一个Excel文档的内容 
117         /// </summary>
118         /// <param name="fileName">Excel文件名</param>
119         /// <param name="cmdText">要执行的SQL 区域选择用 [Sheet1$A1:C7]形式</param>
120         /// <param name="paramters">查询参数</param>
121         /// <returns>查询结果</returns>
122         public static DataTable ExecuteReader(string fileName, string cmdText, params OleDbParameter[] paramters)
123         {
124             string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
125             OleDbConnection cn = null;
126             OleDbCommand cmd = new OleDbCommand();
127             OleDbDataReader reader = null;
128             DataTable table = new DataTable();
129             try
130             {
131                 cn = new OleDbConnection(strCon);
132                 PrepareCommand(cmd, cn, cmdText, paramters);
133                 reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
134                 table.Load(reader);
135                 cmd.Parameters.Clear();
136             }
137             finally
138             {
139                 if (reader != null)
140                 {
141                     reader.Close();
142                     reader.Dispose();
143                 }
144                 if (cn != null)
145                 {
146                     cn.Close();
147                     cn.Dispose();
148                 }
149             }
150             return table;
151         }
152 
153         /// <summary>
154         /// 执行SQL查询一个Excel文档默认工作表Sheet1的内容 
155         /// </summary>
156         /// <param name="fileName">Excel文件名</param>
157         /// <param name="cmdText">查询参数</param>
158         /// <returns>查询结果</returns>
159         public static DataTable ExecuteReader(string fileName, string cmdText)
160         {
161             return ExecuteReader(fileName, cmdText, null);
162         }
163 
164         #endregion
165 
166         #region 通用编辑
167 
168         /// <summary>
169         /// 编辑一个Excel文档
170         /// </summary>
171         /// <param name="fileName">Excel文件名</param>
172         /// <param name="table">要编辑的内容</param>
173         /// <param name="startCell">目标单元格位置</param>
174         public static void EditExcel(string fileName, DataTable table, string startCell)
175         {
176             Tuple<int, int> cell = ConvertCell(startCell);
177 
178             object missing = System.Reflection.Missing.Value;
179             Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
180             app.Application.Workbooks.Add(true);
181 
182             Excel.Workbook book = null;
183             Excel.Worksheet sheet = null;
184             bool existFile=IsExistFile(fileName);
185             if (existFile)
186             {
187                 book=app.Workbooks.Open(fileName, 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true); 
188             }
189             else {
190                 book = (Excel.Workbook)app.ActiveWorkbook;
191             }
192             sheet = (Excel.Worksheet)book.ActiveSheet;
193 
194             for (int i = 0,ei=cell.Item1; i < table.Rows.Count; i++,ei++)
195                 for (int j = 0, ej = cell.Item2; j < table.Columns.Count; j++, ej++)
196                     sheet.Cells[ei, ej] = table.Rows[i][j];
197            
198 
199             if(existFile) book.Save();
200             else book.SaveCopyAs(fileName);
201             //关闭文件
202             book.Close(false, missing, missing);
203             //退出excel
204             app.Quit();
205 
206         }
207 
208         /// <summary>
209         /// 编辑一个Excel文档
210         /// </summary>
211         /// <param name="fileName">Excel文件名</param>
212         /// <param name="table">要编辑的内容</param>
213         public static void EditExcel(string fileName, DataTable table)
214         {
215             EditExcel(fileName, table, "A1");
216         }
217 
218         #endregion
219 
220         #endregion
221 
222         #region 私有方法
223 
224         /// <summary>
225         /// 检查文件是否存在，若不存在则会先确保文件所在的目录存在
226         /// </summary>
227         /// <param name="fileName">文件名</param>
228         /// <returns>检查结果</returns>
229         private static bool IsExistFile(string fileName)
230         {
231             if(File.Exists(fileName))return true;
232             string path = fileName.Substring(0, fileName.LastIndexOf('\\') + 1).Trim('\\');
233             if (!Directory.Exists(path))
234                 Directory.CreateDirectory(path);
235             return false;
236         }
237 
238         /// <summary>
239         /// 转换单元格位置
240         /// </summary>
241         /// <param name="cell">单元格位置</param>
242         /// <returns>int二元组</returns>
243         private static Tuple<int, int> ConvertCell(string cell)
244         { 
245             Match colM=Regex.Match(cell,@"[a-zA-Z]+");
246             if (string.IsNullOrEmpty(colM.Value))
247                 throw new Exception("单元格格式有误！");
248             string colStr = colM.Value.ToUpper();
249             int colIndex = 0;
250             foreach (char ci in colStr)
251                 colIndex += 1+(ci - 'A');
252 
253             Match rowM = Regex.Match(cell, @"\d+");
254             if(string.IsNullOrEmpty(rowM.Value))
255                 throw new Exception("单元格格式有误！");
256             int rowIndex =Convert.ToInt32( rowM.Value);
257 
258             Tuple<int, int> result = new Tuple<int, int>(rowIndex,colIndex);
259             return result;
260         }
261 
262         /// <summary>
263         /// 准备OleDbCommand
264         /// </summary>
265         /// <param name="command">查询命令</param>
266         /// <param name="connection">连接类</param>
267         /// <param name="cmdText">命令内容</param>
268         /// <param name="paramters">查询参数</param>
269         private static void PrepareCommand(OleDbCommand command, OleDbConnection connection, string cmdText, params OleDbParameter[] paramters)
270         {
271             command.CommandText = cmdText;
272             command.Connection = connection;
273 
274             if (paramters != null)
275                 foreach (OleDbParameter item in paramters)
276                     command.Parameters.Add(item);
277 
278             if (connection.State !=  ConnectionState.Open)
279                 connection.Open();
280         }
281 
282         #endregion
283     }
284 }

这里有个连接通向用NPOI封装的ExcelHelper类：另一个ExcelHelper

posted @ 2013-03-14 19:16 猴健居士阅读(5653) 评论(10) 编辑收藏举报

刷新页面返回顶部

一个Excel的帮助类——ExcelHelper

公告