获取Excel工作薄中Sheet页(工作表)名集合

时间:2021-03-15 09:33:48
#region 获取Excel工作薄中Sheet页(工作表)名集合   
02./// <summary>
03./// 获取Excel工作薄中Sheet页(工作表)名集合
04./// </summary>
05./// <param name="excelFile">Excel文件名及路径,EG:C:\Users\JK\Desktop\导入测试.xls</param>
06./// <returns>Sheet页名称集合</returns>
07.private String[] GetExcelSheetNames(string fileName)
08.{
09. OleDbConnection objConn = null;
10. System.Data.DataTable dt = null;
11. try
12. {
13. string connString=string.Empty;
14. string FileType =fileName.Substring(fileName.LastIndexOf("."));
15. if (FileType == ".xls")
16. connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
17. "Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
18. else//.xlsx
19. connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
20. // 创建连接对象
21. objConn = new OleDbConnection(connString);
22. // 打开数据库连接
23. objConn.Open();
24. // 得到包含数据架构的数据表
25. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
26. if (dt == null)
27. {
28. return null;
29. }
30. String[] excelSheets = new String[dt.Rows.Count];
31. int i = 0;
32. // 添加工作表名称到字符串数组
33. foreach (DataRow row in dt.Rows)
34. {
35. string strSheetTableName = row["TABLE_NAME"].ToString();
36. //过滤无效SheetName
37. if (strSheetTableName.Contains("$")&&strSheetTableName.Replace("'", "").EndsWith("$"))
38. {
39. excelSheets[i] = strSheetTableName.Substring(0, strSheetTableName.Length - 1);
40. }
41. i++;
42. }
43. return excelSheets;
44. }
45. catch (Exception ex)
46. {
47. MessageBox.Show(ex.ToString());
48. return null;
49. }
50. finally
51. {
52. // 清理
53. if (objConn != null)
54. {
55. objConn.Close();
56. objConn.Dispose();
57. }
58. if (dt != null)
59. {
60. dt.Dispose();
61. }
62. }
63.}
64.#endregion