ASP.NET Core 2.2 : 十六.扒一扒新的Endpoint路由方案 try.dot.net 的正确使用姿势 .Net NPOI 根据excel模板导出excel、直接生成excel .Net NPOI 上传excel文件、提交后台获取excel里的数据

ASP.NET Core 2.2 : 十六.扒一扒新的Endpoint路由方案

 

ASP.NET Core 从2.2版本开始，采用了一个新的名为Endpoint的路由方案，与原来的方案在使用上差别不大，但从内部运行方式上来说，差别还是很大的。上一篇详细介绍了原版路由方案的运行机制，本文仍然通过一幅图来了解一下新版的运行机制，最后再总结一下二者的异同点。（ASP.NET Core 系列目录）

一、概述

此方案从2.2版本开始，被称作终结点路由（下文以“新版”称呼），它是默认开启的，若想采用原来的方案（<=2.1,下文以原版称呼），可以在AddMvc的时候进行设置

services.AddMvc(option=>option.EnableEndpointRouting = false).SetCompatibilityVersion(CompatibilityVersion.Version_2_2);

EnableEndpointRouting 默认为true，也就是启用新的Endpoint方案，设置为false则采用旧版（<=2.1）的路由方案。

在配置方法上来说，系统仍然采用在Startup中的use.Mvc()中配置，而实际上内部的处理中间件已由原来的RouterMiddleware改为EndpointMiddleware和EndpointRoutingMiddleware两个中间件处理，下面依旧通过一幅图来详细看一下：

二、流程及解析

图一

为了方便查看，依然对几个“重点对象”做了颜色标识（点击图片可以看大图）：

      1. 路由的初始化配置（图的前两个泳道） 

①  一切依然是从Startup开始，而且和旧版一样，是通过UseMvc方法进行配置，传入routes.MapRoute(...)这样的一个或多个配置, 不做赘述。

下面着重说一下后面的流程，看一下MvcApplicationBuilderExtensions中的UseMvc方法：

 1 public static IApplicationBuilder UseMvc(
 2     this IApplicationBuilder app,
 3     Action<IRouteBuilder> configureRoutes)
 4 {
 5 //此处各种验证，略。。
 6     var options = app.ApplicationServices.GetRequiredService<IOptions<MvcOptions>>();
 7     if (options.Value.EnableEndpointRouting)
 8     {
 9         var mvcEndpointDataSource = app.ApplicationServices
10             .GetRequiredService<IEnumerable<EndpointDataSource>>()
11             .OfType<MvcEndpointDataSource>()
12             .First();
13         var parameterPolicyFactory = app.ApplicationServices
14             .GetRequiredService<ParameterPolicyFactory>();
15
16         var endpointRouteBuilder = new EndpointRouteBuilder(app);
17
18         configureRoutes(endpointRouteBuilder);
19
20         foreach (var router in endpointRouteBuilder.Routes)
21         {
22             // Only accept Microsoft.AspNetCore.Routing.Route when converting to endpoint
23             // Sub-types could have additional customization that we can't knowingly convert
24             if (router is Route route && router.GetType() == typeof(Route))
25             {
26                 var endpointInfo = new MvcEndpointInfo(
27                     route.Name,
28                     route.RouteTemplate,
29                     route.Defaults,
30                     route.Constraints.ToDictionary(kvp => kvp.Key, kvp => (object)kvp.Value),
31                     route.DataTokens,
32                     parameterPolicyFactory);
33              mvcEndpointDataSource.ConventionalEndpointInfos.Add(endpointInfo);
34             }
35             else
36             {
37                 throw new InvalidOperationException($"Cannot use '{router.GetType().FullName}' with Endpoint Routing.");
38             }
39         }
40         if (!app.Properties.TryGetValue(EndpointRoutingRegisteredKey, out _))
41         {
42             // Matching middleware has not been registered yet
43             // For back-compat register middleware so an endpoint is matched and then immediately used
44             app.UseEndpointRouting();
45         }
46         return app.UseEndpoint();
47     }
48     else
49     {
50        //旧版路由方案
51     }
52 }

② 第6行，这里会获取并判断设置的EnableEndpointRouting的值，若为false，则采用旧版路由，详见上一篇文章；该值默认为true，即采用新版路由。
            ③ 对应第9行，MvcEndpointDataSource在新版路由中是个非常非常重要的角色，在启动初始化阶段，它完成了路由表存储和转换，此处先用颜色重点标记一下，大家记住它，在后面的流程中详细介绍。
            ④ 对应第16行，同旧版的RouteBuilder一样，这里会new一个 endpointRouteBuilder，二者都是一个IRouteBuilder，所以也同样调用configureRoutes(endpointRouteBuilder)方法（也就是startup中的配置）获取了一个Route的集合（IList<IRouter>）赋值给endpointRouteBuilder.Routes，这里有个特别该注意的地方if (router is Route route && router.GetType() == typeof(Route)) ，也就是这里只接受route类型，终结点路由系统不支持基于 IRouter的可扩展性，包括从 Route继承。
            ⑤ 对应第20行，这里对刚获取到的endpointRouteBuilder.Routes进行遍历，转换成了一个MvcEndpointInfo的集和，赋值给mvcEndpointDataSource.ConventionalEndpointInfos。
            ⑥ 之后就是向管道塞中间件了，这里的处理中间件由原来的RouterMiddleware改为EndpointMiddleware和EndpointRoutingMiddleware。

       2.请求的处理（图的后两个泳道）

请求的处理大部分功能在中间件EndpointRoutingMiddleware，他有个重要的属性_endpointDataSource保存了上文中初始化阶段生成的MvcEndpointDataSource，而中间件EndpointMiddleware的功能比较简单，主要是在EndpointRoutingMiddleware筛选出endpoint之后，调用该endpoint的endpoint.RequestDelegate(httpContext)进行请求处理。
            ⑦ InitializeAsync()方法主要是用于调用InitializeCoreAsync()创建一个matcher，而通过这个方法的代码可以看出它只是在第一次请求的时候执行一次。

private Task<Matcher> InitializeAsync()
{
var initializationTask = _initializationTask;
if (initializationTask != null)
{
return initializationTask;
}

return InitializeCoreAsync();
}

⑧ MvcEndpointDataSource一个重要的方法UpdateEndpoints()，作用是读取所有action，并将这个action列表与它的ConventionalEndpointInfos列表（见⑤）进行匹配，最终生成一个新的列表。如下图，我们默认情况下只配置了一个"{controller=Home}/{action=Index}/{id?}"这样的路由，默认的HomeController有三个action，添加了一个名为FlyLoloController的controller并添加了一个带属性路由的action，最终生成了7个Endpoint，这有点像路由与action的“乘积”。当然，这里只是用默认程序举了个简单的例子，实际项目中可能会有更多的路由模板注册、会有更多的Controller和Action以及属性路由等。

图二

具体代码如下：

 1         private void UpdateEndpoints()
 2         {
 3             lock (_lock)
 4             {
 5                 var endpoints = new List<Endpoint>();
 6                 StringBuilder patternStringBuilder = null;
 7
 8                 foreach (var action in _actions.ActionDescriptors.Items)
 9                 {
10                     if (action.AttributeRouteInfo == null)
11                     {
12                         // In traditional conventional routing setup, the routes defined by a user have a static order
13                         // defined by how they are added into the list. We would like to maintain the same order when building
14                         // up the endpoints too.
15                         //
16                         // Start with an order of '1' for conventional routes as attribute routes have a default order of '0'.
17                         // This is for scenarios dealing with migrating existing Router based code to Endpoint Routing world.
18                         var conventionalRouteOrder = 1;
19
20                         // Check each of the conventional patterns to see if the action would be reachable
21                         // If the action and pattern are compatible then create an endpoint with the
22                         // area/controller/action parameter parts replaced with literals
23                         //
24                         // e.g. {controller}/{action} with HomeController.Index and HomeController.Login
25                         // would result in endpoints:
26                         // - Home/Index
27                         // - Home/Login
28                         foreach (var endpointInfo in ConventionalEndpointInfos)
29                         {
30                             // An 'endpointInfo' is applicable if:
31                             // 1. it has a parameter (or default value) for 'required' non-null route value
32                             // 2. it does not have a parameter (or default value) for 'required' null route value
33                             var isApplicable = true;
34                             foreach (var routeKey in action.RouteValues.Keys)
35                             {
36                                 if (!MatchRouteValue(action, endpointInfo, routeKey))
37                                 {
38                                     isApplicable = false;
39                                     break;
40                                 }
41                             }
42
43                             if (!isApplicable)
44                             {
45                                 continue;
46                             }
47
48                             conventionalRouteOrder = CreateEndpoints(
49                                 endpoints,
50                                 ref patternStringBuilder,
51                                 action,
52                                 conventionalRouteOrder,
53                                 endpointInfo.ParsedPattern,
54                                 endpointInfo.MergedDefaults,
55                                 endpointInfo.Defaults,
56                                 endpointInfo.Name,
57                                 endpointInfo.DataTokens,
58                                 endpointInfo.ParameterPolicies,
59                                 suppressLinkGeneration: false,
60                                 suppressPathMatching: false);
61                         }
62                     }
63                     else
64                     {
65                         var attributeRoutePattern = RoutePatternFactory.Parse(action.AttributeRouteInfo.Template);
66
67                         CreateEndpoints(
68                             endpoints,
69                             ref patternStringBuilder,
70                             action,
71                             action.AttributeRouteInfo.Order,
72                             attributeRoutePattern,
73                             attributeRoutePattern.Defaults,
74                             nonInlineDefaults: null,
75                             action.AttributeRouteInfo.Name,
76                             dataTokens: null,
77                             allParameterPolicies: null,
78                             action.AttributeRouteInfo.SuppressLinkGeneration,
79                             action.AttributeRouteInfo.SuppressPathMatching);
80                     }
81                 }
82
83                 // See comments in DefaultActionDescriptorCollectionProvider. These steps are done
84                 // in a specific order to ensure callers always see a consistent state.
85
86                 // Step 1 - capture old token
87                 var oldCancellationTokenSource = _cancellationTokenSource;
88
89                 // Step 2 - update endpoints
90                 _endpoints = endpoints;
91
92                 // Step 3 - create new change token
93                 _cancellationTokenSource = new CancellationTokenSource();
94                 _changeToken = new CancellationChangeToken(_cancellationTokenSource.Token);
95
96                 // Step 4 - trigger old token
97                 oldCancellationTokenSource?.Cancel();
98             }
99         }

本质就是计算出一个个可能被请求的请求终结点，也就是Endpoint。由此可见，如上一篇文章那样想自定义一个handler来处理特殊模板的方式（如 routes.MapRoute("flylolo/{code}/{name}", MyRouteHandler.Handler);）将被忽略掉，因其无法生成 Endpoint，且此种方式完全可以自定义一个中间件来实现，没必要混在路由中。

⑨ 就是用上面生成的Matcher，携带Endpoint列表与请求URL做匹配，并将匹配到的Endpoint赋值给feature.Endpoint。
            ⑩ 获取feature.Endpoint，若存在则调用其RequestDelegate处理请求httpContext。

三、新版与旧版的异同点总结

简要从应用系统启动和请求处理两个阶段对比说一下两个版本的区别：

1.启动阶段：

这个阶段大部分都差不多，都是通过Startup的app.UseMvc()方法配置一个路由表，一个Route的集合Routes（IList<IRouter>），然后将其简单转换一下

<=2.1:  将Routes转换为RouteCollection

2.2+ :   将Routes转换为List<MvcEndpointInfo>

二者区别不大，虽然名字不同，但本质上还是差不多，都仍可理解为Route的集合的包装。

2.请求处理阶段：

<=2.1:   1. 将请求的URL与RouteCollection中记录的路由模板进行匹配。

2. 找到匹配的Route之后，再根据这个请求的URL判断是否存在对应的Controlled和Action。

3. 若以上均通过，则调用Route的Handler对HttpContext进行处理。

2.2+ :   1. 第一次处理请求时，首先根据启动阶段所配置的路由集合List<MvcEndpointInfo>和_actions.ActionDescriptors.Items（所有的action的信息）做匹配，生成一个列表，这个列表存储了所有可能被匹配的URL模板，如图二，这个列表同样是List<MvcEndpointInfo>，记录了所有可能的URL模式，实际上是列出了一个个可以被访问的详细地址，已经算是最终地址了，即终结点，或许就是为什么叫Endpoint路由的原因。

2.请求的Url和这个生成的表做匹配，找到对应的MvcEndpointInfo。

3. 调用被匹配的MvcEndpointInfo的RequestDelegate方法对请求进行处理。

二者区别就是对于_actions.ActionDescriptors.Items（所有的action的信息）的匹配上，原版是先根据路由模板匹配后，再根据ActionDescriptors判断是否存在对应的Controller和action，而新版是先利用了action信息与路由模板匹配，然后再用请求的URL进行匹配，由于这样的工作只在第一次请求的时候执行，所以虽然没有做执行效率上的测试，但感觉应该是比之前快的。

【简介】

　　微软官方前不久发布了 try.dot.net 这个有趣的网址，开始只是图个新鲜看了一下，后面通过自身实践过后，发现这着实算是个“有趣”的站点！

　　首先我们大概地列举一下这个站点能给我们带来什么？

1. 在线做一些教学教程，附上可以调试的代码片段，很方便他人学习
2. 面试的时候更方便了，面试官写好一个代码片段，可以让不同的求职者直接在上面编写代码运行程序

　　当然不止上述的功能，利用你的想象力去丰富工具的使用场景吧。

　　接下来我们通过一个自身的使用经历介绍一下如何使用这个有趣的工具。

【实现过程】

　　1.登陆 try.dot.net 的官方介绍网站，了解如何使用 try.dot.net

　　我们通过微软提供的github项目地址和微软发布说明可以轻松的了解到try.dot.net的使用说明。这里提供链接：

　　GitHub地址：https://github.com/dotnet/try

　　官方介绍地址：https://dotnet.microsoft.com/platform/try-dotnet

　　从介绍网址看到经典用法：

　

　　通过iframe嵌套到自己的网址展示代码片段，遗憾的是，目前本人还卡在iframe跨域拒绝的阶段 /手动哭（搞了一晚上还是没搞明白怎么解决iframe跨域这个网址，有大神了解可以留言并给个demo瞅瞅）

　　通过上面的iframe地址可以看到try.dot.net 展示代码片段的时候是通过gist这个代码片段id进行链路的。

　　2.gist的使用

　　gist是github的一个附加功能，支持将多个代码片段存放在站点上，站点会针对代码片段自动生成一个id，在别的地方就可以使用id引用了。

　　首先需要登录gist的站点：https://gist.github.com

　　可能有人可以访问github，却访问不了这个网站（比如我），这里有个解决方案：

　　配置hosts（不会自行百度吧）：192.30.253.118 gist.github.com

　　然后就可以登陆到这个站点了。

　　

　　界面非常简洁，直接输入文件名和相关代码点右下角的保存即可。AddFile可以添加另一段代码。

　　随便写了个实体类放在这里：

　　

　　保存后，点击刚才的类文件，便可以看到浏览器的url变成了这样的：

　　https://gist.github.com/sevenTiny/98b8b484dd9d0fbf8bd1bac0425db914

　　已经生成了代码片段的id。

　　接下来我们就要复制这个id去用try.dot.net调试我们的代码片段了。

　　3.try.dot.net的集成

　　在上文中复制id，然后将try.dot.net demo中的fromGist进行替换即可得到自己的代码片段地址

　　https://try.dot.net/?fromGist=98b8b484dd9d0fbf8bd1bac0425db914

　　我们可以访问一下

　　

　　代码运行正常，结果也正确！

　　那么问题来了，有人说我的也是这么操作的，为啥访问地址后，代码没出现呢？代码区域是空白的...

　　我开始也遇到了这个情况，对照了和微软官方的代码段后，发现只有缺少Main方法这个区别，无奈又加了个带Main方法的程序片段，然后问题解决了，代码成功出现！

　　猜测微软这个try.dot.net是模仿了个控制台应用程序，必须要有控制台应用程序的Main方法才能运行。

　　必须提供带Main方法的代码片段

　　

　　运行后的try.dot.net界面只能显示该代码片段，其他代码片段不会显示，但是可以引用使用。

【拓展】

　　通过上述操作，我们已经了解到了如何将自己的代码片段使用 try.dot.net 展示并运行，那么我们可以小小激发我们一下好奇心，了解一下微软怎么在前端界面做的代码提示呢？

　　我们打开浏览器F12查看请求信息，然后输入一段代码

　　

　　

　　可以清晰地看出，每次输入字符，站点都会请求分析当前的代码，然后返回可能提示出的代码片段，弹出代码提示框。

　　原理还是比较直观的。

.Net NPOI 根据excel模板导出excel、直接生成excel

 

