Class ExcelHelper

Extension methods for common EPPlus tasks

Inheritance

System.Object

ExcelHelper

Namespace: SigStat.Common.Helpers

Assembly: SigStat.Common.dll

Syntax

public static class ExcelHelper : object

Methods

FormatAsTable(ExcelRange, ExcelColor, Boolean, Boolean)

Format cells in the range into a table

Declaration

public static void FormatAsTable(this ExcelRange range, ExcelColor color = ExcelColor.Primary, bool showColumnHeader = true, bool showRowHeader = true)

Parameters

Type	Name	Description
ExcelRange	range	The table's cells
ExcelColor	color	Color palette of the table
System.Boolean	showColumnHeader	Defines if the table has column header
System.Boolean	showRowHeader	Defines if the table has row header

FormatAsTableWithTitle(ExcelRange, String, ExcelColor, Boolean, Boolean)

Format cells in the range into a table with possible title

Declaration

public static int FormatAsTableWithTitle(this ExcelRange range, string title, ExcelColor color = ExcelColor.Primary, bool showColumnHeader = true, bool showRowHeader = true)

Parameters

Type	Name	Description
ExcelRange	range	The table's cells
System.String	title	The table's title, if null, the table won't have title
ExcelColor	color	Color palette of the table
System.Boolean	showColumnHeader	Defines if the table has column header
System.Boolean	showRowHeader	Defines if the table has row header

Returns

Type	Description
System.Int32

InsertColumnChart(ExcelWorksheet, ExcelRange, Int32, Int32, String, String, String, ExcelRange, Int32, Int32, String)

Draws a column chart for the given data

Declaration

public static void InsertColumnChart(this ExcelWorksheet ws, ExcelRange range, int row, int col, string name, string xLabel = null, string yLabel = null, ExcelRange serieLabels = null, int width = null, int height = null, string title = null)

Parameters

Type	Name	Description
ExcelWorksheet	ws	Worksheet in wich the graph is inserted
ExcelRange	range	Range containing the data (first row for x axis other rows for series)
System.Int32	row	The graph inserted starts at this row
System.Int32	col	The graph inserted starts at this column
System.String	name	Id and default title of the graph
System.String	xLabel	Label for x axis of the graph
System.String	yLabel	Label for y axis of the graph
ExcelRange	serieLabels	If the graph hase more than one series, each can be named separately
System.Int32	width	Graph's width in px
System.Int32	height	Graph's height in px
System.String	title	Title of the graph if the defauolt name has to be overwritten

InsertDictionary<TKey, TValue>(ExcelWorksheet, Int32, Int32, IEnumerable<KeyValuePair<TKey, TValue>>, String, ExcelColor, String)

Insert table from key-value pairs

Declaration

public static ExcelRange InsertDictionary<TKey, TValue>(this ExcelWorksheet ws, int row, int col, IEnumerable<KeyValuePair<TKey, TValue>> data, string title = null, ExcelColor color = ExcelColor.Primary, string Name = null)

Parameters

Type	Name	Description
ExcelWorksheet	ws	Worksheet in wich the table is created
System.Int32	row	Starting row of the table
System.Int32	col	Starting column of the table
IEnumerable<KeyValuePair<TKey, TValue>>	data	IEnumerable of key-value pairs in wich the data to insert is stored
System.String	title	The table's title
ExcelColor	color	The table's color
System.String	Name	If given, creates a named range, with this name

Returns

Type	Description
ExcelRange	Range of the inserted data

Type Parameters

Name	Description
TKey
TValue

InsertHierarchicalList(ExcelWorksheet, Int32, Int32, HierarchyElement, String, ExcelColor)

Insert a hierarchical list in tree style into the worksheet

Declaration

public static void InsertHierarchicalList(this ExcelWorksheet ws, int row, int col, HierarchyElement root, string title = null, ExcelColor color = ExcelColor.Primary)

Parameters

Type	Name	Description
ExcelWorksheet	ws	Worksheet in wich the list is inserted
System.Int32	row	Starting row of the list
System.Int32	col	Starting column of the list
HierarchyElement	root	Root element of the list
System.String	title	Title of the list
ExcelColor	color	color of the list

InsertLegend(ExcelRange, String, String, ExcelColor)

Insert legend

Declaration

public static void InsertLegend(this ExcelRange range, string text, string title = null, ExcelColor color = ExcelColor.Info)

Parameters

Type	Name	Description
ExcelRange	range	Range of the legend
System.String	text	Text of the legend
System.String	title	Title of the legend (can be null)
ExcelColor	color	Color of the legend

InsertLineChart(ExcelWorksheet, ExcelRange, Int32, Int32, String, String, String, ExcelRange, Int32, Int32, String)

Draws a line chart for the given data

Declaration

public static void InsertLineChart(this ExcelWorksheet ws, ExcelRange range, int row, int col, string name, string xLabel = null, string yLabel = null, ExcelRange SerieLabels = null, int width = null, int height = null, string title = null)

Parameters

Type	Name	Description
ExcelWorksheet	ws	Worksheet in wich the graph is inserted
ExcelRange	range	Range containing the data (first row for x axis other rows for series)
System.Int32	row	The graph inserted starts at this row
System.Int32	col	The graph inserted starts at this column
System.String	name	Id and default title of the graph
System.String	xLabel	Label for x axis of the graph
System.String	yLabel	Label for y axis of the graph
ExcelRange	SerieLabels	Label of the series
System.Int32	width	Graph's width in px
System.Int32	height	Graph's height in px
System.String	title	Title of the graph if the defauolt name has to be overwritten

