C# Class Rock.Utility.ExcelHelper

Methods that facilitate generating Excel files

Public Methods

Method	Description
AddWorksheet ( this excel, DataTable data ) : void	Adds a worksheet from a DataTable
CreateNewFile ( DataSet sqlResults = null, string title = null ) : ExcelPackage	Creates an Excel Workbook from a DataSet
DefaultColumnFormat ( IRockGridField field, object exportValue ) : string	Sets the default formatting for an excel column based on the field type and data type
DefaultColumnFormat ( Type type ) : string	Sets the default formatting for an excel column based on the data type
FinalColumnFormat ( object exportValue, string defaultFormat ) : string	Updates the excel column format based on the level of detail in the data
FormatWorksheet ( this worksheet, string title, int headerRows, int rows, int columns ) : void	Apply the default Rock worksheet formatting
Save ( ExcelPackage excel, string fileName, RockContext rockContext, BinaryFileType binaryFileType = null ) : BinaryFile	Saves an ExcelPackage as a BinaryFile and stores it in the database
SendToBrowser ( this excel, System page, string filename ) : void	Sends an Excel workbook to the user
SetExcelValue ( OfficeOpenXml.ExcelRange range, object exportValue ) : void	Formats the export value.
ToByteArray ( this excel ) : byte[]	Convert an ExcelPackage to a ByteArray
ToMemoryStream ( this excel ) : MemoryStream	Convert an ExcelPackage to a MemoryStream

Adds a worksheet from a DataTable

public static AddWorksheet ( this excel, DataTable data ) : void
excel	this	The ExcelPackage object to add a worksheet to
data	System.Data.DataTable	The DataTable to populate the worksheet with. The TableName of the DataTable will be the header and Tab name of the worksheet.
return	void

Creates an Excel Workbook from a DataSet

public static CreateNewFile ( DataSet sqlResults = null, string title = null ) : ExcelPackage
sqlResults	System.Data.DataSet	The DataSet to populate a workbook with. The TableName of each DataTable will be the header and Tab name of each sheet.
title	string	The Title of the workbook
return	OfficeOpenXml.ExcelPackage

Sets the default formatting for an excel column based on the field type and data type

public static DefaultColumnFormat ( IRockGridField field, object exportValue ) : string
field	IRockGridField	The grid field
exportValue	object	An example value for this column; you can use the data from the first row
return	string

Sets the default formatting for an excel column based on the data type

public static DefaultColumnFormat ( Type type ) : string
type	System.Type	The data type of the column
return	string

Updates the excel column format based on the level of detail in the data

public static FinalColumnFormat ( object exportValue, string defaultFormat ) : string
exportValue	object	The cell value to use for formatting
defaultFormat	string	The existing column format to use if no changes are to be made
return	string

Apply the default Rock worksheet formatting

public static FormatWorksheet ( this worksheet, string title, int headerRows, int rows, int columns ) : void
worksheet	this	The worksheet to be formatted
title	string	The title to display in the worksheet header
headerRows	int	The number of rows to use for the header
rows	int	The number of rows populated in the worksheet
columns	int	The number of columns populated in the worksheet
return	void

Saves an ExcelPackage as a BinaryFile and stores it in the database

public static Save ( ExcelPackage excel, string fileName, RockContext rockContext, BinaryFileType binaryFileType = null ) : BinaryFile
excel	OfficeOpenXml.ExcelPackage	The ExcelPackage object to save
fileName	string	The filename to save the workbook as
rockContext	Rock.Data.RockContext	The RockContext to use
binaryFileType	BinaryFileType	Optionally specifies the BinaryFileType to apply to this file for security purposes
return	BinaryFile

Sends an Excel workbook to the user

public static SendToBrowser ( this excel, System page, string filename ) : void
excel	this	The ExcelPackage to send
page	System	The current Page object
filename	string	The filename to send the file as
return	void

Formats the export value.

public static SetExcelValue ( OfficeOpenXml.ExcelRange range, object exportValue ) : void
range	OfficeOpenXml.ExcelRange	The range.
exportValue	object	The export value.
return	void

Convert an ExcelPackage to a ByteArray

public static ToByteArray ( this excel ) : byte[]
excel	this	The ExcelPackage
return	byte[]

Convert an ExcelPackage to a MemoryStream

public static ToMemoryStream ( this excel ) : MemoryStream
excel	this	The ExcelPackage
return	System.IO.MemoryStream