List of usage examples for org.apache.poi.hssf.usermodel HSSFCell setCellValue
@SuppressWarnings("fallthrough") public void setCellValue(boolean value)
From source file:com.smi.travel.controller.excel.checking.OverdueSummaryExcel.java
private void getOverdueSummary(HSSFWorkbook wb, List refundTicket) { String sheetName = "overdueSummary";// name of sheet HSSFSheet sheet1 = wb.createSheet(sheetName); UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleAlignRightBorderAllHeaderTable = wb.createCellStyle(); styleAlignRightBorderAllHeaderTable.setFont(excelFunction.getHeaderTable(wb.createFont())); styleAlignRightBorderAllHeaderTable.setAlignment(styleAlignRightBorderAllHeaderTable.ALIGN_CENTER); styleAlignRightBorderAllHeaderTable.setBorderTop(styleAlignRightBorderAllHeaderTable.BORDER_THIN); styleAlignRightBorderAllHeaderTable.setBorderBottom(styleAlignRightBorderAllHeaderTable.BORDER_THIN); styleAlignRightBorderAllHeaderTable.setBorderRight(styleAlignRightBorderAllHeaderTable.BORDER_THIN); styleAlignRightBorderAllHeaderTable.setBorderLeft(styleAlignRightBorderAllHeaderTable.BORDER_THIN); HSSFCellStyle styleAlignRightBorderAllNumber = wb.createCellStyle(); styleAlignRightBorderAllNumber.setAlignment(styleAlignRightBorderAllNumber.ALIGN_RIGHT); styleAlignRightBorderAllNumber.setDataFormat(currency.getFormat("#,##0.00")); styleAlignRightBorderAllNumber.setBorderTop(styleAlignRightBorderAllNumber.BORDER_THIN); styleAlignRightBorderAllNumber.setBorderBottom(styleAlignRightBorderAllNumber.BORDER_THIN); styleAlignRightBorderAllNumber.setBorderRight(styleAlignRightBorderAllNumber.BORDER_THIN); HSSFCellStyle styleAlignRightBorderAll = wb.createCellStyle(); styleAlignRightBorderAll.setAlignment(styleAlignRightBorderAll.ALIGN_LEFT); styleAlignRightBorderAll.setBorderTop(styleAlignRightBorderAll.BORDER_THIN); styleAlignRightBorderAll.setBorderBottom(styleAlignRightBorderAll.BORDER_THIN); styleAlignRightBorderAll.setBorderRight(styleAlignRightBorderAll.BORDER_THIN); styleAlignRightBorderAll.setBorderLeft(styleAlignRightBorderAll.BORDER_THIN); HSSFCellStyle styleNumber = wb.createCellStyle(); styleNumber.setAlignment(styleNumber.ALIGN_RIGHT); styleNumber.setDataFormat(currency.getFormat("#,##0.00")); HSSFCellStyle styleNumberBorderRight = wb.createCellStyle(); styleNumberBorderRight.setAlignment(styleNumberBorderRight.ALIGN_RIGHT); styleNumberBorderRight.setDataFormat(currency.getFormat("#,##0.00")); styleNumberBorderRight.setBorderRight(styleNumberBorderRight.BORDER_THIN); HSSFCellStyle styleBorderBottom = wb.createCellStyle(); //use styleBorderBottom.setBorderBottom(styleBorderBottom.BORDER_THIN); styleAlignRightBorderAllNumber.setBorderLeft(styleAlignRightBorderAllNumber.BORDER_THIN); HSSFCellStyle styleAlignRightBorderBottomRight = wb.createCellStyle();//use styleAlignRightBorderBottomRight.setAlignment(styleAlignRightBorderBottomRight.ALIGN_LEFT); styleAlignRightBorderBottomRight.setBorderBottom(styleAlignRightBorderBottomRight.BORDER_THIN); styleAlignRightBorderBottomRight.setBorderRight(styleAlignRightBorderBottomRight.BORDER_THIN); HSSFCellStyle styleAlignRightBorderAllColor = wb.createCellStyle(); styleAlignRightBorderAllColor.setFont(excelFunction.getHeaderTable(wb.createFont())); styleAlignRightBorderAllColor.setAlignment(styleAlignRightBorderAllColor.ALIGN_LEFT); styleAlignRightBorderAllColor.setBorderTop(styleAlignRightBorderAllColor.BORDER_THIN); styleAlignRightBorderAllColor.setBorderBottom(styleAlignRightBorderAllColor.BORDER_THIN); styleAlignRightBorderAllColor.setBorderRight(styleAlignRightBorderAllColor.BORDER_THIN); styleAlignRightBorderAllColor.setBorderLeft(styleAlignRightBorderAllColor.BORDER_THIN); styleAlignRightBorderAllColor.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); HSSFCellStyle styleAlignRightBorderAllDetailTable = wb.createCellStyle(); styleAlignRightBorderAllDetailTable.setFont(excelFunction.getHeadDetailBoldFont(wb.createFont())); styleAlignRightBorderAllDetailTable.setAlignment(styleAlignRightBorderAllDetailTable.ALIGN_LEFT); styleAlignRightBorderAllDetailTable.setBorderTop(styleAlignRightBorderAllDetailTable.BORDER_THIN); styleAlignRightBorderAllDetailTable.setBorderBottom(styleAlignRightBorderAllDetailTable.BORDER_THIN); styleAlignRightBorderAllDetailTable.setBorderRight(styleAlignRightBorderAllDetailTable.BORDER_THIN); styleAlignRightBorderAllDetailTable.setBorderLeft(styleAlignRightBorderAllDetailTable.BORDER_THIN); HSSFCellStyle total = wb.createCellStyle(); total.setFont(excelFunction.getHeadDetailBoldFont(wb.createFont())); total.setAlignment(total.ALIGN_CENTER); total.setBorderTop(total.BORDER_THIN); total.setBorderBottom(total.BORDER_THIN); total.setBorderRight(total.BORDER_THIN); total.setBorderLeft(total.BORDER_THIN); // set Header Report (Row 1) HSSFCellStyle styleC11 = wb.createCellStyle(); HSSFRow row01 = sheet1.createRow(0); HSSFCell cell01 = row01.createCell(0); cell01.setCellValue("Overdue Summary"); styleC11.setFont(excelFunction.getHeaderFont(wb.createFont())); cell01.setCellStyle(styleC11);/*from w ww. j av a 2s . co m*/ sheet1.addMergedRegion(CellRangeAddress.valueOf("A1:G1")); List<OverdueSummartExcel> listOver = new ArrayList<OverdueSummartExcel>(); if (refundTicket != null && refundTicket.size() != 0) { listOver = refundTicket; } else { listOver = null; } OverdueSummartExcel over = new OverdueSummartExcel(); if ((refundTicket != null) && (refundTicket.size() != 0)) { over = (OverdueSummartExcel) refundTicket.get(0); } // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); // Row 2 HSSFRow row02 = sheet1.createRow(1); HSSFCell cell021 = row02.createCell(0); cell021.setCellValue("Client : "); cell021.setCellStyle(styleC21); HSSFCell cell022 = row02.createCell(1); cell022.setCellValue(over.getClientname_page()); cell022.setCellStyle(styleC22); sheet1.addMergedRegion(CellRangeAddress.valueOf("B2:D2")); HSSFCell cell023 = row02.createCell(4); cell023.setCellValue("Agent : "); cell023.setCellStyle(styleC21); HSSFCell cell024 = row02.createCell(5); cell024.setCellValue(over.getStaffname_page()); cell024.setCellStyle(styleC22); // Row 3 HSSFRow row03 = sheet1.createRow(2); HSSFCell cell031 = row03.createCell(0); cell031.setCellValue("Date : "); cell031.setCellStyle(styleC21); HSSFCell cell032 = row03.createCell(1); cell032.setCellValue(over.getFrom_page()); cell032.setCellStyle(styleC22); sheet1.addMergedRegion(CellRangeAddress.valueOf("B3:D3")); HSSFCell cell033 = row03.createCell(4); cell033.setCellValue("Vat Type : "); cell033.setCellStyle(styleC21); HSSFCell cell034 = row03.createCell(5); cell034.setCellValue(over.getVattype_page()); cell034.setCellStyle(styleC22); // Row 4 HSSFRow row04 = sheet1.createRow(3); HSSFCell cell041 = row04.createCell(0); cell041.setCellValue("Department : "); cell041.setCellStyle(styleC21); HSSFCell cell042 = row04.createCell(1); cell042.setCellValue(over.getDepart_page()); cell042.setCellStyle(styleC22); sheet1.addMergedRegion(CellRangeAddress.valueOf("B4:D4")); HSSFCell cell043 = row04.createCell(4); cell043.setCellValue("View : "); cell043.setCellStyle(styleC21); HSSFCell cell044 = row04.createCell(5); cell044.setCellValue(over.getView_page()); cell044.setCellStyle(styleC22); // Row 5 HSSFRow row05 = sheet1.createRow(4); HSSFCell cell051 = row05.createCell(0); cell051.setCellValue("Group : "); cell051.setCellStyle(styleC21); sheet1.addMergedRegion(CellRangeAddress.valueOf("B5:D5")); HSSFCell cell052 = row05.createCell(1); cell052.setCellValue(over.getGroup_page()); cell052.setCellStyle(styleC22); HSSFCell cell053 = row05.createCell(4); cell053.setCellValue("Print By : "); cell053.setCellStyle(styleC21); HSSFCell cell054 = row05.createCell(5); cell054.setCellValue(over.getPrintby_page()); cell054.setCellStyle(styleC22); // Row 3 HSSFRow row06 = sheet1.createRow(5); HSSFCell cell061 = row06.createCell(0); cell061.setCellValue("Print Date : "); cell061.setCellStyle(styleC21); HSSFCell cell062 = row06.createCell(1); cell062.setCellValue(over.getPrintdate_page()); cell062.setCellStyle(styleC22); sheet1.addMergedRegion(CellRangeAddress.valueOf("B6:D6")); // Header Table HSSFRow row6 = sheet1.createRow(8); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue("Invoice No."); cell61.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(0); HSSFCell cell62 = row6.createCell(1); cell62.setCellValue("Date"); cell62.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(1); HSSFCell cell63 = row6.createCell(2); cell63.setCellValue("Detail"); sheet1.autoSizeColumn(2); cell63.setCellStyle(styleAlignRightBorderAllHeaderTable); HSSFCell cell64 = row6.createCell(3); cell64.setCellValue("Baht"); cell64.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(3); HSSFCell cell65 = row6.createCell(4); cell65.setCellValue("JPY"); cell65.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(4); HSSFCell cell66 = row6.createCell(5); cell66.setCellValue("USD"); cell66.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(5); HSSFCell cell67 = row6.createCell(6); cell67.setCellValue("Rec No"); cell67.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(6); HSSFCell cell68 = row6.createCell(7); cell68.setCellValue("Rec Amt"); cell68.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(7); HSSFCell cell69 = row6.createCell(8); cell69.setCellValue("Department"); cell69.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(8); HSSFCell cell70 = row6.createCell(9); cell70.setCellValue("Credit"); cell70.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(9); HSSFCell cell71 = row6.createCell(10); cell71.setCellValue("Ref No"); cell71.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(10); HSSFCell cell72 = row6.createCell(11); cell72.setCellValue("Due Date"); cell72.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(11); HSSFCell cell73 = row6.createCell(12); cell73.setCellValue("Overdue Status"); cell73.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(12); int start = 11; int end = 0; int num = 0; int count = 9; String temp = ""; // String sumThbAll = ""; // String sumJpyAll = ""; // String sumUsdAll = ""; // String sumRecAmtAll = ""; BigDecimal bahtTotalAll = new BigDecimal(BigInteger.ZERO); BigDecimal jpyTotalAll = new BigDecimal(BigInteger.ZERO); BigDecimal usdTotalAll = new BigDecimal(BigInteger.ZERO); BigDecimal recamtTotalAll = new BigDecimal(BigInteger.ZERO); if (listOver != null && listOver.size() != 0) { for (int r = 0; r < listOver.size(); r++) { if (r != 0) { if ("Agent".equals(listOver.get(r).getGroup())) { bahtTotalAll = bahtTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getBath())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getBath()))); jpyTotalAll = jpyTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getJpy())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getJpy()))); usdTotalAll = usdTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getUsd())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getUsd()))); recamtTotalAll = recamtTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getRecamt())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getRecamt()))); if (temp.equals(listOver.get(r).getInvto())) { // equal type if (r != (listOver.size() - 1)) { // check not last row HSSFRow row = sheet1.createRow(r + count); createCell(row, listOver, r, styleAlignRightBorderAllNumber, styleAlignRightBorderAll); sheet1.autoSizeColumn(13); } else { HSSFRow row = sheet1.createRow(r + count); createCell(row, listOver, r, styleAlignRightBorderAllNumber, styleAlignRightBorderAll); sheet1.autoSizeColumn(13); } } else { // not equal type // Start New Row (Group) String totalthb = "SUM(D" + start + ":D" + (r + count) + ")"; String totaljpy = "SUM(E" + start + ":E" + (r + count) + ")"; String totalusd = "SUM(F" + start + ":F" + (r + count) + ")"; String totalrecamt = "SUM(H" + start + ":H" + (r + count) + ")"; // sumThbAll += ",D"+(count+r+1); // sumJpyAll += ",E"+(count+r+1); // sumUsdAll += ",F"+(count+r+1); // sumRecAmtAll += ",H"+(count+r+1); start = count + r + 3; HSSFRow row00 = sheet1.createRow(r + count); HSSFCell cell00 = row00.createCell(0); cell00.setCellValue(""); cell00.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell001 = row00.createCell(1); cell001.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell002 = row00.createCell(2); cell002.setCellStyle(styleAlignRightBorderAllDetailTable); sheet1.addMergedRegion( CellRangeAddress.valueOf("A" + (count + r + 1) + ":C" + (count + r + 1) + "")); HSSFCell cell003 = row00.createCell(3); cell003.setCellFormula(totalthb); cell003.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell004 = row00.createCell(4); cell004.setCellFormula(totaljpy); cell004.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell005 = row00.createCell(5); cell005.setCellFormula(totalusd); cell005.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell006 = row00.createCell(6); cell006.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell007 = row00.createCell(7); cell007.setCellFormula(totalrecamt); cell007.setCellStyle(styleAlignRightBorderAllNumber); for (int k = 8; k < 13; k++) { HSSFCell cell008 = row00.createCell(k); cell008.setCellStyle(styleAlignRightBorderAllNumber); } sheet1.addMergedRegion( CellRangeAddress.valueOf("I" + (count + r + 1) + ":M" + (count + r + 1) + "")); HSSFRow row0 = sheet1.createRow(r + count + 1); HSSFCell cell = row0.createCell(0); cell.setCellValue(listOver.get(r).getInvto()); cell.setCellStyle(styleAlignRightBorderAllDetailTable); String add = "A" + (r + count + 2) + ":M" + (r + count + 2) + ""; sheet1.addMergedRegion(CellRangeAddress.valueOf(add)); row0.createCell(12).setCellStyle(styleAlignRightBorderAllColor); HSSFRow row122 = sheet1.createRow(r + count + 2); createCell(row122, listOver, r, styleAlignRightBorderAllNumber, styleAlignRightBorderAll); sheet1.autoSizeColumn(13); count = count + 2; } temp = listOver.get(r).getInvto(); if (r == (listOver.size() - 1)) { String totalthb = "SUM(D" + start + ":D" + (count + r + 1) + ")"; String totaljpy = "SUM(E" + start + ":E" + (count + r + 1) + ")"; String totalusd = "SUM(F" + start + ":F" + (count + r + 1) + ")"; String totalrecamt = "SUM(H" + start + ":H" + (count + r + 1) + ")"; // sumThbAll += ",D"+(count+r+2); // sumJpyAll += ",E"+(count+r+2); // sumUsdAll += ",F"+(count+r+2); // sumRecAmtAll += ",H"+(count+r+2); HSSFRow row00 = sheet1.createRow(count + r + 1); HSSFCell cell00 = row00.createCell(0); cell00.setCellValue(""); cell00.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell001 = row00.createCell(1); cell001.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell002 = row00.createCell(2); cell002.setCellStyle(styleAlignRightBorderAllDetailTable); sheet1.addMergedRegion( CellRangeAddress.valueOf("A" + (count + r + 2) + ":C" + (count + r + 2) + "")); HSSFCell cell003 = row00.createCell(3); cell003.setCellFormula(totalthb); cell003.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell004 = row00.createCell(4); cell004.setCellFormula(totaljpy); cell004.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell005 = row00.createCell(5); cell005.setCellFormula(totalusd); cell005.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell006 = row00.createCell(6); cell006.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell007 = row00.createCell(7); cell007.setCellFormula(totalrecamt); cell007.setCellStyle(styleAlignRightBorderAllNumber); for (int k = 8; k < 13; k++) { HSSFCell cell008 = row00.createCell(k); cell008.setCellStyle(styleAlignRightBorderAllNumber); } sheet1.addMergedRegion( CellRangeAddress.valueOf("I" + (count + r + 2) + ":M" + (count + r + 2) + "")); HSSFRow rowTotalAll = sheet1.createRow(count + r + 2); HSSFCell cellTotal00 = rowTotalAll.createCell(0); cellTotal00.setCellValue("Total"); cellTotal00.setCellStyle(total); HSSFCell cellTotal001 = rowTotalAll.createCell(1); cellTotal001.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cellTotal002 = rowTotalAll.createCell(2); cellTotal002.setCellStyle(styleAlignRightBorderAllDetailTable); sheet1.addMergedRegion( CellRangeAddress.valueOf("A" + (count + r + 3) + ":C" + (count + r + 3) + "")); HSSFCell cellTotal003 = rowTotalAll.createCell(3); cellTotal003.setCellValue(bahtTotalAll.doubleValue()); cellTotal003.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal004 = rowTotalAll.createCell(4); cellTotal004.setCellValue(jpyTotalAll.doubleValue()); cellTotal004.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal005 = rowTotalAll.createCell(5); cellTotal005.setCellValue(usdTotalAll.doubleValue()); cellTotal005.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal006 = rowTotalAll.createCell(6); cellTotal006.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal007 = rowTotalAll.createCell(7); cellTotal007.setCellValue(recamtTotalAll.doubleValue()); cellTotal007.setCellStyle(styleAlignRightBorderAllNumber); for (int k = 8; k < 13; k++) { HSSFCell cellTotal008 = rowTotalAll.createCell(k); cellTotal008.setCellStyle(styleAlignRightBorderAllNumber); } sheet1.addMergedRegion( CellRangeAddress.valueOf("I" + (count + r + 3) + ":M" + (count + r + 3) + "")); } } else if ("Owner".equals(listOver.get(r).getGroup())) { bahtTotalAll = bahtTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getBath())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getBath()))); jpyTotalAll = jpyTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getJpy())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getJpy()))); usdTotalAll = usdTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getUsd())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getUsd()))); recamtTotalAll = recamtTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getRecamt())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getRecamt()))); if (temp.equals(listOver.get(r).getOwnername())) { // equal type if (r != (listOver.size() - 1)) { // check not last row HSSFRow row = sheet1.createRow(r + count); createCell(row, listOver, r, styleAlignRightBorderAllNumber, styleAlignRightBorderAll); sheet1.autoSizeColumn(13); } else { HSSFRow row = sheet1.createRow(r + count); createCell(row, listOver, r, styleAlignRightBorderAllNumber, styleAlignRightBorderAll); sheet1.autoSizeColumn(13); } } else { // not equal type // Start New Row (Group) String totalthb = "SUM(D" + start + ":D" + (r + count) + ")"; String totaljpy = "SUM(E" + start + ":E" + (r + count) + ")"; String totalusd = "SUM(F" + start + ":F" + (r + count) + ")"; String totalrecamt = "SUM(H" + start + ":H" + (r + count) + ")"; // sumThbAll += ",D"+(count+r+1); // sumJpyAll += ",E"+(count+r+1); // sumUsdAll += ",F"+(count+r+1); // sumRecAmtAll += ",H"+(count+r+1); start = count + r + 3; HSSFRow row00 = sheet1.createRow(r + count); HSSFCell cell00 = row00.createCell(0); cell00.setCellValue(""); cell00.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell001 = row00.createCell(1); cell001.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell002 = row00.createCell(2); cell002.setCellStyle(styleAlignRightBorderAllDetailTable); sheet1.addMergedRegion( CellRangeAddress.valueOf("A" + (count + r + 1) + ":C" + (count + r + 1) + "")); HSSFCell cell003 = row00.createCell(3); cell003.setCellFormula(totalthb); cell003.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell004 = row00.createCell(4); cell004.setCellFormula(totaljpy); cell004.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell005 = row00.createCell(5); cell005.setCellFormula(totalusd); cell005.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell006 = row00.createCell(6); cell006.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell007 = row00.createCell(7); cell007.setCellFormula(totalrecamt); cell007.setCellStyle(styleAlignRightBorderAllNumber); for (int k = 8; k < 13; k++) { HSSFCell cell008 = row00.createCell(k); cell008.setCellStyle(styleAlignRightBorderAllNumber); } sheet1.addMergedRegion( CellRangeAddress.valueOf("I" + (count + r + 1) + ":M" + (count + r + 1) + "")); HSSFRow row0 = sheet1.createRow(r + count + 1); HSSFCell cell = row0.createCell(0); cell.setCellValue(listOver.get(r).getOwnername()); cell.setCellStyle(styleAlignRightBorderAllDetailTable); String add = "A" + (r + count + 2) + ":M" + (r + count + 2) + ""; sheet1.addMergedRegion(CellRangeAddress.valueOf(add)); row0.createCell(12).setCellStyle(styleAlignRightBorderAllColor); HSSFRow row122 = sheet1.createRow(r + count + 2); createCell(row122, listOver, r, styleAlignRightBorderAllNumber, styleAlignRightBorderAll); sheet1.autoSizeColumn(13); count = count + 2; } temp = listOver.get(r).getOwnername(); if (r == (listOver.size() - 1)) { String totalthb = "SUM(D" + start + ":D" + (count + r + 1) + ")"; String totaljpy = "SUM(E" + start + ":E" + (count + r + 1) + ")"; String totalusd = "SUM(F" + start + ":F" + (count + r + 1) + ")"; String totalrecamt = "SUM(H" + start + ":H" + (count + r + 1) + ")"; // sumThbAll += ",D"+(count+r+2); // sumJpyAll += ",E"+(count+r+2); // sumUsdAll += ",F"+(count+r+2); // sumRecAmtAll += ",H"+(count+r+2); HSSFRow row00 = sheet1.createRow(count + r + 1); HSSFCell cell00 = row00.createCell(0); cell00.setCellValue(""); cell00.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell001 = row00.createCell(1); cell001.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell002 = row00.createCell(2); cell002.setCellStyle(styleAlignRightBorderAllDetailTable); sheet1.addMergedRegion( CellRangeAddress.valueOf("A" + (count + r + 2) + ":C" + (count + r + 2) + "")); HSSFCell cell003 = row00.createCell(3); cell003.setCellFormula(totalthb); cell003.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell004 = row00.createCell(4); cell004.setCellFormula(totaljpy); cell004.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell005 = row00.createCell(5); cell005.setCellFormula(totalusd); cell005.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell006 = row00.createCell(6); cell006.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell007 = row00.createCell(7); cell007.setCellFormula(totalrecamt); cell007.setCellStyle(styleAlignRightBorderAllNumber); for (int k = 8; k < 13; k++) { HSSFCell cell008 = row00.createCell(k); cell008.setCellStyle(styleAlignRightBorderAllNumber); } sheet1.addMergedRegion( CellRangeAddress.valueOf("I" + (count + r + 2) + ":M" + (count + r + 2) + "")); HSSFRow rowTotalAll = sheet1.createRow(count + r + 2); HSSFCell cellTotal00 = rowTotalAll.createCell(0); cellTotal00.setCellValue("Total"); cellTotal00.setCellStyle(total); HSSFCell cellTotal001 = rowTotalAll.createCell(1); cellTotal001.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cellTotal002 = rowTotalAll.createCell(2); cellTotal002.setCellStyle(styleAlignRightBorderAllDetailTable); sheet1.addMergedRegion( CellRangeAddress.valueOf("A" + (count + r + 3) + ":C" + (count + r + 3) + "")); HSSFCell cellTotal003 = rowTotalAll.createCell(3); cellTotal003.setCellValue(bahtTotalAll.doubleValue()); cellTotal003.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal004 = rowTotalAll.createCell(4); cellTotal004.setCellValue(jpyTotalAll.doubleValue()); cellTotal004.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal005 = rowTotalAll.createCell(5); cellTotal005.setCellValue(usdTotalAll.doubleValue()); cellTotal005.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal006 = rowTotalAll.createCell(6); cellTotal006.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal007 = rowTotalAll.createCell(7); cellTotal007.setCellValue(recamtTotalAll.doubleValue()); cellTotal007.setCellStyle(styleAlignRightBorderAllNumber); for (int k = 8; k < 13; k++) { HSSFCell cellTotal008 = rowTotalAll.createCell(k); cellTotal008.setCellStyle(styleAlignRightBorderAllNumber); } sheet1.addMergedRegion( CellRangeAddress.valueOf("I" + (count + r + 3) + ":M" + (count + r + 3) + "")); } } } else { bahtTotalAll = bahtTotalAll .add("".equalsIgnoreCase(String.valueOf(listOver.get(r).getBath())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getBath()))); jpyTotalAll = jpyTotalAll .add("".equalsIgnoreCase(String.valueOf(listOver.get(r).getJpy())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getJpy()))); usdTotalAll = usdTotalAll .add("".equalsIgnoreCase(String.valueOf(listOver.get(r).getUsd())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getUsd()))); recamtTotalAll = recamtTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getRecamt())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getRecamt()))); HSSFRow row0 = sheet1.createRow(count + r); if ("Owner".equals(listOver.get(r).getGroup())) { HSSFCell cell = row0.createCell(0); cell.setCellValue(listOver.get(r).getOwnername()); cell.setCellStyle(styleAlignRightBorderAllDetailTable); } else if ("Agent".equals(listOver.get(r).getGroup())) { HSSFCell cell = row0.createCell(0); cell.setCellValue(listOver.get(r).getInvto()); cell.setCellStyle(styleAlignRightBorderAllDetailTable); } String add = "A" + (count + r + 1) + ":M" + (count + r + 1) + ""; sheet1.addMergedRegion(CellRangeAddress.valueOf(add)); row0.createCell(12).setCellStyle(styleAlignRightBorderAllColor); HSSFRow row = sheet1.createRow(count + r + 1); createCell(row, listOver, num, styleAlignRightBorderAllNumber, styleAlignRightBorderAll); sheet1.autoSizeColumn(13); count = count + 1; } } for (int i = 0; i < 30; i++) { sheet1.autoSizeColumn(i); } } }
From source file:com.smi.travel.controller.excel.checking.OverdueSummaryExcel.java
private void createCell(HSSFRow row, List<OverdueSummartExcel> listAgent, int num, HSSFCellStyle styleNumber, HSSFCellStyle styleDetail) {/*w w w . ja v a 2 s . co m*/ UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFCellStyle styleNum = styleNumber; HSSFCellStyle styleDe = styleDetail; HSSFCell cell1 = row.createCell(0); cell1.setCellValue(listAgent.get(num).getInvno()); cell1.setCellStyle(styleDe); HSSFCell cell2 = row.createCell(1); cell2.setCellValue(listAgent.get(num).getDate()); cell2.setCellStyle(styleDe); HSSFCell cell3 = row.createCell(2); cell3.setCellValue(listAgent.get(num).getDetail()); cell3.setCellStyle(styleDe); HSSFCell cell4 = row.createCell(3); // cell4.setCellValue(listAgent.get(num).getBath()); cell4.setCellValue("".equalsIgnoreCase(String.valueOf(listAgent.get(num).getBath())) ? 0 : (new BigDecimal(listAgent.get(num).getBath())).doubleValue()); cell4.setCellStyle(styleNum); HSSFCell cell55 = row.createCell(4); // cell55.setCellValue(listAgent.get(num).getJpy()); cell55.setCellValue("".equalsIgnoreCase(String.valueOf(listAgent.get(num).getJpy())) ? 0 : (new BigDecimal(listAgent.get(num).getJpy())).doubleValue()); cell55.setCellStyle(styleNum); HSSFCell cell5 = row.createCell(5); // cell5.setCellValue(listAgent.get(num).getUsd()); cell5.setCellValue("".equalsIgnoreCase(String.valueOf(listAgent.get(num).getUsd())) ? 0 : (new BigDecimal(listAgent.get(num).getUsd())).doubleValue()); cell5.setCellStyle(styleNum); HSSFCell cell6 = row.createCell(6); cell6.setCellValue(listAgent.get(num).getRecno()); cell6.setCellStyle(styleNum); HSSFCell cell7 = row.createCell(7); // cell7.setCellValue(listAgent.get(num).getRecamt()); cell7.setCellValue("".equalsIgnoreCase(String.valueOf(listAgent.get(num).getRecamt())) ? 0 : (new BigDecimal(listAgent.get(num).getRecamt())).doubleValue()); cell7.setCellStyle(styleNum); HSSFCell cell8 = row.createCell(8); cell8.setCellValue(listAgent.get(num).getDepartment()); cell8.setCellStyle(styleDe); HSSFCell cell9 = row.createCell(9); cell9.setCellValue(listAgent.get(num).getCredit()); cell9.setCellStyle(styleDe); HSSFCell cell10 = row.createCell(10); cell10.setCellValue(listAgent.get(num).getRefno()); cell10.setCellStyle(styleDe); HSSFCell cell11 = row.createCell(11); cell11.setCellValue(listAgent.get(num).getDuedate()); cell11.setCellStyle(styleDe); HSSFCell cell12 = row.createCell(12); cell12.setCellValue(listAgent.get(num).getOverduesstatus()); cell12.setCellStyle(styleDe); }
From source file:com.smi.travel.controller.excel.checking.OverdueSummaryExcel.java
private void getStockInvoiceSummary(HSSFWorkbook wb, List stockInvoiceSummary) { String sheetStockInvoiceSummary = "StockInvoiceSummary";// name of sheet UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFSheet sheet = wb.createSheet(sheetStockInvoiceSummary); HSSFDataFormat currency = wb.createDataFormat(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC25.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC25.setVerticalAlignment(styleC25.VERTICAL_CENTER); styleC25.setDataFormat(currency.getFormat("#,##0.00")); HSSFCellStyle styleC26 = wb.createCellStyle(); styleC26.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC26.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC26.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC26.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC26.setDataFormat(currency.getFormat("#,##0")); styleC26.setAlignment(styleC22.ALIGN_CENTER); HSSFCellStyle styleC27 = wb.createCellStyle(); styleC27.setAlignment(styleC27.ALIGN_RIGHT); styleC27.setDataFormat(currency.getFormat("#,##0.00")); HSSFCellStyle styleC28 = wb.createCellStyle(); styleC28.setAlignment(styleC28.ALIGN_CENTER); styleC28.setDataFormat(currency.getFormat("#,##0")); HSSFCellStyle styleC29 = wb.createCellStyle(); styleC29.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC29.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC29.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC29.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC29.setVerticalAlignment(styleC29.VERTICAL_CENTER); HSSFCellStyle styleC30 = wb.createCellStyle(); styleC30.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC30.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC30.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC30.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC30.setAlignment(styleC30.ALIGN_CENTER); styleC30.setVerticalAlignment(styleC30.VERTICAL_CENTER); HSSFCellStyle styleC31 = wb.createCellStyle(); styleC31.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC31.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC31.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC31.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC31.setDataFormat(currency.getFormat("#,##0.00")); HSSFCellStyle styleC32 = wb.createCellStyle(); styleC32.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC32.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC32.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC32.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC32.setAlignment(styleC32.ALIGN_CENTER); styleC32.setVerticalAlignment(styleC32.VERTICAL_CENTER); styleC32.setWrapText(true);/*w ww . j a va 2s. co m*/ HSSFCellStyle styleC33 = wb.createCellStyle(); styleC33.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC33.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC33.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC33.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC33.setAlignment(styleC33.ALIGN_LEFT); styleC33.setVerticalAlignment(styleC33.VERTICAL_CENTER); styleC33.setWrapText(true); StockInvoiceSummaryView dataheader = (StockInvoiceSummaryView) stockInvoiceSummary.get(0); // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheet.createRow(0); HSSFCell cell1 = row1.createCell(0); cell1.setCellValue("Stock Invoice Summary"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cell1.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Row 2 HSSFRow row2 = sheet.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue("Product : "); cell21.setCellStyle(styleC21); HSSFCell cell22 = row2.createCell(1); cell22.setCellValue(dataheader.getProductHeader()); cell22.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2")); HSSFCell cell23 = row2.createCell(4); cell23.setCellValue("Inv To : "); cell23.setCellStyle(styleC21); HSSFCell cell24 = row2.createCell(5); cell24.setCellValue(dataheader.getInvtoHeader()); cell24.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("F2:I2")); // Row 3 HSSFRow row3 = sheet.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Effective Date : "); cell31.setCellStyle(styleC21); HSSFCell cell32 = row3.createCell(1); cell32.setCellValue(dataheader.getEffectivedateHeader()); cell32.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3")); HSSFCell cell33 = row3.createCell(4); cell33.setCellValue("Invoice Date : "); cell33.setCellStyle(styleC21); HSSFCell cell34 = row3.createCell(5); cell34.setCellValue(dataheader.getInvoicedateHeader()); cell34.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("F3:I3")); // Row 4 HSSFRow row4 = sheet.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Add Date : "); cell41.setCellStyle(styleC21); HSSFCell cell42 = row4.createCell(1); cell42.setCellValue(dataheader.getAdddateHeader()); cell42.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4")); // Header Table HSSFCellStyle styleC3 = wb.createCellStyle(); styleC3.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3.setAlignment(styleC3.ALIGN_CENTER); styleC3.setVerticalAlignment(styleC3.VERTICAL_CENTER); styleC3.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styletop = wb.createCellStyle(); styletop.setBorderTop(HSSFCellStyle.BORDER_THIN); styletop.setBorderLeft(HSSFCellStyle.BORDER_THIN); styletop.setBorderRight(HSSFCellStyle.BORDER_THIN); styletop.setAlignment(styletop.ALIGN_CENTER); styletop.setFont(excelFunction.getHeaderTable(wb.createFont())); styletop.setVerticalAlignment(styletop.VERTICAL_CENTER); HSSFCellStyle stylebottom = wb.createCellStyle(); stylebottom.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylebottom.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylebottom.setBorderRight(HSSFCellStyle.BORDER_THIN); stylebottom.setAlignment(styletop.ALIGN_CENTER); stylebottom.setFont(excelFunction.getHeaderTable(wb.createFont())); stylebottom.setVerticalAlignment(styletop.VERTICAL_CENTER); HSSFRow row5 = sheet.createRow(5); HSSFCell cell51 = row5.createCell(0); cell51.setCellValue("Item No"); cell51.setCellStyle(styletop); HSSFCell cell62 = row5.createCell(1); cell62.setCellValue("Item type"); cell62.setCellStyle(styletop); HSSFCell cell63 = row5.createCell(2); cell63.setCellValue("Ref No"); cell63.setCellStyle(styletop); HSSFCell cell64 = row5.createCell(3); cell64.setCellValue("Owner"); cell64.setCellStyle(styletop); HSSFCell cell65 = row5.createCell(4); cell65.setCellValue("Inv No"); cell65.setCellStyle(styletop); HSSFCell cell66 = row5.createCell(5); cell66.setCellValue("Inv Name"); cell66.setCellStyle(styletop); sheet.autoSizeColumn(5); HSSFCell cell67 = row5.createCell(6); cell67.setCellValue("Inv Date"); cell67.setCellStyle(styletop); HSSFCell cell68 = row5.createCell(7); cell68.setCellValue("Cost"); cell68.setCellStyle(styletop); HSSFCell cell69 = row5.createCell(8); cell69.setCellValue("Sale Price"); cell69.setCellStyle(styletop); HSSFCell cell610 = row5.createCell(9); cell610.setCellValue("Profit"); cell610.setCellStyle(styletop); HSSFCell cell611 = row5.createCell(10); cell611.setCellValue("Stock No"); cell611.setCellStyle(styletop); //Detail of Table int count = 6; boolean isMerge = false; int hMerge = 7; int countMerge = 0; for (int i = 0; i < stockInvoiceSummary.size(); i++) { StockInvoiceSummaryView data = (StockInvoiceSummaryView) stockInvoiceSummary.get(i); StockInvoiceSummaryView dataTemp = new StockInvoiceSummaryView(); if (i != stockInvoiceSummary.size() - 1) { dataTemp = (StockInvoiceSummaryView) stockInvoiceSummary.get(i + 1); } else { dataTemp = null; } HSSFRow row = sheet.createRow(count + i); String id = (data.getId() != null ? data.getId() : ""); String idTemp = (dataTemp != null ? dataTemp.getId() : ""); countMerge++; HSSFCell celldata0 = row.createCell(0); celldata0.setCellValue( !"".equalsIgnoreCase(data.getItemno()) && data.getItemno() != null ? data.getItemno() : ""); celldata0.setCellStyle(styleC30); HSSFCell celldata1 = row.createCell(1); celldata1.setCellValue( !"".equalsIgnoreCase(data.getItemtype()) && data.getItemtype() != null ? data.getItemtype() : ""); celldata1.setCellStyle(styleC30); HSSFCell celldata2 = row.createCell(2); celldata2.setCellValue( !"".equalsIgnoreCase(data.getRefno()) && data.getRefno() != null ? data.getRefno() : ""); celldata2.setCellStyle(styleC30); HSSFCell celldata3 = row.createCell(3); celldata3.setCellValue( !"".equalsIgnoreCase(data.getOwner()) && data.getOwner() != null ? data.getOwner() : ""); celldata3.setCellStyle(styleC30); HSSFCell celldata4 = row.createCell(4); celldata4.setCellValue( !"".equalsIgnoreCase(data.getInvno()) && data.getInvno() != null ? data.getInvno() : ""); celldata4.setCellStyle(styleC33); HSSFCell celldata5 = row.createCell(5); celldata5.setCellValue( !"".equalsIgnoreCase(data.getInvname()) && data.getInvname() != null ? data.getInvname() : ""); celldata5.setCellStyle(styleC29); HSSFCell celldata6 = row.createCell(6); celldata6.setCellValue( !"".equalsIgnoreCase(data.getInvdate()) && data.getInvdate() != null ? data.getInvdate() : ""); celldata6.setCellStyle(styleC33); HSSFCell celldata7 = row.createCell(7); celldata7.setCellValue(!"".equalsIgnoreCase(data.getCost()) && data.getCost() != null ? new BigDecimal(data.getCost()).doubleValue() : 0); celldata7.setCellStyle(styleC25); HSSFCell celldata8 = row.createCell(8); celldata8.setCellValue(!"".equalsIgnoreCase(data.getSaleprice()) && data.getSaleprice() != null ? new BigDecimal(data.getSaleprice()).doubleValue() : 0); celldata8.setCellStyle(styleC25); HSSFCell celldata9 = row.createCell(9); celldata9.setCellValue(!"".equalsIgnoreCase(data.getProfit()) && data.getProfit() != null ? new BigDecimal(data.getProfit()).doubleValue() : 0); celldata9.setCellStyle(styleC25); HSSFCell celldata10 = row.createCell(10); celldata10.setCellValue( !"".equalsIgnoreCase(data.getStockno()) && data.getStockno() != null ? data.getStockno() : ""); celldata10.setCellStyle(styleC29); // HSSFCell celldata11 = row.createCell(11); // celldata11.setCellValue(!"".equalsIgnoreCase(data.getId()) && data.getId() != null ? data.getId() : ""); // celldata11.setCellStyle(styleC29); if (!id.equalsIgnoreCase(idTemp) && (!"".equalsIgnoreCase(id) || !"".equalsIgnoreCase(idTemp))) { if (countMerge > 1) { sheet.addMergedRegion( CellRangeAddress.valueOf("H" + (hMerge) + ":H" + (hMerge + (countMerge - 1)))); sheet.addMergedRegion( CellRangeAddress.valueOf("I" + (hMerge) + ":I" + (hMerge + (countMerge - 1)))); sheet.addMergedRegion( CellRangeAddress.valueOf("J" + (hMerge) + ":J" + (hMerge + (countMerge - 1)))); sheet.addMergedRegion( CellRangeAddress.valueOf("K" + (hMerge) + ":K" + (hMerge + (countMerge - 1)))); } hMerge = 7 + i + 1; countMerge = 0; } // HSSFCell celldata11 = row.createCell(11); // celldata11.setCellValue(!"".equalsIgnoreCase(data.getId()) && data.getId() != null ? data.getId() : ""); // celldata11.setCellStyle(styleC29); } for (int j = 0; j < 11; j++) { sheet.autoSizeColumn(j); } // sheet.setColumnWidth(0, 256*15); // sheet.setColumnWidth(1, 256*15); // sheet.setColumnWidth(2, 256*15); sheet.setColumnWidth(6, 256 * 10); sheet.setColumnWidth(7, 256 * 15); sheet.setColumnWidth(8, 256 * 15); sheet.setColumnWidth(9, 256 * 15); sheet.setColumnWidth(10, 256 * 60); // sheet.setColumnWidth(11, 256*15); // sheet.setColumnWidth(12, 256*15); // sheet.setColumnWidth(13, 256*15); // sheet.setColumnWidth(14, 256*15); // sheet.setColumnWidth(15, 256*15); // sheet.setColumnWidth(16, 256*15); // sheet.setColumnWidth(17, 256*15); // sheet.setColumnWidth(18, 256*15); // sheet.setColumnWidth(19, 256*15); }
From source file:com.smi.travel.controller.excel.checking.OverdueSummaryExcel.java
private void getStockNonInvoiceSummary(HSSFWorkbook wb, List stockNonInvoiceSummary) { String sheetStockNonInvoiceSummary = "StockNonInvoiceSummary";// name of sheet UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFSheet sheet = wb.createSheet(sheetStockNonInvoiceSummary); HSSFDataFormat currency = wb.createDataFormat(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC25.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC25.setVerticalAlignment(styleC25.VERTICAL_CENTER); styleC25.setDataFormat(currency.getFormat("#,##0.00")); HSSFCellStyle styleC26 = wb.createCellStyle(); styleC26.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC26.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC26.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC26.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC26.setDataFormat(currency.getFormat("#,##0")); styleC26.setAlignment(styleC26.ALIGN_CENTER); HSSFCellStyle styleC27 = wb.createCellStyle(); styleC27.setAlignment(styleC27.ALIGN_RIGHT); styleC27.setDataFormat(currency.getFormat("#,##0.00")); HSSFCellStyle styleC28 = wb.createCellStyle(); styleC28.setAlignment(styleC28.ALIGN_CENTER); styleC28.setDataFormat(currency.getFormat("#,##0")); HSSFCellStyle styleC29 = wb.createCellStyle(); styleC29.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC29.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC29.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC29.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC29.setVerticalAlignment(styleC29.VERTICAL_CENTER); HSSFCellStyle styleC30 = wb.createCellStyle(); styleC30.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC30.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC30.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC30.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC30.setAlignment(styleC30.ALIGN_CENTER); styleC30.setVerticalAlignment(styleC30.VERTICAL_CENTER); HSSFCellStyle styleC31 = wb.createCellStyle(); styleC31.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC31.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC31.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC31.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC31.setDataFormat(currency.getFormat("#,##0.00")); HSSFCellStyle styleC32 = wb.createCellStyle(); styleC32.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC32.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC32.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC32.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC32.setAlignment(styleC32.ALIGN_CENTER); styleC32.setVerticalAlignment(styleC32.VERTICAL_CENTER); styleC32.setWrapText(true);/* www . j ava 2 s . c o m*/ StockNonInvoiceSummaryView dataheader = (StockNonInvoiceSummaryView) stockNonInvoiceSummary.get(0); // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheet.createRow(0); HSSFCell cell1 = row1.createCell(0); cell1.setCellValue("Stock Non Invoice Summary"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cell1.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Row 2 HSSFRow row2 = sheet.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue("Product : "); cell21.setCellStyle(styleC21); HSSFCell cell22 = row2.createCell(1); cell22.setCellValue(dataheader.getProductHeader()); cell22.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2")); HSSFCell cell23 = row2.createCell(4); cell23.setCellValue("Invoice Sup : "); cell23.setCellStyle(styleC21); HSSFCell cell24 = row2.createCell(5); cell24.setCellValue(dataheader.getInvoicesupHeader()); cell24.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("F2:H2")); // Row 3 HSSFRow row3 = sheet.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Effective Date : "); cell31.setCellStyle(styleC21); HSSFCell cell32 = row3.createCell(1); cell32.setCellValue(dataheader.getEffectivedateHeader()); cell32.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3")); HSSFCell cell33 = row3.createCell(4); cell33.setCellValue("Pay Date : "); cell33.setCellStyle(styleC21); HSSFCell cell34 = row3.createCell(5); cell34.setCellValue(dataheader.getPaydateHeader()); cell34.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("F3:H3")); // Row 4 HSSFRow row4 = sheet.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Add Date : "); cell41.setCellStyle(styleC21); HSSFCell cell42 = row4.createCell(1); cell42.setCellValue(dataheader.getAdddateHeader()); cell42.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4")); // Header Table HSSFCellStyle styleC3 = wb.createCellStyle(); styleC3.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3.setAlignment(styleC3.ALIGN_CENTER); styleC3.setVerticalAlignment(styleC3.VERTICAL_CENTER); styleC3.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styletop = wb.createCellStyle(); styletop.setBorderTop(HSSFCellStyle.BORDER_THIN); styletop.setBorderLeft(HSSFCellStyle.BORDER_THIN); styletop.setBorderRight(HSSFCellStyle.BORDER_THIN); styletop.setAlignment(styletop.ALIGN_CENTER); styletop.setFont(excelFunction.getHeaderTable(wb.createFont())); styletop.setVerticalAlignment(styletop.VERTICAL_CENTER); HSSFCellStyle stylebottom = wb.createCellStyle(); stylebottom.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylebottom.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylebottom.setBorderRight(HSSFCellStyle.BORDER_THIN); stylebottom.setAlignment(styletop.ALIGN_CENTER); stylebottom.setFont(excelFunction.getHeaderTable(wb.createFont())); stylebottom.setVerticalAlignment(styletop.VERTICAL_CENTER); HSSFRow row5 = sheet.createRow(5); HSSFCell cell51 = row5.createCell(0); cell51.setCellValue("Item No"); cell51.setCellStyle(styletop); HSSFCell cell62 = row5.createCell(1); cell62.setCellValue("Item Type"); cell62.setCellStyle(styletop); HSSFCell cell63 = row5.createCell(2); cell63.setCellValue("Ref No"); cell63.setCellStyle(styletop); HSSFCell cell64 = row5.createCell(3); cell64.setCellValue("Owner"); cell64.setCellStyle(styletop); HSSFCell cell65 = row5.createCell(4); cell65.setCellValue("Pay No"); cell65.setCellStyle(styletop); HSSFCell cell66 = row5.createCell(5); cell66.setCellValue("Pay Date"); cell66.setCellStyle(styletop); HSSFCell cell67 = row5.createCell(6); cell67.setCellValue("Invoice Sup"); cell67.setCellStyle(styletop); HSSFCell cell68 = row5.createCell(7); cell68.setCellValue("Cost"); cell68.setCellStyle(styletop); HSSFCell cell69 = row5.createCell(8); cell69.setCellValue("Stock No"); cell69.setCellStyle(styletop); //Detail of Table int count = 6; for (int i = 0; i < stockNonInvoiceSummary.size(); i++) { StockNonInvoiceSummaryView data = (StockNonInvoiceSummaryView) stockNonInvoiceSummary.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell celldata0 = row.createCell(0); celldata0.setCellValue( !"".equalsIgnoreCase(data.getItemno()) && data.getItemno() != null ? data.getItemno() : ""); celldata0.setCellStyle(styleC29); HSSFCell celldata1 = row.createCell(1); celldata1.setCellValue( !"".equalsIgnoreCase(data.getItemtype()) && data.getItemtype() != null ? data.getItemtype() : ""); celldata1.setCellStyle(styleC30); HSSFCell celldata2 = row.createCell(2); celldata2.setCellValue( !"".equalsIgnoreCase(data.getRefno()) && data.getRefno() != null ? data.getRefno() : ""); celldata2.setCellStyle(styleC30); HSSFCell celldata3 = row.createCell(3); celldata3.setCellValue( !"".equalsIgnoreCase(data.getOwner()) && data.getOwner() != null ? data.getOwner() : ""); celldata3.setCellStyle(styleC29); HSSFCell celldata4 = row.createCell(4); celldata4.setCellValue( !"".equalsIgnoreCase(data.getPayno()) && data.getPayno() != null ? data.getPayno() : ""); celldata4.setCellStyle(styleC32); HSSFCell celldata5 = row.createCell(5); celldata5.setCellValue( !"".equalsIgnoreCase(data.getPaydate()) && data.getPaydate() != null ? data.getPaydate() : ""); celldata5.setCellStyle(styleC30); HSSFCell celldata6 = row.createCell(6); celldata6.setCellValue(!"".equalsIgnoreCase(data.getInvoicesup()) && data.getInvoicesup() != null ? data.getInvoicesup() : ""); celldata6.setCellStyle(styleC29); HSSFCell celldata7 = row.createCell(7); celldata7.setCellValue(!"".equalsIgnoreCase(data.getCost()) && data.getCost() != null ? new BigDecimal(data.getCost()).doubleValue() : 0); celldata7.setCellStyle(styleC25); HSSFCell celldata8 = row.createCell(8); celldata8.setCellValue( !"".equalsIgnoreCase(data.getStockno()) && data.getStockno() != null ? data.getStockno() : ""); celldata8.setCellStyle(styleC29); HSSFCell celldata9 = row.createCell(9); celldata9 .setCellValue(!"".equalsIgnoreCase(data.getPaymentstockno()) && data.getPaymentstockno() != null ? data.getPaymentstockno() : ""); celldata9.setCellStyle(styleC29); } for (int j = 0; j < 9; j++) { sheet.autoSizeColumn(j); } // sheet.setColumnWidth(0, 256*15); // sheet.setColumnWidth(1, 256*15); // sheet.setColumnWidth(2, 256*15); // sheet.setColumnWidth(3, 256*15); // sheet.setColumnWidth(4, 256*25); // sheet.setColumnWidth(5, 256*25); // sheet.setColumnWidth(6, 256*15); // sheet.setColumnWidth(10, 256*15); // sheet.setColumnWidth(11, 256*15); // sheet.setColumnWidth(12, 256*15); // sheet.setColumnWidth(13, 256*15); // sheet.setColumnWidth(14, 256*15); // sheet.setColumnWidth(15, 256*15); // sheet.setColumnWidth(16, 256*15); // sheet.setColumnWidth(17, 256*15); // sheet.setColumnWidth(18, 256*15); // sheet.setColumnWidth(19, 256*15); }
From source file:com.smi.travel.datalayer.view.dao.impl.APNirvanaImpl.java
private String genReport(List<APNirvana> apDataList, String fullFileName, List<APNirvana> APList) { SimpleDateFormat df = new SimpleDateFormat(); df.applyPattern("dd/MM/yyyy"); String status = ""; UtilityFunction util = new UtilityFunction(); try {//from w ww . ja v a2s.c o m HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); int rownum = 0; for (APNirvana ap : apDataList) { HSSFRow dataRow = sheet.createRow(rownum++); int cellnum = 0; HSSFCell cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getRefinvoiceno()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getIntreference()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getVendorid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getVendorname()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getDivisionid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getProjectid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getTranscode()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getTransdate() == null ? "" : util.ConvertString( df.format(util.convertStringToDate(String.valueOf(ap.getTransdate()))))); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getDuedate() == null ? "" : util.ConvertString(df.format(util.convertStringToDate(String.valueOf(ap.getDuedate()))))); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getCurrencyid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getHomerate())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getForeignrate())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getBasevatamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getBasevathmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getVatamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getVathmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getTransamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getTranshmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getVatflag()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getVatid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getWhtflag()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getWhtid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getBasewhtamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getBasewhthmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getWhtamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getWhthmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getYear() == null ? "" : ap.getYear().toString()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPeriod() == null ? "" : ap.getPeriod().toString()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getNote()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount1()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision1()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject1()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt1())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt1())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount2()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision2()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject2()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt2())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt2())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount3()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision3()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject3()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt3())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt3())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount4()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision4()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject4()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt4())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt4())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount5()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision5()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject5()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt5())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt5())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount6()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision6()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject6()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt6())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt6())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount7()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision7()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject7()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt7())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt7())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount8()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision8()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject8()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt8())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt8())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount9()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision9()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject9()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt9())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt9())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount10()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision10()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject10()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt10())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt10())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getService()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getApaccount()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPrefix()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getVoucherno()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getTaxid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getVendor_branch() == null ? "0" : ap.getVendor_branch().toString()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getCompany_branch()); // for(int j =0;j<100;j++){ // sheet.autoSizeColumn(j); // } } FileOutputStream out = new FileOutputStream(new File(fullFileName + ".xls")); workbook.write(out); out.close(); status = "success"; } catch (Exception e) { e.printStackTrace(); for (APNirvana ap : APList) { if (!"".equals(status)) { status += ", "; } status += ap.getPayment_detail_id(); } } return status; }
From source file:com.smi.travel.datalayer.view.dao.impl.ARNirvanaImpl.java
private String genReport(List<ARNirvana> arDataList, String fullFileName, List<ARNirvana> ARList) { UtilityFunction util = new UtilityFunction(); String status = ""; SimpleDateFormat df = new SimpleDateFormat(); df.applyPattern("dd/MM/yyyy"); try {//from w w w .jav a2 s . com HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); int rownum = 0; for (ARNirvana ar : arDataList) { HSSFRow dataRow = sheet.createRow(rownum++); int cellnum = 0; HSSFCell cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getIntreference()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesmanid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getCustomerid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getCustomername()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getDivisionid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getProjectid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getTranscode()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getTransdate() == null ? "" : util.ConvertString( df.format(util.convertStringToDate(String.valueOf(ar.getTransdate()))))); cell = dataRow.createCell(cellnum++); if (ar.getDuedate() == null) { cell.setCellValue(""); } else { cell.setCellValue(util .ConvertString(df.format(util.convertStringToDate(String.valueOf(ar.getDuedate()))))); } cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getCurrencyid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getHomerate())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getForeignrate())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSalesamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSaleshmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getVatamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getVathmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getAramt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getArhmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getVatflag()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getVatid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getWhtflag()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getWhtid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getBasewhtamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getBasewhthmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getWhtamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getWhthmamt())); cell = dataRow.createCell(cellnum++); if (ar.getYear() == null) { cell.setCellValue(""); } else { cell.setCellValue(ar.getYear()); } cell = dataRow.createCell(cellnum++); if (ar.getPeriod() == null) { cell.setCellValue(""); } else { cell.setCellValue(ar.getPeriod()); } cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getNote()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesaccount1()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesdivision1()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesproject1()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSalesamt1())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSaleshmamt1())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesaccount2()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesdivision2()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesproject2()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSalesamt2())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSaleshmamt2())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesaccount3()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesdivision3()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesproject3()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSalesamt3())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSaleshmamt3())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getService()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getAraccount()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getPrefix()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getDocumentno()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getArtrans()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getCust_taxid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getCust_branch() == null ? "" : ar.getCust_branch().toString()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getCompany_branch()); // for(int j =0;j<100;j++){ // sheet.autoSizeColumn(j); // } } FileOutputStream out = new FileOutputStream(new File(fullFileName + ".xls")); workbook.write(out); out.close(); status = "success"; } catch (Exception e) { e.printStackTrace(); for (ARNirvana ar : ARList) { if (!"".equals(status)) { status += ", "; } status += ar.getReceive_detail_id(); } } return status; }
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportTravoxReport(List<MainMigrateModel> list) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setDataFormat(currency.getFormat("#,##0.00")); HSSFSheet sheet = wb.createSheet("TravoxReport"); HSSFRow row2 = sheet.createRow(0);//from w ww .j ava 2 s .c o m HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("GJ"); cell20.setCellStyle(styleC3Center); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("PAY NO"); cell21.setCellStyle(styleC3Center); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("NAME"); cell22.setCellStyle(styleC3Center); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("AP CODE"); cell23.setCellStyle(styleC3Center); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("REFDOC"); cell24.setCellStyle(styleC3Center); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("SYSTEM_DATE"); cell25.setCellStyle(styleC3Center); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("DUE DATE"); cell26.setCellStyle(styleC3Center); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("INVOICE NUM"); cell27.setCellStyle(styleC3Center); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("MAIN DESCRIPTION"); cell28.setCellStyle(styleC3Center); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("CODE"); cell29.setCellStyle(styleC3Center); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("TYPE PRODUCT"); cell30.setCellStyle(styleC3Center); HSSFCell cell31 = row2.createCell(11); cell31.setCellValue("DESCRIPTION"); cell31.setCellStyle(styleC3Center); HSSFCell cell32 = row2.createCell(12); cell32.setCellValue("TOTAL AMOUNT"); cell32.setCellStyle(styleC3Center); HSSFCell cell33 = row2.createCell(13); cell33.setCellValue("TOTAL VAT"); cell33.setCellStyle(styleC3Center); HSSFCell cell34 = row2.createCell(14); cell34.setCellValue("CUR"); cell34.setCellStyle(styleC3Center); HSSFCell cell35 = row2.createCell(15); cell35.setCellValue("AMOUNT"); cell35.setCellStyle(styleC3Center); HSSFCell cell36 = row2.createCell(16); cell36.setCellValue("DEPARTMENT"); cell36.setCellStyle(styleC3Center); HSSFCell cell37 = row2.createCell(17); cell37.setCellValue("ACC NO"); cell37.setCellStyle(styleC3Center); HSSFCell cell38 = row2.createCell(18); cell38.setCellValue("EXPENSE DATE"); cell38.setCellStyle(styleC3Center); HSSFCell cell39 = row2.createCell(19); cell39.setCellValue("VOUCHER NO"); cell39.setCellStyle(styleC3Center); HSSFCell cell40 = row2.createCell(20); cell40.setCellValue("VOUCHER AMOUNT"); cell40.setCellStyle(styleC3Center); if (list != null) { int count = 1; for (int i = 0; i < list.size(); i++) { MainMigrateModel data = (MainMigrateModel) list.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getGj()); cell0.setCellStyle(styleC24); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getPayno()); cell1.setCellStyle(styleC24); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getName()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getApcode()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getRefdoc()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getSystemdate()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getDuedate()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getInvoicenum()); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getMaindescription()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getCode()); cell8.setCellStyle(styleC24); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getTypeproduct()); cell9.setCellStyle(styleC24); HSSFCell cell11 = row.createCell(11); cell11.setCellValue(data.getDescription()); cell11.setCellStyle(styleC24); HSSFCell cell12 = row.createCell(12); cell12.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getTotalamount())) ? (new BigDecimal(data.getTotalamount())).doubleValue() : 0); cell12.setCellStyle(styleC25); HSSFCell cell013 = row.createCell(13); cell013.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getTotalvat())) ? (new BigDecimal(data.getTotalvat())).doubleValue() : 0); cell013.setCellStyle(styleC25); HSSFCell cell14 = row.createCell(14); cell14.setCellValue(data.getCur()); cell14.setCellStyle(styleC23); HSSFCell cell15 = row.createCell(15); cell15.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getAmount())) ? (new BigDecimal(data.getAmount())).doubleValue() : 0); cell15.setCellStyle(styleC25); HSSFCell cell16 = row.createCell(16); cell16.setCellValue(data.getDepartment()); cell16.setCellStyle(styleC24); HSSFCell cell17 = row.createCell(17); cell17.setCellValue(data.getAccno()); cell17.setCellStyle(styleC24); HSSFCell cell18 = row.createCell(18); cell18.setCellValue(data.getExpensedate()); cell18.setCellStyle(styleC24); HSSFCell cell19 = row.createCell(19); cell19.setCellValue(data.getVoucherno()); cell19.setCellStyle(styleC24); HSSFCell cell020 = row.createCell(20); cell020.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getVoucheramount())) ? (new BigDecimal(data.getVoucheramount())).doubleValue() : 0); cell020.setCellStyle(styleC25); } } for (int x = 0; x < 21; x++) { sheet.autoSizeColumn(x); } sheet.setColumnWidth(2, 256 * 30); sheet.setColumnWidth(8, 256 * 30); sheet.setColumnWidth(11, 256 * 30); exportFileExcel("TravoxReport", wb); }
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportInvoiceReport(List<MainMigrateModel> listInv) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setDataFormat(currency.getFormat("#,##0.00")); HSSFSheet sheet = wb.createSheet("Invoice"); HSSFRow row2 = sheet.createRow(0);// ww w. j a v a 2 s .com HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("ID"); cell20.setCellStyle(styleC3Center); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("INV NO"); cell21.setCellStyle(styleC3Center); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("NAME"); cell22.setCellStyle(styleC3Center); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("INV DATE"); cell23.setCellStyle(styleC3Center); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("GRAND TOTAL"); cell24.setCellStyle(styleC3Center); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("GRAND TOTAL GROSS"); cell25.setCellStyle(styleC3Center); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("GRAND TOTAL VAT"); cell26.setCellStyle(styleC3Center); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("CUR"); cell27.setCellStyle(styleC3Center); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("DEPARTMENT"); cell28.setCellStyle(styleC3Center); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("ACC NO"); cell29.setCellStyle(styleC3Center); if (listInv != null) { int count = 1; for (int i = 0; i < listInv.size(); i++) { MainMigrateModel data = (MainMigrateModel) listInv.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getId()); cell0.setCellStyle(styleC24); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getInvno()); cell1.setCellStyle(styleC24); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getName()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getInvdate()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); // cell3.setCellValue(data.getGrandtotal()); cell3.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getGrandtotal())) ? (new BigDecimal(data.getGrandtotal())).doubleValue() : 0); cell3.setCellStyle(styleC25); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getGrandtotalgross())) ? (new BigDecimal(data.getGrandtotalgross())).doubleValue() : 0); cell4.setCellStyle(styleC25); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getGrandtotalvat())) ? (new BigDecimal(data.getGrandtotalvat())).doubleValue() : 0); cell5.setCellStyle(styleC25); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getCur()); cell6.setCellStyle(styleC23); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getDepartment()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getAccno()); cell8.setCellStyle(styleC24); } } for (int x = 0; x < 10; x++) { sheet.autoSizeColumn(x); } sheet.setColumnWidth(2, 256 * 30); exportFileExcel("Invoice", wb); }
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportDeptorInvoiceReport(List<MainMigrateModel> listInv) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); // styleC25.setDataFormat(currency.getFormat("#,##0.00")); HSSFSheet sheet = wb.createSheet("DeptorInvoice"); HSSFRow row2 = sheet.createRow(0);//w w w . ja va 2s . co m HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("INV NO"); cell20.setCellStyle(styleC3Center); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("DATE"); cell21.setCellStyle(styleC3Center); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("NAME"); cell22.setCellStyle(styleC3Center); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("DETAIL"); cell23.setCellStyle(styleC3Center); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("INV AMOUNT"); cell24.setCellStyle(styleC3Center); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("RECEIVE NO"); cell25.setCellStyle(styleC3Center); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("RECEIVE AMOUNT"); cell26.setCellStyle(styleC3Center); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("REMAIN AMOUNT"); cell27.setCellStyle(styleC3Center); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("CODE"); cell28.setCellStyle(styleC3Center); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("DEPARTMENT"); cell29.setCellStyle(styleC3Center); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("GRAND TOTAL VATAMT"); cell30.setCellStyle(styleC3Center); if (listInv != null) { int count = 1; for (int i = 0; i < listInv.size(); i++) { MainMigrateModel data = (MainMigrateModel) listInv.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getInvoiceno()); cell0.setCellStyle(styleC24); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getInvoicedate()); cell1.setCellStyle(styleC24); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getInvoicename()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getInvoicedetail()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getInvoiceamount()); cell3.setCellStyle(styleC25); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getReceiveno()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getReceiveamount()); cell5.setCellStyle(styleC25); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getRemainamount()); cell6.setCellStyle(styleC25); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getCode()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getDepartment()); cell8.setCellStyle(styleC24); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getGrandtotal()); cell9.setCellStyle(styleC25); } } for (int x = 0; x < 11; x++) { sheet.autoSizeColumn(x); } sheet.setColumnWidth(2, 256 * 30); exportFileExcel("DeptorInvoice", wb); }
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportAPReport(List<MainMigrateModel> listAP) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); String datetemp = ""; if (listAP != null) { HSSFSheet sheet = wb.createSheet(listAP.get(0).getPaydate().substring(3, 10).replaceAll("-", "")); int count = 1; for (int i = 0; i < listAP.size(); i++) { MainMigrateModel data = (MainMigrateModel) listAP.get(i); if (!"".equalsIgnoreCase(datetemp) && !datetemp.equalsIgnoreCase(data.getPaydate().substring(3, 10))) { sheet = wb.createSheet(data.getPaydate().substring(3, 10).replaceAll("-", "")); HSSFRow row2 = sheet.createRow(0); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("PAYID"); cell20.setCellStyle(styleC3Center); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("PAY NO"); cell21.setCellStyle(styleC3Center); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("AP CODE"); cell22.setCellStyle(styleC3Center); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("NAME"); cell23.setCellStyle(styleC3Center); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("PAY DATE"); cell24.setCellStyle(styleC3Center); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("DEPARTMENT"); cell25.setCellStyle(styleC3Center); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("VAT TYPE"); cell26.setCellStyle(styleC3Center); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("TAX NO"); cell27.setCellStyle(styleC3Center); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("BRANCH"); cell28.setCellStyle(styleC3Center); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("BRANCH NO"); cell29.setCellStyle(styleC3Center); count = 1;/*w w w . j av a 2s . c o m*/ sheet.setColumnWidth(0, 256 * 15); sheet.setColumnWidth(1, 256 * 15); sheet.setColumnWidth(2, 256 * 15); sheet.setColumnWidth(3, 256 * 25); sheet.setColumnWidth(4, 256 * 15); sheet.setColumnWidth(5, 256 * 15); sheet.setColumnWidth(6, 256 * 15); sheet.setColumnWidth(7, 256 * 15); sheet.setColumnWidth(8, 256 * 15); sheet.setColumnWidth(9, 256 * 15); } else if ("".equalsIgnoreCase(datetemp)) { HSSFRow row2 = sheet.createRow(0); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("PAYID"); cell20.setCellStyle(styleC3Center); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("PAY NO"); cell21.setCellStyle(styleC3Center); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("AP CODE"); cell22.setCellStyle(styleC3Center); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("NAME"); cell23.setCellStyle(styleC3Center); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("PAY DATE"); cell24.setCellStyle(styleC3Center); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("DEPARTMENT"); cell25.setCellStyle(styleC3Center); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("VAT TYPE"); cell26.setCellStyle(styleC3Center); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("TAX NO"); cell27.setCellStyle(styleC3Center); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("BRANCH"); cell28.setCellStyle(styleC3Center); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("BRANCH NO"); cell29.setCellStyle(styleC3Center); sheet.setColumnWidth(0, 256 * 15); sheet.setColumnWidth(1, 256 * 15); sheet.setColumnWidth(2, 256 * 15); sheet.setColumnWidth(3, 256 * 25); sheet.setColumnWidth(4, 256 * 15); sheet.setColumnWidth(5, 256 * 15); sheet.setColumnWidth(6, 256 * 15); sheet.setColumnWidth(7, 256 * 15); sheet.setColumnWidth(8, 256 * 15); sheet.setColumnWidth(9, 256 * 15); } HSSFRow row = sheet.createRow(count); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getPayid()); cell0.setCellStyle(styleC24); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getPayno()); cell1.setCellStyle(styleC24); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getApCode()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(String.valueOf(data.getApname())); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(String.valueOf(data.getPaydate())); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getDepartment()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getVattype()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getTaxno()); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getBranch()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getBranchno()); cell8.setCellStyle(styleC24); datetemp = data.getPaydate().substring(3, 10); count++; } } exportFileExcel("APReport", wb); }