InsertLink(ExcelRange, String)

Creates a link to given sheet

Declaration

public static void InsertLink(this ExcelRange range, string sheet)

Parameters

Type	Name	Description
ExcelRange	range	Cells to place the link in
System.String	sheet	Destination sheet's name

InsertLink(ExcelRange, String, String)

Creates a link to selected cells in given sheet

Declaration

public static void InsertLink(this ExcelRange range, string sheet, string cells)

Parameters

Type	Name	Description
ExcelRange	range	Cells to place the link in
System.String	sheet	Destination sheet's name
System.String	cells	Destination cells' address

InsertTable(ExcelWorksheet, Int32, Int32, IEnumerable<IEnumerable<Object>>, IEnumerable<String>, String, ExcelColor, String)

Insert a table filled with data from an IEnumerable

Declaration

public static ExcelRange InsertTable(this ExcelWorksheet ws, int row, int col, IEnumerable<IEnumerable<object>> data, IEnumerable<string> headers, string title = null, ExcelColor color = ExcelColor.Primary, string name = null)

Parameters

Type	Name	Description
ExcelWorksheet	ws	Worksheet in wich the table is created
System.Int32	row	Starting row of the table
System.Int32	col	Starting column of the table
IEnumerable<IEnumerable<System.Object>>	data	IEnumerable in wich the data to insert is stored
IEnumerable<System.String>	headers	Defines if the table has header
System.String	title	The table's title
ExcelColor	color	The table's color
System.String	name	If given, creates a named range, with this name

Returns

Type	Description
ExcelRange	Range of the inserted data

InsertTable(ExcelWorksheet, Int32, Int32, Double[,], String, ExcelColor, Boolean, Boolean, String)

Insert table filled with data from a 2D array

Declaration

public static ExcelRange InsertTable(this ExcelWorksheet ws, int row, int col, double[, ] data, string title = null, ExcelColor color = ExcelColor.Primary, bool hasRowHeader = true, bool hasColumnHeader = true, string name = null)

Parameters

Type	Name	Description
ExcelWorksheet	ws	Worksheet in wich the table is created
System.Int32	row	Starting row of the table
System.Int32	col	Starting column of the table
System.Double[,]	data	2D array in wich the data to insert is stored (double values)
System.String	title	The table's title
ExcelColor	color	The table's color
System.Boolean	hasRowHeader	Defines if the table has row header
System.Boolean	hasColumnHeader	Defines if the table has column header
System.String	name	If given, creates a named range, with this name

Returns

Type	Description
ExcelRange	Range of the inserted data

InsertTable(ExcelWorksheet, Int32, Int32, Object[,], String, ExcelColor, Boolean, Boolean, String)

Insert table filled with data from a 2D array

Declaration

public static ExcelRange InsertTable(this ExcelWorksheet ws, int row, int col, object[, ] data, string title = null, ExcelColor color = ExcelColor.Primary, bool hasRowHeader = true, bool hasColumnHeader = true, string name = null)

Parameters

Type	Name	Description
ExcelWorksheet	ws	Worksheet in wich the table is created
System.Int32	row	Starting row of the table
System.Int32	col	Starting column of the table
System.Object[,]	data	2D array in wich the data to insert is stored
System.String	title	The table's title
ExcelColor	color	The table's color
System.Boolean	hasRowHeader	Defines if the table has row header
System.Boolean	hasColumnHeader	Defines if the table has column header
System.String	name	If given, creates a named range, with this name

Returns

Type	Description
ExcelRange	Range of the inserted data

InsertTable<T>(ExcelWorksheet, Int32, Int32, IEnumerable<T>, String, ExcelColor, Boolean, String)

Insert a table filled with data from an IEnumerable

Declaration

public static ExcelRange InsertTable<T>(this ExcelWorksheet ws, int row, int col, IEnumerable<T> data, string title = null, ExcelColor color = ExcelColor.Primary, bool showHeader = true, string Name = null)

Parameters

Type	Name	Description
ExcelWorksheet	ws	Worksheet in wich the table is created
System.Int32	row	Starting row of the table
System.Int32	col	Starting column of the table
IEnumerable<T>	data	IEnumerable in wich the data to insert is stored
System.String	title	The table's title
ExcelColor	color	The table's color
System.Boolean	showHeader	Defines if the table has header
System.String	Name	If given, creates a named range, with this name

Returns

Type	Description
ExcelRange	Range of the inserted data

Type Parameters

Name	Description
T	Type of inserted objects

InsertText(ExcelWorksheet, Int32, Int32, String, TextLevel)

Inserts text into the defined cell, and format to match text level

Declaration

public static void InsertText(this ExcelWorksheet ws, int row, int col, string text, TextLevel level = TextLevel.Normal)

Parameters

Type	Name	Description
ExcelWorksheet	ws	Worksheet in wich the text is inserted
System.Int32	row	Row of the cell
System.Int32	col	Column of the cell
System.String	text	Text to insert
TextLevel	level	Level of text

Merge(ExcelRangeBase)

Merge all cells into one in the range.

Declaration

public static void Merge(this ExcelRangeBase range)

Parameters

Type	Name	Description
ExcelRangeBase	range	Cells to merge