使用 Microsoft Jet 引擎读取 XLS 文件 [英] Reading an XLS file with the Microsoft Jet Engine

问题描述

我想允许我的应用程序从 XLS 文件导入数据.我已经使用 CSV 文件和 XML 文件执行此操作，但想为用户打开范围.我在加载文件时遇到问题.我们将文件(XLS、CSV、XML)加载到数据集中并从那里开始处理.XLS 的加载代码如下

I want to allow my application to import data from XLS files. I already do this with CSV files and XML files, but would like to open the scope for users. I am having trouble with loading the file. We load the files (XLS,CSV,XML) into a data set and work on it from there. The loading code for XLS is below

FileInfo fi = new FileInfo(filename);

//create and open a connection with the supplied string
OleDbConnection objOleDBConn;
objOleDBConn = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data  Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'", fi.FullName));
objOleDBConn.Open();

DataTable dt = objOleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dt == null || dt.Rows.Count == 0)
{
    return;
}

string sheet = dt.Rows[0]["TABLE_NAME"].ToString();

//then read the data as usual.
OleDbDataAdapter objOleDBDa;
objOleDBDa = new OleDbDataAdapter(string.Format("select * from [{0}]",sheet), objOleDBConn);
objOleDBDa.Fill(data);
objOleDBConn.Close();

所以我的数据加载正常，但它似乎设置了各个列的数据类型，这是我的一个列的问题.这是一个位域，我们选择接受False、True、Yes、No、Y 和 N.稍后有代码将其转换为布尔值.这在 CSV 文件(连接字符串不同)中工作正常，但在 XLS 中，如果前 10 行是 FALSE或 TRUE，然后说第 11 个说 YES，然后我得到一个空条目.我猜它会读取前几个条目并据此确定数据类型?

So my data gets loaded OK, but it appears to set the data types of various columns, and this is a problem for one of my columns. It's a bit field and we have chosen to accept False, True, Yes, No, Y, and N. There is code that transfers this into a boolean later on. This works fine in a CSV file (for which the connection string is different) but in an XLS, if the first 10 rows are say FALSE or TRUE, and then say the 11th says YES, then I just get a null entry. I'm guessing that it reads the first few entries and determines the data type based on that?

问题:有没有办法关闭根据前几个条目识别列数据类型的机制?

Question: Is there a way to turn off the mechanism that identifies a column's data type based on the first few entries?

推荐答案

技巧是将标题行包含为行以推断数据类型，以便所有列都将被读取为字符串.然后，如果需要，您将能够解析代码以更正数据类型，而不会丢失值 - 用于此 HDR=No

trick is to include header line as row from which to infer data type, so that all columns will be read as string. Then you will be able to parse in code to correct data type, if you need, without losing values - use for this HDR=No

objOleDBConn = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data  Source={0};Extended Properties='Excel 8.0;HDR=No;IMEX=1'", fi.FullName));

这篇关于使用 Microsoft Jet 引擎读取 XLS 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！