Table of Contents

Class NpoiExtensions

Namespace
WeihanLi.Npoi
Assembly
WeihanLi.Npoi.dll
public static class NpoiExtensions
Inheritance
NpoiExtensions
Inherited Members

Methods

GetCellCollection(IRow)

Get Row Cell Collection

public static NpoiCellCollection GetCellCollection(this IRow row)

Parameters

row IRow

excel sheet row

Returns

NpoiCellCollection

row collection

GetCellValue(ICell?, Type, IFormulaEvaluator?)

GetCellValue

public static object? GetCellValue(this ICell? cell, Type propertyType, IFormulaEvaluator? formulaEvaluator = null)

Parameters

cell ICell

cell

propertyType Type

propertyType

formulaEvaluator IFormulaEvaluator

formulaEvaluator

Returns

object

cellValue

GetCellValue<T>(ICell?, IFormulaEvaluator?)

GetCellValue

public static T GetCellValue<T>(this ICell? cell, IFormulaEvaluator? formulaEvaluator = null)

Parameters

cell ICell

cell

formulaEvaluator IFormulaEvaluator

Returns

T

typed cell value

Type Parameters

T

Type

GetFormulaEvaluator(IWorkbook)

get workbook IFormulaEvaluator

public static IFormulaEvaluator GetFormulaEvaluator(this IWorkbook workbook)

Parameters

workbook IWorkbook

workbook

Returns

IFormulaEvaluator

GetPicturesAndPosition(ISheet)

get pictures with position in current sheet

public static Dictionary<CellPosition, IPictureData> GetPicturesAndPosition(this ISheet sheet)

Parameters

sheet ISheet

sheet

Returns

Dictionary<CellPosition, IPictureData>

GetRowCollection(ISheet)

Get Sheet Row Collection

public static NpoiRowCollection GetRowCollection(this ISheet sheet)

Parameters

sheet ISheet

excel sheet

Returns

NpoiRowCollection

row collection

GetWorkbookWithAutoSplitSheet(DataTable, ExcelFormat, ExcelSetting?)

GetWorkbookWithAutoSplitSheet

public static IWorkbook GetWorkbookWithAutoSplitSheet(this DataTable dataTable, ExcelFormat excelFormat, ExcelSetting? excelSetting = null)

Parameters

dataTable DataTable

dataTable

excelFormat ExcelFormat

excel format

excelSetting ExcelSetting

excelSetting

Returns

IWorkbook

excel workbook with data

GetWorkbookWithAutoSplitSheet<TEntity>(IList<TEntity>, ExcelFormat)

GetWorkbookWithAutoSplitSheet

public static IWorkbook GetWorkbookWithAutoSplitSheet<TEntity>(this IList<TEntity> entityList, ExcelFormat excelFormat)

Parameters

entityList IList<TEntity>

entity list

excelFormat ExcelFormat

excel format

Returns

IWorkbook

excel workbook with data

Type Parameters

TEntity

entity type

ImportData(ISheet, DataTable?)

Import dataTable data

public static void ImportData(this ISheet sheet, DataTable? dataTable)

Parameters

sheet ISheet

sheet

dataTable DataTable

dataTable

ImportData<TEntity>(ISheet, IEnumerable<TEntity>)

import entityList to sheet

public static ISheet ImportData<TEntity>(this ISheet sheet, IEnumerable<TEntity> list)

Parameters

sheet ISheet

sheet

list IEnumerable<TEntity>

entityList

Returns

ISheet

Type Parameters

TEntity

EntityType

ImportData<TEntity>(ISheet, IEnumerable<TEntity>, int)

import entityList to sheet

public static ISheet ImportData<TEntity>(this ISheet sheet, IEnumerable<TEntity> list, int sheetIndex)

Parameters

sheet ISheet

sheet

list IEnumerable<TEntity>

entityList

sheetIndex int

sheetIndex

Returns

ISheet

Type Parameters

TEntity

EntityType

ImportData<TEntity>(ISheet, DataTable)

import dataTable to sheet

public static ISheet ImportData<TEntity>(this ISheet sheet, DataTable dataTable)

Parameters

sheet ISheet

sheet

dataTable DataTable

dataTable

Returns

ISheet

Type Parameters

