public RPT002_2_Process(int WPA_ID) { try { List <sp_RPT002_GetWorkPlaceAirReport_2_Result> dataList = vmRpt.GetWorkPlaceAirReport_2(WPA_ID); if (dataList != null && dataList.Count > 0) { string fileName = string.Format("{0} ({1})", dataList[0].ANALYSYS_NO, dataList[0].CUSTOMER_NAME_TH); string tmpFileName = FilesUtil.GetTmpXlsxFileName(fileName); FileInfo newFile = new FileInfo(tmpFileName); using (ExcelPackage NewPck = new ExcelPackage(newFile)) using (ExcelWorkbook NewWb = NewPck.Workbook) { //load template string TEMPLATE_FILE_NAME = "RPT002_Template.xlsx"; string TemplatePath = Path.Combine(Application.StartupPath, OISBaseConstant.PATH_TEMPLATE, TEMPLATE_FILE_NAME); FileInfo OrgFile = new FileInfo(TemplatePath); //create sheet using (ExcelPackage OrgPck = new ExcelPackage(OrgFile)) using (ExcelWorkbook OrgWb = OrgPck.Workbook) { ExcelWorksheet OrgSheet; int firstRecordRow, templateColumnCount, firstReferenceRow; OrgSheet = OrgWb.Worksheets[1]; firstRecordRow = 15; firstReferenceRow = 17; templateColumnCount = CellStringToIndex("BV"); //add sheet NewWb.Worksheets.Add(dataList[0].ANALYSYS_NO, OrgSheet); ExcelWorksheet sht = NewWb.Worksheets[1]; sht.DeleteRow(16); sht.Cells.Worksheet.Workbook.Styles.UpdateXml(); // Header sht.Cells[4, CellStringToIndex("J")].Value = dataList[0].CUSTOMER_NAME_TH; sht.Cells[5, CellStringToIndex("F")].Value = dataList[0].CUSTOMER_ADDRNO; sht.Cells[5, CellStringToIndex("N")].Value = dataList[0].CUSTOMER_MOO; sht.Cells[5, CellStringToIndex("R")].Value = dataList[0].CUSTOMER_ROAD; sht.Cells[5, CellStringToIndex("AC")].Value = dataList[0].CUSTOMER_SUBDISTRICT; sht.Cells[5, CellStringToIndex("AT")].Value = dataList[0].CUSTOMER_DISTRICT; sht.Cells[5, CellStringToIndex("BJ")].Value = dataList[0].CUSTOMER_PROVINCE; sht.Cells[6, CellStringToIndex("G")].Value = dataList[0].CUSTOMER_POSTCODE; sht.Cells[5, CellStringToIndex("O")].Value = dataList[0].CUSTOMER_TEL; sht.Cells[22, CellStringToIndex("W")].Value = dataList[0].ANALYST_NAME; sht.Cells[22, CellStringToIndex("BH")].Value = dataList[0].AGENT_NAME; string t = sht.Cells[23, CellStringToIndex("BH")].Text; sht.Cells[23, CellStringToIndex("BH")].Value = null; var rt1 = sht.Cells[23, CellStringToIndex("BH")].RichText.Add("นายจ้าง"); var rt2 = sht.Cells[23, CellStringToIndex("BH")].RichText.Add("/"); var rt3 = sht.Cells[23, CellStringToIndex("BH")].RichText.Add("ผู้กระทำแทน"); if (dataList[0].AGENT_FLAG) { rt3.Strike = true; } else { rt1.Strike = true; } // Reference int nextRow = firstReferenceRow; var instList = dataList .Where(x => x.INST_NAME != null) .Select(x => new { INST_NAME = x.INST_NAME, INST_EDITION = x.INST_EDITION, INST_PAGE_FROM = x.INST_PAGE_FROM, INST_PAGE_TO = x.INST_PAGE_TO, }).Distinct().ToList(); if (instList != null && instList.Count > 0) { for (int i = 0; i < instList.Count; i++) { if (i > 0 && i < instList.Count - 1) { sht.InsertRow(nextRow, 1, firstReferenceRow); OrgSheet.Cells[firstReferenceRow + 1, 1, firstReferenceRow + 1, templateColumnCount].Copy(sht.Cells[nextRow, 1, nextRow, templateColumnCount]); } var data = instList[i]; sht.Cells[nextRow, CellStringToIndex("V")].Value = string.Format("{0}. {1}", i + 1, data.INST_NAME); sht.Cells[nextRow, CellStringToIndex("AT")].Value = data.INST_EDITION; sht.Cells[nextRow, CellStringToIndex("BG")].Value = data.INST_PAGE_FROM; sht.Cells[nextRow, CellStringToIndex("BP")].Value = data.INST_PAGE_TO; } } // Detail nextRow = firstRecordRow; for (int i = 0; i < dataList.Count; i++) { if (i > 0 && i < dataList.Count - 1) { sht.InsertRow(nextRow, 1, firstRecordRow); OrgSheet.Cells[firstRecordRow + 1, 1, firstRecordRow + 1, templateColumnCount].Copy(sht.Cells[nextRow, 1, nextRow, templateColumnCount]); } sp_RPT002_GetWorkPlaceAirReport_2_Result data = dataList[i]; sht.Cells[nextRow, CellStringToIndex("A")].Value = data.PARAMETER_NAME; sht.Cells[nextRow, CellStringToIndex("H")].Value = data.SAMPLING_DATE; sht.Cells[nextRow, CellStringToIndex("L")].Value = data.LOC_NAME; sht.Cells[nextRow, CellStringToIndex("Y")].Value = data.TOOLPICK_NAME; sht.Cells[nextRow, CellStringToIndex("AK")].Value = data.AIR_FLOW; sht.Cells[nextRow, CellStringToIndex("AN")].Value = data.SAMPLING_TIME; sht.Cells[nextRow, CellStringToIndex("AS")].Value = data.ANALYTICAL_DATE; sht.Cells[nextRow, CellStringToIndex("AX")].Value = data.TOOLANALYSIS_NAME; sht.Cells[nextRow, CellStringToIndex("BJ")].Value = data.RESULT_DISP.ToString(); sht.Cells[nextRow, CellStringToIndex("BO")].Value = data.STANDARD_DISP; sht.Cells[nextRow, CellStringToIndex("BS")].Value = "ไม่เกิน"; nextRow++; } //Open Excel NewPck.Save(); Process.Start(tmpFileName); } } } } catch (Exception ex) { throw ex; } }