How to add excel with header to DataGridView C# Visual Studio

 private void button1_Click(object sender, EventArgs e)
        {

            OpenFileDialog ofd = new OpenFileDialog();
            string file = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            ofd.FileName = "";
            ofd.Title = "Choose a Document...";
            ofd.AddExtension = true;
            ofd.FilterIndex = 0;
            ofd.Multiselect = false;
            ofd.ValidateNames = true;
            ofd.InitialDirectory = file;
            ofd.RestoreDirectory = true;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                BindingSource bs = new BindingSource();
                //add columns to the dgv
                dgv_data_baru.Columns.Add("column_one", "column one");
                dgv_data_baru.Columns.Add("column_two", "column_two");                
                Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.Application();		
                Workbook wb;
                Worksheet ws;
                wb = application.Workbooks.Open(fileexcel);
                //
                ws = wb.Worksheets[1];
                //count rows and column of selected sheet
                for (int i = 1; i <= wb.Sheets.Count; i++)
                {
                    Range excelCell = ws.UsedRange;
                    Object[,] sheetValues = (Object[,])excelCell.Value;
                    int noOfRows = sheetValues.GetLength(0); // first dimention size
                    int noOfColumns = sheetValues.GetLength(1);//second dimention 
                    for (int x = 1; x <= noOfRows;x++)
                    {                        
                        //[x = column, 1 = row] if your excel file has header in it, add +<number> after x to determine which row you will add
                        //for example if your header start from row 1 to 10, then the cell data will be in 11 so make it [x+10, 1]
                        object obj1 = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[x, 1]).Value;
                        object obj2 = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[x, 2]).Value;
                        //add data to dgv rows
                        dgv_data_baru.Rows.Add(obj1, obj2)
                        
                    }
                        
                }

                wb.Close(false, fileexcel,null);
                Marshal.ReleaseComObject(wb);
            }
            else
            {
                return;
            }
        }

Comments

Popular posts from this blog

How to export data from DataGridView to Excel including header as column C# Visual Studio