Interesting discussion at StackOverflow:
Create Excel (.XLS and .XLSX) file from C#

If you want to see how to combine NPOI + Excel Table and Chart,
take a look at the post titled NPOI with Excel Table and dynamic Chart.

NPOI 2.0 series of posts scheduled

Recently I had to implement some code to create an Excel spreadsheet/report using C#.

The task was: given an Excel spreadsheet template - a .XLS file (with formulas, pivot tables, macros, etc) I had to fill some data in one of the sheets of the spreadsheet and send this modified spreadsheet back to the user requesting such an operation (Excel report).

The following attests the need for Excel nowadays:

Excel has long been recognized as the de facto standard when it comes to presenting management reports. The unique combination of great calculation engine, excellent charting facilities, pivot tables and the possibility to perform “what if” analysis, make it the “must have” business intelligence tool.
by John Tunnicliffe

I had a great time while studying the possible ways of doing what the task asks for.

It appears to be a simple task at first but as the time passes by you get to know that this is not the case, well, till the moment this blog post was written at least, I think. :-)

Firstly I tried automating Excel through COM automation, but as the application was meant to be used in a web context it is not recommended to use automation. Why? Because COM automation for Excel is not thread safe, that is, EXCEL.EXE was not constructed to be used by concurrent users accessing the same process, in this case EXCEL.EXE; besides, Microsoft Excel must be installed on the server what is not always possible.

For more details on why you shouldn’t use Excel on the server, read this article on Microsoft Help and Support site: Considerations for server-side Automation of Office. The key part is this:

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

I’ve just experienced the above. EXCEL.EXE insisted in being alive in task manager even after processing the data and being closed in code. Each call to process a spreadsheet opens an EXCEL.EXE process on the server. With such EXCEL.EXE processes don’t being closed as they should you get lots of those processes on memory which could overload the server.

Do not use COM automation if you are developing server-side code.

After struggling with COM automation I finally got to know ExcelPackage  which works with Office Open Document Format (OOXML). It can read an .XLSX (Microsoft Excel 2007) template and create another .XLSX file based on such template, giving you lots of possibilities to work with the template copy.

I’ve gotten really happy because I had found a way of doing what the task was asking for but with a minor detail: ExcelPackage works only with .XLSX file format letting the .XLS (Microsoft Excel 2003) format out of the game. Well it turned out to be a big impediment because the client (software buyer) wouldn’t allow us to install the famous Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats on user machines so that users could open and save OOXML file formats even using Microsoft Office 2003 suite.

Discovering ExcelPackage was good but it didn’t do the trick that implies the use of an .XLS template.

I got back Googling again and strived to find an open source library that would allow me to do what I wanted. After some time I finally discovered NPOI.  Wow, it could read the .XLS template and generate the end result I wanted. Great. I downloaded NPOI 1.2.1 for .NET 2.0 binaries immediately and started playing with it to see what it could really do.

OK, after this short story, I’ll show you how to use both open source projects (ExcelPackage and NPOI).

I’ve created a new ASP.NET MVC project as can be seen in this picture:

 

In the Content folder I’ve placed the template spreadsheets.
In the Libs folder I’ve placed the DLLs necessary to use both ExcelPackage and NPOI open source projects.

The controller that interests us is the one called ExcelWriterController:

The methods that handle the creation of the spreadsheet are: ExcelPackageCreate and NPOICreate.

For each controller action (method) there’s a corresponding view that renders the UI to the user. Those views are the ones shown inside the ExcelWriter folder: ExcelPackage.aspx and NPOI.aspx.

This is the Home Page of the Excel Writer MVC Application - take a look at the tabs (ExcelPackage and NPOI) that lead you to the View pages:

Each view has a button which when clicked calls the corresponding action method on the ExcelWriterController.

This is the NPOI view page:

I’ll play with a simple spreadsheet I filled with the data I got from Excel’s blog post titled Formula to Access a List of Values Interspersed with Zeros or Blanks.

Let’s see the code that goes into the ExcelPackageCreate method:

