C# 使用 Microsoft.Office.Interop.Excel SaveAs 错误导出到 .xlsx
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9769703/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exporting to .xlsx using Microsoft.Office.Interop.Excel SaveAs Error
提问by MoonKnight
I am in the process of writing a module to export a DataTable to Excel using Microsoft.Office.Interop.Excelbut before starting in earnest I want to get the very basics working: open file, save as, and close.
我正在编写一个模块来将 DataTable 导出到 Excel，Microsoft.Office.Interop.Excel但在认真开始之前，我想让最基本的工作：打开文件，另存为，然后关闭。
I have succeeded in opening and saving a file with the .xls extension, but saving with the .xlsx extension does not work. It writes the .xlsx file, but when I try to open it I get the following error:
我已成功打开并保存扩展名为 .xls 的文件，但使用 .xlsx 扩展名保存不起作用。它写入 .xlsx 文件，但是当我尝试打开它时出现以下错误：
Excel cannot open the file 'SomeFile.xlsx' because the file format is not valid. Verify that file has not been corrupted and that the file extension matched the format of the file.
Excel 无法打开文件“SomeFile.xlsx”，因为文件格式无效。验证文件未损坏并且文件扩展名与文件格式匹配。
The code I use to open, save and close the files is:
我用来打开、保存和关闭文件的代码是：
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);
I have also tried the file format Excel.XlFileFormat.xlExcel12in place of Excel.XlFileFormat.xlWorkbookNormalbut this does not even write instead throwing the COMException:
我也尝试过使用文件格式Excel.XlFileFormat.xlExcel12代替，Excel.XlFileFormat.xlWorkbookNormal但这甚至没有写入而是抛出 COMException：
Exception from HRESULT: 0x800A03EC
HRESULT 异常：0x800A03EC
Any help resolving this would be most appreciated.
任何帮助解决这个问题将不胜感激。
Edit: I have now also tried:
编辑：我现在也尝试过：
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);
采纳答案by MoonKnight
This is how you save the relevant file as a Excel12 (.xlsx) file... It is not as you would intuitively think i.e. using Excel.XlFileFormat.xlExcel12but Excel.XlFileFormat.xlOpenXMLWorkbook. The actual C# command was
这是您将相关文件另存为 Excel12 (.xlsx) 文件的方式...这与您直觉上认为的不同，即使用Excel.XlFileFormat.xlExcel12but 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);
I hope this helps someone else in the future.
我希望这会在将来对其他人有所帮助。
Missing.Valueis found in the System.Reflectionnamespace.
Missing.Value在System.Reflection命名空间中找到。
回答by Steve
Try changing the second parameter in the SaveAs call to Excel.XlFileFormat.xlWorkbookDefault.
尝试将 SaveAs 调用中的第二个参数更改为 Excel.XlFileFormat.xlWorkbookDefault。
When I did that, I generated an xlsx file that I was able to successfully open. (Before making the change, I could produce an xlsx file, but I was unable to open it.)
当我这样做时，我生成了一个能够成功打开的 xlsx 文件。（在进行更改之前，我可以生成一个 xlsx 文件，但我无法打开它。）
Also, I'm not sure if it matters or not, but I'm using the Excel 12.0 object library.
另外，我不确定这是否重要，但我使用的是 Excel 12.0 对象库。
回答by lalitha
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();
}
}
}
回答by Ajay Padharia
myBook.Saved = true;
myBook.SaveCopyAs(xlsFileName);
myBook.Close(null, null, null);
myExcel.Workbooks.Close();
myExcel.Quit();
