weihanli / weihanli.npoi Goto Github PK

NPOI Extensions, excel/csv importer/exporter for IEnumerable<T>/DataTable, fluentapi(great flexibility)/attribute configuration

Home Page: https://weihanli.github.io/WeihanLi.Npoi/index.html

License: Apache License 2.0

C# 94.01% PowerShell 0.18% Shell 0.07% HTML 5.69% Dockerfile 0.06%

npoi excel weihanli csv office fluentapi csharp datatable dataset

weihanli.npoi's Introduction

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 or List<TEntity>/IEnumerable<T>
export IEnumerable<TEntity> or DataTable data to Excel file or Excel file bytes or even write excel file stream to your stream
export IEnumerable<TEntity> or DataTable 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 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("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();

see some articles here: https://weihanli.github.io/WeihanLi.Npoi/docs/articles/intro.html

more usage:

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 *.xls file
var workbook = PrepareWorkbook(bool isXls);

// prepare a workbook whether *.xls 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)

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)

Samples

Acknowledgements

Thanks for the contributors and users for this project
Thanks JetBrains for the open source ReSharper license

Contact

Contact me: [email protected]

weihanli.npoi's People

Contributors

Stargazers

Watchers

weihanli.npoi's Issues

This source codes copy from FluentExcel?

Many many codes copy from FluentExcel, please add the copyright info.

AND THE ATTRIBUTE FEATURE HAD BEED REMOVED IN FluentExcel

custom formatter enhancement

add support for much more customized formatter, for example:

ExcelHelper.SettingFor<TEntity>()
.Property(_=>_.Status)
  .HasColumnFormatter(s=>s.GetDisplayName() ?? s.ToString());

ExcelHelper.ToDataTable 报 Cannot get a text value from a numeric cell

InvalidOperationException: Cannot get a text value from a numeric cell

DataTable dataTable=ExcelHelper.ToDataTable(saveFileName);

Hepler/CsvFileHelper.cs(71,13): error CS8652: The feature 'using declarations' is currently in Preview and unsupported. To use Preview features, use the 'preview' language version.

.netcore 2.1 app Use the package docker to compile an error

ExcelHelper.ToDataTable bug when some cells are empty

the row.Cells would skip empty cells, use row.GetCell would sovle this, likes #3

add support for *.csv files import/export

在导入数据的时候，是否可以新增一个参数，用于设定表头开始的行数？

在导入数据的时候，是否可以指定表头的行数，比如表格的表头第一行是一个标题合并行，需要从第二行开始，才是数据的表头（对应实体的类属性），从表头的下一行参才真实的数据？

数据校验

有没有数据校验的功能，对导入的excel数据进行校验，返回不符合的数据行列和错误信息

导出到excel的列是动态不固定的

有个需求是考试场景中，登记考试用户的信息（是动态的），

我导出到excel需要把上图中name的值作为列名，value的值作为cell的内容。有个想法是在ColumnAttribute中增加 IsDict 是否是多字段类型，Dict字典属性，存储“姓名，工号”，有什么其他办法吗，谢谢

purpose of int return value in NpoiExtension methods

this gets me confused, looking at the code it is just a constant value of 1, examples just discard it.
Shouldn't void be more appropriate? Is there something I am missing?

`ColumnAttribute` 能不能支持设置列宽

我现在又几列存放的是 json字符串，这个很长，且里面没有换行符，导致导出的Excel那列很宽，比较丑……

我看了下代码，

使用了自动列宽

感谢~

Index was outside the bounds of the array.
at WeihanLi.Npoi.CsvHelper.ToEntityList[TEntity](String filePath) in D:\Code\Study\NPOI\WeihanLi.Npoi-dev\src\WeihanLi.Npoi\CsvHelper.cs:line 175
at DotNetCoreSample.Program.Main(String[] args) in D:\Code\Study\NPOI\WeihanLi.Npoi-dev\samples\DotNetCoreSample\Program.cs:line 60

add hidden property support like ef

add hidden property support like ef, add support for add export column that can not be mapped to a property of entity

eg:

var settings = ExcelHelper.For<TEntity>();
setting.Property("hiddenProperty")
   .HasColumnFormatter((entity, val)=> {})
   .HasColumnTitle("hiddenProperty")
;

Add maxColumns and removeEmptyRows parameters to ExcelHelper.ToDataTable()

Is your feature request related to a problem? Please describe.

Many times ExcelHelper.ToDataTable() returns DataTable like

The issue is probably caused by that cells has applied formatting, but in practice users might be loading thousands of unnecessary columns and rows which also has performance impact.