一、根据Excel模板导出excel

1、导入NPOI.dll 

2、DAL中添加类ExportExcel.cs

using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
namespace DAL.Assessment
{
    public class ExportExcel
    {
        NPOIExcel excel;

        //构造 采用模板方式
        public ExportExcel(string tempPath)
        {
            excel = new NPOIExcel(tempPath);
        }

        /// <summary>
        /// 流的形式
        /// </summary>
        /// <returns></returns>
        private MemoryStream WriteToStream()
        {
            return excel.Save();
        }

        /// <summary>
        /// PPK 导出
        /// </summary>
        /// <param name="list"></param>
        /// <param name="fileName"></param>
        public void PPKToExcel(string ProductInfo,string avgWeightStr, string DivWeightStr, int[][] dataArray, string fileName)
        {
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", HttpUtility.UrlEncode(fileName)));
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
            HttpContext.Current.Response.Clear();
            ISheet sheet;
            sheet = excel.ActiveSheet;
            sheet.ForceFormulaRecalculation = true;   //允许excel里的公式生效

            ICellStyle style = excel.CreateCellStyle();
            style.BorderBottom = BorderStyle.THIN;
            style.BorderLeft = BorderStyle.THIN;
            style.BorderRight = BorderStyle.THIN;
            style.BorderTop = BorderStyle.THIN;
            style.WrapText = true;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            ///////////////
            ICellStyle styleGray = excel.CreateCellStyle();
            styleGray.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_40_PERCENT.index;
            styleGray.FillPattern = FillPatternType.SOLID_FOREGROUND;//设置背景是否填充
            styleGray.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_40_PERCENT.index;
            styleGray.VerticalAlignment = VerticalAlignment.CENTER;

            excel.SetValue(3, 16, ProductInfo);
            excel.SetValue(3, 25, Convert.ToDouble(avgWeightStr));
            excel.SetValue(3, 28, Convert.ToDouble(DivWeightStr));
            excel.SetValue(4, 26, DateTime.Now);

            int rowIndex = 13;
            for (int i = 0; i < 6; i++)
            {
                int colIndex = 2;
                for (int j = 0; j < dataArray[i].Length; j++)
                {
                    excel.SetValue(rowIndex, colIndex, Convert.ToDouble(dataArray[i][j]) / 100);  //给excel里格子赋值
          　　　　　　//excel.SetStyle(rowIndex, colIndex, styleGray); 设置格式
                    colIndex++;
                }
                rowIndex++;
            }
            byte[] fs;
            fs = WriteToStream().ToArray();
            HttpContext.Current.Response.BinaryWrite(WriteToStream().GetBuffer());
            HttpContext.Current.Response.End();
        }

    }
}

添加 类NPOIExcel.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Drawing;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util;

public class NPOIExcel
{
    internal IWorkbook Book { get; set; }

    private int sheetID = 0;
    /// <summary>
    /// 当前活动的SheetID，所有的操作将指向这个Sheet
    /// </summary>
    public int ActiveSheetID
    {
        get
        {
            return sheetID;
        }
        set
        {
            sheetID = value;
        }
    }

    /// <summary>
    /// 当前活动的SheetName，所有的操作将指向这个Sheet
    /// </summary>
    public string ActiveSheetName
    {
        get
        {
            return Book.GetSheetAt(sheetID).SheetName;
        }
        set
        {
            sheetID = Book.GetSheetIndex(value);
        }
    }
    /// <summary>
    /// 当前活动的Sheet，所有的操作将指向这个Sheet
    /// </summary>
    public ISheet ActiveSheet
    {
        get
        {
            return Book.GetSheetAt(sheetID);
        }
    }

    /// <summary>
    /// 第一行非空行的行号
    /// </summary>
    public int FirstRowNum
    {
        get
        {
            return Book.GetSheetAt(sheetID).FirstRowNum;
        }
    }

    /// <summary>
    /// 最后一行非空行的行号
    /// </summary>
    public int LastRostNum
    {
        get
        {
            return Book.GetSheetAt(sheetID).LastRowNum;
        }
    }

    /// <summary>
    /// 无模板的Excel生成或操作
    /// </summary>
    public NPOIExcel()
    {
        Book = new HSSFWorkbook();
        Book.CreateSheet();
    }

    public NPOIExcel(Stream fileStream, string fileName)
    {
        if (fileName.Substring(fileName.LastIndexOf(".")) == ".xls")
        {
            Book = new HSSFWorkbook(fileStream);
        }
        else
        {
            Book = new XSSFWorkbook(fileStream);
        }
    }
    /// <summary>
    /// 带模板或数据的Excel生成或操作
    /// </summary>
    /// <param name="fileName"></param>
    public NPOIExcel(string fileName)
    {
        Book = CreateBook(fileName);
    }

    /// <summary>
    /// 创建Excel Book
    /// </summary>
    /// <param name="fileName">模板文件名</param>
    /// <returns></returns>
    private IWorkbook CreateBook(string fileName)
    {
        FileInfo file = new FileInfo(fileName);
        if (!file.Exists)
        {
            File.Create(fileName).Close();
        }
        FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
        IWorkbook book;
        if (file.Extension == ".xls")
        {
            book = new HSSFWorkbook(fs);
        }
        else
        {
            book = new XSSFWorkbook(fs);
        }
        fs.Close();
        if (book.NumberOfSheets == 0)
        {
            book.CreateSheet();
        }
        return book;
    }

    /// <summary>
    /// 新建Sheet
    /// </summary>
    /// <returns>新建Sheet</returns>
    public ISheet CreateSheet()
    {
        return Book.CreateSheet();
    }

    /// <summary>
    /// 新建Sheet
    /// </summary>
    /// <param name="sheetName">新建Sheet的名称</param>
    /// <returns>新建Sheet</returns>
    public ISheet CreateSheet(string sheetName)
    {
        return Book.CreateSheet(sheetName);
    }

    /// <summary>
    /// 设置行高
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="height">高度</param>
    public void SetRowHeight(int rowIndex, float height)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        row.Height = (short)(height * 20);
    }

    /// <summary>
    /// 设置列宽
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="columnIndex">列号</param>
    /// <param name="width">宽度</param>
    public void SetColumnWidth(int columnIndex, short width)
    {
        Book.GetSheetAt(sheetID).SetColumnWidth(columnIndex, width * 256);
    }

    /// <summary>
    /// 获取或设置默认行高
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    public short DefaultRowHeight
    {
        get
        {
            return (short)(Book.GetSheetAt(sheetID).DefaultRowHeight / 20);
        }
        set
        {
            Book.GetSheetAt(sheetID).DefaultRowHeight = value * 20;
        }
    }

    /// <summary>
    /// 获取或设置默认列宽
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    public int DefaultColWidth
    {
        get
        {
            return Book.GetSheetAt(sheetID).DefaultColumnWidth;
        }
        set
        {
            Book.GetSheetAt(sheetID).DefaultColumnWidth = value;
        }
    }

    /// <summary>
    /// 某一列的列宽自动调整大小
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="colIndex">列号</param>
    public void AutoColWidth(int colIndex)
    {
        Book.GetSheetAt(sheetID).AutoSizeColumn(colIndex, true);
    }

    /// <summary>
    /// 隐藏一行
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    public void HiddenRow(int rowIndex)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        row.ZeroHeight = true;
    }

    /// <summary>
    /// 删除一行
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    public void RemoveRow(int rowIndex)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row != null)
        {
            ActiveSheet.RemoveRow(row);
        }
    }

    /// <summary>
    /// 读取单元格的值
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <returns>单元格的值</returns>
    public object ReadValue(int rowIndex, int columnIndex, bool? isDateTime = null)
    {
        try
        {
            ICell cell = Book.GetSheetAt(sheetID).GetRow(rowIndex).GetCell(columnIndex);
            short df = cell.CellStyle.DataFormat;

            //return cell.ToString();
            switch (cell.CellType)
            {
                case CellType.BLANK:
                    return null;
                case CellType.BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.ERROR:
                    throw new Exception("Cell Value Error");
                case CellType.FORMULA:
                    {
                        switch (cell.CachedFormulaResultType)
                        {
                            case CellType.BLANK:
                                return "";
                            case CellType.BOOLEAN:
                                return cell.BooleanCellValue;
                            case CellType.ERROR:
                                throw new Exception("Cell Value Error");
                            case CellType.FORMULA:
                                throw new Exception("The formula of this cell is too complex！");
                            case CellType.NUMERIC:
                                if (isDateTime == null)
                                {
                                    if (DateUtil.IsCellDateFormatted(cell))
                                    {
                                        return cell.DateCellValue;
                                    }
                                    else
                                    {
                                        return cell.NumericCellValue;
                                    }
                                }
                                else if (isDateTime == true)
                                {
                                    return cell.DateCellValue;
                                }
                                else
                                {
                                    return cell.NumericCellValue;
                                }
                            case CellType.STRING:
                                return cell.StringCellValue;
                            case CellType.Unknown:
                                return cell.ToString();
                            default:
                                return cell.ToString();
                        }
                    }
                case CellType.NUMERIC:
                    {
                        if (isDateTime == null)
                        {
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                return cell.DateCellValue;
                            }
                            else
                            {
                                return cell.NumericCellValue;
                            }
                        }
                        else if (isDateTime == true)
                        {
                            return cell.DateCellValue;
                        }
                        else
                        {
                            return cell.NumericCellValue;
                        }
                    }
                case CellType.STRING:
                    return cell.StringCellValue;
                case CellType.Unknown:
                    return cell.ToString();
                default:
                    return cell.ToString();
            }
        }
        catch (System.NullReferenceException)
        {
            return null;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    /// <summary>
    /// 设置单元格的值
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <param name="value">单元格的值</param>
    public void SetValue(int rowIndex, int columnIndex, object value)
    {
        SetValue(rowIndex, columnIndex, value, false);
    }

    /// <summary>
    /// 设置单元格的值
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <param name="value">单元格的值</param>
    /// <param name="isFormula">是否是公式</param>
    public void SetValue(int rowIndex, int columnIndex, object value, bool isFormula)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        ICell cell = row.GetCell(columnIndex);
        if (cell == null)
        {
            cell = row.CreateCell(columnIndex);
        }
        if (value == null)
        {
            cell.SetCellValue("");
        }
        if (isFormula)
        {
            cell.SetCellFormula(value.ToString());
        }
        else
        {
            if (value is short)
            {
                cell.SetCellValue((short)value);
            }
            else if (value is int)
            {
                cell.SetCellValue((int)value);
            }
            else if (value is long)
            {
                cell.SetCellValue((long)value);
            }
            else if (value is float)
            {
                cell.SetCellValue((float)value);
            }
            else if (value is double)
            {
                cell.SetCellValue((double)value);
            }
            else if (value is bool)
            {
                cell.SetCellValue((bool)value);
            }
            else if (value is DateTime)
            {
                cell.SetCellValue((DateTime)value);
            }
            else if (value == null)
            {
            }
            else
            {
                cell.SetCellValue(value.ToString());
            }
        }

    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="type">验证类型</param>
    /// <param name="operatorType">验证方式</param>
    /// <param name="minValue">最小值</param>
    /// <param name="maxValue">最大值</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue)
    {
        SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, type, operatorType, minValue, maxValue, "", "");
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="type">验证类型</param>
    /// <param name="operatorType">验证方式</param>
    /// <param name="minValue">最小值</param>
    /// <param name="maxValue">最大值</param>
    /// <param name="formate">数据格式</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue, string formate)
    {
        SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, type, operatorType, minValue, maxValue, formate, "");
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="type">验证类型</param>
    /// <param name="operatorType">验证方式</param>
    /// <param name="minValue">最小值</param>
    /// <param name="maxValue">最大值</param>
    /// <param name="formate">数据格式</param>
    /// <param name="AlertMassage">报错信息</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue, string formate, string AlertMassage)
    {
        CellRangeAddressList regions = new CellRangeAddressList(startRowIndex, EndRowIndex, startColInex, endColIndex);
        DVConstraint constraint = DVConstraint.CreateNumericConstraint(ValidationType.ANY, 0, null, null);
        switch (type)
        {
            case NPOIDataType.Integer:
                constraint = DVConstraint.CreateNumericConstraint(ValidationType.INTEGER, (int)operatorType, minValue, maxValue);
                break;
            case NPOIDataType.Float:
                constraint = DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, (int)operatorType, minValue, maxValue);
                break;
            case NPOIDataType.Date:
                if (formate == "")
                {
                    formate = "yyyy/MM/dd";
                }
                constraint = DVConstraint.CreateDateConstraint((int)operatorType, minValue, maxValue, formate);
                break;
            case NPOIDataType.Time:
                constraint = DVConstraint.CreateTimeConstraint((int)operatorType, minValue, maxValue);
                break;
            case NPOIDataType.TextLength:
                constraint = DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, (int)operatorType, minValue, maxValue);
                break;
            default:
                break;
        }

        HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions, constraint);
        if (!string.IsNullOrEmpty(AlertMassage))
        {
            dataValidate1.CreateErrorBox("Error", AlertMassage);
        }
        ActiveSheet.AddValidationData(dataValidate1);

    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="dataRange">值系列</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, string[] dataRange)
    {

        SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, dataRange, "");
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="dataRange">值系列</param>
    /// <param name="alertMassage">报错信息</param>
    public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string[] dataRange, string alertMassage)
    {
        ISheetConditionalFormatting hscf = ActiveSheet.SheetConditionalFormatting;
        CellRangeAddress[] regions = {
                new CellRangeAddress(startRowIndex, endRowIndex,startColInex,endColIndex)
            };

        CellRangeAddressList rangeList = new CellRangeAddressList();
        rangeList.AddCellRangeAddress(new CellRangeAddress(startRowIndex, endRowIndex, startColInex, endColIndex));
        DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(dataRange);
        HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);

        if (!string.IsNullOrEmpty(alertMassage))
        {
            dataValidation.CreateErrorBox("Error", alertMassage);
        }

        ActiveSheet.AddValidationData(dataValidation);
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="formula">计算公式</param>
    /// <param name="alertMassage">报错信息</param>
    public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string formula, string alertMassage)
    {
        ISheetConditionalFormatting hscf = ActiveSheet.SheetConditionalFormatting;
        CellRangeAddress[] regions = {
                new CellRangeAddress(startRowIndex, endRowIndex,startColInex,endColIndex)
            };

        CellRangeAddressList rangeList = new CellRangeAddressList();
        rangeList.AddCellRangeAddress(new CellRangeAddress(startRowIndex, endRowIndex, startColInex, endColIndex));
        DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint(formula);
        HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);

        if (!string.IsNullOrEmpty(alertMassage))
        {
            dataValidation.CreateErrorBox("Error", alertMassage);
        }

        ActiveSheet.AddValidationData(dataValidation);
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="formula">计算公式</param>
    public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string formula)
    {
        SetValueRange(startRowIndex, endColIndex, startRowIndex, endColIndex, formula, "");
    }

    /// <summary>
    /// 生成单元格样式
    /// </summary>
    /// <returns>与当前Excel相关的单元格样式</returns>
    public ICellStyle CreateCellStyle()
    {
        return Book.CreateCellStyle();
    }

    /// <summary>
    /// 生成字体
    /// </summary>
    /// <returns>与当前Excel相关的字体</returns>
    public IFont CreateFont()
    {
        return Book.CreateFont();
    }

    /// <summary>
    /// 设置单元格样式
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <param name="style">样式</param>
    public void SetStyle(int rowIndex, int columnIndex, ICellStyle style)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        ICell cell = row.GetCell(columnIndex);
        if (cell == null)
        {
            cell = row.CreateCell(columnIndex);
        }
        cell.CellStyle = style;
    }

    /// <summary>
    /// 合并单元格
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行号</param>
    /// <param name="startColumnIndex">开始列号</param>
    /// <param name="endRowIndex">结束行号</param>
    /// <param name="endColumnIndex">结束列号</param>
    public void MergeCells(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
    {
        int index = Book.GetSheetAt(sheetID).AddMergedRegion(new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex));
    }

    /// <summary>
    /// 拆分单元格
    /// 注1：只对当前ActiveSheet有效
    /// 注2：只有合并的单元格才能拆分
    /// </summary>
    /// <param name="startRowIndex">开始行号</param>
    /// <param name="startColumnIndex">开始列号</param>
    public void UnMergeCells(int startRowIndex, int startColumnIndex)
    {
        int merges = Book.GetSheetAt(sheetID).NumMergedRegions;
        CellRangeAddress merge;
        for (int i = 0; i < merges; i++)
        {
            merge = Book.GetSheetAt(sheetID).GetMergedRegion(i);
            if (merge.FirstRow == startRowIndex && merge.FirstColumn == startColumnIndex)
            {
                Book.GetSheetAt(sheetID).RemoveMergedRegion(i);
                break;
            }
        }
    }

    /// <summary>
    /// 保存到文件
    /// 注：有模板的，文件扩展名与模板一样；没有模板的，文件扩展名为“.xls”；
    /// </summary>
    /// <param name="fileName">保存文件名</param>
    public void Save(string fileName)
    {
        FileStream file = new FileStream(fileName, FileMode.Create);
        Book.Write(file);
        file.Close();
    }

    /// <summary>
    /// 保存到流
    /// 注：保存或下载时，有模板的，文件扩展名与模板一样；没有模板的，文件扩展名为“.xls”；
    /// </summary>
    /// <returns>内存流</returns>
    public MemoryStream Save()
    {
        MemoryStream ms = new MemoryStream();
        Book.Write(ms);
        return ms;
    }

    /// <summary>
    /// 把Excel读成DataSet
    /// 注：必须是正规表格式
    /// </summary>
    /// <returns>读出的Excel</returns>
    public DataSet ReadAsDataSet()
    {
        DataSet rtn = new DataSet();
        for (int i = 0; i < SheetCount; i++)
        {
            ISheet sheet = Book.GetSheetAt(i);
            rtn.Tables.Add(GetDataTableBySheet(sheet));
        }
        return rtn;
    }

    private DataTable GetDataTableBySheet(ISheet sheet)
    {
        DataTable dt = new DataTable(sheet.SheetName);
        int maxCols = 0;
        object value;
        while ((value = ReadValue(sheet, 0, maxCols)) != null)
        {
            dt.Columns.Add(value.ToString());
            maxCols++;
        }

        int row = 1;

        bool emptyRow = false;
        int emptyRowCount = 0;
        while (emptyRowCount < 10)
        {
            emptyRow = true;
            DataRow dr = dt.NewRow();
            for (int i = 0; i < maxCols; i++)
            {
                value = ReadValue(sheet, row, i);
                if (value != null)
                {
                    dr[i] = value;
                    emptyRow = false;
                }
            }
            if (!emptyRow)
            {
                dt.Rows.Add(dr);
                emptyRowCount = 0;
            }
            else
            {
                emptyRowCount++;
            }
            row++;
        }

        return dt;
    }

    /// <summary>
    /// 根据SheetName导出数据为DataTable
    /// </summary>
    /// <param name="sheetName">Sheet名称</param>
    /// <returns></returns>
    public DataTable GetDataTableBySheet(string sheetName)
    {

        ISheet sheet = Book.GetSheet(sheetName);
        if (sheet != null)
        {
            return GetDataTableBySheet(sheet);
        }
        return null;

    }

    /// <summary>
    /// 根据SheetName导出数据为DataTable
    /// </summary>
    /// <param name="sheetIndex">Sheet编号</param>
    /// <returns></returns>
    public DataTable GetDataTableBySheet(int sheetIndex)
    {

        ISheet sheet = Book.GetSheetAt(sheetIndex);
        if (sheet != null)
        {
            return GetDataTableBySheet(sheet);
        }
        return null;

    }

    /// <summary>
    /// 写入表格
    /// </summary>
    /// <param name="Data">表格数据</param>
    /// <param name="col">写入的起始列</param>
    /// <param name="row">写入的起始行</param>
    /// <param name="titleColor">标题颜色</param>
    /// <param name="fullBorder">是否需要四周边框</param>
    public void WriteDataTable(DataTable Data, int col = 1, int row = 1, short? titleColor = null, bool fullBorder = true)
    {
        if (Data == null)
        {
            return;
        }

        var titleStyle = CreateCellStyle();
        var rowStyle = CreateCellStyle();
        if (titleColor != null)
            titleStyle.FillForegroundColor = titleColor.Value;

        titleStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
        if (fullBorder)
        {
            titleStyle.BorderBottom = BorderStyle.THIN;
            titleStyle.BorderLeft = BorderStyle.THIN;
            titleStyle.BorderRight = BorderStyle.THIN;
            titleStyle.BorderTop = BorderStyle.THIN;
            titleStyle.BottomBorderColor = NPOIColor.BLACK;
            titleStyle.LeftBorderColor = NPOIColor.BLACK;
            titleStyle.RightBorderColor = NPOIColor.BLACK;
            titleStyle.TopBorderColor = NPOIColor.BLACK;
            rowStyle.BorderBottom = BorderStyle.THIN;
            rowStyle.BorderLeft = BorderStyle.THIN;
            rowStyle.BorderRight = BorderStyle.THIN;
            rowStyle.BorderTop = BorderStyle.THIN;
            rowStyle.BottomBorderColor = NPOIColor.BLACK;
            rowStyle.LeftBorderColor = NPOIColor.BLACK;
            rowStyle.RightBorderColor = NPOIColor.BLACK;
            rowStyle.TopBorderColor = NPOIColor.BLACK;
        }

        int iCol = 0, iRow = 1;

        foreach (DataColumn dc in Data.Columns)
        {
            SetValue(row, col + iCol, dc.ColumnName);
            SetStyle(row, col + iCol, titleStyle);
            iCol++;
        }

        rowStyle.FillForegroundColor = NPOIColor.WHITE;

        foreach (DataRow dr in Data.Rows)
        {
            iCol = 0;
            foreach (DataColumn dc in Data.Columns)
            {
                SetValue(row + iRow, col + iCol, dr[dc]);
                SetStyle(row + iRow, col + iCol, rowStyle);
                iCol++;
            }
            iRow++;
        }

        for (int i = 0; i < iCol; i++)
        {
            this.AutoColWidth(i);
        }
    }

    /// <summary>
    /// 读取单元格的值
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <returns>单元格的值</returns>
    public object ReadValue(ISheet sheet, int rowIndex, int columnIndex, bool? isDateTime = null)
    {
        try
        {
            ICell cell = sheet.GetRow(rowIndex).GetCell(columnIndex);
            short df = cell.CellStyle.DataFormat;

            //return cell.ToString();
            switch (cell.CellType)
            {
                case CellType.BLANK:
                    return null;
                case CellType.BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.ERROR:
                    throw new Exception("Cell Value Error");
                case CellType.FORMULA:
                    {
                        switch (cell.CachedFormulaResultType)
                        {
                            case CellType.BLANK:
                                return "";
                            case CellType.BOOLEAN:
                                return cell.BooleanCellValue;
                            case CellType.ERROR:
                                throw new Exception("Cell Value Error");
                            case CellType.FORMULA:
                                throw new Exception("The formula of this cell is too complex！");
                            case CellType.NUMERIC:
                                if (isDateTime == null)
                                {
                                    if (DateUtil.IsCellDateFormatted(cell))
                                    {
                                        return cell.DateCellValue;
                                    }
                                    else
                                    {
                                        return cell.NumericCellValue;
                                    }
                                }
                                else if (isDateTime == true)
                                {
                                    return cell.DateCellValue;
                                }
                                else
                                {
                                    return cell.NumericCellValue;
                                }
                            case CellType.STRING:
                                return cell.StringCellValue;
                            case CellType.Unknown:
                                return cell.ToString();
                            default:
                                return cell.ToString();
                        }
                    }
                case CellType.NUMERIC:
                    {
                        if (isDateTime == null)
                        {
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                return cell.DateCellValue;
                            }
                            else
                            {
                                return cell.NumericCellValue;
                            }
                        }
                        else if (isDateTime == true)
                        {
                            return cell.DateCellValue;
                        }
                        else
                        {
                            return cell.NumericCellValue;
                        }
                    }
                case CellType.STRING:
                    return cell.StringCellValue;
                case CellType.Unknown:
                    return cell.ToString();
                default:
                    return cell.ToString();
            }
        }
        catch (System.NullReferenceException)
        {
            return null;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public int SheetCount
    {
        get
        {
            return Book.NumberOfSheets;
        }
    }

    public string GetSheetName(int index)
    {
        return Book.GetSheetName(index);
    }

    public void AddPicture(byte[] data, int row, int col)
    {
        int picIndex = Book.AddPicture(data, PictureType.PNG);

        IDrawing draw = ActiveSheet.CreateDrawingPatriarch();

        IClientAnchor anchor = draw.CreateAnchor(0, 0, 255, 255, col, row, col + 5, col + 5);
        IPicture pic = draw.CreatePicture(anchor, picIndex);
        pic.Resize();
    }
}

