Class ExcelHelper

Namespace

WeihanLi.Npoi

Assembly

WeihanLi.Npoi.dll

ExcelHelper

public static class ExcelHelper

Inheritance

object

ExcelHelper

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.MemberwiseClone()

object.ReferenceEquals(object, object)

object.ToString()

Properties

DefaultDataValidator

Default Data Validator

public static IValidator DefaultDataValidator { get; set; }

Property Value

IValidator

DefaultExcelSetting

Default excel setting for export excel files

public static ExcelSetting DefaultExcelSetting { get; set; }

Property Value

ExcelSetting

Methods

LoadExcel(byte[])

load excel from excelBytes

public static IWorkbook LoadExcel(byte[] excelBytes)

Parameters

excelBytes byte[]

excel file bytes

Returns

IWorkbook

workbook

LoadExcel(byte[], ExcelFormat)

load excel from excelBytes

public static IWorkbook LoadExcel(byte[] excelBytes, ExcelFormat excelFormat)

Parameters

excelBytes byte[]

excel file bytes

excelFormat ExcelFormat

excelFormat

Returns

IWorkbook

workbook

LoadExcel(Stream)

load excel from excelBytes

public static IWorkbook LoadExcel(Stream excelStream)

Parameters

excelStream Stream

excel file stream

Returns

IWorkbook

workbook

LoadExcel(Stream, ExcelFormat)

load excel from excelBytes

public static IWorkbook LoadExcel(Stream excelStream, ExcelFormat excelFormat)

Parameters

excelStream Stream

excel file stream

excelFormat ExcelFormat

excelFormat

Returns

IWorkbook

workbook

LoadExcel(string)

load excel from filepath

public static IWorkbook LoadExcel(string excelPath)

Parameters

excelPath string

excel file path

Returns

IWorkbook

workbook

PrepareWorkbook()

get a excel workbook(*.xlsx)

public static IWorkbook PrepareWorkbook()

Returns

IWorkbook

PrepareWorkbook(bool)

get a excel workbook

public static IWorkbook PrepareWorkbook(bool isXlsx)

Parameters

isXlsx bool

is for *.xlsx file

Returns

IWorkbook

PrepareWorkbook(bool, ExcelSetting?)

get a excel workbook

public static IWorkbook PrepareWorkbook(bool isXlsx, ExcelSetting? excelSetting)

Parameters

isXlsx bool

is for *.xlsx file

excelSetting ExcelSetting

excelSettings

Returns

IWorkbook

PrepareWorkbook(string)

prepare a workbook for export

public static IWorkbook PrepareWorkbook(string excelPath)

Parameters

excelPath string

excelPath

Returns

IWorkbook

PrepareWorkbook(string, ExcelSetting?)

prepare a workbook for export

public static IWorkbook PrepareWorkbook(string excelPath, ExcelSetting? excelSetting)

Parameters

excelPath string

excelPath

excelSetting ExcelSetting

excelSetting

Returns

IWorkbook

PrepareWorkbook(ExcelFormat)

get a excel workbook

public static IWorkbook PrepareWorkbook(ExcelFormat excelFormat)

Parameters

excelFormat ExcelFormat

excelFormat

Returns

IWorkbook

PrepareWorkbook(ExcelFormat, ExcelSetting?)

prepare a workbook for export

public static IWorkbook PrepareWorkbook(ExcelFormat excelFormat, ExcelSetting? excelSetting)

Parameters

excelFormat ExcelFormat

excelFormat

excelSetting ExcelSetting

excelSetting

Returns

IWorkbook

ToDataSet(string)

read first sheet of excel from excel file path to a DataSet from second row

public static DataSet ToDataSet(string excelPath)

Parameters

excelPath string

excelPath

Returns

DataSet

ToDataSet(string, int)

read first sheet of excel from excel file path to a DataSet from (headerRowIndex+1) row

public static DataSet ToDataSet(string excelPath, int headerRowIndex)

Parameters

excelPath string

excelPath

headerRowIndex int

headerRowIndex

Returns

DataSet

ToDataTable(byte[], ExcelFormat, bool, int?)

read first sheet of excel from excelBytes to a data table

public static DataTable ToDataTable(byte[] excelBytes, ExcelFormat excelFormat, bool removeEmptyRows = false, int? maxColumns = null)

Parameters

excelBytes byte[]

excelBytes

excelFormat ExcelFormat

removeEmptyRows bool

removeEmptyRows

maxColumns int?

maxColumns

Returns

DataTable

DataTable

ToDataTable(byte[], ExcelFormat, int, bool, int?)

read (sheetIndex) sheet of excel from excelBytes to a data table

public static DataTable ToDataTable(byte[] excelBytes, ExcelFormat excelFormat, int sheetIndex, bool removeEmptyRows = false, int? maxColumns = null)

