Click here to Skip to main content
14,975,219 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 5-Dec-17 7:39am
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

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!
   
v2

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900