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¶

object ← ExcelPackage

Implements¶

IDisposable

Inherited Members¶

object.Equals(object?, object?)
object.Equals(object?)
object.GetHashCode()
object.GetType()
object.ReferenceEquals(object?, object?)
object.ToString()

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;

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(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(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(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(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(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

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(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, 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(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

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¶

Automatically 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>

License¶

Used to set the license EPPlus uses.

For use within a commercial organization. Requires a license key which can be purchased at https://epplussoftware.com.
[SetCommercial(string)](OfficeOpenXml.EPPlusLicense.md#OfficeOpenXml_EPPlusLicense_SetCommercial_System_String_)

For use within a non-commercial organization. The document will be tagged with the Polyform Noncommercial License.
[SetNonCommercialOrganization(string)](OfficeOpenXml.EPPlusLicense.md#OfficeOpenXml_EPPlusLicense_SetNonCommercialOrganization_System_String_)

For personal non-commercial use.
[SetNonCommercialPersonal(string)](OfficeOpenXml.EPPlusLicense.md#OfficeOpenXml_EPPlusLicense_SetNonCommercialPersonal_System_String_)

public static EPPlusLicense License { get; }

Property Value¶

EPPlusLicense

LicenseContext¶

This property is obsolete in EPPlus 8, and will be removed in coming versions. Please use the License property to configure the license. https://epplussoftware.com/developers/licensenotsetexception

[Obsolete("Please use the static 'ExcelPackage.License' property to set the required license information from EPPlus 8 and later versions. For more info see http://epplussoftware.com/developers/licensenotsetexception.")]
public static LicenseContext? LicenseContext { get; set; }

Property Value¶

LicenseContext?

MemorySettings¶

Memmory settings for RecyclableMemoryStream handling

public static MemorySettings MemorySettings { get; }

Property Value¶

MemorySettings

SensibilityLabelHandler¶

If you want your workbooks to be marked with sensibility lables, you can add a handler for authentication, encryption and decryption using the Microsoft Information Protection SDK. For more information

public static ISensitivityLabelHandler SensibilityLabelHandler { get; set; }

Property Value¶

ISensitivityLabelHandler

SensibilityLabels¶

Sensibility labels meta data. SensibilityLabelHandler

public ExcelSensibilityLabelCollection SensibilityLabels { get; }

Property Value¶

ExcelSensibilityLabelCollection

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(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);

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);

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, 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(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

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(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(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(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(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(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

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(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(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(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.

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.

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(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

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(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(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(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

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

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