locked
Convert data from Excel sheet to dataset RRS feed

  • Question

  • User-2122934853 posted

    Respected  Seniors,

     I have converted my Excel sheet data to dataset  it required some setting like

    <connectionStrings>
        <add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Project\App_Data\Demo.xsl;Extended Properties=Excel 8.0"/>
    <connectionStrings>

     

    Its working fine in localhost  but when i upload i dnt know the exact path so i used Response.Write(Server.MapPath("App_Data/Demo.xsl") )

    with this line i got the path which i have mention it in web configu DataSource

    but its not working giving eroor

    Cannot update. Database or object is read-only.

     

    Can any one tell me how will i get the exact path plz help me i ned it

     

    Thursday, April 9, 2009 6:18 AM

Answers

  • User-1641065896 posted

    hi sangwan

    u can put the Connection string in web config as well as Code behind page itself.

    if u are using then define it in web config else you can simply define it explecitely on code behind page..

    in my project i have also imported the excel content from excel sheet to Datatable but if u want then u can import it into dataset as well.

     

    here is the code..

    private DataTable ReadFromExcel(string path, string sheetName)

    {

    String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";

    //You must use the $ after the object you reference in the spreadsheet

    DataTable dt = null;

    try

    {

    OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + sheetName + "$]", strConn);

    DataSet myDataSet = new DataSet();

    myCommand.Fill(myDataSet, "ExcelInfo");dt = myDataSet.Tables["ExcelInfo"];

    }

    catch (Exception ex)

    {

    ex.ToString();

    }

    if (System.IO.File.Exists(TempfileServerPath))

    {

    System.IO.
    File.Delete(TempfileServerPath);

    }

    if (System.IO.Directory.Exists(TempFolderpath))

    {

    System.IO.
    Directory.Delete(TempFolderpath);

    }

    return dt;

    }

    For Doing this u need to save it into a server location after importing u can delete it.

    and one more thing There is a constraint with Sheet name inside the Excel sheet.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 9, 2009 8:16 AM
  • User-1171043462 posted

    Respected  Seniors,

     I have converted my Excel sheet data to dataset  it required some setting like

    <connectionStrings>
        <add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Project\App_Data\Demo.xsl;Extended Properties=Excel 8.0"/>
    <connectionStrings>

     

    Its working fine in localhost  but when i upload i dnt know the exact path so i used Response.Write(Server.MapPath("App_Data/Demo.xsl") )

    with this line i got the path which i have mention it in web configu DataSource

    but its not working giving eroor

    Cannot update. Database or object is read-only.

     

    Can any one tell me how will i get the exact path plz help me i ned it

    Refer my article for orking example

    http://www.aspsnippets.com/post/2009/02/04/Read-Excel-using-ADONet.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 9, 2009 8:22 AM

All replies

  • User-1641065896 posted

    hi sangwan

    u can put the Connection string in web config as well as Code behind page itself.

    if u are using then define it in web config else you can simply define it explecitely on code behind page..

    in my project i have also imported the excel content from excel sheet to Datatable but if u want then u can import it into dataset as well.

     

    here is the code..

    private DataTable ReadFromExcel(string path, string sheetName)

    {

    String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";

    //You must use the $ after the object you reference in the spreadsheet

    DataTable dt = null;

    try

    {

    OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + sheetName + "$]", strConn);

    DataSet myDataSet = new DataSet();

    myCommand.Fill(myDataSet, "ExcelInfo");dt = myDataSet.Tables["ExcelInfo"];

    }

    catch (Exception ex)

    {

    ex.ToString();

    }

    if (System.IO.File.Exists(TempfileServerPath))

    {

    System.IO.
    File.Delete(TempfileServerPath);

    }

    if (System.IO.Directory.Exists(TempFolderpath))

    {

    System.IO.
    Directory.Delete(TempFolderpath);

    }

    return dt;

    }

    For Doing this u need to save it into a server location after importing u can delete it.

    and one more thing There is a constraint with Sheet name inside the Excel sheet.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 9, 2009 8:16 AM
  • User-1171043462 posted

    Respected  Seniors,

     I have converted my Excel sheet data to dataset  it required some setting like

    <connectionStrings>
        <add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Project\App_Data\Demo.xsl;Extended Properties=Excel 8.0"/>
    <connectionStrings>

     

    Its working fine in localhost  but when i upload i dnt know the exact path so i used Response.Write(Server.MapPath("App_Data/Demo.xsl") )

    with this line i got the path which i have mention it in web configu DataSource

    but its not working giving eroor

    Cannot update. Database or object is read-only.

     

    Can any one tell me how will i get the exact path plz help me i ned it

    Refer my article for orking example

    http://www.aspsnippets.com/post/2009/02/04/Read-Excel-using-ADONet.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 9, 2009 8:22 AM
  • User-267353999 posted

    please check this article


    http://www.csharpmagic.com/2011/01/reading-excel-file-using-c.html

    Tuesday, January 25, 2011 5:08 AM
  • User-267353999 posted

    Please check this artical

    http://www.csharpmagic.com/2011/01/reading-excel-file-using-c.html


    Tuesday, January 25, 2011 5:09 AM