返回信息流读取Excel单元格很是诡异
我只要三列数据，第一列中凡是4.5.2这种都读不出来，4.5这种行
不知道为什么。。。
//建立OleDb连接
string connStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " + path + ";Extended Properties=Excel 8.0";
OleDbConnection excelConn = new OleDbConnection(connStr);
//打开连接
excelConn.Open();
//定义查询命令
string excelCmd = "SELECT * FROM [预投产品零部件数据库$]";
OleDbCommand cmd = new OleDbCommand(excelCmd,excelConn);
//查询数据库
OleDbDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
string mylabel = reader[0].ToString() + '(' + reader[1].ToString() + reader[2].ToString() + ')';
MessageBox.Show(mylabel);
}
这是一条镜像帖。来源：北邮人论坛 / soft-design / #22585同步于 2007/11/30
该镜像源已超过 30 天没有更新，可能在源站已被删除。
SoftDesign机器人发帖
c#读取excel单元格有些读不出来，网上也查不到，求牛人指点
tiemeng
2007/11/30镜像同步4 回复
订阅后，新回复会通过你的通知中心匿名送达。
4 条回复
连接字符串有问题，o(∩_∩)o...哈哈
string connStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " + path + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
主要是加上这个;IMEX=1就ok啦
Excel
This is a compiled connection strings reference list on how to connect to Excel.
ODBC
Standard
Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;
SQL syntax "SELECT * FROM [sheet1$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.
COPY TO CLIPBOARD
OLE DB
Standard
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
Important note!
The quota " in the string needs to be escaped using your language specific escape syntax.
c#, c++ \"
VB6, VBScript ""
xml (web.config etc) "
or maybe use a single quota '.
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.
SQL syntax "SELECT * FROM [sheet1$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.
Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance.
If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."
正解，如果不写IMEX=1，那么.net会根据注册表里面一个"TypeGuessRows"字段所定义的行数来判断这一列的数据类型，lz的前8行可能都是数字型的，所以遇到4.5.2这样的字符串就读不出来了。
【 在 Lonhero 的大作中提到: 】
: 连接字符串有问题，o(∩_∩)o...哈哈
: string connStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " + path + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
: 主要是加上这个;IMEX=1就ok啦