大佬教程收集整理的这篇文章主要介绍了使用WeihanLi.Npoi操作Excel,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
Npoi 扩展,适用于.netframework4.5及以上和netstandard2.0,.netframework基于NPOI,.netstandard基于 DotNetCore.NPOI
NpoiExtensions for target framework net4.5 or netstandard2.0,for net45 basedon NPOI,for .netstandard basedon DotNetCore.NPOI
.NetFramework
Install-Package WeihanLi.Npoi
.NetCore
dotnet add package WeihanLi.Npoi
LoadFromExcelFile
it consider the first row of the sheet as the header not for read,it will read data from next row.You can point out your header row through the exposed api if needed.
Read Excel to DataSet
// read excel to dataSet,read all sheets data to dataSet,by default it will read from the headerRowIndex(0) + 1
var dataSet = ExcelHelper.ToDataSet(String excelPath);
// read excel to dataSet,headerRowIndex is not for read,read from headerRowIndex+1
var dataSet = ExcelHelper.ToDataSet(String excelPath,int headerRowIndeX);
Read Excel to DataTable
// read excel to dataTable directly,by default read the first sheet content
var dataTable = ExcelHelper.ToDataTable(String excelPath);
// read excel workbook's sheeTindex sheet to dataTable directly
var dataTabLeofSheeTindex = ExcelHelper.ToDataTable(String excelPath,int sheeTindeX);
// read excel workbook's sheeTindex sheet to dataTable,custom headerRowIndex
var dataTabLeofSheeTindex = ExcelHelper.ToDataTable(String excelPath,int sheeTindex,int headerRowIndeX);
// read excel to dataTable use mapping relations and setTings from typeof(T),by default read the first sheet content
var dataTableT = ExcelHelper.ToDataTable<T>(String excelPath);
// ... sheeTindex and headerRowIndex is also supported like above
Read Excel to List
// read excel first sheet content to a List<T>
var entityList = ExcelHelper.ToEntityList<T>(String excelPath);
// read excel sheeTindex sheet content to a List<T>
// you can custom header row index via sheet attribute or fluent api HasSheet
var entityList1 = ExcelHelper.ToEntityList<T>(String excelPath,int sheeTindeX);
Get a workbook
// load excel workbook from file
var workbook = LoadExcel(String excelPath);
// prepare a workbook accounTing to excelPath
var workbook = PrepareWorkbook(String excelPath);
// prepare a workbook accounTing to excelPath and custom excel setTings
var workbook = PrepareWorkbook(String excelPath,ExcelSetTing excelSetTing);
// prepare a workbook whether *.xLSX file
var workbook = PrepareWorkbook(bool isXLSX);
// prepare a workbook whether *.xLSX file and custom excel setTing
var workbook = PrepareWorkbook(bool isXLSX,ExcelSetTing excelSetTing);
Rich extensions
List<TEntity> ToEntityList<TEntity>([NotNull]this IWorkbook workbook)
DataTable ToDataTable([NotNull]this IWorkbook workbook)
ISheet ImportData<TEntity>([NotNull] this ISheet sheet,DataTable dataTablE)
int ImportData<TEntity>([NotNull] this IWorkbook workbook,IEnumerable<TEntity> list,int sheeTindeX)
int ImportData<TEntity>([NotNull] this ISheet sheet,IEnumerable<TEntity> list)
int ImportData<TEntity>([NotNull] this IWorkbook workbook,[NotNull] DataTable dataTable,int sheeTindeX)
ToExcelFile<TEntity>([NotNull] this IEnumerable<TEntity> entityList,[NotNull] String excelPath)
int ToExcelStream<TEntity>([NotNull] this IEnumerable<TEntity> entityList,[NotNull] Stream stream)
byte[] ToExcelBytes<TEntity>([NotNull] this IEnumerable<TEntity> entityList)
int ToExcelFile([NotNull] this DataTable dataTable,[NotNull] String excelPath)
int ToExcelStream([NotNull] this DataTable dataTable,[NotNull] Stream stream)
byte[] ToExcelBytes([NotNull] this DataTable dataTablE)
byte[] ToExcelBytes([NotNull] this IWorkbook workbook)
int WriteToFile([NotNull] this IWorkbook workbook,String filePath)
object GetCellValue([NotNull] this ICell cell,Type propertyTypE)
T GetCellValue<T>([NotNull] this ICell cell)
SetCellValue([NotNull] this ICell cell,object value)
Attributes
Add columnAttribute
on the property of the entity which you used for export or import
Add SheetAttribute
on the entity which you used for export or import,you can set the StartRowIndex
on your need(by default it is 1
)
for example:
public class TestEntity
{
[column("PKID")]
public int PKID { get; set; }
[column("账单标题")]
public String Billtitle { get; set; }
[column("账单详情")]
public String BillDetails { get; set; }
[column("创建人")]
public String createdby { get; set; }
[column("创建时间")]
public datetiR_296_11845@e CreatedTime { get; set; }
}
internal class TestEntity1
{
/// <sumMary>
/// 用户名
/// </sumMary>
[column("用户名")]
public String Username { get; set; }
[column(IsIgnored = truE)]
public String passwordHash { get; set; }
[column("可用余额")]
public decimal amount { get; set; } = 1000M;
[column("微信id")]
public String WechatOpenId { get; set; }
[column("是否启用")]
public bool IsActive { get; set; }
}
FluentApi
You can also use FluentApi above version 1.0.3
for example:
var setTing = ExcelHelper.SetTingFor<TestEntity>();
// ExcelSetTing
setTing.HasAuthor("WeihanLi")
.Hastitle("WeihanLi.Npoi test")
.HasDescription("")
.HasSubject("");
setTing.HasSheetConfiguration(0,"系统设置列表");
setTing.HasFilter(0,1)
.HasFreezePane(0,1,2,1);
setTing.Property(_ => _.SetTingId)
.HascolumnIndex(0);
setTing.Property(_ => _.SetTingName)
.Hascolumntitle("设置名称")
.HascolumnIndex(1);
setTing.Property(_ => _.DisplayName)
.Hascolumntitle("设置显示名称")
.HascolumnIndex(2);
setTing.Property(_ => _.SetTingvalue)
.Hascolumntitle("设置值")
.HascolumnIndex(3);
setTing.Property(_ => _.CreatedTimE)
.Hascolumntitle("创建时间")
.HascolumnIndex(5)
.HascolumnFormatter("yyyy-MM-dd HH:mm:ss");
setTing.Property(_ => _.createdby)
.HascolumnIndex(4)
.Hascolumntitle("创建人");
setTing.Property(_ => _.updatedBy).Ignored();
setTing.Property(_ => _.updatedTimE).Ignored();
setTing.Property(_ => _.PKID).Ignored();
Contact me: weihanli@outlook.com
以上是大佬教程为你收集整理的使用WeihanLi.Npoi操作Excel全部内容,希望文章能够帮你解决使用WeihanLi.Npoi操作Excel所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。