Table of Contents

Class ExcelPackage

Namespace
OfficeOpenXml
Assembly
EPPlus.dll

Represents an Excel XLSX file package.
This is the top-level object to access all parts of the document.

public sealed class ExcelPackage : IDisposable
Inheritance
ExcelPackage
Implements
Inherited Members

Remarks

   FileInfo newFile = new FileInfo(outputDir.FullName + @"\sample1.xlsx");
if (newFile.Exists)
{
	newFile.Delete();  // ensures we create a new workbook
	newFile = new FileInfo(outputDir.FullName + @"\sample1.xlsx");
}
using (ExcelPackage package = new ExcelPackage(newFile))
   {
       // add a new worksheet to the empty workbook
       ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventory");
       //Add the headers
       worksheet.Cells[1, 1].Value = "ID";
       worksheet.Cells[1, 2].Value = "Product";
       worksheet.Cells[1, 3].Value = "Quantity";
       worksheet.Cells[1, 4].Value = "Price";
       worksheet.Cells[1, 5].Value = "Value";

   //Add some items...
   worksheet.Cells["A2"].Value = "12001";
   worksheet.Cells["B2"].Value = "Nails";
   worksheet.Cells["C2"].Value = 37;
   worksheet.Cells["D2"].Value = 3.99;

   worksheet.Cells["A3"].Value = "12002";
   worksheet.Cells["B3"].Value = "Hammer";
   worksheet.Cells["C3"].Value = 5;
   worksheet.Cells["D3"].Value = 12.10;

   worksheet.Cells["A4"].Value = "12003";
   worksheet.Cells["B4"].Value = "Saw";
   worksheet.Cells["C4"].Value = 12;
   worksheet.Cells["D4"].Value = 15.37;

   //Add a formula for the value-column
   worksheet.Cells["E2:E4"].Formula = "C2*D2";

      //Ok now format the values;
   using (var range = worksheet.Cells[1, 1, 1, 5]) 
    {
       range.Style.Font.Bold = true;
       range.Style.Fill.PatternType = ExcelFillStyle.Solid;
       range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
       range.Style.Font.Color.SetColor(Color.White);
   }

   worksheet.Cells["A5:E5"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
   worksheet.Cells["A5:E5"].Style.Font.Bold = true;

   worksheet.Cells[5, 3, 5, 5].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2,3,4,3).Address);
   worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";
   worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";

   //Create an autofilter for the range
   worksheet.Cells["A1:E4"].AutoFilter = true;

   worksheet.Cells["A1:E5"].AutoFitColumns(0);

   // lets set the header text 
   worksheet.HeaderFooter.oddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventory";
   // 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);
   // add the sheet name to the footer
   worksheet.HeaderFooter.oddFooter.CenteredText = ExcelHeaderFooter.SheetName;
   // add the file path to the footer
   worksheet.HeaderFooter.oddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;

   worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"];
   worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"];

    // Change the sheet view to show it in page layout mode
     worksheet.View.PageLayoutView = true;

   // set some document properties
   package.Workbook.Properties.Title = "Invertory";
   package.Workbook.Properties.Author = "Jan Källman";
   package.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel 2007 workbook using EPPlus";

   // set some extended property values
   package.Workbook.Properties.Company = "AdventureWorks Inc.";

   // set some custom property values
   package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Jan Källman");
   package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");

   // save our new workbook and we are done!
   package.Save();

 }

 return newFile.FullName;</code></pre>



More samples can be found at  https://github.com/EPPlusSoftware/EPPlus/

Constructors

ExcelPackage()

Create a new instance of the ExcelPackage. Output is accessed through the Stream property, using the SaveAs(FileInfo) method or later set the File property.

public ExcelPackage()

ExcelPackage(FileInfo)

Create a new instance of the ExcelPackage class based on a existing file or creates a new file.

public ExcelPackage(FileInfo newFile)

Parameters

newFile FileInfo

If newFile exists, it is opened. Otherwise it is created from scratch.

ExcelPackage(FileInfo, bool)

