NPOI插件使用导出Excel文件-.Net-代码碎片记录

ExportExcelPLCData.cs

packages.config插件引入下载

ExportExcelPLCData.cs

using CIMC.DBUtility;
using Emrys.FlashLog;
using HslControls;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using SatelliteC.Views;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Drawing.Imaging;
using System.Drawing;
using System.IO;
using System.Linq;

namespace SatelliteC.ExportExcel
{
    internal class ExportExcelPLCData
    {
        public string sTime; // 外部赋值
        public string eTime; // 外部赋值
        public int total = 0; // 外部赋值
        public string fileName = "历史数据"; // 文件头的名称,下面代码有拼接可自定义,格式：历史数据-20240716-112130.xlsx
        // 导出保存的文件路径 - App.config配置的，便于打包后调整位置
        static string path = ConfigurationManager.AppSettings["saveExcelPath"].ToString();

        public void Export() {
            try {
// 分页列表需要按查询条件获取总数据，如果列表展示的是总数居不需要次操作
                string sql = $"SELECT * FROM plc_data WHERE create_time BETWEEN '{sTime} 00:00:00' AND '{eTime} 23:59:59' ORDER BY create_time DESC limit {total}";
                Console.WriteLine($"【SQL】{sql}");
                DataSet dataSet = DbHelperMySQL.Query(sql);
                IList<PLCData> list = ModelConvertHelper<PLCData>.ConvertToModel(dataSet.Tables[0]);

                ExportFile(list.ToList());
            }
            catch (Exception ex)
            {
                Console.WriteLine("ExportPLCData - error:" + ex.ToString());
                FlashLogger.Debug("ExportPLCData - error:" + ex.ToString());
            }
        }
        public void ExportFile(List<PLCData> list)
        {
            try
            {
                DateTime beforDT = DateTime.Now;
                IWorkbook excelDoc = new XSSFWorkbook();

                ISheet excelSheet = excelDoc.CreateSheet("Sheet1");

                // 单元格样式------------------------------------
                ICellStyle style = excelDoc.CreateCellStyle();
                style.FillForegroundColor = IndexedColors.RoyalBlue.Index;
                style.FillPattern = FillPattern.SolidForeground;
                // ----------------------------------------------

                // 表头------------------------------------------
                IRow excelRow = excelSheet.CreateRow(0);
                // 应用样式到行
                excelRow.RowStyle = style;
                string[] headCellsName = new string[] { "设备号", "标题1", "标题2", "标题3", "标题4" "创建日期" };
                for (int i = 0; i < headCellsName.Length; i++)
                {
                    excelRow.CreateCell(i).SetCellValue(headCellsName[i]);
                };
                // ----------------------------------------------

                // 表头跨行合并----------------------------------
                // CellRangeAddress(跨行起始, 跨行结束, 跨列起始, 跨列结束)
               /* var region = new CellRangeAddress(0, 0, 0, 0);
                excelSheet.AddMergedRegion(region);*/
                // ----------------------------------------------

                // 设置第一行为固定行----------------------------
                excelSheet.CreateFreezePane(0, 1, 0, 1);
                // ----------------------------------------------

                // 设置单元格宽度，5为对应列下标位置--------------------------------
                excelSheet.SetColumnWidth(5, 25 * 256);
                // ----------------------------------------------

                // 内容------------------------------------------
                // 展示信息过滤器 - 存在状态转文本内容的自定义
                //Filters filters = new Filters();
                for (int i = 0; i < list.Count; i++)
                {
                    PLCData PLCData = list[i];
                    IRow IRow = excelSheet.CreateRow(i + 1);
                    IRow.CreateCell(0).SetCellValue(PLCData.device_no);                      
                    IRow.CreateCell(1).SetCellValue(PLCData.h_l.ToString());                 
                    IRow.CreateCell(2).SetCellValue(PLCData.h_t.ToString());                 
                    IRow.CreateCell(3).SetCellValue(PLCData.h_o_ratio.ToString());           
                    IRow.CreateCell(4).SetCellValue(PLCData.h_leak_detection.ToString());            
                    IRow.CreateCell(5).SetCellValue(PLCData.create_time.ToString());                          
                };
                // ----------------------------------------------

                string name = $"{fileName}-{DateTime.Now.ToString("yyyyMMdd-HHmmss")}.xlsx";
                // 使用CreateDirectory方法创建文件夹 - 文件夹不存在会自动先创建
                if (!Directory.Exists(path)) Directory.CreateDirectory(path);

                using (FileStream file = new FileStream($"{path}\\{name}", FileMode.Create, FileAccess.Write))
                {
                    excelDoc.Write(file);
                    Console.WriteLine($"ExportPLCData: 导出文件成功{path}\\{name}");
                    FlashLogger.Info($"ExportPLCData: 导出文件成功{path}\\{name}");
                    DateTime afterDT = System.DateTime.Now;
                    TimeSpan ts = afterDT.Subtract(beforDT);
                    Console.WriteLine($"导出文件{total}条数据总共花费{ts.TotalMilliseconds}ms.");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("ExportPLCData - error:" + ex.ToString());
                FlashLogger.Error("ExportPLCData - error:" + ex.ToString());
            }
        }
    }
}

App.config配置的文件保存路径

<!--导出历史列表Excel文件保存地址-->
<add key="saveExcelPath" value="C:\\Users\\cimc\\Desktop\\exportExcel" />