c# - Exporting data from externally hosted app and DB

Question

I'm building a web app in C# which needs a facility to export data. I had originally exported to Excel when testing locally and this worked fine, but as the app is hosted on an external server I believe this won't work due to Microsoft.Office.Interop.Excel, is this correct?

Answer 1

You're right in that Microsoft discourages Excel automation in web applications:

https://support.microsoft.com/en-us/kb/257757?wa=wsignin1.0

You still have some options, though. If all you need to do is export data, you can still write a file in CSV format for your export, and it should open in Excel. If you really need to write in Excel formats, you can use the Open XML SDK for Office (https://msdn.microsoft.com/en-us/library/office/bb448854.aspx?f=255&MSPPError=-2147217396).

One other option is to save the data and have the server run Excel automation in a batch process outside of IIS and send the report to the user.

Answer 2

To use Microsoft.Office.Interop.Excel you do need to have Office installed AFAIK.

You could export your data to Excel in a few methods:

1. Create a regular old CSV file. Excel can open and edit such files. It's probably a good idea to make data available this way anyway, simply because it's a portable and open format.
2. Use SpreadsheetML to create an Excel document. You won't have all the features of Excel available this way, but you will be able to add more formatting information to the document, and it will be easily opened by Excel. I believe other spreadsheet software supports this as well.