C＃未能导入所有细胞从Excel US preadsheet [英] C# Failing To Import All of the Cells from an Excel Spreadsheet

问题描述

我使用一些旧的code返回Excel工作表作为数据集。然而，当我迭代所得数据集似乎并非所有的细胞都在那里。正在读取Excel工作表有一些合并单元格，我想知道如果这就是问题所在。这里是code：

 私人数据集Get_S preadsheet_Data（字符串strFileName，串strSheetName）
{
    DataSet的DS =新的DataSet（）;
    字符串strConnectionString =供应商= Microsoft.Jet.OLEDB.4.0; +数据源=+ strFileName +; +扩展属性= Excel的8.0;;
    OleDbConnection的objConn =新的OleDbConnection（strConnectionString）;
    尝试
    {
        objConn.Open（）;
        OleDbCommand的objCmdSelect =新的OleDbCommand（SELECT * FROM [+ strSheetName +$]，objConn）;
        OleDbDataAdapter的objAdapter1 =新OleDbDataAdapter的（）;
        objAdapter1.SelectCommand = objCmdSelect;
        objAdapter1.Fill（DS）;
    }
    赶上（异常前）
    {
        //litOutput.Text =＆LT;跨度风格= \\颜色：红色; \\＆GT;异常发生从S preadsheet提取数据和LT; / SPAN＆GT;＆LT; BR＆GT;详细信息：+ Ex.Message ;
    }
    最后
    {
        objConn.Close（）;
        objConn.Dispose（）;
    }
    返回DS;
}
 

这是code故障？任何的建议是AP preciated。

问候。

解决方案

 字符串strConnectionString =供应商= Microsoft.Jet.OLEDB.4.0; +数据源=+ strFileName +; +扩展属性= Excel的8.0;;
 

阅读需要的：

 字符串strConnectionString =供应商= Microsoft.Jet.OLEDB.4.0; +数据源=+ strFileName +; +扩展属性=的Excel 8.0; HDR = NO; IMEX = 1;;
 

和该做的伎俩！

I am using some legacy code to return an Excel worksheet as a Dataset. However, when I iterate over the resulting data set it seems that not all of the cells are there. The Excel sheet that is being read has some merged cells and I am wondering if that is the problem. Here is the code:

private DataSet Get_Spreadsheet_Data(string strFileName, string strSheetName)
{
    DataSet ds = new DataSet();
    string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFileName + ";" + "Extended Properties=Excel 8.0;";
    OleDbConnection objConn = new OleDbConnection(strConnectionString);
    try
    {
        objConn.Open();
        OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + strSheetName + "$]", objConn);
        OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
        objAdapter1.SelectCommand = objCmdSelect;
        objAdapter1.Fill(ds);
    }
    catch (Exception Ex)
    {
        //litOutput.Text = "<span style=\"color:red;\">Exception Occurred pulling data from the spreadsheet.</span><br>Details: " + Ex.Message;
    }
    finally
    {
        objConn.Close();
        objConn.Dispose();
    }
    return ds;
}

Is this code malfunctioning? Any advice is appreciated.

Regards.

解决方案

string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFileName + ";" + "Extended Properties=Excel 8.0;";

needed to read:

string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFileName + ";" + "Extended Properties="Excel 8.0;HDR=NO;IMEX=1;";

and that did the trick!

这篇关于C＃未能导入所有细胞从Excel US preadsheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！