Create a new instance of the ExcelPackage class based on a existing template.

public ExcelPackage(FileInfo template, bool useStream)

Parameters

template FileInfo

The name of the Excel template to use as the basis of the new Excel file

useStream bool

if true use a stream. If false create a file in the temp dir with a random name

ExcelPackage(FileInfo, bool, string)

Create a new instance of the ExcelPackage class based on a existing template.

public ExcelPackage(FileInfo template, bool useStream, string password)

Parameters

template FileInfo

The name of the Excel template to use as the basis of the new Excel file

useStream bool

if true use a stream. If false create a file in the temp dir with a random name

password string

Password to decrypted the template

ExcelPackage(FileInfo, FileInfo)

Create a new instance of the ExcelPackage class based on a existing template. If newFile exists, it will be overwritten when the Save method is called

public ExcelPackage(FileInfo newFile, FileInfo template)

Parameters

newFile FileInfo

The name of the Excel file to be created

template FileInfo

The name of the Excel template to use as the basis of the new Excel file

ExcelPackage(FileInfo, FileInfo, string)

Create a new instance of the ExcelPackage class based on a existing template. If newFile exists, it will be overwritten when the Save method is called

public ExcelPackage(FileInfo newFile, FileInfo template, string password)

Parameters

newFile FileInfo

The name of the Excel file to be created

template FileInfo

The name of the Excel template to use as the basis of the new Excel file

password string

Password to decrypted the template

ExcelPackage(FileInfo, string)

Create a new instance of the ExcelPackage class based on a existing file or creates a new file.

public ExcelPackage(FileInfo newFile, string password)

Parameters

newFile FileInfo

If newFile exists, it is opened. Otherwise it is created from scratch.

password string

Password for an encrypted package

ExcelPackage(Stream)

Create a new instance of the ExcelPackage class based on a stream

public ExcelPackage(Stream newStream)

Parameters

newStream Stream

The stream object can be empty or contain a package. The stream must be Read/Write

ExcelPackage(Stream, Stream)

Create a new instance of the ExcelPackage class based on a stream

public ExcelPackage(Stream newStream, Stream templateStream)

Parameters

newStream Stream

The output stream. Must be an empty read/write stream.

templateStream Stream

This stream is copied to the output stream at load

ExcelPackage(Stream, Stream, string)

Create a new instance of the ExcelPackage class based on a stream

public ExcelPackage(Stream newStream, Stream templateStream, string Password)

Parameters

newStream Stream

The output stream. Must be an empty read/write stream.

templateStream Stream

This stream is copied to the output stream at load

Password string

Password to decrypted the template

ExcelPackage(Stream, string)

Create a new instance of the ExcelPackage class based on a stream

public ExcelPackage(Stream newStream, string Password)

Parameters

newStream Stream

The stream object can be empty or contain a package. The stream must be Read/Write

Password string

The password to decrypt the document

ExcelPackage(string)

Create a new instance of the ExcelPackage class based on a existing file or creates a new file.

public ExcelPackage(string path)

Parameters

path string

If newFile exists, it is opened. Otherwise it is created from scratch.

ExcelPackage(string, string)

Create a new instance of the ExcelPackage class based on a existing file or creates a new file.

public ExcelPackage(string path, string password)

Parameters

path string

If newFile exists, it is opened. Otherwise it is created from scratch.

password string

Password for an encrypted package

ExcelPackage(string, string, string)

Create a new instance of the ExcelPackage class based on a existing template. If newFile exists, it will be overwritten when the Save method is called

public ExcelPackage(string newFilePath, string templatePath, string password)

Parameters

newFilePath string

The name of the Excel file to be created

templatePath string

The name of the Excel template to use as the basis of the new Excel file

password string

Password to decrypted the template

Fields

MaxColumns

Maximum number of columns in a worksheet (16384).

public const int MaxColumns = 16384

Field Value

int

MaxRows

Maximum number of rows in a worksheet (1048576).

public const int MaxRows = 1048576

Field Value

int

Properties

Compatibility

Compatibility settings for older versions of EPPlus.

