.Net NPOI Excel 导出

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
    }
}

 

三、调用导出

　　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, "序号,规格,类型,单个面积m2,数量,合计面积m2", fileName);

                    //自定义框架导出↓↓↓
                    //Export(dt, "序号,规格,类型,单个面积m2,数量,合计面积m2", 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);
        }

 

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;
        }
    }
}

 

四、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);
        }