I've recently been tasked with creating a web page that allows a user to select some data, and then allows them to download a complicated excel workbook (complete with macros) with that data filled in to the appropriate cells. I was given a .vbs as a starting point for filling out an excel workbook. in the .vbs, the original programmer calls

Set objExcel =  CreateObject("Excel.Application")
set ObjWorkbook =objExcel.Workbooks.Open(blankFile)
`...
objWorkbook.Saveas(outputFile)
objExcel.Quit

and that works fine for him. I'm trying to make a web page using C# and MVC, so I looked into Excel.Application and found some semi-useful things, like this and this. So I developed an action, just to test things out, it looks like this:

public FileResult filledOutWorkbook()
{
    Excel.Application oXL;
    Excel._Workbook oWB;
    Excel._Worksheet oSheet;
    Excel.Range oRng;

    oXL = new Excel.Application();
    oXL.Visible = false;

    oWB = oXL.Workbooks.Open(Server.MapPath("~/Content/Worksheet2.xlsm"));
    oSheet = oWB.Sheets["Information Sheet"];

    oSheet.Range["V2"].Value2 = "customer test lol";

    if (!Directory.Exists(Server.MapPath("~/App_Data/temp")))
    {
        Directory.CreateDirectory(Server.MapPath("~/App_Data/temp"));
    }
    var filename = Server.MapPath("~/App_Data/temp/") + DateTime.Now.ToString("o").Replace(":", "") + ".xlsm";
    oWB.SaveCopyAs(filename);

    oXL.Visible = false;
    oXL.Quit();

    return File(filename, "application/vnd.ms-excel.sheet.macroEnabled.12", "Worksheet.xlsm");
}

(yes, I'm going to have to do a scheduled task or something to clear these out because I'm pretty sure I can't just get a byte stream of the file)

The problem is, this doesn't close excel. What you're apparently doing in C# is not interacting with a library that understands excel workbooks' formatting, you're automating excel. Which I would describe as asinine. I would tolerate it, and just have to suck it up and install excel on the production server, but the problem is that when I tell it to quit excel it doesn't quit. It asks the user if they want to save changes to the template document. That's terrible, I don't know how many times this page is going to get used but I think it's bad practice to have to go in and click "no" however many times on the server just to free up memory. So can I circumvent all this? is there a better library than the COM object Microsoft.Office.Interop.Excel? or can I force excel to close? Or is my best bet to just write all the excel interaction in vb and call that from the controller action?

5 Answers

2
Mohamed Najiullah On Best Solutions

As someone who has used and suffered with the Microsoft Excel Interoperability Library, I'm glad you are looking for an alternative.

Don't get me wrong. The interoperability library is extremely useful if you're trying to automate some simple task with small amounts of data. But it's ridiculously slow when it involves large amounts of data. And if you want this on a web server, you need to have Excel Installed on the server as well.

I suggest you use ClosedXML. It's open source and MIT licensed. It's basically a wrapper around OpenXML. It's also very easy to use, much faster and doesn't need Excel installed on the server

https://github.com/ClosedXML/ClosedXML

2
Dave Cousineau On

If it asks you to save, then it means it thinks the workbook has unsaved changes. Just close the workbook and don't save changes before quitting:

oWB.Close(false);

You might also need to close the first default workbook, in fact that might actually be the one that's causing the save message. Look for another workbook and close it without saving. Something like:

oXL.Workbooks[0].Close(false);

Or just enumerate the collection and close all of them.

It will depend on the actual state that Excel is in. If you're unsure of what to do, show Excel instead of closing it, and try to see what it is that it thinks is unsaved and decide if you want to save it or discard it.

2
Hambone On

You have two good answers here, but as an alternative, you could also tell Excel to suspend warnings. This is helpful any time you do something that would normally prompt a dialog, such as "Save As" on a file that already exists.

The DisplayAlerts property on the Excel object controls this and is true by default. Changing it to false will suspend alerts, so this should also work:

application.DisplayAlerts = false;
application.Quit();
application.DisplayAlerts = true;
0
Christian Loris On

I would suggest you take a different approach here. I would create a tab in the excel workbook where the user enters their choices that drive the data selection. Then write some script to use those settings to pull the data from a web service. See this article as an example: https://atinkerersnotebook.com/2012/12/28/creating-consuming-web-services-with-excel/

Now all you have to do is write the web service to return the requested data.

0
AlexDev On

I want to suggest a bit of a different way to generate an excel file. For medium complexity worksheets it's much easier to use Report Designer to design the report, and then export it to an excel file. It can be generated to a stream, you don't even have to generate a file.