Excel 单元格值被 OLEDB 提供程序截断
我正在使用 OleDbConnection 类从 Excel 2000/2003 工作簿检索数据:
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filename + ";" +
"Extended Properties=\"Excel 8.0;IMEX=1\";";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
// code to get table name from schema omitted
var dataAdapter = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", name),connection);
var myDataSet = new DataSet();
dataAdapter.Fill(myDataSet, "ExcelInfo");
现在发现工作表中长度大于 255 个字符的单元格被截断。 这是 Microsoft.Jet.OLEDB-provider 中的限制吗?或者我可以对此做些什么?
任何人?
I'm using the OleDbConnection class to retrieve data from an Excel 2000/2003 workbook:
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filename + ";" +
"Extended Properties=\"Excel 8.0;IMEX=1\";";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
// code to get table name from schema omitted
var dataAdapter = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", name),connection);
var myDataSet = new DataSet();
dataAdapter.Fill(myDataSet, "ExcelInfo");
Now it turns out that cells in the worksheet with length greater than 255 characters are being truncated. Is this a limitation in the Microsoft.Jet.OLEDB-provider, or is there something I can do about it?
Anyone?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
Excel 的 OLEDB 提供程序将尝试根据前 8 行数据自动确定数据类型,这可以使用连接字符串中的 HDR=Yes/No 属性进行设置。 此外,它还可以应用于文本列的多种类型。 备注类型包含超过 255 个字符,因此如果前 8 行中没有一个包含该字符,那么它将错误地设置数据类型。
更改此设置的方法是更改名为 TypeGuessRows 的注册表设置,如下所述:Microsoft 支持
注意:TypeGuessRows 键值的有效范围是 0 到 16。但是,如果该值为 0,则扫描的源行数为 16384。因此,如果您有一个非常大的文件,请确保最大的行位于最前面。
The OLEDB provider for excel will attempt to automatically determine the DataTypes based off of the first 8 rows of data, this can be set with the HDR=Yes/No property in the connection string. Additionally, there are multiple types that it can apply to text columns. The memo type holds over 255 characters, so if none of the first 8 rows have that then it will incorrectly set the data type.
The way to change this is by changing a registry setting called TypeGuessRows, as described here: Microsoft Support
NOTE: The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. So if you have a very large file make sure the biggest rows are first.
试试这个我通过堆栈溢出发布的OleDBAdapter Excel QA。
我填充了一个工作表单元格 (Rows[0][4]) w/ 445 个字符,它工作得很好......
将其添加到输出代码的末尾
Try this OleDBAdapter Excel QA I posted via stack overflow.
I populated an worksheet cell (Rows[0][4]) w/ 445 characters and it worked fine...
Add this to the end of the code for the ouput