CODEWITHSUNDEEP
c#excelepplus
Coxy
Coxy

Reputation: 8928

When trying to close a spreadsheet, Excel asks user whether to save changes

We generate a bunch of reports to Excel spreadsheets using EPPlus.

Typically the code goes something like this:

var workbookFile = new FileInfo(reportFile);
using (var excel = new ExcelPackage(workbookFile))
{
    var wb = excel.Workbook;
    var ws = wb.GetCleanWorksheet("Report");
    ws.Select();

    // write data to sheet
    ws.Cells[1, 1].Value = "foo";

    excel.Save();
}

When the user opens the spreadsheet, everything looks fine. When they try to close the spreadsheet without having made any changes, Excel will ask them whether they want to save their changes. This isn't too big a deal but it's annoying and slightly worrying.

I've opened the spreadsheets in the OpenXML SDK Productivity Tool and they pass validation in that.

Upvotes: 9

Views: 1757

Answers (5)

Vortex852456
Vortex852456

Reputation: 739

Turn "DisplayAlerts" off while closing. This can be done with an integrated VBA script. Just add following code (workbook is your current workbook):

workbook.CreateVBAProject();
workbook.CodeModule.Name = "DisplayAlertsOff";
StringBuilder sb = new StringBuilder();
sb.AppendLine("Sub CloseBook()");
sb.AppendLine("    Application.DisplayAlerts = False");
sb.AppendLine("    ActiveWorkbook.Close");
sb.AppendLine("    Application.DisplayAlerts = True");
sb.AppendLine("End Sub");
workbook.CodeModule.Code = sb.ToString();

Upvotes: 1

Ulf Kristiansen
Ulf Kristiansen

Reputation: 1631

Try upgrading to the latest version. As of version 4.0.4.0 this doesn't seem to be a problem with simple sheets. The syntax is a little different though:

var workbookFile = new FileInfo(reportfile);
using (var excel = new ExcelPackage(workbookFile))
{
    ExcelWorksheet ws = excel.Workbook.Worksheets.Add("Report");
    ws.Cells[1, 1].Value = "foo";
    excel.Save();
}

Excel does not ask me to save anything (unless I change something). Note that since the file hasn't run through Excels DOM, the behavior you see is still somewhat expected in complex workbooks, because Excel will apply its own formatting, but I don't see it in simple sheets any more.

Upvotes: 1

getglad
getglad

Reputation: 2563

I am not sure about EPPLus, but the Excel Interop would allow you to silence those alerts.

var xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;

Excel will then choose the default response.

Upvotes: 2

Tyress
Tyress

Reputation: 3653

One solution would be to do a ninja style open-save-close with Interop Excel after you save the file with EPPlus. Something like:

using Excel = Microsoft.Office.Interop.Excel;

var workbookFile = new FileInfo(reportFile);
using (var excel = new ExcelPackage(workbookFile))
{
    var wb = excel.Workbook;
    var ws = wb.GetCleanWorksheet("Report");
    ws.Select();

    // write data to sheet
    ws.Cells[1, 1].Value = "foo";

    excel.Save();
}
Excel.Application _Excel = null;
Excel.Workbook WB = null;
try
{

    _Excel = new Microsoft.Office.Interop.Excel.Application();
    WB = _Excel.Workbooks.Open(reportFile);
    _Excel.DisplayAlerts = false;
    WB.Close(true);
}
catch (Exception ex)
{
    WB.Close(false);
}
finally
{
    GC.Collect();
    GC.WaitForPendingFinalizers();
    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(WB);
    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_Excel);
}

Upvotes: 2

microstat10
microstat10

Reputation: 71

Here's an response to a similar question answered by Microsoft support at http://support.microsoft.com/kb/213428 "To force a workbook to close without saving any changes, type the following code in a Visual Basic module of that workbook:

Sub Auto_Close()

ThisWorkbook.Saved = True

End Sub

Because the Saved property is set to True, Excel responds as though the workbook has already been saved and no changes have occurred since that last save."

Hope that helps.

Upvotes: 6

Related Questions