NPOI使用教程附Helper

1 NPOI簡介

1.1 NPOI是什麼

　　NPOI是POI的.NET版本,POI是一套用Java寫成的庫，我們在開發中經常用到匯入匯出表格、文件的情況，NPOI能夠幫助我們在沒有安裝微軟Office的情況下讀寫Office檔案，如xls, doc, ppt等。NPOI採用的是Apache 2.0許可證（poi也是採用這個許可證），這意味著它可以被用於任何商業或非商業專案，我們不用擔心因為使用它而必須開放你自己的原始碼，所以它對於很多從事業務系統開發的公司來說絕對是很不錯的選擇。

1.2 NPOI簡單使用

　　NPOI的API十分人性化，使用起來非常容易上手。首先建立一個控制檯應用程式，通過nuget獲取NPOI，這裡使用的是最新版本2.4.0，如下：

　　專案引入NPOI後就可以直接上手使用了，程式碼如下

        static void Main(string[] args)
        {
            //建立workbook，說白了就是在記憶體中建立一個Excel檔案
            IWorkbook workbook = new HSSFWorkbook();
            //要新增至少一個sheet,沒有sheet的excel是打不開的
            ISheet sheet1 = workbook.CreateSheet("sheet1");
            ISheet sheet2 = workbook.CreateSheet("sheet2");

            IRow row1 = sheet1.CreateRow(0);//新增第1行,注意行列的索引都是從0開始的
            ICell cell1 = row1.CreateCell(0);//給第1行新增第1個單元格
            cell1.SetCellValue("hello npoi!");//給單元格賦值
            //上邊3個步驟合在一起：sheet1.CreateRow(0).CreateCell(0).SetCellValue("hello npoi");

            //獲取第一行第一列的string值
            Console.WriteLine(sheet1.GetRow(0).GetCell(0).StringCellValue); //輸出：hello npoi

            //寫入檔案
            using (FileStream file = new FileStream(@"D:/TestFiles/test.xls", FileMode.Create))
            {
                workbook.Write(file);
            }
        }

執行程式碼，結果如下，同時也在D盤的TestFiles資料夾下建立了test.xls檔案

2 NPOI使用詳解

2.1 單元格資料格式

　　用過Excel的人都知道，單元格是Excel最有意義的東西，我們做任何操作恐怕都要和單元格打交道。在Excel中我們經常要設定格式，比如說日期格式（2018-1-1）、小數點格式（1.20）、貨幣格式（$2000）、百分比格式（99.99%）等等，這些東西在過去我們恐怕只能在伺服器端生成好，不但增加了伺服器端的程式碼量，還造成了不必要的字串替換操作，如今NPOI將讓伺服器從這種完全沒有必要的操作中解放出來，一切都將由Excel在客戶端處理。

　　使用NPOI時要注意，所有的格式都是通過 CellStyle.DataFormat 賦給單元格的，而不是直接賦給單元格，設定單元格資料展示的格式有兩種方法。

1.設定excel中內建的格式：

excel內部設定了很多格式，如下圖所示：

如果我們想使用這些內建的格式，可用通過 DataFormat.GetBuildinFormat('格式') 來使用，使用例項如下：

cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");  //兩位小數，內建格式

2.設定自定義格式

　　有時候內建的格式不能滿足我們的要求，這時候就可以使用 workbook.CreateDataFormat().GetFormat("格式") 自定義格式了，使用語法如下：

cellStyle.DataFormat= workbook.CreateDataFormat().GetFormat("yyyy年m月d日 hh時mm分ss秒");//顯示中文日期和時間，自定義格式