public enum OperatorTypes
{
    /// <summary>
    /// 介于最大值与小值之间
    /// </summary>
    BETWEEN = OperatorType.BETWEEN,
    /// <summary>
    /// 等于最小值
    /// </summary>
    EQUAL = OperatorType.EQUAL,
    /// <summary>
    /// 大于或等于最小值
    /// </summary>
    GREATER_OR_EQUAL = OperatorType.GREATER_OR_EQUAL,
    /// <summary>
    /// 大于最小值
    /// </summary>
    GREATER_THAN = OperatorType.GREATER_THAN,
    /// <summary>
    /// 忽略
    /// </summary>
    NO_COMPARISON = OperatorType.IGNORED,
    /// <summary>
    /// 小于或等于最小值
    /// </summary>
    LESS_OR_EQUAL = OperatorType.LESS_OR_EQUAL,
    /// <summary>
    /// 小于最小值
    /// </summary>
    LESS_THAN = OperatorType.LESS_THAN,
    /// <summary>
    /// 不在最小值与最大值之间
    /// </summary>
    NOT_BETWEEN = OperatorType.NOT_BETWEEN,
    /// <summary>
    /// 不等于最小值
    /// </summary>
    NOT_EQUAL = OperatorType.NOT_EQUAL
}

public enum NPOIDataType
{
    /// <summary>
    /// 验证整数
    /// </summary>
    Integer,
    /// <summary>
    /// 验证符点数
    /// </summary>
    Float,
    /// <summary>
    /// 验证日期
    /// </summary>
    Date,
    /// <summary>
    /// 验证时间
    /// </summary>
    Time,
    /// <summary>
    /// 验证字符长度
    /// </summary>
    TextLength
}

public static class NPOIColor
{

    /// <summary>
    /// 红色
    /// </summary>
    public static short RED { get { return NPOI.HSSF.Util.HSSFColor.RED.index; } }

    /// <summary>
    /// 蓝色
    /// </summary>
    public static short BLUE { get { return NPOI.HSSF.Util.HSSFColor.BLUE.index; } }

    /// <summary>
    /// 浅绿色
    /// </summary>
    public static short AQUA { get { return NPOI.HSSF.Util.HSSFColor.AQUA.index; } }

    /// <summary>
    /// 自动
    /// </summary>
    public static short AUTOMATIC { get { return NPOI.HSSF.Util.HSSFColor.AUTOMATIC.index; } }

    /// <summary>
    /// 黑色
    /// </summary>
    public static short BLACK { get { return NPOI.HSSF.Util.HSSFColor.BLACK.index; } }

    /// <summary>
    /// 蓝灰色
    /// </summary>
    public static short BLUE_GREY { get { return NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index; } }

    /// <summary>
    /// 明绿色
    /// </summary>
    public static short BRIGHT_GREEN { get { return NPOI.HSSF.Util.HSSFColor.BRIGHT_GREEN.index; } }

    /// <summary>
    /// 棕色
    /// </summary>
    public static short BROWN { get { return NPOI.HSSF.Util.HSSFColor.BROWN.index; } }

    /// <summary>
    /// 正常
    /// </summary>
    public static short COLOR_NORMAL { get { return NPOI.HSSF.Util.HSSFColor.COLOR_NORMAL; } }

    /// <summary>
    /// 珊瑚色
    /// </summary>
    public static short CORAL { get { return NPOI.HSSF.Util.HSSFColor.CORAL.index; } }

    /// <summary>
    /// 亮蓝色
    /// </summary>
    public static short CORNFLOWER_BLUE { get { return NPOI.HSSF.Util.HSSFColor.CORNFLOWER_BLUE.index; } }

    /// <summary>
    /// 深蓝色
    /// </summary>
    public static short DARK_BLUE { get { return NPOI.HSSF.Util.HSSFColor.DARK_BLUE.index; } }

    /// <summary>
    /// 深绿色
    /// </summary>
    public static short DARK_GREEN { get { return NPOI.HSSF.Util.HSSFColor.DARK_GREEN.index; } }

    /// <summary>
    /// 深红色
    /// </summary>
    public static short DARK_RED { get { return NPOI.HSSF.Util.HSSFColor.DARK_RED.index; } }

    /// <summary>
    /// 深茶色
    /// </summary>
    public static short DARK_TEAL { get { return NPOI.HSSF.Util.HSSFColor.DARK_TEAL.index; } }

    /// <summary>
    /// 深黄
    /// </summary>
    public static short DARK_YELLOW { get { return NPOI.HSSF.Util.HSSFColor.DARK_YELLOW.index; } }

    /// <summary>
    /// 金色
    /// </summary>
    public static short GOLD { get { return NPOI.HSSF.Util.HSSFColor.GOLD.index; } }

    /// <summary>
    /// 绿色
    /// </summary>
    public static short GREEN { get { return NPOI.HSSF.Util.HSSFColor.GREEN.index; } }

    /// <summary>
    /// 25%灰色
    /// </summary>
    public static short GREY_25_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index; } }

    /// <summary>
    /// 40%灰色
    /// </summary>
    public static short GREY_40_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_40_PERCENT.index; } }

    /// <summary>
    /// 50%灰色
    /// </summary>
    public static short GREY_50_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_50_PERCENT.index; } }

    /// <summary>
    /// 80%灰色
    /// </summary>
    public static short GREY_80_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_80_PERCENT.index; } }

    /// <summary>
    /// 靛蓝色
    /// </summary>
    public static short INDIGO { get { return NPOI.HSSF.Util.HSSFColor.INDIGO.index; } }

    /// <summary>
    /// 淡紫色
    /// </summary>
    public static short LAVENDER { get { return NPOI.HSSF.Util.HSSFColor.LAVENDER.index; } }

    /// <summary>
    /// 粉黄色
    /// </summary>
    public static short LEMON_CHIFFON { get { return NPOI.HSSF.Util.HSSFColor.LEMON_CHIFFON.index; } }

    /// <summary>
    /// 淡蓝色
    /// </summary>
    public static short LIGHT_BLUE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_BLUE.index; } }

    /// <summary>
    /// 淡亮蓝色
    /// </summary>
    public static short LIGHT_CORNFLOWER_BLUE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_CORNFLOWER_BLUE.index; } }

    /// <summary>
    /// 淡绿色
    /// </summary>
    public static short LIGHT_GREEN { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index; } }

    /// <summary>
    /// 淡桔黄色
    /// </summary>
    public static short LIGHT_ORANGE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_ORANGE.index; } }

    /// <summary>
    /// 淡蓝绿色
    /// </summary>
    public static short LIGHT_TURQUOISE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_TURQUOISE.index; } }

    /// <summary>
    /// 淡黄色
    /// </summary>
    public static short LIGHT_YELLOW { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index; } }

    /// <summary>
    /// 绿黄色
    /// </summary>
    public static short LIME { get { return NPOI.HSSF.Util.HSSFColor.LIME.index; } }

    /// <summary>
    /// 栗色
    /// </summary>
    public static short MAROON { get { return NPOI.HSSF.Util.HSSFColor.MAROON.index; } }

    /// <summary>
    /// 橄榄绿色
    /// </summary>
    public static short OLIVE_GREEN { get { return NPOI.HSSF.Util.HSSFColor.OLIVE_GREEN.index; } }

    /// <summary>
    /// 桔色
    /// </summary>
    public static short ORANGE { get { return NPOI.HSSF.Util.HSSFColor.ORANGE.index; } }

    /// <summary>
    /// 白灰蓝色
    /// </summary>
    public static short PALE_BLUE { get { return NPOI.HSSF.Util.HSSFColor.PALE_BLUE.index; } }

    /// <summary>
    /// 粉红色
    /// </summary>
    public static short PINK { get { return NPOI.HSSF.Util.HSSFColor.PINK.index; } }

    /// <summary>
    /// 紫红色
    /// </summary>
    public static short PLUM { get { return NPOI.HSSF.Util.HSSFColor.PLUM.index; } }

    /// <summary>
    /// 玫瑰红色
    /// </summary>
    public static short ROSE { get { return NPOI.HSSF.Util.HSSFColor.ROSE.index; } }

    /// <summary>
    /// 高贵蓝
    /// </summary>
    public static short ROYAL_BLUE { get { return NPOI.HSSF.Util.HSSFColor.ROYAL_BLUE.index; } }

    /// <summary>
    /// 海绿色
    /// </summary>
    public static short SEA_GREEN { get { return NPOI.HSSF.Util.HSSFColor.SEA_GREEN.index; } }

    /// <summary>
    /// 天空蓝
    /// </summary>
    public static short SKY_BLUE { get { return NPOI.HSSF.Util.HSSFColor.SKY_BLUE.index; } }

    /// <summary>
    /// 棕褐色
    /// </summary>
    public static short TAN { get { return NPOI.HSSF.Util.HSSFColor.TAN.index; } }

    /// <summary>
    /// 茶色
    /// </summary>
    public static short TEAL { get { return NPOI.HSSF.Util.HSSFColor.TEAL.index; } }

    /// <summary>
    /// 蓝绿色
    /// </summary>
    public static short TURQUOISE { get { return NPOI.HSSF.Util.HSSFColor.TURQUOISE.index; } }

    /// <summary>
    /// 紫色
    /// </summary>
    public static short VIOLET { get { return NPOI.HSSF.Util.HSSFColor.VIOLET.index; } }

    /// <summary>
    /// 白色
    /// </summary>
    public static short WHITE { get { return NPOI.HSSF.Util.HSSFColor.WHITE.index; } }

    /// <summary>
    /// 黄色
    /// </summary>
    public static short YELLOW { get { return NPOI.HSSF.Util.HSSFColor.YELLOW.index; } }

}

