Generate an excel file from a data grid C#

Question

I am trying to generate an excel file from a datagrid, and this is generating the following error:

An unhandled Exception of type 'System.Invalidcastexception' occurred in Eletronictaxnotes.exe

Additional information: It is not possible to convert the 'Microsoft.Office.Interop.Excel.Applicationclass' COM object to the 'Microsoft.Office.Interop. _Application'. This operation failed because the Queryinterface call in the COM component for the IID interface '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Error loading library/DLL of type. (Exception of HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).

private void ExportarExcel2()
{
  // Creating a Excel object. 
        Excel._Application excel = new Excel.Application();

        if (excel == null)
        {
            MessageBox.Show("Excel is not properly installed!!"); return;
        }

        Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);

        try
        {
            Excel._Worksheet worksheet = workbook.ActiveSheet;

            worksheet.Name = "ExportedFromDatGrid";

            worksheet.Cells[1, 0] = "Codigo";
            worksheet.Cells[1, 1] = "Descrição";
            worksheet.Cells[1, 2] = "Fornecedor";
            worksheet.Cells[1, 3] = "Quantidade";
            worksheet.Cells[1, 4] = "Preço";
            worksheet.Cells[1, 5] = "Preço Total";
            worksheet.Cells[1, 6] = "Ncm";
            worksheet.Cells[1, 7] = "Nota";
            worksheet.Cells[1, 8] = "DataDocumento";
            worksheet.Cells[1, 9] = "Tipo";

            // Passa as celulas do DataGridView para a Pasta do Excel
            for (var i = 0; i <= gridItens.RowCount - 1; i++)
            {
                for (var j = 0; j <= 9; j++)
                {
                    DataGridViewCell cell = j <= 2 ? gridItens[j, i] : gridNotas[j, i];
                    worksheet.Cells[i + 2, j + 1] = cell.Value;
                }
            }

            //Getting the location and file name of the excel to save from user. 
            var saveDialog = new SaveFileDialog
            {
                Filter = @"Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*",
                FilterIndex = 2
            };

            if (saveDialog.ShowDialog() != DialogResult.OK) return;
            workbook.SaveAs(saveDialog.FileName);
            MessageBox.Show(@"Export Successful");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            excel.Quit();
        }
    }

I followed the following suggestions to include the appropriate references:

What I need to use Microsoft.Office.Interop.Excel in . NET?

Cannot find Microsoft.Office.Interop Visual Studio

I have also tried other suggestions, which consists of deleting a duplicate record that could be causing the error:

Error Printing When Using Microssoft.office.Interop

But the record that is giving error does not exist in my records, as picture below:

Similarly the following suggestions did not help me either:

Error accessing COM Components

Class not Registered error when Creating Excel Workbook in C#

Unfortunately none of this worked, the result being always the same:

I am running out of options to proceed. I imagined that such a task would be something simple to do. Does anyone have any other solution suggestions that can help me?

Answer 1

Follow code I use in my application:

Microsoft.Office.Interop.Excel reference:

using Excel = Microsoft.Office.Interop.Excel;

path: C: Windows Assembly GAC Microsoft.Office.Interop.Excel 12.0.0.0__71e9bce111e9429c Microsoft.Office.Interop.Excel.dll

description: Microsoft Excel 12.0 Object Library

Code:

 string ExcelFilePath = "Caminho do arquivo destino";
try
            {
                DataTable dt = //Obtém o Datatable

                int ColumnsCount = dt.Columns.Count;

                if (dt == null || (ColumnsCount) == 0)
                {
                    status = "Tabela de dados não foi definida.";
                }
                else
                {
                    if (dt.Rows.Count == 0)
                    {
                        status = "Nenhum Registro encontrado.";
                    }
                    else
                    {
                        // load excel, and create a new workbook
                        Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
                        Excel.Workbooks.Add();

                        // single worksheet
                        Microsoft.Office.Interop.Excel._Worksheet Worksheet = (Microsoft.Office.Interop.Excel._Worksheet)Excel.ActiveSheet;

                        object[] Header = new object[ColumnsCount];

                        // column headings               
                        for (int i = 0; i < ColumnsCount; i++)
                            Header[i] = dt.Columns[i].ColumnName;

                        Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount]));
                        HeaderRange.Value = Header;
                        //HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
                        HeaderRange.Font.Bold = true;

                        // DataCells
                        int RowsCount = dt.Rows.Count;
                        object[,] Cells = new object[RowsCount, ColumnsCount];
                        maxp1 = RowsCount;
                        maxp2 = ColumnsCount;
                        int loop = 0;
                        for (int j = 0; j < RowsCount; j++)
                        {
                            p1 = j + 1;
                            status = "Processando " + p1 + " / "+ RowsCount;

                            //backgroundWorkerExportExcel.ReportProgress(1);
                            for (int i = 0; i < ColumnsCount; i++)
                            {
                                p2 = i + 1;
                                //status = "Entrando em linha " + j + " coluna " + i;
                                //backgroundWorkerExportExcel.ReportProgress(1);
                                //System.Threading.Thread.Sleep(1);
                                Cells[j, i] = dt.Rows[j][i];
                            }
                        }

                        Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount])).Value = Cells;

                        // check fielpath
                        if (ExcelFilePath != null && ExcelFilePath != "")
                        {
                            try
                            {
                                Worksheet.SaveAs(ExcelFilePath);
                                Excel.Quit();

                                status = "Exportado com sucesso!";
                                p1 = p2 = 0;
                                //backgroundWorkerExportExcel.ReportProgress(1);

                                //System.Windows.MessageBox.Show("Excel file saved!");
                            }
                            catch (Exception ex)
                            {
                                //toolStripStatusLabel1.Text = ex.Message;
                                throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n" + ex.Message);
                            }
                        }
                        else    // no filepath is given
                        {
                            status = "Exportado com sucesso!";
                            Excel.Visible = true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("ExportToExcel: \n" + ex.Message);
            }

Answer 2

I managed to solve the problem after following all the steps exposed in asking and installing office 2013, I already had 2016, but when installing the previous version the code worked perfectly.