public ActionResult Index(FilterCPAR_DetailVM poViewModel) { ViewBag.AC_MENU_RUID = valMENU.CPAR_LOGBOOK_CPAR_INDEX; oVAL_filterCPAR = new FilterCPAR_Validation(poViewModel); oVAL_filterCPAR.Validate_Export(); //Add Error if exists for (int i = 0; i < oVAL_filterCPAR.aValidationMSG.Count; i++) { ModelState.AddModelError(oVAL_filterCPAR.aValidationMSG[i].VAL_ERRID, oVAL_filterCPAR.aValidationMSG[i].VAL_ERRMSG); } //End for (int i = 0; i < oVAL.aValidationMSG.Count; i++) if (ModelState.IsValid) { } //End if (ModelState.IsValid) FilterCPAR_DetailVM oViewModel = new FilterCPAR_DetailVM(); ViewBag.CLASS_RUID_LOV = oDSLOVClass.getDatalist(); ViewBag.CPAR_STS_LOV = oDSLOVCPARsts.getDatalist(); ViewBag.Model = oDS.getDatalist_logbook(poViewModel); return(View(poViewModel)); }
public ActionResult LogbookCPAR_XLS(FilterCPAR_DetailVM poViewModel) { ViewBag.AC_MENU_RUID = valMENU.CPAR_LOGBOOK_CPAR_INDEX; oVAL_filterCPAR = new FilterCPAR_Validation(poViewModel); oVAL_filterCPAR.Validate_Export(); //Add Error if exists for (int i = 0; i < oVAL_filterCPAR.aValidationMSG.Count; i++) { ModelState.AddModelError(oVAL_filterCPAR.aValidationMSG[i].VAL_ERRID, oVAL_filterCPAR.aValidationMSG[i].VAL_ERRMSG); } //End for (int i = 0; i < oVAL.aValidationMSG.Count; i++) if (ModelState.IsValid) { this.sFileName = "LOGBOOK_CPAR_"; this.sSheetName = "Logbook CPAR"; this.nColcount = 18; using (var package = new ExcelPackage()) { //Get Data CPARCPARStdrefDS oDS = new CPARCPARStdrefDS(); 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 CPAR"; //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 sPeriod = ""; if ((poViewModel.CPAR_DT1 != null) && (poViewModel.CPAR_DT2 != null)) { sPeriod = "Periode : "; sPeriod = sPeriod + hlpConvertionAndFormating.ConvertDateToStringDateShortFmt(poViewModel.CPAR_DT1) + " s/d "; sPeriod = sPeriod + hlpConvertionAndFormating.ConvertDateToStringDateShortFmt(poViewModel.CPAR_DT2); } //End if ((poViewModel.CPAR_DT1 != null) && (poViewModel.CPAR_DT2 != null)) ws.Cells[2, 1].Value = sPeriod; //Set Title 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 = "KATEGORI"; ws.Cells[3, 3].Value = "NOMOR"; ws.Cells[3, 4].Value = "MASALAH"; ws.Cells[3, 5].Value = "ANALISA PENYEBAB"; ws.Cells[3, 6].Value = "TINDAKAN PERBAIKAN"; ws.Cells[3, 7].Value = "SUMBER"; ws.Cells[3, 8].Value = "REFERENSI"; ws.Cells[3, 9].Value = "AUDITOR"; ws.Cells[3, 10].Value = "AUDITEE"; ws.Cells[3, 11].Value = "TANGGAL TEMUAN"; ws.Cells[3, 12].Value = "TANGGAL PENYELESAIAN"; ws.Cells[3, 13].Value = "VERIFIKASI KE-1"; ws.Cells[3, 14].Value = "VERIFIKASI KE-2"; ws.Cells[3, 15].Value = "VERIFIKASI KE-3"; ws.Cells[3, 16].Value = "VERIFIKASI KE-4"; ws.Cells[3, 17].Value = "VERIFIKASI KE-5"; ws.Cells[3, 18].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 string sVerify = ""; string sStdref = ""; int i = 4; int nNo = 1; Boolean isFirst = true; 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].CPAR_SUBTYPE_ID; ws.Cells[i, 3].Value = oData[n].CPAR_ID; ws.Cells[i, 4].Value = oData[n].CPAR_DESC; ws.Cells[i, 5].Value = oData[n].CPAR_RESOLUTION1; ws.Cells[i, 6].Value = oData[n].CPAR_RESOLUTION2; ws.Cells[i, 7].Value = oData[n].CPAR_TYPE_NM; ws.Cells[i, 9].Value = oData[n].AUDITOR_NM; ws.Cells[i, 10].Value = oData[n].AUDITEE_NM; ws.Cells[i, 11].Value = hlpConvertionAndFormating.ConvertDateToStringDateShortFmt(oData[n].CPAR_DT); ws.Cells[i, 12].Value = hlpConvertionAndFormating.ConvertDateToStringDateShortFmt(oData[n].CPAR_FINISH_DT); ws.Cells[i, 18].Value = oData[n].CPAR_STS_NM; sVerify = ""; if (oData[n].VFKS1_DT != null) { sVerify = hlpConvertionAndFormating.ConvertDateToStringDateShortFmt(oData[n].VFKS1_DT); } //End if (oData[n].VFKS1_DT != null) if (oData[n].VFKS1_DESC != null) { sVerify = sVerify + "\n" + oData[n].VFKS1_DESC; } //End if (oData[n].VFKS1_DT != null) ws.Cells[i, 13].Value = sVerify; sVerify = ""; if (oData[n].VFKS2_DT != null) { sVerify = hlpConvertionAndFormating.ConvertDateToStringDateShortFmt(oData[n].VFKS2_DT); } //End if (oData[n].VFKS2_DT != null) if (oData[n].VFKS2_DESC != null) { sVerify = sVerify + "\n" + oData[n].VFKS2_DESC; } //End if if (oData[n].VFKS2_DESC != null) ws.Cells[i, 14].Value = sVerify; sVerify = ""; if (oData[n].VFKS3_DT != null) { sVerify = hlpConvertionAndFormating.ConvertDateToStringDateShortFmt(oData[n].VFKS3_DT); } //End if (oData[n].VFKS3_DT != null) if (oData[n].VFKS3_DESC != null) { sVerify = sVerify + "\n" + oData[n].VFKS3_DESC; } //End if if (oData[n].VFKS3_DESC != null) ws.Cells[i, 15].Value = sVerify; sVerify = ""; if (oData[n].VFKS4_DT != null) { sVerify = hlpConvertionAndFormating.ConvertDateToStringDateShortFmt(oData[n].VFKS4_DT); } //End if (oData[n].VFKS4_DT != null) if (oData[n].VFKS4_DESC != null) { sVerify = sVerify + "\n" + oData[n].VFKS4_DESC; } //End if if (oData[n].VFKS4_DESC != null) ws.Cells[i, 16].Value = sVerify; sVerify = ""; if (oData[n].VFKS5_DT != null) { sVerify = hlpConvertionAndFormating.ConvertDateToStringDateShortFmt(oData[n].VFKS5_DT); } //End if (oData[n].VFKS5_DT != null) if (oData[n].VFKS5_DESC != null) { sVerify = sVerify + "\n" + oData[n].VFKS5_DESC; } //End if if (oData[n].VFKS5_DESC != null) ws.Cells[i, 17].Value = sVerify; sStdref = oData[n].STDREFLOV_ID + " - " + oData[n].STDREFLOV_NM; while (true) { if (n + 1 >= oData.Count) { break; } //End if (n + 1 < oData.Count) if (oData[n].RUID != oData[n + 1].RUID) { break; } //End if (oData[n].RUID != oData[n + 1].RUID) n++; //sStdref = sStdref + "\n\r" + oData[n].STDREFLOV_ID + " - " + oData[n].STDREFLOV_NM; sStdref = sStdref + "\n" + oData[n].STDREFLOV_ID + " - " + oData[n].STDREFLOV_NM; } //End while (true) ws.Cells[i, 8].Value = sStdref; 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)); }