save data in excel file -

Question

I have small application where user at the end can save the results in excel file. Everything looks fine but a few seconds after this appear Microsoft Windows communicate: "Program Microsoft Office Excel stop working". Ok - ingoring it.

If user saves data as *.xls file there is no problem to open this file - data saved into this file are correct.

If user saves data as *.xlsx file ther is a problem. When I try to open this file there show me message: "Program Microsoft Excel can't open file *.xlsx because there is wrong file format or extension. Check if the file was damaged and if the file extension is correct with his format".

The code that I use to save data as excel file are below:

public void SaveData(ExcelWriter ew)
    {
        SaveFileDialog saveFD = new SaveFileDialog();

        saveFD.InitialDirectory = "C:\\users\\Documents";
        saveFD.FileName = this._saveExcelFileName;
        saveFD.Filter = "excel 97-03(*.xls)|*.xls|excel 2007 (*.xlsx)|*.xlsx";
        saveFD.FilterIndex = 2;
        saveFD.RestoreDirectory = true;

        if (saveFD.ShowDialog() == DialogResult.OK)
        {
            try
            {
                this._saveExcelFileName = saveFD.FileName;
                ew.RunExcelWriter(_dt, _saveExcelFileName);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }


    public void RunExcelWriter(DataTable DataT, string fileName)
    {
        StartExcel();
        GetANewWorkbook();
        GetTheActiveSheet();
        ProcessTheDataTable(DataT);
        SaveTheSheet(fileName);
        Clean();
    }

private void StartExcel()
    {
        // Start Excel and get Application object.
        oXL = new Excel.Application();
        // Set some properties
        oXL.Visible = true;
        oXL.DisplayAlerts = false;
    }

    private void GetANewWorkbook()
    {
        // Get a new workbook.
        oWB = oXL.Workbooks.Add(Missing.Value);
    }

    private void GetTheActiveSheet()
    {
        oSheet = (Excel.Worksheet)oWB.ActiveSheet;
        oSheet.Name= "Wyniki";
    }

    private void ProcessTheDataTable(DataTable DT)
    {

        int rowCount = 1;
        foreach (DataRow dr in DT.Rows)
        {
            rowCount += 1;
            for (int i = 1; i < DT.Columns.Count + 1; i++)
            {
                // Add the header the first time through
                if (rowCount == 2)
                {
                    oSheet.Cells[1, i] = DT.Columns[i - 1].ColumnName;
                    
                }
                oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
            }
        }
        // Resize the columns
        oRange = oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[rowCount, DT.Columns.Count]);
        oRange.EntireColumn.AutoFit();
        //oRange.Style = oSheet.Cells.Style;      
    }

    private void SaveTheSheet(string FN)
    {
        oSheet = null;
        oRange = null;
        oWB.SaveAs(FN, Excel.XlFileFormat.xlWorkbookNormal,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            Excel.XlSaveAsAccessMode.xlShared,
            Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);
        
        if(oWB.Saved==true)
        {
            MessageBox.Show("Plik został zapisany pomyślnie");
        }
        else
        {
            MessageBox.Show("PLIKU NIE ZAPISANO");
        }
        
        oWB.Close(Missing.Value, Missing.Value, Missing.Value);
        oWB = null;
        oXL.Quit();
    }

If anyone knows why this happens? Many thanks in advance

Update 1:

The suggested answer didn't work. I change to the following:

private void SaveTheSheet(string FN)
    {
        oSheet = null;
        oRange = null;
        oWB.SaveAs(FN, Excel.XlFileFormat.xlXMLSpreadsheet,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            Excel.XlSaveAsAccessMode.xlShared,
            Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);
        
        if(oWB.Saved==true)
        {
            MessageBox.Show("Plik został zapisany pomyślnie");
        }
        else
        {
            MessageBox.Show("PLIKU NIE ZAPISANO");
        }
        
        oWB.Close(Missing.Value, Missing.Value, Missing.Value);
        oWB = null;
        oXL.Quit();
    }

Now when I saving there is a meesage:

"Exception from HRESULT: 0x800A03EC

Update 2:

I struggled a little bit yesterday and I managed to get the desired effect when writing data to the .xlsx format. When saving to .xls I still get the Microsoft Windows message to close the excel application, but at least (after the approval of another warning) I can open this file. Below code which I'm using to saving to .xlsx format:

oWB.SaveAs(FN, Excel.XlFileFormat.xlWorkbookDefault,
                Missing.Value, Missing.Value, false, false,
                Excel.XlSaveAsAccessMode.xlNoChange,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);

Here is to .xls

                oWB.SaveAs(FN, Excel.XlFileFormat.xlXMLSpreadsheet,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Excel.XlSaveAsAccessMode.xlNoChange,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);

Please - any advice is important.

Your question isn't getting many views. It might help to tag it with the programming language you're using. — Bill the Lizard

JanOlMajti JanOlMajti · Accepted Answer · 2012-03-28T11:43:33

In my case aslo works Excel.XlFileFormat.xlWorkbookDefault and Excel.XlFileFormat.xlOpenXMLWorkbook

 oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

or

 oWB.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

save data in excel file -

Update 1:

Update 2:

2 Answers