OleDB&混合Excel数据类型:缺失数据

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (44)

我有一个Excel工作表,我想要读到DataTable--除了Excel表中的一个特定列之外,一切都很好。列“productid”是类似于##########n#########...

通过将其读入DataSet/DataTable,但是“Productid”中的任何值都类似n######都丢失了,被忽略了,并留下了空白。我尝试手动创建DataTable,方法是使用数据中心循环遍历每一行,但结果完全相同。

下面是代码:

// add the column names manually to the datatable as column_1, column_2, ...
for (colnum = 0; colnum < num_columns; colnum ++){
  ds.Tables["products"].Columns.Add("column_" +colnum , System.Type.GetType("System.String")); 
}
while(myDataReader.Read()){
  // loop through each excel row adding a new respective datarow to my datatable 
  DataRow a_row = ds.Tables["products"].NewRow();
  for (col = 0; col < num_columns; col ++){
    try {  a_row[col] = rdr.GetString(col);  }
    catch {  a_row[col] = rdr.GetValue(col).ToString(); }
  }
  ds.Tables["products"].Rows.Add(a_row);
}

我不明白为什么它不让我读像n######...。我该怎么做?

提问于
用户回答回答于

使用.NET 4.0和读取Excel文件时,我遇到了类似的问题OleDbDataAdapter-即读取MS Excel中“PartID”列上的混合数据类型,其中PartID值可以是数字(例如561)或文本(例如HL 4354),尽管excel列格式为“text”。

据我所知,ADO.NET是根据列中的大多数值选择数据类型的(其中一个领带是数字数据类型),也就是说,如果示例集中的PartID大部分是数字的,ADO.NET将声明该列为数字。因此,ADO.NET将尝试将每个单元格转换为一个数字,这将导致“text”PartID值失败,而不是导入那些“text”PartID。

我的解决方案是设置OleDbConnection要使用的连接字符串Extended Properties=IMEX=1;HDR=NO若要指示这是一个导入,表将不包括标头。Excel文件有一个标题行,因此在本例中,请告诉ADO.NET不要使用它。然后在后面的代码中,从DataSet中删除标题行,然后为该列创建混合数据类型。

string sql = "SELECT F1, F2, F3, F4, F5 FROM [sheet1$] WHERE F1 IS NOT NULL";

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PrmPathExcelFile + @";Extended Properties=""Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text""");

OleDbCommand cmd = new OleDbCommand(sql, connection);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();
ds.Tables.Add("xlsImport", "Excel");
da.Fill(ds, "xlsImport");

// Remove the first row (header row)
DataRow rowDel = ds.Tables["xlsImport"].Rows[0];
ds.Tables["xlsImport"].Rows.Remove(rowDel);

ds.Tables["xlsImport"].Columns[0].ColumnName = "LocationID";
ds.Tables["xlsImport"].Columns[1].ColumnName = "PartID";
ds.Tables["xlsImport"].Columns[2].ColumnName = "Qty";
ds.Tables["xlsImport"].Columns[3].ColumnName = "UserNotes";
ds.Tables["xlsImport"].Columns[4].ColumnName = "UserID";

connection.Close(); 

//现在可以使用LINQ搜索字段

    var data = ds.Tables["xlsImport"].AsEnumerable();
    var query = data.Where(x => x.Field<string>("LocationID") == "COOKCOUNTY").Select(x =>
                new Contact
                {
                    LocationID= x.Field<string>("LocationID"),
                    PartID = x.Field<string>("PartID"),
                    Quantity = x.Field<string>("Qty"),
                    Notes = x.Field<string>("UserNotes"),
                    UserID = x.Field<string>("UserID")
                });
用户回答回答于

我发现有几个论坛声称IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text对于连接字符串中的ExtendedProperties,将修复此问题,但情况并非如此。最后,我通过在连接字符串中添加“HDR=no”来解决这个问题(如BrianWells所示),这样我就可以导入混合类型。

然后,我添加了一些通用代码,以第一行数据命名列,然后删除第一行。

    public static DataTable ImportMyDataTableFromExcel(string filePath)
    {
        DataTable dt = new DataTable();

        string fullPath = Path.GetFullPath(filePath);

        string connString =
           "Provider=Microsoft.Jet.OLEDB.4.0;" +
           "Data Source=\"" + fullPath + "\";" +
           "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"";

        string sql = @"SELECT * FROM [sheet1$]";

        using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, connString))
        {
            dataAdapter.Fill(dt);
        }

        dt = BuildHeadersFromFirstRowThenRemoveFirstRow(dt);

        return dt;
    }

    private static DataTable BuildHeadersFromFirstRowThenRemoveFirstRow(DataTable dt)
    {
        DataRow firstRow = dt.Rows[0];

        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if(!string.IsNullOrWhiteSpace(firstRow[i].ToString())) // handle empty cell
              dt.Columns[i].ColumnName = firstRow[i].ToString().Trim();
        }

        dt.Rows.RemoveAt(0);

        return dt;
    }

扫码关注云+社区