Load excel to gridview

Question

0.00/5 (No votes)

See more:

I want to load an excel to gridview, and I was able to do so with an excel file that had sheet1 as the first sheet, but I want to load any sheet in the workbook.
I need to fill a combobox with the sheet names in the workbook, and when the sheet is selected, I need to populate the gridview.

What I have tried:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Interfaz2
{
    public partial class _Default : Page
    {
        //public static string s1 = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            
        }

        protected void imp_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                GetExcelSheetNames(FileUpload1.PostedFile.FileName.ToString());
            }
        }
        private String[] GetExcelSheetNames(string excelFile)
        {
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;

            try
            {
                // Connection String. Change the excel file to the file you
                // will search.
                String connString = "Provider=Microsoft.Jet.OLEDB.12.0;" +
                    "Data Source=" + excelFile + ";Extended Properties=Excel 12.0;";
                // Create connection object by using the preceding connection string.
                objConn = new OleDbConnection(connString);
                // Open connection with the database.
                objConn.Open();
                // Get the data table containg the schema guid.
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null)
                {
                    return null;
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;

                // Add the sheet name to the string array.
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    sheet.Items.Add(excelSheets[i]);
                    i++;
                }

                // Loop through all of the sheets if you want too...
                for (int j = 0; j < excelSheets.Length; j++)
                {
                    // Query each excel sheet.
                }

                return excelSheets;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                // Clean up.
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }

        protected void FileUpload1_Load(object sender, EventArgs e)
        {
            
        }

        protected void sheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            //s1 = sheet.SelectedValue.ToString();
            string connString = "";
            string strFileType = Path.GetExtension(FileUpload1.FileName).ToLower();
            string path = FileUpload1.PostedFile.FileName;

            string fileBasePath = Server.MapPath("~/Files/");
            string fileName = Path.GetFileName(this.FileUpload1.FileName);
            string fullFilePath = fileBasePath + fileName;
            //Connection String to Excel Workbook
            if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullFilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
            }
            else if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullFilePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
            }
            if (FileUpload1.HasFile)
            {

                string query = "SELECT [UserName],[Education],[Location] FROM [" + sheet.SelectedValue.ToString() + "]";
                OleDbConnection conn = new OleDbConnection(connString);
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                OleDbCommand cmd = new OleDbCommand(query, conn);
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                grid.DataSource = ds.Tables[0];
                grid.DataBind();
                da.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        protected void FileUpload1_DataBinding(object sender, EventArgs e)
        {
            GetExcelSheetNames(FileUpload1.FileName.ToString());
        }
    }

Posted 17-Nov-17 11:14am

Member 10850253

Updated 5-Dec-17 7:39am

Add a Solution

Comments

CHill60 18-Nov-17 5:43am

What is your actual question? What is wrong with the code you have? (Other than very poor error handling)

Member 10850253 18-Nov-17 9:03am

I loaded all the worksheets in a combobox, but now I want to load the excel file to a gridview, depending on the selection of the combobox.

using System;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Configuration; 

public partial class _Default : System.Web.UI.Page 
{
    public static OleDbConnection connExcel;
    public static OleDbCommand cmdExcel;
    public static OleDbDataAdapter oda;
    public static DataTable dt;
    public static string FilePath;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

            FilePath = Server.MapPath(FolderPath + FileName);
            FileUpload1.SaveAs(FilePath);
            Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
        }
    }
    private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
        string conStr="";
        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        conStr = String.Format(conStr, FilePath, isHDR);
        connExcel = new OleDbConnection(conStr);
        cmdExcel = new OleDbCommand();
        oda = new OleDbDataAdapter();
        dt = new DataTable(); 
        cmdExcel.Connection = connExcel;

        //Get the name of First Sheet
        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        for (int i = 0; i < dtExcelSchema.Rows.Count; i++)

        {

            String sheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();

            sheetName = sheetName.Substring(0, sheetName.Length - 1);

            sheet.Items.Add(sheetName);

        }
        //string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();

       
    }
    protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"] ;
        string FileName = GridView1.Caption;
        string Extension = Path.GetExtension(FileName);
        string FilePath = Server.MapPath(FolderPath + FileName);

        Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);  
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataBind();  
    }

    protected void sheet_SelectedIndexChanged(object sender, EventArgs e)
    {
        //Read Data from First Sheet
        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + sheet.Text.ToString() + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();

        //Bind Data to GridView
        GridView1.Caption = Path.GetFileName(FilePath);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

Member 10850253 18-Nov-17 13:10pm

Please let me know how should I display a sheet in gridview, based on a combobox selection.

Member 10850253 18-Nov-17 8:24am

How to load any sheet of an excel workbook in asp.net c#?

Karthik_Mahalingam 18-Nov-17 8:47am

use Reply button to post comments/query to the concerned user, so that the user gets notified and respond to your text.

Member 10850253 18-Nov-17 9:03am

I loaded all the worksheets in a combobox, but now I want to load the excel file to a gridview, depending on the selection of the combobox.

using System;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Configuration; 

public partial class _Default : System.Web.UI.Page 
{
    public static OleDbConnection connExcel;
    public static OleDbCommand cmdExcel;
    public static OleDbDataAdapter oda;
    public static DataTable dt;
    public static string FilePath;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

            FilePath = Server.MapPath(FolderPath + FileName);
            FileUpload1.SaveAs(FilePath);
            Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
        }
    }
    private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
        string conStr="";
        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        conStr = String.Format(conStr, FilePath, isHDR);
        connExcel = new OleDbConnection(conStr);
        cmdExcel = new OleDbCommand();
        oda = new OleDbDataAdapter();
        dt = new DataTable(); 
        cmdExcel.Connection = connExcel;

        //Get the name of First Sheet
        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        for (int i = 0; i < dtExcelSchema.Rows.Count; i++)

        {

            String sheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();

            sheetName = sheetName.Substring(0, sheetName.Length - 1);

            sheet.Items.Add(sheetName);

        }
        //string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();

       
    }
    protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"] ;
        string FileName = GridView1.Caption;
        string Extension = Path.GetExtension(FileName);
        string FilePath = Server.MapPath(FolderPath + FileName);

        Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);  
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataBind();  
    }

    protected void sheet_SelectedIndexChanged(object sender, EventArgs e)
    {
        //Read Data from First Sheet
        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + sheet.Text.ToString() + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();

        //Bind Data to GridView
        GridView1.Caption = Path.GetFileName(FilePath);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

alexvw · Answer 1 · 2017-12-05T06:40:00

Hello Member 10850253,

This is how I would do it:

1) Upload your file to your repository folder.
2) List the available file(s) in a listbox or similar control.
3) Once a file name is selected form the above list, run the GetExcelSheetNames method to populate your combobox(dropdownlist).
4) Once a sheet name is selected from such control, run the Import_To_Grid method.

Things to consider:
a) Page_Load event must make sure to load the available/selected FileList and SheetList controls. Personally, if there is only one file, I would automatically select it in order o populate the SheetList; if there are more, then pre-select the most recent one. Make use of "IsPostBack" to avoid unnecessary workload.

b) In your Import_To_Grid, query the selected sheet, where Sheet1$ = Selected Sheet Name:

OleDbCommand objCmd = new OleDbCommand("Select * From [Sheet1$]", objConn);

By the way, also consider implementing an "available files cleaning" routine to avoid accumulating files upon files upon files.

Hope this helps you get on track.

Cheers!