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" />