Parameters

excelBytes byte[]

excelBytes

excelFormat ExcelFormat

sheetIndex int

sheetIndex

removeEmptyRows bool

removeEmptyRows

maxColumns int?

maxColumns

Returns

DataTable

DataTable

ToDataTable(byte[], ExcelFormat, int, int, bool, int?)

read (sheetIndex) sheet of excel from excelBytes to a data table

public static DataTable ToDataTable(byte[] excelBytes, ExcelFormat excelFormat, int sheetIndex, int headerRowIndex, bool removeEmptyRows = false, int? maxColumns = null)

Parameters

excelBytes byte[]

excelBytes

excelFormat ExcelFormat

sheetIndex int

sheetIndex

headerRowIndex int

headerRowIndex

removeEmptyRows bool

removeEmptyRows

maxColumns int?

maxColumns

Returns

DataTable

DataTable

ToDataTable(string)

read first sheet of excel from excel file path to a data table

public static DataTable ToDataTable(string excelPath)

Parameters

excelPath string

excelPath

Returns

DataTable

DataTable

ToDataTable(string, int)

read first sheet of excel from excel file path to a data table

public static DataTable ToDataTable(string excelPath, int sheetIndex)

Parameters

excelPath string

excelPath

sheetIndex int

sheetIndex

Returns

DataTable

DataTable

ToDataTable(string, int, int, bool, int?)

read (sheetIndex) sheet of excel from excel file path to a data table

public static DataTable ToDataTable(string excelPath, int sheetIndex, int headerRowIndex, bool removeEmptyRows = false, int? maxColumns = null)

Parameters

excelPath string

excelPath

sheetIndex int

sheetIndex

headerRowIndex int

headerRowIndex

removeEmptyRows bool

removeEmptyRows

maxColumns int?

maxColumns

Returns

DataTable

DataTable

ToDataTable<TEntity>(string)

read first sheet of excel from excel file path to a data table

public static DataTable ToDataTable<TEntity>(string excelPath) where TEntity : new()

Parameters

excelPath string

excelPath

Returns

DataTable

DataTable

Type Parameters

TEntity

EntityType

ToDataTable<TEntity>(string, int)

read (sheetIndex) sheet of excel from excel file path to a list(for specific class type)

public static DataTable ToDataTable<TEntity>(string excelPath, int sheetIndex) where TEntity : new()

Parameters

excelPath string

excelPath

sheetIndex int

sheetIndex

Returns

DataTable

DataTable

Type Parameters

TEntity

EntityType

ToEntities<TEntity>(byte[], ExcelFormat, int)

public static IEnumerable<TEntity?> ToEntities<TEntity>(byte[] excelBytes, ExcelFormat excelFormat = ExcelFormat.Xls, int sheetIndex = 0) where TEntity : new()

Parameters

excelBytes byte[]

excelFormat ExcelFormat

sheetIndex int

Returns

IEnumerable<TEntity>

Type Parameters

TEntity

ToEntities<TEntity>(Stream, ExcelFormat, int)

public static IEnumerable<TEntity?> ToEntities<TEntity>(Stream excelStream, ExcelFormat excelFormat = ExcelFormat.Xls, int sheetIndex = 0) where TEntity : new()

Parameters

excelStream Stream

excelFormat ExcelFormat

sheetIndex int

Returns

IEnumerable<TEntity>

Type Parameters

TEntity

ToEntities<TEntity>(string, int)

public static IEnumerable<TEntity?> ToEntities<TEntity>(string excelPath, int sheetIndex) where TEntity : new()

Parameters

excelPath string

sheetIndex int

Returns

IEnumerable<TEntity>

Type Parameters

TEntity

ToEntityListWithValidationResult<TEntity>(byte[], ExcelFormat, int, IValidator<TEntity>?)

read (sheetIndex) sheet of excel from excel bytes to a list

public static (List<TEntity?> EntityList, Dictionary<int, ValidationResult> ValidationResults) ToEntityListWithValidationResult<TEntity>(byte[] excelBytes, ExcelFormat excelFormat = ExcelFormat.Xls, int sheetIndex = 0, IValidator<TEntity>? validator = null) where TEntity : new()

Parameters

excelBytes byte[]

excelBytes

excelFormat ExcelFormat

excelFormat

sheetIndex int

sheetIndex

validator IValidator<TEntity>

validator

Returns

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

List and validationResult

Type Parameters

TEntity

EntityType

ToEntityListWithValidationResult<TEntity>(Stream, ExcelFormat, int, IValidator<TEntity>?)

read (sheetIndex) sheet of excel from excel bytes path to a list

