Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
推荐这个:"Provider=Microsoft.Ace.OlEDb.12.0;Password=;User ID=;Data Source=C:\MyExcel.xls;Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
string strConn = "Provider=Microsoft.Ace.OlEDb.12.0;Password=;User ID=;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";
//string strConn="Provider=Microsoft.ACE.OLEDB.12.0;Password=;User ID=;Data Source="+filename+";Mode=Share Deny Write;Extended Properties=\"HDR=YES;\";Jet OLEDB:Engine Type=37";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.DataTable dt = new System.Data.DataTable();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + sheetName + "$]", conn);
odda.Fill(dt);
return dt;
(多用一个\"表示必须输出”字符串)
Microsoft.Jet. 不支持EXCEL2007
Microsoft.Ace 兼容之前版本并支持EXCEL2007以后版本
说一下 Extended Properties这几个Key是指什么:
Excel8.0指Excel的驱动版本,从Excel97到2003都用这个,Excel2007中开始用Excel12.0;
HDR 有两个可选的值:Yes或者No,默认是Yes; 指是否需要列标题,Yes表示第一行当作列标题来处理,No则把第一行当作数据内容来处理;一般我们用到的导入导出的Excel都是有标题栏的,那就可以不写这个参数或者设为Yes。
0 is Export mode -------导出模式(从Excel文件读取数据时使用)
1 is Import mode -------导入模式(向Excel文件插入数据时使用)
2 is Linked mode (full update capabilities) ----- Linked模式(需要完全更新Excel文件时使用)
这个值具体有啥影响呢?
上传的Excel数据中如果既有数字型,又有文本型,则上传后读到DataTable中,发现文本的单元格都是空(有时是第一列的数字型值为空); 尝试用模板文件来限定数据类型,根本不顶用!
使用Remark在Excel模板中标注,这个只能提醒User,但也不能保证User输入的结果啊!
使用宏定义来强制转化类型, 大多情况下,都是禁用宏的,客户端的事,更是难以控制,所以这个方法用在这里也不是最好的~!
所以,还是把解决问题的思路回归到Server端: 解决读不到混合格式的Excel数据的问题;
关于Excel中的数据类型缺省值,MS是这样说的:Excel 驱动程序读取指定源中一定数量的行(默认情况下为 8 行)以推测每列的数据类型。如果推测出列可能包含混合数据类型(尤其是混合了文本数据的数值数据时),驱动程序将决定采用占多数的数据类型,并对包含其他类型数据的单元返回空值。(如果各种数据类型的数量相当,则采用数值类型。)Excel 工作表中大部分单元格格式设置选项不会影响此数据类型判断。可以通过指定导入模式来修改 Excel 驱动程序的此行为。
简单点,其实对我们有用就是这样:IMEX=0的时候读取Excel中的数据时,它还是根据上述的默认8行规则来确定数据类型; IMEX设为1时,将把所有单元格数据都作为文本类型来处理; 当设置IMEX为2时,用来更新Excel中的数据用;
问题还没有结束!! 如果在前八行内的数据类型是数字型,且在第八行之后如果出现字符,则字符数据一样的读不到~!
这个问题需要修改注册表来解决:
将Server上注册表中的HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\下的TypeGuessRows这个键值改为一个较大的数; 如果预估一次要上传的资料数量最大为1000的话,那就设为一个比1000稍大的值即可;(注:也不要改太大,因为太大会降低上传Excel时的性能;这个值最大可为65535,)
原因:读取Excel的Microsoft Jet4.0驱动默认按照前八行(标题行除外)来判断该列的数据类型,之前有微软有说法是在读取Excel的数据时,设置连接字符串的扩展参数IMEX=1就会把混合类型的数据列当作文本来处理,但实际上效果并不完全是这样的; 即使设置了IMEX=1,或者把上传的Excel的列数据格式都设置为文本型,Jet驱动其实还是按照前八行来判断数据类型的,只有当前八行数据中出现既有文本型又有数字型(也即前八行为混合型数据),并设置IMEX=1时,该列数据才会当作文本来处理; 所以我们的解决办法就是把MS Jet这个定义的“8”这个值改大,这样当在第八行以后出现字符时,Jet驱动就会把这列值当作文本来处理,这样就能读到值了。
如此一来,只要设置IMEX=1,则任何在设置的这个值范围内的行如果出现混合类型的数据,都将会作为文本来处理,这样就能保证Jet驱动一定能读到Excel中的值