OpenXML: Alternative that helps avoid installing MS Excel on Server

I was working on creating a tool that can help automate importing and exporting content copies from Excel. The solution worked well on my local workstation, but failed to address the need when deployed on Server. Instead, I was getting an exception relating to Microsoft.Office.Interop.Excel assembly.

So, the reason is implied that it was working on my local IIS since I have MS Office installed but we can’t have such privileges on Server.

As an alternate solution to this issue, I opted for OpenXML SDK. The Open XML SDK 2.0 provided by Microsoft simplifies the task of manipulating Open XML packages and the underlying Open XML schema elements within a package. The Open XML Application Programming Interface (API) encapsulates many common tasks that developers perform on Open XML packages.

In order to use the classes, you must add as a reference the DocumentFormat.OpenXml library from Open XML SDK 2.0, and WindowsBase in your project; adding in Visual Studio is straightforward. Go to your Solution Explorer > right click on references and then click Manage NuGet Packages. Then search in tab “Online” for DocumentFormat.OpenXml and install it. You would also need to Add Reference to “WindowsBase” library.

openxml-nuget

Following namespaces should be referenced from your code now:

Create four service classes in your project and add the below code in respective class files:

  1. SLExcelData
  2. SLExcelStatus
  3. SLExcelReader
  4. SLExcelWriter

Once you are able to include and compile your project with above files, the final step is to consume them to read/write the data. Below are the code snippet to help you on this: