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

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.

Uploading large file in chunks in Mvc c# from Javascript ajax

Often we have a requirement to upload files in, Mvc c# application but when it comes to uploading larger file, we always think how to do it as uploading large file in one go have many challenges like UI responsiveness, If network fluctuate for a moment in between then uploading task get breaks and user have to upload it again etc.

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 -

Scrollable Gridview with fixed headers in

horizontal and vertical scrollable Gridview with static header row.fixed header in gridview in header in gridview through java script.I was looking for a solution for this for a long time and found most of the answers are not working or not suitable for my situation i also find most of the java script code for that they worked but only with the vertical scroll not with the horizontal scroll and also combination of header and rows doesn't match.