当前位置:网站首页>Excel import and export based on epplus and npoi

Excel import and export based on epplus and npoi

2021-12-03 23:54:50 Broken pilot

be based on EPPlus and NPOI Realized Excel Import and export

CollapseNav.Net.Tool.Excel(NuGet Address )

Too long to see

  • Import
    • excel The file stream will be converted to ExcelTestDto A collection of types
    • var config = new ReadConfig<ExcelTestDto>()
      .Default(item => item.Field0, "233")
      .Require("Field1", item => item.Field1)
      .Add("Field3", item => item.Field3);
      IEnumerable<ExcelTestDto> data = await config.EPPlusExcelToEntityAsync(excelStream);
      
  • export
    • ExcelTestDto The collection of types will be converted to excel File stream
    • var config = new ExportConfig<ExcelTestDto>(datas);
      .Add("Field0", item => item.Field0)
      .Add("Field1", item => item.Field1)
      .Add("Field2", item => item.Field2 ? "Male" : "Female")
      .Add("Field3", item => item.Field3);
      Stream stream = await exportConfig.EPPlusExportAsync();
      

The simple way to use it is as above , At least in the use scenarios I need work Of

NuGet Wrap it on it

Preface

In order to facilitate their own handling of some excel Import and export function of , So it took Longer Time made this bag

The main reason is that I think to calculate each unknown subscript position , Calculate those 0 1 2 3 4 5 It's too much trouble

And every time you change a template, you have to do it again , Poor readability

I want to get rid of this hell , Then struggled a few times

You can take a look at Previous post

This chapter is about how to use this bag

Excel Data

Table data used Demo, For the time being, you can only handle simple of single line header excel

Field0 Field1 Field2 Field3
233 23 Male 233.33
1122 12 Female 123.23
233 23 Male 233.33
1122 12 Female 123.23
... ... ... ...

How To Use

Import (Import/Read/...)

stay In the usage scenarios I came across , In general, it is necessary to Excel Data is transferred to an entity in the system

For example, import a product list Excel, Turn this list into Goods object , Then use off the shelf AddRange(IEnumerable<Goods> datas) Method to the database

Based on the above Excel-->Entitys This set of things is designed for the use scenario of

I didn't test the performance , It's probably pulling

Suppose my entity looks like this

public class ExcelTestDto
{
    public string Field0 { get; set; }
    public int Field1 { get; set; }
    public bool Field2 { get; set; }
    public double Field3 { get; set; }
}

1.BuildReadConfig

The first step is to create a ReadConfig As excel Read configuration for

var config = new ReadConfig<ExcelTestDto>();

This configuration determines how and which columns will be read later

2.AddCellOptions

With ReadConfig After that, you need to add specific configurations

For the time being Default Require Add Add pair Single entity field Read settings for

  • Default
    • Do not rely on tabular data , Yes ExcelTestDto Add default values to the properties in the
    • config.Default(item => item.Field0, "233");
      
  • Require
    • By Require The cell of cannot be empty , Otherwise, an exception will be thrown when reading
    • config.Require("Field1", item => item.Field1);
      
  • Add
    • Normal add cell settings , Relatively more flexible
    • config.Add("Field3", item => item.Field3)
      

be-all excel Cells will be read as string

Require Add You can use Func<string, object> Delegate custom processing of reading cells

Because it is entrusted , You can do a lot of things , But it's easy to affect performance , It's best not to write complex and time-consuming delegates

config.Add("Field1", item => item.Field1, item =>
{
    var numCellData = int.Parse(item);
    numCellData += 2333;
    return numCellData;
});

All the above operations will return ReadConfig , therefore Strongly recommend Write the following call

var config = new ReadConfig<ExcelTestDto>()
.Default(item => item.Field0, "233")
.Require("Field1", item => item.Field1)
.Add("Field3", item => item.Field3)
;

At the same time, the corresponding DefaultIf RequireIf AddIf Method , Used to add different configurations according to different conditions

3.AddInit

Occasionally, there is a need to calculate something after reading a row of data , such as Grade students' grades in all subjects , So provide a AddInit Method , By passing in a Func<T, T> To do something

config.AddInit(item =>
{
    item.Field0 += "23333";
    //  The initialization of some properties can also be done here , Instead of  Default  It's OK, too 
    item.Field2 = false;
    return item;
});

4.ConvertExcel

After configuration, you can use EPPlusExcelToEntityAsync The corresponding excel Convert to entity set

//  If excel It's a file stream 
IEnumerable<ExcelTestDto> data = await config.EPPlusExcelToEntityAsync(excelStream);

Except for flow , Other parameters are also supported

  • string filepath
    • Simple physical file path , Will read directly on the physical path excel file
  • ExcelPackage pack
    • EPPlus Of ExcelPackage, It usually needs to be created manually
  • ExcelWorksheet sheet
    • EPPlus Of ExcelWorksheet, It usually needs to be created manually

export (Export/...)

Sometimes someone always needs to export the list data in the system into Excel

Then lead it back to the system like a fool

Therefore, relative import has another export function , The similarity between the two is high

1.BuildExportConfig

Create an export configuration ExportConfig

// datas  by  ExcelTestDto aggregate 
var config = new ExportConfig<ExcelTestDto>(datas);

Because the export is relatively simple , So there's a GenDefaultConfig Can directly Generate from generics Config ( Probably not easy to use )

2.AddCellOption

Because the export is relatively simple , So there's only one Add and AddIf Method to add a cell setting ( Although there are two )

config
.Add("Field0", item => item.Field0)
.Add("Field1", item => item.Field1)
.Add("Field2", item => item.Field2 ? "Male" : "Female")
.Add("Field3", item => item.Field3);

3.GenerateExcel

Use EPPlusExportAsync Generate Excel

//  The new version should already support exporting as a stream without parameters 
// Stream stream = await exportConfig.EPPlusExportAsync();
Stream stream = await exportConfig.EPPlusExportAsync(someStream);

Method returns a stream , After you get the stream, you can do what you want to do ...

You can also pass in a physical path (string type ), This will generate... At the specified location excel

TODO

  • [x] No parameter Export
  • [ ] Merge the same import configuration
  • [ ] Consider adding error handling
  • [ ] Test performance issues
  • [ ] Generate import / export configuration based on configuration
    • Generate configuration based on generic properties
    • according to attribute Generate configuration
    • Data generation configuration that can be stored in a general relational database

版权声明
本文为[Broken pilot]所创,转载请带上原文链接,感谢
https://chowdera.com/2021/11/20211116180614460s.html

随机推荐