.Net Wrapper for opendocument.excel
License
—
Deps
3
Install Size
—
Vulns
✓ 0
Published
Jun 27, 2025
$ dotnet add package Nutstone.Excel.Provider.Net (8.0) wrapper for the DocumentFormat.OpenXml.Spreadsheet package. It provides methods and classes to manipulate Microsoft excel spreadsheets using typed classes and other input/output methods. it allows manipulation of an MS spreadsheet without the hosting process having the MS excel application installed and is compatable with version of MS Excel 2019 onwards.
As well as manipulating data there are methods to implement excel styles for formatting rows and cells within a created spreadsheet.
Services are implemented via a static extension to a servicecollection (but can also be instantiated by 'newing' up the required services)
so for example registering the services in your applicatiom startup :-
// register excel services in servicecollection
var services = new ServiceCollection();
services.RegisterExcelServices();
var builder = services.BuildServiceProvider();
// get excel manager
var excelManager = builder.GetRequiredService<IExcelManager>();
once the services are registered you can get the excelmanager (or inject it) and instantiate and instance of IExcelApplication like so:-
public class MyExcel : IMyExcel
{
private IExcelApplication excelApplication;
public MyExcel(IExcelManager excelManager)
{
this.excelApplication = excelManager.GetExcelApplication();
// .. do something with IExcelApplication
}
}
IExcelApplication is transient , so each instance of of excelManager.GetExcelApplication is unique , i.e you can have multiple instances within your application.
You can create styles either using the methods within IExcelApplication OR by creating a class that implements the IExcelCustomStyle interface and registering it in your service collection startup. for example :-
// build a custom style handler
public class UkCustomStyles : IExcelCustomStyle
{
public ExcelStyleOptionCollection GetStyles()
{
return ExcelStyleOptionCollection.Create()
.WithStyleOption(GetBaseStyle().Clone()
.WithName("UKDateStyle")
.WithCustomFormat("dd/mm/yyyy"))
.WithStyleOption(GetBaseStyle().Clone()
.WithName("AlignLeft"))
.WithStyleOption(GetBaseStyle().Clone()
.WithName("UKCurrencyStyle")
.WithCustomFormat("£#,##0.00"));
}
private ExcelCellStyleOption GetBaseStyle()
{
return ExcelCellStyleOption.Create()
.WithHorizontalAlignment(HorizontalAlignmentValues.Left);
}
}
// register with servicecollection
var services = new ServiceCollection()
.AddTransient<IExcelCustomStyle, UkCustomStyles>()
.RegisterExcelServices();
Inserting , reading and styling data is achieved directly using the methods defined in IExcelApplication (and it's worksheets) OR by formatters that implement the IExcelFormatter interface. There are two pre-built formatters :-
Formats generic or typed objects into or from excel worksheets.
public class TestObj
{
public string Name {get; set;}
public int Age {get; set;}
public DateTime Dob {get; set;}
}
var testData = new List<TestObj>() { ... ... ...}
var excelApplication = excelManager.GetExcelApplication();
var options = ExcelFormattingOptions.Create()
.WithHeaderRow(true)
.WithAutoFitColumns(true)
.WithFreezeTopRow(true)
.WithColumnStyles(....);
// write data
excelApplication.PopulateExcelData(testList, ExcelConstants.ObjectFormatter, options, "WorkSheet1");
// read data
var dataGeneric = excelApplication.GetExcelData<TestObj>(ExcelConstants.ObjectFormatter, options.WithStartRow(0), "WorkSheet1");
var name = dataGeneric[0].Name;
...
Formats an ExcelDataSet into or from excel worksheet(s). The ExcelDataSet is derived from a system.Data.Dataset and is compatable with any dataset (SQL etc)
the IExcelApplication exposes specific methods for this formatter :-
var excelApplication = excelManager.GetExcelApplication();
var excelDataSet = ExcelDataSet.Create("test")
.AddObjectArray(..someobjectarray.., columnStyles, "David", headerStyleSheet.Name);
// Write
var resultbool = excelApplication.PopulateExcelDataFromDataset(excelDataSet,
ExcelFormattingOptions.Create()
.WithStartRow(1)
.WithHeaderRow(true)
.WithAutoFitColumns(true)
// Read .WithFreezeTopRow(true));
var excelDataSet = excelApplication.GetExcelData(ExcelFormattingOptions.Create());
..
You can create new formatters by implementing the IExcelFormatter interface and registering it in your service collection Then calling either PopulateExcelData or GetExcelData with your formater key and any ExcelFormattingOptions.
IExcelFormatter interface
public interface IExcelFormatter
{
/// <summary>
/// the unique identifier for the formatter type.
/// </summary>
string FormatterType { get; }
/// <summary>
/// populate the given worksheet data with the object data.
/// the object must be a collection of objects or a single object.
/// </summary>
/// <param name="obj">a singleobject or IEnumerable<object></param>
/// <param name="data">the spreadheet data from an excel worksheet</param>
/// <param name="options">formatting options</param>
/// <returns>result of the populate operation</returns>
bool Populate(object obj, ExcelWorkSheetData data, ExcelFormattingOptions options);
/// <summary>
/// Get the data from the worksheet and return it as a collection of objects.
/// </summary>
/// <param name="data">the spreadheet data from an excel worksheet</param>
/// <param name="options">formatting options</param>
/// <param name="type">the type of the ienumerable object</param>
/// <returns>an ienuerable of type</returns>
IEnumerable<object> Get(ExcelWorkSheetData data, ExcelFormattingOptions options, Type type);
/// <summary>
/// returns an undefined object based on the worksheet data and formatting options.
/// </summary>
/// <param name="data">the spreadsheet data from an excel spreadsheet</param>
/// <param name="options">formatting options</param>
/// <returns>an object defined by the formatter</returns>
object Get(ExcelWorkSheetData data, ExcelFormattingOptions options);
}
A simple example that creates a single row in a spreadsheet , with a style , and re-reads it into an ExcelDataset
Primary entry point to instantiate an IExcelApplication
Excel main application. Create Via the Excel Manager
| Method | Description |
|---|---|
| Close | Closes the excel application and releases any resources |
| Open | Opens an existing excel or opendoc file from a MemoryStream. If the stream is empty an exception is thrown. If the stream is not editable it is opened as read-only. |
| Open | Opens an existing excel or opendoc file |
| New | Creates a new excel application with the specified (or default) worksheet |
| GetDefaultWorkSheet | Returns the default worksheet ExcelWorkSheet in the document. If no worksheets are available an exception is thrown. |
| GetWorkSheet | Returns the specified WorkSheet ExcelWorkSheet. If the worksheet is not found an exception is thrown. |
| RenameWorkSheet | Renames an existing worksheet returns ExcelApplication |
| RemoveWorkSheet | Removes an existing worksheet by name. If the worksheet is not found an exception is thrown. returns ExcelApplication |
| Save | Saves an existing excel file. Returns ExcelApplication |
| SaveToStream | Saves the current document to a MemoryStream and closes the document. returns the MemoryStream |
| SaveAs | Save an existing excel application to a new file. If the file exists it is replaced. Returns ExcelApplication |
| SaveToBase64 | Saves the current excel spreahseet and returns it as a base64 string. () |
| AddStyle | Adds a style ExcelCellStyleOption to the current document. If the style already exists it is replaced. Returns ExcelApplication |
| AddStyleCollection | Adds a style ExcelStyleCollection to the current document. If any style already exists it is replaced. Returns ExcelApplication |
| CloneStyle | Returns a cloned style from the given stylename (that must already be registered) |
| AddWorkSheet | Adds a new worksheet to the current document with the specified name. returns ExcelApplication |
| GetWorkSheetNames | Returns a list of all the worksheet names in the (current) document |
| GetExcelData | Returns an ExcelDataSet object from all the worksheets in the current excel application |
| GetExcelData | Returns a ExcelDataSet object that contains a list of DataTables from the given worksheet names |
| GetExcelData | Returns T that is mapped to specific rows and columns specified in ExcelMappingCollection from the specified (or default) worksheet |
| GetExcelData | Returns and object of type (Type) that is mapped to specific rows and columns specified in ExcelMappingCollection from the specified (or default) worksheet |
| GetExcelData | Returns an IEnumerable of data from the specified worksheet using the specified formatter type and options |
| GetExcelData | Returns an IEnumerable of data from the specified worksheet using the specified formatter type and options |
| PopulateExcelData | Populates the current document with data from an object. The object can be a single object or an IEnumerable. If the worksheet does not exist it is created. If the worksheet exists it is overwritten. |
| PopulateExcelDataFromDataset | Populates the current document with data from an ExcelDataSet. If the worksheet does not exist it is created. If the worksheet exists it is overwritten. |
| SetCellValue | Set the value of a cell at the given row and column index. if not specified the first worksheet is used/created |
| GetCellValue | Get the value of a cell at the given row and column index. if not specified the first worksheet is used/created |
| GetCellValue | Get the value T of a cell at the given row and column index. if not specified the first worksheet is used/created |
| MergeCells | Merges the given row and cellrange (columnStart, columnEnd) for the given (or default) worksheet |
| MergeRow | Merges all the cells in the given row into one cell for the given width (columnEnd-ColumnStart) and inserts the given value, in the given (or default worksheet) |
| GetMaxColumnCount | Returns the maximum (Populated) column from all of the rows in the specified (or default) worksheet |
| SetRowHeight | Sets the row height (in points) of the specified row for the given (or default) worksheet |
Closes the excel application and releases any resources
Returns IExcelApplication
var IExcelApplicationResult = Close();
Opens an existing excel or opendoc file from a MemoryStream. If the stream is empty an exception is thrown. If the stream is not editable it is opened as read-only.
Returns IExcelApplication
var IExcelApplicationResult = Open(stream, isEditable);
Type: [MemoryStream] Default: Not Applicable
Type: [Boolean] Default: False
Opens an existing excel or opendoc file
Returns IExcelApplication
var IExcelApplicationResult = Open(filePath, isEditable);
Type: [String] Default: Not Applicable
Type: [Boolean] Default: False
Creates a new excel application with the specified (or default) worksheet
Returns IExcelApplication
var IExcelApplicationResult = New(defaultWorkSheet);
Type: [String] Default: Default
Returns the default worksheet ExcelWorkSheet in the document. If no worksheets are available an exception is thrown.
Returns ExcelWorkSheet
var ExcelWorkSheetResult = GetDefaultWorkSheet();
Returns the specified WorkSheet ExcelWorkSheet. If the worksheet is not found an exception is thrown.
Returns ExcelWorkSheet
var ExcelWorkSheetResult = GetWorkSheet(workSheetName);
Type: [String] Default: Not Applicable
Renames an existing worksheet returns ExcelApplication
Returns IExcelApplication
var IExcelApplicationResult = RenameWorkSheet(oldName, newName);
Type: [String] Default: Not Applicable
Type: [String] Default: Not Applicable
Removes an existing worksheet by name. If the worksheet is not found an exception is thrown. returns ExcelApplication
Returns IExcelApplication
var IExcelApplicationResult = RemoveWorkSheet(workSheetName);
Type: [String] Default: Not Applicable
Saves an existing excel file. Returns ExcelApplication
Returns IExcelApplication
var IExcelApplicationResult = Save();
Saves the current document to a MemoryStream and closes the document. returns the MemoryStream
Returns MemoryStream
var MemoryStreamResult = SaveToStream();
Save an existing excel application to a new file. If the file exists it is replaced. Returns ExcelApplication
Returns IExcelApplication
var IExcelApplicationResult = SaveAs(filePath);
Type: [String] Default: Not Applicable
Saves the current excel spreahseet and returns it as a base64 string. ()
Returns String
var StringResult = SaveToBase64();
Adds a style ExcelCellStyleOption to the current document. If the style already exists it is replaced. Returns ExcelApplication
Returns IExcelApplication
var IExcelApplicationResult = AddStyle(styleOptions);
Type: [ExcelCellStyleOption] Default: Not Applicable
Adds a style ExcelStyleCollection to the current document. If any style already exists it is replaced. Returns ExcelApplication
Returns IExcelApplication
var IExcelApplicationResult = AddStyleCollection(excelCellStyleCollection);
Type: [ExcelStyleOptionCollection] Default: Not Applicable
Returns a cloned style from the given stylename (that must already be registered)
Returns ExcelCellStyleOption
var ExcelCellStyleOptionResult = CloneStyle(styleName);
Type: [String] Default: Not Applicable
Adds a new worksheet to the current document with the specified name. returns ExcelApplication
Returns IExcelApplication
var IExcelApplicationResult = AddWorkSheet(workSheetName);
Type: [String] Default: Not Applicable
Returns a list of all the worksheet names in the (current) document
Returns List`1
var List`1Result = GetWorkSheetNames();
Returns an ExcelDataSet object from all the worksheets in the current excel application
Returns ExcelDataSet
var ExcelDataSetResult = GetExcelData(options);
Type: [ExcelFormattingOptions] Default: Not Applicable
Returns a ExcelDataSet object that contains a list of DataTables from the given worksheet names
Returns ExcelDataSet
var ExcelDataSetResult = GetExcelData(workSheets, options);
Type: [IEnumerable<String>] Default: Not Applicable
Type: [ExcelFormattingOptions] Default: Not Applicable
Returns T that is mapped to specific rows and columns specified in ExcelMappingCollection from the specified (or default) worksheet
Returns T
var TResult = GetExcelData(excelMappings, workSheetName);
Type: [ExcelMappingCollection] Default: Not Applicable
Type: [String] Default:
Returns and object of type (Type) that is mapped to specific rows and columns specified in ExcelMappingCollection from the specified (or default) worksheet
Returns Object
var ObjectResult = GetExcelData(excelMappings, type, workSheetName);
Type: [ExcelMappingCollection] Default: Not Applicable
Type: [Type] Default: Not Applicable
Type: [String] Default:
Returns an IEnumerable of data from the specified worksheet using the specified formatter type and options
Returns IEnumerable`1
var IEnumerable`1Result = GetExcelData(formatterType, options, workSheetName);
Type: [String] Default: Not Applicable
Type: [ExcelFormattingOptions] Default: Not Applicable
Type: [String] Default:
Returns an IEnumerable of data from the specified worksheet using the specified formatter type and options
Returns IEnumerable`1
var IEnumerable`1Result = GetExcelData(formatterType, options, type, workSheetName);** Parameters **
formatterType
Type: [String] Default: Not Applicable
options
Type: [ExcelFormattingOptions] Default: Not Applicable
type
Type: [Type] Default: Not Applicable
workSheetName
Type: [String] Default:
PopulateExcelData
Populates the current document with data from an object. The object can be a single object or an IEnumerable. If the worksheet does not exist it is created. If the worksheet exists it is overwritten.
Returns Boolean
var BooleanResult = PopulateExcelData(obj, formatterType, options, workSheetName, callBack);** Parameters **
obj
Type: [Object] Default: Not Applicable
formatterType
Type: [String] Default: Not Applicable
options
Type: [ExcelFormattingOptions] Default: Not Applicable
workSheetName
Type: [String] Default:
callBack
Type: [] Default:
PopulateExcelDataFromDataset
Populates the current document with data from an ExcelDataSet. If the worksheet does not exist it is created. If the worksheet exists it is overwritten.
Returns Boolean
var BooleanResult = PopulateExcelDataFromDataset(dataset, options, callBack);** Parameters **
dataset
Type: [ExcelDataSet] Default: Not Applicable
options
Type: [ExcelFormattingOptions] Default: Not Applicable
callBack
Type: [] Default:
SetCellValue
Set the value of a cell at the given row and column index. if not specified the first worksheet is used/created
Returns IExcelApplication
var IExcelApplicationResult = SetCellValue(rowIndex, columnIndex, value, worksheetName, cellOptions);** Parameters **
rowIndex
Type: [Int32] Default: Not Applicable
columnIndex
Type: [Int32] Default: Not Applicable
value
Type: [Object] Default: Not Applicable
worksheetName
Type: [String] Default:
cellOptions
Type: [CellOptions] Default:
GetCellValue
Get the value of a cell at the given row and column index. if not specified the first worksheet is used/created
Returns Object
var ObjectResult = GetCellValue(rowIndex, columnIndex, worksheetName);** Parameters **
rowIndex
Type: [Int32] Default: Not Applicable
columnIndex
Type: [Int32] Default: Not Applicable
worksheetName
Type: [String] Default:
GetCellValue
Get the value T of a cell at the given row and column index. if not specified the first worksheet is used/created
Returns T
var TResult = GetCellValue(rowIndex, columnIndex, worksheetName);** Parameters **
rowIndex
Type: [Int32] Default: Not Applicable
columnIndex
Type: [Int32] Default: Not Applicable
worksheetName
Type: [String] Default:
MergeCells
Merges the given row and cellrange (columnStart, columnEnd) for the given (or default) worksheet
Returns IExcelApplication
var IExcelApplicationResult = MergeCells(row, columnStart, columnEnd, workSheetName);** Parameters **
row
Type: [Int32] Default: Not Applicable
columnStart
Type: [Int32] Default: Not Applicable
columnEnd
Type: [Int32] Default: Not Applicable
workSheetName
Type: [String] Default:
MergeRow
Merges all the cells in the given row into one cell for the given width (columnEnd-ColumnStart) and inserts the given value, in the given (or default worksheet)
Returns IExcelApplication
var IExcelApplicationResult = MergeRow(row, columnEnd, value, columnStart, styleName, workSheetName);** Parameters **
row
Type: [Int32] Default: Not Applicable
columnEnd
Type: [Int32] Default: Not Applicable
value
Type: [Object] Default: Not Applicable
columnStart
Type: [Int32] Default: 1
styleName
Type: [String] Default:
workSheetName
Type: [String] Default:
GetMaxColumnCount
Returns the maximum (Populated) column from all of the rows in the specified (or default) worksheet
Returns Int32
var Int32Result = GetMaxColumnCount(workSheetName);** Parameters **
workSheetName
Type: [String] Default:
SetRowHeight
Sets the row height (in points) of the specified row for the given (or default) worksheet
Returns IExcelApplication
var IExcelApplicationResult = SetRowHeight(row, height, workSheetName);** Parameters **
row
Type: [Int32] Default: Not Applicable
height
Type: [Double] Default: Not Applicable
workSheetName
Type: [String] Default:
Class ExcelWorkSheet ()
Wrapper for an excel spreadsheet
Method Description SetCellValue Sets the cell value based on an object value and CellOptions GetCellValue Gets the cell value based on T FreezeTopRows Freezes the top rows where rows indicates the number of rows from the top AutoFitColumns Auto fits all populated columns in the worksheet SetCellValue
Sets the cell value based on an object value and CellOptions
Returns ExcelWorkSheet
var ExcelWorkSheetResult = SetCellValue(rowIndex, columnIndex, value, cellOptions);** Parameters **
rowIndex
Type: [Int32] Default: Not Applicable
columnIndex
Type: [Int32] Default: Not Applicable
value
Type: [Object] Default: Not Applicable
cellOptions
Type: [CellOptions] Default:
GetCellValue
Gets the cell value based on T
Returns T
var TResult = GetCellValue(rowIndex, columnIndex);** Parameters **
rowIndex
Type: [Int32] Default: Not Applicable
columnIndex
Type: [Int32] Default: Not Applicable
FreezeTopRows
Freezes the top rows where rows indicates the number of rows from the top
Returns ExcelWorkSheet
var ExcelWorkSheetResult = FreezeTopRows(rows);** Parameters **
rows
Type: [Int32] Default: 1
AutoFitColumns
Auto fits all populated columns in the worksheet
Returns ExcelWorkSheet
var ExcelWorkSheetResult = AutoFitColumns(padding);** Parameters **
padding
Type: [Double] Default: 2
Class ExcelWorkSheetData ()
Main entry point to manipulate the contents of a worksheet (the data)
Method Description GetCell Returns a cell based on the cell reference GetRowByIndex Returns the row matched by index GetMaxColumnCount Returns the maximum column count of all rows in the worksheet GetOrCreateCell Returns or creates a cell at the given row and column name GetCellValue Gets the cell (string) value by row and column index GetCellValue Gets the cell T value by row and column index SetCellValue Sets the cell value T by row and column index SetCellValue Sets the cell value (object) by row and column index SetCellValue Sets the value of a cell based on rowindex abd columns name. returns ExcelWorkSheetData SetCellValue Sets the value of a cell based on rowindex abd columns name. returns ExcelWorkSheetData GetCellValue Gets the value on a cell based on rowindex and columnName GetCellValue Gets the value of a cell and returns it as T GetCellValueFromType Gets the value of a cell at row and cell and returns the value as Type type GetCell
Returns a cell based on the cell reference
Returns Cell
var CellResult = GetCell(cellReference);** Parameters **
cellReference
Type: [String] Default: Not Applicable
GetRowByIndex
Returns the row matched by index
Returns Row
var RowResult = GetRowByIndex(rowIndex);** Parameters **
rowIndex
Type: [Int32] Default: Not Applicable
GetMaxColumnCount
Returns the maximum column count of all rows in the worksheet
Returns Int32
var Int32Result = GetMaxColumnCount();** Parameters **
GetOrCreateCell
Returns or creates a cell at the given row and column name
Returns Cell
var CellResult = GetOrCreateCell(rowIndex, columnName);** Parameters **
rowIndex
Type: [UInt32] Default: Not Applicable
columnName
Type: [String] Default: Not Applicable
GetCellValue
Gets the cell (string) value by row and column index
Returns String
var StringResult = GetCellValue(rowIndex, columnIndex);** Parameters **
rowIndex
Type: [UInt32] Default: Not Applicable
columnIndex
Type: [Int32] Default: Not Applicable
GetCellValue
Gets the cell T value by row and column index
Returns T
var TResult = GetCellValue(rowIndex, columnIndex);** Parameters **
rowIndex
Type: [UInt32] Default: Not Applicable
columnIndex
Type: [Int32] Default: Not Applicable
SetCellValue
Sets the cell value T by row and column index
Returns ExcelWorkSheetData
var ExcelWorkSheetDataResult = SetCellValue(rowIndex, columnIndex, value, cellOptions);** Parameters **
rowIndex
Type: [UInt32] Default: Not Applicable
columnIndex
Type: [Int32] Default: Not Applicable
value
Type: [T] Default: Not Applicable
cellOptions
Type: [CellOptions] Default:
SetCellValue
Sets the cell value (object) by row and column index
Returns ExcelWorkSheetData
var ExcelWorkSheetDataResult = SetCellValue(rowIndex, columnIndex, value, cellOptions);** Parameters **
rowIndex
Type: [UInt32] Default: Not Applicable
columnIndex
Type: [Int32] Default: Not Applicable
value
Type: [Object] Default: Not Applicable
cellOptions
Type: [CellOptions] Default:
SetCellValue
Sets the value of a cell based on rowindex abd columns name. returns ExcelWorkSheetData
Returns ExcelWorkSheetData
var ExcelWorkSheetDataResult = SetCellValue(rowIndex, columnName, value, cellOptions);** Parameters **
rowIndex
Type: [UInt32] Default: Not Applicable
columnName
Type: [String] Default: Not Applicable
value
Type: [T] Default: Not Applicable
cellOptions
Type: [CellOptions] Default:
SetCellValue
Sets the value of a cell based on rowindex abd columns name. returns ExcelWorkSheetData
Returns ExcelWorkSheetData
var ExcelWorkSheetDataResult = SetCellValue(rowIndex, columnName, value, cellOptions);** Parameters **
rowIndex
Type: [UInt32] Default: Not Applicable
columnName
Type: [String] Default: Not Applicable
value
Type: [Object] Default: Not Applicable
cellOptions
Type: [CellOptions] Default:
GetCellValue
Gets the value on a cell based on rowindex and columnName
Returns String
var StringResult = GetCellValue(rowIndex, columnName);** Parameters **
rowIndex
Type: [UInt32] Default: Not Applicable
columnName
Type: [String] Default: Not Applicable
GetCellValue
Gets the value of a cell and returns it as T
Returns T
var TResult = GetCellValue(rowIndex, columnName);** Parameters **
rowIndex
Type: [UInt32] Default: Not Applicable
columnName
Type: [String] Default: Not Applicable
GetCellValueFromType
Gets the value of a cell at row and cell and returns the value as Type type
Returns Object
var ObjectResult = GetCellValueFromType(rowIndex, columnIndex, type);** Parameters **
rowIndex
Type: [UInt32] Default: Not Applicable
columnIndex
Type: [Int32] Default: Not Applicable
type
Type: [Type] Default: Not Applicable
Class ExcelColumnStyleCollection (IList<ExcelColumnStyle>)
Excel Column Style Collection
Method Description AddColumnStyle Add a column style to the collection GetStyleByName Returns a ExcelColumnStyle object from the given style name AddColumnStyle Adds a column style to the collection SetHeaderName Sets the header name for that is different from the property type name SetHeaderAndStyle Sets the header name and style name for the column SetStyleName Sets the column style name SetColumnIndex Sets the column index of the column for mapping during read operations. Columns start at 1. SetHeaderStyle Sets the header style name Create Creates an empty collection CreateFromType Creates a collection of column styles from a type's properties. Each property will be added as a column style with its name and header name set to the property name. CreateFromDataTable Creates a collection of column styles from a DataTable column properties. Each property will be added as a column style with its name and header name set to the property name. AddColumnStyle
Add a column style to the collection
Returns ExcelColumnStyleCollection
var ExcelColumnStyleCollectionResult = AddColumnStyle(name, headerName, styleName, type);** Parameters **
name
Type: [String] Default: Not Applicable
headerName
Type: [String] Default: Not Applicable
styleName
Type: [String] Default: Not Applicable
type
Type: [Type] Default: Not Applicable
GetStyleByName
Returns a ExcelColumnStyle object from the given style name
Returns ExcelColumnStyle
var ExcelColumnStyleResult = GetStyleByName(name);** Parameters **
name
Type: [String] Default: Not Applicable
AddColumnStyle
Adds a column style to the collection
Returns ExcelColumnStyleCollection
var ExcelColumnStyleCollectionResult = AddColumnStyle(columnStyle);** Parameters **
columnStyle
Type: [ExcelColumnStyle] Default: Not Applicable
SetHeaderName
Sets the header name for that is different from the property type name
Returns ExcelColumnStyleCollection
var ExcelColumnStyleCollectionResult = SetHeaderName(name, headerName);** Parameters **
name
Type: [String] Default: Not Applicable
headerName
Type: [String] Default: Not Applicable
SetHeaderAndStyle
Sets the header name and style name for the column
Returns ExcelColumnStyleCollection
var ExcelColumnStyleCollectionResult = SetHeaderAndStyle(name, headerName, styleName);** Parameters **
name
Type: [String] Default: Not Applicable
headerName
Type: [String] Default: Not Applicable
styleName
Type: [String] Default: Not Applicable
SetStyleName
Sets the column style name
Returns ExcelColumnStyleCollection
var ExcelColumnStyleCollectionResult = SetStyleName(name, styleName);** Parameters **
name
Type: [String] Default: Not Applicable
styleName
Type: [String] Default: Not Applicable
SetColumnIndex
Sets the column index of the column for mapping during read operations. Columns start at 1.
Returns ExcelColumnStyleCollection
var ExcelColumnStyleCollectionResult = SetColumnIndex(name, columnIndex);** Parameters **
name
Type: [String] Default: Not Applicable
columnIndex
Type: [Int32] Default: Not Applicable
SetHeaderStyle
Sets the header style name
Returns ExcelColumnStyleCollection
var ExcelColumnStyleCollectionResult = SetHeaderStyle(headerStyleName);** Parameters **
headerStyleName
Type: [String] Default: Not Applicable
Create
Creates an empty collection
Returns ExcelColumnStyleCollection
var ExcelColumnStyleCollectionResult = Create();** Parameters **
CreateFromType
Creates a collection of column styles from a type's properties. Each property will be added as a column style with its name and header name set to the property name.
Returns ExcelColumnStyleCollection
var ExcelColumnStyleCollectionResult = CreateFromType(type);** Parameters **
type
Type: [Type] Default: Not Applicable
CreateFromDataTable
Creates a collection of column styles from a DataTable column properties. Each property will be added as a column style with its name and header name set to the property name.
Returns ExcelColumnStyleCollection
var ExcelColumnStyleCollectionResult = CreateFromDataTable(dataTable);** Parameters **
dataTable
Type: [DataTable] Default: Not Applicable
Class ExcelWorkSheetCollection (IList<ExcelWorkSheet>)
Collection of ExcelWorksheets
Method Description GetWorkSheetByName Returns the first instance of an ExcelWorkSheet with the specified name AddWorkSheet Adds a new worksheet to an Open excel application workbook GetWorkSheetByName
Returns the first instance of an ExcelWorkSheet with the specified name
Returns ExcelWorkSheet
var ExcelWorkSheetResult = GetWorkSheetByName(name);** Parameters **
name
Type: [String] Default: Not Applicable
AddWorkSheet
Adds a new worksheet to an Open excel application workbook
Returns ExcelWorkSheet
var ExcelWorkSheetResult = AddWorkSheet(name);** Parameters **
name
Type: [String] Default: Not Applicable
Class ExcelDataSet (IComponent)
Excel version of a System.Data.Dataset
Method Description AddDataTable Adds an existing DataTable to the ExcelDataset GetExcelColumnStylesFromTable Returns an ExcelColumnStyleCollection populated from a DataTable (name) in the ExcelDataset GetExcelColumnStylesFromTableIndex Returns an ExcelColumnStyleCollection populated from a DataTable (index) in the ExcelDataset AddDataRowsToTable Adds a table from an IEnumerable to the ExcelDataSet SetTableOptions Sets the ExcelColumnStyleCollection options and the headerrowstyle of the given table GetTableByName Returns the table (name) in the ExcelDataSet AddObjectArray Adds the given IEnumerable as a table with rows Create Creates an empty ExcelDataSet CreateFromDataSet Creates a new ExcelDataset from the given DataSet AddDataTable
Adds an existing DataTable to the ExcelDataset
Returns ExcelDataSet
var ExcelDataSetResult = AddDataTable(table, workSheetName, excelColumnStyles, headerRowStyle);** Parameters **
table
Type: [DataTable] Default: Not Applicable
workSheetName
Type: [String] Default: Not Applicable
excelColumnStyles
Type: [ExcelColumnStyleCollection] Default:
headerRowStyle
Type: [String] Default:
GetExcelColumnStylesFromTable
Returns an ExcelColumnStyleCollection populated from a DataTable (name) in the ExcelDataset
Returns ExcelColumnStyleCollection
var ExcelColumnStyleCollectionResult = GetExcelColumnStylesFromTable(tableName);** Parameters **
tableName
Type: [String] Default: Not Applicable
GetExcelColumnStylesFromTableIndex
Returns an ExcelColumnStyleCollection populated from a DataTable (index) in the ExcelDataset
Returns ExcelColumnStyleCollection
var ExcelColumnStyleCollectionResult = GetExcelColumnStylesFromTableIndex(tableIndex);** Parameters **
tableIndex
Type: [Int32] Default: Not Applicable
AddDataRowsToTable
Adds a table from an IEnumerable to the ExcelDataSet
Returns ExcelDataSet
var ExcelDataSetResult = AddDataRowsToTable(rows, excelColumnStyles, tableName, headerRowStyle);** Parameters **
rows
Type: [IEnumerable<DataRow>] Default: Not Applicable
excelColumnStyles
Type: [ExcelColumnStyleCollection] Default:
tableName
Type: [String] Default:
headerRowStyle
Type: [String] Default:
SetTableOptions
Sets the ExcelColumnStyleCollection options and the headerrowstyle of the given table
Returns ExcelDataSet
var ExcelDataSetResult = SetTableOptions(tableName, excelColumnStyles, headerRowStyle);** Parameters **
tableName
Type: [String] Default: Not Applicable
excelColumnStyles
Type: [ExcelColumnStyleCollection] Default:
headerRowStyle
Type: [String] Default:
GetTableByName
Returns the table (name) in the ExcelDataSet
Returns DataTable
var DataTableResult = GetTableByName(name);** Parameters **
name
Type: [String] Default: Not Applicable
AddObjectArray
Adds the given IEnumerable as a table with rows
Returns ExcelDataSet
var ExcelDataSetResult = AddObjectArray(rows, styles, workSheetName, headerStyleSheetName);** Parameters **
rows
Type: [IEnumerable<Object>] Default: Not Applicable
styles
Type: [ExcelColumnStyleCollection] Default: Not Applicable
workSheetName
Type: [String] Default: Not Applicable
headerStyleSheetName
Type: [String] Default: Not Applicable
Create
Creates an empty ExcelDataSet
Returns ExcelDataSet
var ExcelDataSetResult = Create(name);** Parameters **
name
Type: [String] Default: Not Applicable
CreateFromDataSet
Creates a new ExcelDataset from the given DataSet
Returns ExcelDataSet
var ExcelDataSetResult = CreateFromDataSet(dataSet);** Parameters **
dataSet
Type: [DataSet] Default: Not Applicable
Models
CellOptions
public class CellOptions { public String? StyleIndexName { get; set; } public ExcelStyleHelper ExcelStyleHelper { get; set; } }ExcelCellStyleOption
public class ExcelCellStyleOption { public String Name { get; set; } public Nullable\<ExcelNumberFormat>? NumberFormat { get; set; } public String? CustomNumberFormat { get; set; } public Nullable\<HorizontalAlignmentValues>? HorizontalAlignment { get; set; } public Nullable\<VerticalAlignmentValues>? VerticalAlignment { get; set; } public Boolean WrapText { get; set; } public Color BackgroundColor { get; set; } public Boolean Bold { get; set; } public Boolean Italic { get; set; } public Nullable\<UInt32>? FontSize { get; set; } public String FontName { get; set; } public Nullable\<Color>? FontColor { get; set; } public String? FontColorHex { get; } public Boolean WithBorder { get; set; } public Color BorderColor { get; set; } public String BackgroundColorHex { get; } public String BorderColorHex { get; } }ExcelColumnStyle
public class ExcelColumnStyle { public String Name { get; set; } public String HeaderName { get; set; } public String StyleName { get; set; } public String HeaderStyleName { get; set; } public Type ColumnType { get; set; } public Int32 ColumnIndex { get; set; } }ExcelFormattingOptions
public class ExcelFormattingOptions { public Int32 StartRow { get; set; } public Int32 EndRow { get; set; } public Boolean HeaderRow { get; set; } public Boolean FreezeTopRow { get; set; } public Boolean AutoFitColumns { get; set; } public String HeaderRowStyle { get; set; } public Nullable\<Int32>? HeaderRowHeight { get; set; } public Double Padding { get; set; } public Int32 NumberOfRowsToFreeze { get; set; } public Boolean RemoveDefaultWorkSheet { get; set; } public ExcelFilterCollection Filters { get; set; } public ExcelMappingCollection Mapping { get; set; } public ExcelColumnStyleCollection ColumnStyles { get; set; } }ExcelMapping
public class ExcelMapping { public Int32 Column { get; set; } public Int32 Row { get; set; } public String PropertyName { get; set; } }ExcelStyleOptionCollection
public class ExcelStyleOptionCollection { public Int32 Capacity { get; set; } public Int32 Count { get; } public ExcelCellStyleOption Item { get; set; } }