使用Microsoft.Office.Interop.Excel另存为错误导出到.xlsx
本文关键字:xlsx 错误 另存为 Microsoft Office Interop Excel 使用 | 更新日期: 2023-09-27 18:28:19
我正在编写一个使用Microsoft.Office.Interop.Excel
将DataTable导出到Excel的模块,但在认真开始之前,我想先完成一些基本的工作:打开文件、另存为和关闭。
我已成功打开并保存了一个扩展名为.xls的文件,但使用.xlsx扩展名保存不起作用。它写入.xlsx文件,但当我试图打开它时,我会收到以下错误:
Excel无法打开文件"SomeFile.xlsx",因为该文件格式无效。请验证文件是否已损坏,以及文件扩展名是否与文件的格式匹配。
我用来打开、保存和关闭文件的代码是:
Excel.Application excelApplication = new Excel.Application();
//excelApplication.Visible = true;
//dynamic excelWorkBook = excelApplication.Workbooks.Add();
Excel.Workbook excelWorkBook = excelApplication.Workbooks.Add();
//Excel.Worksheet wkSheetData = excelWorkBook.ActiveSheet;
int rowIndex = 1; int colIndex = 1;
excelApplication.Cells[rowIndex, colIndex] = "TextField";
// This works.
excelWorkBook.SaveAs("C:''MyExcelTestTest.xls", Excel.XlFileFormat.xlWorkbookNormal,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false,
Excel.XlSaveAsAccessMode.xlShared, false, false, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value);
// This does not!?
excelWorkBook.SaveAs("C:''MyExcelTestTest.xlsx", Excel.XlFileFormat.xlWorkbookNormal,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false,
Excel.XlSaveAsAccessMode.xlShared, false, false, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value);
excelWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
我也尝试过用Excel.XlFileFormat.xlExcel12
代替Excel.XlFileFormat.xlWorkbookNormal
的文件格式,但这甚至没有写入,而是抛出了COMException:
HRESULT:0x800A03EC 出现异常
如有任何帮助,我们将不胜感激。
编辑:我现在也尝试过:
excelWorkBook.SaveAs("C:''MyExcelTestTest", Excel.XlFileFormat.xlExcel12,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false,
Excel.XlSaveAsAccessMode.xlShared, false, false, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value);
这就是将相关文件保存为Excel12(.xlsx)文件的方法。。。它并不像您直观地认为的那样,即使用Excel.XlFileFormat.xlExcel12
,而是使用Excel.XlFileFormat.xlOpenXMLWorkbook
。实际的C#命令是
excelWorkbook.SaveAs(strFullFilePathNoExt, Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value,
Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlUserResolution, true,
Missing.Value, Missing.Value, Missing.Value);
在System.Reflection
命名空间中找到Missing.Value
。
尝试将SaveAs调用中的第二个参数更改为Excel.XlFileFormat.xlWorkbookDefault.
当我这样做的时候,我生成了一个xlsx文件,我能够成功地打开它。(在进行更改之前,我可以生成一个xlsx文件,但无法打开它。)
此外,我不确定这是否重要,但我使用的是Excel12.0对象库。
public static void ExportToExcel(DataGridView dgView)
{
Microsoft.Office.Interop.Excel.Application excelApp = null;
try
{
// instantiating the excel application class
excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook currentWorkbook = excelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet currentWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)currentWorkbook.ActiveSheet;
currentWorksheet.Columns.ColumnWidth = 18;
if (dgView.Rows.Count > 0)
{
currentWorksheet.Cells[1, 1] = DateTime.Now.ToString("s");
int i = 1;
foreach (DataGridViewColumn dgviewColumn in dgView.Columns)
{
// Excel work sheet indexing starts with 1
currentWorksheet.Cells[2, i] = dgviewColumn.Name;
++i;
}
Microsoft.Office.Interop.Excel.Range headerColumnRange = currentWorksheet.get_Range("A2", "G2");
headerColumnRange.Font.Bold = true;
headerColumnRange.Font.Color = 0xFF0000;
//headerColumnRange.EntireColumn.AutoFit();
int rowIndex = 0;
for (rowIndex = 0; rowIndex < dgView.Rows.Count; rowIndex++)
{
DataGridViewRow dgRow = dgView.Rows[rowIndex];
for (int cellIndex = 0; cellIndex < dgRow.Cells.Count; cellIndex++)
{
currentWorksheet.Cells[rowIndex + 3, cellIndex + 1] = dgRow.Cells[cellIndex].Value;
}
}
Microsoft.Office.Interop.Excel.Range fullTextRange = currentWorksheet.get_Range("A1", "G" + (rowIndex + 1).ToString());
fullTextRange.WrapText = true;
fullTextRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
else
{
string timeStamp = DateTime.Now.ToString("s");
timeStamp = timeStamp.Replace(':', '-');
timeStamp = timeStamp.Replace("T", "__");
currentWorksheet.Cells[1, 1] = timeStamp;
currentWorksheet.Cells[1, 2] = "No error occured";
}
using (SaveFileDialog exportSaveFileDialog = new SaveFileDialog())
{
exportSaveFileDialog.Title = "Select Excel File";
exportSaveFileDialog.Filter = "Microsoft Office Excel Workbook(*.xlsx)|*.xlsx";
if (DialogResult.OK == exportSaveFileDialog.ShowDialog())
{
string fullFileName = exportSaveFileDialog.FileName;
// currentWorkbook.SaveCopyAs(fullFileName);
// indicating that we already saved the workbook, otherwise call to Quit() will pop up
// the save file dialogue box
currentWorkbook.SaveAs(fullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, Missing.Value, Missing.Value, Missing.Value);
currentWorkbook.Saved = true;
MessageBox.Show("Error memory exported successfully", "Exported to Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (excelApp != null)
{
excelApp.Quit();
}
}
}
myBook.Saved = true;
myBook.SaveCopyAs(xlsFileName);
myBook.Close(null, null, null);
myExcel.Workbooks.Close();
myExcel.Quit();