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

What version of Excel do you have installed on the machine? – Jodrell Mar 19 '12 at 12:03 — Jodrell, Mar 19 '12 at 12:03

score 103 · Accepted Answer · edited Dec 24 '22 at 02:52

103

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

Missing.Value is found in the System.Reflection namespace.

edited Dec 24 '22 at 02:52

starball

20,030
7
43
238

answered Mar 22 '12 at 13:03

MoonKnight

23,214
40
145
277

2

This was very helpful! I only had to tweak the code a little to use the Office 2007 libraries (Version 12) as xlsWorksheet.SaveAs(filename, XlFileFormat.xlOpenXMLWorkbook, Missing.Value, Missing.Value, false, false, false, Missing.Value, Missing.Value); – Ted Aug 27 '13 at 23:21

score 11 · Answer 2 · answered Mar 19 '12 at 14:22

11

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.

answered Mar 19 '12 at 14:22

Steve

543
1
6
14

unfortunately this has not worked. Using `Excel.XlFileFormat.xlWorkbookDefault` give me a .xls file. I am using the new Excel 14.0 object library for reference... – MoonKnight Mar 20 '12 at 14:21
I'm not sure what you mean by its giving you an xls file. I have Excel 2007 on my machine. If I hit the Office button at the top left, press the Excel Options button, then select Save, I see a choice for "Save files in this format". Mine says .xlsx. What does yours say? – Steve Mar 22 '12 at 12:50
When I create the workbook, I have a parameter in the Add method: "workbook = excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);" Also, you probably know this already, but you may want to look into open xml as an alternative for working with spreadsheets. – Steve Mar 22 '12 at 13:17
We currently work with SpreadSheetgear which is the quickest spreadsheet facility you are ever likely to come accross. However, we need to hand the current project over to another company/set of developers hence the use of this very slow and clunky COM interface. I have also written an exported that can do excel files using OleDb and the Access Database Engin but this does not allow for formatting. Thanks very much for your help. – MoonKnight Mar 22 '12 at 15:00
1

I suspect you will find that xlWorkbookDefault saves it as the user defined default file format. File -> Option -> Save -> "Save Files In this Format" – Roger Willcocks Mar 29 '16 at 03:21

score 1 · Answer 3 · answered Apr 18 '12 at 08:40

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



    }

score 0 · Answer 4 · edited Aug 23 '13 at 07:44

0

myBook.Saved = true;
myBook.SaveCopyAs(xlsFileName);
myBook.Close(null, null, null);
myExcel.Workbooks.Close();
myExcel.Quit();

edited Aug 23 '13 at 07:44

kleopatra

51,061
28
99
211

answered Aug 23 '13 at 07:36

Ajay Padharia

11
1

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

4 Answers4

Linked

Related