using FluentEmail.Core;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using NPOI.HPSF;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using Ropin.Inspection.Api.Common;
using Ropin.Inspection.Api.Controllers;
using Ropin.Inspection.Common.Helper;
using Ropin.Inspection.Model;
using Ropin.Inspection.Model.Common;
using Ropin.Inspection.Model.ViewModel.DEV;
using Ropin.Inspection.Service;
using Ropin.Inspection.Service.DEV.Interface;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Drawing.Printing;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Runtime.ConstrainedExecution;
using System.Threading.Tasks;
namespace Ropin.Inspection.Api
{
public class TdevDevOpeAccountController : BaseController
{
public ILogger<TdevDevOpeAccountController> _logger { get; }
private readonly ITdevDevOpeAccountService _TdevDevOpeAccountService;
private readonly IDevDevOpeAccountConfigService _devDevOpeAccountConfigService;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="TdevDevOpeAccountService"></param>
/// <param name="logger"></param>
public TdevDevOpeAccountController(ITdevDevOpeAccountService TdevDevOpeAccountService, IDevDevOpeAccountConfigService devDevOpeAccountConfigService, ILogger<TdevDevOpeAccountController> logger)
{
_TdevDevOpeAccountService = TdevDevOpeAccountService;
_logger = logger;
_devDevOpeAccountConfigService = devDevOpeAccountConfigService;
}
/// <summary>
/// 通过id获取业主设备运行台账信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
[HttpGet("GetDevOpeAccountAsync/{id}")]
public async Task<ApiResult> GetDevOpeAccountAsync(string id)
{
if (string.IsNullOrEmpty(id))
{
return new ApiResult(ReturnCode.GeneralError);
}
try
{
var content = await _TdevDevOpeAccountService.GetConditionAsync(new TdevDevOpeAccountSearchModel { C_ID = id });
return new ApiResult<TdevDevOpeAccountViewModel>(content.FirstOrDefault());
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 获取所有业主设备运行台账
/// </summary>
/// <returns></returns>
[HttpGet("GetDevOpeAccountsAsync")]
public async Task<ApiResult> GetDevOpeAccountsAsync()
{
try
{
var contentList = await _TdevDevOpeAccountService.GetAllAsync();
return new ApiResult<IEnumerable<TdevDevOpeAccountViewModel>>(contentList);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 通过业主设备运行台账名称条件查询
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
[HttpPost("GetDevOpeAccountsByAsync")]
public async Task<ApiResult> GetDevOpeAccountsByAsync(TdevDevOpeAccountSearchModel searchModel)
{
if (searchModel == null)
{
return new ApiResult(ReturnCode.ArgsError);
}
searchModel.IsPagination = false;
try
{
var contentList = await _TdevDevOpeAccountService.GetConditionAsync(searchModel);
return new ApiResult<PagesModel<TdevDevOpeAccountViewModel>>(new PagesModel<TdevDevOpeAccountViewModel>(contentList, searchModel));
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 查询运行台账
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
[HttpPost("GetDevOpeAccountContentByAsync")]
[AllowAnonymous]
public async Task<ApiResult> GetDevOpeAccountContentByAsync(TdevDevOpeAccountSearchModel searchModel)
{
if (searchModel == null)
{
return new ApiResult(ReturnCode.ArgsError);
}
try
{
var devOpeAccountList = await _TdevDevOpeAccountService.GetConditionAsync(searchModel);
if (devOpeAccountList==null|| devOpeAccountList.Count()==0)
{
return new ApiResult<object>(new
{
tabName = new string[0],
pages = new PagesModel<object>(null, searchModel)
});
}
List<RunSpotConfigName> tabs = new List<RunSpotConfigName>();
PagesModel<object> pages = null;
var confCode = searchModel.C_DevOpeAccountConfigCode;
if (!string.IsNullOrEmpty(confCode))
{
tabs.Add(new RunSpotConfigName { Label = "日期", Name = "Date", BReadDevSpot=false });
var content = await _devDevOpeAccountConfigService.GetEntityByID(confCode);
if (content != null && !string.IsNullOrEmpty(content.C_Config))
{
var RunSpotConfig = JsonConvert.DeserializeObject<TdevDevOpeAccountConfigNameViewModel>(content.C_Config);
foreach (var item in RunSpotConfig?.RunSpotConfigList.ToList())
{
if (item!=null)
{
if (item.BReadDevSpot)
{
//string labVal = "";string labVal1 ="";
//if (item.Label.Contains('(')|| item.Label.Contains('（'))
//{
// labVal = item.Label.Replace("（","(").Replace("(","最大值（");
// labVal1 = item.Label.Replace("（", "(").Replace("(", "最小值（");
//}
//else
//{
// labVal = item.Label + "最大值";
// labVal1 = item.Label + "最小值";
//}
tabs.Add(new RunSpotConfigName { Label = item.Label + "@平均值", Name = item.Name, BReadDevSpot = item.BReadDevSpot });
tabs.Add(new RunSpotConfigName { Label = item.Label+ "@最大值", Name = item.Name+ "_Max", BReadDevSpot = item.BReadDevSpot });
tabs.Add(new RunSpotConfigName { Label = item.Label + "@最小值", Name = item.Name + "_Min", BReadDevSpot = item.BReadDevSpot });
}
else
{
tabs.Add(item);
}
}
}
}
}
var confData = devOpeAccountList.Select(t => t.C_Content).ToList();
List<object> list = new List<object>();
for (int i = 0; i < confData.Count; i++)
{
Dictionary<string, object> dict = JsonConvert.DeserializeObject<Dictionary<string, object>>(confData[i]);
if (dict.ContainsKey("RunWhetherNormal"))
{
var RunWhetherNormal = dict["RunWhetherNormal"].ToString();
dict["RunWhetherNormal"] = RunWhetherNormal.ToLower() == "true" ? "是" : "否";
}
var trueList = tabs?.Where(t => t.BReadDevSpot == true);
foreach (var item in trueList)
{
if (item!=null)
{
if (!dict.ContainsKey(item.Name))
{
dict.Add(item.Name, "0");
}
}
}
foreach (var item in dict)
{
if (item.Key?.ToString()== "Date")
{
dict[item.Key] = Convert.ToDateTime(item.Value).ToString("yyyy-MM-dd");
}
else
{
if (item.Value != null)
{
if (DateTime.TryParse(item.Value.ToString(), out DateTime dt) && item.Value.ToString().Length >= 10)
{
dict[item.Key] = dt.ToString("yyyy-MM-dd HH:mm:ss");
}
}
}
}
list.Add(dict);
}
pages = new PagesModel<object>(list, searchModel);
//var tabArray = tabs.Select(t => t.Label).ToArray();
List<DevOpeAccountConfigTableHeadeModel> tabList = new List<DevOpeAccountConfigTableHeadeModel>();
for (int i = 0; i < tabs.Count; i++)
{
var vals = tabs[i].Label.ToString();
var name = tabs[i].Name.ToString();
if (vals.Contains("@"))
{
string[] tmp = vals.Split('@');//按,分割字符串
var TableHeadeModel = tabList.Find(t => t.label == tmp[0].ToString());
if (TableHeadeModel!=null)
{
if (TableHeadeModel.children!=null&& TableHeadeModel.children.Count>0)
{
if (tmp.Count() == 2)
{
TableHeadeModel.children.Add(new DevOpeAccountConfigTableHeadeModel { prop = name, label = tmp[1].ToString(), Level = 2, children = null });
}
if (tmp.Count() == 3)
{
var childModel = TableHeadeModel.children.Find(x => x.label == tmp[1].ToString());
if (childModel!=null)
{
if (childModel.children==null)
{
childModel.children = new List<DevOpeAccountConfigTableHeadeModel>();
}
childModel.children.Add(new DevOpeAccountConfigTableHeadeModel { prop = name, label = tmp[2].ToString(), Level = 3, children = null });
}
else
{
List<DevOpeAccountConfigTableHeadeModel> chile = null;
chile = new List<DevOpeAccountConfigTableHeadeModel>();
chile.Add(new DevOpeAccountConfigTableHeadeModel { prop = name, label = tmp[2].ToString(), Level = 3, children = null });
TableHeadeModel.children.Add(new DevOpeAccountConfigTableHeadeModel { prop = null, label = tmp[1].ToString(), Level = 2, children = chile });
}
}
}
else
{
TableHeadeModel.children = new List<DevOpeAccountConfigTableHeadeModel>();
if (tmp.Count() == 2)
{
TableHeadeModel.children.Add(new DevOpeAccountConfigTableHeadeModel { prop = name, label = tmp[1].ToString(), Level = 2, children = null });
}
if (tmp.Count() == 3)
{
List<DevOpeAccountConfigTableHeadeModel> chile = null;
chile = new List<DevOpeAccountConfigTableHeadeModel>();
chile.Add(new DevOpeAccountConfigTableHeadeModel { prop = name, label = tmp[2].ToString(), Level = 3, children = null });
TableHeadeModel.children.Add(new DevOpeAccountConfigTableHeadeModel { prop = null, label = tmp[1].ToString(), Level = 2, children = chile });
}
}
}
else
{
var cc = new DevOpeAccountConfigTableHeadeModel { prop = null, label = tmp[0].ToString(),Level=1, children = new List<DevOpeAccountConfigTableHeadeModel>()};
if (tmp.Count() == 2)
{
cc.children.Add(new DevOpeAccountConfigTableHeadeModel { prop = name, label = tmp[1].ToString(), Level = 2, children = null });
}
if (tmp.Count()==3)
{
List<DevOpeAccountConfigTableHeadeModel> chile = null;
chile = new List<DevOpeAccountConfigTableHeadeModel>();
chile.Add(new DevOpeAccountConfigTableHeadeModel { prop = name, label = tmp[2].ToString(), Level = 3, children =null});
cc.children.Add(new DevOpeAccountConfigTableHeadeModel { prop = null, label = tmp[1].ToString(), Level = 2, children = chile });
}
tabList.Add(cc);
}
}
else
{
tabList.Add(new DevOpeAccountConfigTableHeadeModel { prop = name, label=vals, Level = 1, children = null});
}
}
var data = new
{
DevOpeAccountConfigCode= confCode,
tabName = tabs.Select(t => t.Label).ToArray(),
tabArray= tabList,
pages
};
return new ApiResult<object>(data);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 查询运行台账-导出
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
[HttpPost("GetDevOpeAccountContentExportByAsync")]
[AllowAnonymous]
public async Task<ApiResult> GetDevOpeAccountContentExportByAsync(TdevDevOpeAccountSearchModel searchModel)
{
if (searchModel == null)
{
return new ApiResult(ReturnCode.ArgsError);
}
try
{
var devOpeAccountList = await _TdevDevOpeAccountService.GetConditionAsync(searchModel);
if (devOpeAccountList == null || devOpeAccountList.Count() == 0)
{
return new ApiResult<object>(new
{
tabName = new string[0],
pages = new PagesModel<object>(null, searchModel)
});
}
List<RunSpotConfigName> tabs = new List<RunSpotConfigName>();
PagesModel<object> pages = null;
var confCode = searchModel.C_DevOpeAccountConfigCode;
if (!string.IsNullOrEmpty(confCode))
{
tabs.Add(new RunSpotConfigName { Label = "日期", Name = "Date" ,BReadDevSpot=false });
var content = await _devDevOpeAccountConfigService.GetEntityByID(confCode);
if (content != null && !string.IsNullOrEmpty(content.C_Config))
{
var RunSpotConfig = JsonConvert.DeserializeObject<TdevDevOpeAccountConfigNameViewModel>(content.C_Config);
foreach (var item in RunSpotConfig?.RunSpotConfigList.ToList())
{
if (item != null)
{
if (item.BReadDevSpot)
{
//string labVal = ""; string labVal1 = "";
//if (item.Label.Contains('(') || item.Label.Contains('（'))
//{
// labVal = item.Label.Replace("（", "(").Replace("(", "最大值（");
// labVal1 = item.Label.Replace("（", "(").Replace("(", "最小值（");
//}
//else
//{
// labVal = item.Label + "最大值";
// labVal1 = item.Label + "最小值";
//}
tabs.Add(new RunSpotConfigName { Label = item.Label + "@平均值", Name = item.Name, BReadDevSpot = item.BReadDevSpot });
tabs.Add(new RunSpotConfigName { Label = item.Label + "@最大值", Name = item.Name + "_Max", BReadDevSpot = item.BReadDevSpot });
tabs.Add(new RunSpotConfigName { Label = item.Label + "@最小值", Name = item.Name + "_Min", BReadDevSpot = item.BReadDevSpot });
}
else
{
tabs.Add(item);
}
}
}
}
}
var confData = devOpeAccountList.Select(t => t.C_Content).ToList();
List<object> list = new List<object>();
for (int i = 0; i < confData.Count; i++)
{
Dictionary<string, object> dict = JsonConvert.DeserializeObject<Dictionary<string, object>>(confData[i]);
string[] cels = new string[tabs.Count];
int forCount = dict.Count + 1;
if (tabs.Count() > dict.Count) { forCount = tabs.Count() + 1; }
for (int j = 0; j < forCount; j++)
{
if (j < tabs.Count)
{
string itemVal = "";
if (dict.ContainsKey(tabs[j].Name))
{
itemVal = dict[tabs[j].Name]?.ToString();
}
if (tabs[j].Name== "RunWhetherNormal")
{
cels[j] = itemVal?.ToLower() == "true" ? "是" : "否";
}else if (tabs[j].Name == "Date")
{
cels[j] = Convert.ToDateTime(itemVal).ToString("yyyy-MM-dd");
}
else
{
if (DateTime.TryParse(itemVal, out DateTime dt) && itemVal.Length >= 10)
{
cels[j] = dt.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
cels[j] = itemVal;
}
}
}
}
list.Add(cels);
}
pages = new PagesModel<object>(list, searchModel);
//var tabArray = tabs.Select(t => t.Label).ToArray();
List<DevOpeAccountConfigTableHeadeModel> tabList = new List<DevOpeAccountConfigTableHeadeModel>();
for (int i = 0; i < tabs.Count; i++)
{
var vals = tabs[i].Label.ToString();
var name = tabs[i].Name.ToString();
if (vals.Contains("@"))
{
string[] tmp = vals.Split('@');//按,分割字符串
var TableHeadeModel = tabList.Find(t => t.prop == tmp[0].ToString());
if (TableHeadeModel != null)
{
if (TableHeadeModel.children != null && TableHeadeModel.children.Count > 0)
{
TableHeadeModel.children.Add(new DevOpeAccountConfigTableHeadeModel { prop = tmp[1].ToString(), label = tmp[1].ToString(), Level = 2, children = null });
}
else
{
TableHeadeModel.children = new List<DevOpeAccountConfigTableHeadeModel>();
TableHeadeModel.children.Add(new DevOpeAccountConfigTableHeadeModel { prop = tmp[1].ToString(), label = tmp[1].ToString(), Level = 2, children = null });
}
}
else
{
var cc = new DevOpeAccountConfigTableHeadeModel { prop = tmp[0].ToString(), label = tmp[0].ToString(), Level = 1, children = new List<DevOpeAccountConfigTableHeadeModel>() };
cc.children.Add(new DevOpeAccountConfigTableHeadeModel { prop = tmp[1].ToString(), label = tmp[1].ToString(), Level = 2, children = null });
tabList.Add(cc);
}
}
else
{
tabList.Add(new DevOpeAccountConfigTableHeadeModel { prop = vals, label = vals, Level = 1, children = null });
}
}
var data = new
{
DevOpeAccountConfigCode = confCode,
tabName = tabs.Select(t => t.Label).ToArray(),
tabArray = tabList,
pages
};
return new ApiResult<object>(data);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 查询运行台账-【返回配置集合】
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
[HttpPost("GetDevOpeAccountConfingAsync")]
[AllowAnonymous]
public async Task<ApiResult> GetDevOpeAccountConfingAsync(TdevDevOpeAccountSearchModel searchModel)
{
if (searchModel == null)
{
return new ApiResult(ReturnCode.ArgsError);
}
try
{
PagesModel<object> pages = null;
List<DevOpeAccountConfigSelect> select = await _TdevDevOpeAccountService.GetDevOpeAccountConfigSelectAsync(searchModel);
pages = new PagesModel<object>(select, searchModel);
var data = new
{
pages
};
return new ApiResult<object>(data);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
}
/// <summary>
/// 创建业主设备运行台账
/// </summary>
/// <param name="content"></param>
/// <returns></returns>
[HttpPost("CreateDevOpeAccountAsync")]
public async Task<ApiResult> CreateDevOpeAccountAsync(TdevDevOpeAccountViewModel content)
{
if (content == null)
{
return new ApiResult(ReturnCode.ArgsError);
}
try
{
await _TdevDevOpeAccountService.CreateOneAsync(content);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
return new ApiResult(ReturnCode.Success);
}
/// <summary>
/// 删除业主设备运行台账
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
[HttpDelete("DeleteDevOpeAccountAsync/{id}")]
public async Task<ApiResult> DeleteDevOpeAccountAsync(string id)
{
if (string.IsNullOrEmpty(id))
{
return new ApiResult(ReturnCode.GeneralError);
}
try
{
await _TdevDevOpeAccountService.DeleteAsync(id);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
return new ApiResult(ReturnCode.Success);
}
/// <summary>
/// 更新业主设备运行台账
/// </summary>
/// <param name="id"></param>
/// <param name="updateModel"></param>
/// <returns></returns>
[HttpPut("UpdateDevOpeAccountAsync/{id}")]
public async Task<ApiResult> UpdateDevOpeAccountAsync(string id, TdevDevOpeAccountUpdateModel updateModel)
{
if (string.IsNullOrEmpty(id))
{
return new ApiResult(ReturnCode.GeneralError);
}
try
{
await _TdevDevOpeAccountService.UpdateAsync(id, updateModel);
}
catch (Exception ex)
{
return new ApiResult(ReturnCode.GeneralError, ex.Message);
}
return new ApiResult(ReturnCode.Success);
}
/// <summary>
/// 发送邮件(附件为台帐)
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
[HttpPost("SendDevOpeAccountToEmail")]
[AllowAnonymous]
public async Task SendDevOpeAccountToEmail(SendDevOpeAccountToEmailModel searchModel)
{
if (searchModel == null)
{
return;
}
try
{
var devOpeAccountList = await _TdevDevOpeAccountService.GetConditionAsync(searchModel);
var contentList = from c in devOpeAccountList.Select(t => t.C_Content)
select JsonConvert.DeserializeObject<TdevDevOpeContentViewModel>(c);
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 12));
var titleRow = sheet.CreateRow(0);
titleRow.Height = 20 * 25;
ICell titleCell = titleRow.CreateCell(0);
titleCell.SetCellValue("设各台账记录");
//第一行字体样式
IFont font = workbook.CreateFont();
font.IsBold = true;
font.FontHeightInPoints = 16;
font.FontName = "宋体";
ICellStyle titleCellStyle = workbook.CreateCellStyle();
titleCellStyle.SetFont(font);
titleCellStyle.Alignment = HorizontalAlignment.Center; //字体居中
//边框
titleCellStyle.BorderBottom = BorderStyle.Thin;
titleCellStyle.BorderLeft = BorderStyle.Thin;
titleCellStyle.BorderRight = BorderStyle.Thin;
titleCellStyle.BorderTop = BorderStyle.Thin;
titleCell.CellStyle = titleCellStyle;
var headRow = sheet.CreateRow(1);
//headRow.CreateCell(0).SetCellValue("序 号");
headRow.CreateCell(0).SetCellValue("日期");
headRow.CreateCell(1).SetCellValue("排污口编号");
headRow.CreateCell(2).SetCellValue("设备名称");
headRow.CreateCell(3).SetCellValue("参数名称");
headRow.CreateCell(4).SetCellValue("设计值");
headRow.CreateCell(5).SetCellValue("单位");
headRow.CreateCell(6).SetCellValue("污染因子");
headRow.CreateCell(7).SetCellValue("数据来源");
headRow.CreateCell(8).SetCellValue("烟气筒高度(m)");
headRow.CreateCell(9).SetCellValue("排放温度(°C)");
headRow.CreateCell(10).SetCellValue("压力");
headRow.CreateCell(11).SetCellValue("开机时间");
headRow.CreateCell(12).SetCellValue("关机时间");
//第二行,列名
IFont font1 = workbook.CreateFont();
font1.IsBold = true;
font1.FontHeightInPoints = 12;
font1.FontName = "宋体";
ICellStyle headCellStyle = workbook.CreateCellStyle();
headCellStyle.SetFont(font1);
//边框
headCellStyle.BorderBottom = BorderStyle.Thin;
headCellStyle.BorderLeft = BorderStyle.Thin;
headCellStyle.BorderRight = BorderStyle.Thin;
headCellStyle.BorderTop = BorderStyle.Thin;
foreach (var item in headRow.Cells)
{
item.CellStyle = headCellStyle;
}
int start = 2;
IFont font3 = workbook.CreateFont();
font3.FontHeightInPoints = 9;
font3.FontName = "宋体";
ICellStyle contentCellStyle = workbook.CreateCellStyle();
contentCellStyle.SetFont(font3);
//边框
contentCellStyle.BorderBottom = BorderStyle.Thin;
contentCellStyle.BorderLeft = BorderStyle.Thin;
contentCellStyle.BorderRight = BorderStyle.Thin;
contentCellStyle.BorderTop = BorderStyle.Thin;
foreach (var item in contentList)
{
var row = sheet.CreateRow(start);
row.CreateCell(0).SetCellValue(item.Date.ToString("yyyy-MM-dd hh:mm:ss"));
row.CreateCell(1).SetCellValue(item.DrainOutletNumber);
row.CreateCell(2).SetCellValue(item.DevStoreName);
row.CreateCell(3).SetCellValue(item.DevStoreType);
row.CreateCell(4).SetCellValue(item.SpecificationsParameterName);
row.CreateCell(5).SetCellValue(item.SpecificationsDesignValue);
row.CreateCell(6).SetCellValue(item.PollutionFactor);
row.CreateCell(7).SetCellValue(item.DataSources);
row.CreateCell(8).SetCellValue(item.HeightOfFlue);
row.CreateCell(9).SetCellValue(item.DischargeTemperature);
row.CreateCell(10).SetCellValue(item.Pressure);
row.CreateCell(11).SetCellValue(item.RunStartTime.ToString("yyyy-MM-dd hh:mm:ss"));
row.CreateCell(12).SetCellValue(item.RunEndTime.ToString("yyyy-MM-dd hh:mm:ss"));
start++;
foreach (var cell in row.Cells)
{
cell.CellStyle = contentCellStyle;
}
}
// 自适应单元格
for (int i = 0; i < sheet.LastRowNum; i++)
{
sheet.AutoSizeRow(i);
}
for (int i = 0; i < 12; i++)
{
sheet.AutoSizeColumn(i, true);
}
using (var stream = new NpoiMemoryStream())
{
workbook.Write(stream);
stream.Seek(0, SeekOrigin.Begin);
string emailName = $"{searchModel.DevName} 设备台账记录报表 {searchModel.D_Start?.ToString("yyyy-MM-dd HH:mm:ss")}至{searchModel.D_End?.ToString("yyyy-MM-dd HH:mm:ss")}";
EmailHelper.SendEmail(searchModel.Mails, emailName, "", "报表见附件", $"{emailName}.xlsx", "application/vnd.ms-excel", stream);
}
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 查询运行台账-导出-Excel
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
[HttpPost("DevOpeAccountContentExportExcel")]
[AllowAnonymous]
public async Task<IActionResult> DevOpeAccountContentExportExcel(TdevDevOpeAccountSearchModel searchModel)
{
try
{
var devOpeAccountList = await _TdevDevOpeAccountService.GetConditionAsync(searchModel);
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region
//标题加粗居中.
IFont font = workbook.CreateFont();
font.IsBold = true;
font.FontHeightInPoints = 25;
font.FontName = "宋体";
ICellStyle titleCellStyle = workbook.CreateCellStyle();
titleCellStyle.SetFont(font);
titleCellStyle.Alignment = HorizontalAlignment.Center; //字体居中
//边框
titleCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
titleCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
titleCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
titleCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
titleCellStyle.WrapText = true;
#endregion
#region
//标题加粗居中.
IFont font1 = workbook.CreateFont();
font1.IsBold = true;
font1.FontHeightInPoints = 14;
font1.FontName = "宋体";
ICellStyle headCellStyle = workbook.CreateCellStyle();
headCellStyle.SetFont(font1);
headCellStyle.Alignment = HorizontalAlignment.Center; //字体左右居中
headCellStyle.VerticalAlignment = VerticalAlignment.Center;//字体上下居中
//边框
headCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
headCellStyle.WrapText = true;
#endregion
#region
//标题加粗居中.
IFont font2 = workbook.CreateFont();
font2.IsBold = false;
font2.FontHeightInPoints = 12;
font2.FontName = "宋体";
ICellStyle rowCellStyle = workbook.CreateCellStyle();
rowCellStyle.SetFont(font2);
rowCellStyle.Alignment = HorizontalAlignment.Center; //字体居中
//边框
rowCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
rowCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
rowCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
rowCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
rowCellStyle.WrapText = true;
#endregion
IRow title = sheet.CreateRow(0);
title.Height = 30 * 25;
title.CreateCell(0).SetCellValue("VOCs治理设施基本信息与维保管理信息表");
foreach (var item in title.Cells)
{
item.CellStyle = titleCellStyle;
}
if (devOpeAccountList == null || devOpeAccountList.Count() == 0)
{
IRow content1 = sheet.CreateRow(1);
content1.CreateCell(0).SetCellValue("暂无数据");
content1.Cells[0].CellStyle = headCellStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 15));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 15));
}
else
{
List<RunSpotConfigName> tabs = new List<RunSpotConfigName>();
var confCode = searchModel.C_DevOpeAccountConfigCode;
IRow head = sheet.CreateRow(1);
IRow head2 = sheet.CreateRow(2);
IRow head3 = sheet.CreateRow(3);
head.Height = 30 * 20;
head2.Height = 30 * 20;
head3.Height = 30 * 20;
if (!string.IsNullOrEmpty(confCode))
{
head.CreateCell(0).SetCellValue("日期");
tabs.Add(new RunSpotConfigName { Label = "日期", Name = "Date",BReadDevSpot=false });
sheet.AddMergedRegion(new CellRangeAddress(1, 3, 0, 0));
int cel = 1;
var content = await _devDevOpeAccountConfigService.GetEntityByID(confCode);
if (content != null && !string.IsNullOrEmpty(content.C_Config))
{
var RunSpotConfig = JsonConvert.DeserializeObject<TdevDevOpeAccountConfigNameViewModel>(content.C_Config);
List<RunSpotConfigName> CtabColumns = new List<RunSpotConfigName>();
int headerNum = 1;
foreach (var item in RunSpotConfig?.RunSpotConfigList.ToList())
{
if (item != null)
{
headerNum= item.Label.Split("@").Count();
if (item.BReadDevSpot)
{
headerNum++;
tabs.Add(new RunSpotConfigName { Label = item.Label + "@平均值", Name = item.Name, BReadDevSpot = item.BReadDevSpot });
tabs.Add(new RunSpotConfigName { Label = item.Label + "@最大值", Name = item.Name + "_Max", BReadDevSpot = item.BReadDevSpot });
tabs.Add(new RunSpotConfigName { Label = item.Label + "@最小值", Name = item.Name + "_Min", BReadDevSpot = item.BReadDevSpot });
CtabColumns.Add(new RunSpotConfigName { Label = item.Label + "@平均值", Name = item.Name, BReadDevSpot = item.BReadDevSpot });
CtabColumns.Add(new RunSpotConfigName { Label = item.Label + "@最大值", Name = item.Name + "_Max", BReadDevSpot = item.BReadDevSpot });
CtabColumns.Add(new RunSpotConfigName { Label = item.Label + "@最小值", Name = item.Name + "_Min", BReadDevSpot = item.BReadDevSpot });
}
else
{
tabs.Add(item);
CtabColumns.Add(item);
}
}
}
bool IsVal = false;int merge = 0;
bool IsVal2 = false; int merge2 = 0;
foreach (var tab in CtabColumns)
{
if (tab != null)
{
var vals = tab.Label;
if (vals.Contains("@"))
{
string[] tmp = vals.Split('@');//按,分割字符串
if (tmp.Count()==3)
{
var getCel = sheet.GetRow(1).GetCell(cel - 1 - merge).ToString();
var getCel2 = sheet.GetRow(2).GetCell(cel - 1 - merge2).ToString();
if (getCel == tmp[0].ToString())
{
head.CreateCell(cel).SetCellValue("");
merge++;
IsVal = true;
}
else
{
IsVal = false;
head.CreateCell(cel).SetCellValue(tmp[0].ToString());
}
if (getCel2 == tmp[1].ToString())
{
head2.CreateCell(cel).SetCellValue("");
merge2++;
IsVal2 = true;
}
else
{
head2.CreateCell(cel).SetCellValue(tmp[1].ToString());
IsVal2 = false;
}
head3.CreateCell(cel).SetCellValue(tmp[2].ToString());
}
else
{
IsVal2 = false;
var getCel = sheet.GetRow(1).GetCell(cel - 1 - merge).ToString();
if (getCel == tmp[0].ToString())
{
head.CreateCell(cel).SetCellValue("");
merge++;
IsVal = true;
}
else
{
IsVal = false;
head.CreateCell(cel).SetCellValue(tmp[0].ToString());
}
head2.CreateCell(cel).SetCellValue(tmp[1].ToString());
head3.CreateCell(cel).SetCellValue("");
sheet.AddMergedRegion(new CellRangeAddress(2, 3, cel, cel));
}
}
else
{
IsVal = false;
IsVal2 = false;
head.CreateCell(cel).SetCellValue(vals);
head2.CreateCell(cel).SetCellValue("");
head3.CreateCell(cel).SetCellValue("");
sheet.AddMergedRegion(new CellRangeAddress(1, 3, cel, cel));
}
if (!IsVal && merge > 0)
{
sheet.AddMergedRegion(new CellRangeAddress(1, 1, cel - merge - 1, cel - 1));
merge = 0;
}
if (!IsVal2 && merge2 > 0)
{
sheet.AddMergedRegion(new CellRangeAddress(2, 2, cel - merge2 - 1, cel - 1));
merge2 = 0;
}
cel++;
}
}
if (cel== CtabColumns.Count+1&& merge>0&& IsVal)
{
sheet.AddMergedRegion(new CellRangeAddress(1, 1, cel - merge - 1, cel - 1));
merge = 0; IsVal = false;
}
if (cel == CtabColumns.Count + 1 && merge2 > 0 && IsVal2)
{
sheet.AddMergedRegion(new CellRangeAddress(1, 2, cel - merge2 - 1, cel - 1));
merge2 = 0; IsVal2 = false;
}
}
}
foreach (var item in head.Cells)
{
item.CellStyle = headCellStyle;
}
foreach (var item in head2.Cells)
{
item.CellStyle = headCellStyle;
}
foreach (var item in head3.Cells)
{
item.CellStyle = headCellStyle;
}
int rowNumber = 4;
var confData = devOpeAccountList.Select(t => t.C_Content).ToList();
for (int i = 0; i < confData.Count; i++)
{
IRow content = sheet.CreateRow(rowNumber);
Dictionary<string, object> dict = JsonConvert.DeserializeObject<Dictionary<string, object>>(confData[i]);
int forCount = dict.Count + 1;
if (tabs.Count() > dict.Count) { forCount = tabs.Count() + 1; }
for (int j = 0; j < forCount; j++)
{
if (j < tabs.Count)
{
string celVal = "";
if (dict.ContainsKey(tabs[j].Name))
{
celVal = dict[tabs[j].Name]?.ToString();
}
if (tabs[j].Name == "RunWhetherNormal")
{
celVal = celVal.ToLower() == "true" ? "是" : "否";
content.CreateCell(j).SetCellValue(celVal);
}else if (tabs[j].Name == "Date")
{
content.CreateCell(j).SetCellValue(Convert.ToDateTime(celVal).ToString("yyyy-MM-dd"));
}
else if(DateTime.TryParse(celVal, out DateTime dt) && celVal.Length >= 10)
{
content.CreateCell(j).SetCellValue(dt.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
content.CreateCell(j).SetCellValue(celVal);
}
}
}
foreach (var item in content.Cells)
{
item.CellStyle = rowCellStyle;
}
rowNumber++;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, tabs.Count-1));
for (int i = 0; i <= tabs.Count; i++)
{
sheet.SetColumnWidth(i, 5000);
}
}
using (MemoryStream stream = new MemoryStream())
{
workbook.Write(stream);
byte[] data = stream.ToArray();
return File(data, "application/vnd.ms-excel", "设备运行台账.xlsx");
}
}
catch (Exception ex)
{
throw;
}
}
}
}