public CompatibilitySettings Compatibility { get; }

Property Value

CompatibilitySettings

Compression

Compression option for the package

public CompressionLevel Compression { get; set; }

Property Value

CompressionLevel

DoAdjustDrawings

Automaticlly adjust drawing size when column width/row height are adjusted, depending on the drawings editBy property. Default True

public bool DoAdjustDrawings { get; set; }

Property Value

bool

Encryption

Information how and if the package is encrypted

public ExcelEncryption Encryption { get; }

Property Value

ExcelEncryption

File

The output file. Null if no file is used

public FileInfo File { get; set; }

Property Value

FileInfo

InitializationErrors

Errors that has been logged during initialization of the ExcelPackage class.

public IEnumerable<ExcelInitializationError> InitializationErrors { get; }

Property Value

IEnumerable<ExcelInitializationError>

LicenseContext

To use the EPPlus library in debug mode a Licensetype must be specified. Use LicenseContext.NonCommercial if you use EPPlus in an non commercial context. Use LicenseContext.Commercial if you have purchased an license to use EPPlus See https://epplussoftware.com/developers/licenseexception

public static LicenseContext? LicenseContext { get; set; }

Property Value

LicenseContext?

MemorySettings

Memmory settings for RecyclableMemoryStream handling

public static MemorySettings MemorySettings { get; }

Property Value

MemorySettings

Settings

Package generic settings

public ExcelPackageSettings Settings { get; }

Property Value

ExcelPackageSettings

Stream

The output stream. This stream is the not the encrypted package. To get the encrypted package use the SaveAs(stream) method.

public Stream Stream { get; }

Property Value

Stream

Workbook

Returns a reference to the workbook component within the package. All worksheets and cells can be accessed through the workbook.

public ExcelWorkbook Workbook { get; }

Property Value

ExcelWorkbook

Methods

Configure(Action<ExcelPackageConfiguration>)

Global configuration for the ExcelPackage class

public static void Configure(Action<ExcelPackageConfiguration> configHandler)

Parameters

configHandler Action<ExcelPackageConfiguration>

Dispose()

Closes the package.

public void Dispose()

GetAsByteArray()

Saves and returns the Excel files as a bytearray. Note that the package is closed upon save

public byte[] GetAsByteArray()

Returns

byte[]

Examples

Example how to return a document from a Webserver...

ExcelPackage package=new ExcelPackage();
/**** ... Create the document ****/
Byte[] bin = package.GetAsByteArray();
Response.ContentType = "Application/vnd.ms-Excel";
Response.AddHeader("content-disposition", "attachment;  filename=TheFile.xlsx");
Response.BinaryWrite(bin);

GetAsByteArray(string)

Saves and returns the Excel files as a bytearray Note that the package is closed upon save

public byte[] GetAsByteArray(string password)

Parameters

password string

The password to encrypt the workbook with. This parameter overrides the Encryption.Password.

Returns

byte[]

Examples

Example how to return a document from a Webserver...

ExcelPackage package=new ExcelPackage();
/**** ... Create the document ****/
Byte[] bin = package.GetAsByteArray();
Response.ContentType = "Application/vnd.ms-Excel";
Response.AddHeader("content-disposition", "attachment;  filename=TheFile.xlsx");
Response.BinaryWrite(bin);

GetAsByteArrayAsync(string, CancellationToken)

Saves and returns the Excel files as a bytearray Note that the package is closed upon save

public Task<byte[]> GetAsByteArrayAsync(string password, CancellationToken cancellationToken = default)

Parameters

password string

The password to encrypt the workbook with. This parameter overrides the Encryption.Password.

cancellationToken CancellationToken

The cancellation token

Returns

Task<byte[]>

Examples

Example how to return a document from a Webserver...

ExcelPackage package=new ExcelPackage();
/**** ... Create the document ****/
Byte[] bin = package.GetAsByteArray();
Response.ContentType = "Application/vnd.ms-Excel";
Response.AddHeader("content-disposition", "attachment;  filename=TheFile.xlsx");
Response.BinaryWrite(bin);

