Creating an Excel spreadsheet from scratch

Sample 1 shows how to create a new Excel spreadsheet containing some basic data and calculations. So let’s see how this is achieved.

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


This creates a new instance of the all important ExcelPackage class which gives you access to the Excel workbook and worksheets. If mynewfile.xlsx already exists, then ExcelPackage will open the existing file. Otherwise mynewfile.xlsx will be created from scratch.
Let’s start by adding a new worksheet called Tinned Goods and adding some basic data and a simple calculation:

ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Tinned Goods");
// write some titles into column 1
worksheet.Cell(1, 1).Value = "Product";
…
worksheet.Cell(4, 1).Value = "Peas";
worksheet.Cell(5, 1).Value = "Total";

// write some values into column 2
worksheet.Cell(1, 2).Value = "Tins Sold";

ExcelCell cell = worksheet.Cell(2, 2);
cell.Value = "15"; // tins of Beans sold
string calcStartAddress = cell.CellAddress;  // we want this for the formula
worksheet.Cell(3, 2).Value = "32";  // tins Carrots sold
… 
worksheet.Cell(5, 2).Formula = string.Format("SUM({0}:{1})",
calcStartAddress, calcEndAddress);


If all this seems a bit too easy – well yes it is! The ExcelPackage assembly does all the hard work of creating the XML elements that are needed to represent an Excel worksheet, the Excel rows, the Excel cells etc. All you need to do is connect in the data! The ExcelWorksheet class has all the properties and methods needed to create and manipulate worksheets. A number of supporting classes (such as ExcelCell, ExcelRow, ExcelColumn, ExcelHeaderFooter etc.) provide properties and methods of each worksheet component. They also provide helper functions that make it easy to manipulate Excel data. For example, the ExcelCell.GetCellAddress(iRow, iColumn) method turns your row and column integers into Excel-style cell addresses.

Ok, so in our sample some of the data is too wide for the column, so let’s change the column size:

worksheet.Column(1).Width = 15;

Next, add some headers and footers to the spreadsheet. Note how we use the PageNumber and NumberOfPages constants to insert codes into the footer text. This causes Excel to insert the page number and the number of pages in the document footer.

worksheet.HeaderFooter.oddHeader.CenteredText = "Tinned Goods Sales";
// add the page number to the footer plus the total number of pages
worksheet.HeaderFooter.oddFooter.RightAlignedText =
string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, 
ExcelHeaderFooter.NumberOfPages);


OK, so let’s write some real hard code. Let’s insert a line into the worksheet so we can add some more data. This will screw up our formula as it will be referencing the wrong set of rows (i.e. the new row will not be included in the total).

worksheet.InsertRow(3);


Well hell no, the formula is correct. The InsertRow method not only updates all the row and cell references in the underlying XML, but also updates all the formulas in the spreadsheet!

Ok, we now have our report, but we want to ensure our corporate search engine can find the file later. So let’s add some standard and custom document properties.

xlPackage.Workbook.Properties.Title = "Sample 1";
xlPackage.Workbook.Properties.Author = "John Tunnicliffe";
xlPackage.Workbook.Properties.SetCustomPropertyValue("EmployeeID", "1147");


Now save the file and all its components.

xlPackage.Save();


Below is a screenshot of the final output showing the header and the document properties.

Sample 1 output showing the header and document properties

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