Resolve error in Excel file after exporting

Question

I have a function to export my GridViews to Excel as .xlsx. However I face a problem with the Excel file that was opened/saved with a save up dialogue. The error happens when I try to open the Excel file:

My current code:

  protected void EXPORT_BUTTON_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            String DATA1 = "DATA 1";
            String DATA2 = "DATA 2";
            ExportToExcel(app, workbook, DATA1 , DATA_1 );
            workbook.Worksheets["Sheet1"].Delete();
            workbook.Worksheets["Sheet2"].Delete();
            workbook.Worksheets["Sheet3"].Delete();
            ExportToExcel(app, workbook, DATA2 , DATA_2);
            workbook.SaveAs(@"C:\Users\testacc\Desktop\Test\" + "Server_" + datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            var filename = "Report_" + datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xlsx";
            workbook.SaveAs(Server.MapPath("~/Exports/") + filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment, filename=" + filename);
            workbook.Close();
            Response.TransmitFile(Server.MapPath("~/Exports/") + filename);
            app.Quit();
        }

this could be caused due to non proper closing of excel file without saving changes or sudden shut down — Arijit Mukherjee
– Arijit Mukherjee, Commented Oct 19, 2015 at 6:12
currently im using workbook.close() for my Response.TransmitFile(Server.MapPath("~/Exports/") + filename); to work. Please advice how can I change my codes to close the workbook thanks — James Boer
– James Boer, Commented Oct 19, 2015 at 6:14
well I do not remember the actual codes, but what I can suggest you is try closing the application as well — Arijit Mukherjee
– Arijit Mukherjee, Commented Oct 19, 2015 at 6:16
do you want to save the file to specific folder or just download it? — shreesha
– shreesha, Commented Oct 19, 2015 at 6:22
I tried app.Quit(); before response.ContentType ... but I still receive the same error. — James Boer
– James Boer, Commented Oct 19, 2015 at 6:22

Prashant Kankhara · Accepted Answer · 2015-10-19 06:37:13Z

You can try below code, if it works.

protected void EXPORT_BUTTON_Click(object sender, EventArgs e)
        {
            using(Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application())
            // creating new WorkBook within Excel application
             using(Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing))
             {
                String DATA1 = "DATA 1";
                String DATA2 = "DATA 2";
                ExportToExcel(app, workbook, DATA1 , DATA_1 );
                workbook.Worksheets["Sheet1"].Delete();
                workbook.Worksheets["Sheet2"].Delete();
                workbook.Worksheets["Sheet3"].Delete();
                ExportToExcel(app, workbook, DATA2 , DATA_2);
                workbook.SaveAs(@"C:\Users\testacc\Desktop\Test\" + "Server_" + datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                var filename = "Report_" + datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xlsx";
                workbook.SaveAs(Server.MapPath("~/Exports/") + filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment, filename=" + filename);
                workbook.Close();
                Response.TransmitFile(Server.MapPath("~/Exports/") + filename);
                Response.End();
                app.Quit();
            }
        }

Collectives™ on Stack Overflow

Resolve error in Excel file after exporting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related