/// <summary>
/// Creates a new Excel spreadsheet based on a template using the ExcelPackage library.
/// A new file is created on the server based on a template.
/// </summary>
/// <returns>Excel report</returns>
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult ExcelPackageCreate()
{
    try
    {
        FileInfo template = new FileInfo(Server.MapPath(@"\Content\ExcelPackageTemplate.xlsx"));

        FileInfo newFile = new FileInfo(Server.MapPath(@"\Content\ExcelPackageNewFile.xlsx"));

        // Using the template to create the newFile...
        using(ExcelPackage excelPackage = new ExcelPackage(newFile, template))
        {
            // Getting the complete workbook...
            ExcelWorkbook myWorkbook = excelPackage.Workbook;

            // Getting the worksheet by its name...
            ExcelWorksheet myWorksheet = myWorkbook.Worksheets["Sheet1"];

            // Setting the value 77 at row 5 column 1...
            myWorksheet.Cell(5, 1).Value = 77.ToString();

            // Saving the change...
            excelPackage.Save();
        }

        TempData["Message"] = "Excel report created successfully!";

        return RedirectToAction("ExcelPackage");
    }
    catch(Exception ex)
    {
        TempData["Message"] = "Oops! Something went wrong.";

        return RedirectToAction("ExcelPackage");
    }
}

Let’s see the code that goes into the NPOICreate method:

/// <summary>
/// Creates a new Excel spreadsheet based on a template using the NPOI library.
/// The template is changed in memory and a copy of it is sent to
/// the user computer through a file stream.
/// </summary>
/// <returns>Excel report</returns>
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult NPOICreate()
{
    try
    {
        // Opening the Excel template...
        FileStream fs =
            new FileStream(Server.MapPath(@"\Content\NPOITemplate.xls"), FileMode.Open, FileAccess.Read);

        // Getting the complete workbook...
        HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);

        // Getting the worksheet by its name...
        HSSFSheet sheet = templateWorkbook.GetSheet("Sheet1");

        // Getting the row... 0 is the first row.
        HSSFRow dataRow = sheet.GetRow(4);

        // Setting the value 77 at row 5 column 1
        dataRow.GetCell(0).SetCellValue(77);

        // Forcing formula recalculation...
        sheet.ForceFormulaRecalculation = true;

        MemoryStream ms = new MemoryStream();

        // Writing the workbook content to the FileStream...
        templateWorkbook.Write(ms);

        TempData["Message"] = "Excel report created successfully!";

        // Sending the server processed data back to the user computer...
        return File(ms.ToArray(), "application/vnd.ms-excel", "NPOINewFile.xls");
    }
    catch(Exception ex)
    {
        TempData["Message"] = "Oops! Something went wrong.";

        return RedirectToAction("NPOI");
    }
}

One drawback of the ExcelPackage library is that it must create a file on the server. There are some modifications to the library that enables you to create the template copy on memory and send it to the user as the NPOI library does. Take a look at the ExcelPackage’s discussion page at CodePlex and specifically this thread: Why create Excel spreadsheets on the server?

The great thing about NPOI of course is that it enables you to work with the template in code and then send a copy of the spreadsheet directly to the user. The template remains intact and the user receives a modified copy of the template which contains the data processed by the application.

With NPOI when you click on the Create Excel report button you get the download dialog window:

With ExcelPackage you’d have to get the path of the file created on the server, in this case \Content\ExcelPackageNewFile.xlsx and then send that file to the user. This is an extra step and adds an additional burden to the server. I didn’t implement it and so I let this as an exercise to you.

Well, the spreadsheet included in this simple project has only formulas but you can for sure have an Excel template with lots of formulas, pivot tables, macros, etc. This gives you the power of Excel in code in a clean fashion.

Hope this helps shed some light on this topic!

Note
Using the open source libraries presented in this post you won’t need Microsoft Excel installed on the server.

Updated on 9/22/2010

There’s now EPPlus that extends ExcelPackage.

EPPlus is a .net library that reads and writes Excel 2007 files using the Open Office XML format (XLSX).

EPPlus supports ranges, cell styling, charts, pictures, shapes, named ranges, autofilters and a lot of other stuff.

Updated on 6/2/2010

I’m working on an ASP.NET project that uses .NET Framework 1.1. In such a case I needed to use NPOI 1.2.1 for .NET 1.1.

The code you’ll use with NPOI 1.2.1 for .NET 1.1 is the same presented on this post. Just pay attention to include this using directive inside your C# code:

using NPOI.HSSF.UserModel

Updated on 1/3/2010

If you like NPOI, help spread the word about it voting up on this ad:

Visual Studio 2008 C# ASP.NET MVC Web Application
You can get the Microsoft Visual Studio Project at:

http://sites.google.com/site/leniel/blog/ExcelWriterMvcProject.zip

