Error exporting to Excel (Corrupted file)

2

I am making an application to export the result of a sql query to Excel. After the file created the time I'm going to open it it gives a corrupted file error.

SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Execl files (*.xls)|*.xls";
sfd.FileName = "teste";

if (sfd.ShowDialog() == DialogResult.OK)
{
    Stream s = File.Open(sfd.FileName, FileMode.CreateNew);
    xlApp.DisplayAlerts = false;
    xlWorkBook.SaveAs(s, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel12, 
                      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);
    xlWorkBook.Close(true, misValue, misValue);         
}

    
asked by anonymous 26.01.2015 / 15:23

1 answer

2

I do not know this library and I do not know if it has any specific problems using it but this code is strange.

I imagine the first parameter of xlWorkBook.SaveAs to be the name of the file. Then pass the filename there and not a Stream . Which by the way was opened without doing anything with it and it was not closed.

What was closed was xlWorkBook itself. Probably it should not have been closed there. If it was open somewhere else this place is supposed to be responsible for closing. In fact, the closure should be automatic. When you try to close manually you may be a resource leak if an exception is thrown. Few programmers realize this. They test in the best situation and forget that if something goes wrong, you will have a bad situation and probably more difficult to identify the problem.

Without knowing the whole, if I understood the intention, I would say that this solves the problem (if it does not solve, it probably has some more closed that is not apparent):

SaveFileDialog sfd = new SaveFileDialog();

sfd.Filter = "Execl files (*.xls)|*.xls";
sfd.FileName = "teste";

if (sfd.ShowDialog() == DialogResult.OK) {
    xlApp.DisplayAlerts = false;
    xlWorkBook.SaveAs(sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel12,
        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);
    xlWorkBook.Close(true, misValue, misValue); //isto deveria estar aqui mesmo?
}

And something tells me that this xlApp.DisplayAlerts = false; is there to "hide" problems that are happening.

    
26.01.2015 / 15:38