TEntity

EntityType

ImportData<TEntity>(ISheet, DataTable, int)

import dataTable to sheet

public static ISheet ImportData<TEntity>(this ISheet sheet, DataTable dataTable, int sheetIndex)

Parameters

sheet ISheet

sheet

dataTable DataTable

dataTable

sheetIndex int

sheetIndex

Returns

ISheet

Type Parameters

TEntity

EntityType

ImportData<TEntity>(IWorkbook, IEnumerable<TEntity>)

import entityList to workbook first sheet

public static int ImportData<TEntity>(this IWorkbook workbook, IEnumerable<TEntity> list)

Parameters

workbook IWorkbook

workbook

list IEnumerable<TEntity>

entityList

Returns

int

Type Parameters

TEntity

TEntity

ImportData<TEntity>(IWorkbook, IEnumerable<TEntity>, int)

import entityList to workbook sheet

public static int ImportData<TEntity>(this IWorkbook workbook, IEnumerable<TEntity> list, int sheetIndex)

Parameters

workbook IWorkbook

workbook

list IEnumerable<TEntity>

entityList

sheetIndex int

sheetIndex

Returns

int

the sheet LastRowNum

Type Parameters

TEntity

TEntity

ImportData<TEntity>(IWorkbook, DataTable)

import dataTable to workbook first sheet

public static int ImportData<TEntity>(this IWorkbook workbook, DataTable dataTable)

Parameters

workbook IWorkbook

workbook

dataTable DataTable

dataTable

Returns

int

Type Parameters

TEntity

TEntity

ImportData<TEntity>(IWorkbook, DataTable, int)

import dataTable to workbook first sheet

public static int ImportData<TEntity>(this IWorkbook workbook, DataTable dataTable, int sheetIndex)

Parameters

workbook IWorkbook

workbook

dataTable DataTable

dataTable

sheetIndex int

sheetIndex

Returns

int

the sheet LastRowNum

Type Parameters

TEntity

TEntity

SetCellValue(ICell, object?)

SetCellValue

public static void SetCellValue(this ICell cell, object? value)

Parameters

cell ICell

ICell

value object

value

SetCellValue(ICell, object?, string?)

SetCellValue

public static void SetCellValue(this ICell cell, object? value, string? formatter)

Parameters

cell ICell

ICell

value object

value

formatter string

formatter

ToDataSet(IWorkbook, bool, int?)

Workbook2ToDataSet

public static DataSet ToDataSet(this IWorkbook workbook, bool removeEmptyRows = false, int? maxColumns = null)

Parameters

workbook IWorkbook

excel workbook

removeEmptyRows bool

removeEmptyRows

maxColumns int?

maxColumns

Returns

DataSet

DataSet

ToDataSet(IWorkbook, int, bool, int?)

Workbook2ToDataSet

public static DataSet ToDataSet(this IWorkbook workbook, int headerRowIndex, bool removeEmptyRows = false, int? maxColumns = null)

Parameters

workbook IWorkbook

excel workbook

headerRowIndex int

headerRowIndex

removeEmptyRows bool

removeEmptyRows

maxColumns int?

maxColumns

Returns

DataSet

DataSet

ToDataTable(ISheet, bool, int?)

Sheet2DataTable

public static DataTable ToDataTable(this ISheet sheet, bool removeEmptyRows = false, int? maxColumns = null)

Parameters

sheet ISheet

excel sheet

removeEmptyRows bool

removeEmptyRows

maxColumns int?

maxColumns

Returns

DataTable

DataTable

ToDataTable(ISheet, int, bool, int?)

Sheet2DataTable

public static DataTable ToDataTable(this ISheet sheet, int headerRowIndex, bool removeEmptyRows = false, int? maxColumns = null)

Parameters

sheet ISheet

excel sheet

headerRowIndex int

headerRowIndex

removeEmptyRows bool

removeEmptyRows

maxColumns int?

maxColumns

Returns

DataTable

DataTable

ToDataTable(IWorkbook, bool, int?)

Workbook2ToDataTable

public static DataTable ToDataTable(this IWorkbook workbook, bool removeEmptyRows = false, int? maxColumns = null)

Parameters

workbook IWorkbook

excel workbook

removeEmptyRows bool

