You must Sign In to post a response.
  • Category: .NET

    How to upload open office excel sheet data to gridview

    I need to develop a web application that displays the data present in open office excel sheet in gridview i had tried a lot but i could not do it can anyone please help me out in uploading the open office excel data to gridview.Below is the code for displaying the data in microsoft excel to gridview but it need to implement export the data in open office excel sheet to gridview how can i do this its very urgent can anyone please help me out its very urgent
    <div>

    Import Excel File:
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <br />
    <br />
    <asp:Button ID="btnUpload" runat="server" OnClick="btnUpload_Click" Text="Upload" />
    <br />
    <br />
    <asp:Label ID="Label1" runat="server"></asp:Label>
    <br />
    <asp:GridView ID="gvExcelFile" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <EditRowStyle BackColor="#999999" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <SortedAscendingCellStyle BackColor="#E9E7E2" />
    <SortedAscendingHeaderStyle BackColor="#506C8C" />
    <SortedDescendingCellStyle BackColor="#FFFDF8" />
    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
    </asp:GridView>

    </div>
    protected void btnUpload_Click(object sender, EventArgs e)
    {
    //Coneection String by default empty
    string ConStr = "";
    //Extantion of the file upload control saving into ext because
    //there are two types of extation .xls and .xlsx of Excel
    string ext = Path.GetExtension(FileUpload1.FileName).ToLower();
    //getting the path of the file
    string path = Server.MapPath("~/MyFolder/" + FileUpload1.FileName);
    //saving the file inside the MyFolder of the server
    FileUpload1.SaveAs(path);
    Label1.Text = FileUpload1.FileName + "\'s Data showing into the GridView";
    //checking that extantion is .xls or .xlsx
    if (ext.Trim() == ".xls")
    {
    //connection string for that file which extantion is .xls
    ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
    }
    else if (ext.Trim() == ".xlsx")
    {
    //connection string for that file which extantion is .xlsx
    ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
    }
    //making query
    string query = "SELECT * FROM [Sheet1$]";
    //Providing connection
    OleDbConnection conn = new OleDbConnection(ConStr);
    //checking that connection state is closed or not if closed the
    //open the connection
    if (conn.State == ConnectionState.Closed)
    {
    conn.Open();
    }
    //create command object
    OleDbCommand cmd = new OleDbCommand(query, conn);
    // create a data adapter and get the data into dataadapter
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    DataSet ds = new DataSet();
    //fill the Excel data to data set
    da.Fill(ds);
    //set data source of the grid view
    gvExcelFile.DataSource = ds.Tables[0];
    //binding the gridview
    gvExcelFile.DataBind();
    //close the connection
    conn.Close();


    }
  • #766679
    Hi,

    If you want to display excel data into gridview control then you need to connect to OLEDB data provider, this will help you to connect to excel database and display the same in your application,

    Refer below connectionstring for OLEDB data provider,

    oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");


    Refer below sample code for displaying excel data into gridview control.


    private void Display()
    {
    OleDbConnection oconn = null;
    DataTable dt1 = new DataTable();

    //Add dummy columns to datatable.

    dt1.Columns.Add("ENAME");
    dt1.Columns.Add("JOB");
    dt1.Columns.Add("MGR");
    dt1.Columns.Add("SAL");
    dt1.Columns.Add("COMM");
    try
    {
    string FileName = ViewState["FileName"] as string;
    string FilePath = ViewState["FilePath"] as string;

    oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");

    //select file name based upon dropdown selecteditem.

    OleDbCommand ocmd = new OleDbCommand("select * from [" + ddlSheet.SelectedItem.ToString() + "]", oconn);

    oconn.Open();

    //Read all rows and then store in DataTable

    OleDbDataReader odr = ocmd.ExecuteReader();
    string ENAME=string.Empty;
    string JOB=string.Empty;
    string MGR=string.Empty;
    string SAL=string.Empty;
    string COMM=string.Empty;
    while (odr.Read())
    {
    ENAME = odr["ENAME"].ToString();
    JOB = odr["JOB"].ToString();
    MGR = odr["MGR"].ToString();
    SAL = odr["SAL"].ToString();
    COMM = odr["COMM"].ToString();

    DataRow dr = dt1.NewRow();
    dr["ENAME"] = ENAME;
    dr["JOB"] = JOB;
    dr["MGR"] = MGR;
    dr["SAL"] = SAL;
    dr["COMM"] = COMM;
    dt1.Rows.Add(dr);
    }

    //Display data to gridview if Records are found

    if (dt1.Rows.Count > 0)
    {
    Session["Table"] = dt1;
    gvExcelData.Visible = true;
    gvExcelData.DataSource = dt1;
    gvExcelData.DataBind();

    }
    }
    catch (DataException ex)
    {
    }
    finally
    {
    if (oconn != null)
    {
    oconn.Close();
    oconn.Dispose();
    }
    if (dt1 != null)
    {
    dt1.Dispose();
    }
    }
    }


    For more details refer below link, http://www.dotnetspider.com/resources/45140-Display-Excel-Data-GridView-aspnet.aspx

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #766682
    There mare few ways to do this. In the first way is to use the interop.
    To convert template(Excel) from to word document you have to use the Interop.dll.

    You can try some third party tools as second way.
    Open XML is the one of the solution for your requirement. It is very easy to handle, you do not depend in the interop.

    XML SDK 2.5 for Office is free from Microsoft.
    you can try that. Following is the link for doing that
    https://msdn.microsoft.com/en-us/library/office/bb448854.aspx

    By Nathan
    Direction is important than speed

  • #766683
    i dont not require ddl i need to browse the open office excel spread sheet and after browsing when i click on export or import button that open office excel sheet data should be diplayed in gridview

  • #766685
    if you need to read open office spread sheet, You should have a look at the GemBox.Spreadsheet component from GemBox that supports XLSX, XLS, CSV, ODS or HTML file format.
    There's both a free and a paid version of it.
    other way is to read its content.xml file
    see below steps
    1.Extract .ods file
    2.Get content.xml file (which contains sheets data)
    3.Create XmlDocument object from content.xml file
    4.Create DataSet (that represent Spreadsheet file)
    5.With XmlDocument, we select "table:table" elements, and then we create adequate DataTables
    6.We parse children of "table:table" element and fill DataTables with those data
    7.At the end, we return DataSet and show it in the application's interface
    go through below links for more details
    http://www.codeproject.com/Articles/38425/How-to-Read-and-Write-ODF-ODS-Files-OpenDocument-S

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #766705
    [Response removed by Admin. Read forum policies.]

  • #766727
    but still i am getting the same exception as The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. near if (conn.State == ConnectionState.Closed) { conn.Open();----->here i am getting the exception }

  • #766728
    Hi,

    This error occurs because of your system driver, if it is 32 bit or 64 bit format?

    For this your system should support 32 bit format,

    Refer below link, how to configure 32 bit format in IIS local machine https://help.webcontrolcenter.com/kb/a1114/how-to-enable-a-32-bit-application-pool-in-iis-7-dedicated-vps.aspx

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #766730
    it is x64 and in the solution explorer by choosing configuration manager i changed from any cpu to x86 but still its not working in order to do the changes by choosing iis i have no permission

  • #766732
    Hi

    what is your system driver 32 or 64 bit ?

    refer this url it will guide you step by step and download provisions


    "codeproject.com/Tips/417397/OLEDB-Provider-is-Not-Registered-on-the-Local-Mach"
    "microsoft.com/en-us/download/details.aspx?id=23734"

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #766736
    it is 64 bit operating system and installed open office is 4.1.1 and it is windows 8

  • #766737
    Hi
    you can Download below link for 64 bit operating system and installed open office is 4.1.1 and it is windows 8


    "microsoft.com/en-in/download/details.aspx?id=13255"
    "mikesdotnetting.com/article/280/solved-the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine"
    "codeproject.com/Questions/564625/Microsoft-ACE-OLEDB-plusisplusnotplusregiste"

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #766739
    Hi,

    This happens because of driver issue only, it should support 32 bit format, if you want to clear this issue, refer below link here they explain the causes of this issue and resolution types for the same.

    http://www.aspsnippets.com/Articles/The-MicrosoftACEOLEDB120-provider-is-not-registered-on-the-local-machine.aspx

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #766755
    Hi,

    Normally we see this error when you run the code in 64bit machine where you have only 32 bit ACE.OLEDB. What you have to do is either you may modify your project's platform from 'Any CPU' to 'X86'.
    Right click Project--> Properties-->Select Build/Platform's Target and chaange the platform from 'Any CPU' to 'X86'.

    Otherwise you may install 64bit ACE.OLEDB from below Microsoft link: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255


    Regards,
    Asheej T K
    Microsoft MVP[ASP.NET/IIS]
    DotNetSpider MVM

  • #766765
    i had changed from any cpu to X86 but still i am getting that External table is not in the expected format. oledbexception was unhandled by the user code

  • #766792

    public static string path = @"give the excel file path name";
    public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

    SRI RAMA PHANI BHUSHAN KAMBHAMPATI

  • #766793
    Do you close your file after opening it please make sure that the file is closed when you want to fetch the data.
    SRI RAMA PHANI BHUSHAN KAMBHAMPATI

  • #766798
    can you please post the complete code for reading open office excel spread sheet and display it in gridview i had done all those suugestions that are provided but still i am getting the exceptions that oledb is not registered on your local machine or external table is not in correct format can you please post the complete code for reading open office excel spread sheet and display it in gridview its very urgent

  • #766799
    can anyone provide the complete code for reading open office excel spread sheet and displaying it in gridview that worked for you i tried a lot but unable to get the solution in displaying the open office excel sheet data in gridview if any of you got the solution for this please help me out its very urgent

  • #766823
    Did you try XML SDK 2.5 for Office?
    It is free from Microsoft and also easy to handle it.

    you can try that. Following is the link for doing that. And also there is some samples.
    https://msdn.microsoft.com/en-us/library/office/bb448854.aspx

    By Nathan
    Direction is important than speed

  • #766824
    i tried but i didnt work for me if it worked for you can you provide me the code

  • #766825
    can anyone help me out if it worked for you its urgent requirement

  • #766832
    [Response removed by Admin. Read forum policies.]
    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #766837
    but i need to display the text in spread sheet using open office spread sheet not using ms office


Sign In to post your comments