WeihanLi.Npoi
2.3.0
Prefix Reserved
See the version list below for details.
dotnet add package WeihanLi.Npoi --version 2.3.0
NuGet\Install-Package WeihanLi.Npoi -Version 2.3.0
<PackageReference Include="WeihanLi.Npoi" Version="2.3.0" />
paket add WeihanLi.Npoi --version 2.3.0
#r "nuget: WeihanLi.Npoi, 2.3.0"
// Install WeihanLi.Npoi as a Cake Addin #addin nuget:?package=WeihanLi.Npoi&version=2.3.0 // Install WeihanLi.Npoi as a Cake Tool #tool nuget:?package=WeihanLi.Npoi&version=2.3.0
WeihanLi.Npoi
Build Status
Intro
NPOI extensions based on target framework netstandard2.0
/net6.0
.
There're a lot of useful extensions for you, core features are as follows:
- mapping a excel file data to a
DataTable
orList<TEntity>
/IEnumerable<T>
- export
IEnumerable<TEntity>
orDataTable
data to Excel file or Excel file bytes or even write excel file stream to your stream - export
IEnumerable<TEntity>
orDataTable
data to csv file or bytes. - custom configuration/mappings by Attribute or FluentAPI(inspired by FluentExcel)
- great flexibility by fluent InputFormatter/OutputFormatter/ColumnInputFormatter/ColumnOutputFormatter/CellReader
GetStarted
Export list/dataTable to Excel/csv
var entities = new List<Entity>(); entities.ToExcelFile(string excelPath); entities.ToExcelBytes(ExcelFormat excelFormat); entities.ToCsvFile(string csvPath); entities.ToCsvBytes();
Read Excel/csv to List
// read excel first sheet content to List<T> var entityList = ExcelHelper.ToEntityList<T>(string excelPath); // read excel first sheet content to IEnumerable<T> var entityList = ExcelHelper.ToEntities<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); var entityList2 = CsvHelper.ToEntityList<T>(string csvPath); var entityList3 = CsvHelper.ToEntityList<T>(byte[] csvBytes);
Read Excel/csv 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); // read csv file data to dataTable var dataTable1 = CsvHelper.ToDataTable(string csvFilePath);
More Api here: https://weihanli.github.io/WeihanLi.Npoi/docs/api/WeihanLi.Npoi.html
Define Custom Mapping and settings
Attributes
Add
ColumnAttribute
on the property of the entity which you used for export or importAdd
SheetAttribute
on the entity which you used for export or import,you can set theStartRowIndex
on your need(by default it is1
)for example:
public class TestEntity { [Column("Id")] public int PKID { get; set; } [Column("Bill Title")] public string BillTitle { get; set; } [Column("Bill Details")] public string BillDetails { get; set; } [Column("CreatedBy")] public string CreatedBy { get; set; } [Column("CreatedTime")] public DateTime CreatedTime { get; set; } } public class TestEntity1 { [Column("Username")] public string Username { get; set; } [Column(IsIgnored = true)] public string PasswordHash { get; set; } [Column("Amount")] public decimal Amount { get; set; } = 1000M; [Column("WechatOpenId")] public string WechatOpenId { get; set; } [Column("IsActive")] public bool IsActive { get; set; } }
FluentApi (Recommended)
You can use FluentApi for great flexibility
for example:
var setting = FluentSettings.For<TestEntity>(); // ExcelSetting setting.HasAuthor("WeihanLi") .HasTitle("WeihanLi.Npoi test") .HasDescription("WeihanLi.Npoi test") .HasSubject("WeihanLi.Npoi test"); setting.HasSheetConfiguration(0, "SystemSettingsList", true); // setting.HasSheetConfiguration(1, "SystemSettingsList", 1, true); // setting.HasFilter(0, 1).HasFreezePane(0, 1, 2, 1); setting.Property(_ => _.SettingId) .HasColumnIndex(0); setting.Property(_ => _.SettingName) .HasColumnTitle("SettingName") .HasColumnIndex(1); setting.Property(_ => _.DisplayName) .HasOutputFormatter((entity, displayName) => $"AAA_{entity.SettingName}_{displayName}") .HasInputFormatter((entity, originVal) => originVal.Split(new[] { '_' })[2]) .HasColumnTitle("DisplayName") .HasColumnIndex(2); setting.Property(_ => _.SettingValue) .HasColumnTitle("SettingValue") .HasColumnIndex(3); setting.Property(_ => _.CreatedTime) .HasColumnTitle("CreatedTime") .HasColumnIndex(4) .HasColumnWidth(10) .HasColumnFormatter("yyyy-MM-dd HH:mm:ss"); setting.Property(_ => _.CreatedBy) .HasColumnInputFormatter(x => x += "_test") .HasColumnIndex(4) .HasColumnTitle("CreatedBy"); setting.Property(x => x.Enabled) .HasColumnInputFormatter(val => "Enabled".Equals(val)) .HasColumnOutputFormatter(v => v ? "Enabled" : "Disabled"); setting.Property("HiddenProp") .HasOutputFormatter((entity, val) => $"HiddenProp_{entity.PKID}"); setting.Property(_ => _.PKID).Ignored(); setting.Property(_ => _.UpdatedBy).Ignored(); setting.Property(_ => _.UpdatedTime).Ignored();
More
see some articles here: https://weihanli.github.io/WeihanLi.Npoi/docs/articles/intro.html
more usage:
<details> <summary>Get a workbook</summary>
// 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 *.xls file
var workbook = PrepareWorkbook(bool isXls);
// prepare a workbook whether *.xls file and custom excel setting
var workbook = PrepareWorkbook(bool isXlsx, ExcelSetting excelSetting);
</details>
<details> <summary>Rich extensions</summary>
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)
void SetCellValue([NotNull] this ICell cell, object value)
byte[] ToCsvBytes<TEntity>(this IEnumerable<TEntity> entities, bool includeHeader)
ToCsvFile<TEntity>(this IEnumerable<TEntity> entities, string filePath, bool includeHeader)
void ToCsvFile(this DataTable dt, string filePath, bool includeHeader)
byte[] ToCsvBytes(this DataTable dt, bool includeHeader)
</details>
Samples
Acknowledgements
- Thanks for the contributors and users for this project
- Thanks JetBrains for the open source ReSharper license
Contact
Contact me: weihanli@outlook.com
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 was computed. net5.0-windows was computed. net6.0 is compatible. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. 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. |
.NET Core | netcoreapp2.0 was computed. netcoreapp2.1 was computed. netcoreapp2.2 was computed. netcoreapp3.0 was computed. netcoreapp3.1 was computed. |
.NET Standard | netstandard2.0 is compatible. netstandard2.1 was computed. |
.NET Framework | net461 was computed. net462 was computed. net463 was computed. net47 was computed. net471 was computed. net472 was computed. net48 was computed. net481 was computed. |
MonoAndroid | monoandroid was computed. |
MonoMac | monomac was computed. |
MonoTouch | monotouch was computed. |
Tizen | tizen40 was computed. tizen60 was computed. |
Xamarin.iOS | xamarinios was computed. |
Xamarin.Mac | xamarinmac was computed. |
Xamarin.TVOS | xamarintvos was computed. |
Xamarin.WatchOS | xamarinwatchos was computed. |
-
.NETStandard 2.0
- NPOI (>= 2.4.1)
- WeihanLi.Common (>= 1.0.52)
-
net6.0
- NPOI (>= 2.4.1)
- WeihanLi.Common (>= 1.0.52)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories (1)
Showing the top 1 popular GitHub repositories that depend on WeihanLi.Npoi:
Repository | Stars |
---|---|
WeihanLi/DbTool
数据库工具,根据表结构文档生成创建表sql,根据数据库表信息导出Model和表结构文档,根据文档生成数据库表,根据已有Model文件生成创建数据库表sql
|
Version | Downloads | Last updated |
---|---|---|
2.5.0 | 111 | 7/29/2024 |
2.4.2 | 27,835 | 8/10/2022 |
2.4.2-preview-20220810-082616 | 141 | 8/10/2022 |
2.4.1 | 447 | 8/10/2022 |
2.4.0 | 423 | 8/9/2022 |
2.4.0-preview-20220809-141016 | 130 | 8/9/2022 |
2.3.0 | 6,290 | 6/7/2022 |
2.2.0 | 982 | 5/12/2022 |
2.1.0 | 676 | 4/24/2022 |
2.1.0-preview-20220423-164958 | 142 | 4/23/2022 |
2.0.2 | 674 | 4/18/2022 |
2.0.1 | 678 | 4/12/2022 |
2.0.0 | 470 | 4/11/2022 |
2.0.0-preview-20220410-172027 | 160 | 4/10/2022 |
2.0.0-preview-20220409-173325 | 137 | 4/9/2022 |
1.21.0 | 4,703 | 10/7/2021 |
1.21.0-preview-20210913-151411 | 234 | 9/13/2021 |
1.21.0-preview-20210909-023107 | 220 | 9/9/2021 |
1.20.0 | 2,614 | 8/22/2021 |
1.19.1 | 377 | 8/16/2021 |
1.19.0 | 466 | 8/14/2021 |
1.18.0 | 733 | 6/12/2021 |
1.18.0-preview-20210611-160356 | 263 | 6/11/2021 |
1.17.0 | 8,096 | 4/29/2021 |
1.16.0 | 4,445 | 3/7/2021 |
1.16.0-preview-20210306-055310 | 282 | 3/6/2021 |
1.15.0 | 449 | 3/1/2021 |
1.15.0-preview-20210228-054739 | 232 | 2/28/2021 |
1.14.0 | 620 | 1/17/2021 |
1.14.0-preview-20210117-033710 | 280 | 1/17/2021 |
1.13.0 | 820 | 11/14/2020 |
1.13.0-preview-20201110-055747 | 305 | 11/10/2020 |
1.12.0 | 633 | 11/4/2020 |
1.12.0-preview-20201102-172211 | 327 | 11/2/2020 |
1.11.0 | 625 | 10/18/2020 |
1.11.0-preview-20200920-142738 | 320 | 9/20/2020 |
1.10.0 | 700 | 9/13/2020 |
1.10.0-preview-20200912-144223 | 427 | 9/12/2020 |
1.9.6 | 1,408 | 8/9/2020 |
1.9.5 | 487 | 8/9/2020 |
1.9.5-preview-20200809-030704 | 309 | 8/9/2020 |
1.9.4 | 701 | 7/15/2020 |
1.9.3 | 1,420 | 5/12/2020 |
1.9.2 | 546 | 4/29/2020 |
1.9.1 | 602 | 4/19/2020 |
1.9.1-preview-20200419-092753 | 332 | 4/19/2020 |
1.9.0 | 477 | 4/15/2020 |
1.9.0-preview-20200413-122712 | 352 | 4/13/2020 |
1.9.0-preview-20200412-033805 | 392 | 4/12/2020 |
1.8.2 | 1,081 | 1/28/2020 |
1.8.2-preview-20200128-053147 | 389 | 1/28/2020 |
1.8.1 | 552 | 1/27/2020 |
1.7.0 | 764 | 1/3/2020 |
1.7.0-preview-20200103-133726 | 419 | 1/3/2020 |
1.6.1 | 535 | 12/19/2019 |
1.6.0 | 630 | 12/12/2019 |
1.6.0-preview-20191212-112128 | 358 | 12/12/2019 |
1.5.1 | 616 | 11/30/2019 |
1.5.0 | 685 | 11/7/2019 |
1.5.0-preview-20191107-092645 | 376 | 11/7/2019 |
1.5.0-preview-20191102-140451 | 376 | 11/2/2019 |
1.5.0-preview-20191029-023734 | 383 | 10/29/2019 |
1.4.5 | 625 | 10/23/2019 |
1.4.4 | 593 | 10/18/2019 |
1.4.3 | 549 | 10/14/2019 |
1.4.0 | 618 | 9/24/2019 |
1.3.8 | 4,999 | 8/3/2019 |
1.3.8-preview-20190802-135712 | 435 | 8/2/2019 |
1.3.7 | 604 | 7/31/2019 |
1.3.7-preview-20190730-051952 | 467 | 7/30/2019 |
1.3.6 | 585 | 7/24/2019 |
1.3.5 | 1,116 | 5/19/2019 |
1.3.3 | 1,144 | 3/18/2019 |
1.3.0 | 999 | 12/30/2018 |
1.2.0 | 1,041 | 11/1/2018 |
1.1.0 | 1,000 | 10/17/2018 |