Class ExcelPackage

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

Inheritance

System.Object

ExcelPackage

Implements

System.IDisposable

Inherited Members

System.Object.Equals(System.Object)

System.Object.Equals(System.Object, System.Object)

System.Object.GetHashCode()

System.Object.GetType()

System.Object.MemberwiseClone()

System.Object.ReferenceEquals(System.Object, System.Object)

System.Object.ToString()

Namespace: OfficeOpenXml

Assembly: EPPlus.dll

Syntax

public sealed class ExcelPackage : IDisposable

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[&quot;A2&quot;].Value = &quot;12001&quot;;
    worksheet.Cells[&quot;B2&quot;].Value = &quot;Nails&quot;;
    worksheet.Cells[&quot;C2&quot;].Value = 37;
    worksheet.Cells[&quot;D2&quot;].Value = 3.99;

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

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

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

       //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[&quot;A5:E5&quot;].Style.Border.Top.Style = ExcelBorderStyle.Thin;
    worksheet.Cells[&quot;A5:E5&quot;].Style.Font.Bold = true;

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

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

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

    // lets set the header text 
    worksheet.HeaderFooter.oddHeader.CenteredText = &quot;&amp;24&amp;U&amp;\&quot;Arial,Regular Bold\&quot; Inventory&quot;;
    // add the page number to the footer plus the total number of pages
    worksheet.HeaderFooter.oddFooter.RightAlignedText =
    string.Format(&quot;Page {0} of {1}&quot;, 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[&quot;1:2&quot;];
    worksheet.PrinterSettings.RepeatColumns = worksheet.Cells[&quot;A:G&quot;];

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

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

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

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

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

  }

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

More samples can be found at  https://github.com/JanKallman/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.

Declaration

public ExcelPackage()

ExcelPackage(FileInfo)

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

Declaration

public ExcelPackage(FileInfo newFile)

Parameters

Type	Name	Description
System.IO.FileInfo	newFile	If newFile exists, it is opened. Otherwise it is created from scratch.

ExcelPackage(FileInfo, Boolean)

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

Declaration

public ExcelPackage(FileInfo template, bool useStream)

Parameters

Type	Name	Description
System.IO.FileInfo	template	The name of the Excel template to use as the basis of the new Excel file
System.Boolean	useStream	if true use a stream. If false create a file in the temp dir with a random name

ExcelPackage(FileInfo, Boolean, String)

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

Declaration

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

Parameters

Type	Name	Description
System.IO.FileInfo	template	The name of the Excel template to use as the basis of the new Excel file
System.Boolean	useStream	if true use a stream. If false create a file in the temp dir with a random name
System.String	password	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

Declaration

public ExcelPackage(FileInfo newFile, FileInfo template)

Parameters

Type	Name	Description
System.IO.FileInfo	newFile	The name of the Excel file to be created
System.IO.FileInfo	template	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

Declaration

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

Parameters

Type	Name	Description
System.IO.FileInfo	newFile	The name of the Excel file to be created
System.IO.FileInfo	template	The name of the Excel template to use as the basis of the new Excel file
System.String	password	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.

Declaration

public ExcelPackage(FileInfo newFile, string password)

Parameters

Type	Name	Description
System.IO.FileInfo	newFile	If newFile exists, it is opened. Otherwise it is created from scratch.
System.String	password	Password for an encrypted package

ExcelPackage(Stream)

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

Declaration

public ExcelPackage(Stream newStream)

Parameters

Type	Name	Description
System.IO.Stream	newStream	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

Declaration

public ExcelPackage(Stream newStream, Stream templateStream)

Parameters

Type	Name	Description
System.IO.Stream	newStream	The output stream. Must be an empty read/write stream.
System.IO.Stream	templateStream	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

Declaration

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

Parameters

Type	Name	Description
System.IO.Stream	newStream	The output stream. Must be an empty read/write stream.
System.IO.Stream	templateStream	This stream is copied to the output stream at load
System.String	Password	Password to decrypted the template

ExcelPackage(Stream, String)

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

Declaration

public ExcelPackage(Stream newStream, string Password)

Parameters

Type	Name	Description
System.IO.Stream	newStream	The stream object can be empty or contain a package. The stream must be Read/Write
System.String	Password	The password to decrypt the document

Fields

MaxColumns

Maximum number of columns in a worksheet (16384).

Declaration

public const int MaxColumns = 16384

Field Value

Type	Description
System.Int32

MaxRows

Maximum number of rows in a worksheet (1048576).

Declaration

public const int MaxRows = 1048576

Field Value

Type	Description
System.Int32

Properties

Compatibility

Compatibility settings for older versions of EPPlus.

Declaration

public CompatibilitySettings Compatibility { get; }

Property Value

Type	Description
CompatibilitySettings

Compression

Compression option for the package

Declaration

public CompressionLevel Compression { get; set; }

Property Value

Type	Description
CompressionLevel

DoAdjustDrawings

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

Declaration

public bool DoAdjustDrawings { get; set; }

Property Value

Type	Description
System.Boolean

Encryption

Information how and if the package is encrypted

Declaration

public ExcelEncryption Encryption { get; }

Property Value

Type	Description
ExcelEncryption

File

The output file. Null if no file is used

Declaration

public FileInfo File { get; set; }

Property Value

Type	Description
System.IO.FileInfo

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

Declaration

public static LicenseContext? LicenseContext { get; set; }

Property Value

Type	Description
System.Nullable<LicenseContext>

Stream

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

Declaration

public Stream Stream { get; }

Property Value

Type	Description
System.IO.Stream

Workbook

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

Declaration

public ExcelWorkbook Workbook { get; }