To try out the code you can use the free Microsoft Visual Web Developer 2008 Express Edition that you can get at: http://www.microsoft.com/express/vwd/Default.aspx

References
ExcelPackage: Office Open XML Format file creation
http://excelpackage.codeplex.com/

ExcelPackage binaries download
http://excelpackage.codeplex.com/Release/ProjectReleases.aspx

NPOI
http://npoi.codeplex.com/

NPOI 1.2.1 for .NET 1.1 
http://npoi.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=33203

NPOI 1.2.1 for .NET 2.0
http://npoi.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=19351

NPOI samples
http://npoi.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=19351#DownloadId=70100

Hello World
A "hello world" application prints out "Hello, world!" on the screen. It is used in many introductory tutorials for teaching a programming language or upcoming technology. Such a program is typically one of the simplest programs possible in a such language or specific technology.

ASP.NET
ASP.NET is a web application framework developed and marketed by Microsoft, that developers can use to build dynamic web sites, web applications and web services. It was first released in January 2002 with version 1.0 of the .NET Framework, and is the successor to Microsoft's Active Server Pages (ASP) technology. ASP.NET is built on the Common Language Runtime, allowing programmers to write ASP.NET code using any supported .NET language.

MVC
Model-View-Controller (MVC) is an architectural pattern used in software engineering. Successful use of the pattern isolates business logic from user interface considerations, resulting in an application where it is easier to modify either the visual appearance of the application or the underlying business rules without affecting the other. In MVC, the Model represents the information (the data) of the application and the business rules used to manipulate the data, the View corresponds to elements of the user interface such as text, checkbox items, and so forth, and the Controller manages details involving the communication to the Model of user actions such as keystrokes and mouse movements.

In detail what happens in the Model-View-Controller pattern is:

• Browser requests URL
• Route is determined
• Controller is activated
• Method on Controller is invoked
• Controller does some stuff
• Controller renders the View, passing in ViewData

Hello World Web Site
This Web Site basically displays a "Hello, [Name]" message. The variable "Name" receives the data that is passed in the URL to the HelloWorldController action method. The action method will store the URL data in the Controller's ViewData object. The ViewData object will then be used when the View is rendered. It'll be more clear after you implement the code.

Note: I'm not using database interaction (database Model), so the Model part of the MVC pattern isn't created.

I'll show you the steps I used to get an ASP.NET MVC Web Site running with Microsoft Visual Web Developer 2008 Express Edition.

It's important to note that the current version of ASP.NET MVC is the ASP.NET MVC Preview 2. This version wasn't planned to be used with Visual Web Developer Express, so it's necessary to use an adapted project template to get it going properly.

Stuff to download
In order to get the necessary software parts you should install the following if you still haven't them.

1. Microsoft Visual Web Developer 2008 Express Edition
2. ASP.NET MVC Preview 2
3. ASP.NET 3.5 Extensions Preview
4. ASP.NET MVC Project template

It's mandatory that you install all the above software to avoid erros when debugging the web site. I've run into errors just because I hadn't the ASP.NET 3.5 Extensions Preview, so do install everything.

Implementing the Hello World Web Site
Open Visual Web Developer and go to menu File - New Web Site. On the New Web Site dialog window, select your language of preference according to the ASP.NET MVC Project Template you selected above. This option can be selected on the combo box related to Language. If you don't change the language you won't see the project template. Give the Web Site the name HelloWorldMvcWebSite.

The project structure is different from the one of a Web Application that is only available on paid versions of Visual Studio Web Developer that is included in Microsoft Visual Studio Standard and Microsoft Visual Professional.

The following is the the structure you get when a new ASP.NET MVC Web Site is created:

Creating a new Controller
Let's create a new Controller called HelloWorld. To accomplish this, right-click on the file HomeController.cs and select Copy. Right-click on the folder Controls and select Paste. You'll have a new file called Copy of HomeController.cs. Right-click on this file and rename it to HelloWorldController.cs. Open this file and change its content so that it looks like the following:

public class HelloWorldController : Controller
{
  [ControllerAction]
  public void HiThere()
  {
    RenderView("HelloWorld");
  }
}