下邊的栗子展示了幾種常用的資料格式用法：

       static void Main(string[] args)
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("new sheet");
            //設定日期格式，2018年5月5日格式
            ICell cell = sheet.CreateRow(0).CreateCell(0);
            cell.SetCellValue(new DateTime(2018, 5, 5, 11, 31, 56));
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy年m月d日 hh時mm分ss秒");
            //cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/mm/dd hh:mm:ss"); 
            cell.CellStyle = cellStyle;

            //保留2位小數
            ICell cell2 = sheet.CreateRow(1).CreateCell(0);
            cell2.SetCellValue(12.346666);
            ICellStyle cellStyle2 = workbook.CreateCellStyle();
            cellStyle2.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
            cell2.CellStyle = cellStyle2;

            //貨幣格式
            ICell cell3 = sheet.CreateRow(2).CreateCell(0);
            cell3.SetCellValue(1234.66666);
            ICellStyle cellStyle3 = workbook.CreateCellStyle();
            cellStyle3.DataFormat = workbook.CreateDataFormat().GetFormat("￥#,##0.00");//美元的話格式為 $#,##0.00，其中#，##0表示千分號
            cell3.CellStyle = cellStyle3;

            //百分比
            ICell cell4 = sheet.CreateRow(3).CreateCell(0);
            cell4.SetCellValue(1.236666);
            ICellStyle cellStyle4 = workbook.CreateCellStyle();
            //cellStyle4.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");//保留兩位小數
            cellStyle4.DataFormat = HSSFDataFormat.GetBuiltinFormat("0%");
            cell4.CellStyle = cellStyle4;

            //中文大寫數字
            ICell cell5 = sheet.CreateRow(4).CreateCell(0);
            cell5.SetCellValue(111);
            ICellStyle cellStyle5 = workbook.CreateCellStyle();
            cellStyle5.DataFormat = workbook.CreateDataFormat().GetFormat("[DbNum2][$-804]0.00");//不保留小數： [DbNum2][$-804]0
            cell5.CellStyle = cellStyle5;


            //科學計數法
            ICell cell6 = sheet.CreateRow(5).CreateCell(0);
            cell6.SetCellValue(1234.6666);
            ICellStyle cellStyle6 = workbook.CreateCellStyle();
            cellStyle6.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
            cell6.CellStyle = cellStyle6;

            using (FileStream file = new FileStream(@"D:/TestFiles/test.xls", FileMode.Create))
            {
                workbook.Write(file);
            }
        }

執行程式，生成的excel檔案如下所示：

2.2 單元格合併及設定風格

　　單元格的合併和風格這部分內容比較多，但是Api十分的簡單，為了看起來清晰，我們分成兩個栗子來演示這部分的功能，程式碼中的註釋很詳細，這裡就直接上程式碼了

1.第一個栗子演示單元格合併，內容停靠，字型風格顏色大小等

       static void Main(string[] args)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("sheet1");
            
            //------------------單元格合併
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 1, 5));//合併單元格,4個引數依次為為startRow,endRow,startCol,endCol
            ICell cell = sheet.CreateRow(1).CreateCell(1);
            cell.SetCellValue("合併的區域");
            cell.Row.HeightInPoints = 40;//行高
            ICellStyle cellstyle = workbook.CreateCellStyle();

            //----------------設定單元格常用風格
            cellstyle.Alignment = HorizontalAlignment.Left;//水平居左，可選Right,Center
            cellstyle.VerticalAlignment = VerticalAlignment.Bottom;//垂直居中,可選Top,Center
            cellstyle.WrapText = true;//自動換行

            cellstyle.Indention = 4;//縮排4個位元組
            //cellstyle.Rotation = 90;//字型旋轉90度，取值範圍是[-90,90]

            //---------------字型，字型大小、顏色
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 24;//字號為24，可以用font.FontHeight = 24 * 20;FontHeight的單位是1/20點

            font.FontName = "楷體";//字型
            font.Boldweight = 700;//加粗
            font.Color = HSSFColor.Blue.Index;//字型顏色
            cellstyle.SetFont(font);
            cell.CellStyle = cellstyle;
            using (FileStream file = new FileStream(@"D:/TestFiles/test.xls", FileMode.Create))
            {
                workbook.Write(file);
            }
        }

生成的excel檔案如下圖所示：

