cannot import excel to datagridview in c#

Ahmet Taha

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 < importExcelToDataGridViewRange.Rows.Count; excelWorkSheetRowIndex++)
{
dataGridView1.Rows.Add(importExcelToDataGridViewWorksheet.Cells[excelWorkSheetRowIndex,1].Value,);
}
}
}
catch (Exception ex)
{
}
finally
{
}
}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Leon D5y ago
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
0
Tamal Banerjee5y ago
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\testing.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 < ColCount; 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 <= ColCount; j++) { dr[j - 1] = ((Excel.Range)range1.Cells[i, j]).Value2; }
                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;
+1
Salman Beg5y ago
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
+1