rigofunc / npoi.extension

This repo contains the extension for the NPOI, which provides IEnumerable<T> have save to and load from excel functionalities.

The extensions for the NPOI, which provides IEnumerable<T> have save to and load from excel functionalities.

IMPORTAMT

1. The future features will be support by FluentExcel, and will only support Fluent API.
2. All the issues found in this repo will be and only be fixed by FluentExcel, so, please update your code to use FluentExcel.

Features

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

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

Overview

Get Started

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

Using Package Manager Console to install NPOI.Extension

    PM> Install-Package NPOI.Extension

Reference NPOI.Extension in code

    using NPOI.Extension;

Configure model's excel behaviors

We can use fluent api or attributes to configure the model excel behaviors. If both had been used, fluent configurations will has the Hight Priority

1. Use Fluent Api

        public class Report {
            public string City { get; set; }
            public string Building { get; set; }
            public DateTime HandleTime { get; set; }
            public string Broker { get; set; }
            public string Customer { get; set; }
            public string Room { get; set; }
            public decimal Brokerage { get; set; }
            public decimal Profits { get; set; }
        }

        /// <summary>
        /// Use fluent configuration api. (doesn't poison your POCO)
        /// </summary>
        static void FluentConfiguration() 
        {
            var fc = 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();

            fc.Property(r => r.Building)
              .HasExcelIndex(1)
              .HasExcelTitle("楼盘")
              .IsMergeEnabled();

            fc.Property(r => r.HandleTime)
              .HasExcelIndex(2)
              .HasExcelTitle("成交时间")
              .HasDataFormatter("yyyy-MM-dd HH:mm:ss");
            
            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)
              .HasExcelTitle("佣金(元)");

            fc.Property(r => r.Profits)
              .HasExcelIndex(7)
              .HasExcelTitle("收益(元)");
        }

2. Use attributes

    [Statistics(Name = "合计", Formula = "SUM", Columns = new[] { 6, 7 })]
    [Filter(FirstCol = 0, FirstRow = 0, LastCol = 2)]
    [Freeze(ColSplit = 2, RowSplit = 1, LeftMostColumn = 2, TopRow = 1)]
    public class Report {
        [Column(Index = 0, Title = "城市", AllowMerge = true)]
        public string City { get; set; }
        [Column(Index = 1, Title = "楼盘", AllowMerge = true)]
        public string Building { get; set; }
        [Column(Index = 2, Title = "成交时间", Formatter = "yyyy-MM-dd HH:mm:ss")]
        public DateTime HandleTime { get; set; }
        [Column(Index = 3, Title = "经纪人")]
        public string Broker { get; set; }
        [Column(Index = 4, Title = "客户")]
        public string Customer { get; set; }
        [Column(Index = 5, Title = "房源")]
        public string Room { get; set; }
        [Column(Index = 6, Title = "佣金(元)")]
        public decimal Brokerage { get; set; }
        [Column(Index = 7, Title = "收益(元)")]
        public decimal Profits { get; set; }
    }

Export POCO to excel & Load IEnumerable<T> from excel.

using System;
using NPOI.Extension;

namespace samples
{
    class Program
    {
        static void Main(string[] args)
        {
            // global call this
            FluentConfiguration();

            var len = 10;
            var reports = new Report[len];
            for (int i = 0; i < len; i++)
            {
                reports[i] = new Report
                {
                    City = "ningbo",
                    Building = "世茂首府",
                    HandleTime = new DateTime(2015, 11, 23),
                    Broker = "rigofunc 18957139**7",
                    Customer = "rigofunc 18957139**7",
                    Room = "2#1703",
                    Brokerage = 125M,
                    Profits = 25m
                };
            }

            var excelFile = @"/Users/rigofunc/Documents/sample.xlsx";

            // save to excel file
            reports.ToExcel(excelFile);

            // load from excel
            var loadFromExcel = Excel.Load<Report>(excelFile);
        }

