Nutstone.Excel.Provider 1.0.11

.NET 8.0 This package targets .NET 8.0. The package is compatible with this framework or higher.
There is a newer version of this package available.
See the version list below for details. 
dotnet add package Nutstone.Excel.Provider --version 1.0.11
                    


                    

                
NuGet\Install-Package Nutstone.Excel.Provider -Version 1.0.11
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package. 
<PackageReference Include="Nutstone.Excel.Provider" Version="1.0.11" />
For projects that support PackageReference, copy this XML node into the project file to reference the package. 
<PackageVersion Include="Nutstone.Excel.Provider" Version="1.0.11" />
                    


                            Directory.Packages.props
                        

                    

                
<PackageReference Include="Nutstone.Excel.Provider" />
                    


                            Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package. 
paket add Nutstone.Excel.Provider --version 1.0.11
                    


                    

                
#r "nuget: Nutstone.Excel.Provider, 1.0.11"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package. 
#:package Nutstone.Excel.Provider@1.0.11
#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package. 
#addin nuget:?package=Nutstone.Excel.Provider&version=1.0.11
                    


                            Install as a Cake Addin
                        

                    

                
#tool nuget:?package=Nutstone.Excel.Provider&version=1.0.11
                    


                            Install as a Cake Tool

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.

Implementation

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.

Styles and Formatting

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 data

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 :-

  • ExcelObjectFormatter (key "ObjectFormatter")
  • ExcelDataSetFormatter (key "ExcelDatasetFormatter")

ExcelObjectFormatter

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

ExcelDataSetFormatter

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

Custom Formatters

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

Simple Example

A simple example that creates a single row in a spreadsheet , with a style , and re-reads it into an ExcelDataset

Class ExcelManager (IExcelManager)

Primary entry point to instantiate an IExcelApplication