or it does not load DataTable at all, because of weird System.IndexOutOfRangeException, when cell count is for example 5, but row count is 4 (I do not know how to create such an excel, but I attach an example with that exception
ExcelWithOutOfRangeException.xlsx

Describe the solution you'd like
So I thought an elegant solution would be to extend method signature of ExcelHelper.ToDataTable() with maxColumns and removeEmptyRows like

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

Describe alternatives you've considered
Removing columns and rows can be done later when DataTable is already loaded (except that case throwing System.IndexOutOfRangeException) but it requires unnecessary additional loop through DataTable and is so common operation that it would be great to have it in one method and not have to write it separately.

Additional context
if there will be an interest in these features, I'll be happy to send a pull request.

Add `ToEntities` return `IEnumerable<TEntity>`

Is your feature request related to a problem? Please describe.

Read content via lazy loading

If one of the excel columns is empty, then all the values get overlapped

If one of the excel columns is empty, then all the values get overlapped, this happens when using this :

var entityList = ExcelHelper.ToEntityList<T>(string excelPath);

I tried using what i thought were column mappers

setting.Property(_ => _.DisplayName)
    .HasColumnTitle("设置显示名称")
    .HasColumnIndex(2);

but this proved to be innecfective.

So, whenever i have excel columns which are empty, I fill them with 1 blank space so the values are not overlapped when they are read by your excelhelper.

Excel导出超出65536行报错 Invalid row number (65536) outside allowable range (0..65535)

大佬
Excel导出超出65536行报错 Invalid row number (65536) outside allowable range (0..65535)
该怎么办，好像没有直接的方法可以自动分sheet的

ToEntityList 不支持以 Tab 分隔的 csv 文件

csv 文件中不是按照列来分隔，而是用 tab 分隔的整行文字。

我正在筹建NPOI电报群可否在readme.md和nuget里加个telegram链接

电报群地址：https://t.me/npoidevs

感谢

List.Count in the method of ToEntityList<>

If you have 2 rows of data, use the format brush to process the following 3 rows of the table. The List.Count = 5 after use ToEntityList<> .

how to convert to dto list if header row is not in first row

I have a sample sheet to be import, and for some reason, the header row was not in first row( was export by other system), I had tried the sheet.ToDataTable() API, but the dataTable's ToEntities() API not working

 using (var fs = File.OpenRead(fileName))
            {
                // auto detect workbook type, for .et file, got HSSFWokbook
                var workbook = ExcelHelper.LoadExcel(fs);

                // get default sheet
                var sheet = workbook.GetSheetAt(0);
                var dt = sheet.ToDataTable(1);

                var list = dt.ToEntities<TestEntity>();  // the list was not empty, bug every item's value is empty!!
}

below is the sample file

data.zip

格式问题

那个导入之后 excel的格式也带过来了比如加粗之类的咋清除

不能识别纯汉字的日期格式

二〇二二年一月一日格式的日期单元格识别为0001/1/1 0:00:00

Attributes.Sheet(StartRowIndex = 0) dont work

net 4.5.2 WeihanLi.Npoi 1.3.7

Codes for test:

    [WeihanLi.Npoi.Attributes.Sheet(StartRowIndex = 0)]
    internal class MyClass
    {
        [WeihanLi.Npoi.Attributes.Column(Index = 0)]
        public int id { get; set; }

        [WeihanLi.Npoi.Attributes.Column(Index = 1)]
        public string Name { get; set; }

        [WeihanLi.Npoi.Attributes.Column(Index = 2)]
        public double pp { get; set; }

        [WeihanLi.Npoi.Attributes.Column(Index = 3)]
        public DateTime year { get; set; } = DateTime.Now;
    }

            var res = ExcelHelper.ToEntityList<MyClass>("test1.xlsx", 0);
            foreach (var r in res)
            {
                Console.WriteLine($"{r.id}---{r.Name}---{r.pp}---{r.year}");
            }

Exception:
System.InvalidOperationException: Cannot get a text value from a numeric cell

test1.xlsx

sheetname问题

版本1.17.0，指定 sheetname 好像不起作用

ToExcelFile 导出文件格式xlsx，用excel打开会提示"发现xxx.xls中的部分内容有问题"

如果文件格式写成xls 打开不会提示。用的DotNetCoreSample 测试也是这样

通过Attributes绑定excel中的列，出现与excel中列对应不上

在1.3.6版本上可以对应，1.3.8版本上出现问题
` [SheetAttribute(SheetIndex = 0, StartRowIndex = 1)]
public class QuestionModel
{

    [Column("题目")]
    public string title { set; get; }

    [Column("难易程度")]
    public string difficult { get; set; } = "简单";

    [Column("题型")]
    public string type { get; set; } = "单选";

    [Column("分类")]
    public string category_name { get; set; }

    [Column("选项A")]
    public string option_a { get; set; }

    [Column("选项B")]
    public string option_b { get; set; }

    [Column("选项C")]
    public string option_c { get; set; }

    [Column("选项D")]
    public string option_d { get; set; }

    [Column("选项E")]
    public string option_e { get; set; }

    [Column("选项F")]
    public string option_f { get; set; }


    [Column("正确答案")]
    public string ans_right { get; set; }

    [Column("解析")]
    public string analysis { get; set; }
}`

ExcelHelper.ToEntityList<T> ，对象属性包含图片映射时，导入表格无图直接报错

ExcelHelper.ToEntityList

export data via template

export to excel based on a custom template

我的Model里有个基类Id属性,我在导出的时候忽略Id列会报错，怎么忽略这个属性呢

Originally posted by @hailin-jin in #51 (comment)

Open an exist excel and then modify or write to excelFile

var excelFile = $"D:\Scheme0.xls";

DataTable table = ExcelHelper.ToDataTable(excelFile);
NpoiExtensions.ToExcelFile(table, excelFile);

System.UnauthorizedAccessException
HResult=0x80070005
Message=对路径“D:\Scheme0.xls”的访问被拒绝。
Source=mscorlib
StackTrace:
在 System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
在 System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
在 System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize)
在 System.IO.File.Create(String path)
在 EagleEyeOS.Excel.NpoiExtensions.WriteToFile(IWorkbook workbook, String filePath) 在 D:\Work\Code\EagleEyeOS.Excel\src\EagleEyeOS.Excel\NpoiExtensions.cs 中: 第 1223 行
在 EagleEyeOS.Excel.NpoiExtensions.ToExcelFile(DataTable dataTable, String excelPath, ExcelSetting excelSetting) 在 D:\Work\Code\EagleEyeOS.Excel\src\EagleEyeOS.Excel\NpoiExtensions.cs 中: 第 796 行
在 EagleEyeOS.Excel.NpoiExtensions.ToExcelFile(DataTable dataTable, String excelPath) 在 D:\Work\Code\EagleEyeOS.Excel\src\EagleEyeOS.Excel\NpoiExtensions.cs 中: 第 740 行
在 Demo.Form1.Form1_Load(Object sender, EventArgs e) 在 D:\Work\Code\EagleEyeOS.Excel\Demo\Form1.cs 中: 第 85 行
在 System.Windows.Forms.Form.OnLoad(EventArgs e)
在 System.Windows.Forms.Form.OnCreateControl()
在 System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
在 System.Windows.Forms.Control.CreateControl()
在 System.Windows.Forms.Control.WmShowWindow(Message& m)
在 System.Windows.Forms.Control.WndProc(Message& m)
在 System.Windows.Forms.ScrollableControl.WndProc(Message& m)
在 System.Windows.Forms.Form.WmShowWindow(Message& m)
在 System.Windows.Forms.Form.WndProc(Message& m)
在 System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
在 System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
在 System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

