Using a template to create an Excel spreadsheet

Sample 3 shows how to create a new Excel spreadsheet based on an existing file and populate it with data from a database. This is a much better approach as you can quickly create a spreadsheet with the right formula and the correct corporate look and feel using Excel 2007. You can then have the calculations in your template validated by the business before starting to write any code. This whole approach saves a lot of coding time!

Before running the code sample, open the template and take a look at its content. You will see it already has the desired layout and all the formula and formatting required for the title and total lines. However, it only has room for three data rows (i.e. rows 5, 6 & 7). You will see how we cope with this later.

Sample 3 Template
So let’s start by creating a new Excel spreadsheet based on a template.

using OfficeOpenXml;  // namespace for the ExcelPackage assembly
…
FileInfo newFile = new FileInfo(@"C:\sample3.xlsx");
FileInfo template = new FileInfo(@"C:\sample3template.xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(newFile, template)) {…}


Behind the scenes, the ExcelPackage constructor simply copies the template and opens the new package. Now obtain a reference to the existing worksheet and initialize some variables:

ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets["Sales"];
ExcelCell cell;
const int startRow = 5;
int row = startRow;


Next open a connection to the database and run the query. This example uses data from the AdventureWorks sample database, so you will need this installed if you want to run the sample.

while (sqlReader.Read())
{
  int col = 1;
  // we have our total formula on row 7, so push them down so we can insert more data
  if (row > startRow) worksheet.InsertRow(row);

  // our query has the columns in the right order, so simply
  // iterate through the columns
  for (int i = 0; i < sqlReader.FieldCount; i++)
  {
    // use the email address as a hyperlink for column 1
    if (sqlReader.GetName(i) == "EmailAddress")
    {
	// insert the email address as a hyperlink for the name
	string hyperlink = "mailto:" + sqlReader.GetValue(i).ToString();
	worksheet.Cell(row, 1).Hyperlink = new Uri(hyperlink, UriKind.Absolute);
    }
    else
    {
	// do not bother filling cell with blank data 
       // (also useful if we have a formula in a cell)
       if (sqlReader.GetValue(i) != null)
	  worksheet.Cell(row, col).Value = sqlReader.GetValue(i).ToString();
	col++;
    }
  }
  row++;
}


So now we have filled our worksheet with the entire dataset. Note how we use the email address as a hyperlink. Using hyperlinks is useful when you want to link one report up with another.

The purist among you will notice that all the data is written into the cell as a string. However, the Cell(row, col).Value = “xxx” property assignment code checks if the value is a number or a string and sets the cell’s data type accordingly.

As mentioned earlier, the template only has room for three data rows. We cope with this by simply inserting rows into the template – thereby pushing the Total row down the sheet. The InsertRow method automatically updates the formula in the Total row so that they take into account the extra rows.
As we have inserted a whole set of rows into the spreadsheet, they will not have the correct style. We correct this by simply iterating through the new rows and copying the style from the first row to all the other rows.

// First copy the styles from startRow to the new rows.     
for (int iCol = 1; iCol <= 7; iCol++)
{
  cell = worksheet.Cell(startRow, iCol);
  for (int iRow = startRow; iRow <= row; iRow++)
  {
    worksheet.Cell(iRow, iCol).StyleID = cell.StyleID;
  }
}

The Power of Named Styles

Anyone familiar with styling HTML with CSS will understand the power and flexibility of using named styles rather than updating the style of every individual element. With named styles, the look and feel of the whole spreadsheet can be altered by changing one style definition. This capability was introduced in Excel 2003, but Excel 2007 goes one step further and makes it a dream to create a template using named styles.

We apply two built-in named styles to highlight the top achieving and the worst performing sales reps.

// style the first row as they are the top achiever
worksheet.Cell(startRow, 6).Style = "Good";
// style the last row as they are the worst performer
worksheet.Cell(row, 6).Style = "Bad";


The biggest problem with named styles in Excel 2007 is that if they are not used in your template, then Excel strips out the definition when the file is saved. This is a real headache. There are two ways to cope with this (1) add extra rows that have styles applied and delete them later (which is the technique used in this sample) or (2) load your own style.xml file which contains all the definitions you want to use.

The final output of Sample 3 code should look something like this – much more professional than anything that can be achieved starting from scratch.

Sample 3 Output

Last edited Jan 4, 2007 at 8:00 PM by JohnTunnicliffe, version 7

Comments

pk_davidson Jan 23, 2013 at 4:46 PM 
Looks like this project has been subsumed by the EPPlus project:
http://epplus.codeplex.com/

phreaks Oct 14, 2010 at 3:55 PM 
Is this project dead?

Mo9a7i Apr 19, 2010 at 11:56 AM 
theres something here too,
when i use the insertRow function, let's say insertRow(3)
so rows 4 5 6 are moved down one row
the formulas are moved too
but the styles aren't, if you have formated some cells as tables in the template, and tried to add rows to these tables, the header rows of the following tables will be shifted 1 row down leaving the header rows empty

is there a work around for this one ?

maverick1109 Dec 17, 2009 at 9:12 PM 
can we assign cell background colour font from the code?instead of using it with the template?

maverick1109 Dec 16, 2009 at 8:30 PM 
on clicking the hyperlink how do i link to an other worksheet?i have given the worksheetname but it says invalid uri : the format of the uri could not be determined.Please help me....

lnmthuc Dec 5, 2008 at 12:23 AM 
I'm not sure if it is bug of the library:
When I set a value of cell having single quote or double quote to a cell, there's an exception of XPath.
How do I solve this problem?

gregchak Oct 3, 2007 at 5:51 PM 
Didn't see your comments until after the fact. Was stuck on NullReferenceException Object reference not set to an instance of an object when trying to save. Anyone know why you have to update every sheet in the workbook?

andrewjones Feb 5, 2007 at 9:33 PM 
Watch out for this bug in V1.0.0.1 : If you are using a template with multiple tabs you must ensure that you update at least one cell on each tab otherwise the Save() function throws an exception.