none
What is the error in function to export data from excel to dataset? RRS feed

  • Question

  • I have a function to export data from excel to Dataset which is as follows,

    public DataSet GetDataFromExcel(string filePath)  
    {  
        string strConn;  
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +  
        "Data Source=" + filePath + ";" +  
        "Extended Properties=Excel 8.0;";  
        DataTable dt = new DataTable();  
        dt = null;  
        using (OleDbConnection oleDB = new OleDbConnection(strConn))  
        {  
            oleDB.Open();  
            dt = oleDB.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
            if (dt == null)  
                return null;  
      
            ArrayList arr = new ArrayList();  
            //ListItemCollection items = new ListItemCollection();  
            int i = 0;  
      
            for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)  
            {  
                string excelSheetName;  
                string lastCharacter = "";  
      
                excelSheetName = dt.Rows[rowIndex]["TABLE_NAME"].ToString();  
                excelSheetName = excelSheetName.Replace("'", "");  
                lastCharacter = excelSheetName.Substring(excelSheetName.Length - 1, 1);  
                if (lastCharacter == "$")  
                {  
                    arr.Add(dt.Rows[rowIndex]["TABLE_NAME"].ToString());  
                    //items.Add(dt.Rows[rowIndex]["TABLE_NAME"].ToString());  
                }  
            }  
            //if (items.Count > 1)  
            if (arr.Count > 1)  
                return null;  
      
            string sName;  
            string query;  
      
            //sName = items[0].ToString();  
            sName = arr[0].ToString();  
            sName = sName.Replace("'", "");  
            sName = sName.Replace("$", "");  
      
            query = "";  
            query = String.Format("select * from [{0}$]", sName);  
            OleDbDataAdapter da = new OleDbDataAdapter(query, strConn);  
            DataSet ds = new DataSet();  
            da.Fill(ds);  
            return ds;  
        }  
    } 

    I tried to export data from one of my excel sheet which have 900 rows. The function only gets 253 rows. But I want all the rows. What is the problem with the function? Could you help me out? Thanks.



    Javed Ahmed

    Thursday, July 19, 2012 5:54 AM

Answers

  • Hi Javed,

    I guess you might want go through the following link which has a example project you could refer to:

    Please notice the Command Select section in ExcelReader.cs file.

    Best Regards,


    Tony Xiao [MSFT]
    MSDN Community Support | Feedback to us

    Monday, July 23, 2012 9:48 AM
    Moderator
  • can you try this belwo chagnes to fill the DataTable from the excel instead of using ArrayList:

    String[] arrSheetName = new String[dtExcelSheet.Rows.Count];
    foreach (DataRow dr in dt.Rows)
    {
    arrSheetName[iCount] = dr["TABLE_NAME"].ToString();
    if (arrSheetName[iCount] == "NameOfTheExcelSheet$")
    {
    DataTable dtExcelRecords = new DataTable();
    (new OleDbDataAdapter("select * from [NameOfTheExcelSheet$]", oleDb)).Fill(dtExcelRecords);

    }

    }

    regards

    joon

    Tuesday, July 24, 2012 1:02 PM

All replies

  • Hi Javed,

    I guess you might want go through the following link which has a example project you could refer to:

    Please notice the Command Select section in ExcelReader.cs file.

    Best Regards,


    Tony Xiao [MSFT]
    MSDN Community Support | Feedback to us

    Monday, July 23, 2012 9:48 AM
    Moderator
  • can you try this belwo chagnes to fill the DataTable from the excel instead of using ArrayList:

    String[] arrSheetName = new String[dtExcelSheet.Rows.Count];
    foreach (DataRow dr in dt.Rows)
    {
    arrSheetName[iCount] = dr["TABLE_NAME"].ToString();
    if (arrSheetName[iCount] == "NameOfTheExcelSheet$")
    {
    DataTable dtExcelRecords = new DataTable();
    (new OleDbDataAdapter("select * from [NameOfTheExcelSheet$]", oleDb)).Fill(dtExcelRecords);

    }

    }

    regards

    joon

    Tuesday, July 24, 2012 1:02 PM