将Excel读入DateTable的问题，兼谈考虑复用的设计
有位兄弟写了将Excel读入DataTable的代码如下：
将连接字符串的Extended Properties=\"Excel 8.0;HDR=1\"改为Extended Properties=\"Excel 8.0;HDR=1;IMEX=1\"，指定IMEX=1将所有读入数据看作字符,否则出现数字/汉字同列不相容等现象，正如指定HDR=yes或no可以选择第一列是否当标题看待一样。可惜HDR的指定网络上说明较多而IMEX的说明网上较少。下面是将Excel读入DataTable的修改后代码：
1
using System;
2using System.Data;
3using System.Configuration;
4using System.Web;
5using System.Web.Security;
6using System.Web.UI;
7using System.Web.UI.WebControls;
8using System.Web.UI.WebControls.WebParts;
9using System.Web.UI.HtmlControls;
10using System.IO;
11using System.Reflection;
12using System.Runtime.InteropServices;
13
14
15
/// <summary>
16
/// 将Excel读取到DataTable中
17/// <!-- ------------------------------------------------------ -->
18/// <!-- Copyright ××××××××××× -->
19/// <!-- Author:××××× -->
20/// <!-- Date:××××× -->
21/// <!-- Copyright ××××××××××× -->
22/// <!-- Please see ××××××××××× for terms of use. -->
23/// <!-- ------------------------------------------------------ -->
24
/// </summary>
25public class ExcelToDataTable
26{
27 public ExcelToDataTable()
28
{
29 //
30 // TODO: Add constructor logic here
31 //
32
}
33 /// <summary>
34 /// 将上传Excel文件的一个Sheet读取到DataTable
35 /// 通过传入sheet名称读取
36 /// </summary>
37 /// <param name="ExcelPath">excel文件所在路径</param>
38 /// <param name="SheetName">工作表名</param>
39 /// <param name="Range">读取数据区域(矩形范围,如A1:H4等)</param>
40 /// <returns></returns>
41 public static DataTable ExcelToDTBySheetName(string ExcelPath, string SheetName, string Range)
42 {
43 System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection();
44 System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter();
45 System.Data.OleDb.OleDbCommand ocmd = new System.Data.OleDb.OleDbCommand();
46 System.Data.DataSet ds = new DataSet();
47 oconn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + ExcelPath + ";Extended Properties=\"Excel 8.0;HDR=1\"";
48 oda.SelectCommand = ocmd;
49 ocmd.Connection = oconn;
50 oconn.Open();
51 ocmd.CommandText = "select * from [" + SheetName + "$" + Range + "]";
52 oda.Fill(ds);
53 oconn.Close();
54 DataTable dt = ds.Tables[0];
55 return dt;
56 }
57 /// <summary>
58 /// 将上传Excel文件的一个Sheet读取到DataTable
59 /// 通过传入sheet序号读取,起始序号为0
60 /// </summary>
61 /// <param name="ExcelPath">excel文件所在路径</param>
62 /// <param name="SheetName">工作表名</param>
63 /// <param name="Range">读取数据区域(矩形范围,如A1:H4等)</param>
64 /// <returns></returns>
65 public static DataTable ExcelToDTBySheetIndex(string ExcelPath, int SheetIndex, string Range)
66 {
67 System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection();
68 System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter();
69 System.Data.OleDb.OleDbCommand ocmd = new System.Data.OleDb.OleDbCommand();
70 System.Data.DataSet ds = new DataSet();
71 oconn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + ExcelPath + ";Extended Properties=\"Excel 8.0;HDR=1\"";
72 oda.SelectCommand = ocmd;
73 ocmd.Connection = oconn;
74 oconn.Open();
75 DataTable schematable = oconn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
76 string SheetName = schematable.Rows[SheetIndex][2].ToString().Trim();
77 ocmd.CommandText = "select * from [" + SheetName + Range + "]";
78 oda.Fill(ds);
79 oconn.Close();
80 DataTable dt = ds.Tables[0];
81 return dt;
82 }
83}
84
这位兄弟遇到的问题是读取出来的数据如果一列中既有数字又有字符读取结果会统一按照数字对待或按照字符对待，错误格式化为与原Excel不相同的数据。于是他强制编码啊，设定文化啊什么的折腾了一下午也没有变化，遂来问我，告诉他解决方法后该兄弟很吃惊兼郁闷，愤愤不平为什么有这样的参数。因为我改的地方只有一处：using System;2
using System.Data;3
using System.Configuration;4
using System.Web;5
using System.Web.Security;6
using System.Web.UI;7
using System.Web.UI.WebControls;8
using System.Web.UI.WebControls.WebParts;9
using System.Web.UI.HtmlControls;10
using System.IO;11
using System.Reflection;12
using System.Runtime.InteropServices;13
14
15
/// <summary>
16
/// 将Excel读取到DataTable中17
/// <!-- ------------------------------------------------------ -->18
/// <!-- Copyright ××××××××××× -->19
/// <!-- Author:××××× -->20
/// <!-- Date:××××× -->21
/// <!-- Copyright ××××××××××× -->22
/// <!-- Please see ××××××××××× for terms of use. -->23
/// <!-- ------------------------------------------------------ -->24
/// </summary>25
public class ExcelToDataTable26
{
27
public ExcelToDataTable()28
{
29
//30
// TODO: Add constructor logic here31
//32
}33
/// <summary>34
/// 将上传Excel文件的一个Sheet读取到DataTable 35
/// 通过传入sheet名称读取36
/// </summary>37
/// <param name="ExcelPath">excel文件所在路径</param>38
/// <param name="SheetName">工作表名</param>39
/// <param name="Range">读取数据区域(矩形范围,如A1:H4等)</param>40
/// <returns></returns>41
public static DataTable ExcelToDTBySheetName(string ExcelPath, string SheetName, string Range)42
{43
System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection();44
System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter();45
System.Data.OleDb.OleDbCommand ocmd = new System.Data.OleDb.OleDbCommand();46
System.Data.DataSet ds = new DataSet();47
oconn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + ExcelPath + ";Extended Properties=\"Excel 8.0;HDR=1\"";48
oda.SelectCommand = ocmd;49
ocmd.Connection = oconn;50
oconn.Open();51
ocmd.CommandText = "select * from [" + SheetName + "$" + Range + "]";52
oda.Fill(ds);53
oconn.Close();54
DataTable dt = ds.Tables[0];55
return dt;56
}57
/// <summary>58
/// 将上传Excel文件的一个Sheet读取到DataTable 59
/// 通过传入sheet序号读取,起始序号为060
/// </summary>61
/// <param name="ExcelPath">excel文件所在路径</param>62
/// <param name="SheetName">工作表名</param>63
/// <param name="Range">读取数据区域(矩形范围,如A1:H4等)</param>64
/// <returns></returns>65
public static DataTable ExcelToDTBySheetIndex(string ExcelPath, int SheetIndex, string Range)66
{67
System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection();68
System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter();69
System.Data.OleDb.OleDbCommand ocmd = new System.Data.OleDb.OleDbCommand();70
System.Data.DataSet ds = new DataSet();71
oconn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + ExcelPath + ";Extended Properties=\"Excel 8.0;HDR=1\"";72
oda.SelectCommand = ocmd;73
ocmd.Connection = oconn;74
oconn.Open();75
DataTable schematable = oconn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);76
string SheetName = schematable.Rows[SheetIndex][2].ToString().Trim();77
ocmd.CommandText = "select * from [" + SheetName + Range + "]";78
oda.Fill(ds);79
oconn.Close();80
DataTable dt = ds.Tables[0];81
return dt;82
}83
}84
将连接字符串的Extended Properties=\"Excel 8.0;HDR=1\"改为Extended Properties=\"Excel 8.0;HDR=1;IMEX=1\"，指定IMEX=1将所有读入数据看作字符,否则出现数字/汉字同列不相容等现象，正如指定HDR=yes或no可以选择第一列是否当标题看待一样。可惜HDR的指定网络上说明较多而IMEX的说明网上较少。下面是将Excel读入DataTable的修改后代码：
1using System;
2using System.Data;
3using System.Configuration;
4using System.Web;
5using System.Web.Security;
6using System.Web.UI;
7using System.Web.UI.WebControls;
8using System.Web.UI.WebControls.WebParts;
9using System.Web.UI.HtmlControls;
10using System.IO;
11using System.Reflection;
12using System.Runtime.InteropServices;
13
14
15/// <summary>
16/// 将Excel读取到DataTable中
17/// <!-- ------------------------------------------------------ -->
18/// <!-- Copyright ××××××××××× -->
19/// <!-- Author:××××× -->
20/// <!-- Date:××××× -->
21/// <!-- Copyright ××××××××××× -->
22/// <!-- Please see ××××××××××× for terms of use. -->
23/// <!-- ------------------------------------------------------ -->
24/// </summary>
25public class ExcelToDataTable
26{
27 public ExcelToDataTable()
28 {
29 //
30 // TODO: Add constructor logic here
31 //
32 }
33 /// <summary>
34 /// 将上传Excel文件的一个Sheet读取到DataTable
35 /// 通过传入sheet名称读取
36 /// </summary>
37 /// <param name="ExcelPath">excel文件所在路径</param>
38 /// <param name="SheetName">工作表名</param>
39 /// <param name="Range">读取数据区域(矩形范围,如A1:H4等)</param>
40 /// <returns></returns>
41 public static DataTable ExcelToDTBySheetName(string ExcelPath, string SheetName, string Range)
42 {
43 System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection();
44 System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter();
45 System.Data.OleDb.OleDbCommand ocmd = new System.Data.OleDb.OleDbCommand();
46 System.Data.DataSet ds = new DataSet();
47 oconn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + ExcelPath + ";Extended Properties=\"Excel 8.0;HDR=1;IMEX=1\"";
48 oda.SelectCommand = ocmd;
49 ocmd.Connection = oconn;
50 oconn.Open();
51 ocmd.CommandText = "select * from [" + SheetName + "$" + Range + "]";
52 oda.Fill(ds);
53 oconn.Close();
54 DataTable dt = ds.Tables[0];
55 return dt;
56 }
57 /// <summary>
58 /// 将上传Excel文件的一个Sheet读取到DataTable
59 /// 通过传入sheet序号读取,起始序号为0
60 /// </summary>
61 /// <param name="ExcelPath">excel文件所在路径</param>
62 /// <param name="SheetName">工作表名</param>
63 /// <param name="Range">读取数据区域(矩形范围,如A1:H4等)</param>
64 /// <returns></returns>
65 public static DataTable ExcelToDTBySheetIndex(string ExcelPath, int SheetIndex, string Range)
66 {
67 System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection();
68 System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter();
69 System.Data.OleDb.OleDbCommand ocmd = new System.Data.OleDb.OleDbCommand();
70 System.Data.DataSet ds = new DataSet();
71 oconn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + ExcelPath + ";Extended Properties=\"Excel 8.0;HDR=1;IMEX=1\"";
72 oda.SelectCommand = ocmd;
73 ocmd.Connection = oconn;
74 oconn.Open();
75 DataTable schematable = oconn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
76 string SheetName = schematable.Rows[SheetIndex][2].ToString().Trim();
77 ocmd.CommandText = "select * from [" + SheetName + Range + "]";
78 oda.Fill(ds);
79 oconn.Close();
80 DataTable dt = ds.Tables[0];
81 return dt;
82 }
83}
84
using System;2
using System.Data;3
using System.Configuration;4
using System.Web;5
using System.Web.Security;6
using System.Web.UI;7
using System.Web.UI.WebControls;8
using System.Web.UI.WebControls.WebParts;9
using System.Web.UI.HtmlControls;10
using System.IO;11
using System.Reflection;12
using System.Runtime.InteropServices;13
14
15
/// <summary>16
/// 将Excel读取到DataTable中17
/// <!-- ------------------------------------------------------ -->18
/// <!-- Copyright ××××××××××× -->19
/// <!-- Author:××××× -->20
/// <!-- Date:××××× -->21
/// <!-- Copyright ××××××××××× -->22
/// <!-- Please see ××××××××××× for terms of use. -->23
/// <!-- ------------------------------------------------------ -->24
/// </summary>25
public class ExcelToDataTable26
{27
public ExcelToDataTable()28
{29
//30
// TODO: Add constructor logic here31
//32
}33
/// <summary>34
/// 将上传Excel文件的一个Sheet读取到DataTable 35
/// 通过传入sheet名称读取36
/// </summary>37
/// <param name="ExcelPath">excel文件所在路径</param>38
/// <param name="SheetName">工作表名</param>39
/// <param name="Range">读取数据区域(矩形范围,如A1:H4等)</param>40
/// <returns></returns>41
public static DataTable ExcelToDTBySheetName(string ExcelPath, string SheetName, string Range)42
{43
System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection();44
System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter();45
System.Data.OleDb.OleDbCommand ocmd = new System.Data.OleDb.OleDbCommand();46
System.Data.DataSet ds = new DataSet();47
oconn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + ExcelPath + ";Extended Properties=\"Excel 8.0;HDR=1;IMEX=1\"";48
oda.SelectCommand = ocmd;49
ocmd.Connection = oconn;50
oconn.Open();51
ocmd.CommandText = "select * from [" + SheetName + "$" + Range + "]";52
oda.Fill(ds);53
oconn.Close();54
DataTable dt = ds.Tables[0];55
return dt;56
}57
/// <summary>58
/// 将上传Excel文件的一个Sheet读取到DataTable 59
/// 通过传入sheet序号读取,起始序号为060
/// </summary>61
/// <param name="ExcelPath">excel文件所在路径</param>62
/// <param name="SheetName">工作表名</param>63
/// <param name="Range">读取数据区域(矩形范围,如A1:H4等)</param>64
/// <returns></returns>65
public static DataTable ExcelToDTBySheetIndex(string ExcelPath, int SheetIndex, string Range)66
{67
System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection();68
System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter();69
System.Data.OleDb.OleDbCommand ocmd = new System.Data.OleDb.OleDbCommand();70
System.Data.DataSet ds = new DataSet();71
oconn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + ExcelPath + ";Extended Properties=\"Excel 8.0;HDR=1;IMEX=1\"";72
oda.SelectCommand = ocmd;73
ocmd.Connection = oconn;74
oconn.Open();75
DataTable schematable = oconn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);76
string SheetName = schematable.Rows[SheetIndex][2].ToString().Trim();77
ocmd.CommandText = "select * from [" + SheetName + Range + "]";78
oda.Fill(ds);79
oconn.Close();80
DataTable dt = ds.Tables[0];81
return dt;82
}83
}84
其实这并不是一个好设计，就像结构化异常处理出来之前用错误号返回错误一样，需要什么查询说明甚至猜测尝试来得到解决之道，除了能让知道的人沾沾自喜、成为不知者的编程障碍外没有多大作用。应该在设计方法时需要避免这种情况发生，以有意义的参数或明确的参数说明帮助使用者在最短时间内掌握正确的方法使用之道。
Excel导到DataTable，问题本身很简单，但折射出很多需要思考的东西。编码层级的设计模式并不能解决所有的问题，我们写每个方法的时候，可曾细细考虑过自己的方法在别人复用时有无人为的障碍？如何才能减少自己的方法、模块、程序集复用的人为障碍？
浙公网安备 33010602011771号