removeEmptyRows

maxColumns int?

maxColumns

Returns

DataTable

DataTable

ToDataTable(IWorkbook, int, int, bool, int?)

Workbook2ToDataTable

public static DataTable ToDataTable(this IWorkbook workbook, int sheetIndex, int headerRowIndex, bool removeEmptyRows = false, int? maxColumns = null)

Parameters

workbook IWorkbook

excel workbook

sheetIndex int

sheetIndex

headerRowIndex int

headerRowIndex

removeEmptyRows bool

removeEmptyRows

maxColumns int?

maxColumns

Returns

DataTable

DataTable

ToEntities<TEntity>(ISheet, int) 

public static IEnumerable<TEntity?> ToEntities<TEntity>(this ISheet sheet, int sheetIndex) where TEntity : new()

Parameters

sheet ISheet
sheetIndex int

Returns

IEnumerable<TEntity>

Type Parameters

TEntity

ToEntities<TEntity>(IWorkbook, int) 

public static IEnumerable<TEntity?> ToEntities<TEntity>(this IWorkbook workbook, int sheetIndex) where TEntity : new()

Parameters

workbook IWorkbook
sheetIndex int

Returns

IEnumerable<TEntity>

Type Parameters

TEntity

ToEntityListWithValidationResult<TEntity>(ISheet, int, IValidator<TEntity>?)

Sheet2EntityList and validate

public static (List<TEntity?> EntityList, Dictionary<int, ValidationResult> ValidationResults) ToEntityListWithValidationResult<TEntity>(this ISheet sheet, int sheetIndex = 0, IValidator<TEntity>? validator = null) where TEntity : new()

Parameters

sheet ISheet

excel sheet

sheetIndex int

sheetIndex

validator IValidator<TEntity>

validator

Returns

(List<TEntity> EntityList, Dictionary<int, ValidationResult> ValidationResults)

entity list and validation results

Type Parameters

TEntity

EntityType

ToEntityList<TEntity>(ISheet)

Sheet2EntityList

public static List<TEntity?> ToEntityList<TEntity>(this ISheet sheet) where TEntity : new()

Parameters

sheet ISheet

excel sheet

Returns

List<TEntity>

entity list

Type Parameters

TEntity

EntityType

ToEntityList<TEntity>(ISheet, int)

Sheet2EntityList

public static List<TEntity?> ToEntityList<TEntity>(this ISheet sheet, int sheetIndex) where TEntity : new()

Parameters

sheet ISheet

excel sheet

sheetIndex int

sheetIndex

Returns

List<TEntity>

entity list

Type Parameters

TEntity

EntityType

ToEntityList<TEntity>(IWorkbook)

Workbook2EntityList

public static List<TEntity?> ToEntityList<TEntity>(this IWorkbook workbook) where TEntity : new()

Parameters

workbook IWorkbook

excel workbook

Returns

List<TEntity>

entity list

Type Parameters

TEntity

EntityType

ToEntityList<TEntity>(IWorkbook, int)

Workbook2EntityList

public static List<TEntity?> ToEntityList<TEntity>(this IWorkbook workbook, int sheetIndex) where TEntity : new()

Parameters

workbook IWorkbook

excel workbook

sheetIndex int

sheetIndex

Returns

List<TEntity>

entity list

Type Parameters

TEntity

EntityType

ToExcelBytes(IWorkbook)

ToExcelBytes

public static byte[] ToExcelBytes(this IWorkbook workbook)

Parameters

workbook IWorkbook

workbook

Returns

byte[]

excel bytes

ToExcelBytes(IWorkbook, bool)

ToExcelBytes

public static byte[] ToExcelBytes(this IWorkbook workbook, bool closeWorkbook)

Parameters

workbook IWorkbook

workbook

closeWorkbook bool

whether to close the workbook

Returns

byte[]

excel bytes

ToExcelBytes(DataTable)

DataTable2ExcelBytes(*.xlsx by default)

public static byte[] ToExcelBytes(this DataTable dataTable)

Parameters

dataTable DataTable

dataTable

Returns

byte[]

ToExcelBytes(DataTable, ExcelFormat)

DataTable2ExcelBytes

public static byte[] ToExcelBytes(this DataTable dataTable, ExcelFormat excelFormat)

