我在将数据网格视图传输到 Excel 时遇到问题

Question

我在下面的datagridview中出现错误的原因是什么？

System.InvalidCastException：无法将类型为"Microsoft.Office.Interop.Excel.ApplicationClass"的COM对象分配给接口类型"Microsoft.Office[Interop.Eexcel_Application"。此操作失败，因为COM组件中IID为"｛000208D5-0000-0000-C000-000000000046｝"的接口的QueryInterface调用失败，并出现以下错误：加载时出错类型库/DLL。(返回HRESULT异常：0x80029C4A(TYPE_E_CANTLOADLIBRARY((。'

我写的代码是：

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

Answer 1

使用发布的代码，我没有得到你描述的错误…

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

这在创建新工作簿时似乎是正确的。但是，在将数据写入工作簿时，似乎出现了问题。问题是代码正在将数据写入excelApp，这是不正确的。excelApp可以打开多个工作簿，每个工作簿可以有多个"工作表"。您需要指定要写入的"位置"(工作簿中的哪个工作表(。

由于您正在创建一个新的工作簿，您需要"添加"一个新工作表并写入该工作表，而不是excelApp.

我测试了下面的代码，它将数据正确地写入新工作簿中的新工作表。

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