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