SIMDP_FEM/SIMDP/SIMDP.Project/ExcelExporter.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SIMDP.Model;
using ProjectBase.Data.Logs;

namespace SIMDP.Project
{
    public class ExcelExporter
    {
        string modelPath = Environment.CurrentDirectory + "\\model.xlsx";
        string generatePath = System.Configuration.ConfigurationManager.AppSettings["export_path"];
        int linetoWrite;
        string dateTime;
        XSSFWorkbook xsWorkBook;
        FileStream filetoWrite;
        public ExcelExporter(string dateTime)
        {
            linetoWrite = 0;
            this.dateTime = dateTime;
        }

        public bool LoadFile()
        {
            if (!File.Exists(modelPath))
            {
                LogHelper.log.Debug("LoadFile：模板文件丢失");
                LogAndNotice("模板文件丢失", 3, $"无法找到{modelPath}");
                return false;
            }
            if (!Directory.Exists(generatePath))
            {
                Directory.CreateDirectory(generatePath);
            }

            try
            {
                using (FileStream fsModel = new FileStream(modelPath, FileMode.Open))
                {
                    xsWorkBook = new XSSFWorkbook(fsModel);
                }
                generatePath += ("\\" + dateTime + ".xlsx");
                if (!File.Exists(generatePath))
                {
                    filetoWrite = new FileStream(generatePath, FileMode.Create, FileAccess.ReadWrite, FileShare.None);
                    LogHelper.log.Debug("LoadFile：新建当日Excel文件： " + dateTime + ".xlsx");
                }
                else//已经存在的 获取行号
                {
                    using (FileStream fsExist = File.OpenRead(generatePath))
                    {
                        xsWorkBook = new XSSFWorkbook(fsExist);
                    }
                    filetoWrite = new FileStream(generatePath, FileMode.Open,FileAccess.Write);
                    LogHelper.log.Debug("LoadFile：打开当日Excel文件，待写入行号： " + linetoWrite);
                }
                return true;
            }
            catch (Exception ex)
            {
                LogAndNotice("初始化Excel错误", 3, ex.Message);
                LogHelper.log.Debug("LoadFile：错误" + ex.Message);
                return false;
            }
        }

        public void ExitFile()
        {
            filetoWrite?.Close();
            filetoWrite?.Dispose();
        }

        public bool WriteLine(MoProductData moProductData)
        {
            try
            {
                XSSFSheet unionSheet = (XSSFSheet)xsWorkBook.GetSheetAt(0);
                linetoWrite = unionSheet.LastRowNum + 1;
                xsWorkBook.SetSheetHidden(0, false);
                xsWorkBook.SetActiveSheet(0);
                unionSheet.CreateRow(linetoWrite).CreateCell(0).SetCellValue(moProductData.RuleTime.ToString("yyyyMMdd HH:mm:ss"));//一定用HH大写显示24进制
                unionSheet.GetRow(linetoWrite).CreateCell(1).SetCellValue(Convert.ToDouble(moProductData.Batchid));
                string[] values = moProductData.DataValue.Split(',');
                for (int i = 2; i < values.Count() + 1; i++)
                {
                    bool isNum = double.TryParse(values[i - 2].ToString(), out double res);//判断是否数值型
                    if (isNum) unionSheet.GetRow(linetoWrite).CreateCell(i).SetCellValue(res);
                    else unionSheet.GetRow(linetoWrite).CreateCell(i).SetCellValue(values[i - 2].ToString());
                }

                ICell xssfCell1 = unionSheet.GetRow(linetoWrite).CreateCell(9);
                xssfCell1.SetCellValue(values[7].ToString());
                XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);//File也可
                link.Address = "file:///" + values[7].ToString().Replace("\\","/");// @"file:///C:/Log/excelExport";
                xssfCell1.Hyperlink = link; 

                xsWorkBook.Write(filetoWrite);
                LogHelper.log.Debug("ExcelExporter.WriteLine：写入Excel文件，第 " + linetoWrite + " 行 [" + moProductData.RuleTime + "]");
                return true;
            }
            catch (Exception ex)
            {
                LogAndNotice("写入Excel错误", 3, ex.Message);
                LogHelper.log.Debug("ExcelExporter.WriteLine：写入Excel文件错误" + ex.Message);
                return false;
            }
        }


        public static void LogAndNotice(string title, int type, string content)
        {
            if (type == 3)
                LogHelper.log.Error($"{title}：{content}");
            else if (type == 2)
                LogHelper.log.Warn($"{title}：{content}");
            else if (type == 1)
                LogHelper.log.Info($"{title}：{content}");
            ProjectBase.Data.BaseDAL.BLLFactory<BLL.BlSystemNotice>.Instance.PublishSysMessage(title, type, content);
        }


    }
}