none
Reading Excel Sheet RRS feed

  • Question

  • Hi,

    I have an excel sheet in multiple row-columns, Now I have to read to excel row by row and insert the data in a table.

    I want to read excel , without using any provider.

    Can anyone help me in this matter.

    I am providing the following image as a sample.

    Thanks and Regards,

    Bhaskar Das

     


    • Edited by Bhaskar Das Thursday, December 15, 2011 6:34 AM
    Thursday, December 15, 2011 5:52 AM

Answers

  • Hi,

    Use the below code to read Data from Excel Sheet

    if (!Page.IsPostBack)
                {
                    Excel.Application appExl;
                    Excel.Workbook workbook;
                    Excel.Worksheet NwSheet;
                    Excel.Range ShtRange;
                    appExl = new Excel.ApplicationClass();
     
    
                      //Opening Excel file(myData.xlsx)
                    workbook = appExl.Workbooks.Open(Server.MapPath("myData.xlsx"), Missing.Value, Missing.Value,Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value);
    
                    NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
                    int Cnum = 0;
                    int Rnum = 0;
         
                    ShtRange = NwSheet.UsedRange; //gives the used cells in sheet
                   
                      //Reading Excel file.
                   //Creating datatable to read the containt of the Sheet in File.
                    DataTable dt = new DataTable();
                    dt.Columns.Add("ID");
                    dt.Columns.Add("Name");
                    dt.Columns.Add("Status");
                    dt.Columns.Add("Contact");
    
                    for (Rnum = 2; Rnum <= ShtRange.Rows.Count; Rnum++)
                    {
                        DataRow dr = dt.NewRow();
    //Reading Each Column value From sheet to datatable Colunms                  
                        for (Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
                     {
                           dr[Cnum - 1] = (ShtRange.Cells[Rnum, Cnum] as Excel.Range).Value2.ToString();
                        }
                        dt.Rows.Add(dr); // adding Row into DataTable
                        dt.AcceptChanges();
                    }
    
                    workbook.Close(true, Missing.Value, Missing.Value);
                    appExl.Quit();
    
                     gvOne.DataSource = dt;//DataSource to GrigView(Id:gvOne)
                    gvOne.DataBind();
          }

    Reference from http://www.dotnetfunda.com/articles/article1370-reading-opening-excel-file-in-csharp-using-microsoftofficeintropexcel-objec.aspx


    PS.Shakeer Hussain
    Thursday, December 15, 2011 6:43 AM
  • Hi,

    Add the below namespace

    Using Excel=Microsoft.Office.Interop.Excel; 
    Using System.Reflection;
    PS.Shakeer Hussain
    Thursday, December 15, 2011 7:30 AM

All replies

  • Hi,

    Use the below code to read Data from Excel Sheet

    if (!Page.IsPostBack)
                {
                    Excel.Application appExl;
                    Excel.Workbook workbook;
                    Excel.Worksheet NwSheet;
                    Excel.Range ShtRange;
                    appExl = new Excel.ApplicationClass();
     
    
                      //Opening Excel file(myData.xlsx)
                    workbook = appExl.Workbooks.Open(Server.MapPath("myData.xlsx"), Missing.Value, Missing.Value,Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value);
    
                    NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
                    int Cnum = 0;
                    int Rnum = 0;
         
                    ShtRange = NwSheet.UsedRange; //gives the used cells in sheet
                   
                      //Reading Excel file.
                   //Creating datatable to read the containt of the Sheet in File.
                    DataTable dt = new DataTable();
                    dt.Columns.Add("ID");
                    dt.Columns.Add("Name");
                    dt.Columns.Add("Status");
                    dt.Columns.Add("Contact");
    
                    for (Rnum = 2; Rnum <= ShtRange.Rows.Count; Rnum++)
                    {
                        DataRow dr = dt.NewRow();
    //Reading Each Column value From sheet to datatable Colunms                  
                        for (Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
                     {
                           dr[Cnum - 1] = (ShtRange.Cells[Rnum, Cnum] as Excel.Range).Value2.ToString();
                        }
                        dt.Rows.Add(dr); // adding Row into DataTable
                        dt.AcceptChanges();
                    }
    
                    workbook.Close(true, Missing.Value, Missing.Value);
                    appExl.Quit();
    
                     gvOne.DataSource = dt;//DataSource to GrigView(Id:gvOne)
                    gvOne.DataBind();
          }

    Reference from http://www.dotnetfunda.com/articles/article1370-reading-opening-excel-file-in-csharp-using-microsoftofficeintropexcel-objec.aspx


    PS.Shakeer Hussain
    Thursday, December 15, 2011 6:43 AM
  • Thanks  PS.Shakeer Hussain,

    Can you please tell me, what namespace should i use, here.

    Regards,

    Bhaskar Das

    Thursday, December 15, 2011 6:57 AM
  • Hi,

    Add the below namespace

    Using Excel=Microsoft.Office.Interop.Excel; 
    Using System.Reflection;
    PS.Shakeer Hussain
    Thursday, December 15, 2011 7:30 AM
  • Thank you very much, its working.

     

    Bhaskar Das

    Thursday, December 15, 2011 10:48 AM
  • if it helps you Mark it as Answer
    PS.Shakeer Hussain
    Thursday, December 15, 2011 10:59 AM