.Net, ASP.NET, DataTable, Excel, Excel 2003, Excel 2007, Excel 2010, MS Office, Programming, Software, VB.NET, Web Development, WebForms

Using ClosedXML.Excel with ASP.NET to Generate Excel Documents on the Fly

I’ve been playing around with the ClosedXML.Excel library for creating or reading MS Excel documents in your ASP.NET code. Bottom line is that this tool is going to save you a lot of time:
  1. In simplifying your task of putting the Workbook and Worksheets together
  2. and I can’t stress enough how much time this will save you from trying to re-jig your Web server to serve Excel documents using the Microsoft.Office.Interop.Excel library.

First Some Historical Information About Exporting Excel Files

The main benefit of the the ClosedXML.Excel library, and libraries like it is that you are no longer reliant on the Microsoft Office libraries to generate your Excel documents. This may not seem like a big deal on your local development machine, but the Microsoft default libraries can easily turn into a roadblock when you are deploying your code to a production Web server that doesn’t have Office pre-installed. Or even if your Web server does have Office, you won’t have to mess with DCOM and server user security settings to try to allow IIS to serve up Excel documents.

Examples of the Problems with using Microsoft.Office.Interop.Excel

For an example of the extent of server configuration steps that have to happen if you are trying to use the Microsoft.Office.Interop.Excel library, have a look at this article:

Steps for Adding the ClosedXML.Excel library to your local project

For starters you are going to want the library on your local machine so you can develop with it. Adding it is reasonably easy using NuGet.
In Visual Studio 2015:
  1. Click on the Tools Option on the top menu bar
  2. Expand the option NuGet Package Manager
  3. Click Package Manager Console
  4. You will see the Console open at the bottom of the main Window of Visual Studio
  5. At the PM> command line, type:
  6. Install-Package ClosedXML
  7. The ClosedXML and DocumentFormat.OpenXML DLLs should now be installed on your system and should also be available in the Bin folder of your project. For quick portability the DLLs in your project’s Bin folder are your go-to option.
  8. You should now be able to import and code your page using the ClosedXML library. When you are done you’ll want to make the ClosedXML library available on your production Web server as well.

Simple way to make the ClosedXML library available on your production Web server

What was once a difficult/almost impossible task with the Microsoft Office libraries is now a breeze.
Basically, it’s as simple as moving the ClosedXML and OpenXml files from your development environment’s Bin folder to the Bin folder of the site on your production Web server. To be specific, move the following files:
  • ClosedXML.dll
  • ClosedCML.dll.refresh
  • ClosedXML.xml
  • DocumentFormat.OpenXml.dll
  • DocumentFormat.OpenXml.dll.refresh

Super-Basic WebForm Example of using the ClosedXML Library (in Visual Basic)

Now just to give you an idea of what an ASP.NET WebForm will look like when it is using the ClosedXML Library, I’m including below a very basic example. The Excel export triggers on the page load event and will show a single Workbook and Worksheet containing a line of text.

Note that you could enhance this example by just putting an ASP.NET dataTable into a worksheet using the command:

workbook.Worksheets.Add(tmpTbl)
<%@ Page Language="VB" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="ClosedXML.Excel" %>
<!DOCTYPE html>
<script runat="server">
Protected Sub Page_Load(sender As Object, e As System.EventArgs)
Dim workbook As XLWorkbook = New XLWorkbook()
workbook.Worksheets.Add("TEST WORKSHEET").Cell(1, 1).SetValue("THIS IS A TEST")
Dim httpResponse = Response
httpResponse.Clear()
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
httpResponse.AddHeader("content-disposition", "attachment;filename=""TestDocument.xlsx""")
Using tmpMemoryStream As MemoryStream = New MemoryStream()
workbook.SaveAs(tmpMemoryStream)
tmpMemoryStream.WriteTo(httpResponse.OutputStream)
tmpMemoryStream.Close()
End Using
httpResponse.End()
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>



<form id="form1" runat="server">



<div></div>



</form>



</body>
</html>
Advertisements

3 thoughts on “Using ClosedXML.Excel with ASP.NET to Generate Excel Documents on the Fly”

  1. I’ve got a problem using hyperlink with relative file path. It is not working for the file from same folder. E.g. If I set hyperlink as @”Images/sample.jpg”, I saw the link was generated as “file:///C:\users\myAccount\Downloads\ExcelExport.xlsx – ‘SampleSheet’!Images/sample.jpg”. Is this ClosedXML bug or any work around?

  2. After having struggled with the Office interop for … well, two hours (I won’t say “days” or “weeks”), the ClosedXML library is much easier to work with.

    However, one adjustment I found worked better from my end than using MemoryStream was to save the file first, and then write the file to the HTTP response. Something like this.

    Dim File_Path As String = Server.MapPath("~/App_Data/") & Filename
    If File.Exists(File_Path) Then File.Delete(File_Path) ' I only have this in here because I'm using the app_data folder for a small group of users and the likelihood of two of them overwriting any file is non-existent.
    oBook.SaveAs(File_Path)
    oBook.Dispose()
    Response.AddHeader("Content-Disposition", "attachment; filename=" & Filename)
    Response.WriteFile(File_Path)
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    Response.End()

    Minor adjustment, though. Overall this is a major timesaver and much better to work with than the Office interop. Thanks for this, Justin.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s