Class Excel

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

    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 http://epplus.codeplex.com/

Inheritance

System.Object

Excel

Implements

System.IDisposable

Inherited Members

System.Object.ToString()

System.Object.Equals(System.Object)

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

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

System.Object.GetHashCode()

System.Object.GetType()

System.Object.MemberwiseClone()

Namespace: Experior.Core.Data.OfficeOpenXml

Assembly: Experior.Core.dll

Syntax

public sealed class Excel : IDisposable

Constructors

Excel()

Create a new instance of the ExcelPackage. Output is accessed through the Stream property.

Declaration

public Excel()

Excel(FileInfo)

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

Declaration

public Excel(FileInfo newFile)

Parameters

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

Excel(FileInfo, Boolean)

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

Declaration

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

Excel(FileInfo, Boolean, String)

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

Declaration

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

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

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

Excel(FileInfo, String)

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

Declaration

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

Excel(Stream)

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

Declaration

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

Excel(Stream, Stream)

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

Declaration

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

Excel(Stream, Stream, String)

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

Declaration

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

Excel(Stream, String)

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

Declaration

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

Compression

Compression option for the package

Declaration

public CompressionOption Compression { get; set; }

Property Value

Type	Description
System.IO.Packaging.CompressionOption

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

Package

Returns a reference to the package

Declaration

public Package Package { get; }

Property Value

Type	Description
System.IO.Packaging.Package

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

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

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 been saved

Declaration

public void Save(string password)

Parameters

Type	Name	Description
System.String	password	The password to encrypt the workbook with. 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

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.

Implements

System.IDisposable