Exporting to .xlsx using Microsoft.Office.Interop.Excel SaveAs Error

Question

I am in the process of writing a module to export a DataTable to Excel using Microsoft.Office.Interop.Excel but before starting in earnest I want to get the very basics working: open file, save as, and close.

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:

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.

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.xlExcel12 in place of Excel.XlFileFormat.xlWorkbookNormal but this does not even write instead throwing the COMException:

Exception from 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); 

Answer 1

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.xlExcel12 but Excel.XlFileFormat.xlOpenXMLWorkbook. The actual C# command was

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.Value is found in the System.Reflection namespace.