GetAsByteArrayAsync(CancellationToken)

Saves and returns the Excel files as a bytearray. Note that the package is closed upon save

public Task<byte[]> GetAsByteArrayAsync(CancellationToken cancellationToken = default)

Parameters

cancellationToken CancellationToken

The cancellation token

Returns

Task<byte[]>

Examples

Example how to return a document from a Webserver...

ExcelPackage package=new ExcelPackage();
/**** ... Create the document ****/
Byte[] bin = package.GetAsByteArray();
Response.ContentType = "Application/vnd.ms-Excel";
Response.AddHeader("content-disposition", "attachment;  filename=TheFile.xlsx");
Response.BinaryWrite(bin);

Load(Stream)

Loads the specified package data from a stream.

public void Load(Stream input)

Parameters

input Stream

The input.

Load(Stream, string)

Loads the specified package data from a stream.

public void Load(Stream input, string Password)

Parameters

input Stream

The input.

Password string

The password to decrypt the document

LoadAsync(FileInfo, Stream, string, CancellationToken)

Loads the specified package data from a stream.

public Task LoadAsync(FileInfo fileInfo, Stream output, string Password, CancellationToken cancellationToken = default)

Parameters

fileInfo FileInfo

The input file.

output Stream

The out stream. Sets the Stream property

Password string

The password

cancellationToken CancellationToken

The cancellation token

Returns

Task

LoadAsync(FileInfo, string, CancellationToken)

Loads the specified package data from a stream.

public Task LoadAsync(FileInfo fileInfo, string Password, CancellationToken cancellationToken = default)

Parameters

fileInfo FileInfo

The input file.

Password string

The password

cancellationToken CancellationToken

The cancellation token

Returns

Task

LoadAsync(FileInfo, CancellationToken)

Loads the specified package data from a stream.

public Task LoadAsync(FileInfo fileInfo, CancellationToken cancellationToken = default)

Parameters

fileInfo FileInfo

The input file.

cancellationToken CancellationToken

The cancellation token

Returns

Task

LoadAsync(Stream, string, CancellationToken)

Loads the specified package data from a stream.

public Task LoadAsync(Stream input, string Password, CancellationToken cancellationToken = default)

Parameters

input Stream

The input.

Password string

The password to decrypt the document

cancellationToken CancellationToken

The cancellation token

Returns

Task

LoadAsync(Stream, CancellationToken)

Loads the specified package data from a stream.

public Task LoadAsync(Stream input, CancellationToken cancellationToken = default)

Parameters

input Stream

The input.

cancellationToken CancellationToken

The cancellation token

Returns

Task

LoadAsync(string, Stream, string, CancellationToken)

Loads the specified package data from a stream.

public Task LoadAsync(string filePath, Stream output, string password, CancellationToken cancellationToken = default)

Parameters

filePath string

The input file.

output Stream

The out stream. Sets the Stream property

password string

The password

cancellationToken CancellationToken

The cancellation token

Returns

Task

LoadAsync(string, string, CancellationToken)

Loads the specified package data from a stream.

public Task LoadAsync(string filePath, string password, CancellationToken cancellationToken = default)

Parameters

filePath string

The input file.

password string

The password

cancellationToken CancellationToken

The cancellation token

Returns

Task

LoadAsync(string, CancellationToken)

Loads the specified package data from a stream.

public Task LoadAsync(string filePath, CancellationToken cancellationToken = default)

Parameters

filePath string

The input file.

cancellationToken CancellationToken

The cancellation token

Returns

Task

Save()

Saves all the components back into the package. This method recursively calls the Save method on all sub-components. We close the package after the save is done.

public void Save()

Save(string)

Saves all the components back into the package. This method recursively calls the Save method on all sub-components. The package is closed after it has ben saved Supply a password to encrypt the workbook with.

public void Save(string password)

Parameters

password string

This parameter overrides the Workbook.Encryption.Password.

SaveAs(FileInfo)

Saves the workbook to a new file The package is closed after it has been saved

public void SaveAs(FileInfo file)

Parameters

