2012,我的C#全能Excel操作（无需Office,不使用XML）
其实我也清楚，这并不是全能，这样写标题也就是只有等待拍砖的份了。不过话又说回来，对Excel的操作，方法多得很，有的还真不敢用，现在这个是在我的项目中使用着的，今天等放假（下班就开始放年假），总结总结。
我说的全能非所谓的全能也，只是我自己的功能已满足罢了。
话题未放正，正经一点。
有时候，Excel操作会成为我们程序员的痛处。
COM的方式处理？杀进程？需要安装Office？
No,nononono!这些都不想了。
我只要OleDb就够用了。
读取内容产生DataSet？ 没问题！
创建Excel文件？ 没问题！
在Excel文件中创建Sheet? 没问题！
我要取出Excel文件中的所有表名？ 没问题！
我要以索引来操作Sheet,不是名字行不？ 没问题！
导出的数据太多，要分开多个Sheet放，行不？ 没问题！
我要再扩展，可不可以？ 代码都给你了，你说呢？
代码总体功能如下：
调用的方式如下图：
测试数据如下图：
产生的文件：
分Sheet写入内容：
想下班了，直接给代码吧，有一些注释应该够了，就不再多说了。
记得记得开开心心过年！
1: using System;
2: using System.Collections.Generic;
3: using System.Text;
4: using System.Data;
5: using System.Data.OleDb;
6: using System.IO;
7:
8: namespace Core.DarrenExcelHelper
9: {
10: /// <summary>
11: /// 描述：對Excel文件的創建表、讀取、寫入數據操作.
12: /// 程序員：谢堂文(Darren Xie)
13: /// 創建日期：
14: /// 版本：1.0
15: /// </summary>
16: public static class MyExcelUtls
17: {
18: #region 取文件的擴展名
19: /// <summary>
20: /// 取文件的擴展名
21: /// </summary>
22: /// <param name="FileName">文件名稱</param>
23: /// <returns>string</returns>
24: public static string GetExtFileTypeName(string FileName)
25: {
26: string sFile = FileName;// myFile.PostedFile.FileName;
27: sFile = sFile.Substring(sFile.LastIndexOf("\\") + 1);
28: sFile = sFile.Substring(sFile.LastIndexOf(".")).ToLower();
29: return sFile;
30: }
31: #endregion
32:
33: #region 檢查一個文件是不是2007版本的Excel文件
34: /// <summary>
35: /// 檢查一個文件是不是2007版本的Excel文件
36: /// </summary>
37: /// <param name="FileName">文件名稱</param>
38: /// <returns>bool</returns>
39: public static bool IsExcel2007(string FileName)
40: {
41: bool r;
42: switch (GetExtFileTypeName(FileName))
43: {
44: case ".xls":
45: r = false;
46: break;
47: case ".xlsx":
48: r = true;
49: break;
50: default:
51: throw new Exception("你要檢查" + FileName + "是2007版本的Excel文件還是之前版本的Excel文件，但是這個文件不是一個有效的Excel文件。");
52:
53: }
54: return r;
55: }
56:
57: #endregion
58:
59: #region Excel的連接串
60: //Excel的連接串
61: //2007和之前的版本是有區別的，但是新的可以讀取舊的
62:
63: /// <summary>
64: /// Excel文件在服務器上的OLE連接字符串
65: /// </summary>
66: /// <param name="excelFile">Excel文件在服務器上的路徑</param>
67: /// <param name="no_HDR">第一行不是標題：true;第一行是標題：false;</param>
68: /// <returns>String</returns>
69: public static String GetExcelConnectionString(string excelFile, bool no_HDR)
70: {
71:
72: try
73: {
74: if (no_HDR)
75: {
76: if (IsExcel2007(excelFile))
77: {
78: return "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + excelFile + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件
79: }
80: else
81: {
82: return "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + excelFile + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件
83:
84: }
85: }
86: else
87: {
88: return GetExcelConnectionString(excelFile);
89: }
90: }
91: catch (Exception ee)
92: {
93: throw new Exception(ee.Message);
94: }
95: }
96: /// <summary>
97: /// Excel文件在服務器上的OLE連接字符串
98: /// </summary>
99: /// <param name="excelFile">Excel文件在服務器上的路徑</param>
100: /// <returns>String</returns>
101: public static String GetExcelConnectionString(string excelFile)
102: {
103: try
104: {
105: if (IsExcel2007(excelFile))
106: {
107: return "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + excelFile + ";Extended Properties='Excel 12.0; IMEX=1'"; //此连接可以操作.xls与.xlsx文件
108: }
109: else
110: {
111: return "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + excelFile + ";Extended Properties='Excel 8.0; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件
112:
113: }
114: }
115: catch (Exception ee)
116: {
117: throw new Exception(ee.Message);
118: }
119: }
120: /// <summary>
121: /// Excel文件在服務器上的OLE連接字符串
122: /// </summary>
123: /// <param name="excelFile">Excel文件在服務器上的路徑</param>
124: /// <returns>String</returns>
125: public static String GetExcelConnectionStringByWrite(string excelFile)
126: {
127: try
128: {
129: if (IsExcel2007(excelFile))
130: {
131: return "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + excelFile + ";Extended Properties='Excel 12.0;'"; //此连接可以操作.xls与.xlsx文件
132: }
133: else
134: {
135: return "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + excelFile + ";Extended Properties='Excel 8.0;'"; //此连接只能操作Excel2007之前(.xls)文件
136:
137: }
138: }
139: catch (Exception ee)
140: {
141: throw new Exception(ee.Message);
142: }
143: }
144: #endregion
145:
146: #region 讀取Excel中的所有表名
147: //讀取Excel中的所有表名
148: //读取Excel文件时，可能一个文件中会有多个Sheet，因此获取Sheet的名称是非常有用的
149:
150: /// <summary>
151: /// 根据Excel物理路径获取Excel文件中所有表名,列名是TABLE_NAME
152: /// </summary>
153: /// <param name="excelFile">Excel物理路径</param>
154: /// <returns>DataTable</returns>
155: public static System.Data.DataTable GetExcelSheetNames2DataTable(string excelFile)
156: {
157: OleDbConnection objConn = null;
158: System.Data.DataTable dt = null;
159:
160: try
161: {
162: string strConn = GetExcelConnectionString(excelFile);
163: objConn = new OleDbConnection(strConn);
164: objConn.Open();
165: dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
166: if (dt == null)
167: {
168: return null;
169: }
170: return dt;
171: }
172: catch (Exception ee)
173: {
174: throw new Exception(ee.Message);
175: }
176: finally
177: {
178: if (objConn != null)
179: {
180: objConn.Close();
181: objConn.Dispose();
182: }
183: if (dt != null)
184: {
185: dt.Dispose();
186: }
187: }
188: }
189:
190: /// <summary>
191: /// 根据Excel物理路径获取Excel文件中所有表名
192: /// </summary>
193: /// <param name="excelFile">Excel物理路径</param>
194: /// <returns>String[]</returns>
195: public static String[] GetExcelSheetNames(string excelFile)
196: {
197: System.Data.DataTable dt = null;
198:
199: try
200: {
201:
202: dt = GetExcelSheetNames2DataTable(excelFile);
203: if (dt == null)
204: {
205: return null;
206: }
207: String[] excelSheets = new String[dt.Rows.Count];
208: int i = 0;
209: foreach (DataRow row in dt.Rows)
210: {
211: excelSheets[i] = row["TABLE_NAME"].ToString();
212: i++;
213: }
214:
215: return excelSheets;
216: }
217: catch (Exception ee)
218: {
219: throw new Exception(ee.Message);
220: }
221: finally
222: {
223: if (dt != null)
224: {
225: dt.Dispose();
226: }
227: }
228: }
229: /// <summary>
230: /// 根据Excel物理路径获取Excel文件中所有表名
231: /// </summary>
232: /// <param name="excelFile">Excel物理路径</param>
233: /// <returns>String[]</returns>
234: public static List<string> GetExcelSheetNames2List(string excelFile)
235: {
236: List<string> l = new List<string>();
237: try
238: {
239: if (File.Exists(excelFile))//如果文件不存在，就不用檢查了，一定是0個表的
240: {
241: string[] t = GetExcelSheetNames(excelFile);
242: foreach (string s in t)
243: {
244: string ss = s;
245: if (ss.LastIndexOf('$') > 0)
246: {
247: ss = ss.Substring(0, ss.Length - 1);
248: }
249: l.Add(ss);
250: }
251: }
252: return l;
253: }
254: catch (Exception ee)
255: {
256: throw ee;
257: }
258:
259: }
260: #endregion
261:
262: #region Sheet2DataTable
263: /// <summary>
264: /// 獲取Excel文件中指定SheetName的內容到DataTable
265: /// </summary>
266: /// <param name="FileFullPath">Excel物理路径</param>
267: /// <param name="SheetName">SheetName</param>
268: /// <param name="no_HDR">第一行不是標題：true;第一行是標題：false;</param>
269: /// <returns>DataTable</returns>
270: public static DataTable GetExcelToDataTableBySheet(string FileFullPath, string SheetName, bool no_HDR)
271: {
272: try
273: {
274: return GetExcelToDataSet(FileFullPath, no_HDR, SheetName).Tables[SheetName];
275: }
276: catch (Exception ee)
277: {
278: throw new Exception(ee.Message);
279: }
280: }
281: /// <summary>
282: /// 獲取Excel文件中指定SheetName的內容到DataTable
283: /// </summary>
284: /// <param name="FileFullPath">Excel物理路径</param>
285: /// <param name="SheetName">SheetName</param>
286: /// <returns>DataTable</returns>
287: public static DataTable GetExcelToDataTableBySheet(string FileFullPath, string SheetName)
288: {
289: try
290: {
291: return GetExcelToDataTableBySheet(FileFullPath, SheetName, false);
292: }
293: catch (Exception ee)
294: {
295: throw new Exception(ee.Message);
296: }
297: }
298: #endregion
299:
300: #region Excel2DataSet
301: /// <summary>
302: /// 獲取Excel文件中所有Sheet的內容到DataSet，以Sheet名做DataTable名
303: /// </summary>
304: /// <param name="FileFullPath">Excel物理路径</param>
305: /// <param name="no_HDR">第一行不是標題：true;第一行是標題：false;</param>
306: /// <returns>DataSet</returns>
307: public static DataSet GetExcelToDataSet(string FileFullPath, bool no_HDR)
308: {
309: try
310: {
311: string strConn = GetExcelConnectionString(FileFullPath, no_HDR);
312: OleDbConnection conn = new OleDbConnection(strConn);
313: conn.Open();
314: DataSet ds = new DataSet();
315: foreach (string colName in GetExcelSheetNames(FileFullPath))
316: {
317: OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", colName), conn); //("select * from [Sheet1$]", conn);
318: odda.Fill(ds, colName);
319: }
320: conn.Close();
321: return ds;
322: }
323: catch (Exception ee)
324: {
325: throw new Exception(ee.Message);
326: }
327: }
328: /// <summary>
329: /// 獲取Excel文件中指定Sheet的內容到DataSet，以Sheet名做DataTable名
330: /// </summary>
331: /// <param name="FileFullPath">Excel物理路径</param>
332: /// <param name="no_HDR">第一行不是標題：true;第一行是標題：false;</param>
333: /// <param name="SheetName">第一行不是標題：true;第一行是標題：false;</param>
334: /// <returns>DataSet</returns>
335: public static DataSet GetExcelToDataSet(string FileFullPath, bool no_HDR, string SheetName)
336: {
337: try
338: {
339: string strConn = GetExcelConnectionString(FileFullPath, no_HDR);
340: OleDbConnection conn = new OleDbConnection(strConn);
341: conn.Open();
342: DataSet ds = new DataSet();
343: OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", SheetName), conn); //("select * from [Sheet1$]", conn);
344: odda.Fill(ds, SheetName);
345: conn.Close();
346: return ds;
347: }
348: catch (Exception ee)
349: {
350: throw new Exception(ee.Message);
351: }
352: }
353: #endregion
354:
355: #region 刪除過時文件
356: //刪除過時文件
357: public static bool DeleteOldFile(string servepath)
358: {
359: try
360: {
361: FileInfo F = new FileInfo(servepath);
362: F.Delete();
363: return true;
364: }
365: catch (Exception ee)
366: {
367: throw new Exception(ee.Message + "刪除" + servepath + "出錯.");
368: }
369: }
370: #endregion
371:
372: #region 在Excel文件中創建表,Excel物理路径如果文件不是一個已存在的文件，會自動創建文件
373: /// <summary>
374: /// 在一個Excel文件中創建Sheet
375: /// </summary>
376: /// <param name="servepath">Excel物理路径,如果文件不是一個已存在的文件，會自動創建文件</param>
377: /// <param name="sheetName">Sheet Name</param>
378: /// <param name="cols">表頭列表</param>
379: /// <returns>bool</returns>
380: public static bool CreateSheet(string servepath,string sheetName,string[] cols)
381: {
382: try
383: {
384: if (sheetName.Trim() == "")
385: {
386: throw new Exception( "需要提供表名。");
387: }
388: //if (!File.Exists(servepath))
389: //{
390: // throw new Exception(servepath+"不是一個有效的文件路徑。");
391: //}
392: if(cols.Equals(null))
393: {
394: throw new Exception("創建表需要提供字段列表。");
395: }
396: using (OleDbConnection conn = new OleDbConnection(GetExcelConnectionStringByWrite(servepath)))
397: {
398: conn.Open();
399: OleDbCommand cmd = new OleDbCommand();
400: cmd.Connection = conn;
401: if (sheetName.LastIndexOf('$') > 0)
402: {
403: sheetName = sheetName.Substring(sheetName.Length-1);
404: }
405: cmd.CommandType = CommandType.Text;
406: cmd.CommandTimeout = 3600;
407: StringBuilder sql = new StringBuilder();
408: sql.Append("CREATE TABLE [" + sheetName + "](");
409: foreach (string s in cols)
410: {
411: sql.Append("[" + s + "] text,");
412: }
413: sql = sql.Remove(sql.Length - 1, 1);
414: sql.Append(")");
415: cmd.CommandText = sql.ToString();
416: cmd.ExecuteNonQuery();
417: return true;
418: }
419: }
420: catch(Exception ee)
421: {
422: throw ee;
423: }
424: }
425: #endregion
426:
427: #region DataTable2Sheet,把一個DataTable寫入Excel中的表,Excel物理路径,如果文件不是一個已存在的文件，會自動創建文件
428: /// <summary>
429: /// 把一個DataTable寫入到一個或多個Sheet中
430: /// </summary>
431: /// <param name="servepath">Excel物理路径,如果文件不是一個已存在的文件，會自動創建文件</param>
432: /// <param name="dt">DataTable</param>
433: /// <returns>bool</returns>
434: public static bool DataTable2Sheet(string servepath, DataTable dt)
435: {
436: try
437: {
438: return DataTable2Sheet(servepath, dt, dt.TableName);
439: }
440: catch (Exception ee)
441: {
442: throw ee;
443: }
444: }
445: /// <summary>
446: /// 把一個DataTable寫入到一個或多個Sheet中
447: /// </summary>
448: /// <param name="servepath">Excel物理路径,如果文件不是一個已存在的文件，會自動創建文件</param>
449: /// <param name="dt">DataTable</param>
450: /// <param name="maxrow">一個Sheet的行數</param>
451: /// <returns>bool</returns>
452: public static bool DataTable2Sheet(string servepath, DataTable dt,int maxrow)
453: {
454: try
455: {
456: return DataTable2Sheet(servepath, dt, dt.TableName, maxrow);
457: }
458: catch (Exception ee)
459: {
460: throw ee;
461: }
462: }
463: /// <summary>
464: /// 把一個DataTable寫入到一個或多個Sheet中
465: /// </summary>
466: /// <param name="servepath">Excel物理路径,如果文件不是一個已存在的文件，會自動創建文件</param>
467: /// <param name="dt">DataTable</param>
468: /// <param name="sheetName">Sheet Name</param>
469: /// <returns>bool</returns>
470: public static bool DataTable2Sheet(string servepath, DataTable dt, string sheetName)
471: {
472: try
473: {
474: return DataTable2Sheet(servepath, dt, dt.TableName,0);
475: }
476: catch (Exception ee)
477: {
478: throw ee;
479: }
480: }
481: /// <summary>
482: /// 把一個DataTable寫入到一個或多個Sheet中
483: /// </summary>
484: /// <param name="servepath">Excel物理路径,如果文件不是一個已存在的文件，會自動創建文件</param>
485: /// <param name="dt">DataTable</param>
486: /// <param name="sheetName">Sheet Name</param>
487: /// <param name="maxrow">一個Sheet的行數</param>
488: /// <returns>bool</returns>
489: public static bool DataTable2Sheet(string servepath,DataTable dt,string sheetName,int maxrow)
490: {
491: try
492: {
493: if (sheetName.Trim() == "")
494: {
495: throw new Exception("需要提供表名。");
496: }
497: StringBuilder strSQL = new StringBuilder();
498: //看看目標表是否已存在
499: List<string> tables = GetExcelSheetNames2List(servepath);
500: if (tables.Contains(sheetName))
501: {
502: //存在,直接寫入
503: using (OleDbConnection conn = new OleDbConnection(GetExcelConnectionStringByWrite(servepath)))
504: {
505: conn.Open();
506: OleDbCommand cmd = new OleDbCommand();
507: cmd.Connection = conn;
508: for (int i = 0; i < dt.Rows.Count; i++)
509: {
510: StringBuilder strfield = new StringBuilder();
511: StringBuilder strvalue = new StringBuilder();
512: for (int j = 0; j < dt.Columns.Count; j++)
513: {
514: strfield.Append("[" + dt.Columns[j].ColumnName + "]");
515: strvalue.Append("'" + dt.Rows[i][j].ToString() + "'");
516: if (j != dt.Columns.Count - 1)
517: {
518: strfield.Append(",");
519: strvalue.Append(",");
520: }
521: }
522: if (maxrow == 0)//不需要限制一個表的行數
523: {
524: cmd.CommandText = strSQL.Append(" insert into [" + sheetName + "]( ")
525: .Append(strfield.ToString()).Append(") values (").Append(strvalue).Append(")").ToString() ;
526: }
527: else
528: {
529: //加1才可才防止i=0的情況只寫入一行
530: string sheetNameT=sheetName + ((i+1) / maxrow + (Math.IEEERemainder(i+1, maxrow) == 0 ? 0 : 1)).ToString();
531: if (!tables.Contains(sheetNameT))
532: {
533: tables = GetExcelSheetNames2List(servepath);
534: string[] cols = new string[dt.Columns.Count];
535: for (int ii = 0; ii < dt.Columns.Count; ii++)
536: {
537: cols[ii] = dt.Columns[ii].ColumnName;
538: }
539: if (!(CreateSheet(servepath, sheetNameT, cols)))
540: {
541: throw new Exception("在" + servepath + "上創建表" + sheetName + "失敗.");
542: }
543: else
544: {
545: tables = GetExcelSheetNames2List(servepath);
546: }
547: }
548: cmd.CommandText = strSQL.Append(" insert into [" + sheetNameT + "]( ")
549: .Append(strfield.ToString()).Append(") values (").Append(strvalue).Append(")").ToString() ;
550:
551: }
552: cmd.ExecuteNonQuery();
553: strSQL.Remove(0, strSQL.Length);
554: }
555:
556:
557:
558: conn.Close();
559: }
560: }
561: else
562: {
563: //不存在,需要先創建
564: using (OleDbConnection conn = new OleDbConnection(GetExcelConnectionStringByWrite(servepath)))
565: {
566: conn.Open();
567: OleDbCommand cmd = new OleDbCommand();
568: cmd.Connection = conn;
569: //創建表
570: string[] cols = new string[dt.Columns.Count];
571: for (int i = 0; i < dt.Columns.Count; i++)
572: {
573: cols[i] = dt.Columns[i].ColumnName;
574: }
575:
576: //產生寫數據的語句
577: for (int i = 0; i < dt.Rows.Count; i++)
578: {
579: StringBuilder strfield = new StringBuilder();
580: StringBuilder strvalue = new StringBuilder();
581: for (int j = 0; j < dt.Columns.Count; j++)
582: {
583: strfield.Append("[" + dt.Columns[j].ColumnName + "]");
584: strvalue.Append("'" + dt.Rows[i][j].ToString() + "'");
585: if (j != dt.Columns.Count - 1)
586: {
587: strfield.Append(",");
588: strvalue.Append(",");
589: }
590: }
591: if (maxrow == 0)//不需要限制一個表的行數
592: {
593: if (!tables.Contains(sheetName))
594: {
595: if (!(CreateSheet(servepath, sheetName, cols)))
596: {
597: throw new Exception("在" + servepath + "上創建表" + sheetName + "失敗.");
598: }
599: else
600: {
601: tables = GetExcelSheetNames2List(servepath);
602: }
603: }
604: cmd.CommandText = strSQL.Append(" insert into [" + sheetName + "]( ")
605: .Append(strfield.ToString()).Append(") values (").Append(strvalue).Append(")").ToString() ;
606: }
607: else
608: {
609: //加1才可才防止i=0的情況只寫入一行
610: string sheetNameT=sheetName + ((i+1) / maxrow + (Math.IEEERemainder(i+1, maxrow) == 0 ? 0 : 1)).ToString();
611:
612: if (!tables.Contains(sheetNameT))
613: {
614: for (int ii = 0; ii < dt.Columns.Count; ii++)
615: {
616: cols[ii] = dt.Columns[ii].ColumnName;
617: }
618: if (!(CreateSheet(servepath, sheetNameT, cols)))
619: {
620: throw new Exception("在" + servepath + "上創建表" + sheetName + "失敗.");
621: }
622: else
623: {
624: tables = GetExcelSheetNames2List(servepath);
625: }
626: }
627: cmd.CommandText = strSQL.Append(" insert into [" + sheetNameT + "]( ")
628: .Append(strfield.ToString()).Append(") values (").Append(strvalue).Append(")").ToString();
629:
630: //
631: }
632: cmd.ExecuteNonQuery();
633: strSQL.Remove(0,strSQL.Length);
634: }
635: conn.Close();
636: }
637: }
638: return true;
639: }
640: catch (Exception ee)
641: {
642: throw ee;
643: }
644: }
645: #endregion
646: }
647: }
转载于:https://www.cnblogs.com/yiyumeng/archive/2012/01/19/2327469.html