/// <summary>
/// 针对excel的Oledb
/// </summary>
public class OleDbExcel
{
    /// <summary>
    /// OLEDB连接
    /// </summary>
    public OleDbConnection Connection
    {
        get;
        set;
    }

    /// <summary>
    /// 用Oledb对Excel进行操作
    /// 注：必须是标准表形式Excel内容
    /// </summary>
    /// <param name="excelFile">Excel文件</param>
    public OleDbExcel(string excelFile)
    {
        string conStr = string.Empty;
        FileInfo file = new FileInfo(excelFile);
        if (!file.Exists) { throw new Exception("文件不存在"); }
        string extension = file.Extension;
        switch (extension)
        {
            case ".xls":
                conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                break;
            case ".xlsx":
                conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                break;
            default:
                conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                break;
        }

        //链接Excel
        Connection = new OleDbConnection(conStr);
    }

    private List<string> tableNames;

    /// <summary>
    /// 获取Excel内的Sheet名称
    /// </summary>
    public List<string> Sheets
    {
        get
        {
            if (tableNames == null)
            {
                try
                {
                    tableNames = new List<string>();
                    //读取Excel里面的sheet名
                    Connection.Open();

                    DataTable schemaTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        DataRow dr = schemaTable.Rows[i];
                        string tbName = dr["table_name"].ToString();
                        if (tbName[tbName.Length - 1] == '$')
                        {
                            tableNames.Add(tbName);
                        }
                    }
                    Connection.Close();
                }
                catch (Exception ex)
                {
                    if (Connection.State != ConnectionState.Closed)
                    {
                        Connection.Close();
                    }
                    throw new Exception(ex.Message, ex);
                }
            }
            return tableNames;
        }
    }

    /// <summary>
    /// 查询出所有数据
    /// </summary>
    /// <param name="tableName">Sheet名称</param>
    /// <returns>sheet内的所有数据</returns>
    public DataSet QueryAll(string tableName)
    {
        try
        {
            DataSet excelData = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = new OleDbCommand();
            adapter.SelectCommand.Connection = Connection;
            adapter.SelectCommand.CommandText = string.Format("SELECT * FROM {0}", "[" + tableName + "]");
            adapter.Fill(excelData);
            return excelData;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }

    /// <summary>
    /// 查询出所有数据
    /// </summary>
    /// <param name="tableIndex">Sheet序号（从0开始）</param>
    /// <returns>sheet内的所有数据</returns>
    public DataSet QueryAll(int tableIndex)
    {
        return QueryAll(Sheets[tableIndex]);
    }

    /// <summary>
    /// 利用Sql进行查询
    /// </summary>
    /// <param name="sql">Sql语句</param>
    /// <returns>查询出的数据</returns>
    public DataSet Query(string sql)
    {
        try
        {
            DataSet excelData = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, Connection);
            adapter.Fill(excelData);
            return excelData;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }

    /// <summary>
    /// 利用Sql进行查询
    /// </summary>
    /// <param name="sql">Sql语句</param>
    /// <param name="param">查询参数</param>
    /// <returns>查询出的数据</returns>
    public DataSet Query(string sql, params OleDbParameter[] param)
    {
        try
        {
            DataSet excelData = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, Connection);
            adapter.SelectCommand.Parameters.AddRange(param);
            adapter.Fill(excelData);
            return excelData;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }

    /// <summary>
    /// 利用Sql进行数据操作
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns>影响的行数</returns>
    public int ExecuteSql(string sql)
    {
        try
        {
            Connection.Open();
            OleDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = sql;
            int rtn = cmd.ExecuteNonQuery();
            Connection.Close();
            return rtn;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }

    /// <summary>
    /// 利用Sql进行数据操作
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <param name="param">执行参数</param>
    /// <returns>影响的行数</returns>
    public int ExecuteSql(string sql, params OleDbParameter[] param)
    {
        try
        {
            Connection.Open();
            OleDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(param);
            int rtn = cmd.ExecuteNonQuery();
            Connection.Close();
            return rtn;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }
}

3、使用前台调用 传入参数，获取数据

[HttpPost]
public string PPKExport(string id)
{var ProductInfo = "";

var avgWeightStr = "";

var DivWeightStr = "";int[][] dataArray = new int[8][];

string templateFile = "~/ExcelTemp/PPKTemplate.xls";      //模板位置
 ex = new ExportExcel(Server.MapPath(templateFile));
 string fileName = "Xbar-R控制图.xls";                      //导出的excel命名
 ex.PPKToExcel(ProductInfo,avgWeightStr, DivWeightStr, dataArray, fileName);     //传入参数
  return fileName;
｝

 4、效果

界面导出按钮

点击导出按钮，选择位置保存即可。

二、将界面table里的内容直接生成excel

1、操作excel的NPOI工具类

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Drawing;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util;

public class NPOIExcel
{
    internal IWorkbook Book { get; set; }

    private int sheetID = 0;
    /// <summary>
    /// 当前活动的SheetID，所有的操作将指向这个Sheet
    /// </summary>
    public int ActiveSheetID
    {
        get
        {
            return sheetID;
        }
        set
        {
            sheetID = value;
        }
    }

    /// <summary>
    /// 当前活动的SheetName，所有的操作将指向这个Sheet
    /// </summary>
    public string ActiveSheetName
    {
        get
        {
            return Book.GetSheetAt(sheetID).SheetName;
        }
        set
        {
            sheetID = Book.GetSheetIndex(value);
        }
    }
    /// <summary>
    /// 当前活动的Sheet，所有的操作将指向这个Sheet
    /// </summary>
    public ISheet ActiveSheet
    {
        get
        {
            return Book.GetSheetAt(sheetID);
        }
    }

    /// <summary>
    /// 第一行非空行的行号
    /// </summary>
    public int FirstRowNum
    {
        get
        {
            return Book.GetSheetAt(sheetID).FirstRowNum;
        }
    }

    /// <summary>
    /// 最后一行非空行的行号
    /// </summary>
    public int LastRostNum
    {
        get
        {
            return Book.GetSheetAt(sheetID).LastRowNum;
        }
    }

    /// <summary>
    /// 无模板的Excel生成或操作
    /// </summary>
    public NPOIExcel()
    {
        Book = new HSSFWorkbook();
        Book.CreateSheet();
    }

    public NPOIExcel(Stream fileStream, string fileName)
    {
        if (fileName.Substring(fileName.LastIndexOf(".")) == ".xls")
        {
            Book = new HSSFWorkbook(fileStream);
        }
        else
        {
            Book = new XSSFWorkbook(fileStream);
        }
    }
    /// <summary>
    /// 带模板或数据的Excel生成或操作
    /// </summary>
    /// <param name="fileName"></param>
    public NPOIExcel(string fileName)
    {
        Book = CreateBook(fileName);
    }

    /// <summary>
    /// 创建Excel Book
    /// </summary>
    /// <param name="fileName">模板文件名</param>
    /// <returns></returns>
    private IWorkbook CreateBook(string fileName)
    {
        FileInfo file = new FileInfo(fileName);
        if (!file.Exists)
        {
            File.Create(fileName).Close();
        }
        FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
        IWorkbook book;
        if (file.Extension == ".xls")
        {
            book = new HSSFWorkbook(fs);
        }
        else
        {
            book = new XSSFWorkbook(fs);
        }
        fs.Close();
        if (book.NumberOfSheets == 0)
        {
            book.CreateSheet();
        }
        return book;
    }

    /// <summary>
    /// 新建Sheet
    /// </summary>
    /// <returns>新建Sheet</returns>
    public ISheet CreateSheet()
    {
        return Book.CreateSheet();
    }

    /// <summary>
    /// 新建Sheet
    /// </summary>
    /// <param name="sheetName">新建Sheet的名称</param>
    /// <returns>新建Sheet</returns>
    public ISheet CreateSheet(string sheetName)
    {
        return Book.CreateSheet(sheetName);
    }

    /// <summary>
    /// 设置行高
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="height">高度</param>
    public void SetRowHeight(int rowIndex, float height)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        row.Height = (short)(height * 20);
    }

    /// <summary>
    /// 设置列宽
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="columnIndex">列号</param>
    /// <param name="width">宽度</param>
    public void SetColumnWidth(int columnIndex, short width)
    {
        Book.GetSheetAt(sheetID).SetColumnWidth(columnIndex, width * 256);
    }

    /// <summary>
    /// 获取或设置默认行高
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    public short DefaultRowHeight
    {
        get
        {
            return (short)(Book.GetSheetAt(sheetID).DefaultRowHeight / 20);
        }
        set
        {
            Book.GetSheetAt(sheetID).DefaultRowHeight = value * 20;
        }
    }

    /// <summary>
    /// 获取或设置默认列宽
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    public int DefaultColWidth
    {
        get
        {
            return Book.GetSheetAt(sheetID).DefaultColumnWidth;
        }
        set
        {
            Book.GetSheetAt(sheetID).DefaultColumnWidth = value;
        }
    }

    /// <summary>
    /// 某一列的列宽自动调整大小
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="colIndex">列号</param>
    public void AutoColWidth(int colIndex)
    {
        Book.GetSheetAt(sheetID).AutoSizeColumn(colIndex, true);
    }

    /// <summary>
    /// 隐藏一行
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    public void HiddenRow(int rowIndex)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        row.ZeroHeight = true;
    }

    /// <summary>
    /// 删除一行
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    public void RemoveRow(int rowIndex)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row != null)
        {
            ActiveSheet.RemoveRow(row);
        }
    }

    /// <summary>
    /// 读取单元格的值
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <returns>单元格的值</returns>
    public object ReadValue(int rowIndex, int columnIndex, bool? isDateTime = null)
    {
        try
        {
            ICell cell = Book.GetSheetAt(sheetID).GetRow(rowIndex).GetCell(columnIndex);
            short df = cell.CellStyle.DataFormat;

            //return cell.ToString();
            switch (cell.CellType)
            {
                case CellType.BLANK:
                    return null;
                case CellType.BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.ERROR:
                    throw new Exception("Cell Value Error");
                case CellType.FORMULA:
                    {
                        switch (cell.CachedFormulaResultType)
                        {
                            case CellType.BLANK:
                                return "";
                            case CellType.BOOLEAN:
                                return cell.BooleanCellValue;
                            case CellType.ERROR:
                                throw new Exception("Cell Value Error");
                            case CellType.FORMULA:
                                throw new Exception("The formula of this cell is too complex！");
                            case CellType.NUMERIC:
                                if (isDateTime == null)
                                {
                                    if (DateUtil.IsCellDateFormatted(cell))
                                    {
                                        return cell.DateCellValue;
                                    }
                                    else
                                    {
                                        return cell.NumericCellValue;
                                    }
                                }
                                else if (isDateTime == true)
                                {
                                    return cell.DateCellValue;
                                }
                                else
                                {
                                    return cell.NumericCellValue;
                                }
                            case CellType.STRING:
                                return cell.StringCellValue;
                            case CellType.Unknown:
                                return cell.ToString();
                            default:
                                return cell.ToString();
                        }
                    }
                case CellType.NUMERIC:
                    {
                        if (isDateTime == null)
                        {
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                return cell.DateCellValue;
                            }
                            else
                            {
                                return cell.NumericCellValue;
                            }
                        }
                        else if (isDateTime == true)
                        {
                            return cell.DateCellValue;
                        }
                        else
                        {
                            return cell.NumericCellValue;
                        }
                    }
                case CellType.STRING:
                    return cell.StringCellValue;
                case CellType.Unknown:
                    return cell.ToString();
                default:
                    return cell.ToString();
            }
        }
        catch (System.NullReferenceException)
        {
            return null;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    /// <summary>
    /// 设置单元格的值
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <param name="value">单元格的值</param>
    public void SetValue(int rowIndex, int columnIndex, object value)
    {
        SetValue(rowIndex, columnIndex, value, false);
    }

    /// <summary>
    /// 设置单元格的值
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <param name="value">单元格的值</param>
    /// <param name="isFormula">是否是公式</param>
    public void SetValue(int rowIndex, int columnIndex, object value, bool isFormula)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        ICell cell = row.GetCell(columnIndex);
        if (cell == null)
        {
            cell = row.CreateCell(columnIndex);
        }
        if (value == null)
        {
            cell.SetCellValue("");
        }
        if (isFormula)
        {
            cell.SetCellFormula(value.ToString());
        }
        else
        {
            if (value is short)
            {
                cell.SetCellValue((short)value);
            }
            else if (value is int)
            {
                cell.SetCellValue((int)value);
            }
            else if (value is long)
            {
                cell.SetCellValue((long)value);
            }
            else if (value is float)
            {
                cell.SetCellValue((float)value);
            }
            else if (value is double)
            {
                cell.SetCellValue((double)value);
            }
            else if (value is bool)
            {
                cell.SetCellValue((bool)value);
            }
            else if (value is DateTime)
            {
                cell.SetCellValue((DateTime)value);
            }
            else if (value == null)
            {
            }
            else
            {
                cell.SetCellValue(value.ToString());
            }
        }

    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="type">验证类型</param>
    /// <param name="operatorType">验证方式</param>
    /// <param name="minValue">最小值</param>
    /// <param name="maxValue">最大值</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue)
    {
        SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, type, operatorType, minValue, maxValue, "", "");
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="type">验证类型</param>
    /// <param name="operatorType">验证方式</param>
    /// <param name="minValue">最小值</param>
    /// <param name="maxValue">最大值</param>
    /// <param name="formate">数据格式</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue, string formate)
    {
        SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, type, operatorType, minValue, maxValue, formate, "");
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="type">验证类型</param>
    /// <param name="operatorType">验证方式</param>
    /// <param name="minValue">最小值</param>
    /// <param name="maxValue">最大值</param>
    /// <param name="formate">数据格式</param>
    /// <param name="AlertMassage">报错信息</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue, string formate, string AlertMassage)
    {
        CellRangeAddressList regions = new CellRangeAddressList(startRowIndex, EndRowIndex, startColInex, endColIndex);
        DVConstraint constraint = DVConstraint.CreateNumericConstraint(ValidationType.ANY, 0, null, null);
        switch (type)
        {
            case NPOIDataType.Integer:
                constraint = DVConstraint.CreateNumericConstraint(ValidationType.INTEGER, (int)operatorType, minValue, maxValue);
                break;
            case NPOIDataType.Float:
                constraint = DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, (int)operatorType, minValue, maxValue);
                break;
            case NPOIDataType.Date:
                if (formate == "")
                {
                    formate = "yyyy/MM/dd";
                }
                constraint = DVConstraint.CreateDateConstraint((int)operatorType, minValue, maxValue, formate);
                break;
            case NPOIDataType.Time:
                constraint = DVConstraint.CreateTimeConstraint((int)operatorType, minValue, maxValue);
                break;
            case NPOIDataType.TextLength:
                constraint = DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, (int)operatorType, minValue, maxValue);
                break;
            default:
                break;
        }

        HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions, constraint);
        if (!string.IsNullOrEmpty(AlertMassage))
        {
            dataValidate1.CreateErrorBox("Error", AlertMassage);
        }
        ActiveSheet.AddValidationData(dataValidate1);

    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="dataRange">值系列</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, string[] dataRange)
    {

        SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, dataRange, "");
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="dataRange">值系列</param>
    /// <param name="alertMassage">报错信息</param>
    public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string[] dataRange, string alertMassage)
    {
        ISheetConditionalFormatting hscf = ActiveSheet.SheetConditionalFormatting;
        CellRangeAddress[] regions = {
                new CellRangeAddress(startRowIndex, endRowIndex,startColInex,endColIndex)
            };

        CellRangeAddressList rangeList = new CellRangeAddressList();
        rangeList.AddCellRangeAddress(new CellRangeAddress(startRowIndex, endRowIndex, startColInex, endColIndex));
        DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(dataRange);
        HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);

        if (!string.IsNullOrEmpty(alertMassage))
        {
            dataValidation.CreateErrorBox("Error", alertMassage);
        }

        ActiveSheet.AddValidationData(dataValidation);
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="formula">计算公式</param>
    /// <param name="alertMassage">报错信息</param>
    public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string formula, string alertMassage)
    {
        ISheetConditionalFormatting hscf = ActiveSheet.SheetConditionalFormatting;
        CellRangeAddress[] regions = {
                new CellRangeAddress(startRowIndex, endRowIndex,startColInex,endColIndex)
            };

        CellRangeAddressList rangeList = new CellRangeAddressList();
        rangeList.AddCellRangeAddress(new CellRangeAddress(startRowIndex, endRowIndex, startColInex, endColIndex));
        DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint(formula);
        HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);

        if (!string.IsNullOrEmpty(alertMassage))
        {
            dataValidation.CreateErrorBox("Error", alertMassage);
        }

        ActiveSheet.AddValidationData(dataValidation);
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="formula">计算公式</param>
    public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string formula)
    {
        SetValueRange(startRowIndex, endColIndex, startRowIndex, endColIndex, formula, "");
    }

    /// <summary>
    /// 生成单元格样式
    /// </summary>
    /// <returns>与当前Excel相关的单元格样式</returns>
    public ICellStyle CreateCellStyle()
    {
        return Book.CreateCellStyle();
    }

    /// <summary>
    /// 生成字体
    /// </summary>
    /// <returns>与当前Excel相关的字体</returns>
    public IFont CreateFont()
    {
        return Book.CreateFont();
    }

    /// <summary>
    /// 设置单元格样式
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <param name="style">样式</param>
    public void SetStyle(int rowIndex, int columnIndex, ICellStyle style)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        ICell cell = row.GetCell(columnIndex);
        if (cell == null)
        {
            cell = row.CreateCell(columnIndex);
        }
        cell.CellStyle = style;
    }

    /// <summary>
    /// 合并单元格
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行号</param>
    /// <param name="startColumnIndex">开始列号</param>
    /// <param name="endRowIndex">结束行号</param>
    /// <param name="endColumnIndex">结束列号</param>
    public void MergeCells(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
    {
        int index = Book.GetSheetAt(sheetID).AddMergedRegion(new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex));
    }

    /// <summary>
    /// 拆分单元格
    /// 注1：只对当前ActiveSheet有效
    /// 注2：只有合并的单元格才能拆分
    /// </summary>
    /// <param name="startRowIndex">开始行号</param>
    /// <param name="startColumnIndex">开始列号</param>
    public void UnMergeCells(int startRowIndex, int startColumnIndex)
    {
        int merges = Book.GetSheetAt(sheetID).NumMergedRegions;
        CellRangeAddress merge;
        for (int i = 0; i < merges; i++)
        {
            merge = Book.GetSheetAt(sheetID).GetMergedRegion(i);
            if (merge.FirstRow == startRowIndex && merge.FirstColumn == startColumnIndex)
            {
                Book.GetSheetAt(sheetID).RemoveMergedRegion(i);
                break;
            }
        }
    }

    /// <summary>
    /// 保存到文件
    /// 注：有模板的，文件扩展名与模板一样；没有模板的，文件扩展名为“.xls”；
    /// </summary>
    /// <param name="fileName">保存文件名</param>
    public void Save(string fileName)
    {
        FileStream file = new FileStream(fileName, FileMode.Create);
        Book.Write(file);
        file.Close();
    }

    /// <summary>
    /// 保存到流
    /// 注：保存或下载时，有模板的，文件扩展名与模板一样；没有模板的，文件扩展名为“.xls”；
    /// </summary>
    /// <returns>内存流</returns>
    public MemoryStream Save()
    {
        MemoryStream ms = new MemoryStream();
        Book.Write(ms);
        return ms;
    }

    /// <summary>
    /// 把Excel读成DataSet
    /// 注：必须是正规表格式
    /// </summary>
    /// <returns>读出的Excel</returns>
    public DataSet ReadAsDataSet()
    {
        DataSet rtn = new DataSet();
        for (int i = 0; i < SheetCount; i++)
        {
            ISheet sheet = Book.GetSheetAt(i);
            rtn.Tables.Add(GetDataTableBySheet(sheet));
        }
        return rtn;
    }

    private DataTable GetDataTableBySheet(ISheet sheet)
    {
        DataTable dt = new DataTable(sheet.SheetName);
        int maxCols = 0;
        object value;
        while ((value = ReadValue(sheet, 0, maxCols)) != null)
        {
            dt.Columns.Add(value.ToString());
            maxCols++;
        }

        int row = 1;

        bool emptyRow = false;
        int emptyRowCount = 0;
        while (emptyRowCount < 10)
        {
            emptyRow = true;
            DataRow dr = dt.NewRow();
            for (int i = 0; i < maxCols; i++)
            {
                value = ReadValue(sheet, row, i);
                if (value != null)
                {
                    dr[i] = value;
                    emptyRow = false;
                }
            }
            if (!emptyRow)
            {
                dt.Rows.Add(dr);
                emptyRowCount = 0;
            }
            else
            {
                emptyRowCount++;
            }
            row++;
        }

        return dt;
    }

    /// <summary>
    /// 根据SheetName导出数据为DataTable
    /// </summary>
    /// <param name="sheetName">Sheet名称</param>
    /// <returns></returns>
    public DataTable GetDataTableBySheet(string sheetName)
    {

        ISheet sheet = Book.GetSheet(sheetName);
        if (sheet != null)
        {
            return GetDataTableBySheet(sheet);
        }
        return null;

    }

    /// <summary>
    /// 根据SheetName导出数据为DataTable
    /// </summary>
    /// <param name="sheetIndex">Sheet编号</param>
    /// <returns></returns>
    public DataTable GetDataTableBySheet(int sheetIndex)
    {

        ISheet sheet = Book.GetSheetAt(sheetIndex);
        if (sheet != null)
        {
            return GetDataTableBySheet(sheet);
        }
        return null;

    }

    /// <summary>
    /// 写入表格
    /// </summary>
    /// <param name="Data">表格数据</param>
    /// <param name="col">写入的起始列</param>
    /// <param name="row">写入的起始行</param>
    /// <param name="titleColor">标题颜色</param>
    /// <param name="fullBorder">是否需要四周边框</param>
    public void WriteDataTable(DataTable Data, int col = 1, int row = 1, short? titleColor = null, bool fullBorder = true)
    {
        if (Data == null)
        {
            return;
        }

        var titleStyle = CreateCellStyle();
        var rowStyle = CreateCellStyle();
        if (titleColor != null)
            titleStyle.FillForegroundColor = titleColor.Value;

        titleStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
        if (fullBorder)
        {
            titleStyle.BorderBottom = BorderStyle.THIN;
            titleStyle.BorderLeft = BorderStyle.THIN;
            titleStyle.BorderRight = BorderStyle.THIN;
            titleStyle.BorderTop = BorderStyle.THIN;
            titleStyle.BottomBorderColor = NPOIColor.BLACK;
            titleStyle.LeftBorderColor = NPOIColor.BLACK;
            titleStyle.RightBorderColor = NPOIColor.BLACK;
            titleStyle.TopBorderColor = NPOIColor.BLACK;
            rowStyle.BorderBottom = BorderStyle.THIN;
            rowStyle.BorderLeft = BorderStyle.THIN;
            rowStyle.BorderRight = BorderStyle.THIN;
            rowStyle.BorderTop = BorderStyle.THIN;
            rowStyle.BottomBorderColor = NPOIColor.BLACK;
            rowStyle.LeftBorderColor = NPOIColor.BLACK;
            rowStyle.RightBorderColor = NPOIColor.BLACK;
            rowStyle.TopBorderColor = NPOIColor.BLACK;
        }

        int iCol = 0, iRow = 1;

        foreach (DataColumn dc in Data.Columns)
        {
            SetValue(row, col + iCol, dc.ColumnName);
            SetStyle(row, col + iCol, titleStyle);
            iCol++;
        }

        rowStyle.FillForegroundColor = NPOIColor.WHITE;

        foreach (DataRow dr in Data.Rows)
        {
            iCol = 0;
            foreach (DataColumn dc in Data.Columns)
            {
                SetValue(row + iRow, col + iCol, dr[dc]);
                SetStyle(row + iRow, col + iCol, rowStyle);
                iCol++;
            }
            iRow++;
        }

        for (int i = 0; i < iCol; i++)
        {
            this.AutoColWidth(i);
        }
    }

    /// <summary>
    /// 读取单元格的值
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <returns>单元格的值</returns>
    public object ReadValue(ISheet sheet, int rowIndex, int columnIndex, bool? isDateTime = null)
    {
        try
        {
            ICell cell = sheet.GetRow(rowIndex).GetCell(columnIndex);
            short df = cell.CellStyle.DataFormat;

            //return cell.ToString();
            switch (cell.CellType)
            {
                case CellType.BLANK:
                    return null;
                case CellType.BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.ERROR:
                    throw new Exception("Cell Value Error");
                case CellType.FORMULA:
                    {
                        switch (cell.CachedFormulaResultType)
                        {
                            case CellType.BLANK:
                                return "";
                            case CellType.BOOLEAN:
                                return cell.BooleanCellValue;
                            case CellType.ERROR:
                                throw new Exception("Cell Value Error");
                            case CellType.FORMULA:
                                throw new Exception("The formula of this cell is too complex！");
                            case CellType.NUMERIC:
                                if (isDateTime == null)
                                {
                                    if (DateUtil.IsCellDateFormatted(cell))
                                    {
                                        return cell.DateCellValue;
                                    }
                                    else
                                    {
                                        return cell.NumericCellValue;
                                    }
                                }
                                else if (isDateTime == true)
                                {
                                    return cell.DateCellValue;
                                }
                                else
                                {
                                    return cell.NumericCellValue;
                                }
                            case CellType.STRING:
                                return cell.StringCellValue;
                            case CellType.Unknown:
                                return cell.ToString();
                            default:
                                return cell.ToString();
                        }
                    }
                case CellType.NUMERIC:
                    {
                        if (isDateTime == null)
                        {
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                return cell.DateCellValue;
                            }
                            else
                            {
                                return cell.NumericCellValue;
                            }
                        }
                        else if (isDateTime == true)
                        {
                            return cell.DateCellValue;
                        }
                        else
                        {
                            return cell.NumericCellValue;
                        }
                    }
                case CellType.STRING:
                    return cell.StringCellValue;
                case CellType.Unknown:
                    return cell.ToString();
                default:
                    return cell.ToString();
            }
        }
        catch (System.NullReferenceException)
        {
            return null;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public int SheetCount
    {
        get
        {
            return Book.NumberOfSheets;
        }
    }

    public string GetSheetName(int index)
    {
        return Book.GetSheetName(index);
    }

    public void AddPicture(byte[] data, int row, int col)
    {
        int picIndex = Book.AddPicture(data, PictureType.PNG);

        IDrawing draw = ActiveSheet.CreateDrawingPatriarch();

        IClientAnchor anchor = draw.CreateAnchor(0, 0, 255, 255, col, row, col + 5, col + 5);
        IPicture pic = draw.CreatePicture(anchor, picIndex);
        pic.Resize();
    }
}