此异常最初是在此调用堆栈中引发的:
System.IO.__Error.WinIOError(int, string)
System.IO.FileStream.Init(string, System.IO.FileMode, System.IO.FileAccess, int, bool, System.IO.FileShare, int, System.IO.FileOptions, Microsoft.Win32.Win32Native.SECURITY_ATTRIBUTES, string, bool, bool, bool)
System.IO.FileStream.FileStream(string, System.IO.FileMode, System.IO.FileAccess, System.IO.FileShare, int)
System.IO.File.Create(string)
EagleEyeOS.Excel.NpoiExtensions.WriteToFile(NPOI.SS.UserModel.IWorkbook, string) (位于 NpoiExtensions.cs 中)
EagleEyeOS.Excel.NpoiExtensions.ToExcelFile(System.Data.DataTable, string, EagleEyeOS.Excel.Settings.ExcelSetting) (位于 NpoiExtensions.cs 中)
EagleEyeOS.Excel.NpoiExtensions.ToExcelFile(System.Data.DataTable, string) (位于 NpoiExtensions.cs 中)
Demo.Form1.Form1_Load(object, System.EventArgs) (位于 Form1.cs 中)
System.Windows.Forms.Form.OnLoad(System.EventArgs)
System.Windows.Forms.Form.OnCreateControl()
...
[调用堆栈已截断]

使用`SheetAttribute`配置的第一个Sheet的SheetName永远不会生效

使用ToExcelFile方法直接对EntityList导出文件，默认使用了sheetIndex=0的配置，且不会尝试获取实体的SheetAttribute，因为总是传0到方法中去，不会有任何CreateSheet的调用

在这边怎么修改都不会有用

Support duplicate column names and avoid System.Data.DuplicateNameException

Is your feature request related to a problem? Please describe.
I have following table

A	B	C	A	B	C
1	2	3	4	5	6

and ExcelHelper.ToDataTable() throws System.Data.DuplicateNameException

Describe the solution you'd like
Implicitly, or explicitly via an argument parameter, add for example GUID identifier to duplicated column name, for example the 2nd column A would be A_a48f0a43bae1469ab61ae60c15285325 in DataTable or similar solution.

Describe alternatives you've considered
An alternative is to probably change the excel header in memory and then load the DataTable, which is unnecessarily complicated.

Additional context
If you are interested, I am happy to send pull request, but not sure if to add this behavior as the default (but it probably makes more sense).

关于项目命名

我看到你在新版中已经增加了驱动层，适配EPPlus和NPOI。

我是建议你参考ExcelReport项目的命名，驱动层他用的是ExcelReport.Driver.EPPlus和ExcelReport.Driver.NPOI，如果我没有记错的话。其实我不建议你把一个项目的名字用自己的名字冠名，虽然早期来看能够提升你的个人名气，但其实后期并没有什么帮助，而且会让别人觉得这个库完全是个人作品，存在一定使用风险。开源项目更多的时候是看社区贡献，而不是个人贡献，虽然主要贡献者不干了，基本这项目也挂了。