Skip to main content

Importing all sheet of excel 2003 .xls,2007 .xlsx file into dataset and bind it to gridview in c#

In this article you will see how to import all sheet from excel 2003 and 2007 file to c# dataset object and then you will display this data into girdview.

Method ImportExcelXLS given below takes two parameter first full path of .xls or .xlsx file and another bool type hasHeaders that is for including header of sheet or not, if you want to import data client file then you need to upload this file through fileupload and save this posted file to the server then pass this server full file path to this method and display data to girdview.

Upload excel file to server extract data and bind it to gridview code

protected void btn_Import_Click(object sender, EventArgs e)
        if (fu.HasFile)
            if (Path.GetExtension(fu.FileName).ToLower() == ".xls" || Path.GetExtension(fu.FileName).ToLower() == ".xlsx")
                fu.SaveAs(Server.MapPath(@"File/" + fu.FileName));
                DataSet ds = ImportExcelXLS(Server.MapPath(@"File/" + fu.FileName), true);
                GridView1.DataSource = ds;

Import from excel file into dataset code -

private static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
        string HDR = hasHeaders ? "Yes" : "No";
        string strConn = "";

        if (Path.GetExtension(FileName).ToLower() == ".xls")
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
            FileName + ";Extended Properties=\"Excel 8.0;HDR=" +
            HDR + ";IMEX=1\"";
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
            FileName + ";Extended Properties=\"Excel 12.0;HDR=" +
            HDR + ";IMEX=1\"";

        DataSet output = new DataSet();
        using (OleDbConnection conn = new OleDbConnection(strConn))
            DataTable schemaTable = conn.GetOleDbSchemaTable(
            OleDbSchemaGuid.Tables, new object[] { nullnullnull"TABLE" });

            foreach (DataRow schemaRow in schemaTable.Rows)
                string sheet = schemaRow["TABLE_NAME"].ToString();
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                cmd.CommandType = CommandType.Text;
                DataTable outputTable = new DataTable(sheet);
                new OleDbDataAdapter(cmd).Fill(outputTable);
        return output;

Popular posts from this blog

how $document.ready() is different from window.onload()

we often use window.onload() in javascript and $document.ready() in jquery and assume that both are same just jquery library wrap up javascript window.onload() and introduce $document.ready() but no there is much difference between them below is the explanation -

regex - check if a string contains only alphabets c#

How to validate that input string contains only alphabets, validating that textbox contains only alphabets (letter), so here is some of the ways for doing such task. char have a property named isLetter which is for checking if character is a letter or not, or you can check by the regular expression  or you can validate your textbox through regular expression validator in Following code demonstrating the various ways of implementation.

Merging multiple PDFs using iTextSharp in c#

In this article i will show you how to merge multiple pdfs into one using ITextSharp below is the two approach one is to pass your input files path, output file path (will be created if not exist) and another is pass direct input stream, output stream and it will write the merge files into output stream.

How to handle click event of linkbutton inside gridview

Recently I have posted how to sort only current page of gridview , Scrollble gridview with fixed header through javascript , File upload control inside gridview during postback and now i am going to explain how to handle click event of linkbutton or any button type control inside gridview. We can handle click event of any button type control inside gridview by two way first is through event bubbling and second one is directly (in this type of event handling we need to access current girdviewrow container)