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