Parameters

dataTable DataTable

dataTable

excelFormat ExcelFormat

excel格式

Returns

byte[]

ToExcelBytes(DataTable, ExcelFormat, ExcelSetting?)

DataTable2ExcelBytes

public static byte[] ToExcelBytes(this DataTable dataTable, ExcelFormat excelFormat, ExcelSetting? excelSetting)

Parameters

dataTable DataTable

dataTable

excelFormat ExcelFormat

excelFormat

excelSetting ExcelSetting

excelSetting

Returns

byte[]

ToExcelBytesByTemplate<TEntity>(IEnumerable<TEntity>, ISheet, object?)

export excel via template

public static byte[] ToExcelBytesByTemplate<TEntity>(this IEnumerable<TEntity> entities, ISheet templateSheet, object? extraData = null)

Parameters

entities IEnumerable<TEntity>

entities

templateSheet ISheet
extraData object

extraData

Returns

byte[]

exported excel bytes

Type Parameters

TEntity

Entity Type

ToExcelBytesByTemplate<TEntity>(IEnumerable<TEntity>, IWorkbook, int, object?)

export excel via template

public static byte[] ToExcelBytesByTemplate<TEntity>(this IEnumerable<TEntity> entities, IWorkbook templateWorkbook, int sheetIndex = 0, object? extraData = null)

Parameters

entities IEnumerable<TEntity>

entities

templateWorkbook IWorkbook

templateWorkbook

sheetIndex int

sheetIndex

extraData object

extraData

Returns

byte[]

exported excel bytes

Type Parameters

TEntity

Entity Type

ToExcelBytesByTemplate<TEntity>(IEnumerable<TEntity>, byte[], ExcelFormat, int, object?)

export excel via template

public static byte[] ToExcelBytesByTemplate<TEntity>(this IEnumerable<TEntity> entities, byte[] templateBytes, ExcelFormat excelFormat = ExcelFormat.Xls, int sheetIndex = 0, object? extraData = null)

Parameters

entities IEnumerable<TEntity>

entities

templateBytes byte[]

templateBytes

excelFormat ExcelFormat

excelFormat

sheetIndex int

sheetIndex,zero by default

extraData object

extraData

Returns

byte[]

exported excel bytes

Type Parameters

TEntity

Entity Type

ToExcelBytesByTemplate<TEntity>(IEnumerable<TEntity>, Stream, ExcelFormat, int, object?)

export excel via template

public static byte[] ToExcelBytesByTemplate<TEntity>(this IEnumerable<TEntity> entities, Stream templateStream, ExcelFormat excelFormat = ExcelFormat.Xls, int sheetIndex = 0, object? extraData = null)

Parameters

entities IEnumerable<TEntity>

entities

templateStream Stream

templateStream

excelFormat ExcelFormat

excelFormat

sheetIndex int

sheetIndex,zero by default

extraData object

extraData

Returns

byte[]

exported excel bytes

Type Parameters

TEntity

Entity Type

ToExcelBytesByTemplate<TEntity>(IEnumerable<TEntity>, string, int, object?)

export excel via template

public static byte[] ToExcelBytesByTemplate<TEntity>(this IEnumerable<TEntity> entities, string templatePath, int sheetIndex = 0, object? extraData = null)

Parameters

entities IEnumerable<TEntity>

entities

templatePath string

templatePath

sheetIndex int

sheetIndex,zero by default

extraData object

extraData

Returns

byte[]

exported excel bytes

Type Parameters

TEntity

Entity Type

ToExcelBytes<TEntity>(IEnumerable<TEntity>)

EntityList2ExcelBytes(*.xls by default)

public static byte[] ToExcelBytes<TEntity>(this IEnumerable<TEntity> entityList)

Parameters

entityList IEnumerable<TEntity>

entityList

Returns

byte[]

Type Parameters

TEntity

EntityType

ToExcelBytes<TEntity>(IEnumerable<TEntity>, ExcelFormat)

EntityList2ExcelBytes

public static byte[] ToExcelBytes<TEntity>(this IEnumerable<TEntity> entityList, ExcelFormat excelFormat)

Parameters

entityList IEnumerable<TEntity>

entityList