2.第二個栗子演示單元格的寬高、背景色、邊框風格等

       static void Main(string[] args)
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("sheet1");

            ICell cell = sheet.CreateRow(1).CreateCell(1);
            cell.SetCellValue("hello");
            ICellStyle cellstyle = workbook.CreateCellStyle();
            //設定寬度，把第2列設成20位元組寬；引數1指定列的索引；引數2指定寬度，單位是1/256個字元寬度
            sheet.SetColumnWidth(1, 20 * 256);
            //高度設成40，單位是點；也可以用cell2.Row.Height = 40 * 20;Height的單位是1/20點
            cell.Row.HeightInPoints = 40;
            //右邊框設定成點畫線
            cellstyle.BorderRight = BorderStyle.MediumDashDot;
            //右邊框設成粉色
            cellstyle.RightBorderColor = HSSFColor.Pink.Index;
            //前景色填充模式，實心
            cellstyle.FillPattern = FillPattern.SolidForeground;
            //前景色填充顏色，黃色
            cellstyle.FillForegroundColor = HSSFColor.Yellow.Index;
            //背景色
            //cellstyle2.FillBackgroundColor = HSSFColor.Green.Index;//背景色設成green
            cell.CellStyle = cellstyle;

            using (FileStream file = new FileStream(@"D:/TestFiles/test.xls", FileMode.Create))
            {
                workbook.Write(file);
            }
        }

執行程式，生成的excel檔案如下：

　　其實在NPOI中除了操作單元格的內容和風格外，還提供了新增excel檔案摘要，新增批註，畫圖，使用公式等功能，但是這些功能我們用的比較少，所以就不一一展示了，NPOI的公式我自己很少去使用，一般通過程式直接計算結果，個人感覺在程式中計算更加靈活，當excel的結構發生變化時修改起來也更方便些。

3.封裝一個NPOI的helper類

　　我們先看一下這個helper的使用吧，先看從DataTable匯出到excel

   class Program
    {
        static void Main(string[] args)
        {
            log4net.Config.XmlConfigurator.Configure();
           
            //測試dataTable資料來源 
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("id", typeof(int));
            dataTable.Columns.Add("uname", typeof(string));
            dataTable.Columns.Add("sex", typeof(string));
            dataTable.Columns.Add("age", typeof(int));
            dataTable.Columns.Add("pwd", typeof(string));
            dataTable.Columns.Add("email", typeof(string));
            dataTable.Columns.Add("address", typeof(string));
            Random r = new Random();
            for (int i = 0; i < 1000; i++)
            {
                DataRow row = dataTable.NewRow();
                row["id"] = i;
                row["uname"] = "hellox" + i.ToString();
                row["sex"] = r.Next(2) % 2 == 0 ? "男" : "女";
                row["age"] = r.Next(40) + 18;
                row["pwd"] = "pwd" + r.Next(5000).ToString();
                row["email"] = r.Next(100000) + "@qq.com";
                row["address"] = $"北京市，西{r.Next(4) + 1}環，xx路{r.Next(100)}號";
                dataTable.Rows.Add(row);
            }
            //DataTable的列名和excel的列名對應字典，因為excel的列名一般是中文的，DataTable的列名是英文的，字典主要是儲存excel和DataTable列明的對應關係，當然我們也可以把這個對應關係存在配置檔案或者其他地方
            Dictionary<string, string> dir = new Dictionary<string, string>();
            dir.Add("id", "編號");
            dir.Add("uname", "使用者");
            dir.Add("sex", "性別");
            dir.Add("age", "年齡");
            dir.Add("pwd", "密碼");
            dir.Add("email", "郵箱");
            dir.Add("address", "住址");

            //使用helper類匯出DataTable資料到excel表格中,引數依次是 （源資料，excel表名，excel存放位置，列名對應字典，是否清空以前的資料，每個sheet放的資料條數）
            NPOIHelper.ExportDTtoExcel(dataTable, "考勤資訊表", @"C:\Users\ZDZN\Desktop/Hello11.xlsx", dir, false, 400);
           
        }
    }

執行程式，當一個sheet的數目滿了後會新生產一個sheet1來儲存，效果如下：

