Using Fluent API to configure POCO excel behaviors, and then provides IEnumerable<T> has save to and load from excel functionalities.

Features

• Decouple the configuration from the POCO model by using fluent api.
• Support none configuration POCO, so that if English is your mother language, none any more configurations;

The first features will be very useful for English not their mother language developers.

IMPORTANT

1. This repo fork from my NPOI.Extension, and remove all the attributes based features (but can be extended, see the following demo), and will only support Fluent API.
2. All the issues found in NPOI.Extension will be and only be fixed by FluentExcel, so, please update your codes use FluentExcel.

Overview

Get Started

The following demo codes come from sample, download and run it for more information.

Install FluentExcel package

 PM> Install-Package FluentExcel

Using FluentExcel in code

 using FluentExcel;

Saving IEnumerable<T> to excel.

varexcelFile=@"/Users/rigofunc/Documents/sample.xlsx";// save to excel filereports.ToExcel(excelFile);

Loading IEnumerable<T> from excel.

// load from excelvarloadFromExcel=Excel.Load<Report>(excelFile);

Change title cell style

Default title cell style can be changed by using Excel.Setting.TitleCellStyleApplier:

// Center, Green background, White font colorstaticvoidMyTitleCellApplier(ICellStylecellStyle,IFontfont){cellStyle.Alignment=HorizontalAlignment.Center;cellStyle.VerticalAlignment=VerticalAlignment.Center;cellStyle.FillPattern=FillPattern.SolidForeground;cellStyle.FillForegroundColor=HSSFColor.Green.Index;font.Color=HSSFColor.White.Index;cellStyle.SetFont(font);}[...]Excel.Setting.TitleCellStyleApplier=MyTitleCellApplier;reports.ToExcel(excelFile);

Use Fluent Api to configure POCO's excel behaviors

We can use fluent api to configure the model excel behaviors.

/// <summary>/// Use fluent configuration api. (doesn't poison your POCO)/// </summary>staticvoidFluentConfiguration(){varfc=Excel.Setting.For<Report>();fc.HasStatistics("合计","SUM",6,7).HasFilter(firstColumn:0,lastColumn:2,firstRow:0).HasFreeze(columnSplit:2,rowSplit:1,leftMostColumn:2,topMostRow:1);fc.Property(r =>r.City).HasExcelIndex(0).HasExcelTitle("城市").IsMergeEnabled();// or//fc.Property(r => r.City).HasExcelCell(0,"城市", allowMerge: true);fc.Property(r =>r.Building).HasExcelIndex(1).HasExcelTitle("楼盘").IsMergeEnabled();// configures the ignore when exporting or importing.fc.Property(r =>r.Area).HasExcelIndex(8).HasExcelTitle("Area").IsIgnored(exportingIsIgnored:false,importingIsIgnored:true);// or//fc.Property(r => r.Area).IsIgnored(8, "Area", formatter: null, exportingIsIgnored: false, importingIsIgnored: true);fc.Property(r =>r.HandleTime).HasExcelIndex(2).HasExcelTitle("成交时间").HasDataFormatter("yyyy-MM-dd");// or //fc.Property(r => r.HandleTime).HasExcelCell(2, "成交时间", formatter: "yyyy-MM-dd", allowMerge: false);// or//fc.Property(r => r.HandleTime).HasExcelCell(2, "成交时间", "yyyy-MM-dd");fc.Property(r =>r.Broker).HasExcelIndex(3).HasExcelTitle("经纪人");fc.Property(r =>r.Customer).HasExcelIndex(4).HasExcelTitle("客户");fc.Property(r =>r.Room).HasExcelIndex(5).HasExcelTitle("房源");fc.Property(r =>r.Brokerage).HasExcelIndex(6).HasDataFormatter("￥0.00").HasExcelTitle("佣金(元)");fc.Property(r =>r.Profits).HasExcelIndex(7).HasExcelTitle("收益(元)");}

classProgram{staticvoidMain(string[]args){// global call thisFluentConfiguration();// demo the extension point//var fc = Excel.Setting.For<Report>().FromAnnotations();varlen=20;varreports=newReport[len];for(inti=0;i<len;i++){reports[i]=newReport{City="ningbo",Building="世茂首府",HandleTime=DateTime.Now,Broker="rigofunc 18957139**7",Customer="yingting 18957139**7",Room="2#1703",Brokerage=125*i,Profits=25*i};}varexcelFile=@"/Users/rigofunc/Documents/sample.xlsx";// save to excel filereports.ToExcel(excelFile);// load from excelvarloadFromExcel=Excel.Load<Report>(excelFile);}}

EXTENSIONS/CUSTOMIZING DEMO: From Annotations by extenstion methods.

Excel.Setting.For<Report>().FromAnnotations().AdjustAutoIndex();

The following demo show how to extend the exist functionalities by extension methods.

1. Applying annotations to the specified model

publicclassReport{[Display(Name="城市")]publicstringCity{get;set;}[Display(Name="楼盘")]publicstringBuilding{get;set;}[Display(Name="区域")]publicstringArea{get;set;}[Display(Name="成交时间")]publicDateTimeHandleTime{get;set;}[Display(Name="经纪人")]publicstringBroker{get;set;}[Display(Name="客户")]publicstringCustomer{get;set;}[Display(Name="房源")]publicstringRoom{get;set;}[Display(Name="佣金(元)")]publicdecimalBrokerage{get;set;}[Display(Name="收益(元)")]publicdecimalProfits{get;set;}}

2. Defines the extension methods.

publicstaticclassFluentConfigurationExtensions{publicstaticFluentConfiguration<TModel>FromAnnotations<TModel>(thisFluentConfiguration<TModel>fluentConfiguration)whereTModel:class{varproperties=typeof(TModel).GetProperties();foreach(varpropertyinproperties){varpc=fluentConfiguration.Property(property);vardisplay=property.GetCustomAttribute<DisplayAttribute>();if(display!=null){pc.HasExcelTitle(display.Name);if(display.GetOrder().HasValue){pc.HasExcelIndex(display.Order);}}else{pc.HasExcelTitle(property.Name);}varformat=property.GetCustomAttribute<DisplayFormatAttribute>();if(format!=null){pc.HasDataFormatter(format.DataFormatString.Replace("{0:","").Replace("}",""));}if(pc.Index<0){pc.HasAutoIndex();}}returnfluentConfiguration;}}