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, Nov 17 '19 at 12:36
Yes, excel installed.speak error receiving line error receiving line;excelApp.Application.Workbooks.Add(Type.Missing); — Nasuf Mutlu, Nov 17 '19 at 12:44
I'm no expert with Excel interop, but passing `Type.Missing` to `Workbooks.Add()` doesn't look like it would work. — Crowcoder, Nov 17 '19 at 12:48
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, Nov 17 '19 at 12:52

JohnG · Answer 1 · 2019-11-19T01:22:59.487

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);
}

Thank you so much.I tested the code. But I got the same error again. Microsoft.Excel.Interop.Excel is also installed in the references.I don't understand what the problem is. :( — Nasuf Mutlu, Nov 18 '19 at 15:58
Reaching for straws here, however, from your comment… _”this code has already been used in other applications_” … makes me wonder if the Excel library (in the code) matches the target machine. Example, if you take the code from another machine that is using a “different” version of the Excel library may cause this error. I would think that “any” interop commands at that point would fail, however, I do know that if you take the code that uses MS Excel 14.0 object library and run it on a machine that uses MS Excel 12.0 Object library, then you may have some compatibility issues. — JohnG, Nov 19 '19 at 00:37
Fortunately, you should be able to simply “dump” the current version and re-add the Excel reference that targets the local machine. I.E. … right click on the projects “References” then delete the Excel Lib, then, re-add it. This will at least ensure that the code library reference is the same used on that machine. — JohnG, Nov 19 '19 at 00:38
If that does not work, then there is another possibility if the current machine had at some earlier time a “later” version of Excel than the current version, then, my understanding is that a registry entry may be lingering from the previous version and the entry needs to be removed. Check this SO answer… [unable to cast COM object of type 'microsoft.Office.Interop.Excel.ApplicationClass' to 'microsoft.Office.Interop.Excel.Application'"](https://stackoverflow.com/questions/28066719/unable-to-cast-com-object-of-type-microsoft-office-interop-excel-applicationcla) — JohnG, Nov 19 '19 at 00:38
One other point in reference to using interop objects. In the current code, you will notice that even though we are closing the workbook and quitting the `excelApp`… the “com” objects will remain. You can see this by running the code to completion, and open the task manager. You will notice that there is a background “Excel” app still running. Point being, that it is important to “RELEASE” those lingering “com” objects the code created to avoid leaking a resource. I added this code to my answer. — JohnG, Nov 19 '19 at 01:20
Thank you so much.The problem is caused by the Microsoft Office product.The problem improved after reloading. :)))))) — Nasuf Mutlu, Nov 19 '19 at 18:07

I'm having trouble transferring datagridview to Excel

1 Answers1