public List <ESubmit> GetSubmitOnDate(DateTime submitDate, ECheckAreaDef area_id)
{
try
{
string date = submitDate.ToString("hh:mm tt, dd/MM/yy");
return(DataContext.ESubmit
.Include("ECheckingDailys")
.Where(i => i.AreaId == (int)area_id && i.UpdateTime == date)
.ToList());
}
catch { return(null); }
}
public EChecking(DataContext dataContext, ECheckItemDef itemcode, ECheckAreaDef areacode)
{
try
{
var item = dataContext.ECheckItem.Where(i => i.ECheckItemCode == (int)itemcode).First();
var area = dataContext.ECheckArea.Where(i => i.AreaCode == (int)areacode).First();
ECheckItemId = item.id;
ECheckItemCode = item.ECheckItemCode;
ECheckAreaCode = area.AreaCode;
ECheckAreaId = area.id;
}
catch { }
}
public string ExportToExcel(ExcelPackage excelPackage, DateTime Date, ECheckAreaDef area)
{
try
{
string update_log = string.Empty;
var submits = Data_Services.GetSubmitOnDate(Date, area);
string areaNotes = string.Empty;
foreach (var item in submits)
{
bool excp = UpdateToExcel(item, excelPackage, ref areaNotes);
string update_excp = excp ? "Successfull" : "Fail";
update_log += $"Update submit: [area id: {item.AreaId}], " +
$"[person id:{item.PersonId}], [date: {item.UpdateTime}], [Update: {update_excp}]\r\n";
}
return(update_log);
}
catch
{
return("Exception throw when export data to excel file\r\n");
}
}
public ECheckArea(ECheckAreaDef code, string name)
{
AreaCode = (int)code;
AreaName = name;
}
private static void CreateNewSheet(ECheckAreaDef area, ExcelPackage excelPackage)
{
string sheetName = "";
string sheetHeader = "";
string sheetDate = $"Ngày: {DateTime.Now.ToString("dd/ MM/ yyyy")}";
List <ECheckItemDef> CheckItem = new List <ECheckItemDef>();
switch (area)
{
case ECheckAreaDef.ROOM_31:
sheetName = $"PM 3.1";
sheetHeader = @"CHECKLIST KIỂM TRA PHÒNG MÁY HKH9103-1";
CheckItem.AddRange(ECheckingItem.Room_31_32);
break;
case ECheckAreaDef.ROOM_32:
sheetName = $"PM 3.2";
sheetHeader = @"CHECKLIST KIỂM TRA PHÒNG MÁY HKH9103-2";
CheckItem.AddRange(ECheckingItem.Room_31_32);
break;
case ECheckAreaDef.POWER_ROOM_T3:
sheetName = $"P.Nguon T3";
sheetHeader = @"CHECKLIST KIỂM TRA PHÒNG NGUỒN + PHÒNG ACCU TẦNG 3";
CheckItem.AddRange(ECheckingItem.PowerRoomT3);
break;
case ECheckAreaDef.POWER_ROOM_T1:
sheetName = $"P.Nguon T1";
sheetHeader = @"CHECKLIST KIỂM TRA PHÒNG NGUỒN TẦNG 1";
CheckItem.AddRange(ECheckingItem.PowerRoomT1);
break;
case ECheckAreaDef.GENERATOR:
sheetName = $"MPĐ";
sheetHeader = @"CHECKLIST KIỂM TRA MÁY PHÁT ĐIỆN";
CheckItem.AddRange(ECheckingItem.Generator);
break;
case ECheckAreaDef.L_H_VOLTAGE_TRANSFORMER:
sheetName = $"Day nha TBA";
sheetHeader = @"CHECKLIST KIỂM TRA DÃY NHÀ TRẠM BIẾN ÁP";
CheckItem.AddRange(ECheckingItem.LHVoltage);
break;
case ECheckAreaDef.LAB_ROOM:
sheetName = $"Phong Lab";
sheetHeader = @"CHECKLIST KIỂM TRA PHÒNG LAB VÀ PHÒNG ACCU ";
CheckItem.AddRange(ECheckingItem.LabRoom);
break;
default:
break;
}
var sheet = excelPackage.Workbook.Worksheets.Add(sheetName);
// setup column width
sheet.Column(1).Width = 46;
sheet.Column(1).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
//var namedStyle = excelPackage.Workbook.Styles.CreateNamedStyle("HyperLink");
//namedStyle.Style.Font.Name = "Times New Roman";
//namedStyle.Style.Font.Color.SetColor(Color.Blue);
int headersize = 20;
int titlesize = 13;
int textsize = 14;
ExcelRange header = sheet.Cells["A1:Y1"];
ExcelRange contentHeader = sheet.Cells["A2:A4"];
ExcelRange TimeHeader = sheet.Cells["B2:Y2"];
header.Merge = true;
contentHeader.Merge = true;
TimeHeader.Merge = true;
sheet.Row(1).Height = 45;
sheet.Row(2).Height = 25;
sheet.Row(3).Height = 25;
sheet.Row(4).Height = 25;
header.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
contentHeader.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
header.Value = sheetHeader;
contentHeader.Value = "Nội dung kiểm tra";
TimeHeader.Value = sheetDate;
header.Style.Font.Size = headersize;
TimeHeader.Style.Font.Size = titlesize;
contentHeader.Style.Font.Size = titlesize;
header.Style.Font.Bold = true;
TimeHeader.Style.Font.Bold = true;
contentHeader.Style.Font.Bold = true;
//
TimeHeader.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
header.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
contentHeader.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
//
TimeHeader.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
List <string> ItemList = new List <string>();
using (var datacontext = new DataContext())
{
foreach (var item in CheckItem)
{
var CItem = datacontext.ECheckItem.Where(i => i.ECheckItemCode == (int)item).First();
ItemList.Add(CItem.CheckName);
}
}
int ContinueRow = 0;
for (int i = 0; i < ItemList.Count; i++)
{
sheet.Row(i + 5).Height = 45;
var cell = sheet.Cells[i + 5, 1];
cell.Value = ItemList[i];
cell.Style.WrapText = true;
cell.Style.Font.Size = 14;
sheet.Cells[i + 5, 1, i + 5, 25].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
ContinueRow = i + 5;
}
ContinueRow++;
sheet.Cells[$"A{ContinueRow}"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[$"A{ContinueRow}"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
sheet.Cells[$"A{ContinueRow + 1}"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
sheet.Cells[$"A{ContinueRow + 1}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[$"A{ContinueRow}"].Style.Font.Size = 14;
sheet.Cells[$"A{ContinueRow + 1}"].Style.Font.Size = 14;
sheet.Cells[$"B{ContinueRow + 1}:Y{ ContinueRow + 1}"].Merge = true;
sheet.Row(ContinueRow).Height = 87;
sheet.Cells[$"A{ContinueRow}"].Value = "Ký xác nhận\r\n(Ghi rõ họ và tên)";
sheet.Row(ContinueRow + 1).Height = 87;
sheet.Cells[$"A{ContinueRow + 1}"].Value = "Ghi chú \r\n(Ghi rõ thông tin trường hợp NOT OK)";
int timevalue = 0;
for (int i = 2; i <= 25; i += 2)
{
sheet.Column(i).Width = 8.5;
sheet.Column(i + 1).Width = 8.5;
ExcelRange checktime = sheet.Cells[3, i, 3, i + 1];
checktime.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
checktime.Merge = true;
checktime.Value = $"{timevalue}h";
checktime.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
checktime.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
checktime.Style.Font.Bold = true;
checktime.Style.Font.Size = titlesize;
sheet.Cells[4, i, ContinueRow - 1, i].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[4, i, ContinueRow - 1, i].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Dotted;
sheet.Cells[4, i, 4, i + 1].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells[4, i].Value = "OK";
sheet.Cells[4, i].Style.Font.Bold = true;
sheet.Cells[4, i + 1].Value = "NOK";
sheet.Cells[4, i + 1].Style.Font.Bold = true;
sheet.Cells[ContinueRow, i, ContinueRow, i + 1].Merge = true;
sheet.Cells[ContinueRow, i, ContinueRow, i + 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
timevalue += 2;
}
sheet.Cells[1, 1, ContinueRow + 1, 25].Style.WrapText = true;
sheet.Cells[1, 1, ContinueRow + 1, 25].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
sheet.Cells[1, 1, ContinueRow + 1, 25].Style.Font.Name = "Times New Roman";
sheet.Cells[1, 1, ContinueRow + 1, 25].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells[1, 1, ContinueRow + 1, 25].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[5, 1, ContinueRow + 1, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
}