public enum OperatorTypes
{
    /// <summary>
    /// 介于最大值与小值之间
    /// </summary>
    BETWEEN = OperatorType.BETWEEN,
    /// <summary>
    /// 等于最小值
    /// </summary>
    EQUAL = OperatorType.EQUAL,
    /// <summary>
    /// 大于或等于最小值
    /// </summary>
    GREATER_OR_EQUAL = OperatorType.GREATER_OR_EQUAL,
    /// <summary>
    /// 大于最小值
    /// </summary>
    GREATER_THAN = OperatorType.GREATER_THAN,
    /// <summary>
    /// 忽略
    /// </summary>
    NO_COMPARISON = OperatorType.IGNORED,
    /// <summary>
    /// 小于或等于最小值
    /// </summary>
    LESS_OR_EQUAL = OperatorType.LESS_OR_EQUAL,
    /// <summary>
    /// 小于最小值
    /// </summary>
    LESS_THAN = OperatorType.LESS_THAN,
    /// <summary>
    /// 不在最小值与最大值之间
    /// </summary>
    NOT_BETWEEN = OperatorType.NOT_BETWEEN,
    /// <summary>
    /// 不等于最小值
    /// </summary>
    NOT_EQUAL = OperatorType.NOT_EQUAL
}

public enum NPOIDataType
{
    /// <summary>
    /// 验证整数
    /// </summary>
    Integer,
    /// <summary>
    /// 验证符点数
    /// </summary>
    Float,
    /// <summary>
    /// 验证日期
    /// </summary>
    Date,
    /// <summary>
    /// 验证时间
    /// </summary>
    Time,
    /// <summary>
    /// 验证字符长度
    /// </summary>
    TextLength
}

public static class NPOIColor
{

    /// <summary>
    /// 红色
    /// </summary>
    public static short RED { get { return NPOI.HSSF.Util.HSSFColor.RED.index; } }

    /// <summary>
    /// 蓝色
    /// </summary>
    public static short BLUE { get { return NPOI.HSSF.Util.HSSFColor.BLUE.index; } }

    /// <summary>
    /// 浅绿色
    /// </summary>
    public static short AQUA { get { return NPOI.HSSF.Util.HSSFColor.AQUA.index; } }

    /// <summary>
    /// 自动
    /// </summary>
    public static short AUTOMATIC { get { return NPOI.HSSF.Util.HSSFColor.AUTOMATIC.index; } }

    /// <summary>
    /// 黑色
    /// </summary>
    public static short BLACK { get { return NPOI.HSSF.Util.HSSFColor.BLACK.index; } }

    /// <summary>
    /// 蓝灰色
    /// </summary>
    public static short BLUE_GREY { get { return NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index; } }

    /// <summary>
    /// 明绿色
    /// </summary>
    public static short BRIGHT_GREEN { get { return NPOI.HSSF.Util.HSSFColor.BRIGHT_GREEN.index; } }

    /// <summary>
    /// 棕色
    /// </summary>
    public static short BROWN { get { return NPOI.HSSF.Util.HSSFColor.BROWN.index; } }

    /// <summary>
    /// 正常
    /// </summary>
    public static short COLOR_NORMAL { get { return NPOI.HSSF.Util.HSSFColor.COLOR_NORMAL; } }

    /// <summary>
    /// 珊瑚色
    /// </summary>
    public static short CORAL { get { return NPOI.HSSF.Util.HSSFColor.CORAL.index; } }

    /// <summary>
    /// 亮蓝色
    /// </summary>
    public static short CORNFLOWER_BLUE { get { return NPOI.HSSF.Util.HSSFColor.CORNFLOWER_BLUE.index; } }

    /// <summary>
    /// 深蓝色
    /// </summary>
    public static short DARK_BLUE { get { return NPOI.HSSF.Util.HSSFColor.DARK_BLUE.index; } }

    /// <summary>
    /// 深绿色
    /// </summary>
    public static short DARK_GREEN { get { return NPOI.HSSF.Util.HSSFColor.DARK_GREEN.index; } }

    /// <summary>
    /// 深红色
    /// </summary>
    public static short DARK_RED { get { return NPOI.HSSF.Util.HSSFColor.DARK_RED.index; } }

    /// <summary>
    /// 深茶色
    /// </summary>
    public static short DARK_TEAL { get { return NPOI.HSSF.Util.HSSFColor.DARK_TEAL.index; } }

    /// <summary>
    /// 深黄
    /// </summary>
    public static short DARK_YELLOW { get { return NPOI.HSSF.Util.HSSFColor.DARK_YELLOW.index; } }

    /// <summary>
    /// 金色
    /// </summary>
    public static short GOLD { get { return NPOI.HSSF.Util.HSSFColor.GOLD.index; } }

    /// <summary>
    /// 绿色
    /// </summary>
    public static short GREEN { get { return NPOI.HSSF.Util.HSSFColor.GREEN.index; } }

