I'm having trouble transferring datagridview to Excel

Question

What is the reason I am getting the error like in datagridview below?

System.InvalidCastException: The COM object of type '' Microsoft.Office.Interop.Excel.ApplicationClass' could not be assigned to interface type 'Microsoft.Office.Interop.Excel._Application'. This operation failed because the QueryInterface call in the COM component for the interface with the IID '{000208D5-0000-0000-C000-000000000046}' failed with the following error: Error loading type library / DLL. (HRESULT exception returned: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)). '

The code I wrote is:

saveFileDialog.InitialDirectory = "C:";
saveFileDialog.Title = "Save as Excel File";
saveFileDialog.FileName = "Data";
saveFileDialog.Filter = "Excel Files(2003)|*.xls|Excel Files(2007)|*.xlsx";

if (saveFileDialog.ShowDialog() != DialogResult.Cancel)
{
    Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
    excelApp.Application.Workbooks.Add(Type.Missing);

    excelApp.Columns.ColumnWidth = 20;

    for (int i = 1; i < dgwReport.Columns.Count + 1; i++)
    {
        excelApp.Cells[1, i] = dgwReport.Columns[i - 1].HeaderText;
    }

    for (int i = 0; i < dgwReport.Rows.Count; i++)
    {
        for (int j = 0; j < dgwReport.Columns.Count; j++)
        {
            excelApp.Cells[i + 2, j + 1] = dgwReport.Rows[i].Cells[j].Value;
        }
    }

    excelApp.ActiveWorkbook.SaveCopyAs(saveFileDialog.FileName.ToString());
    excelApp.ActiveWorkbook.Saved = true;
    excelApp.Quit();
}

What line throws the exception? Do you have Excel installed? — Crowcoder
Yes, excel installed.speak error receiving line error receiving line;excelApp.Application.Workbooks.Add(Type.Missing); — Nasuf Mutlu
I'm no expert with Excel interop, but passing Type.Missing to Workbooks.Add() doesn't look like it would work. — Crowcoder
yes, this code has already been used in other applications, but it should work normally.I don't understand where the problem is. :( — Nasuf Mutlu

JohnG JohnG · Accepted Answer · 2019-11-18T00:36:00

Using the posted code, I did not get the error you describe…

excelApp.Application.Workbooks.Add(Type.Missing);

This appears correct in creating a new Workbook. However, when it comes to writing the data to the workbook it appears to have a problem. The problem is that the code is writing the data to the excelApp and this is incorrect. The excelApp could have numerous workbooks open and each workbook could have numerous “worksheets.” You need to specify “where” (which worksheet in which workbook) you want to write to.

Since you are creating a new workbook, you need to “add” a new worksheet and write to that worksheet instead of the excelApp.

I tested the code below and it writes the data properly to a new worksheet in a new workbook.

saveFileDialog.InitialDirectory = "C:";
saveFileDialog.Title = "Save as Excel File";
saveFileDialog.FileName = "Data";
saveFileDialog.Filter = "Excel Files(2003)|*.xls|Excel Files(2007)|*.xlsx";

Microsoft.Office.Interop.Excel.Application excelApp = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;

try {
   if (saveFileDialog.ShowDialog() != DialogResult.Cancel) {
    excelApp = new Microsoft.Office.Interop.Excel.Application();
    workbook = excelApp.Application.Workbooks.Add(Type.Missing);
    worksheet = workbook.ActiveSheet;
    excelApp.Columns.ColumnWidth = 20;
    for (int i = 1; i < dgwReport.Columns.Count + 1; i++) {
      worksheet.Cells[1, i] = dgwReport.Columns[i - 1].HeaderText;
    }
    for (int i = 0; i < dgwReport.Rows.Count; i++) {
      for (int j = 0; j < dgwReport.Columns.Count; j++) {
        worksheet.Cells[i + 2, j + 1] = dgwReport.Rows[i].Cells[j].Value;
      }
    }
    excelApp.ActiveWorkbook.SaveCopyAs(saveFileDialog.FileName.ToString());
    excelApp.ActiveWorkbook.Saved = true;
    workbook.Close();
    excelApp.Quit();
  }
}
catch (Exception ex) {
  MessageBox.Show("Excel write error: " + ex.Message);
}
finally {
  // release the excel objects to prevent leaking the unused resource
  if (worksheet != null)
    Marshal.ReleaseComObject(worksheet);
  if (workbook != null)
    Marshal.ReleaseComObject(workbook);
  if (excelApp != null)
    Marshal.ReleaseComObject(excelApp);
}

I'm having trouble transferring datagridview to Excel

1 Answers