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: }