接下來測試讀取excel中的資料到datatable中，我們就讀取剛才生成的excel中的資料到DataTable中去：

 static void Main(string[] args)
        {
            log4net.Config.XmlConfigurator.Configure();
           
            //DataTable的列名和excel的列名對應字典
            Dictionary<string, string> dir = new Dictionary<string, string>();
            dir.Add("id", "編號");
            dir.Add("uname", "使用者");
            dir.Add("sex", "性別");
            dir.Add("age", "年齡");
            dir.Add("pwd", "密碼");
            dir.Add("email", "郵箱");
            dir.Add("address", "住址");
            
            //讀取資料到DataTable，引數依此是（excel檔案路徑，列名對應字典，列名所在行，sheet索引）
            DataTable dt = NPOIHelper.ImportExceltoDt(@"C:\Users\ZDZN\Desktop/Hello11.xlsx", dir, 1, 0);
           //遍歷DataTable---------------------------
            foreach (DataColumn item in dt.Columns)
            {
                //顯示dataTable的列名
                Console.Write(item.ColumnName + new string('-', 10));
            }
            Console.WriteLine();
            //遍歷DataTable中的資料
            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    Console.Write(row[i].ToString() + new string(' ', 10));

                }
                Console.WriteLine();
            }

            Console.WriteLine("ok");
            Console.ReadKey();
        }

程式執行如下：

上邊例子中的helper相容了xls和xlsx兩種格式，通過helper只需要一行程式碼就可以快速地進行excel和datatable間的資料轉換，helper附件如下

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.Util;
using NPOI.SS;
using NPOI.DDF;
using System.Linq;
using NPOI.SS.Util;
using System.Collections;
using System.Text.RegularExpressions;
using NPOI.XSSF;
using NPOI.XSSF.UserModel;
using log4net;

public class NPOIHelper
{
    private static ILog loger = LogManager.GetLogger("myLogger");

    #region 從DataTable匯出到excel檔案中，支援xls和xlsx格式
    #region 匯出為xls檔案內部方法
    /// <summary>
    /// 從datatable 中匯出到excel
    /// </summary>
    /// <param name="strFileName">excel檔名</param>
    /// <param name="dtSource">datatabe源資料</param>
    /// <param name="strHeaderText">表名</param>
    /// <param name="sheetnum">sheet的編號</param>
    /// <returns></returns>
    static MemoryStream ExportDT(String strFileName, DataTable dtSource, string strHeaderText, Dictionary<string, string> dir, int sheetnum)
    {
        //建立工作簿和sheet
        IWorkbook workbook = new HSSFWorkbook();
        using (Stream writefile = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read))
        {
            if (writefile.Length > 0 && sheetnum > 0)
            {
                workbook = WorkbookFactory.Create(writefile) ;
            }
        }

