public static CReturnDataDeviate GetDataDeviate(CForGetDataDeviate lstSearch)
{
CReturnDataDeviate result = new CReturnDataDeviate();
result.lstMonth = new List <CDataDeviate>();
if (!UserAcc.UserExpired())
{
result.lstMonth = SystemFunction.GetDeviate(lstSearch.nIncID, lstSearch.nOprtID, lstSearch.nFacID, lstSearch.sYear).Where(w => lstSearch.lstMonth.Contains(w.nMonth) && w.sRemark.Trim() != "").Select(s => new CDataDeviate
{
nMonth = s.nMonth,
sRemark = s.sRemark.Replace("\n", "<br/>")
}).ToList();
result.Status = SystemFunction.process_Success;
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
private void Export_EXCEL(string sFacility, string sYear)
{
int nFacility = SystemFunction.GetIntNullToZero(sFacility);
string[] alphabet = { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" };
MemoryStream fsExport = new MemoryStream();
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
string[] Arr_Column = { "Indicator", "Unit", "Target", "Q1 : Jan", "Q1 : Feb", "Q1 : Mar", "Q2 : Apr", "Q2 : May", "Q2 : Jun", "Q3 : Jul", "Q3 : Aug", "Q3 : Sep", "Q4 : Oct", "Q4 : Nov", "Q4 : Dec", "Remark" };
double[] Arr_ColumnWidth = { 50, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 50 };
using (XLWorkbook wb = new XLWorkbook())
{
#region LG
var ws = wb.Worksheets.Add("EPIFROM_Intensity");
//สร้างหัวข้อ
string sCol = alphabet[Arr_Column.Length - 1].ToUpper();
#region CreatHeadReport
ws.Cell("A1").Value = "Indicator : Intensity Denominator ";
ws.Cell("A1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A1").Style.Font.FontName = "Cordia New";
ws.Cell("A1").Style.Font.FontSize = 14.00;
ws.Cell("A2").Value = "Operation Type : Refinery";
ws.Cell("A2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A2").Style.Font.FontName = "Cordia New";
ws.Cell("A2").Style.Font.FontSize = 14.00;
var Facility = db.mTFacility.FirstOrDefault(w => w.cActive == "Y" && w.cDel == "N" && w.nLevel == 2 && w.ID + "" == sFacility);
if (Facility != null)
{
sFacility = Facility.Name;
}
ws.Cell("A3").Value = "Sub-facility : " + sFacility;
ws.Cell("A3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A3").Style.Font.FontName = "Cordia New";
ws.Cell("A3").Style.Font.FontSize = 14.00;
ws.Cell("A4").Value = "Year : " + sYear;
ws.Cell("A4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A4").Style.Font.FontName = "Cordia New";
ws.Cell("A4").Style.Font.FontSize = 14.00;
#endregion
//สร้างหัวตาราง
int nRow = 5;
var RowsHead = ws;
string sCC = "#dbea97";
int nRowna = 1;
#region สร้างตาราง
var lst = ssTRetunrLoadData_1;
lst.lstIn.ForEach(f =>
{
sCC = "#dbea97";
if (f.ProductID != 89 && f.ProductID != 90)
{
for (int i = 0; i < Arr_Column.Length; i++)
{
sCol = alphabet[i].ToUpper();
GetCellHead(RowsHead, Arr_ColumnWidth[i], sCol, nRow + "", Arr_Column[i] + "", sCC);
}
}
if (f.ProductID != 89 && f.ProductID != 90)
{
nRow++;
}
string sText = "";
XLAlignmentHorizontalValues align = XLAlignmentHorizontalValues.Center;
for (int j = 0; j < Arr_Column.Length; j++)
{
sCol = alphabet[j].ToUpper();
sCC = "#dbea97";
if (f.cTotalAll != "Y")
{
sCC = "#ffedc4";
}
#region CaseRow
if (f.ProductID != 89 && f.ProductID != 90)
{
sText = SetText(j, f);
}
else
{
sysGlobalClass.T_TIntensity_Other ftemp = new sysGlobalClass.T_TIntensity_Other();
ftemp.sIndicator = f.ProductName;
ftemp.sUnit = f.sUnit;
ftemp.sTarget = f.Target;
ftemp.M1 = f.M1;
ftemp.M2 = f.M2;
ftemp.M3 = f.M3;
ftemp.M4 = f.M4;
ftemp.M5 = f.M5;
ftemp.M6 = f.M6;
ftemp.M7 = f.M7;
ftemp.M8 = f.M8;
ftemp.M9 = f.M9;
ftemp.M10 = f.M10;
ftemp.M11 = f.M11;
ftemp.M12 = f.M12;
sCC = "#FFFFFF";
sText = SetTextDetail(j, ftemp);
}
align = SetAlign(j);
#endregion
GetCellText(RowsHead, Arr_ColumnWidth[j], sCol, nRow + "", sText, align, j + "", sCC);
}
nRow++;
f.lstarrDetail.ForEach(f2 =>
{
for (int k = 0; k < Arr_Column.Length; k++)
{
sCC = "#FFFFFF";
sCol = alphabet[k].ToUpper();
sText = SetTextDetail(k, f2);
align = SetAlign(k);
GetCellText(RowsHead, Arr_ColumnWidth[k], sCol, nRow + "", sText, align, k + "", sCC);
}
nRow++;
});
});
#endregion
#endregion
#region Deviate
var dv = wb.Worksheets.Add("Deviate");
//สร้างหัวข้อ
sCol = alphabet[Arr_Column.Length - 1].ToUpper();
#region CreatHeadReport
dv.Cell("A1").Value = "Indicator : Intensity Denominator ";
dv.Cell("A1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
dv.Cell("A1").Style.Font.FontName = "Cordia New";
dv.Cell("A1").Style.Font.FontSize = 14.00;
dv.Cell("A2").Value = "Operation Type : Refinery";
dv.Cell("A2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
dv.Cell("A2").Style.Font.FontName = "Cordia New";
dv.Cell("A2").Style.Font.FontSize = 14.00;
dv.Cell("A3").Value = "Sub-facility : " + sFacility;
dv.Cell("A3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
dv.Cell("A3").Style.Font.FontName = "Cordia New";
dv.Cell("A3").Style.Font.FontSize = 14.00;
dv.Cell("A4").Value = "Year : " + sYear;
dv.Cell("A4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
dv.Cell("A4").Style.Font.FontName = "Cordia New";
dv.Cell("A4").Style.Font.FontSize = 14.00;
#endregion
//สร้างหัวตาราง
nRow = 5;
RowsHead = dv;
sCC = "#9cb726";
nRowna = 1;
#region สร้างตาราง
var lstdv = SystemFunction.GetDeviate(nIndicator, nOperationType, nFacility, sYear);
GetCellHead(RowsHead, 10, "A", nRow + "", "No." + "", sCC);
GetCellHead(RowsHead, 20, "B", nRow + "", "Month" + "", sCC);
GetCellHead(RowsHead, 20, "C", nRow + "", "Remark" + "", sCC);
GetCellHead(RowsHead, 20, "D", nRow + "", "Action By" + "", sCC);
GetCellHead(RowsHead, 20, "E", nRow + "", "Date" + "", sCC);
nRow++;
sCC = "#ffffff";
lstdv.ForEach(f =>
{
XLAlignmentHorizontalValues align = XLAlignmentHorizontalValues.Center;
GetCellText(RowsHead, 10, "A", nRow + "", nRowna + "", align, 0 + "", sCC);
GetCellText(RowsHead, 20, "B", nRow + "", f.sMonth + "", align, 0 + "", sCC);
GetCellText(RowsHead, 20, "C", nRow + "", f.sRemark + "", XLAlignmentHorizontalValues.Left, 0 + "", sCC);
GetCellText(RowsHead, 20, "D", nRow + "", f.sActionBy + "", align, 0 + "", sCC);
GetCellText(RowsHead, 20, "E", nRow + "", f.sDate + "", align, 0 + "", sCC);
nRow++;
nRowna++;
});
#endregion
#endregion
wb.SaveAs(fsExport);
}
string saveAsFileName = "Input_Intensity_" + sFacility + "_" + DateTime.Now.ToString("ddMMyyHHmmss", new CultureInfo("en-US")) + ".xlsx";
fsExport.Position = 0;
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=" + saveAsFileName);
Response.ContentType = "application/vnd.ms-excel";
try
{
Response.BinaryWrite(fsExport.ToArray());
}
catch (Exception ee)
{ }
ScriptManager.RegisterStartupScript(this, this.GetType(), "SessionExpire", "UnblockUI();", true);
Response.End();
}
public void ExportData(string sIncID, string sOprtID, string sFacID, string sYear)
{
HttpResponse httpResponse = Response;
XLWorkbook wb = new XLWorkbook();
int nRow = 1;
int nCol = 1;
#region Action
Action <IXLWorksheet, string, int, int, int, bool, XLAlignmentHorizontalValues, XLAlignmentVerticalValues, bool, int?, double?> SetTbl = (sWorkSheet, sTxt, row, col, FontSize, Bold, Horizontal, Vertical, wraptext, dec, width) =>
{
if (sTxt == null)
{
sTxt = "";
}
sWorkSheet.Cell(row, col).Value = sTxt;
sWorkSheet.Cell(row, col).Style.Font.FontSize = FontSize;
sWorkSheet.Cell(row, col).Style.Font.Bold = Bold;
sWorkSheet.Cell(row, col).Style.Alignment.WrapText = true;
sWorkSheet.Cell(row, col).Style.Alignment.Horizontal = Horizontal;
sWorkSheet.Cell(row, col).Style.Alignment.Vertical = Vertical;
if (width != null)
{
sWorkSheet.Column(col).Width = width.Value;
}
if (dec != null && sTxt != "0")
{
string sformate = "#,##";
sWorkSheet.Cell(row, col).Style.NumberFormat.Format = sformate;
}
var nIndex = sTxt.Split('/').Length;
if (nIndex == 3)
{
sWorkSheet.Cell(row, col).Style.DateFormat.Format = "dd/MM/yyyy";
}
};
#endregion
#region QUERY
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <cReport> lstProduct = new List <cReport>();
string[] arrShortMonth = new string[12] {
"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
};
int nIndID = SystemFunction.GetIntNullToZero(sIncID);
int nOprtID = SystemFunction.GetIntNullToZero(sOprtID);
int nFacID = SystemFunction.GetIntNullToZero(sFacID);
string sIncName = db.mTIndicator.Any(w => w.ID == nIndID) ? db.mTIndicator.FirstOrDefault(w => w.ID == nIndID).Indicator : "";
string sOprtName = db.mOperationType.Any(w => w.ID == nOprtID) ? db.mOperationType.FirstOrDefault(w => w.ID == nOprtID).Name : "";
string sFacName = db.mTFacility.Any(w => w.ID == nFacID) ? db.mTFacility.FirstOrDefault(w => w.ID == nFacID).Name : "";
var lstComplaintType = db.TM_ComplaintType.Where(w => w.cDel != "Y").ToList();
bool IsNew = true;
var itemEPI_FORM = db.TEPI_Forms.FirstOrDefault(w => w.sYear == sYear && w.IDIndicator == nIndID && w.OperationTypeID == nOprtID && w.FacilityID == nFacID);
int EPI_FORMID = 0;
if (itemEPI_FORM != null)
{
IsNew = false;
EPI_FORMID = itemEPI_FORM.FormID;
}
var lstImpactInFROM = db.TComplaint_Impact.Where(w => w.FormID == EPI_FORMID).ToList();
string sProductName = "";
var dataComplaint = db.mTProductIndicator.FirstOrDefault(w => w.IDIndicator == 2);
if (dataComplaint != null)
{
sProductName = dataComplaint.ProductName;
}
lstProduct = db.TComplaint.Where(w => w.FormID == EPI_FORMID).OrderBy(o => o.ComplaintDate).Select(s => new cReport
{
nComplaintID = s.nComplaintID,
ComplaintDate = s.ComplaintDate.HasValue ? s.ComplaintDate.Value : (DateTime?)null,
sProductName = sProductName,
nMonth = s.ComplaintDate.HasValue ? s.ComplaintDate.Value.Month : 0,
sIssueBy = s.sIssueBy,
sSubject = s.sSubject,
sDetail = s.sDetail,
nComplaintTypeID = s.nComplaintTypeID,
nComplaintByID = s.nComplaintByID,
sComplaintByOther = s.sComplaintByOther,
sCorrectiveAction = s.sCorrectiveAction,
}).ToList();
lstProduct.ForEach(f =>
{
var dataComplaintType = lstComplaintType.FirstOrDefault(w => w.nType == 1 && w.nID == f.nComplaintTypeID);
var dataComplaintBy = lstComplaintType.FirstOrDefault(w => w.nType == 3 && w.nID == f.nComplaintByID);
f.sIssueDate = f.ComplaintDate.HasValue ? f.ComplaintDate.Value.ToString("dd/MM/yyyy") : "";
f.sComplaintBy = dataComplaintBy != null ? dataComplaintBy.sName + (!string.IsNullOrEmpty(f.sComplaintByOther) ? " (" + f.sComplaintByOther + ")" : "") : "";
lstImpactInFROM.Where(w => w.nComplaintID == f.nComplaintID).ForEach(ipct =>
{
var dataImpact = lstComplaintType.FirstOrDefault(w => w.nType == 2 && w.nID == ipct.nImpactTypeID);
f.sImpact += dataImpact != null ? " ," + dataImpact.sName + (!string.IsNullOrEmpty(ipct.sOther) ? " (" + ipct.sOther + ")" : "") : "";
});
f.sImpact = f.sImpact.Length > 0 ? f.sImpact.Remove(0, 2) : f.sImpact;
f.sComplaintType = dataComplaintType != null ? dataComplaintType.sName : "";
});
var lstDeviate = SystemFunction.GetDeviate(nIndID, nOprtID, nFacID, sYear);
#endregion
#region BIND DATA
IXLWorksheet ws1 = wb.Worksheets.Add("Data");
ws1.PageSetup.Margins.Top = 0.2;
ws1.PageSetup.Margins.Bottom = 0.2;
ws1.PageSetup.Margins.Left = 0.1;
ws1.PageSetup.Margins.Right = 0;
ws1.PageSetup.Margins.Footer = 0;
ws1.PageSetup.Margins.Header = 0;
ws1.Style.Font.FontName = "Cordia New";
nRow = 1;
nCol = 1;
SetTbl(ws1, "Indicator : " + sIncName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
SetTbl(ws1, "Operation : " + sOprtName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
SetTbl(ws1, "Facility : " + sFacName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
SetTbl(ws1, "Year : " + sYear, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
SetTbl(ws1, "Product", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 45);
nCol++;
SetTbl(ws1, "Month", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 17);
nCol++;
SetTbl(ws1, "Complaint Type", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 17);
nCol++;
SetTbl(ws1, "Impact Type", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 17);
nCol++;
SetTbl(ws1, "Issue date", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 12);
nCol++;
SetTbl(ws1, "Issued by", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 15);
nCol++;
SetTbl(ws1, "Subject", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 35);
nCol++;
SetTbl(ws1, "Complaint by", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 17);
nCol++;
SetTbl(ws1, "Detail", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 35);
nCol++;
SetTbl(ws1, "Corrective action", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 35);
ws1.Range(nRow, 1, nRow, nCol).Style.Fill.BackgroundColor = XLColor.FromHtml("#9cb726");
ws1.Range(nRow, 1, nRow, nCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws1.Range(nRow, 1, nRow, nCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
foreach (var item in lstProduct)
{
nRow++;
nCol = 1;
SetTbl(ws1, "'" + item.sProductName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + arrShortMonth[item.nMonth.Value - 1], nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sComplaintType, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sImpact, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sIssueDate, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sIssueBy, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sSubject, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sComplaintBy, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sDetail, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sCorrectiveAction, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws1.Range(nRow, 1, nRow, nCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws1.Range(nRow, 1, nRow, nCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
#endregion
#region BIND DATA DEVIATE
IXLWorksheet ws2 = wb.Worksheets.Add("Deviate");
ws2.PageSetup.Margins.Top = 0.2;
ws2.PageSetup.Margins.Bottom = 0.2;
ws2.PageSetup.Margins.Left = 0.1;
ws2.PageSetup.Margins.Right = 0;
ws2.PageSetup.Margins.Footer = 0;
ws2.PageSetup.Margins.Header = 0;
ws2.Style.Font.FontName = "Cordia New";
nRow = 1;
nCol = 1;
SetTbl(ws2, "Indicator : " + sIncName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, nCol, nRow, nCol + 1).Merge();
nCol++;
SetTbl(ws2, sIncName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
nCol = 1;
SetTbl(ws2, "Operation : " + sOprtName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, nCol, nRow, nCol + 1).Merge();
nCol++;
SetTbl(ws2, sOprtName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
nCol = 1;
SetTbl(ws2, "Facility : " + sFacName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, nCol, nRow, nCol + 1).Merge();
nCol++;
SetTbl(ws2, sFacName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
nCol = 1;
SetTbl(ws2, "Year : " + sYear, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, nCol, nRow, nCol + 1).Merge();
nCol++;
SetTbl(ws2, sYear, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
nCol = 1;
SetTbl(ws2, "No.", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 10);
nCol++;
SetTbl(ws2, "Month", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 13);
nCol++;
SetTbl(ws2, "Remark", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 50);
nCol++;
SetTbl(ws2, "Action By", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 20);
nCol++;
SetTbl(ws2, "Date", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 15);
ws2.Range(nRow, 1, nRow, nCol).Style.Fill.BackgroundColor = XLColor.FromHtml("#9cb726");
ws2.Range(nRow, 1, nRow, nCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws2.Range(nRow, 1, nRow, nCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
for (int i = 0; i < lstDeviate.Count(); i++)
{
nRow++;
nCol = 1;
SetTbl(ws2, "'" + (i + 1), nRow, nCol, 14, false, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws2, lstDeviate[i].sMonth, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws2, lstDeviate[i].sRemark, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws2, lstDeviate[i].sActionBy, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws2, lstDeviate[i].sDate, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, 1, nRow, nCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws2.Range(nRow, 1, nRow, nCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
#endregion
#region CreateEXCEL
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string sName = "Input_" + sIncName + "_" + sFacName + "_" + DateTime.Now.ToString("ddMMyyHHmmss", new CultureInfo("en-US"));
httpResponse.AddHeader("content-disposition", "attachment;filename=" + sName + ".xlsx");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
wb.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
#endregion
}