    /// <summary>
    /// 25%灰色
    /// </summary>
    public static short GREY_25_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index; } }

    /// <summary>
    /// 40%灰色
    /// </summary>
    public static short GREY_40_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_40_PERCENT.index; } }

    /// <summary>
    /// 50%灰色
    /// </summary>
    public static short GREY_50_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_50_PERCENT.index; } }

    /// <summary>
    /// 80%灰色
    /// </summary>
    public static short GREY_80_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_80_PERCENT.index; } }

    /// <summary>
    /// 靛蓝色
    /// </summary>
    public static short INDIGO { get { return NPOI.HSSF.Util.HSSFColor.INDIGO.index; } }

    /// <summary>
    /// 淡紫色
    /// </summary>
    public static short LAVENDER { get { return NPOI.HSSF.Util.HSSFColor.LAVENDER.index; } }

    /// <summary>
    /// 粉黄色
    /// </summary>
    public static short LEMON_CHIFFON { get { return NPOI.HSSF.Util.HSSFColor.LEMON_CHIFFON.index; } }

    /// <summary>
    /// 淡蓝色
    /// </summary>
    public static short LIGHT_BLUE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_BLUE.index; } }

    /// <summary>
    /// 淡亮蓝色
    /// </summary>
    public static short LIGHT_CORNFLOWER_BLUE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_CORNFLOWER_BLUE.index; } }

    /// <summary>
    /// 淡绿色
    /// </summary>
    public static short LIGHT_GREEN { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index; } }

    /// <summary>
    /// 淡桔黄色
    /// </summary>
    public static short LIGHT_ORANGE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_ORANGE.index; } }

    /// <summary>
    /// 淡蓝绿色
    /// </summary>
    public static short LIGHT_TURQUOISE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_TURQUOISE.index; } }

    /// <summary>
    /// 淡黄色
    /// </summary>
    public static short LIGHT_YELLOW { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index; } }

    /// <summary>
    /// 绿黄色
    /// </summary>
    public static short LIME { get { return NPOI.HSSF.Util.HSSFColor.LIME.index; } }

    /// <summary>
    /// 栗色
    /// </summary>
    public static short MAROON { get { return NPOI.HSSF.Util.HSSFColor.MAROON.index; } }

    /// <summary>
    /// 橄榄绿色
    /// </summary>
    public static short OLIVE_GREEN { get { return NPOI.HSSF.Util.HSSFColor.OLIVE_GREEN.index; } }

    /// <summary>
    /// 桔色
    /// </summary>
    public static short ORANGE { get { return NPOI.HSSF.Util.HSSFColor.ORANGE.index; } }

    /// <summary>
    /// 白灰蓝色
    /// </summary>
    public static short PALE_BLUE { get { return NPOI.HSSF.Util.HSSFColor.PALE_BLUE.index; } }

    /// <summary>
    /// 粉红色
    /// </summary>
    public static short PINK { get { return NPOI.HSSF.Util.HSSFColor.PINK.index; } }

    /// <summary>
    /// 紫红色
    /// </summary>
    public static short PLUM { get { return NPOI.HSSF.Util.HSSFColor.PLUM.index; } }

    /// <summary>
    /// 玫瑰红色
    /// </summary>
    public static short ROSE { get { return NPOI.HSSF.Util.HSSFColor.ROSE.index; } }

    /// <summary>
    /// 高贵蓝
    /// </summary>
    public static short ROYAL_BLUE { get { return NPOI.HSSF.Util.HSSFColor.ROYAL_BLUE.index; } }

    /// <summary>
    /// 海绿色
    /// </summary>
    public static short SEA_GREEN { get { return NPOI.HSSF.Util.HSSFColor.SEA_GREEN.index; } }

    /// <summary>
    /// 天空蓝
    /// </summary>
    public static short SKY_BLUE { get { return NPOI.HSSF.Util.HSSFColor.SKY_BLUE.index; } }

    /// <summary>
    /// 棕褐色
    /// </summary>
    public static short TAN { get { return NPOI.HSSF.Util.HSSFColor.TAN.index; } }

    /// <summary>
    /// 茶色
    /// </summary>
    public static short TEAL { get { return NPOI.HSSF.Util.HSSFColor.TEAL.index; } }

    /// <summary>
    /// 蓝绿色
    /// </summary>
    public static short TURQUOISE { get { return NPOI.HSSF.Util.HSSFColor.TURQUOISE.index; } }

    /// <summary>
    /// 紫色
    /// </summary>
    public static short VIOLET { get { return NPOI.HSSF.Util.HSSFColor.VIOLET.index; } }

    /// <summary>
    /// 白色
    /// </summary>
    public static short WHITE { get { return NPOI.HSSF.Util.HSSFColor.WHITE.index; } }

    /// <summary>
    /// 黄色
    /// </summary>
    public static short YELLOW { get { return NPOI.HSSF.Util.HSSFColor.YELLOW.index; } }

}

/// <summary>
/// 针对excel的Oledb
/// </summary>
public class OleDbExcel
{
    /// <summary>
    /// OLEDB连接
    /// </summary>
    public OleDbConnection Connection
    {
        get;
        set;
    }

    /// <summary>
    /// 用Oledb对Excel进行操作
    /// 注：必须是标准表形式Excel内容
    /// </summary>
    /// <param name="excelFile">Excel文件</param>
    public OleDbExcel(string excelFile)
    {
        string conStr = string.Empty;
        FileInfo file = new FileInfo(excelFile);
        if (!file.Exists) { throw new Exception("文件不存在"); }
        string extension = file.Extension;
        switch (extension)
        {
            case ".xls":
                conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                break;
            case ".xlsx":
                conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                break;
            default:
                conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                break;
        }

        //链接Excel
        Connection = new OleDbConnection(conStr);
    }

    private List<string> tableNames;