        ISheet sheet = null;
        ICellStyle dateStyle = workbook.CreateCellStyle();
        IDataFormat format = workbook.CreateDataFormat();
        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
        int[] arrColWidth = new int[dtSource.Columns.Count];
        foreach (DataColumn item in dtSource.Columns)
        {
            arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
        }
        for (int i = 0; i < dtSource.Rows.Count; i++)
        {
            for (int j = 0; j < dtSource.Columns.Count; j++)
            {
                int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                if (intTemp > arrColWidth[j])
                {
                    arrColWidth[j] = intTemp;
                }
            }
        }
        int rowIndex = 0;
        foreach (DataRow row in dtSource.Rows)
        {
            #region 新建表，填充表頭，填充列頭，樣式
            if (rowIndex == 0)
            {
                string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
                if (workbook.GetSheetIndex(sheetName) >= 0)
                {
                    workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
                }
                sheet = workbook.CreateSheet(sheetName);
                #region 表頭及樣式
                {
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    IRow headerRow = sheet.CreateRow(0);
                    headerRow.HeightInPoints = 25;
                    headerRow.CreateCell(0).SetCellValue(strHeaderText);
                    ICellStyle headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;
                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 20;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    headerRow.GetCell(0).CellStyle = headStyle;

                    rowIndex = 1;
                }
                #endregion

                #region 列頭及樣式

                if (rowIndex == 1)
                {
                    IRow headerRow = sheet.CreateRow(1);//第二行設定列名
                    ICellStyle headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;
                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    //寫入列標題
                    foreach (DataColumn column in dtSource.Columns)
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                        //設定列寬
                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);
                    }
                    rowIndex = 2;
                }
                #endregion
            }
            #endregion

            #region 填充內容

            IRow dataRow = sheet.CreateRow(rowIndex);
            foreach (DataColumn column in dtSource.Columns)
            {
                ICell newCell = dataRow.CreateCell(column.Ordinal);
                string drValue = row[column].ToString();
                switch (column.DataType.ToString())
                {
                    case "System.String": //字串型別
                        double result;
                        if (isNumeric(drValue, out result))
                        {
                            //數字字串
                            double.TryParse(drValue, out result);
                            newCell.SetCellValue(result);
                            break;
                        }
                        else
                        {
                            newCell.SetCellValue(drValue);
                            break;
                        }

                    case "System.DateTime": //日期型別
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell.SetCellValue(dateV);

                        newCell.CellStyle = dateStyle; //格式化顯示
                        break;
                    case "System.Boolean": //布林型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16": //整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell.SetCellValue(intV);
                        break;
                    case "System.Decimal": //浮點型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull": //空值處理
                        newCell.SetCellValue("");
                        break;
                    default:
                        newCell.SetCellValue("");
                        break;
                }

            }
            #endregion
            rowIndex++;
        }
        //DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
        //dsi.Company = "江南皮革廠";
        //workbook.DocumentSummaryInformation = dsi;
        //SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        //si.Author = "bigpig"; //填加xls檔案作者資訊
        //si.ApplicationName = "NPOI測試程式"; //填加xls檔案建立程式資訊
        //si.LastAuthor = "小豬佩奇"; //填加xls檔案最後儲存者資訊
        //si.Comments = "說明資訊"; //填加xls檔案作者資訊
        //si.Title = "NPOI測試"; //填加xls檔案標題資訊
        //si.Subject = "匯出datatable中的資料"; //填加檔案主題資訊
        //si.CreateDateTime = DateTime.Now;
        //workbook.SummaryInformation = si;
        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return ms;
        }

    }
    #endregion

    #region 匯出為xlsx檔案內部方法
    /// <summary>
    /// 從datatable 中匯出到excel
    /// </summary>
    /// <param name="dtSource">datatable資料來源</param>
    /// <param name="strHeaderText">表名</param>
    /// <param name="fs">檔案流</param>
    /// <param name="readfs">記憶體流</param>
    /// <param name="sheetnum">sheet索引</param>
    static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs, MemoryStream readfs, Dictionary<string, string> dir, int sheetnum)
    {
        IWorkbook workbook = new XSSFWorkbook();
        if (readfs.Length > 0 && sheetnum > 0)
        {
            workbook = WorkbookFactory.Create(readfs);
        }
        ISheet sheet = null;
        ICellStyle dateStyle = workbook.CreateCellStyle();
        IDataFormat format = workbook.CreateDataFormat();
        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

        //取得列寬
        int[] arrColWidth = new int[dtSource.Columns.Count];
        foreach (DataColumn item in dtSource.Columns)
        {
            arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
        }
        for (int i = 0; i < dtSource.Rows.Count; i++)
        {
            for (int j = 0; j < dtSource.Columns.Count; j++)
            {
                int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                if (intTemp > arrColWidth[j])
                {
                    arrColWidth[j] = intTemp;
                }
            }
        }
        int rowIndex = 0;

        foreach (DataRow row in dtSource.Rows)
        {
            #region 新建表，填充表頭，填充列頭，樣式

            if (rowIndex == 0)
            {
                #region 表頭及樣式
                {
                    string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
                    if (workbook.GetSheetIndex(sheetName) >= 0)
                    {
                        workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
                    }
                    sheet = workbook.CreateSheet(sheetName);
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    IRow headerRow = sheet.CreateRow(0);
                    headerRow.HeightInPoints = 25;
                    headerRow.CreateCell(0).SetCellValue(strHeaderText);

                    ICellStyle headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;
                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 20;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    headerRow.GetCell(0).CellStyle = headStyle;
                }
                #endregion

                #region 列頭及樣式
                {
                    IRow headerRow = sheet.CreateRow(1);
                    ICellStyle headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;
                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);


                    foreach (DataColumn column in dtSource.Columns)
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                        //設定列寬
                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);

                    }
                }

                #endregion

                rowIndex = 2;
            }
            #endregion

            #region 填充內容

            IRow dataRow = sheet.CreateRow(rowIndex);
            foreach (DataColumn column in dtSource.Columns)
            {
                ICell newCell = dataRow.CreateCell(column.Ordinal);
                string drValue = row[column].ToString();
                switch (column.DataType.ToString())
                {
                    case "System.String": //字串型別
                        double result;
                        if (isNumeric(drValue, out result))
                        {

                            double.TryParse(drValue, out result);
                            newCell.SetCellValue(result);
                            break;
                        }
                        else
                        {
                            newCell.SetCellValue(drValue);
                            break;
                        }

                    case "System.DateTime": //日期型別
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell.SetCellValue(dateV);

                        newCell.CellStyle = dateStyle; //格式化顯示
                        break;
                    case "System.Boolean": //布林型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16": //整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell.SetCellValue(intV);
                        break;
                    case "System.Decimal": //浮點型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull": //空值處理
                        newCell.SetCellValue("");
                        break;
                    default:
                        newCell.SetCellValue("");
                        break;
                }
            }
            #endregion
            rowIndex++;
        }
        workbook.Write(fs);
        fs.Close();
    }
    #endregion

    #region 匯出excel表格
    /// <summary>
    ///  DataTable匯出到Excel檔案，xls檔案
    /// </summary>
    /// <param name="dtSource">資料來源</param>
    /// <param name="strHeaderText">表名</param>
    /// <param name="strFileName">excel檔名</param>
    /// <param name="dir">datatable和excel列名對應字典</param>
    /// <param name="sheetRow">每個sheet存放的行數</param>
    public static void ExportDTtoExcel(DataTable dtSource, string strHeaderText, string strFileName, Dictionary<string, string> dir, bool isNew, int sheetRow = 50000)
    {
        int currentSheetCount = GetSheetNumber(strFileName);//現有的頁數sheetnum
        if (sheetRow <= 0)
        {
            sheetRow = dtSource.Rows.Count;
        }
        string[] temp = strFileName.Split('.');
        string fileExtens = temp[temp.Length - 1];
        int sheetCount = (int)Math.Ceiling((double)dtSource.Rows.Count / sheetRow);//sheet數目
        if (temp[temp.Length - 1] == "xls" && dtSource.Columns.Count < 256 && sheetRow < 65536)
        {
            if (isNew)
            {
                currentSheetCount = 0;
            }
            for (int i = currentSheetCount; i < currentSheetCount + sheetCount; i++)
            {
                DataTable pageDataTable = dtSource.Clone();
                int hasRowCount = dtSource.Rows.Count - sheetRow * (i - currentSheetCount) < sheetRow ? dtSource.Rows.Count - sheetRow * (i - currentSheetCount) : sheetRow;
                for (int j = 0; j < hasRowCount; j++)
                {
                    pageDataTable.ImportRow(dtSource.Rows[(i- currentSheetCount) * sheetRow + j]);
                }

                using (MemoryStream ms = ExportDT(strFileName, pageDataTable, strHeaderText, dir, i))
                {
                    using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                    {

                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                }
            }
        }
        else
        {
            if (temp[temp.Length - 1] == "xls")
                strFileName = strFileName + "x";
            if (isNew)
            {
                currentSheetCount = 0;
            }
            for (int i = currentSheetCount; i < currentSheetCount + sheetCount; i++)
            {
                DataTable pageDataTable = dtSource.Clone();
                int hasRowCount = dtSource.Rows.Count - sheetRow * (i - currentSheetCount) < sheetRow ? dtSource.Rows.Count - sheetRow * (i - currentSheetCount) : sheetRow;
                for (int j = 0; j < hasRowCount; j++)
                {
                    pageDataTable.ImportRow(dtSource.Rows[(i - currentSheetCount) * sheetRow + j]);
                }
                FileStream readfs = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read);
                MemoryStream readfsm = new MemoryStream();
                readfs.CopyTo(readfsm);
                readfs.Close();
                using (FileStream writefs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {

                    ExportDTI(pageDataTable, strHeaderText, writefs, readfsm, dir, i);
                }
                readfsm.Close();
            }
        }
    }
    #endregion 
    #endregion

    #region 從excel檔案中將資料匯出到datatable/datatable
    /// <summary>
    /// 將制定sheet中的資料匯出到datatable中
    /// </summary>
    /// <param name="sheet">需要匯出的sheet</param>
    /// <param name="HeaderRowIndex">列頭所在行號，-1表示沒有列頭</param>
    /// <param name="dir">excel列名和DataTable列名的對應字典</param>
    /// <returns></returns>
    static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, Dictionary<string, string> dir)
    {
        DataTable table = new DataTable();
        IRow headerRow;
        int cellCount;
        try
        {
            //沒有標頭或者不需要表頭用excel列的序號（1,2,3..）作為DataTable的列名
            if (HeaderRowIndex < 0)
            {
                headerRow = sheet.GetRow(0);
                cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                {
                    DataColumn column = new DataColumn(Convert.ToString(i));
                    table.Columns.Add(column);
                }
            }
            //有表頭，使用表頭做為DataTable的列名
            else
            {
                headerRow = sheet.GetRow(HeaderRowIndex);
                cellCount = headerRow.LastCellNum;
                for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                {
                    //如果excel某一列列名不存在：以該列的序號作為Datatable的列名，如果DataTable中包含了這個序列為名的列，那麼列名為重複列名+序號
                    if (headerRow.GetCell(i) == null)
                    {
                        if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
                        {
                            DataColumn column = new DataColumn(Convert.ToString("重複列名" + i));
                            table.Columns.Add(column);
                        }
                        else
                        {
                            DataColumn column = new DataColumn(Convert.ToString(i));
                            table.Columns.Add(column);
                        }

                    }
                    //excel中的某一列列名不為空，但是重複了：對應的Datatable列名為“重複列名+序號”
                    else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
                    {
                        DataColumn column = new DataColumn(Convert.ToString("重複列名" + i));
                        table.Columns.Add(column);
                    }
                    else
                    //正常情況，列名存在且不重複：用excel中的列名作為datatable中對應的列名
                    {
                        string colName = dir.Where(s => s.Value == headerRow.GetCell(i).ToString()).First().Key;
                        DataColumn column = new DataColumn(colName);
                        table.Columns.Add(column);
                    }
                }
            }
            int rowCount = sheet.LastRowNum;
            for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)//excel行遍歷
            {
                try
                {
                    IRow row;
                    if (sheet.GetRow(i) == null)//如果excel有空行，則新增缺失的行
                    {
                        row = sheet.CreateRow(i);
                    }
                    else
                    {
                        row = sheet.GetRow(i);
                    }

                    DataRow dataRow = table.NewRow();

                    for (int j = row.FirstCellNum; j <= cellCount; j++)//excel列遍歷
                    {
                        try
                        {
                            if (row.GetCell(j) != null)
                            {
                                switch (row.GetCell(j).CellType)
                                {
                                    case CellType.String://字串
                                        string str = row.GetCell(j).StringCellValue;
                                        if (str != null && str.Length > 0)
                                        {
                                            dataRow[j] = str.ToString();
                                        }
                                        else
                                        {
                                            dataRow[j] = default(string);
                                        }
                                        break;
                                    case CellType.Numeric://數字
                                        if (DateUtil.IsCellDateFormatted(row.GetCell(j)))//時間戳數字
                                        {
                                            dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
                                        }
                                        else
                                        {
                                            dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
                                        }
                                        break;
                                    case CellType.Boolean:
                                        dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                        break;
                                    case CellType.Error:
                                        dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                        break;
                                    case CellType.Formula://公式
                                        switch (row.GetCell(j).CachedFormulaResultType)
                                        {
                                            case CellType.String:
                                                string strFORMULA = row.GetCell(j).StringCellValue;
                                                if (strFORMULA != null && strFORMULA.Length > 0)
                                                {
                                                    dataRow[j] = strFORMULA.ToString();
                                                }
                                                else
                                                {
                                                    dataRow[j] = null;
                                                }
                                                break;
                                            case CellType.Numeric:
                                                dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
                                                break;
                                            case CellType.Boolean:
                                                dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                                break;
                                            case CellType.Error:
                                                dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                                break;
                                            default:
                                                dataRow[j] = "";
                                                break;
                                        }
                                        break;
                                    default:
                                        dataRow[j] = "";
                                        break;
                                }
                            }
                        }
                        catch (Exception exception)
                        {
                            loger.Error(exception.ToString());
                        }
                    }
                    table.Rows.Add(dataRow);
                }
                catch (Exception exception)
                {
                    loger.Error(exception.ToString());
                }
            }
        }
        catch (Exception exception)
        {
            loger.Error(exception.ToString());
        }
        return table;
    }

    /// <summary>
    /// 讀取Excel檔案特定名字sheet的內容到DataTable
    /// </summary>
    /// <param name="strFileName">excel檔案路徑</param>
    /// <param name="sheet">需要匯出的sheet</param>
    /// <param name="HeaderRowIndex">列頭所在行號，-1表示沒有列頭</param>
    /// <param name="dir">excel列名和DataTable列名的對應字典</param>
    /// <returns></returns>
    public static DataTable ImportExceltoDt(string strFileName, Dictionary<string, string> dir, string SheetName, int HeaderRowIndex = 1)
    {
        DataTable table = new DataTable();
        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
        {
            if (file.Length > 0)
            {
                IWorkbook wb = WorkbookFactory.Create(file);
                ISheet isheet = wb.GetSheet(SheetName);
                table = ImportDt(isheet, HeaderRowIndex, dir);
                isheet = null;
            }
        }
        return table;
    }

    /// <summary>
    /// 讀取Excel檔案某一索引sheet的內容到DataTable
    /// </summary>
    /// <param name="strFileName">excel檔案路徑</param>
    /// <param name="sheet">需要匯出的sheet序號</param>
    /// <param name="HeaderRowIndex">列頭所在行號，-1表示沒有列頭</param>
    /// <param name="dir">excel列名和DataTable列名的對應字典</param>
    /// <returns></returns>
    public static DataTable ImportExceltoDt(string strFileName, Dictionary<string, string> dir, int HeaderRowIndex = 1, int SheetIndex = 0)
    {
        DataTable table = new DataTable();
        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
        {
            if (file.Length > 0)
            {
                IWorkbook wb = WorkbookFactory.Create(file);
                ISheet isheet = wb.GetSheetAt(SheetIndex);
                table = ImportDt(isheet, HeaderRowIndex, dir);
                isheet = null;
            }
        }
        return table;

    }
    #endregion



    /// <summary>
    /// 獲取excel檔案的sheet數目
    /// </summary>
    /// <param name="outputFile"></param>
    /// <returns></returns>
    public static int GetSheetNumber(string outputFile)
    {
        int number = 0;
        using (FileStream readfile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Read))
        {
            if (readfile.Length > 0)
            {
                IWorkbook wb = WorkbookFactory.Create(readfile);
                number = wb.NumberOfSheets;
            }
        }
        return number;
    }

    /// <summary>
    /// 判斷內容是否是數字
    /// </summary>
    /// <param name="message"></param>
    /// <param name="result"></param>
    /// <returns></returns>
    public static bool isNumeric(String message, out double result)
    {
        Regex rex = new Regex(@"^[-]?\d+[.]?\d*$");
        result = -1;
        if (rex.IsMatch(message))
        {
            result = double.Parse(message);
            return true;
        }
        else
            return false;
    }


    /// <summary>
    /// 驗證匯入的Excel是否有資料
    /// </summary>
    /// <param name="excelFileStream"></param>
    /// <returns></returns>
    public static bool HasData(Stream excelFileStream)
    {
        using (excelFileStream)
        {
            IWorkbook workBook = new HSSFWorkbook(excelFileStream);
            if (workBook.NumberOfSheets > 0)
            {
                ISheet sheet = workBook.GetSheetAt(0);
                return sheet.PhysicalNumberOfRows > 0;
            }
        }
        return false;
    }
}