Justin Cooney

Web Development Tips and Examples

recent posts

about

I am a Senior Applications Programmer / Analyst with years of experience developing enterprise solutions using the Microsoft technology stack including C#, VB.NET, ASP.NET, AJAX, IIS and SQL Server. I specialize in Web application development with a focus on building secure systems, integrating applications, and designing robust database structures.

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>
Posted in , , , , , , , , , , , ,

4 responses to “Using ClosedXML.Excel with ASP.NET to Generate Excel Documents on the Fly”

  1. tslin Avatar
    tslin

    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?

    Reply
    1. Deza Avatar
      Deza

      myCell.Hyperlink.IsExternal = true;
      myCell.Hyperlink.ExternalAddress = new Uri(myUrl, UriKind.Relative);

      Reply
  2. ClosedXML Nicely Formatted Header Row – Justin Cooney

    […] into setting up and calling functions using .Net and the ClosedXML library, have a look at a previous article I wrote, or visit my site (jwcooney.com) and browse the available […]

    Reply
  3. Adam Senour Avatar
    Adam Senour

    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.

    Reply

Leave a reply to ClosedXML Nicely Formatted Header Row – Justin Cooney Cancel reply