        /// <summary>
        /// Use fluent configuration api. (doesn't poison your POCO)
        /// </summary>
        static void FluentConfiguration() 
        {
            var fc = 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();

            fc.Property(r => r.Building)
              .HasExcelIndex(1)
              .HasExcelTitle("楼盘")
              .IsMergeEnabled();

            fc.Property(r => r.HandleTime)
              .HasExcelIndex(2)
              .HasExcelTitle("成交时间")
              .HasDataFormatter("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)
              .HasExcelTitle("佣金(元)");

            fc.Property(r => r.Profits)
              .HasExcelIndex(7)
              .HasExcelTitle("收益(元)");
        }
    }
}

npoi.extension's Issues

HasFilter 和HasFreeze的用法和参数含义

2个方法的参数是什么意思

Supports more custom cell value formatter

Right now, only the datetime cell value can specify the formatter, plan to support more custom formatters for cell value when v2.1 release.

Support customize date time formatter for exporting to excel

The default date time formatter is yyyy-MM-dd HH:mm:ss, but user can set by following code

Excel.Setting.DateFormatter = "yyyy-MM-dd";

Support using Fluent API to configure the POCO export/import behavior rather than use attribute.

This will be released as v2.0.0

无法获取公式计算后的值

背景

在导入Excel时
excel文件如下：
单元格A2:A2
单元格B2:=CONCATENATE("test", A2)

实体类
public ExcelData{
[Column(Index = 0)]
public string A{get;set;}
[Column(Index = 1)]
public string B{get;set;}
}

var data = Excel.Load(file, sheetIndex: 1);

期望结果及实际结果

期望data 的B属性为：testA2
实际结果为：CONCATENATE("test", A2)

NpoiExtension.cs 从“DateTime”到“Double”的强制转换无效。

在property.PropertyType == typeof(DateTime)这个else if判断分支，要加上或者等于DateTime?，并且修改Convert.ToDateTime为其它方法才行。
else if (property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?))
{
if (dateCellStyle == null)
{
// create the cache.
dateCellStyle = workbook.CreateCellStyle();

                            var dateFormat = workbook.CreateDataFormat();

                            dateCellStyle.DataFormat = dateFormat.GetFormat(Excel.Setting.DateFormatter);
                        }

                        cell.CellStyle = dateCellStyle;

                        **DateTime? time = value.ChangeType((DateTime?)null);
                        if (time.HasValue)
                        {
                            cell.SetCellValue(time.Value);
                        }
                        else
                        {
                            cell.SetCellValue("");
                        }**
                    }

fluent api have some issues

There are some issues about fluent api, but had fixed by FluentExcel, please use FluentExcel in your code

update Execl.cs:line 225 add datetime Formatter use

case CellType.Numeric: if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型 { if (!string.IsNullOrEmpty(cellConfig?.Formatter)) { return cell.DateCellValue.ToString(cellConfig?.Formatter); } return cell.DateCellValue.ToString(); } else//其他数字类型 { return cell.ToString(); }

数据导出不支持GUID类型

NpoiExtension.cs 113行，值类型判断不包含GUID类型，如果导出model存在GUID，则出错

Export support multiple sheets

1. Support same POCO group by key Property;
2. Support to export POCO to an exist xls, xlsx file;

能否支持针对XLSX的拓展？

The Excel.Load<T>(excelFile) doesn't use the fluent api

The Excel.Load(excelFile) doesn't use the fluent api, will be fixed when v2.1 released next month

Update NPOI to latest

Update NPOI to v2.2.1

errors when load from excel files

1.export to excel just fine,but when loading excel file,I came across a lot of formatter problems.(even use the excel file generate by this extension).
2.suggestions:Will you plan to support multiple Statistics instance,I need sum,avg when exporting excel.
3.suggestions:will you plan to support custom format cell other than just customize datetime

Support specifying which sheet (index) for importing POCO from excel

is there any plan to support datatable/dataset

this extension really a great time saver.But In my practice,most of times i just simply to convert database data to excel files.it is tedious to map db structures to class files.so i think it will be better to just simply transform a datatable/dataset to excel file(s).But it seems that currently this extension does not support datatable.

so,I looking forward that datatable to excel will be supported in the next version for barely transfer db to excel without and modifications.

提个小问题

1.如果可以，能否把代码注释一起打包?
2.对于输出到 工作簿表格文件，有考虑过要设计一个开关 是否覆盖与重新创建的操作吗?

建议导入Excel增加行过滤功能

背景
在导入excel时，需要动态的决定那些行是真实的有效行数据

例如：当第二列没填值时，这一行为无效数据，输出结果中不应该包含这一行数据

建议

增加参数Func<IRow, bool> customerRowsFilter = null),在处理每行数据时先校验传入的委托，决定是否要加入到输出结果中
public static IEnumerable Load(string excelFile, int startRow = 1, int sheetIndex = 0, ValueConverter valueConverter = null, Func<IRow, bool> customerRowsFilter = null) where T : class, new()

Support .NET Core

This will be a new repo in Npoi.Core

Project rename after v2.1 released and only support fluent api

After v2.1 released, I'll rename this to Arch/Excel or other something better, attribute based configuration will not be supported, only fluent configuration api will be supported.

Not Support .NET 4.0 ?