Creating a new View
Let's create a new View that will render the data. To accomplish this, right-click on the folder Views and select New Folder. Give the new folder the name HelloWorld. Now right-click on the file Index.aspx and select Copy. Right-click on the folder HelloWorld and select Paste. You'll have a new file called Index.aspx inside the HelloWorld folder. Right-click on this file and rename it to HelloWorld.aspx. Open this file and change its content so that it looks like the following:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="HelloWorld.aspx.cs" Inherits="views_Home_Index" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Hello World ASP.NET MVC Application</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
         <h1>Hello, <%= ViewData["Name"] %></h1>
    </div>
    </form>
</body>
</html>

Configuring the routes on the Global.asax file
The routes map the URL to the proper action method defined within the Controller.

An action method called HiThere was created inside the HelloWorldController. This method is responsible for invoking the RenderView method that then will render the View (HelloWorld.aspx).

To the above described take effect it's necessary that the proper routing (mapping rules from URLs to action methods) be configured when the application starts.

Open the file Global.asax and change its content so that it looks like the following:

<%@ Application Language="C#" %>
<%@ Import Namespace="System.Web.Mvc" %>

<script RunAt="server">

  void Application_Start(object sender, EventArgs e)
  {
    // Code that runs on application startup
    RouteTable.Routes.Add(new Route
    {
      Url = "[controller]/[action]/[id]",

      Defaults = new { action = "HiThere", id = (string)null },

      RouteHandler = typeof(MvcRouteHandler)
    });

    RouteTable.Routes.Add(new Route
    {
      Url = "Default.aspx",

      Defaults = new { controller = "Home", action = "Index", id = (string)null },

      RouteHandler = typeof(MvcRouteHandler)
    });
  }

  void Application_End(object sender, EventArgs e)
  {
    //  Code that runs on application shutdown
  }

  void Application_Error(object sender, EventArgs e)
  {
    // Code that runs when an unhandled error occurs
  }

  void Session_Start(object sender, EventArgs e)
  {
    // Code that runs when a new session is started
  }

  void Session_End(object sender, EventArgs e)
  {
    // Code that runs when a session ends.

    // Note: The Session_End event is raised only when the sessionstate mode

    // is set to InProc in the Web.config file. If session mode is set to StateServer

    // or SQLServer, the event is not raised.
  }

</script>

The structure of the Web Site must be like the following in the end:

Starting the debugger
Now hit F5. The Web Site will appear in a new web browser window with the following URL: http://localhost:1717/HelloWorldMvcWebSite/

A message of Welcome will be displayed since the page Index.aspx is the Start Page.

Type the following address: http://localhost:1717/HelloWorldMvcWebSite/HelloWorld/

A "Hello,", message is shown. The HelloWorld Controller is being called according to the route table defined above. The default method is HiThere with its id parameter set to null. That's why the View (HelloWorld.aspx) is showing a "Hello, " message. Since an id isn't being passed the only message shown is "Hello, ".

Typing the following address: http://localhost:1717/HelloWorldMvcWebSite/HelloWorld/HiThere/Leniel

A "Hello, Leniel" message is shown. This time the URL conforms with the route that was defined inside the Global.asax file, that is:

Url = "[controller]/[action]/[id]"

These are the assignments done when the routing system detects a URL like the one above:

controller = HelloWord action = HiThere id = Leniel

Final notes
There is no doubt that the ASP.NET MVC Framework turns the life more clean and simple.

It's always good to work in an organized environment. Separating the code related to data base interaction (Model), business logic (Controller) and Presentation/UI (View) is perfect.

Not so long ago, the programming environment was a mess wit lots of event handlers mixed with data base interactions and UI code. It was really difficult to manage all that mess. But thanks God things are getting better as it should.

Another great advantage is how the URL routing is done. It translates/conforms well to the naming scheme adopted in a given project. No more unreadable URLs that are difficult to deal with. It lends to a better searchable web site. The website will play friendly with web search crawlers.

References
To get a handful of examples I advise you to check ScottGu's blog. http://weblogs.asp.net/scottgu/archive/tags/MVC/default.aspx http://weblogs.asp.net/scottgu/archive/2007/11/13/asp-net-mvc-framework-part-1.aspx

ASP.NET MVC : The Official Microsoft ASP.NET Site
http://www.asp.net/mvc/

ASP.NET MVC - Building Web Apps without Web Forms
http://msdn.microsoft.com/en-us/magazine/cc337884.aspx

you've been HAACKED
http://haacked.com/

Visual Web Developer Web Site Project
You can get the Hello World MVC Web Site project at: http://leniel.googlepages.com/HelloWorldMvcWebSite.zip