1 /// <summary>
2 /// Excel导入/导出通用类
3 /// </summary>
4 public class ExcelHelper
5 {
6 /// <summary>
7 /// 是否是版本号为12的Excel文件
8 /// </summary>
9 private static string FileExt = ".xls";
10 /// <summary>
11 /// 构造Excel的连接字符串
12 /// </summary>
13 /// <param name="excelPath"></param>
14 /// <returns></returns>
15 private static string ExcelConnectionString(string excelPath)
16 {
17 if (!excelPath.ToLower().Contains(".xlsx"))
18 {
19 return string.Format(
20 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;",
21 excelPath);
22 }
23 return string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'",
24 excelPath);
25
26 }
27
28 /// <summary>
29 /// 构造Excel的连接字符串
30 /// </summary>
31 /// <param name="excelPath"></param>
32 /// <returns></returns>
33 private static string ExcelConnectionString(string excelPath, string fileExt)
34 {
35 FileExt = fileExt;
36 if (Regex.IsMatch(FileExt, @"^\.?xls$", RegexOptions.IgnoreCase))
37 return string.Format(
38 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;",
39 excelPath);
40 else
41 return string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'",
42 excelPath);
43 }
44
45 /// <summary>
46 /// 由EXCEL转换成DataTable(Excel绝对路径)
47 /// </summary>
48 /// <param name="excelPath"></param>
49 /// <returns></returns>
50 public static DataTable GetDataTable(string excelPath)
51 {
52 string selectCommandText = "select * from [sheet1$]";
53
54 DataSet dataSet = null;
55 dataSet = new DataSet();
56 new OleDbDataAdapter(selectCommandText, ExcelConnectionString(excelPath)).Fill(dataSet);
57 if (dataSet.Tables.Count > 0)
58 {
59 return dataSet.Tables[0];
60 }
61
62 return null;
63 }
64
65 /// <summary>
66 /// 由EXCEL转换成DataTable(Excel绝对路径,Sheet名称)
67 /// </summary>
68 /// <param name="strpath"> 文件路径及文件名 </param>
69 /// <param name="SheetName">工作表名称</param>
70 /// <returns> </returns>
71 public static DataTable GetDataTable(String strpath, string SheetName)
72 {
73 OleDbDataAdapter myCommand = new OleDbDataAdapter(
74 "SELECT * FROM [" + SheetName + "$A1:Z1002]", ExcelConnectionString(strpath));
75
76 DataTable dt = new DataTable();
77 myCommand.Fill(dt);
78 return dt;
79 }
80
81
82
83 /// <summary>
84 /// 由EXCEL转换成DataTable(Excel绝对路径)
85 /// </summary>
86 /// <param name="excelPath"></param>
87 /// <returns></returns>
88 public static DataTable GetDataTable1(string excelPath)
89 {
90 string selectCommandText = "select * from [sheet1$A3:F35] ";
91
92 DataSet dataSet = null;
93 dataSet = new DataSet();
94 new OleDbDataAdapter(selectCommandText, ExcelConnectionString(excelPath)).Fill(dataSet);
95 if (dataSet.Tables.Count > 0)
96 {
97 return dataSet.Tables[0];
98 }
99
100 return null;
101 }
102
103 }
