#region 讀取Excel中的數據
02./// <summary>
03./// 讀取Excel中的數據
04./// </summary>
05./// <param name="excelFile">Excel文件名及路徑,EG:C:\Users\JK\Desktop\導入測試.xls</param>
06./// <returns>Excel中的數據</returns>
07.private DataTable GetTable(string fileName)
08.{
09. OleDbConnection objConn = null;
10. System.Data.DataTable dt = null;
11. string connString = string.Empty;
12. OleDbDataAdapter da = new OleDbDataAdapter();
13. //獲取Excel工作薄中Sheet頁(工作表)名集合
14. String[] ss = this.GetExcelSheetNames(fileName);
15. DataTable dataTable = new DataTable();
16. try
17. {
18. string FileType = fileName.Substring(fileName.LastIndexOf("."));
19. if (FileType == ".xls")
20. connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
21. "Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
22. else//.xlsx
23. connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
24. // 創建連接對象
25. objConn = new OleDbConnection(connString);
26. // 打開數據庫連接
27. objConn.Open();
28.
29. string sql_F = "Select * FROM [{0}]";
30. for (int i = 0; i < ss.Length;i++ )
31. {
32. da.SelectCommand = new OleDbCommand(String.Format(sql_F, ss[i].ToString() + "$"), objConn);
33. da.Fill(dataTable);
34. MessageBox.Show("第"+i+"次表中數據量="+dataTable.Rows.Count.ToString());
35. }
36. dataTable = DeleteBlank(dataTable,9);
37. MessageBox.Show("刪除空行後,表中數據量=" + dataTable.Rows.Count.ToString());
38. return dataTable;
39. }
40. catch (Exception ex)
41. {
42. MessageBox.Show(ex.ToString());
43. return null;
44. }
45. finally
46. {
47. // 清理
48. if (objConn != null)
49. {
50. objConn.Close();
51. objConn.Dispose();
52. }
53. if (dt != null)
54. {
55. dt.Dispose();
56. }
57. }
58.}
59.
60.#endregion
本文URL：http://www.bianceng.cn/Programming/csharp/201410/45598.htm
#region 刪除指定表中的空白行
02./// <summary>
03.///刪除指定表中的空白行
04./// </summary>
05./// <param name="dt">表名</param>
06./// <param name="ColNum">Excel中的列數</param>
07./// <returns>刪除空白行後的DataTable</returns>
08.private DataTable DeleteBlank(DataTable dt,int ColNum)
09.{
10. if (dt == null || dt.Rows.Count==0)
11. {
12. return dt;
13. }
14. //刪除其中的空行(注意for循環的形式)
15. for (int i = dt.Rows.Count - 1; i >= 0; i--)
16. {
17. DataRow row = dt.Rows[i];
18. bool flag = true;
19. //當某行的ColNum列,均為空時,改行為空
20. for (int j = 0; j < ColNum; j++)
21. {
22. object o = row[j];
23. if (o != DBNull.Value && Convert.ToString(o).Trim().Length > 0)
24. {
25. flag = false;
26. break;
27. }
28. }
29. if (flag)
30. {
31. dt.Rows[i].Delete();
32. }
33. }
34. dt.AcceptChanges();
35. //把行中DBNull列替換成空字符串
36. for (int k = dt.Rows.Count - 1; k >= 0; k--)
37. {
38. DataRow row = dt.Rows[k];
39. for (int z = 0; z < ColNum; z++)
40. {
41. object o = row[z];
42. if (o == DBNull.Value)
43. {
44. if (dt.Columns[z].DataType == typeof(string))
45. {
46. row[z] = "";
47. }
48. }
49. }
50. }
51. dt.AcceptChanges();
52. return dt;
53.}
54.#endregion
小注：
讀取Excel的時候，會自動處理表頭。
