cannot import excel to datagridview in c#

Question

hello guyz i need your help, i cannot import excel to datagridview in c#.net even though I add a reference using Microsoft.Office.Interlop.Excel , I m using a visual studio 2010 and this is my source code private void button1_Click( object sender, EventArgs e) { _Application importExcelToDataGridViewAppl; _Workbook importExcelToDataGridViewWorkbook; _Worksheet importExcelToDataGridViewWorksheet; Range importExcelToDataGridViewRange; try { importExcelToDataGridViewAppl = new Microsoft.Office.Interop.Excel.Application(); OpenFileDialog importExcelToDataGridViewFileDialog = new OpenFileDialog(); importExcelToDataGridViewFileDialog.Title= "yahoo" ; importExcelToDataGridViewFileDialog.Filter = "Choose Excel File | *.xlsx;*.xls;*.xlsm" ; // importExcelToDataGridViewFileDialog.Title = "Import Excel File"; if (importExcelToDataGridViewFileDialog.ShowDialog() == DialogResult.OK) { importExcelToDataGridViewWorkbook = importExcelToDataGridViewAppl.Workbooks.Open(importExcelToDataGridViewFileDialog.FileName); importExcelToDataGridViewWorksheet = importExcelToDataGridViewWorkbook.ActiveSheet; importExcelToDataGridViewRange = importExcelToDataGridViewWorksheet.UsedRange; // for ( int excelWorkSheetRowIndex=0;excelWorkSheetRowIndex { dataGridView1.Rows.Add(importExcelToDataGridViewWorksheet.Cells[excelWorkSheetRowIndex,1].Value,); } } } catch (Exception ex) { } finally { } }

Tamal Banerjee · Answer

Hi, you can try this (I've added a little helper method named ConvertRangeToDataTable) : private static Excel.Range xlRange= null ; void Button1Click( object sender, EventArgs e) { try { //Create COM Objects. Create a COM object for everything that is referenced Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@ "C:\Users\Tamal\Downloads	esting.xlsx" ); Excel.Worksheet xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[1]; xlRange = (Excel.Range)xlWorksheet.UsedRange; if (xlRange != null ) { DataTable dt = ConvertRangeToDataTable(); if (dt != null ) { dataGridView1.DataSource = dt; } } //cleanup GC.Collect(); GC.WaitForPendingFinalizers(); //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } MessageBox.Show( "Done" ); } private static DataTable ConvertRangeToDataTable() { try { DataTable dt = new DataTable(); int ColCount = xlRange.Columns.Count; int RowCount = xlRange.Rows.Count; for ( int i = 0; i DataColumn dc = new DataColumn(); dt.Columns.Add(dc); } foreach (Excel.Range item in xlRange.Areas) { int i = 1; foreach (Excel.Range row in item.Rows) { Excel.Range range1 = (Excel.Range)row.get_Range(row.Address); DataRow dr = dt.NewRow(); for ( int j = 1; j dt.Rows.Add(dr); i++; Marshal.ReleaseComObject(range1); } } return dt; } catch { return null ; } } Add the following as well: using System.Runtime.InteropServices; using System.Data; using System.Threading.Tasks; using Excel = Microsoft.Office.Interop.Excel; using System; using System.Linq; using System.Windows.Forms;

Salman Beg · Answer

Are you getting any exception? See these links for example, https://www.c-sharpcorner.com/UploadFile/ankurmee/import-data-from-excel-to-datagridview-in-C-Sharp/ https://stackoverflow.com/questions/16293643/import-excel-to-datagridview http://csharp.net-informations.com/datagridview/csharp-datagridview-load-excel.htm

Leon D · Answer

Free spire.xls library can be used to import datagridview to database. It's easy to use and does not require MS Office to be installed. https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Excel-Import/Export-Import-Data-from-Excel-to-Datatable.html