    /// <summary>
    /// 获取Excel内的Sheet名称
    /// </summary>
    public List<string> Sheets
    {
        get
        {
            if (tableNames == null)
            {
                try
                {
                    tableNames = new List<string>();
                    //读取Excel里面的sheet名
                    Connection.Open();

                    DataTable schemaTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        DataRow dr = schemaTable.Rows[i];
                        string tbName = dr["table_name"].ToString();
                        if (tbName[tbName.Length - 1] == '$')
                        {
                            tableNames.Add(tbName);
                        }
                    }
                    Connection.Close();
                }
                catch (Exception ex)
                {
                    if (Connection.State != ConnectionState.Closed)
                    {
                        Connection.Close();
                    }
                    throw new Exception(ex.Message, ex);
                }
            }
            return tableNames;
        }
    }

    /// <summary>
    /// 查询出所有数据
    /// </summary>
    /// <param name="tableName">Sheet名称</param>
    /// <returns>sheet内的所有数据</returns>
    public DataSet QueryAll(string tableName)
    {
        try
        {
            DataSet excelData = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = new OleDbCommand();
            adapter.SelectCommand.Connection = Connection;
            adapter.SelectCommand.CommandText = string.Format("SELECT * FROM {0}", "[" + tableName + "]");
            adapter.Fill(excelData);
            return excelData;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }

    /// <summary>
    /// 查询出所有数据
    /// </summary>
    /// <param name="tableIndex">Sheet序号（从0开始）</param>
    /// <returns>sheet内的所有数据</returns>
    public DataSet QueryAll(int tableIndex)
    {
        return QueryAll(Sheets[tableIndex]);
    }

    /// <summary>
    /// 利用Sql进行查询
    /// </summary>
    /// <param name="sql">Sql语句</param>
    /// <returns>查询出的数据</returns>
    public DataSet Query(string sql)
    {
        try
        {
            DataSet excelData = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, Connection);
            adapter.Fill(excelData);
            return excelData;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }

    /// <summary>
    /// 利用Sql进行查询
    /// </summary>
    /// <param name="sql">Sql语句</param>
    /// <param name="param">查询参数</param>
    /// <returns>查询出的数据</returns>
    public DataSet Query(string sql, params OleDbParameter[] param)
    {
        try
        {
            DataSet excelData = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, Connection);
            adapter.SelectCommand.Parameters.AddRange(param);
            adapter.Fill(excelData);
            return excelData;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }

    /// <summary>
    /// 利用Sql进行数据操作
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns>影响的行数</returns>
    public int ExecuteSql(string sql)
    {
        try
        {
            Connection.Open();
            OleDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = sql;
            int rtn = cmd.ExecuteNonQuery();
            Connection.Close();
            return rtn;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }

    /// <summary>
    /// 利用Sql进行数据操作
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <param name="param">执行参数</param>
    /// <returns>影响的行数</returns>
    public int ExecuteSql(string sql, params OleDbParameter[] param)
    {
        try
        {
            Connection.Open();
            OleDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(param);
            int rtn = cmd.ExecuteNonQuery();
            Connection.Close();
            return rtn;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }
}

2、使用 后台

        [HttpPost]
        public void ExportExcel(string excelName, string tableStr, string imageAction = "")
        {
            tableStr = Microsoft.JScript.GlobalObject.unescape(tableStr);
            //tableStr = "<table><tr><td>test1</td><td>test2</td></tr><tr><td>1</td><td>2</td></tr></table>";
            DataTable dt = HtmlTableToDataTable(tableStr);
            NPOIExcel excel = new NPOIExcel();
            excel.WriteDataTable(dt, 0, 0, NPOIColor.SKY_BLUE);

            if (!string.IsNullOrWhiteSpace(imageAction))
            {
                var method = this.GetType().GetMethod(imageAction);
                var param = method.GetParameters();
                object[] objParam = new object[param.Length];
                int i = 0;
                foreach (var pa in param)
                {
                    if (pa.Name == "export")
                    {
                        objParam[i] = true;
                        continue;
                    }
                    string value = Request[pa.Name];
                    if (string.IsNullOrEmpty(value))
                    {
                        objParam[i] = pa.RawDefaultValue;
                    }
                    else
                    {
                        objParam[i] = Convert.ChangeType(value, pa.ParameterType);
                    }
                    i++;
                }

                byte[] bytes = (byte[])method.Invoke(this, objParam);
                excel.AddPicture(bytes, dt.Rows.Count + 1, 0);

            }
            MemoryStream ms = excel.Save();
            ms.Position = 0;
            Response.Clear();
            Response.ContentType = "application/-excel";
            Response.HeaderEncoding = System.Text.Encoding.UTF8;
            Response.AddHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(excelName + ".xls"));
            byte[] buffer = new byte[ms.Length];
            ms.Read(buffer, 0, buffer.Length);
            Response.AddHeader("Content-Length", buffer.Length.ToString());
            Response.BinaryWrite(buffer);
            Response.Flush();

        }

        private DataTable HtmlTableToDataTable(string tableStr)
        {
            tableStr = tableStr.Replace("\n", "");
            tableStr = tableStr.Replace("\t", "");
            Regex re = new Regex("<BR>", RegexOptions.IgnoreCase);
            tableStr = re.Replace(tableStr, "\r\n");
            re = new Regex("&nbsp;", RegexOptions.IgnoreCase);
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(tableStr);

            var trs = doc.GetElementsByTagName("tr");

            DataTable rtn = new DataTable();

            for (int i = 0; i < trs.Count; i++)
            {
                for (int j = 0; j < trs[i].ChildNodes.Count; j++)
                {
                    var td = trs[i].ChildNodes[j].InnerText.Trim();
                    td = td.Replace(" ", "");
                    re.Replace(td, " ");
                    if (rtn.Columns.Count <= j)
                    {
                        rtn.Columns.Add(td);
                    }
                    else
                    {
                        if (rtn.Rows.Count <= i - 1)
                        {
                            rtn.Rows.Add(rtn.NewRow());
                        }
                        rtn.Rows[i - 1][j] = td;
                    }
                }
            }

            return rtn;

        }

3、前台

按钮

 <a href="javascript:void(0)" onclick="ExportExcel('AssManageQueryClose','exportTable')" style="font-size: 13px;">@Html.Lang("输出报表", "Export Report")</a>

table：

  <table class="dataList" id="exportTable">
                                <tr class="title">
                                    <td>
                                      title</td>
                                </tr>

                                @foreach (var item in Model)
                                {
                                    <tr class="item">
                                        <td>
                                           Content</td>
                                    </tr>
                                }
                            </table>

js：

    //导出Excel
        function ExportExcel(excelName, tableID) {
            if ($("#" + excelName).length <= 0) {
                var table = "<table>" + $("#" + tableID).html() + "</table>";
                table = escape(table);
                var form = "<form id='" + excelName + "' action='@Url.Action("ExportExcel")' method='post'>";
                form += "<input type='hidden' id='excelName' name='excelName' value='" + excelName + "' />";
                form += "<input type='hidden' id='tableStr' name='tableStr' value='" + table + "' />";
                form += "</form>";
                $(form).appendTo("body");
            }
            $("#" + excelName).submit();
        }

.Net NPOI 上传excel文件、提交后台获取excel里的数据

 

1、导入NPOI.dll

2、添加类NPOIExcel.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Drawing;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util;

public class NPOIExcel
{
    internal IWorkbook Book { get; set; }

    private int sheetID = 0;
    /// <summary>
    /// 当前活动的SheetID，所有的操作将指向这个Sheet
    /// </summary>
    public int ActiveSheetID
    {
        get
        {
            return sheetID;
        }
        set
        {
            sheetID = value;
        }
    }

    /// <summary>
    /// 当前活动的SheetName，所有的操作将指向这个Sheet
    /// </summary>
    public string ActiveSheetName
    {
        get
        {
            return Book.GetSheetAt(sheetID).SheetName;
        }
        set
        {
            sheetID = Book.GetSheetIndex(value);
        }
    }
    /// <summary>
    /// 当前活动的Sheet，所有的操作将指向这个Sheet
    /// </summary>
    public ISheet ActiveSheet
    {
        get
        {
            return Book.GetSheetAt(sheetID);
        }
    }

    /// <summary>
    /// 第一行非空行的行号
    /// </summary>
    public int FirstRowNum
    {
        get
        {
            return Book.GetSheetAt(sheetID).FirstRowNum;
        }
    }

    /// <summary>
    /// 最后一行非空行的行号
    /// </summary>
    public int LastRostNum
    {
        get
        {
            return Book.GetSheetAt(sheetID).LastRowNum;
        }
    }

    /// <summary>
    /// 无模板的Excel生成或操作
    /// </summary>
    public NPOIExcel()
    {
        Book = new HSSFWorkbook();
        Book.CreateSheet();
    }

    public NPOIExcel(Stream fileStream, string fileName)
    {
        if (fileName.Substring(fileName.LastIndexOf(".")) == ".xls")
        {
            Book = new HSSFWorkbook(fileStream);
        }
        else
        {
            Book = new XSSFWorkbook(fileStream);
        }
    }
    /// <summary>
    /// 带模板或数据的Excel生成或操作
    /// </summary>
    /// <param name="fileName"></param>
    public NPOIExcel(string fileName)
    {
        Book = CreateBook(fileName);
    }

    /// <summary>
    /// 创建Excel Book
    /// </summary>
    /// <param name="fileName">模板文件名</param>
    /// <returns></returns>
    private IWorkbook CreateBook(string fileName)
    {
        FileInfo file = new FileInfo(fileName);
        if (!file.Exists)
        {
            File.Create(fileName).Close();
        }
        FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
        IWorkbook book;
        if (file.Extension == ".xls")
        {
            book = new HSSFWorkbook(fs);
        }
        else
        {
            book = new XSSFWorkbook(fs);
        }
        fs.Close();
        if (book.NumberOfSheets == 0)
        {
            book.CreateSheet();
        }
        return book;
    }

    /// <summary>
    /// 新建Sheet
    /// </summary>
    /// <returns>新建Sheet</returns>
    public ISheet CreateSheet()
    {
        return Book.CreateSheet();
    }

    /// <summary>
    /// 新建Sheet
    /// </summary>
    /// <param name="sheetName">新建Sheet的名称</param>
    /// <returns>新建Sheet</returns>
    public ISheet CreateSheet(string sheetName)
    {
        return Book.CreateSheet(sheetName);
    }

    /// <summary>
    /// 设置行高
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="height">高度</param>
    public void SetRowHeight(int rowIndex, float height)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        row.Height = (short)(height * 20);
    }

    /// <summary>
    /// 设置列宽
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="columnIndex">列号</param>
    /// <param name="width">宽度</param>
    public void SetColumnWidth(int columnIndex, short width)
    {
        Book.GetSheetAt(sheetID).SetColumnWidth(columnIndex, width * 256);
    }

    /// <summary>
    /// 获取或设置默认行高
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    public short DefaultRowHeight
    {
        get
        {
            return (short)(Book.GetSheetAt(sheetID).DefaultRowHeight / 20);
        }
        set
        {
            Book.GetSheetAt(sheetID).DefaultRowHeight = value * 20;
        }
    }

    /// <summary>
    /// 获取或设置默认列宽
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    public int DefaultColWidth
    {
        get
        {
            return Book.GetSheetAt(sheetID).DefaultColumnWidth;
        }
        set
        {
            Book.GetSheetAt(sheetID).DefaultColumnWidth = value;
        }
    }

    /// <summary>
    /// 某一列的列宽自动调整大小
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="colIndex">列号</param>
    public void AutoColWidth(int colIndex)
    {
        Book.GetSheetAt(sheetID).AutoSizeColumn(colIndex, true);
    }

    /// <summary>
    /// 隐藏一行
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    public void HiddenRow(int rowIndex)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        row.ZeroHeight = true;
    }

    /// <summary>
    /// 删除一行
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    public void RemoveRow(int rowIndex)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row != null)
        {
            ActiveSheet.RemoveRow(row);
        }
    }

    /// <summary>
    /// 读取单元格的值
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <returns>单元格的值</returns>
    public object ReadValue(int rowIndex, int columnIndex, bool? isDateTime = null)
    {
        try
        {
            ICell cell = Book.GetSheetAt(sheetID).GetRow(rowIndex).GetCell(columnIndex);
            short df = cell.CellStyle.DataFormat;

            //return cell.ToString();
            switch (cell.CellType)
            {
                case CellType.BLANK:
                    return null;
                case CellType.BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.ERROR:
                    throw new Exception("Cell Value Error");
                case CellType.FORMULA:
                    {
                        switch (cell.CachedFormulaResultType)
                        {
                            case CellType.BLANK:
                                return "";
                            case CellType.BOOLEAN:
                                return cell.BooleanCellValue;
                            case CellType.ERROR:
                                throw new Exception("Cell Value Error");
                            case CellType.FORMULA:
                                throw new Exception("The formula of this cell is too complex！");
                            case CellType.NUMERIC:
                                if (isDateTime == null)
                                {
                                    if (DateUtil.IsCellDateFormatted(cell))
                                    {
                                        return cell.DateCellValue;
                                    }
                                    else
                                    {
                                        return cell.NumericCellValue;
                                    }
                                }
                                else if (isDateTime == true)
                                {
                                    return cell.DateCellValue;
                                }
                                else
                                {
                                    return cell.NumericCellValue;
                                }
                            case CellType.STRING:
                                return cell.StringCellValue;
                            case CellType.Unknown:
                                return cell.ToString();
                            default:
                                return cell.ToString();
                        }
                    }
                case CellType.NUMERIC:
                    {
                        if (isDateTime == null)
                        {
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                return cell.DateCellValue;
                            }
                            else
                            {
                                return cell.NumericCellValue;
                            }
                        }
                        else if (isDateTime == true)
                        {
                            return cell.DateCellValue;
                        }
                        else
                        {
                            return cell.NumericCellValue;
                        }
                    }
                case CellType.STRING:
                    return cell.StringCellValue;
                case CellType.Unknown:
                    return cell.ToString();
                default:
                    return cell.ToString();
            }
        }
        catch (System.NullReferenceException)
        {
            return null;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    /// <summary>
    /// 设置单元格的值
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <param name="value">单元格的值</param>
    public void SetValue(int rowIndex, int columnIndex, object value)
    {
        SetValue(rowIndex, columnIndex, value, false);
    }

    /// <summary>
    /// 设置单元格的值
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <param name="value">单元格的值</param>
    /// <param name="isFormula">是否是公式</param>
    public void SetValue(int rowIndex, int columnIndex, object value, bool isFormula)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        ICell cell = row.GetCell(columnIndex);
        if (cell == null)
        {
            cell = row.CreateCell(columnIndex);
        }
        if (value == null)
        {
            cell.SetCellValue("");
        }
        if (isFormula)
        {
            cell.SetCellFormula(value.ToString());
        }
        else
        {
            if (value is short)
            {
                cell.SetCellValue((short)value);
            }
            else if (value is int)
            {
                cell.SetCellValue((int)value);
            }
            else if (value is long)
            {
                cell.SetCellValue((long)value);
            }
            else if (value is float)
            {
                cell.SetCellValue((float)value);
            }
            else if (value is double)
            {
                cell.SetCellValue((double)value);
            }
            else if (value is bool)
            {
                cell.SetCellValue((bool)value);
            }
            else if (value is DateTime)
            {
                cell.SetCellValue((DateTime)value);
            }
            else if (value == null)
            {
            }
            else
            {
                cell.SetCellValue(value.ToString());
            }
        }

    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="type">验证类型</param>
    /// <param name="operatorType">验证方式</param>
    /// <param name="minValue">最小值</param>
    /// <param name="maxValue">最大值</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue)
    {
        SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, type, operatorType, minValue, maxValue, "", "");
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="type">验证类型</param>
    /// <param name="operatorType">验证方式</param>
    /// <param name="minValue">最小值</param>
    /// <param name="maxValue">最大值</param>
    /// <param name="formate">数据格式</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue, string formate)
    {
        SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, type, operatorType, minValue, maxValue, formate, "");
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="type">验证类型</param>
    /// <param name="operatorType">验证方式</param>
    /// <param name="minValue">最小值</param>
    /// <param name="maxValue">最大值</param>
    /// <param name="formate">数据格式</param>
    /// <param name="AlertMassage">报错信息</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue, string formate, string AlertMassage)
    {
        CellRangeAddressList regions = new CellRangeAddressList(startRowIndex, EndRowIndex, startColInex, endColIndex);
        DVConstraint constraint = DVConstraint.CreateNumericConstraint(ValidationType.ANY, 0, null, null);
        switch (type)
        {
            case NPOIDataType.Integer:
                constraint = DVConstraint.CreateNumericConstraint(ValidationType.INTEGER, (int)operatorType, minValue, maxValue);
                break;
            case NPOIDataType.Float:
                constraint = DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, (int)operatorType, minValue, maxValue);
                break;
            case NPOIDataType.Date:
                if (formate == "")
                {
                    formate = "yyyy/MM/dd";
                }
                constraint = DVConstraint.CreateDateConstraint((int)operatorType, minValue, maxValue, formate);
                break;
            case NPOIDataType.Time:
                constraint = DVConstraint.CreateTimeConstraint((int)operatorType, minValue, maxValue);
                break;
            case NPOIDataType.TextLength:
                constraint = DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, (int)operatorType, minValue, maxValue);
                break;
            default:
                break;
        }

        HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions, constraint);
        if (!string.IsNullOrEmpty(AlertMassage))
        {
            dataValidate1.CreateErrorBox("Error", AlertMassage);
        }
        ActiveSheet.AddValidationData(dataValidate1);

    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="dataRange">值系列</param>
    public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, string[] dataRange)
    {

        SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, dataRange, "");
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="dataRange">值系列</param>
    /// <param name="alertMassage">报错信息</param>
    public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string[] dataRange, string alertMassage)
    {
        ISheetConditionalFormatting hscf = ActiveSheet.SheetConditionalFormatting;
        CellRangeAddress[] regions = {
                new CellRangeAddress(startRowIndex, endRowIndex,startColInex,endColIndex)
            };

        CellRangeAddressList rangeList = new CellRangeAddressList();
        rangeList.AddCellRangeAddress(new CellRangeAddress(startRowIndex, endRowIndex, startColInex, endColIndex));
        DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(dataRange);
        HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);

        if (!string.IsNullOrEmpty(alertMassage))
        {
            dataValidation.CreateErrorBox("Error", alertMassage);
        }

        ActiveSheet.AddValidationData(dataValidation);
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="formula">计算公式</param>
    /// <param name="alertMassage">报错信息</param>
    public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string formula, string alertMassage)
    {
        ISheetConditionalFormatting hscf = ActiveSheet.SheetConditionalFormatting;
        CellRangeAddress[] regions = {
                new CellRangeAddress(startRowIndex, endRowIndex,startColInex,endColIndex)
            };

        CellRangeAddressList rangeList = new CellRangeAddressList();
        rangeList.AddCellRangeAddress(new CellRangeAddress(startRowIndex, endRowIndex, startColInex, endColIndex));
        DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint(formula);
        HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);

        if (!string.IsNullOrEmpty(alertMassage))
        {
            dataValidation.CreateErrorBox("Error", alertMassage);
        }

        ActiveSheet.AddValidationData(dataValidation);
    }

    /// <summary>
    /// 设置一个区域内的单元格的值范围
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行</param>
    /// <param name="EndRowIndex">结束行</param>
    /// <param name="startColInex">开始列</param>
    /// <param name="endColIndex">结束列</param>
    /// <param name="formula">计算公式</param>
    public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string formula)
    {
        SetValueRange(startRowIndex, endColIndex, startRowIndex, endColIndex, formula, "");
    }

    /// <summary>
    /// 生成单元格样式
    /// </summary>
    /// <returns>与当前Excel相关的单元格样式</returns>
    public ICellStyle CreateCellStyle()
    {
        return Book.CreateCellStyle();
    }

    /// <summary>
    /// 生成字体
    /// </summary>
    /// <returns>与当前Excel相关的字体</returns>
    public IFont CreateFont()
    {
        return Book.CreateFont();
    }

    /// <summary>
    /// 设置单元格样式
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <param name="style">样式</param>
    public void SetStyle(int rowIndex, int columnIndex, ICellStyle style)
    {
        IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
        if (row == null)
        {
            row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
        }
        ICell cell = row.GetCell(columnIndex);
        if (cell == null)
        {
            cell = row.CreateCell(columnIndex);
        }
        cell.CellStyle = style;
    }

    /// <summary>
    /// 合并单元格
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="startRowIndex">开始行号</param>
    /// <param name="startColumnIndex">开始列号</param>
    /// <param name="endRowIndex">结束行号</param>
    /// <param name="endColumnIndex">结束列号</param>
    public void MergeCells(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
    {
        int index = Book.GetSheetAt(sheetID).AddMergedRegion(new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex));
    }

    /// <summary>
    /// 拆分单元格
    /// 注1：只对当前ActiveSheet有效
    /// 注2：只有合并的单元格才能拆分
    /// </summary>
    /// <param name="startRowIndex">开始行号</param>
    /// <param name="startColumnIndex">开始列号</param>
    public void UnMergeCells(int startRowIndex, int startColumnIndex)
    {
        int merges = Book.GetSheetAt(sheetID).NumMergedRegions;
        CellRangeAddress merge;
        for (int i = 0; i < merges; i++)
        {
            merge = Book.GetSheetAt(sheetID).GetMergedRegion(i);
            if (merge.FirstRow == startRowIndex && merge.FirstColumn == startColumnIndex)
            {
                Book.GetSheetAt(sheetID).RemoveMergedRegion(i);
                break;
            }
        }
    }

    /// <summary>
    /// 保存到文件
    /// 注：有模板的，文件扩展名与模板一样；没有模板的，文件扩展名为“.xls”；
    /// </summary>
    /// <param name="fileName">保存文件名</param>
    public void Save(string fileName)
    {
        FileStream file = new FileStream(fileName, FileMode.Create);
        Book.Write(file);
        file.Close();
    }

    /// <summary>
    /// 保存到流
    /// 注：保存或下载时，有模板的，文件扩展名与模板一样；没有模板的，文件扩展名为“.xls”；
    /// </summary>
    /// <returns>内存流</returns>
    public MemoryStream Save()
    {
        MemoryStream ms = new MemoryStream();
        Book.Write(ms);
        return ms;
    }

    /// <summary>
    /// 把Excel读成DataSet
    /// 注：必须是正规表格式
    /// </summary>
    /// <returns>读出的Excel</returns>
    public DataSet ReadAsDataSet()
    {
        DataSet rtn = new DataSet();
        for (int i = 0; i < SheetCount; i++)
        {
            ISheet sheet = Book.GetSheetAt(i);
            rtn.Tables.Add(GetDataTableBySheet(sheet));
        }
        return rtn;
    }

    private DataTable GetDataTableBySheet(ISheet sheet)
    {
        DataTable dt = new DataTable(sheet.SheetName);
        int maxCols = 0;
        object value;
        while ((value = ReadValue(sheet, 0, maxCols)) != null)
        {
            dt.Columns.Add(value.ToString());
            maxCols++;
        }

        int row = 1;

        bool emptyRow = false;
        int emptyRowCount = 0;
        while (emptyRowCount < 10)
        {
            emptyRow = true;
            DataRow dr = dt.NewRow();
            for (int i = 0; i < maxCols; i++)
            {
                value = ReadValue(sheet, row, i);
                if (value != null)
                {
                    dr[i] = value;
                    emptyRow = false;
                }
            }
            if (!emptyRow)
            {
                dt.Rows.Add(dr);
                emptyRowCount = 0;
            }
            else
            {
                emptyRowCount++;
            }
            row++;
        }

        return dt;
    }

    /// <summary>
    /// 根据SheetName导出数据为DataTable
    /// </summary>
    /// <param name="sheetName">Sheet名称</param>
    /// <returns></returns>
    public DataTable GetDataTableBySheet(string sheetName)
    {

        ISheet sheet = Book.GetSheet(sheetName);
        if (sheet != null)
        {
            return GetDataTableBySheet(sheet);
        }
        return null;

    }

    /// <summary>
    /// 根据SheetName导出数据为DataTable
    /// </summary>
    /// <param name="sheetIndex">Sheet编号</param>
    /// <returns></returns>
    public DataTable GetDataTableBySheet(int sheetIndex)
    {

        ISheet sheet = Book.GetSheetAt(sheetIndex);
        if (sheet != null)
        {
            return GetDataTableBySheet(sheet);
        }
        return null;

    }

    /// <summary>
    /// 写入表格
    /// </summary>
    /// <param name="Data">表格数据</param>
    /// <param name="col">写入的起始列</param>
    /// <param name="row">写入的起始行</param>
    /// <param name="titleColor">标题颜色</param>
    /// <param name="fullBorder">是否需要四周边框</param>
    public void WriteDataTable(DataTable Data, int col = 1, int row = 1, short? titleColor = null, bool fullBorder = true)
    {
        if (Data == null)
        {
            return;
        }

        var titleStyle = CreateCellStyle();
        var rowStyle = CreateCellStyle();
        if (titleColor != null)
            titleStyle.FillForegroundColor = titleColor.Value;

        titleStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
        if (fullBorder)
        {
            titleStyle.BorderBottom = BorderStyle.THIN;
            titleStyle.BorderLeft = BorderStyle.THIN;
            titleStyle.BorderRight = BorderStyle.THIN;
            titleStyle.BorderTop = BorderStyle.THIN;
            titleStyle.BottomBorderColor = NPOIColor.BLACK;
            titleStyle.LeftBorderColor = NPOIColor.BLACK;
            titleStyle.RightBorderColor = NPOIColor.BLACK;
            titleStyle.TopBorderColor = NPOIColor.BLACK;
            rowStyle.BorderBottom = BorderStyle.THIN;
            rowStyle.BorderLeft = BorderStyle.THIN;
            rowStyle.BorderRight = BorderStyle.THIN;
            rowStyle.BorderTop = BorderStyle.THIN;
            rowStyle.BottomBorderColor = NPOIColor.BLACK;
            rowStyle.LeftBorderColor = NPOIColor.BLACK;
            rowStyle.RightBorderColor = NPOIColor.BLACK;
            rowStyle.TopBorderColor = NPOIColor.BLACK;
        }

        int iCol = 0, iRow = 1;

        foreach (DataColumn dc in Data.Columns)
        {
            SetValue(row, col + iCol, dc.ColumnName);
            SetStyle(row, col + iCol, titleStyle);
            iCol++;
        }

        rowStyle.FillForegroundColor = NPOIColor.WHITE;

        foreach (DataRow dr in Data.Rows)
        {
            iCol = 0;
            foreach (DataColumn dc in Data.Columns)
            {
                SetValue(row + iRow, col + iCol, dr[dc]);
                SetStyle(row + iRow, col + iCol, rowStyle);
                iCol++;
            }
            iRow++;
        }

        for (int i = 0; i < iCol; i++)
        {
            this.AutoColWidth(i);
        }
    }

    /// <summary>
    /// 读取单元格的值
    /// 注：只对当前ActiveSheet有效
    /// </summary>
    /// <param name="rowIndex">行号</param>
    /// <param name="columnIndex">列号</param>
    /// <returns>单元格的值</returns>
    public object ReadValue(ISheet sheet, int rowIndex, int columnIndex, bool? isDateTime = null)
    {
        try
        {
            ICell cell = sheet.GetRow(rowIndex).GetCell(columnIndex);
            short df = cell.CellStyle.DataFormat;

            //return cell.ToString();
            switch (cell.CellType)
            {
                case CellType.BLANK:
                    return null;
                case CellType.BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.ERROR:
                    throw new Exception("Cell Value Error");
                case CellType.FORMULA:
                    {
                        switch (cell.CachedFormulaResultType)
                        {
                            case CellType.BLANK:
                                return "";
                            case CellType.BOOLEAN:
                                return cell.BooleanCellValue;
                            case CellType.ERROR:
                                throw new Exception("Cell Value Error");
                            case CellType.FORMULA:
                                throw new Exception("The formula of this cell is too complex！");
                            case CellType.NUMERIC:
                                if (isDateTime == null)
                                {
                                    if (DateUtil.IsCellDateFormatted(cell))
                                    {
                                        return cell.DateCellValue;
                                    }
                                    else
                                    {
                                        return cell.NumericCellValue;
                                    }
                                }
                                else if (isDateTime == true)
                                {
                                    return cell.DateCellValue;
                                }
                                else
                                {
                                    return cell.NumericCellValue;
                                }
                            case CellType.STRING:
                                return cell.StringCellValue;
                            case CellType.Unknown:
                                return cell.ToString();
                            default:
                                return cell.ToString();
                        }
                    }
                case CellType.NUMERIC:
                    {
                        if (isDateTime == null)
                        {
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                return cell.DateCellValue;
                            }
                            else
                            {
                                return cell.NumericCellValue;
                            }
                        }
                        else if (isDateTime == true)
                        {
                            return cell.DateCellValue;
                        }
                        else
                        {
                            return cell.NumericCellValue;
                        }
                    }
                case CellType.STRING:
                    return cell.StringCellValue;
                case CellType.Unknown:
                    return cell.ToString();
                default:
                    return cell.ToString();
            }
        }
        catch (System.NullReferenceException)
        {
            return null;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public int SheetCount
    {
        get
        {
            return Book.NumberOfSheets;
        }
    }

    public string GetSheetName(int index)
    {
        return Book.GetSheetName(index);
    }

    public void AddPicture(byte[] data, int row, int col)
    {
        int picIndex = Book.AddPicture(data, PictureType.PNG);

        IDrawing draw = ActiveSheet.CreateDrawingPatriarch();

        IClientAnchor anchor = draw.CreateAnchor(0, 0, 255, 255, col, row, col + 5, col + 5);
        IPicture pic = draw.CreatePicture(anchor, picIndex);
        pic.Resize();
    }
}

