For the purposes of these examples, all we are going to do is create a document. Populating cells and doing other tasks, while easy enough, would detract from the focus of this post.
The scenario I outline below is rather common. Let’s start with the initial creation of the application on a developer’s box with Microsoft Office installed on it.
using Microsoft.Office.Interop.Excel;
protected void Button1_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application myApp = new Microsoft.Office.Interop.Excel.Application();
myApp.Visible = true;
Workbook myWorkBook = myApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet myWorkSheet = (Worksheet)myWorkBook.Worksheets[1];
}
While this would indeed work on the developer’s machine, once uploaded to the server, an error such as the following would be thrown:
Could not load file or assembly 'Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.
Naturally, they would then assume they needed to upload the Microsoft.Office.Interop.Excel.dll file to the bin directory. However this would lead to another error, similar to:
Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046}
failed due to the following error: 80040154.
If it runs on the developer’s machine, why doesn’t it run on the server?
First, some background. Interoperability is what allows .NET (the CLR specifically) to leverage unmanaged libraries. Essentially it works as a proxy. In this case, we are interested in programmatically accessing the functionality of the Excel COM object.
The application itself isn’t concerned with the COM object, only the interop assembly. This is the reason for the first error. Upon uploading the interop to the bin directory, the application will at least then compile, but attempting to work with the interop will result in the second error. This is because unlike the developer’s station, the COM object isn’t present on the server (Office cannot be installed on the servers – licensing, security, and performance issues prevent this).
Ok so what’s the solution? Well, as it turns out, there is a project called ExcelPackage that will allow us to work with Excel documents without having to worry about Office, Interops, OWC, or anything else! This is due to the use of XML file formats. The only downside being it won’t help you when working with ancient versions of Excel. So let’s get to it.
- First, go get ExcelPackage
- Next unzip it and place the .dll in your applications bin directory
- Create an .xlsx document in Excel and put it in your applications root (for this sample). This is the template we are going to use to create our document from I’ve called mine Book1.xlsx
- Create a reference in Visual Studio for the ExcelPackage.dll
- Build your application and upload!
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
FileInfo myTemplate = new FileInfo(@"e:\web\domain\htdocs\APP\book1.xlsx");
FileInfo myExcelFile = new FileInfo(@"e:\web\domain\htdocs\APP\NewXclFile.xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(myExcelFile, myTemplate));
Response.Redirect(@”~/NewXclFile.xlsx”);
}
}
Michael Ossou