- In simplifying your task of putting the Workbook and Worksheets together
- 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
Examples of the Problems with using Microsoft.Office.Interop.Excel
and here is a guide to the server configurations that had to happen:
Steps for Adding the ClosedXML.Excel library to your local project
- Click on the Tools Option on the top menu bar
- Expand the option NuGet Package Manager
- Click Package Manager Console
- You will see the Console open at the bottom of the main Window of Visual Studio
- At the PM> command line, type:
- Install-Package ClosedXML
- 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.
- 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
Super-Basic WebForm Example of using the ClosedXML Library (in Visual Basic)
Note that you could enhance this example by just putting an ASP.NET dataTable into a worksheet using the command:
<%@ 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>