public enum OperatorTypes
{
    /// <summary>
    /// 介于最大值与小值之间
    /// </summary>
    BETWEEN = OperatorType.BETWEEN,
    /// <summary>
    /// 等于最小值
    /// </summary>
    EQUAL = OperatorType.EQUAL,
    /// <summary>
    /// 大于或等于最小值
    /// </summary>
    GREATER_OR_EQUAL = OperatorType.GREATER_OR_EQUAL,
    /// <summary>
    /// 大于最小值
    /// </summary>
    GREATER_THAN = OperatorType.GREATER_THAN,
    /// <summary>
    /// 忽略
    /// </summary>
    NO_COMPARISON = OperatorType.IGNORED,
    /// <summary>
    /// 小于或等于最小值
    /// </summary>
    LESS_OR_EQUAL = OperatorType.LESS_OR_EQUAL,
    /// <summary>
    /// 小于最小值
    /// </summary>
    LESS_THAN = OperatorType.LESS_THAN,
    /// <summary>
    /// 不在最小值与最大值之间
    /// </summary>
    NOT_BETWEEN = OperatorType.NOT_BETWEEN,
    /// <summary>
    /// 不等于最小值
    /// </summary>
    NOT_EQUAL = OperatorType.NOT_EQUAL
}

public enum NPOIDataType
{
    /// <summary>
    /// 验证整数
    /// </summary>
    Integer,
    /// <summary>
    /// 验证符点数
    /// </summary>
    Float,
    /// <summary>
    /// 验证日期
    /// </summary>
    Date,
    /// <summary>
    /// 验证时间
    /// </summary>
    Time,
    /// <summary>
    /// 验证字符长度
    /// </summary>
    TextLength
}

public static class NPOIColor
{

    /// <summary>
    /// 红色
    /// </summary>
    public static short RED { get { return NPOI.HSSF.Util.HSSFColor.RED.index; } }

    /// <summary>
    /// 蓝色
    /// </summary>
    public static short BLUE { get { return NPOI.HSSF.Util.HSSFColor.BLUE.index; } }

    /// <summary>
    /// 浅绿色
    /// </summary>
    public static short AQUA { get { return NPOI.HSSF.Util.HSSFColor.AQUA.index; } }

    /// <summary>
    /// 自动
    /// </summary>
    public static short AUTOMATIC { get { return NPOI.HSSF.Util.HSSFColor.AUTOMATIC.index; } }

    /// <summary>
    /// 黑色
    /// </summary>
    public static short BLACK { get { return NPOI.HSSF.Util.HSSFColor.BLACK.index; } }

    /// <summary>
    /// 蓝灰色
    /// </summary>
    public static short BLUE_GREY { get { return NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index; } }

    /// <summary>
    /// 明绿色
    /// </summary>
    public static short BRIGHT_GREEN { get { return NPOI.HSSF.Util.HSSFColor.BRIGHT_GREEN.index; } }

    /// <summary>
    /// 棕色
    /// </summary>
    public static short BROWN { get { return NPOI.HSSF.Util.HSSFColor.BROWN.index; } }

    /// <summary>
    /// 正常
    /// </summary>
    public static short COLOR_NORMAL { get { return NPOI.HSSF.Util.HSSFColor.COLOR_NORMAL; } }

    /// <summary>
    /// 珊瑚色
    /// </summary>
    public static short CORAL { get { return NPOI.HSSF.Util.HSSFColor.CORAL.index; } }

    /// <summary>
    /// 亮蓝色
    /// </summary>
    public static short CORNFLOWER_BLUE { get { return NPOI.HSSF.Util.HSSFColor.CORNFLOWER_BLUE.index; } }

    /// <summary>
    /// 深蓝色
    /// </summary>
    public static short DARK_BLUE { get { return NPOI.HSSF.Util.HSSFColor.DARK_BLUE.index; } }

    /// <summary>
    /// 深绿色
    /// </summary>
    public static short DARK_GREEN { get { return NPOI.HSSF.Util.HSSFColor.DARK_GREEN.index; } }

    /// <summary>
    /// 深红色
    /// </summary>
    public static short DARK_RED { get { return NPOI.HSSF.Util.HSSFColor.DARK_RED.index; } }

    /// <summary>
    /// 深茶色
    /// </summary>
    public static short DARK_TEAL { get { return NPOI.HSSF.Util.HSSFColor.DARK_TEAL.index; } }

    /// <summary>
    /// 深黄
    /// </summary>
    public static short DARK_YELLOW { get { return NPOI.HSSF.Util.HSSFColor.DARK_YELLOW.index; } }

    /// <summary>
    /// 金色
    /// </summary>
    public static short GOLD { get { return NPOI.HSSF.Util.HSSFColor.GOLD.index; } }

    /// <summary>
    /// 绿色
    /// </summary>
    public static short GREEN { get { return NPOI.HSSF.Util.HSSFColor.GREEN.index; } }

    /// <summary>
    /// 25%灰色
    /// </summary>
    public static short GREY_25_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index; } }

    /// <summary>
    /// 40%灰色
    /// </summary>
    public static short GREY_40_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_40_PERCENT.index; } }

    /// <summary>
    /// 50%灰色
    /// </summary>
    public static short GREY_50_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_50_PERCENT.index; } }

    /// <summary>
    /// 80%灰色
    /// </summary>
    public static short GREY_80_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_80_PERCENT.index; } }

    /// <summary>
    /// 靛蓝色
    /// </summary>
    public static short INDIGO { get { return NPOI.HSSF.Util.HSSFColor.INDIGO.index; } }

    /// <summary>
    /// 淡紫色
    /// </summary>
    public static short LAVENDER { get { return NPOI.HSSF.Util.HSSFColor.LAVENDER.index; } }

    /// <summary>
    /// 粉黄色
    /// </summary>
    public static short LEMON_CHIFFON { get { return NPOI.HSSF.Util.HSSFColor.LEMON_CHIFFON.index; } }

    /// <summary>
    /// 淡蓝色
    /// </summary>
    public static short LIGHT_BLUE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_BLUE.index; } }

    /// <summary>
    /// 淡亮蓝色
    /// </summary>
    public static short LIGHT_CORNFLOWER_BLUE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_CORNFLOWER_BLUE.index; } }

    /// <summary>
    /// 淡绿色
    /// </summary>
    public static short LIGHT_GREEN { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index; } }

    /// <summary>
    /// 淡桔黄色
    /// </summary>
    public static short LIGHT_ORANGE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_ORANGE.index; } }

    /// <summary>
    /// 淡蓝绿色
    /// </summary>
    public static short LIGHT_TURQUOISE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_TURQUOISE.index; } }

    /// <summary>
    /// 淡黄色
    /// </summary>
    public static short LIGHT_YELLOW { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index; } }

    /// <summary>
    /// 绿黄色
    /// </summary>
    public static short LIME { get { return NPOI.HSSF.Util.HSSFColor.LIME.index; } }

    /// <summary>
    /// 栗色
    /// </summary>
    public static short MAROON { get { return NPOI.HSSF.Util.HSSFColor.MAROON.index; } }

    /// <summary>
    /// 橄榄绿色
    /// </summary>
    public static short OLIVE_GREEN { get { return NPOI.HSSF.Util.HSSFColor.OLIVE_GREEN.index; } }

    /// <summary>
    /// 桔色
    /// </summary>
    public static short ORANGE { get { return NPOI.HSSF.Util.HSSFColor.ORANGE.index; } }

    /// <summary>
    /// 白灰蓝色
    /// </summary>
    public static short PALE_BLUE { get { return NPOI.HSSF.Util.HSSFColor.PALE_BLUE.index; } }

    /// <summary>
    /// 粉红色
    /// </summary>
    public static short PINK { get { return NPOI.HSSF.Util.HSSFColor.PINK.index; } }

    /// <summary>
    /// 紫红色
    /// </summary>
    public static short PLUM { get { return NPOI.HSSF.Util.HSSFColor.PLUM.index; } }

    /// <summary>
    /// 玫瑰红色
    /// </summary>
    public static short ROSE { get { return NPOI.HSSF.Util.HSSFColor.ROSE.index; } }

    /// <summary>
    /// 高贵蓝
    /// </summary>
    public static short ROYAL_BLUE { get { return NPOI.HSSF.Util.HSSFColor.ROYAL_BLUE.index; } }

    /// <summary>
    /// 海绿色
    /// </summary>
    public static short SEA_GREEN { get { return NPOI.HSSF.Util.HSSFColor.SEA_GREEN.index; } }

    /// <summary>
    /// 天空蓝
    /// </summary>
    public static short SKY_BLUE { get { return NPOI.HSSF.Util.HSSFColor.SKY_BLUE.index; } }

    /// <summary>
    /// 棕褐色
    /// </summary>
    public static short TAN { get { return NPOI.HSSF.Util.HSSFColor.TAN.index; } }

    /// <summary>
    /// 茶色
    /// </summary>
    public static short TEAL { get { return NPOI.HSSF.Util.HSSFColor.TEAL.index; } }

    /// <summary>
    /// 蓝绿色
    /// </summary>
    public static short TURQUOISE { get { return NPOI.HSSF.Util.HSSFColor.TURQUOISE.index; } }

    /// <summary>
    /// 紫色
    /// </summary>
    public static short VIOLET { get { return NPOI.HSSF.Util.HSSFColor.VIOLET.index; } }

    /// <summary>
    /// 白色
    /// </summary>
    public static short WHITE { get { return NPOI.HSSF.Util.HSSFColor.WHITE.index; } }

    /// <summary>
    /// 黄色
    /// </summary>
    public static short YELLOW { get { return NPOI.HSSF.Util.HSSFColor.YELLOW.index; } }

}

/// <summary>
/// 针对excel的Oledb
/// </summary>
public class OleDbExcel
{
    /// <summary>
    /// OLEDB连接
    /// </summary>
    public OleDbConnection Connection
    {
        get;
        set;
    }

    /// <summary>
    /// 用Oledb对Excel进行操作
    /// 注：必须是标准表形式Excel内容
    /// </summary>
    /// <param name="excelFile">Excel文件</param>
    public OleDbExcel(string excelFile)
    {
        string conStr = string.Empty;
        FileInfo file = new FileInfo(excelFile);
        if (!file.Exists) { throw new Exception("文件不存在"); }
        string extension = file.Extension;
        switch (extension)
        {
            case ".xls":
                conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                break;
            case ".xlsx":
                conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                break;
            default:
                conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                break;
        }

        //链接Excel
        Connection = new OleDbConnection(conStr);
    }

    private List<string> tableNames;

    /// <summary>
    /// 获取Excel内的Sheet名称
    /// </summary>
    public List<string> Sheets
    {
        get
        {
            if (tableNames == null)
            {
                try
                {
                    tableNames = new List<string>();
                    //读取Excel里面的sheet名
                    Connection.Open();

                    DataTable schemaTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        DataRow dr = schemaTable.Rows[i];
                        string tbName = dr["table_name"].ToString();
                        if (tbName[tbName.Length - 1] == '$')
                        {
                            tableNames.Add(tbName);
                        }
                    }
                    Connection.Close();
                }
                catch (Exception ex)
                {
                    if (Connection.State != ConnectionState.Closed)
                    {
                        Connection.Close();
                    }
                    throw new Exception(ex.Message, ex);
                }
            }
            return tableNames;
        }
    }

    /// <summary>
    /// 查询出所有数据
    /// </summary>
    /// <param name="tableName">Sheet名称</param>
    /// <returns>sheet内的所有数据</returns>
    public DataSet QueryAll(string tableName)
    {
        try
        {
            DataSet excelData = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = new OleDbCommand();
            adapter.SelectCommand.Connection = Connection;
            adapter.SelectCommand.CommandText = string.Format("SELECT * FROM {0}", "[" + tableName + "]");
            adapter.Fill(excelData);
            return excelData;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }

    /// <summary>
    /// 查询出所有数据
    /// </summary>
    /// <param name="tableIndex">Sheet序号（从0开始）</param>
    /// <returns>sheet内的所有数据</returns>
    public DataSet QueryAll(int tableIndex)
    {
        return QueryAll(Sheets[tableIndex]);
    }

    /// <summary>
    /// 利用Sql进行查询
    /// </summary>
    /// <param name="sql">Sql语句</param>
    /// <returns>查询出的数据</returns>
    public DataSet Query(string sql)
    {
        try
        {
            DataSet excelData = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, Connection);
            adapter.Fill(excelData);
            return excelData;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }

    /// <summary>
    /// 利用Sql进行查询
    /// </summary>
    /// <param name="sql">Sql语句</param>
    /// <param name="param">查询参数</param>
    /// <returns>查询出的数据</returns>
    public DataSet Query(string sql, params OleDbParameter[] param)
    {
        try
        {
            DataSet excelData = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, Connection);
            adapter.SelectCommand.Parameters.AddRange(param);
            adapter.Fill(excelData);
            return excelData;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }

    /// <summary>
    /// 利用Sql进行数据操作
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns>影响的行数</returns>
    public int ExecuteSql(string sql)
    {
        try
        {
            Connection.Open();
            OleDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = sql;
            int rtn = cmd.ExecuteNonQuery();
            Connection.Close();
            return rtn;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }

    /// <summary>
    /// 利用Sql进行数据操作
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <param name="param">执行参数</param>
    /// <returns>影响的行数</returns>
    public int ExecuteSql(string sql, params OleDbParameter[] param)
    {
        try
        {
            Connection.Open();
            OleDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(param);
            int rtn = cmd.ExecuteNonQuery();
            Connection.Close();
            return rtn;
        }
        catch (Exception ex)
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            throw new Exception(ex.Message, ex);
        }
    }
}

3、后台引用

　　　　[HttpPost]
        public ActionResult Import(HttpPostedFileBase importfile, SGM_ShenHeForm form, int? pageSize, int? CurrentPageIndex, int? ReplaceFlag = 0)
        {
            importfile = Request.Files["importfile"];
            if (importfile == null)
            {
                return Content("<script>alert('未选择文件！');history.go(-1);</script>");
            } 

　　　　　　　　NPOIExcel excel = new NPOIExcel(importfile.InputStream, importfile.FileName);

                var ITEM_SERI_STR = excel.ReadValue(i, 2);   //获取excel里的值

        }

4、界面、JS

  <div style="margin-top: 10px; margin-bottom: 5px; position: relative; overflow-y: hidden;display:inline">
                        <input type="button" id="btnImport" value="导入" />
                        <input name="importfile" id="importfile" type="file" style="opacity: 0; filter: alpha(opacity=0); width: 80px; font-size: 15px; position: absolute; top: 0; left: 0; margin-left: -15px;" accept=".xls,.xlsx"
                            onchange=" $.messager.progress({title: '请稍候',msg: '正在处理数据...'});Import();" />
                    </div>

  function Import() {
        debugger;
        var old = $("#f1").attr("action");  //form 表单的ID
        $("#f1").attr("action", "@Url.Action("Import")");
        $("#f1").attr("enctype", "multipart/form-data");
        $("#f1").submit();
        $("#f1").attr("action", old);
    }

前台也可以是不用表单形式，放一个 <input type="file" name="upfile" value=" " /> 按钮提交到后台一个方法，方法里接收file

 5、效果

点击导入按钮后弹出选择文件框，选择excel文件后（双击或者打开）直接提交后台