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

User · Answer

myBook Saved   true  myBook SaveCopyAs xlsFileName   myBook Close null  null  null   myExcel Workbooks Close    myExcel Quit

User · Answer

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

User · Answer

Try changing the second parameter in the SaveAs call to 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    Also  I m not sure if it matters or not  but I m using the Excel 12 0 object library

User · Answer

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  gt  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  lt  dgView Rows Count  rowIndex                                          DataGridViewRow dgRow   dgView Rows rowIndex                       for  int cellIndex   0  cellIndex  lt  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

[c#] Exporting to .xlsx using Microsoft.Office.Interop.Excel SaveAs Error

Examples related to c#

Examples related to excel

Examples related to winforms-interop