public ActionResult LogbookComplain_XLS(FilterComplain_DetailVM poViewModel)
{
ViewBag.AC_MENU_RUID = valMENU.CPAR_LOGBOOK_COMPLAIN_INDEX;
oVAL_filterComplain = new FilterComplain_Validation(poViewModel);
oVAL_filterComplain.Validate_Export();
//Add Error if exists
for (int i = 0; i < oVAL_filterComplain.aValidationMSG.Count; i++)
{
ModelState.AddModelError(oVAL_filterComplain.aValidationMSG[i].VAL_ERRID, oVAL_filterComplain.aValidationMSG[i].VAL_ERRMSG);
} //End for (int i = 0; i < oVAL.aValidationMSG.Count; i++)
if (ModelState.IsValid)
{
this.sFileName = "LOGBOOK_Complain_";
this.sSheetName = "Logbook Complain";
this.nColcount = 16;
using (var package = new ExcelPackage())
{
//Get Data
ComplainDS oDS = new ComplainDS();
var oData = oDS.getDatalist_logbookprint(poViewModel);
if (oData == null)
{
ViewBag.Datanotfound = "Data tidak ditemukan";
}
if (oData.Count <= 0)
{
ViewBag.Datanotfound = "Data tidak ditemukan";
}
if (ViewBag.Datanotfound != null)
{
return(View(poViewModel));
}
package.Workbook.Worksheets.Add(sSheetName);
ExcelWorksheet ws = package.Workbook.Worksheets[1];
ws.Name = sSheetName; //Setting Sheet's name
ws.Cells.Style.Font.Size = nFontSize_Default; //Default font size for whole sheet
ws.Cells.Style.Font.Name = sFontName_Default; //Default Font name for whole sheet
ws.View.ShowGridLines = false;
//Render Header Data
//Judul
ws.Cells[1, 1].Value = "Logbook Complain"; //Set Title
ws.Cells[1, 1, 1, 10].Merge = true; //Merge columns start and end range
ws.Cells[1, 1, 1, 10].Style.Font.Bold = true; //Font should be bold
ws.Cells[1, 1, 1, 10].Style.Font.Size = nFontSize_Title;
ws.Cells[1, 1, 1, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; // Aligmnet is center
//Periode
string sPeriode = "";
if ((poViewModel.Complain_DT1 != null) && (poViewModel.Complain_DT2 != null))
{
sPeriode = sPeriode + "Periode ";
sPeriode = sPeriode + hlpConvertionAndFormating.ConvertDateToStringDateShortFmt(poViewModel.Complain_DT1) + " s/d ";
sPeriode = sPeriode + hlpConvertionAndFormating.ConvertDateToStringDateShortFmt(poViewModel.Complain_DT2);
} //End if ((poViewModel.Complain_DT1 != null) && (poViewModel.Complain_DT2 != null))
ws.Cells[2, 1].Value = sPeriode;
ws.Cells[2, 1, 2, 10].Merge = true; //Merge columns start and end range
ws.Cells[2, 1, 2, 10].Style.Font.Bold = false; //Font should be bold
ws.Cells[2, 1, 2, 10].Style.Font.Size = nFontSize_Default;
ws.Cells[2, 1, 2, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; // Aligmnet is center
//Column Header
ws.Cells[3, 1].Value = "NO";
ws.Cells[3, 2].Value = "NO. PROYEK";
ws.Cells[3, 3].Value = "NAMA PROYEK";
ws.Cells[3, 4].Value = "STATUS\nPROYEK";
ws.Cells[3, 5].Value = "BLN/THN\nPROYEK SELESAI";
ws.Cells[3, 6].Value = "TANGGAL\nCOMPLAIN DITERIMA";
ws.Cells[3, 7].Value = "PENERIMA\nCOMPLAIN";
ws.Cells[3, 8].Value = "PENCATAT\nCOMPLAIN";
ws.Cells[3, 9].Value = "JENIS COMPLAIN";
ws.Cells[3, 10].Value = "PENJELASAN COMPLAIN";
ws.Cells[3, 11].Value = "PIC\nPENANGANAN\nCOMPLAIN";
ws.Cells[3, 12].Value = "TANGGAL\nCOMPLAIN\nDI RESPONSE";
ws.Cells[3, 13].Value = "ANALISA PENYEBAB";
ws.Cells[3, 14].Value = "KOREKSI\n(Short Term Action)";
ws.Cells[3, 15].Value = "TARGET\nSELESAI";
ws.Cells[3, 16].Value = "STATUS";
for (int j = 0; j < nColcount; j++)
{
ws.Cells[3, j + 1].Style.Border.BorderAround(ExcelBorderStyle.Hair);
ws.Cells[3, j + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center
ws.Cells[3, j + 1].Style.Font.Bold = true;
ws.Cells[3, j + 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ws.Cells[3, j + 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(204, 204, 204));
} //End for (int j = 0; j < 13; j++)
//Render Content Data
int i = 4;
int nNo = 1;
for (int n = 0; n < oData.Count; n++)
{
for (int j = 0; j < nColcount; j++)
{
ws.Cells[i, j + 1].Style.VerticalAlignment = ExcelVerticalAlignment.Top;
} //End for (int j = 0; j < 13; j++)
ws.Cells[i, 1].Value = nNo;
ws.Cells[i, 2].Value = oData[n].PROJ_ID;
ws.Cells[i, 3].Value = oData[n].PROJ_NM;
ws.Cells[i, 4].Value = oData[n].PROJ_STS_NM;
//ws.Cells[i, 5].Value = oData[n]. //BLN/THN\nPROYEK SELESAI
ws.Cells[i, 6].Value = hlpConvertionAndFormating.ConvertDateToStringDateShortFmt(oData[n].ISSUED_DT);
ws.Cells[i, 7].Value = oData[n].RECIPIENT_NM;
//ws.Cells[i, 8].Value = oData[n]. //PENCATAT\nCOMPLAIN
ws.Cells[i, 9].Value = oData[n].COMPLAIN_TYPE_NM;
ws.Cells[i, 10].Value = oData[n].DESCRIPTION;
ws.Cells[i, 11].Value = oData[n].PIC_NM;
ws.Cells[i, 12].Value = hlpConvertionAndFormating.ConvertDateToStringDateShortFmt(oData[n].RESPONSE_DT);
//ws.Cells[i, 13].Value = oData[n]. //ANALISA PENYEBAB
ws.Cells[i, 14].Value = oData[n].SOLUTION;
//ws.Cells[i, 15].Value = oData[n]. //TARGET\nSELESAI
ws.Cells[i, 16].Value = oData[n].COMPLAIN_STS_NM;
i++;
nNo++;
} //End for (int n = 0; n < oData.Count; n++)
ws.Cells[3, 1, i, nColcount].Style.Border.BorderAround(ExcelBorderStyle.Hair);
//Create File
var oMemoryStream = package.GetAsByteArray();
var ofileName = string.Format(sFileName + "{0:yyyyMMdd-HHmmss}.xlsx", DateTime.UtcNow);
return(base.File(oMemoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", ofileName));
} //End if (ModelState.IsValid)
} //End using (var package = new ExcelPackage())
return(View(poViewModel));
}
