Import multiple excel sheet into gridview using exceldatareader

Question

currently I able to import an excel file into gridview by using exceldatareader library. Now, I want to further improve to import multiple excel sheet into gridview. This is the output I wish to do. ImageOutput I able to get the excel sheet name into the dropdownlist but I have no idea how to get the data from the second sheet. I tried google but still cant found any answer yet.

So now my problem how can I select the sheet name from the dropdownlist and show that sheet data in the gridview.

Below is my code:

public partial class ReadExcel : System.Web.UI.Page
{
    DataSet result;
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void btnLoad_Click(object sender, EventArgs e)
    {
        dataUpload();
    }
    protected void dataUpload()
    {
        if (FileUpload1.HasFile)
        {
            string path = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string filepath = Server.MapPath("~/" + path);
            FileUpload1.SaveAs(filepath);
            FileStream stream = File.Open(filepath, FileMode.Open, FileAccess.Read);
            //for excel 2003
            // IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            // for Excel 2007
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            excelReader.IsFirstRowAsColumnNames = true;
            result = excelReader.AsDataSet();
            ddlSheet.Items.Clear();
            foreach(DataTable dt in result.Tables)
            {
                ddlSheet.Items.Add(dt.TableName);
            }
            //excelReader.IsFirstRowAsColumnNames = true;
            while (excelReader.Read())
            {
                // int i = excelReader.GetInt32(0);
                GridView1.DataSource = result;
                GridView1.DataBind();
            }

        }
        else
        {
            lblError.Text = "Unable to upload the selected file. Please check the selected file path or confirm that the file is not blank!";
        }
    }

    protected void ddlSheet_SelectedIndexChanged(object sender, EventArgs e)
    {
        GridView1.DataSource = result;
        GridView1.DataBind();
    }
}

Your question is how to get sheetname or data from particular sheet ? — Vandita, Mar 31 '17 at 05:38

score 0 · Answer 1 · answered Apr 03 '17 at 13:37

Hummm, interesting. I've never tried what you described here, but I think the link below will help you get started.

https://msdn.microsoft.com/en-us/library/aa480727.aspx?f=255&MSPPError=-2147217396

Also, below is a small sample of code to get things going.

You can use the excel library from this link http://code.google.com/p/excellibrary/. There is actually an example code provided on this page, it demonstrates how to read from an excel file.

using ExcelLibrary.SpreadSheet;

// open xls file
Workbook book = Workbook.Load(file);
Worksheet sheet = book.Worksheets[0];

// iterate with a proper condition, at the moment it will iterate with a given length!
for (int i = 0; i < length; i++)
{
  comboBox.Items.Add(sheet.Cells[i,column_index].Value);
}

score 0 · Answer 2 · answered Jun 11 '18 at 10:37

0

Your result is a DataSet object that has Tables[] property and ExcelDataReader stores each sheet as a table.
So, I think you should change

GridView1.DataSource = result;

to

GridView1.DataSource = result.Tables[ddlSheet.SelectedText];

answered Jun 11 '18 at 10:37

shA.t

16,580
5
54
111

Import multiple excel sheet into gridview using exceldatareader

2 Answers2