Class ExcelApplication (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<T> of data from the specified worksheet using the specified formatter type and options
GetExcelData Returns an IEnumerable<object> 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<object>. 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

Close

Closes the excel application and releases any resources

Returns IExcelApplication

var IExcelApplicationResult = Close();

** Parameters **

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.

Returns IExcelApplication

var IExcelApplicationResult = Open(stream, isEditable);

** Parameters **

stream

Type: [MemoryStream] Default: Not Applicable

isEditable

Type: [Boolean] Default: False

Open

Opens an existing excel or opendoc file

Returns IExcelApplication

var IExcelApplicationResult = Open(filePath, isEditable);

** Parameters **

filePath

Type: [String] Default: Not Applicable

isEditable

Type: [Boolean] Default: False

New

Creates a new excel application with the specified (or default) worksheet

Returns IExcelApplication

var IExcelApplicationResult = New(defaultWorkSheet);

** Parameters **

defaultWorkSheet

Type: [String] Default: Default

GetDefaultWorkSheet

Returns the default worksheet ExcelWorkSheet in the document. If no worksheets are available an exception is thrown.

Returns ExcelWorkSheet

var ExcelWorkSheetResult = GetDefaultWorkSheet();

** Parameters **

GetWorkSheet

Returns the specified WorkSheet ExcelWorkSheet. If the worksheet is not found an exception is thrown.

Returns ExcelWorkSheet

var ExcelWorkSheetResult = GetWorkSheet(workSheetName);

** Parameters **

workSheetName

Type: [String] Default: Not Applicable

RenameWorkSheet

Renames an existing worksheet returns ExcelApplication

Returns IExcelApplication

var IExcelApplicationResult = RenameWorkSheet(oldName, newName);

** Parameters **

oldName

Type: [String] Default: Not Applicable

newName

Type: [String] Default: Not Applicable

RemoveWorkSheet

Removes an existing worksheet by name. If the worksheet is not found an exception is thrown. returns ExcelApplication

Returns IExcelApplication

var IExcelApplicationResult = RemoveWorkSheet(workSheetName);

** Parameters **

workSheetName

Type: [String] Default: Not Applicable

Save

Saves an existing excel file. Returns ExcelApplication

Returns IExcelApplication

var IExcelApplicationResult = Save();

** Parameters **

SaveToStream

Saves the current document to a MemoryStream and closes the document. returns the MemoryStream

Returns MemoryStream

var MemoryStreamResult = SaveToStream();

** Parameters **

SaveAs

Save an existing excel application to a new file. If the file exists it is replaced. Returns ExcelApplication

Returns IExcelApplication

var IExcelApplicationResult = SaveAs(filePath);

** Parameters **

filePath

Type: [String] Default: Not Applicable

SaveToBase64

Saves the current excel spreahseet and returns it as a base64 string. ()

Returns String

var StringResult = SaveToBase64();

** Parameters **

AddStyle

Adds a style ExcelCellStyleOption to the current document. If the style already exists it is replaced. Returns ExcelApplication

Returns IExcelApplication

var IExcelApplicationResult = AddStyle(styleOptions);

** Parameters **

styleOptions

Type: [ExcelCellStyleOption] Default: Not Applicable

AddStyleCollection

Adds a style ExcelStyleCollection to the current document. If any style already exists it is replaced. Returns ExcelApplication

Returns IExcelApplication

var IExcelApplicationResult = AddStyleCollection(excelCellStyleCollection);

** Parameters **

excelCellStyleCollection

Type: [ExcelStyleOptionCollection] Default: Not Applicable

CloneStyle

Returns a cloned style from the given stylename (that must already be registered)

Returns ExcelCellStyleOption

var ExcelCellStyleOptionResult = CloneStyle(styleName);

** Parameters **

styleName

Type: [String] Default: Not Applicable

AddWorkSheet

Adds a new worksheet to the current document with the specified name. returns ExcelApplication

Returns IExcelApplication

var IExcelApplicationResult = AddWorkSheet(workSheetName);

** Parameters **

workSheetName

Type: [String] Default: Not Applicable

GetWorkSheetNames

Returns a list of all the worksheet names in the (current) document

Returns List`1

var List`1Result = GetWorkSheetNames();

** Parameters **

GetExcelData

Returns an ExcelDataSet object from all the worksheets in the current excel application

Returns ExcelDataSet

var ExcelDataSetResult = GetExcelData(options);

** Parameters **

options

Type: [ExcelFormattingOptions] Default: Not Applicable

GetExcelData

Returns a ExcelDataSet object that contains a list of DataTables from the given worksheet names

Returns ExcelDataSet

var ExcelDataSetResult = GetExcelData(workSheets, options);

** Parameters **

workSheets

Type: [IEnumerable<String>] Default: Not Applicable

options

Type: [ExcelFormattingOptions] Default: Not Applicable

GetExcelData

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

** Parameters **

excelMappings

Type: [ExcelMappingCollection] Default: Not Applicable

workSheetName

Type: [String] Default:

GetExcelData

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

** Parameters **

excelMappings

Type: [ExcelMappingCollection] Default: Not Applicable

type

Type: [Type] Default: Not Applicable

workSheetName

Type: [String] Default:

GetExcelData

Returns an IEnumerable<T> of data from the specified worksheet using the specified formatter type and options

Returns IEnumerable`1

var IEnumerable`1Result = GetExcelData(formatterType, options, workSheetName);

** Parameters **

formatterType

Type: [String] Default: Not Applicable

options

Type: [ExcelFormattingOptions] Default: Not Applicable

workSheetName

Type: [String] Default:

GetExcelData

Returns an IEnumerable<object> 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<object>. 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);

** Parameters **

obj

Type: [Object] Default: Not Applicable

formatterType

Type: [String] Default: Not Applicable

options

Type: [ExcelFormattingOptions] Default: Not Applicable

workSheetName

Type: [String] 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);

** Parameters **

dataset

Type: [ExcelDataSet] Default: Not Applicable

options

Type: [ExcelFormattingOptions] Default: Not Applicable

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<DataRow> 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<object> 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<DataRow> 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<object> 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; }
}
Product Compatible and additional computed target framework versions.
.NET net8.0 is compatible.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed.  net9.0 was computed.  net9.0-android was computed.  net9.0-browser was computed.  net9.0-ios was computed.  net9.0-maccatalyst was computed.  net9.0-macos was computed.  net9.0-tvos was computed.  net9.0-windows was computed.  net10.0 was computed.  net10.0-android was computed.  net10.0-browser was computed.  net10.0-ios was computed.  net10.0-maccatalyst was computed.  net10.0-macos was computed.  net10.0-tvos was computed.  net10.0-windows was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last Updated
1.0.13 93 6/27/2025
1.0.12 134 6/26/2025
1.0.11 134 6/26/2025
1.0.10 135 6/25/2025
1.0.9 130 6/24/2025
1.0.8 136 6/24/2025
1.0.7 282 6/12/2025
1.0.6 280 6/11/2025
1.0.5 284 6/10/2025
1.0.4 281 6/10/2025
1.0.3 218 6/9/2025
1.0.2 189 6/8/2025
1.0.1 100 6/6/2025
1.0.0 134 6/4/2025
0.0.1 98 6/6/2025