public static (List<TEntity?> EntityList, Dictionary<int, ValidationResult> ValidationResults) ToEntityListWithValidationResult<TEntity>(Stream excelStream, ExcelFormat excelFormat = ExcelFormat.Xls, int sheetIndex = 0, IValidator<TEntity>? validator = null) where TEntity : new()

Parameters

excelStream Stream

excelStream

excelFormat ExcelFormat

excelFormat

sheetIndex int

sheetIndex

validator IValidator<TEntity>

data validator

Returns

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

List

Type Parameters

TEntity

EntityType

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

read (sheetIndex) sheet of excel from excel file path to a list

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

Parameters

excelPath string

excelPath

sheetIndex int

sheetIndex

validator IValidator<TEntity>

validator

Returns

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

List and validationResult

Type Parameters

TEntity

EntityType

ToEntityList<TEntity>(byte[])

read first sheet of excel from excel file bytes to a list

public static List<TEntity?> ToEntityList<TEntity>(byte[] excelBytes) where TEntity : new()

Parameters

excelBytes byte[]

excelBytes

Returns

List<TEntity>

List

Type Parameters

TEntity

EntityType

ToEntityList<TEntity>(byte[], int)

read (sheetIndex) sheet of excel from excel file bytes to a list

public static List<TEntity?> ToEntityList<TEntity>(byte[] excelBytes, int sheetIndex) where TEntity : new()

Parameters

excelBytes byte[]

excelBytes

sheetIndex int

sheetIndex

Returns

List<TEntity>

List

Type Parameters

TEntity

EntityType

ToEntityList<TEntity>(byte[], ExcelFormat)

read first sheet of excel from excel file bytes to a list

public static List<TEntity?> ToEntityList<TEntity>(byte[] excelBytes, ExcelFormat excelFormat) where TEntity : new()

Parameters

excelBytes byte[]

excelBytes

excelFormat ExcelFormat

excelFormat

Returns

List<TEntity>

List

Type Parameters

TEntity

EntityType

ToEntityList<TEntity>(byte[], ExcelFormat, int)

read (sheetIndex) sheet of excel from excel bytes to a list

public static List<TEntity?> ToEntityList<TEntity>(byte[] excelBytes, ExcelFormat excelFormat, int sheetIndex) where TEntity : new()

Parameters

excelBytes byte[]

excelBytes

excelFormat ExcelFormat

excelFormat

sheetIndex int

sheetIndex

Returns

List<TEntity>

List

Type Parameters

TEntity

EntityType

ToEntityList<TEntity>(Stream)

read first sheet of excel from excel stream to a list

public static List<TEntity?> ToEntityList<TEntity>(Stream excelStream) where TEntity : new()

Parameters

excelStream Stream

excelStream

Returns

List<TEntity>

List

Type Parameters

TEntity

EntityType

ToEntityList<TEntity>(Stream, int)

read (sheetIndex) sheet of excel from excel file bytes to a list

public static List<TEntity?> ToEntityList<TEntity>(Stream excelStream, int sheetIndex) where TEntity : new()

Parameters

excelStream Stream

excelStream

sheetIndex int

sheetIndex

Returns

List<TEntity>

List

Type Parameters

TEntity

EntityType

ToEntityList<TEntity>(Stream, ExcelFormat)

read first sheet of excel from excel file bytes to a list

public static List<TEntity?> ToEntityList<TEntity>(Stream excelStream, ExcelFormat excelFormat) where TEntity : new()

Parameters

excelStream Stream

excelStream

excelFormat ExcelFormat

excelFormat

Returns

List<TEntity>

List

Type Parameters

TEntity

EntityType

ToEntityList<TEntity>(Stream, ExcelFormat, int)

read (sheetIndex) sheet of excel from excel bytes path to a list

public static List<TEntity?> ToEntityList<TEntity>(Stream excelStream, ExcelFormat excelFormat, int sheetIndex) where TEntity : new()

Parameters

excelStream Stream

excelStream

excelFormat ExcelFormat

excelFormat

sheetIndex int

sheetIndex

Returns

List<TEntity>

List

Type Parameters

TEntity

EntityType

ToEntityList<TEntity>(string)

read first sheet of excel from excel file path to a list

public static List<TEntity?> ToEntityList<TEntity>(string excelPath) where TEntity : new()

Parameters

excelPath string

excelPath

Returns

List<TEntity>

List

Type Parameters

TEntity

EntityType

ToEntityList<TEntity>(string, int)

read (sheetIndex) sheet of excel from excel file path to a list

public static List<TEntity?> ToEntityList<TEntity>(string excelPath, int sheetIndex) where TEntity : new()

Parameters

excelPath string

excelPath

sheetIndex int

sheetIndex

Returns

List<TEntity>

List

Type Parameters

TEntity

EntityType