Generate an excel file from a data grid C #

Question

Generate an excel file from a data grid C #

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

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

Anunhandledexceptionoftype'System.InvalidCastException'occurredinEletronicTaxNotes.exe

Additionalinformation:TheCOMobjectoftype'Microsoft.Office.Interop.Excel.ApplicationClass'cannotbeconvertedintheinterfacetype'Microsoft.Office.Interop.Excel._Application'.ThisoperationfailedbecausetheQueryInterfacecallontheCOMcomponentfortheinterfacewithIID'{000208D5-0000-0000-C000-000000000046}'failedduetothefollowingerror:Errorloadingthelibrary/DLLoftype.(ExceptionfromHRESULT:0x80029C4A(TYPE_E_CANTLOADLIBRARY)).

privatevoidExportarExcel2(){//CreatingaExcelobject.Excel._Applicationexcel=newExcel.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 reference do I need to use Microsoft.Office.Interop.Excel in .NET?

Can not find Microsoft.Office.Interop Visual Studio

Ialsotriedothersuggestions,whichistodeleteaduplicaterecordthatcouldbecausingtheerror:

Error Printing When Using Microssoft.office.interop

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

Likewise,thefollowingsuggestionsdidnothelpeither:

Error accessing COM components

Class not registered error when creating Excel workbook in C #

Unfortunately, none of this has any effect, and the result is always the same:

I'm running out of options to move on. I imagined that such a task would be something simple to do. Does anyone have any other suggested solution that can help me?

c# excel office interop

asked by anonymous 02.05.2017 / 15:44

2 answers

How to add scrollbar to a Scrollpane? HTTP return 0

score 1 · Answer 1

Here is the 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);
            }

score 0 · Answer 2

0

I was able 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.

02.05.2017 / 20:31