display excel to datagrid view

Jun 24, 2013 at 4:37 PM
how to display excel file to data grid view?
and user can choose the sheet to?

thanks
Jun 24, 2013 at 5:14 PM
There are many ways to do what your looking for, the following is a simplistic answer:
public Form1()
{
  InitializeComponent();
  LoadSheets();
}

private void LoadSheets()
{
  string _PathFilename = @"C:\Temp\test3.xlsx";
  using (FileStream streamIn = File.Open(_PathFilename, FileMode.Open, FileAccess.Read))
  using (IExcelDataReader execlReader = (Path.GetExtension(_PathFilename) == ".xlsx" ? ExcelReaderFactory.CreateOpenXmlReader(streamIn) : ExcelReaderFactory.CreateBinaryReader(streamIn)))
  {
    DataSet ds = new DataSet();
    ds = execlReader.AsDataSet();

    int r = ds.Tables[0].Rows.Count;
    int c = ds.Tables[0].Columns.Count;

    if (ds.Tables.Count < 1)
    {
      MessageBox.Show("  No sheets in workbook.");
    }
    else
    {
      int x = 0, y = 0;
      foreach (DataTable sheet in ds.Tables)
      {
        DataGridView dgv = new DataGridView();
        dgv.Location = new Point(x+=100, y+=100);
        dgv.DataSource = sheet;
        this.Controls.Add(dgv);
      }
    }
  }
}
Jun 24, 2013 at 5:44 PM
why do you initialize r an c but you don;t use it?
Jun 24, 2013 at 5:49 PM
That's from your previous request where you wanted to know how many rows and columns.
Jun 24, 2013 at 6:04 PM
oh i see. thanks a lot.
my previous problem has been solved :)

but, why if i put the location, the position of result not same with my data grid design??
*sorry my grammar is so bad
thanks :)
Jun 24, 2013 at 7:01 PM
The location is only an x\y position where the grid starts drawing at. If you don't change your offset, the grids wind up on top of each other. I'm afraid I can't answer your design requirements. My example was not meant to be a full design. You need one grid for each sheet. You could place them on the form manually and use their names in the code.
Jun 25, 2013 at 4:08 AM
oh i see..

but i change "dgv" to my grid view's name and when i run my program, the grid appear under the tab page (fyi i use tab page in my program).
how to solved it?
Jun 25, 2013 at 2:29 PM
If you have 3 sheets you need three datagridview controls. So you will have to set sheet[0] to dgv1, sheet[1] to dgv2, etc. My example creates three new datagridviews dynamically. Also, don't use the location part of the code if you are placing the grids on the form.