Property Value

Type	Description
ExcelWorkbook

Methods

Dispose()

Closes the package.

Declaration

public void Dispose()

GetAsByteArray()

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

Declaration

public byte[] GetAsByteArray()

Returns

Type	Description
System.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

Declaration

public byte[] GetAsByteArray(string password)

Parameters

Type	Name	Description
System.String	password	The password to encrypt the workbook with. This parameter overrides the Encryption.Password.

Returns

Type	Description
System.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

Declaration

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

Parameters

Type	Name	Description
System.String	password	The password to encrypt the workbook with. This parameter overrides the Encryption.Password.
System.Threading.CancellationToken	cancellationToken	The cancellation token

Returns

Type	Description
System.Threading.Tasks.Task<System.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

Declaration

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

Parameters

Type	Name	Description
System.Threading.CancellationToken	cancellationToken	The cancellation token

Returns

Type	Description
System.Threading.Tasks.Task<System.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.

Declaration

public void Load(Stream input)

Parameters

Type	Name	Description
System.IO.Stream	input	The input.

Load(Stream, String)

Loads the specified package data from a stream.

Declaration

public void Load(Stream input, string Password)

Parameters

Type	Name	Description
System.IO.Stream	input	The input.
System.String	Password	The password to decrypt the document

LoadAsync(FileInfo, Stream, String, CancellationToken)

Loads the specified package data from a stream.

Declaration

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

Parameters

Type	Name	Description
System.IO.FileInfo	fileInfo	The input file.
System.IO.Stream	output	The out stream. Sets the Stream property
System.String	Password	The password
System.Threading.CancellationToken	cancellationToken	The cancellation token

Returns

Type	Description
System.Threading.Tasks.Task

LoadAsync(FileInfo, String, CancellationToken)

Loads the specified package data from a stream.

Declaration

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

Parameters

Type	Name	Description
System.IO.FileInfo	fileInfo	The input file.
System.String	Password	The password
System.Threading.CancellationToken	cancellationToken	The cancellation token

Returns

Type	Description
System.Threading.Tasks.Task

LoadAsync(FileInfo, CancellationToken)

Loads the specified package data from a stream.

Declaration

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

Parameters

Type	Name	Description
System.IO.FileInfo	fileInfo	The input file.
System.Threading.CancellationToken	cancellationToken	The cancellation token

Returns

Type	Description
System.Threading.Tasks.Task

LoadAsync(Stream, String, CancellationToken)

Loads the specified package data from a stream.

Declaration

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

Parameters

Type	Name	Description
System.IO.Stream	input	The input.
System.String	Password	The password to decrypt the document
System.Threading.CancellationToken	cancellationToken	The cancellation token

Returns

Type	Description
System.Threading.Tasks.Task

LoadAsync(Stream, CancellationToken)

Loads the specified package data from a stream.

Declaration

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

Parameters

Type	Name	Description
System.IO.Stream	input	The input.
System.Threading.CancellationToken	cancellationToken	The cancellation token

Returns

Type	Description
System.Threading.Tasks.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.

Declaration

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.

Declaration

public void Save(string password)

Parameters

Type	Name	Description
System.String	password	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

Declaration

public void SaveAs(FileInfo file)

Parameters

Type	Name	Description
System.IO.FileInfo	file	The file location

SaveAs(FileInfo, String)

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

Declaration

public void SaveAs(FileInfo file, string password)

Parameters

Type	Name	Description
System.IO.FileInfo	file	The file
System.String	password	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

Declaration

public void SaveAs(Stream OutputStream)

Parameters

Type	Name	Description
System.IO.Stream	OutputStream	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

Declaration

public void SaveAs(Stream OutputStream, string password)

Parameters

Type	Name	Description
System.IO.Stream	OutputStream	The stream to copy the package to
System.String	password	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

Declaration

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

Parameters

Type	Name	Description
System.IO.FileInfo	file	The file
System.String	password	The password to encrypt the workbook with. This parameter overrides the Encryption.Password.
System.Threading.CancellationToken	cancellationToken	The cancellation token

Returns

Type	Description
System.Threading.Tasks.Task

SaveAsAsync(FileInfo, CancellationToken)

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

Declaration

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

Parameters

Type	Name	Description
System.IO.FileInfo	file	The file location
System.Threading.CancellationToken	cancellationToken	The cancellation token

Returns

Type	Description
System.Threading.Tasks.Task

SaveAsAsync(Stream, String, CancellationToken)

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

Declaration

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

Parameters

Type	Name	Description
System.IO.Stream	OutputStream	The stream to copy the package to
System.String	password	The password to encrypt the workbook with. This parameter overrides the Encryption.Password.
System.Threading.CancellationToken	cancellationToken	The cancellation token

Returns

Type	Description
System.Threading.Tasks.Task

SaveAsAsync(Stream, CancellationToken)

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

Declaration

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

Parameters

Type	Name	Description
System.IO.Stream	OutputStream	The stream to copy the package to
System.Threading.CancellationToken	cancellationToken	The cancellation token

Returns

Type	Description
System.Threading.Tasks.Task

SaveAsync(String, CancellationToken)

Declaration

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

Parameters

Type	Name	Description
System.String	password	This parameter overrides the Workbook.Encryption.Password.
System.Threading.CancellationToken	cancellationToken	The cancellation token

Returns

Type	Description
System.Threading.Tasks.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.

Declaration

public Task SaveAsync(CancellationToken cancellationToken = default(CancellationToken))

Parameters

Type	Name	Description
System.Threading.CancellationToken	cancellationToken

Returns

Type	Description
System.Threading.Tasks.Task

Implements

System.IDisposable