Creating Excel File stream using Interop Services for WCF Service

Question

In my application, I am trying t create a WCF service that accepts a tabular json data and that converts that json data into Excel file stream and sends back to client. So for creating Excel file i am using the following code that uses Interop Service

        object misValue = System.Reflection.Missing.Value;

        var xlApp = new Excel.Application();
        var xlWorkBook = xlApp.Workbooks.Add(misValue);

        var xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Item[1];
        xlWorkSheet.Cells[1, 1] = "FirstName";
        xlWorkSheet.Cells[1, 2] = "Last Name";
        xlWorkSheet.Cells[1, 3] = "Age";


        xlWorkSheet.Cells[2, 1] = "Stezma";
        xlWorkSheet.Cells[2, 2] = "Seb";
        xlWorkSheet.Cells[2, 3] = "25";

        xlWorkBook.SaveAs("csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

**Here my doubts are **

Is it advisable to use Interop Services for this requirement, If no What are the other alternatives that i can go for to create excel file stream.
If it is advisable how i can create the stream of that excel file created, Currently the files saves in document folder of machine. I don't want to save file into local folder, How i can get the stream of that file through interop method.

Duplicate of http://stackoverflow.com/questions/24436298/how-to-create-a-excel-file-using-wcf-service ? — a-h, Jun 26 '14 at 18:13
Sorry, I answered that other question, but it has been deleted by the author. — a-h, Jun 27 '14 at 16:06
In the other answer, I suggested using Office Open XML, since it has no interop requirements, no licensing issue (since you don't need to install Office on the server). There's more information here: http://msdn.microsoft.com/en-us/library/hh180830(v=office.14).aspx — a-h, Jun 27 '14 at 16:07

score 1 · Answer 1 · edited May 23 '17 at 10:26

The Excel Interop will work. However, you must be careful with it. You must manually dispose of interop objects using System.Runtime.InteropServices.Marshal.ReleaseComObject. Additionally, you must avoid using the property of a property / "Double Dots" while using the Interop.

See Cleaning up Excel Interop

Failure to do so will result in the Excel Application process refusing to close. This causes a major memory leak that will eventually crash your server!

If you wish to avoid all the headaches of the interop, I would suggest Visual Studio Tools for Office (VSTO). It handles all of the cleanup for you, while offering even more control than the interop.

However, VSTO requires a local Office installation.

If all you are doing is adding data, you can access Excel files using Access' database drive, ACE, with OLEDB. Writing to Excel with OLEDB

OLEDB is fast and lightweight for data transactions, and treats Excel as just another database. However, it is unable to adjust any other facet of the file such as formatting. It also runs on straight SQL, so creating custom SQL strings can be a pain.

Unfortunately none of these methods really supports "streaming only". Interop and VSTO will create a WorkBook in the temp folder when opening a new WorkBook. Additionally, if OLEDB doesn't find a file it will simply create a new file.

Even when someone manually opens an Excel file at their desk to do spreadsheet work, Office creates a copy in the temp folder. The "Save" button merely overwrites the original with the temporary copy.

So you will have to explore third party tools if "never saving the file to disk ever, even to the temp folder" is a requirement.

The "send a file to the client" method will be the same method as any other file. Excel is not special in that regard. An example of a download method: example

Creating Excel File stream using Interop Services for WCF Service

1 Answers1