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 9:17 PM by JohnTunnicliffe, version 8

Comments

monkeyman512 Oct 4, 2013 at 7:21 PM 
Your documentation is bad and you should feel bad. The source code and the compiled code are not the same and your example does not work. This page shows how to actually access a cell:
http://stackoverflow.com/questions/15651272/writing-an-excel-file-in-epplus

Ramanjaneya Jul 10, 2013 at 6:09 AM 
Getting an error like Object reference not set to an instance of an object while executing the statement xlPackage.Save()
description : I will have one excel workbook with excel sheets and I am going to add the records to the existing sheets, then I am getting this error.
Can any one please help out.

DGirard Jun 1, 2011 at 5:24 AM 
Delete row, but no delete column method? Please implement this. Please.

lanting Aug 5, 2010 at 8:46 AM 
I export 10,000 rows to a excel file, but it is too slow, it almost needs more than 10 minutes. does it really such slow?

yrameshbabu Aug 24, 2009 at 10:05 AM 
I tried the above code, but i haven't generate the excel sheet, i generated the below format. Can anybody help me on the same?
I am getting the below text in desired excel sheet. I am using VS2.0 and office 2003

PK
Ãs;ŠH==xl/workbook.xml ¢( <workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<bookViews>
<workbookView />
</bookViews>
<sheets>
<sheet name="Tinned Goods" sheetId="1" r:id="R05d8310340ac476c" />
</sheets>
</workbook>PK
Ãs;\ßÅd[Content_Types].xml ¢( <?xml version="1.0" encoding="utf-8"?><Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="xml" ContentType="application/xml" /><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml" /><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml" /><Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" /><Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml" /><Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml" /><Override PartName="/docProps/custom.xml" ContentType="application/vnd.openxmlformats-officedocument.custom-properties+xml" /></Types>PK
Ãs;üGôÈjj _rels/.rels ¢( <?xml version="1.0" encoding="utf-8"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="/xl/workbook.xml" Id="Re42a1129a77f46d2" /><Relationship Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="/docProps/core.xml" Id="R2a0bb58dfb3c4d2b" /><Relationship Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties" Target="/docProps/custom.xml" Id="Rb2197b5f8c624bd0" /></Relationships>PK
Ãs;‡ÿ?  xl/worksheets/sheet1.xml ¢( <?xml version="1.0" encoding="utf-8"?>
<worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<sheetViews>
<sheetView workbookViewId="0" />
</sheetViews>
<cols>
<col min="1" max="1" width="15" />
</cols>
<sheetData>
<row r="1">
<c r="A1" t="s">
<v>0</v>
</c>
<c r="B1" t="s">
<v>3</v>
</c>
</row>
<row r="2">
<c r="B2">
<v>15</v>
</c>
</row>
<row r="3" />
<row r="4">
<c r="B4">
<v>32</v>
</c>
</row>
<row r="5">
<c r="A5" t="s">
<v>1</v>
</c>
</row>
<row r="6">
<c r="A6" t="s">
<v>2</v>
</c>
<c r="B6">
<f>SUM(B2:B2)</f>
</c>
</row>
</sheetData>
<headerFooter>
<oddHeader>&amp;CTinned Goods Sales</oddHeader>
<oddFooter>&amp;RPage &amp;P of &amp;N</oddFooter>
</headerFooter>
</worksheet>PK
Ãs;¬á[¡ËËxl/_rels/workbook.xml.rels ¢( <?xml version="1.0" encoding="utf-8"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="/xl/worksheets/sheet1.xml" Id="R05d8310340ac476c" /><Relationship Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="/xl/sharedStrings.xml" Id="Refa62834c2c042c1" /></Relationships>PK
Ãs;ÿ[¼yúúxl/sharedStrings.xml ¢( <sst count="0" uniqueCount="0" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<si>
<t>Product</t>
</si>
<si>
<t>Peas</t>
</si>
<si>
<t>Total</t>
</si>
<si>
<t>Tins Sold</t>
</si>
</sst>PK
Ãs;£$ᣁdocProps/core.xml ¢( <cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<dc:title>Sample 1</dc:title>
<dc:creator>Ramesh Babu Y</dc:creator>
</cp:coreProperties>PK
Ãs;´ŽX@@docProps/custom.xml ¢( <Properties xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes" xmlns="http://schemas.openxmlformats.org/officeDocument/2006/custom-properties">
<property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="5" name="EmployeeID">
<vt:lpwstr>17</vt:lpwstr>
</property>
</Properties>PK-
Ãs;ŠH==xl/workbook.xmlPK-
Ãs;\ßÅd†[Content_Types].xmlPK-
Ãs;üGôÈjj p_rels/.relsPK-
Ãs;‡ÿ?  xl/worksheets/sheet1.xmlPK-
Ãs;¬á[¡ËË‘ xl/_rels/workbook.xml.relsPK-
Ãs;ÿ[¼yúú°xl/sharedStrings.xmlPK-
Ãs;£$ᣁødocProps/core.xmlPK-
Ãs;´ŽX@@ÄdocProps/custom.xmlPKQ

FilipAdrian Oct 9, 2008 at 4:41 PM 
There is a bug in the code sample.
The variable "calcEndAddress" must be declared as well as "calcStartAddress"

The code should look like this:

string calcStartAddress = cell.CellAddress; // we want this for the formula

cell = worksheet.Cell(3, 2);
cell.Value = "32"; // tins Carrots sold
string calcEndAddress = cell.CellAddress; // we want this for the formula

worksheet.Cell(5, 2).Formula = string.Format("SUM({0}:{1})", calcStartAddress, calcEndAddress);

marlin7 Dec 7, 2007 at 10:18 PM 
I was able to fix this by turning of User Accounts/UAC on my Vista box. Thank you John for also suggesting that the file needs to be closed for any operation with it and that .net 3.0 must be installed.

marlin7 Dec 7, 2007 at 1:01 AM 

So i'm creating an excel workbook for the first time via this new assembly. I'm very hopeful, but maybe someone indicate how i can give the exe perms to create the excel file even just on my local machine? Here's the exception about access denied.


System.UnauthorizedAccessException was unhandled
Message="Access to the path 'C:\\mynewfile.xlsx' is denied."
Source="mscorlib"
StackTrace:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, Boolean useAsync)
at MS.Internal.IO.Zip.ZipArchive.OpenOnFile(String path, FileMode mode, FileAccess access, FileShare share, Boolean streaming)
at System.IO.Packaging.ZipPackage..ctor(String path, FileMode mode, FileAccess access, FileShare share, Boolean streaming)
at System.IO.Packaging.Package.Open(String path, FileMode packageMode, FileAccess packageAccess, FileShare packageShare, Boolean streaming)
at System.IO.Packaging.Package.Open(String path, FileMode packageMode, FileAccess packageAccess)
at OfficeOpenXml.ExcelPackage..ctor(FileInfo newFile) in D:\ReportingProject\70_ServerReports\ExcelPackage\ExcelPackage.cs:line 78
at TFSSMSDataCollector.TFSSMSDataCollector.CreateExcel(String sWIID, String sXLSXURI) in C:\Users\v-marlin\Documents\Visual Studio 2005\Projects\CreateExcelforSMS\Program.cs:line 70
at TFSSMSDataCollector.Program.Main() in C:\Users\v-marlin\Documents\Visual Studio 2005\Projects\CreateExcelforSMS\Program.cs:line 37