file FileInfo

The file location

SaveAs(FileInfo, string)

Saves the workbook to a new file The package is closed after it has been saved

public void SaveAs(FileInfo file, string password)

Parameters

file FileInfo

The file

password string

The password to encrypt the workbook with. This parameter overrides the Encryption.Password.

SaveAs(Stream)

Copies the Package to the Outstream The package is closed after it has been saved

public void SaveAs(Stream OutputStream)

Parameters

OutputStream Stream

The stream to copy the package to

SaveAs(Stream, string)

Copies the Package to the Outstream The package is closed after it has been saved

public void SaveAs(Stream OutputStream, string password)

Parameters

OutputStream Stream

The stream to copy the package to

password string

The password to encrypt the workbook with. This parameter overrides the Encryption.Password.

SaveAs(string)

Saves the workbook to a new file The package is closed after it has been saved

public void SaveAs(string filePath)

Parameters

filePath string

The file location

SaveAs(string, string)

Saves the workbook to a new file The package is closed after it has been saved

public void SaveAs(string filePath, string password)

Parameters

filePath string

The file

password string

The password to encrypt the workbook with. This parameter overrides the Encryption.Password.

SaveAsAsync(FileInfo, string, CancellationToken)

Saves the workbook to a new file The package is closed after it has been saved

public Task SaveAsAsync(FileInfo file, string password, CancellationToken cancellationToken = default)

Parameters

file FileInfo

The file

password string

The password to encrypt the workbook with. This parameter overrides the Encryption.Password.

cancellationToken CancellationToken

The cancellation token

Returns

Task

SaveAsAsync(FileInfo, CancellationToken)

Saves the workbook to a new file The package is closed after it has been saved

public Task SaveAsAsync(FileInfo file, CancellationToken cancellationToken = default)

Parameters

file FileInfo

The file location

cancellationToken CancellationToken

The cancellation token

Returns

Task

SaveAsAsync(Stream, string, CancellationToken)

Copies the Package to the Outstream The package is closed after it has been saved

public Task SaveAsAsync(Stream OutputStream, string password, CancellationToken cancellationToken = default)

Parameters

OutputStream Stream

The stream to copy the package to

password string

The password to encrypt the workbook with. This parameter overrides the Encryption.Password.

cancellationToken CancellationToken

The cancellation token

Returns

Task

SaveAsAsync(Stream, CancellationToken)

Copies the Package to the Outstream The package is closed after it has been saved

public Task SaveAsAsync(Stream OutputStream, CancellationToken cancellationToken = default)

Parameters

OutputStream Stream

The stream to copy the package to

cancellationToken CancellationToken

The cancellation token

Returns

Task

SaveAsAsync(string, string, CancellationToken)

Saves the workbook to a new file The package is closed after it has been saved

public Task SaveAsAsync(string filePath, string password, CancellationToken cancellationToken = default)

Parameters

filePath string

The file

password string

The password to encrypt the workbook with. This parameter overrides the Encryption.Password.

cancellationToken CancellationToken

The cancellation token

Returns

Task

SaveAsAsync(string, CancellationToken)

Saves the workbook to a new file The package is closed after it has been saved

public Task SaveAsAsync(string filePath, CancellationToken cancellationToken = default)

Parameters

filePath string

The file location

cancellationToken CancellationToken

The cancellation token

Returns

Task

SaveAsync(string, CancellationToken)

Saves all the components back into the package. This method recursively calls the Save method on all sub-components. The package is closed after it has ben saved Supply a password to encrypt the workbook package.

public Task SaveAsync(string password, CancellationToken cancellationToken = default)

Parameters

password string

This parameter overrides the Workbook.Encryption.Password.

cancellationToken CancellationToken

The cancellation token

Returns

Task

SaveAsync(CancellationToken)

Saves all the components back into the package. This method recursively calls the Save method on all sub-components. The package is closed after it has ben saved d to encrypt the workbook with.

public Task SaveAsync(CancellationToken cancellationToken = default)

Parameters

cancellationToken CancellationToken

Returns

Task