Zach Hunter's Busy Life

So I’ve been using NPOI all week and decide to do a quick “demo” for my team today. My demo was to show how to use NPOI to populate (update) an Excel template you’ve created.  Microsoft has a bunch of free “pretty” templates on their website, so I went and choose one with nice colors, formulas and a few charts.  It took me about an hour to build the complete demo. It’s actually a very simple process and only took a few lines of code to update your template.

Development Summary ( Step-by-Step )

1. Get a template, I grabbed mine from here ( office.microsoft.com ).
   ** I only used the first sheet and deleted all the sample data
2. Create a new ASP.NET 3.5 Web Application projecct
3. Download NPOI binaries and include in your project
4. Build a UI that will be used to populate your template.
   ** This could also be populated by a data sources (db, XML, etc..)
   ** NOTE:  I used Excel to create the form using Excel formulas
5. Add some c# code “magic” to load data into the template using NPOI

Sounds simple because it is… Here is the code to add the form contents into the Excel template.

            // Open Template
            FileStream fs = new FileStream(Server.MapPath(@"\template\Template_EventBudget.xls"), FileMode.Open, FileAccess.Read);

            // Load the template into a NPOI workbook
            HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);

            // Load the sheet you are going to use as a template into NPOI
            HSSFSheet sheet = templateWorkbook.GetSheet("Event Budget");

            // Insert data into template
            sheet.GetRow(1).GetCell(1).SetCellValue(EventName.Value);  // Inserting a string value into Excel
            sheet.GetRow(1).GetCell(5).SetCellValue(DateTime.Parse(EventDate.Value));  // Inserting a date value into Excel

            sheet.GetRow(5).GetCell(2).SetCellValue(Double.Parse(Roomandhallfees.Value));  // Inserting a number value into Excel
            sheet.GetRow(6).GetCell(2).SetCellValue(Double.Parse(Sitestaff.Value));
            sheet.GetRow(7).GetCell(2).SetCellValue(Double.Parse(Equipment.Value));
            sheet.GetRow(8).GetCell(2).SetCellValue(Double.Parse(Tablesandchairs.Value));
            sheet.GetRow(12).GetCell(2).SetCellValue(Double.Parse(Flowers.Value));
            sheet.GetRow(13).GetCell(2).SetCellValue(Double.Parse(Candles.Value));
            sheet.GetRow(14).GetCell(2).SetCellValue(Double.Parse(Lighting.Value));
            sheet.GetRow(15).GetCell(2).SetCellValue(Double.Parse(Balloons.Value));
            sheet.GetRow(16).GetCell(2).SetCellValue(Double.Parse(Papersupplies.Value));
            sheet.GetRow(20).GetCell(2).SetCellValue(Double.Parse(Graphicswork.Value));
            sheet.GetRow(21).GetCell(2).SetCellValue(Double.Parse(Photocopying_Printing.Value));
            sheet.GetRow(22).GetCell(2).SetCellValue(Double.Parse(Postage.Value));
            sheet.GetRow(26).GetCell(2).SetCellValue(Double.Parse(Telephone.Value));
            sheet.GetRow(27).GetCell(2).SetCellValue(Double.Parse(Transportation.Value));
            sheet.GetRow(28).GetCell(2).SetCellValue(Double.Parse(Stationerysupplies.Value));
            sheet.GetRow(29).GetCell(2).SetCellValue(Double.Parse(Faxservices.Value));
            sheet.GetRow(33).GetCell(2).SetCellValue(Double.Parse(Food.Value));
            sheet.GetRow(34).GetCell(2).SetCellValue(Double.Parse(Drinks.Value));
            sheet.GetRow(35).GetCell(2).SetCellValue(Double.Parse(Linens.Value));
            sheet.GetRow(36).GetCell(2).SetCellValue(Double.Parse(Staffandgratuities.Value));
            sheet.GetRow(40).GetCell(2).SetCellValue(Double.Parse(Performers.Value));
            sheet.GetRow(41).GetCell(2).SetCellValue(Double.Parse(Speakers.Value));
            sheet.GetRow(42).GetCell(2).SetCellValue(Double.Parse(Travel.Value));
            sheet.GetRow(43).GetCell(2).SetCellValue(Double.Parse(Hotel.Value));
            sheet.GetRow(44).GetCell(2).SetCellValue(Double.Parse(Other.Value));
            sheet.GetRow(48).GetCell(2).SetCellValue(Double.Parse(Ribbons_Plaques_Trophies.Value));
            sheet.GetRow(49).GetCell(2).SetCellValue(Double.Parse(Gifts.Value));

            // Force formulas to update with new data we added
            sheet.ForceFormulaRecalculation = true;

            // Save the NPOI workbook into a memory stream to be sent to the browser, could have saved to disk.
            MemoryStream ms = new MemoryStream();
            templateWorkbook.Write(ms);

            // Send the memory stream to the browser
            ExportDataTableToExcel(ms, "EventExpenseReport.xls");

Here are the screen shots of the form and completed template…

Here is demo UI.

Here is a populated template.

Here is a copy of the XLS file created in the program:  NPOI – ASP.NET Form merged with Excel template

Here is a copy of the source code for the demo:  NPOI – Visual Studio 2008 (ASP.NET 3.5) Template Merge Demo

Like all things, there is one catch.  During the demo I found a bug with NPOI opening my XLS file and I had to download the source code and make two changes to get NPOI to open my XLS template (bug states this issue is caused when you save a XLSX file as XLS in Excel 2007). Read this post to see the fix or use the DLLs for NPOI included in my demo project.