How to import data from Excel to grid view based on excel sheet Name?

50 pts.
Tags:
Microsoft Excel
How to get excel document data from data grid view based on selected sheet name? I cannot find the answer.. Whenever i have to get 1st sheet data only. How do I do this? I need your suggestions. This is my source code:
private void BtnBrowse_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt.Reset();
            Excel.Workbook workbook;
            Excel.Worksheet NwSheet;
            Excel.Range ShtRange;
            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            OpenFileDialog filedlgExcel = new OpenFileDialog();
            filedlgExcel.Title = "Select file";
            filedlgExcel.InitialDirectory = @"c:";
            filedlgExcel.Filter = "Excel Sheet(*.xlsx)|*.xlsx|All Files(*.*)|*.*";
            filedlgExcel.FilterIndex = 1;
            filedlgExcel.RestoreDirectory = true;
            if (filedlgExcel.ShowDialog() == DialogResult.OK)
            {
                workbook = ExcelObj.Workbooks.Open(filedlgExcel.FileName, 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);
                plSelectOptions.Visible = true;
                NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
                string FilePath = filedlgExcel.FileName;
                this.txtFileName.Text = FilePath;
                GetExcelSheetNames(FilePath);
                ShtRange = NwSheet.UsedRange;
                cmbSheetName.Items.Clear();
                foreach (string sheetname in GetExcelSheetNames(FilePath))
                {
                    this.cmbSheetName.Items.Add(sheetname);
                    cmbSheetName.SelectedIndex = 0;
                    try
                    {
                        for (int Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
                        {
                            dt.Columns.Add(new DataColumn((ShtRange.Cells[1, Cnum] as Excel.Range).Value2.ToString()));
                        }
                    }
                    catch { }
                    string[] columnName = (from dc in dt.Columns.Cast() select dc.ColumnName).ToArray();
                    for (int Rnum = 2; Rnum <= ShtRange.Rows.Count; Rnum++)
                    {
                        DataRow dr = dt.NewRow();
                        for (int Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
                        {
                            if ((ShtRange.Cells[Rnum, Cnum] as Excel.Range).Value2 != null)
                            {
                                dr[Cnum - 1] = (ShtRange.Cells[Rnum, Cnum] as Excel.Range).Value2.ToString();
                            }
                        }
                        dt.Rows.Add(dr);
                        dt.AcceptChanges();
                    }
                }
                workbook.Close(true, Missing.Value, Missing.Value);
                ExcelObj.Quit(); 
                dgvGetColumn.DataSource = dt;
            }
        }
     
       

        public static IEnumerable GetExcelSheetNames(string excelFile) //Get the excel sheet names
        {
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(excelFile); ;
            String[] excelSheets = new String[xlWorkBook.Worksheets.Count];
            int i = 0;
            foreach (Microsoft.Office.Interop.Excel.Worksheet wSheet in xlWorkBook.Worksheets)
            {
                excelSheets[i] = wSheet.Name;
                i++;
            }
            xlWorkBook.Close();
            xlApp.Quit();
            return excelSheets;
        }
I have to get selected document sheets name in combo box. but how to get selected sheet data's from grid view.


Software/Hardware used:
.Net(C#) Winforms
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: