NPOI 匯出 Excel

最終效果圖

環境：Revit，WPF，NPOI 2.5.6，.Net Framework 4.7.2

一、參照 NPOI

　　右擊專案參照，選擇 "管理NuGet程式包"，在瀏覽搜尋 NPOI ，選擇版本（我這裡是2.5.6），安裝

　　安裝成功後，參照裡會出現這四個參照（NPOI，NPOI.OOXML，NPOI.OpenXml4Net，NPOI.OpenXmlFormats）

　　在NuGet程式包裡會多出來這兩個（Portable.BouncyCastle，SharpZipLib）這是 NPOI 2.5.6 的依賴項；

　　2.6.0的依賴的項會多，同時擴充套件了很多方法。我的需求只是匯出Excel，所以這塊我選擇依賴項相對較少的 2.5.6 版本

二、實現匯出Excle方法

　　我這裡粗略的封裝了一個類，主要由以下幾個方法組成

　　建立表格（）實現，建立列名，建立行

　　設定單元格樣式（）在建立列名，建立行時會用到

　　獲取儲存檔案絕對路徑（）

　　儲存檔案（）

　　我這裡有兩個匯出方法，分別「匯出」，「自定義框架匯出」。

　　自定義框架匯出，大概邏輯就是，在外層，建立好頂部和底部，再呼叫匯出建立出紅色區域

　　匯出的效果就是，沒有前兩行的標題和副標題，也沒有最後一行的總計，就是紅色區域

using Microsoft.Win32;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FormWork.Draft.ViewModels
{
    public class FileHelper
    {
        /// <summary>
        /// Excel行高，Height的單位是1/20個點。例：設定高度為25個點
        /// </summary>
        private static short rowHeight = 25 * 20;

        #region 匯出Excel

        #region 匯出

        /// <summary>
        /// 匯出
        /// </summary>
        /// <param name="dt">資料來源</param>
        /// <param name="strHeader">列名</param>
        /// <param name="fileName">絕對路徑</param>
        /// <param name="sheetName">sheet頁名</param>
        public static void Export(DataTable dt, string strHeader, string fileName, string sheetName = "Sheet1")
        {
            // 使用 NPOI 元件匯出 Excel 檔案

            //XSSFWorkbook:是操作Excel2007的版本，擴充套件名是.xlsx
            XSSFWorkbook workbook = new XSSFWorkbook();

            //HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，擴充套件名是.xls
            //HSSFWorkbook workbook = new HSSFWorkbook();

            //建立Sheet
            ISheet sheet = workbook.CreateSheet(sheetName);

            //對列名拆分
            string[] strArry = strHeader.Split(',');

            //設定單元格樣式
            ICellStyle style = SetCellStyle(workbook);

            //建立表格
            NPOICreateTable(dt, strArry, sheet, style, 0);

            // 將 Excel 檔案儲存到磁碟
            NPOISaveFile(workbook, fileName);

            // 釋放資源
            //workbook.Dispose();
        }
        #endregion

        #region 自定義框架，匯出

        /// <summary>
        /// 自定義頂部，匯出
        /// </summary>
        /// <param name="dt">資料來源</param>
        /// <param name="strArry">拆分後列名</param>
        /// <param name="fileName">絕對路徑，路徑+檔名+字尾</param>
        /// <param name="num">新一行索引，開始</param>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        public static void Export(DataTable dt, string[] strArry, string fileName, int num, XSSFWorkbook workbook, ISheet sheet)
        {
            //設定單元格樣式
            ICellStyle style = SetCellStyle(workbook);

            //建立表格
            NPOICreateTable(dt, strArry, sheet, style, num);

            // 將 Excel 檔案儲存到磁碟
            NPOISaveFile(workbook, fileName);

            // 釋放資源
            //workbook.Dispose();
        }
        #endregion

        #endregion

        #region 獲取使用者選擇儲存路徑

        /// <summary>
        /// 獲取讓使用者選擇儲存檔案的絕對路徑
        /// </summary>
        /// <returns></returns>
        public static string GetSaveFileRoute(string filter, string fileName)
        {
            SaveFileDialog dialog = new SaveFileDialog();
            dialog.Filter = filter;
            dialog.FileName = fileName;
            if (dialog.ShowDialog() == true)
            {
                return dialog.FileName;
            }
            return "";
        }

        #endregion

        #region 儲存檔案

        private static string NPOISaveFile(XSSFWorkbook workbook, string fileName)
        {
            try
            {
                // 將 Excel 檔案儲存到磁碟
                using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(fs);
                }

                return "";
            }
            catch (Exception err)
            {
                return err.Message;
            }
        }

        #endregion

        #region 建立表格

        /// <summary>
        /// 建立表格
        /// </summary>
        /// <param name="dt">資料來源</param>
        /// <param name="strArry">拆分後的列名</param>
        /// <param name="sheet">Sheet頁</param>
        /// <param name="style">樣式</param>
        /// <param name="num">行索引</param>
        private static void NPOICreateTable(DataTable dt, string[] strArry, ISheet sheet, ICellStyle style, int num)
        {
            IRow row = null;//宣告行

            #region 建立列名

            //在索引 num 的位置 建立一行
            row = sheet.CreateRow(num);
            row.Height = rowHeight;//設定行高

            //迴圈列名陣列，建立單元格並賦值、樣式
            for (int i = 0; i < strArry.Length; i++)
            {
                row.CreateCell(i).SetCellValue(strArry[i]);
                row.GetCell(i).CellStyle = style;
            }

            #endregion

            #region 建立行

            //迴圈資料來源 建立行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //建立行
                row = sheet.CreateRow(num + 1);
                row.Height = rowHeight;//設定行高
                num++;//行索引自增

                //迴圈資料來源列集合，建立單元格
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string ValueType = "";//值型別
                    string Value = "";//值
                    //型別 和 值，賦值
                    if (dt.Rows[i][j].ToString() != null)
                    {
                        ValueType = dt.Rows[i][j].GetType().ToString();
                        Value = dt.Rows[i][j].ToString();
                    }
                    //根據不同資料型別，對資料處理。處理後建立單元格並賦值 和 樣式
                    switch (ValueType)
                    {
                        case "System.String"://字串型別
                            row.CreateCell(j).SetCellValue(Value);
                            break;
                        case "System.DateTime"://日期型別
                            System.DateTime dateV;
                            System.DateTime.TryParse(Value, out dateV);
                            row.CreateCell(j).SetCellValue(dateV.ToString("yyyy-MM-dd"));
                            break;
                        case "System.Boolean"://布林型
                            bool boolV = false;
                            bool.TryParse(Value, out boolV);
                            row.CreateCell(j).SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(Value, out intV);
                            row.CreateCell(j).SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮點型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(Value, out doubV);
                            row.CreateCell(j).SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值處理
                            row.CreateCell(j).SetCellValue("");
                            break;
                        default:
                            row.CreateCell(j).SetCellValue("");
                            break;
                    }
                    row.GetCell(j).CellStyle = style;
                    //設定寬度
                    //sheet.SetColumnWidth(j, (Value.Length + 10) * 256);
                }
            }

            #endregion

            //迴圈列名陣列，多所有列 設定 自動列寬
            for (int i = 0; i < strArry.Length; i++)
            {
                sheet.AutoSizeColumn(i);
            }
        }

        #endregion

        #region 設定單元格樣式

        public static ICellStyle SetCellStyle(XSSFWorkbook workbook)
        {
            #region 單元格樣式

            //建立一個樣式
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;//水平對齊
            style.VerticalAlignment = VerticalAlignment.Center;//垂直對齊

            style.BorderBottom = BorderStyle.Thin;//下邊框為細線邊框
            style.BorderLeft = BorderStyle.Thin;//左邊框
            style.BorderRight = BorderStyle.Thin;//上邊框
            style.BorderTop = BorderStyle.Thin;//右邊框

            #endregion

            return style;
        }

        #endregion
    }
}

View Code

三、呼叫匯出

　　OnExportCommand 此方法我是寫在 ViewModel 裡的。MVVM 模式，通過Command 繫結的事件。

　　這個方法就相當於是按鈕的 Click事件

        /// <summary>
        /// 匯出
        /// </summary>
        public void OnExportCommand()
        {
            try
            {
                //獲取 DataGrid 控制元件的 資料來源
                List<VisualList> visualLists = (List<VisualList>)view.gridVisualList.ItemsSource;

                //將 集合 轉換為 DaTable
                DataTable dt = visualLists.toDataTable();
                dt.Columns.Remove("Id");//刪除不需要匯出的列

                //獲取要將檔案儲存到 哪裡 的絕對路徑                       （儲存型別，預設檔名）
                string fileName = ViewModelHelper.GetSaveFileRoute("Excel檔案|*.xlsx", "視覺化清單.xlsx");
                if (fileName != "")
                {
                    ViewModelHelper.Export(dt, "序號,規格,型別,單個面積m²,數量,合計面積m²", fileName);

                    //自定義框架匯出↓↓↓
                    //Export(dt, "序號,規格,型別,單個面積m²,數量,合計面積m²", fileName);
                }
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message, "錯誤");
            }
        }


        /// <summary>
        /// 自定義框架，匯出
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="strHeader"></param>
        /// <param name="fileName"></param>
        /// <param name="sheetName"></param>
        public void Export(DataTable dt, string strHeader, string fileName, string sheetName = "Sheet1")
        {
            string[] strArry = strHeader.Split(',');

            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow row = null;
            ICellStyle style = null;
            IFont font = null;

            #region 第一行/標題

            //建立第一行
            row = sheet.CreateRow(0);
            row.Height = 25 * 20;

            //樣式
            style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            font = workbook.CreateFont();
            font.IsBold = true;
            font.FontHeightInPoints = 15;
            style.SetFont(font);

            //建立單元格，並賦 值 和 樣式
            row.CreateCell(0).SetCellValue("視覺化清單");
            row.GetCell(0).CellStyle = style;
            //合併單元格
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, strArry.Length - 1);
            sheet.AddMergedRegion(region);

            #endregion

            #region 第二行/副標題

            row = sheet.CreateRow(1);
            row.Height = 25 * 20;

            //樣式
            style = workbook.CreateCellStyle();
            style.VerticalAlignment = VerticalAlignment.Center;
            font = workbook.CreateFont();
            font.IsBold = true;
            style.SetFont(font);

            row.CreateCell(0).SetCellValue("工程名稱：專案名稱");
            row.GetCell(0).CellStyle = style;
            region = new CellRangeAddress(1, 1, 0, strArry.Length / 2 - 1);
            sheet.AddMergedRegion(region);

            row.CreateCell(strArry.Length / 2).SetCellValue("編號：專案編號");
            row.GetCell(strArry.Length / 2).CellStyle = style;
            region = new CellRangeAddress(1, 1, strArry.Length / 2, strArry.Length - 1);
            sheet.AddMergedRegion(region);

            #endregion


            #region 最後一行，總計

            row = sheet.CreateRow(2 + 1 + dt.Rows.Count);//標題副標題+列名+資料行數
            row.Height = 25 * 20;
            row.CreateCell(0).SetCellValue("總計：");

            int count = dt.Columns.IndexOf("Count");
            row.CreateCell(count).SetCellValue(SumAllCount);

            int area = dt.Columns.IndexOf("SumArea");
            row.CreateCell(area).SetCellValue(SumAllArea);

            for (int i = 0; i < strArry.Length; i++)
            {
                if (row.GetCell(i) == null)
                {
                    row.CreateCell(i);
                }
                row.GetCell(i).CellStyle = ViewModelHelper.SetCellStyle(workbook);
            }

            #endregion

            ViewModelHelper.Export(dt, strArry, fileName, 2, workbook, sheet);
        }

