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.

NPOI - ASP.NET Form used to populate a Excel Template

Here is a populated template.

NPOI - ASP.NET Form merged with Excel 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.

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • DotNetKicks
  • Facebook
  • Digg