excelFormat ExcelFormat

excelFormat

Returns

byte[]

Type Parameters

TEntity

EntityType

ToExcelBytes<TEntity>(IEnumerable<TEntity>, ExcelFormat, int)

EntityList2ExcelBytes

public static byte[] ToExcelBytes<TEntity>(this IEnumerable<TEntity> entityList, ExcelFormat excelFormat, int sheetIndex)

Parameters

entityList IEnumerable<TEntity>

entityList

excelFormat ExcelFormat

excelFormat

sheetIndex int

sheetIndex

Returns

byte[]

Type Parameters

TEntity

EntityType

ToExcelBytes<TEntity>(IList<TEntity>, ExcelFormat)

EntityList2ExcelBytes

public static byte[] ToExcelBytes<TEntity>(this IList<TEntity> entityList, ExcelFormat excelFormat = ExcelFormat.Xls)

Parameters

entityList IList<TEntity>

entityList

excelFormat ExcelFormat

excelFormat

Returns

byte[]

Type Parameters

TEntity

EntityType

ToExcelFile(DataTable, string)

export DataTable to excel file

public static void ToExcelFile(this DataTable dataTable, string excelPath)

Parameters

dataTable DataTable

dataTable

excelPath string

excelPath

ToExcelFile(DataTable, string, ExcelSetting?)

export DataTable to excel file

public static void ToExcelFile(this DataTable dataTable, string excelPath, ExcelSetting? excelSetting)

Parameters

dataTable DataTable

dataTable

excelPath string

excelPath

excelSetting ExcelSetting

excelSetting

ToExcelFileByTemplate<TEntity>(IEnumerable<TEntity>, IWorkbook, string, int, object?)

export excel via template

public static void ToExcelFileByTemplate<TEntity>(this IEnumerable<TEntity> entities, IWorkbook templateWorkbook, string excelPath, int sheetIndex = 0, object? extraData = null)

Parameters

entities IEnumerable<TEntity>

entities

templateWorkbook IWorkbook

templateWorkbook

excelPath string
sheetIndex int

sheetIndex

extraData object

extraData

Type Parameters

TEntity

Entity Type

ToExcelFileByTemplate<TEntity>(IEnumerable<TEntity>, byte[], string, ExcelFormat, int, object?)

export excel via template

public static void ToExcelFileByTemplate<TEntity>(this IEnumerable<TEntity> entities, byte[] templateBytes, string excelPath, ExcelFormat excelFormat = ExcelFormat.Xls, int sheetIndex = 0, object? extraData = null)

Parameters

entities IEnumerable<TEntity>

entities

templateBytes byte[]

templateBytes

excelPath string

excelPath

excelFormat ExcelFormat

excelFormat

sheetIndex int

sheetIndex,zero by default

extraData object

extraData

Type Parameters

TEntity

Entity Type

ToExcelFileByTemplate<TEntity>(IEnumerable<TEntity>, string, string, int, object?)

export excel via template

public static void ToExcelFileByTemplate<TEntity>(this IEnumerable<TEntity> entities, string templatePath, string excelPath, int sheetIndex = 0, object? extraData = null)

Parameters

entities IEnumerable<TEntity>

entities

templatePath string
excelPath string

templateBytes

sheetIndex int

sheetIndex,zero by default

extraData object

extraData

Type Parameters

TEntity

Entity Type

ToExcelFile<TEntity>(IEnumerable<TEntity>, string)

EntityList2ExcelFile

public static void ToExcelFile<TEntity>(this IEnumerable<TEntity> entityList, string excelPath)

Parameters

entityList IEnumerable<TEntity>

entityList

excelPath string

excelPath

Type Parameters

TEntity

EntityType

ToExcelFile<TEntity>(IEnumerable<TEntity>, string, int)

EntityList2ExcelFile

public static void ToExcelFile<TEntity>(this IEnumerable<TEntity> entityList, string excelPath, int sheetIndex)

Parameters

entityList IEnumerable<TEntity>

entityList

excelPath string

excelPath

sheetIndex int

sheetIndex

Type Parameters

TEntity

EntityType

ToExcelFile<TEntity>(IList<TEntity>, string)

EntityList2ExcelFile