View Code

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace FormWork.Draft.Extension
{
    /// <summary>
    /// 資料轉換擴充套件類
    /// </summary>
    public static class DataConversionExtend
    {
        /// <summary>
        /// 轉DataTable
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="_tName"></param>
        /// <returns></returns>
        public static DataTable toDataTable(this object obj, string _tName = null)
        {
            Type t = obj.GetType();
            dynamic ts = obj;
            object tf = ts[0];

            PropertyInfo[] pi = tf.GetType().GetProperties(BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public);
            DataTable DT = new DataTable();
            DT.TableName = _tName == null ? tf.GetType().ToString() : _tName;
            foreach (PropertyInfo p in pi)
            {
                DT.Columns.Add(p.Name, p.PropertyType);
            }

            DataRow dr = null;
            foreach (var v in ts)
            {
                dr = DT.NewRow();
                foreach (PropertyInfo p in pi)
                {
                    dr[p.Name] = p.GetValue(v, null);
                }
                DT.Rows.Add(dr);
            }
            return DT;
        }
    }
}

List轉DataTable 擴充套件方法

四、Revit 啟用 WPF表單，WPF 匯出

　　在這裡我遇到個問題，就是

　　在Revit 命令類中，可以直接通過NPOI匯出到excel，但命令開啟WPF無法匯出會報錯

　　未能載入檔案或程式集"NPOI,Version=2.5.6.0,Culture=neutral,PublicKeyToken=0df73ec7942b34e1"或它的某一個依賴項，系統找不到指定的檔案

　　我目前尚未了解其真正原因，只是通過不斷測試，發現在 Execute 方法內匯出一次，便不會報錯

　　我現在的理解就是在Execute結束前不匯出一次，便不會載入NPOI程式集。未知其原理與原因。

　　如有遇到此問題的小夥伴，歡迎留言

        public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
        {
            try
            {
                baseClass = new CmdBaseClass(commandData);

                VisualListView view = VisualListView.Load(this);

                //視窗一直顯示在主程式之前
                System.Windows.Interop.WindowInteropHelper mainUI = new System.Windows.Interop.WindowInteropHelper(view);
                mainUI.Owner = System.Diagnostics.Process.GetCurrentProcess().MainWindowHandle;
                //開啟非模態檢視
                view.Show();

                #region NPOI/匯出

                //未能載入檔案或程式集"NPOI,Version=2.5.6.0,Culture=neutral,PublicKeyToken=0df73ec7942b34e1"或它的某一個依賴項，系統找不到指定的檔案
                //此句勿刪，在WPF中通過NPOI匯出excel，如果不在此匯出一次，就會報上面錯誤↑↑↑
                //在命令類中，可以直接通過NPOI匯出到excel，但命令開啟的WPF無法匯出 會報錯
                //目前尚未了解其真正原因，只是通過不斷測試，發現在 Execute 方法內 匯出一次，便不會報錯
                //在Execute結束前不匯出一次，便不會載入NPOI程式集
                Export();

                #endregion

                return Result.Succeeded;
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message, "錯誤");
                return Result.Failed;
            }
        }

        private void Export()
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet worksheet = workbook.CreateSheet("Sheet1");

            // 將 Excel 檔案儲存到磁碟
            string fileName = @"D:\視覺化清單" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            File.Delete(fileName);
        }

Revit

.Net NPOI Excel 匯出

NPOI 匯出 Excel

一、參照 NPOI

二、實現匯出Excle方法

三、呼叫匯出

四、Revit 啟用 WPF表單，WPF 匯出