ASP.NET: Office Automation on Web Server (IIS)

Often in web application, we require to automate office application. One common such requirement is Export To Excel. Here in this article, we will discuss different approaches for automating office application on web server and will try to conclude which is recommended approach with respect to technical & licensing feasibility along with reference link and available open source library from community. However for simplicity we will discuss around Export To Excel functionality as it is most common and required feature in any web application but approaches discussed here and external references given here apply to other office application as well including Word and Power Point.

If we Google it out for Export To Excel in ASP.NET, we will find plenty of solution. Each solution has its own pros and cons. Sometime some of the solutions are not feasible with respect to application requirement or sometime it is not feasible with respect to production deployment scenario. Let start discussion by listing few common approach which I observed for Export To Excel.

Export As CSV

If application requirement is only to export tabular data, then often I have observed that developer are exporting it as CSV rather than getting into generating excel file. However in this approach we don’t get luxury of cell formatting, merging etc.

Grid View Rendered HTML

Another most common scenario, I have seen is using Grid View control, write Grid View rendered HTML into HTML writer and flush that HTML to response stream with appropriate ContentType response header. Here we can have little flexibility over cell formatting but it is very tedious programming task to set proper cell formatting via Grid View. Apart from cell formatting sometime based on requirement we even require more control for e.g. cell merge, column width, word-wrap in case of descriptive content in particular cell, etc and lot more which is not possible or feasible with Grid View control.

Grid View & ASP.NET MVC Razor View

Again if application is built with ASP.NET MVC & Razor View and when we are bound to use Razor view only at that time we will not find Grid View control handy. Here what we can do is we can generate HTML table and flush it into response stream with appropriate ContentType response header.

Office Interop Library

In some of the reference, I found that some developers are using Office Interop Library on web server. Office Interop Library gives full control over office application but It is strongly recommended to NOT USE Office Interop Library on web server or ASP.NET application. It requires office to be installed on web server and other various settings which is not recommended on web server and sometime even denied on production server by server admin. These settings include additional permission, setting interactive desktop user profile because most of the time production server will be logged off. Sometime it even requires setting interactive user or user identity under Component Service as displayed in below image.

Full list of Problems using server-side Automation of Office is given here in this link http://support.microsoft.com/kb/257757. Apart from technical issue there can be issue with licensing also. As mentioned on above link-

Besides the technical problems, we must also consider licensing issues. Current licensing guidelines prevent Office applications from being used on a server to service client requests, unless those clients themselves have licensed copies of Office. Using server-side Automation to provide Office functionality to unlicensed workstations is not covered by the End User License Agreement (EULA).

P.S. above reference is quoted from http://support.microsoft.com/kb/257757 as on while I published this post. i.e. 06-Aug-13.

Conclusion is we SHOULD NOT USE Office Interop Library for office automation on web server & ASP.NET application.

Open XML SDK (Recommended Approach)

Ahhh… after long discussion we finally stepped at recommended approach, but I believe this long discussion was required to understand as requirement evolve (from simple data extract in CSV to formatted well aligned excel file) how we knowingly or unknowingly fall into situation which can lead to technical as well licensing issues over the time. Covering Open XML SDK tutorial is beyond the scope of this post. But let me quickly discuss few things with reference article from MSDN and Microsoft Knowledge Base.

If we look into above beginner level link, we will find using Open XML SDK means dealing with package and XML document manipulation. Apart from here also http://support.microsoft.com/kb/257757 we can find few links which show how to use Open XML SDK with Excel, Word & Power Point.

Open Source Library

Apart from above MSDN tutorials, I came across few open source library on codeplex one of the best I found is https://closedxml.codeplex.com/ which is wrapper over Open XML SDK. A benefit with this library is that you do not need to deal with raw package and XML document. Here on documentation page on codeplex we can find step by step tutorial for auto sum, auto filter, pivot table, etc. Apart from above wrapper over Open XML SDK I found few other projects on codeplex which is as below but I have not tested its functionality thoroughly.

Hope this post would be helpful! You can follow me on twitter for latest link and update on ASP.NET & MVC.