public static void ToExcelFile<TEntity>(this IList<TEntity> entityList, string excelPath)

Parameters

entityList IList<TEntity>

entityList

excelPath string

excelPath

Type Parameters

TEntity

EntityType

ToExcelStream(DataTable, Stream)

DataTable2ExcelStream

public static void ToExcelStream(this DataTable dataTable, Stream stream)

Parameters

dataTable DataTable

dataTable

stream Stream

stream

ToExcelStream(DataTable, Stream, ExcelFormat)

DataTable2ExcelStream

public static void ToExcelStream(this DataTable dataTable, Stream stream, ExcelFormat excelFormat)

Parameters

dataTable DataTable

dataTable

stream Stream

stream

excelFormat ExcelFormat

excelFormat

ToExcelStream(DataTable, Stream, ExcelFormat, ExcelSetting?)

DataTable2ExcelStream

public static void ToExcelStream(this DataTable dataTable, Stream stream, ExcelFormat excelFormat, ExcelSetting? excelSetting)

Parameters

dataTable DataTable

dataTable

stream Stream

stream

excelFormat ExcelFormat

excelFormat

excelSetting ExcelSetting

excelSetting

ToExcelStream<TEntity>(IEnumerable<TEntity>, Stream)

EntityList2ExcelStream(*.xls by default)

public static void ToExcelStream<TEntity>(this IEnumerable<TEntity> entityList, Stream stream)

Parameters

entityList IEnumerable<TEntity>

entityList

stream Stream

stream where to write

Type Parameters

TEntity

EntityType

ToExcelStream<TEntity>(IEnumerable<TEntity>, Stream, ExcelFormat)

EntityList2ExcelStream

public static void ToExcelStream<TEntity>(this IEnumerable<TEntity> entityList, Stream stream, ExcelFormat excelFormat)

Parameters

entityList IEnumerable<TEntity>

entityList

stream Stream

stream where to write

excelFormat ExcelFormat

excelFormat

Type Parameters

TEntity

EntityType

ToExcelStream<TEntity>(IEnumerable<TEntity>, Stream, ExcelFormat, int)

EntityList2ExcelStream

public static void ToExcelStream<TEntity>(this IEnumerable<TEntity> entityList, Stream stream, ExcelFormat excelFormat, int sheetIndex)

Parameters

entityList IEnumerable<TEntity>

entityList

stream Stream

stream where to write

excelFormat ExcelFormat

excelFormat

sheetIndex int

sheetIndex

Type Parameters

TEntity

EntityType

ToExcelStream<TEntity>(IList<TEntity>, Stream, ExcelFormat)

EntityList2ExcelStream

public static void ToExcelStream<TEntity>(this IList<TEntity> entityList, Stream stream, ExcelFormat excelFormat = ExcelFormat.Xls)

Parameters

entityList IList<TEntity>

entityList

stream Stream

stream where to write

excelFormat ExcelFormat

excelFormat

Type Parameters

TEntity

EntityType

TryAddPicture(ISheet, int, int, IPictureData)

TryAddPicture in specific cell

public static bool TryAddPicture(this ISheet sheet, int row, int col, IPictureData pictureData)

Parameters

sheet ISheet

sheet

row int

cell rowIndex

col int

cell columnIndex

pictureData IPictureData

pictureData

Returns

bool

whether add success

TryAddPicture(ISheet, int, int, byte[], PictureType)

TryAddPicture in specific cell

public static bool TryAddPicture(this ISheet sheet, int row, int col, byte[] pictureBytes, PictureType pictureType = PictureType.PNG)

Parameters

sheet ISheet

sheet

row int

cell rowIndex

col int

cell columnIndex

pictureBytes byte[]

picture bytes

pictureType PictureType

picture type

Returns

bool

whether add success

WriteToFile(IWorkbook, string)

Write workbook to excel file

public static void WriteToFile(this IWorkbook workbook, string filePath)

Parameters

workbook IWorkbook

workbook

filePath string

file path

WriteToFile(IWorkbook, string, bool)

Write workbook to excel file

public static void WriteToFile(this IWorkbook workbook, string filePath, bool closeWorkbook)

Parameters

workbook IWorkbook

workbook

filePath string

file path

closeWorkbook bool

whether to close the workbook

Edit this page