List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:com.smi.travel.controller.excel.checking.airticket.TicketFareSummary.java
public void genTicketFareAirlineReport(HSSFWorkbook wb, List TicketFare) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); String sheetName = "Sheet1";// name of sheet HSSFSheet sheet = wb.createSheet(sheetName); HSSFDataFormat currency = wb.createDataFormat(); TicketFareReport dataheader = new TicketFareReport(); if ((TicketFare != null) && (TicketFare.size() != 0)) { dataheader = (TicketFareReport) TicketFare.get(0); }/* w w w.j av a 2 s . co m*/ // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheet.createRow(0); HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("List Ticket Fare Airline"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); // Row 2 HSSFRow row2 = sheet.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue("Print By : "); cell21.setCellStyle(styleC21); HSSFCell cell22 = row2.createCell(1); cell22.setCellValue(dataheader.getPrintby()); cell22.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2")); HSSFCell cell23 = row2.createCell(4); cell23.setCellValue("Air Line : "); cell23.setCellStyle(styleC21); HSSFCell cell24 = row2.createCell(5); cell24.setCellValue(dataheader.getAirline()); cell24.setCellStyle(styleC22); // Row 3 HSSFRow row3 = sheet.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Department : "); cell31.setCellStyle(styleC21); HSSFCell cell32 = row3.createCell(1); cell32.setCellValue(dataheader.getHeaddepartment()); cell32.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3")); HSSFCell cell33 = row3.createCell(4); cell33.setCellValue("Ticket Buy : "); cell33.setCellStyle(styleC21); HSSFCell cell34 = row3.createCell(5); cell34.setCellValue(dataheader.getTickettype()); cell34.setCellStyle(styleC22); // Row 4 HSSFRow row4 = sheet.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Term Pay : "); cell41.setCellStyle(styleC21); HSSFCell cell42 = row4.createCell(1); cell42.setCellValue(dataheader.getHeadtermpay()); cell42.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4")); HSSFCell cell43 = row4.createCell(4); cell43.setCellValue("Ticket Type : "); cell43.setCellStyle(styleC21); HSSFCell cell44 = row4.createCell(5); cell44.setCellValue(dataheader.getTicketbuy()); cell44.setCellStyle(styleC22); // Row 5 HSSFRow row5 = sheet.createRow(4); HSSFCell cell51 = row5.createCell(0); cell51.setCellValue("Sale Staff : "); cell51.setCellStyle(styleC21); HSSFCell cell52 = row5.createCell(1); cell52.setCellValue(dataheader.getHeadstaff()); cell52.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5")); HSSFCell cell53 = row5.createCell(4); cell53.setCellValue("Invoice Date : "); cell53.setCellStyle(styleC21); HSSFCell cell54 = row5.createCell(5); cell54.setCellValue(dataheader.getInvoicedatefrom() + " to " + dataheader.getInvoicedateto()); cell54.setCellStyle(styleC22); // Row 6 HSSFRow row06 = sheet.createRow(5); HSSFCell cell061 = row06.createCell(0); cell061.setCellValue("Report of : "); cell061.setCellStyle(styleC21); sheet.addMergedRegion(CellRangeAddress.valueOf("A6:D6")); HSSFCell cell062 = row06.createCell(4); if (!"".equalsIgnoreCase(dataheader.getFrom())) { cell062.setCellValue(dataheader.getFrom()); cell062.setCellStyle(styleC22); } HSSFCell cell063 = row06.createCell(5); if (!"".equalsIgnoreCase(dataheader.getTo())) { cell063.setCellValue("to " + dataheader.getTo()); cell063.setCellStyle(styleC22); } // Row 7 HSSFRow row07 = sheet.createRow(6); HSSFCell cell071 = row07.createCell(0); cell071.setCellValue("Print on : "); cell071.setCellStyle(styleC21); sheet.addMergedRegion(CellRangeAddress.valueOf("A7:D7")); HSSFCell cell072 = row07.createCell(4); cell072.setCellValue(dataheader.getPrintondate()); cell072.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("E7:F7")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row6 = sheet.createRow(8); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue("Air"); cell61.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell62 = row6.createCell(1); cell62.setCellValue("Document Number"); cell62.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell622 = row6.createCell(2); cell622.setCellValue("Ref No"); cell622.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell63 = row6.createCell(3); cell63.setCellValue("Issue Date"); sheet.autoSizeColumn(3); cell63.setCellStyle(styleC3Center); HSSFCell cell64 = row6.createCell(4); cell64.setCellValue("Department"); cell64.setCellStyle(styleC3Center); sheet.autoSizeColumn(4); HSSFCell cell65 = row6.createCell(5); cell65.setCellValue("Staff"); cell65.setCellStyle(styleC3Center); sheet.autoSizeColumn(5); HSSFCell cell66 = row6.createCell(6); cell66.setCellValue("Term Pay"); cell66.setCellStyle(styleC3Center); sheet.autoSizeColumn(6); HSSFCellStyle styleC3Right = wb.createCellStyle(); styleC3Right.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Right.setAlignment(styleC3Right.ALIGN_RIGHT); styleC3Right.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Right.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Right.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Right.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCell cell67 = row6.createCell(7); cell67.setCellValue("Tax"); cell67.setCellStyle(styleC3Right); sheet.autoSizeColumn(7); HSSFCell cell68 = row6.createCell(8); cell68.setCellValue("Actual Commission"); cell68.setCellStyle(styleC3Right); sheet.autoSizeColumn(8); HSSFCell cell69 = row6.createCell(9); cell69.setCellValue("Insurance"); cell69.setCellStyle(styleC3Right); sheet.autoSizeColumn(9); HSSFCell cell70 = row6.createCell(10); cell70.setCellValue("Net Sales"); cell70.setCellStyle(styleC3Right); sheet.autoSizeColumn(10); HSSFCell cell71 = row6.createCell(11); cell71.setCellValue("Vat"); cell71.setCellStyle(styleC3Right); sheet.autoSizeColumn(11); HSSFCell cell711 = row6.createCell(12); cell711.setCellValue("Wht"); cell711.setCellStyle(styleC3Right); sheet.autoSizeColumn(12); HSSFCell cell72 = row6.createCell(13); cell72.setCellValue("Invoice No."); cell72.setCellStyle(styleC3Center); sheet.autoSizeColumn(13); HSSFCell cell73 = row6.createCell(14); cell73.setCellValue("Invoice Amount"); cell73.setCellStyle(styleC3Right); sheet.autoSizeColumn(14); HSSFCell cell74 = row6.createCell(15); cell74.setCellValue("Balance Payable"); cell74.setCellStyle(styleC3Right); sheet.autoSizeColumn(15); //Detail of Table int count = 9; HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC22.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); 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")); for (int i = 0; i < TicketFare.size(); i++) { // System.out.println("get size : "+ i); TicketFareReport data = (TicketFareReport) TicketFare.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getAir()); cell0.setCellStyle(styleC24); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getDocno()); cell1.setCellStyle(styleC24); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getRefno()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getIssuedate()); cell2.setCellStyle(styleC23); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getDepartment()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getStaff()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getTermpay()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(7); cell6.setCellValue( !"".equalsIgnoreCase(data.getTax()) ? new BigDecimal(data.getTax()).doubleValue() : 0); cell6.setCellStyle(styleC25); HSSFCell cell7 = row.createCell(8); cell7.setCellValue( !"".equalsIgnoreCase(data.getTicketcom()) ? new BigDecimal(data.getTicketcom()).doubleValue() : 0); cell7.setCellStyle(styleC25); HSSFCell cell8 = row.createCell(9); cell8.setCellValue( !"".equalsIgnoreCase(data.getIns()) ? new BigDecimal(data.getIns()).doubleValue() : 0); cell8.setCellStyle(styleC25); HSSFCell cell9 = row.createCell(10); cell9.setCellValue( !"".equalsIgnoreCase(data.getNetsale()) ? new BigDecimal(data.getNetsale()).doubleValue() : 0); cell9.setCellStyle(styleC25); HSSFCell cell10 = row.createCell(11); cell10.setCellValue( !"".equalsIgnoreCase(data.getVat()) ? new BigDecimal(data.getVat()).doubleValue() : 0); cell10.setCellStyle(styleC25); HSSFCell cell14 = row.createCell(12); cell14.setCellValue( !"".equalsIgnoreCase(data.getWht()) ? new BigDecimal(data.getWht()).doubleValue() : 0); cell14.setCellStyle(styleC25); HSSFCell cell11 = row.createCell(13); cell11.setCellValue(data.getInvno()); cell11.setCellStyle(styleC24); HSSFCell cell12 = row.createCell(14); cell12.setCellValue( !"".equalsIgnoreCase(data.getInvamount()) ? new BigDecimal(data.getInvamount()).doubleValue() : 0); cell12.setCellStyle(styleC25); HSSFCell cell15 = row.createCell(15); cell15.setCellValue( !"".equalsIgnoreCase(data.getBalance()) ? new BigDecimal(data.getBalance()).doubleValue() : 0); cell15.setCellStyle(styleC25); if (i == (TicketFare.size() - 1)) { row = sheet.createRow(count + i + 1); for (int k = 0; k < 8; k++) { HSSFCellStyle styleSum = wb.createCellStyle(); styleSum.setAlignment(styleC24.ALIGN_RIGHT); styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN); styleSum.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFCell cellSum = row.createCell(k); if (k == 0) { styleSum.setBorderLeft(HSSFCellStyle.BORDER_THIN); } cellSum.setCellStyle(styleSum); } HSSFCellStyle styleSum = wb.createCellStyle(); styleSum.setAlignment(styleSum.ALIGN_RIGHT); styleSum.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleSum.setBorderRight(HSSFCellStyle.BORDER_THIN); styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN); styleSum.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleSum.setDataFormat(currency.getFormat("#,##0.00")); String sumTax = "SUM(H" + 10 + ":H" + (count + i + 1) + ")"; String sumActComm = "SUM(I" + 10 + ":I" + (count + i + 1) + ")"; String sumInsurance = "SUM(J" + 10 + ":J" + (count + i + 1) + ")"; String sumNetSales = "SUM(K" + 10 + ":K" + (count + i + 1) + ")"; String sumVat = "SUM(L" + 10 + ":L" + (count + i + 1) + ")"; String sumWht = "SUM(M" + 10 + ":M" + (count + i + 1) + ")"; String sumInvAmount = "SUM(O" + 10 + ":O" + (count + i + 1) + ")"; String sumBalance = "SUM(P" + 10 + ":P" + (count + i + 1) + ")"; HSSFCell cell5Sum = row.createCell(5); cell5Sum.setCellValue("Total"); cell5Sum.setCellStyle(styleSum); HSSFCell cell111Sum = row.createCell(6); cell111Sum.setCellStyle(styleSum); HSSFCell cell6Sum = row.createCell(7); cell6Sum.setCellFormula(sumTax); cell6Sum.setCellStyle(styleSum); HSSFCell cell7Sum = row.createCell(8); cell7Sum.setCellFormula(sumActComm); cell7Sum.setCellStyle(styleSum); HSSFCell cell8Sum = row.createCell(9); cell8Sum.setCellFormula(sumInsurance); cell8Sum.setCellStyle(styleSum); HSSFCell cell9Sum = row.createCell(10); cell9Sum.setCellFormula(sumNetSales); cell9Sum.setCellStyle(styleSum); HSSFCell cell10Sum = row.createCell(11); cell10Sum.setCellFormula(sumVat); cell10Sum.setCellStyle(styleSum); HSSFCell cell101Sum = row.createCell(12); cell101Sum.setCellFormula(sumWht); cell101Sum.setCellStyle(styleSum); HSSFCell cell11Sum = row.createCell(13); cell11Sum.setCellStyle(styleSum); HSSFCell cell12Sum = row.createCell(14); cell12Sum.setCellFormula(sumInvAmount); cell12Sum.setCellStyle(styleSum); HSSFCell cell13Sum = row.createCell(15); cell13Sum.setCellFormula(sumBalance); cell13Sum.setCellStyle(styleSum); } } for (int j = 0; j < 15; j++) { sheet.autoSizeColumn(j); } }
From source file:com.smi.travel.controller.excel.checking.airticket.TicketFareSummary.java
public void genTicketFareInvoiceReport(HSSFWorkbook wb, List TicketFare) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); String sheetName = "Sheet1";// name of sheet HSSFSheet sheet = wb.createSheet(sheetName); TicketFareReport dataheader = new TicketFareReport(); HSSFDataFormat currency = wb.createDataFormat(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); styleC21.setDataFormat(currency.getFormat("#,##0.00")); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); if (!TicketFare.isEmpty()) { dataheader = (TicketFareReport) TicketFare.get(0); // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheet.createRow(0); HSSFCell cell1 = row1.createCell(0); cell1.setCellValue("List Ticket Fare Invoice"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cell1.setCellStyle(styleC1);/*w w w. j a v a 2 s . c o m*/ sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Row 2 HSSFRow row2 = sheet.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue("Print By : "); cell21.setCellStyle(styleC21); HSSFCell cell22 = row2.createCell(1); cell22.setCellValue(dataheader.getPrintby()); cell22.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2")); HSSFCell cell23 = row2.createCell(4); cell23.setCellValue("Airline : "); cell23.setCellStyle(styleC21); HSSFCell cell24 = row2.createCell(5); cell24.setCellValue(dataheader.getAirline()); cell24.setCellStyle(styleC22); // Row 3 HSSFRow row3 = sheet.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Department : "); cell31.setCellStyle(styleC21); HSSFCell cell32 = row3.createCell(1); cell32.setCellValue(dataheader.getHeaddepartment()); cell32.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3")); HSSFCell cell33 = row3.createCell(4); cell33.setCellValue("Ticket Type : "); cell33.setCellStyle(styleC21); HSSFCell cell34 = row3.createCell(5); cell34.setCellValue(dataheader.getTickettype()); cell34.setCellStyle(styleC22); // Row 4 HSSFRow row4 = sheet.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Term Pay : "); cell41.setCellStyle(styleC21); HSSFCell cell42 = row4.createCell(1); cell42.setCellValue(dataheader.getHeadtermpay()); cell42.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4")); HSSFCell cell43 = row4.createCell(4); cell43.setCellValue("Ticket Buy : "); cell43.setCellStyle(styleC21); HSSFCell cell44 = row4.createCell(5); cell44.setCellValue(dataheader.getTicketbuy()); cell44.setCellStyle(styleC22); // Row 5 HSSFRow row5 = sheet.createRow(4); HSSFCell cell51 = row5.createCell(0); cell51.setCellValue("Sale Staff : "); cell51.setCellStyle(styleC21); HSSFCell cell52 = row5.createCell(1); cell52.setCellValue(dataheader.getHeadstaff()); cell52.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5")); HSSFCell cell53 = row5.createCell(4); cell53.setCellValue("Invoice Date : "); cell53.setCellStyle(styleC21); HSSFCell cell54 = row5.createCell(5); cell54.setCellValue(dataheader.getInvoicedatefrom() + " to " + dataheader.getInvoicedateto()); cell54.setCellStyle(styleC22); // Row 6 HSSFRow row06 = sheet.createRow(5); HSSFCell cell061 = row06.createCell(0); cell061.setCellValue("Report of : "); cell061.setCellStyle(styleC21); sheet.addMergedRegion(CellRangeAddress.valueOf("A6:D6")); HSSFCell cell062 = row06.createCell(4); if (!"".equalsIgnoreCase(dataheader.getFrom())) { cell062.setCellValue(dataheader.getFrom()); cell062.setCellStyle(styleC22); } HSSFCell cell063 = row06.createCell(5); if (!"".equalsIgnoreCase(dataheader.getTo())) { cell063.setCellValue("to " + dataheader.getTo()); cell063.setCellStyle(styleC22); } // Row 7 HSSFRow row07 = sheet.createRow(6); HSSFCell cell071 = row07.createCell(0); cell071.setCellValue("Print on : "); cell071.setCellStyle(styleC21); sheet.addMergedRegion(CellRangeAddress.valueOf("A7:D7")); HSSFCell cell072 = row07.createCell(4); cell072.setCellValue(dataheader.getPrintondate()); cell072.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("E7:F7")); } // Header Table HSSFCellStyle styleC3 = wb.createCellStyle(); styleC3.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3.setAlignment(styleC3.ALIGN_CENTER); styleC3.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row6 = sheet.createRow(8); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue("Inv. No."); cell61.setCellStyle(styleC3); sheet.autoSizeColumn(0); HSSFCell cell62 = row6.createCell(1); cell62.setCellValue("Inv. Date"); cell62.setCellStyle(styleC3); sheet.autoSizeColumn(1); HSSFCell cell63 = row6.createCell(2); cell63.setCellValue("Department"); sheet.autoSizeColumn(2); cell63.setCellStyle(styleC3); HSSFCell cell64 = row6.createCell(3); cell64.setCellValue("Staff"); cell64.setCellStyle(styleC3); sheet.autoSizeColumn(3); HSSFCell cell65 = row6.createCell(4); cell65.setCellValue("Term Pay"); cell65.setCellStyle(styleC3); sheet.autoSizeColumn(4); HSSFCell cell66 = row6.createCell(5); cell66.setCellValue("Passenger"); cell66.setCellStyle(styleC3); sheet.autoSizeColumn(5); HSSFCell cell67 = row6.createCell(6); cell67.setCellValue("Air"); cell67.setCellStyle(styleC3); sheet.autoSizeColumn(6); HSSFCell cell68 = row6.createCell(7); cell68.setCellValue("Document Number"); cell68.setCellStyle(styleC3); sheet.autoSizeColumn(7); HSSFCell cell688 = row6.createCell(8); cell688.setCellValue("Ref No"); cell688.setCellStyle(styleC3); sheet.autoSizeColumn(8); HSSFCell cell69 = row6.createCell(9); cell69.setCellValue("Issue Date"); cell69.setCellStyle(styleC3); sheet.autoSizeColumn(9); HSSFCell cell70 = row6.createCell(10); cell70.setCellValue("Net Sales"); cell70.setCellStyle(styleC3); sheet.autoSizeColumn(10); HSSFCell cell71 = row6.createCell(11); cell71.setCellValue("Tax"); cell71.setCellStyle(styleC3); sheet.autoSizeColumn(11); HSSFCell cell72 = row6.createCell(12); cell72.setCellValue("Insurance"); cell72.setCellStyle(styleC3); sheet.autoSizeColumn(12); HSSFCell cell73 = row6.createCell(13); cell73.setCellValue("Actual Commission"); cell73.setCellStyle(styleC3); sheet.autoSizeColumn(13); HSSFCell cell733 = row6.createCell(14); cell733.setCellValue("Wht"); cell733.setCellStyle(styleC3); sheet.autoSizeColumn(14); HSSFCell cell74 = row6.createCell(15); cell74.setCellValue("Inv. Amount"); cell74.setCellStyle(styleC3); sheet.autoSizeColumn(15); //Detail of Table int count = 9; 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); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); 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")); for (int i = 0; i < TicketFare.size(); i++) { TicketFareReport data = (TicketFareReport) TicketFare.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell celldata0 = row.createCell(0); celldata0.setCellValue(data.getInvno()); celldata0.setCellStyle(styleC24); HSSFCell celldata1 = row.createCell(1); celldata1.setCellValue(data.getInvdate()); celldata1.setCellStyle(styleC24); HSSFCell celldata2 = row.createCell(2); celldata2.setCellValue(data.getDepartment()); celldata2.setCellStyle(styleC24); HSSFCell celldata3 = row.createCell(3); celldata3.setCellValue(data.getStaff()); celldata3.setCellStyle(styleC24); HSSFCell celldata4 = row.createCell(4); celldata4.setCellValue(data.getTermpay()); celldata4.setCellStyle(styleC24); HSSFCell celldata5 = row.createCell(5); celldata5.setCellValue(data.getPassenger()); celldata5.setCellStyle(styleC24); HSSFCell celldata6 = row.createCell(6); celldata6.setCellValue(data.getAir()); celldata6.setCellStyle(styleC24); HSSFCell celldata7 = row.createCell(7); celldata7.setCellValue(data.getDocno()); celldata7.setCellStyle(styleC24); HSSFCell celldata14 = row.createCell(8); celldata14.setCellValue(data.getRefno()); celldata14.setCellStyle(styleC24); //set data HSSFCell celldata8 = row.createCell(9); celldata8.setCellValue(data.getIssuedate()); celldata8.setCellStyle(styleC23); HSSFCell celldata9 = row.createCell(10); celldata9.setCellValue("".equalsIgnoreCase(String.valueOf(data.getNetsale())) ? 0 : new BigDecimal(data.getNetsale()).doubleValue()); celldata9.setCellStyle(styleC25); HSSFCell celldata10 = row.createCell(11); celldata10.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTax())) ? 0 : new BigDecimal(data.getTax()).doubleValue()); celldata10.setCellStyle(styleC25); HSSFCell celldata11 = row.createCell(12); celldata11.setCellValue("".equalsIgnoreCase(String.valueOf(data.getIns())) ? 0 : new BigDecimal(data.getIns()).doubleValue()); celldata11.setCellStyle(styleC25); HSSFCell celldata12 = row.createCell(13); celldata12.setCellValue("".equalsIgnoreCase(String.valueOf(data.getActcom())) ? 0 : new BigDecimal(data.getActcom()).doubleValue()); celldata12.setCellStyle(styleC25); HSSFCell celldata15 = row.createCell(14); celldata15.setCellValue("".equalsIgnoreCase(String.valueOf(data.getWht())) ? 0 : new BigDecimal(data.getWht()).doubleValue()); celldata15.setCellStyle(styleC25); HSSFCell celldata13 = row.createCell(15); celldata13.setCellValue("".equalsIgnoreCase(String.valueOf(data.getInvamount())) ? 0 : new BigDecimal(data.getInvamount()).doubleValue()); celldata13.setCellStyle(styleC25); if (i == (TicketFare.size() - 1)) { row = sheet.createRow(count + i + 1); for (int k = 0; k < 10; k++) { HSSFCellStyle styleSum = wb.createCellStyle(); styleSum.setAlignment(styleC24.ALIGN_RIGHT); styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN); styleSum.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFCell cellSum = row.createCell(k); if (k == 0) { styleSum.setBorderLeft(HSSFCellStyle.BORDER_THIN); } cellSum.setCellStyle(styleSum); } HSSFCellStyle styleSum = wb.createCellStyle(); styleSum.setAlignment(styleSum.ALIGN_RIGHT); styleSum.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleSum.setBorderRight(HSSFCellStyle.BORDER_THIN); styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN); styleSum.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleSum.setDataFormat(currency.getFormat("#,##0.00")); String netsalesTotal = "SUM(K" + 10 + ":K" + (count + i + 1) + ")"; String taxTotal = "SUM(L" + 10 + ":L" + (count + i + 1) + ")"; String insTotal = "SUM(M" + 10 + ":M" + (count + i + 1) + ")"; String actcommTotal = "SUM(N" + 10 + ":N" + (count + i + 1) + ")"; String sumWht = "SUM(O" + 10 + ":O" + (count + i + 1) + ")"; String invamountTotal = "SUM(P" + 10 + ":P" + (count + i + 1) + ")"; HSSFCell cellTotal00 = row.createCell(9); cellTotal00.setCellValue("Total"); cellTotal00.setCellStyle(styleSum); HSSFCell cellTotal01 = row.createCell(10); cellTotal01.setCellFormula(netsalesTotal); cellTotal01.setCellStyle(styleSum); HSSFCell cellTotal02 = row.createCell(11); cellTotal02.setCellFormula(taxTotal); cellTotal02.setCellStyle(styleSum); HSSFCell cellTotal03 = row.createCell(12); cellTotal03.setCellFormula(insTotal); cellTotal03.setCellStyle(styleSum); HSSFCell cellTotal04 = row.createCell(13); cellTotal04.setCellFormula(actcommTotal); cellTotal04.setCellStyle(styleSum); HSSFCell cellTotal06 = row.createCell(14); cellTotal06.setCellFormula(sumWht); cellTotal06.setCellStyle(styleSum); HSSFCell cellTotal05 = row.createCell(15); cellTotal05.setCellFormula(invamountTotal); cellTotal05.setCellStyle(styleSum); } } for (int j = 0; j < 15; j++) { sheet.autoSizeColumn(j); } }
From source file:com.smi.travel.controller.excel.checking.airticket.TicketFareSummary.java
private void genTicketFareAgentReport(HSSFWorkbook wb, List TicketAgent) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); String sheetName = "Sheet1";// name of sheet HSSFSheet sheet = wb.createSheet(sheetName); TicketFareReport dataheader = new TicketFareReport(); 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); if (TicketAgent.size() > 0) { dataheader = (TicketFareReport) TicketAgent.get(0); }// ww w . j a v a 2s. com // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheet.createRow(0); HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("List Ticket Payment Agent"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Row 2 HSSFRow row2 = sheet.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue("Print By : "); cell21.setCellStyle(styleC21); HSSFCell cell22 = row2.createCell(1); cell22.setCellValue(dataheader.getPrintby()); cell22.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2")); HSSFCell cell23 = row2.createCell(4); cell23.setCellValue("Air Line : "); cell23.setCellStyle(styleC21); HSSFCell cell24 = row2.createCell(5); cell24.setCellValue(dataheader.getAirline()); cell24.setCellStyle(styleC22); // Row 3 HSSFRow row3 = sheet.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Department : "); cell31.setCellStyle(styleC21); HSSFCell cell32 = row3.createCell(1); cell32.setCellValue(dataheader.getHeaddepartment()); cell32.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3")); HSSFCell cell33 = row3.createCell(4); cell33.setCellValue("Ticket Type : "); cell33.setCellStyle(styleC21); HSSFCell cell34 = row3.createCell(5); cell34.setCellValue(dataheader.getTickettype()); cell34.setCellStyle(styleC22); // Row 4 HSSFRow row4 = sheet.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Term Pay : "); cell41.setCellStyle(styleC21); HSSFCell cell42 = row4.createCell(1); cell42.setCellValue(dataheader.getHeadtermpay()); cell42.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4")); HSSFCell cell43 = row4.createCell(4); cell43.setCellValue("Ticket Buy : "); cell43.setCellStyle(styleC21); HSSFCell cell44 = row4.createCell(5); cell44.setCellValue(dataheader.getTicketbuy()); cell44.setCellStyle(styleC22); // Row 5 HSSFRow row5 = sheet.createRow(4); HSSFCell cell51 = row5.createCell(0); cell51.setCellValue("Sale Staff : "); cell51.setCellStyle(styleC21); HSSFCell cell52 = row5.createCell(1); cell52.setCellValue(dataheader.getHeadstaff()); cell52.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5")); HSSFCell cell53 = row5.createCell(4); cell53.setCellValue("Invoice Date : "); cell53.setCellStyle(styleC21); HSSFCell cell54 = row5.createCell(5); cell54.setCellValue(dataheader.getInvoicedatefrom() + " to " + dataheader.getInvoicedateto()); cell54.setCellStyle(styleC22); // Row 6 HSSFRow row06 = sheet.createRow(5); HSSFCell cell061 = row06.createCell(0); cell061.setCellValue("Report of : "); cell061.setCellStyle(styleC21); sheet.addMergedRegion(CellRangeAddress.valueOf("A6:D6")); HSSFCell cell062 = row06.createCell(4); if (!"".equalsIgnoreCase(dataheader.getFrom())) { cell062.setCellValue(dataheader.getFrom()); cell062.setCellStyle(styleC22); } HSSFCell cell063 = row06.createCell(5); if (!"".equalsIgnoreCase(dataheader.getTo())) { cell063.setCellValue("to " + dataheader.getTo()); cell063.setCellStyle(styleC22); } // Row 7 HSSFRow row07 = sheet.createRow(6); HSSFCell cell071 = row07.createCell(0); cell071.setCellValue("Print on : "); cell071.setCellStyle(styleC21); sheet.addMergedRegion(CellRangeAddress.valueOf("A7:D7")); HSSFCell cell072 = row07.createCell(4); cell072.setCellValue(dataheader.getPrintondate()); cell072.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("E7:F7")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row6 = sheet.createRow(8); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue("Invoice No."); cell61.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell62 = row6.createCell(1); cell62.setCellValue("Issue Date"); cell62.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell63 = row6.createCell(2); cell63.setCellValue("Agent"); cell63.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell631 = row6.createCell(3); cell631.setCellValue("Air"); cell631.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell632 = row6.createCell(4); cell632.setCellValue("Doc No"); cell632.setCellStyle(styleC3Center); sheet.autoSizeColumn(4); HSSFCell cell633 = row6.createCell(5); cell633.setCellValue("Ref No"); cell633.setCellStyle(styleC3Center); sheet.autoSizeColumn(5); cell63.setCellStyle(styleC3Center); HSSFCell cell65 = row6.createCell(6); cell65.setCellValue("Department"); cell65.setCellStyle(styleC3Center); sheet.autoSizeColumn(6); HSSFCell cell66 = row6.createCell(7); cell66.setCellValue("Staff"); cell66.setCellStyle(styleC3Center); sheet.autoSizeColumn(7); HSSFCell cell67 = row6.createCell(8); cell67.setCellValue("Term Pay"); cell67.setCellStyle(styleC3Center); sheet.autoSizeColumn(8); HSSFCellStyle styleC3Right = wb.createCellStyle(); styleC3Right.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Right.setAlignment(styleC3Right.ALIGN_RIGHT); styleC3Right.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Right.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Right.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Right.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCell cell68 = row6.createCell(9); cell68.setCellValue("Inv. Amount"); cell68.setCellStyle(styleC3Right); sheet.autoSizeColumn(9); HSSFCell cell661 = row6.createCell(10); cell661.setCellValue("Cost"); cell661.setCellStyle(styleC3Right); sheet.autoSizeColumn(10); HSSFCell cell69 = row6.createCell(11); cell69.setCellValue("Ticket Comm"); cell69.setCellStyle(styleC3Right); sheet.autoSizeColumn(11); HSSFCell cell70 = row6.createCell(12); cell70.setCellValue("Sale Price"); cell70.setCellStyle(styleC3Right); sheet.autoSizeColumn(12); HSSFCell cell71 = row6.createCell(13); cell71.setCellValue("Agent Comm"); cell71.setCellStyle(styleC3Right); sheet.autoSizeColumn(13); HSSFCell cell72 = row6.createCell(14); cell72.setCellValue("Profit"); cell72.setCellStyle(styleC3Right); sheet.autoSizeColumn(14); //Detail of Table int count = 9; HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC22.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); 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")); for (int i = 0; i < TicketAgent.size(); i++) { TicketFareReport data = (TicketFareReport) TicketAgent.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getInvno()); cell0.setCellStyle(styleC24); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getIssuedate()); cell1.setCellStyle(styleC23); HSSFCell cell2 = row.createCell(2); cell2.setCellValue(data.getAgent()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(3); cell3.setCellValue(data.getAir()); cell3.setCellStyle(styleC23); HSSFCell cell14 = row.createCell(4); cell14.setCellValue(data.getDocno()); cell14.setCellStyle(styleC24); HSSFCell cell15 = row.createCell(5); cell15.setCellValue(data.getRefno()); cell15.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(6); cell4.setCellValue(data.getDepartment()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(7); cell5.setCellValue(data.getStaff()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(8); cell6.setCellValue(data.getTermpay()); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(9); cell7.setCellValue( !"".equalsIgnoreCase(data.getInvamount()) ? new BigDecimal(data.getInvamount()).doubleValue() : 0); cell7.setCellStyle(styleC25); HSSFCell cell16 = row.createCell(10); cell16.setCellValue( !"".equalsIgnoreCase(data.getCost()) ? new BigDecimal(data.getCost()).doubleValue() : 0); cell16.setCellStyle(styleC25); HSSFCell cell8 = row.createCell(11); cell8.setCellValue( !"".equalsIgnoreCase(data.getTicketcom()) ? new BigDecimal(data.getTicketcom()).doubleValue() : 0); cell8.setCellStyle(styleC25); HSSFCell cell9 = row.createCell(12); cell9.setCellValue( !"".equalsIgnoreCase(data.getSaleprice()) ? new BigDecimal(data.getSaleprice()).doubleValue() : 0); cell9.setCellStyle(styleC25); HSSFCell cell10 = row.createCell(13); cell10.setCellValue( !"".equalsIgnoreCase(data.getAgentcom()) ? new BigDecimal(data.getAgentcom()).doubleValue() : 0); cell10.setCellStyle(styleC25); HSSFCell cell11 = row.createCell(14); cell11.setCellValue( !"".equalsIgnoreCase(data.getProfit()) ? new BigDecimal(data.getProfit()).doubleValue() : 0); cell11.setCellStyle(styleC25); sheet.autoSizeColumn(14); if (i == (TicketAgent.size() - 1)) { row = sheet.createRow(count + i + 1); for (int k = 0; k < 10; k++) { HSSFCellStyle styleSum = wb.createCellStyle(); styleSum.setAlignment(styleC24.ALIGN_RIGHT); styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN); styleSum.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFCell cellSum = row.createCell(k); if (k == 0) { styleSum.setBorderLeft(HSSFCellStyle.BORDER_THIN); } cellSum.setCellStyle(styleSum); } HSSFCellStyle styleSum = wb.createCellStyle(); styleSum.setAlignment(styleSum.ALIGN_RIGHT); styleSum.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleSum.setBorderRight(HSSFCellStyle.BORDER_THIN); styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN); styleSum.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleSum.setDataFormat(currency.getFormat("#,##0.00")); String sumInvAmount = "SUM(J" + 10 + ":J" + (count + i + 1) + ")"; String sumCost = "SUM(K" + 10 + ":K" + (count + i + 1) + ")"; String sumTicketComm = "SUM(L" + 10 + ":L" + (count + i + 1) + ")"; String sumSalePrice = "SUM(M" + 10 + ":M" + (count + i + 1) + ")"; String sumAgentComm = "SUM(N" + 10 + ":N" + (count + i + 1) + ")"; String sumProfit = "SUM(O" + 10 + ":O" + (count + i + 1) + ")"; HSSFCell cell6Sum = row.createCell(8); cell6Sum.setCellValue("Total"); cell6Sum.setCellStyle(styleSum); HSSFCell cell7Sum = row.createCell(9); cell7Sum.setCellFormula(sumInvAmount); cell7Sum.setCellStyle(styleSum); HSSFCell cell71Sum = row.createCell(10); cell71Sum.setCellFormula(sumCost); cell71Sum.setCellStyle(styleSum); HSSFCell cell8Sum = row.createCell(11); cell8Sum.setCellFormula(sumTicketComm); cell8Sum.setCellStyle(styleSum); HSSFCell cell9Sum = row.createCell(12); cell9Sum.setCellFormula(sumSalePrice); cell9Sum.setCellStyle(styleSum); HSSFCell cell10Sum = row.createCell(13); cell10Sum.setCellFormula(sumAgentComm); cell10Sum.setCellStyle(styleSum); HSSFCell cell11Sum = row.createCell(14); cell11Sum.setCellFormula(sumProfit); cell11Sum.setCellStyle(styleSum); sheet.autoSizeColumn(14); } } for (int j = 0; j < 15; j++) { sheet.autoSizeColumn(j); } }
From source file:com.smi.travel.controller.excel.checking.airticket.TicketFareSummary.java
public void genTicketFareSummaryByStaff(HSSFWorkbook wb, List ticketSumByStaff) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); String sheetName = "Sheet1";// name of sheet HSSFSheet sheet = wb.createSheet(sheetName); TicketFareSummaryByAgentStaff dataheader = new TicketFareSummaryByAgentStaff(); HSSFDataFormat currency = wb.createDataFormat(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); styleC21.setDataFormat(currency.getFormat("#,##0")); 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.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); 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(styleC22.ALIGN_CENTER); if (!ticketSumByStaff.isEmpty()) { dataheader = (TicketFareSummaryByAgentStaff) ticketSumByStaff.get(0); // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheet.createRow(0); HSSFCell cell1 = row1.createCell(0); cell1.setCellValue("List Ticket Summary By Staff"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cell1.setCellStyle(styleC1);/*w w w. ja v a 2s .c om*/ sheet.addMergedRegion(CellRangeAddress.valueOf("A1:G1")); // Row 2 HSSFRow row2 = sheet.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue("Print By : "); cell21.setCellStyle(styleC21); HSSFCell cell22 = row2.createCell(1); cell22.setCellValue(dataheader.getPrintby()); cell22.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2")); HSSFCell cell23 = row2.createCell(4); cell23.setCellValue("Air Line Code : "); cell23.setCellStyle(styleC21); HSSFCell cell24 = row2.createCell(5); cell24.setCellValue(dataheader.getAirline()); cell24.setCellStyle(styleC22); // Row 3 HSSFRow row3 = sheet.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Department : "); cell31.setCellStyle(styleC21); HSSFCell cell32 = row3.createCell(1); cell32.setCellValue(dataheader.getHeaddepartment()); cell32.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3")); HSSFCell cell33 = row3.createCell(4); cell33.setCellValue("Ticket Type : "); cell33.setCellStyle(styleC21); HSSFCell cell34 = row3.createCell(5); cell34.setCellValue(dataheader.getHeadtickettype()); cell34.setCellStyle(styleC22); // Row 4 HSSFRow row4 = sheet.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Term Pay : "); cell41.setCellStyle(styleC21); HSSFCell cell42 = row4.createCell(1); cell42.setCellValue(dataheader.getTermpay()); cell42.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4")); HSSFCell cell43 = row4.createCell(4); cell43.setCellValue("Ticket Buy : "); cell43.setCellStyle(styleC21); HSSFCell cell44 = row4.createCell(5); cell44.setCellValue(dataheader.getHeadticketbuy()); cell44.setCellStyle(styleC22); // Row 5 HSSFRow row5 = sheet.createRow(4); HSSFCell cell51 = row5.createCell(0); cell51.setCellValue("Airline : "); cell51.setCellStyle(styleC21); HSSFCell cell52 = row5.createCell(1); cell52.setCellValue(dataheader.getHeaderairline()); cell52.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5")); HSSFCell cell53 = row5.createCell(4); cell53.setCellValue("Sale Staff : "); cell53.setCellStyle(styleC21); HSSFCell cell54 = row5.createCell(5); cell54.setCellValue(dataheader.getHeadsale()); cell54.setCellStyle(styleC22); // Row 6 HSSFRow row6 = sheet.createRow(5); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue("Issue Date : "); cell61.setCellStyle(styleC21); sheet.addMergedRegion(CellRangeAddress.valueOf("A6:D6")); HSSFCell cell62 = row6.createCell(4); if (!"".equalsIgnoreCase(dataheader.getIssuefrom())) { cell62.setCellValue(dataheader.getIssuefrom()); cell62.setCellStyle(styleC22); } HSSFCell cell63 = row6.createCell(5); if (!"".equalsIgnoreCase(dataheader.getIssueto())) { cell63.setCellValue("to " + dataheader.getIssueto()); cell63.setCellStyle(styleC22); } // Row 7 HSSFRow row7 = sheet.createRow(6); HSSFCell cell71 = row7.createCell(0); cell71.setCellValue("Invoice Date : "); cell71.setCellStyle(styleC21); sheet.addMergedRegion(CellRangeAddress.valueOf("A7:D7")); HSSFCell cell72 = row7.createCell(4); if (!"".equalsIgnoreCase(dataheader.getInvdatefrom())) { cell72.setCellValue(dataheader.getInvdatefrom()); cell72.setCellStyle(styleC22); } HSSFCell cell73 = row7.createCell(5); if (!"".equalsIgnoreCase(dataheader.getInvdateto())) { cell73.setCellValue("to " + dataheader.getInvdateto()); cell73.setCellStyle(styleC22); } // Row 8 HSSFRow row8 = sheet.createRow(7); HSSFCell cell81 = row8.createCell(0); cell81.setCellValue("Print on : "); cell81.setCellStyle(styleC21); sheet.addMergedRegion(CellRangeAddress.valueOf("A8:D8")); HSSFCell cell82 = row8.createCell(4); cell82.setCellValue(dataheader.getPrinton()); cell82.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("E8:F8")); } // Header Table HSSFCellStyle styleC3 = wb.createCellStyle(); styleC3.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC3.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3.setAlignment(styleC3.ALIGN_CENTER); // Detail of Table String temp = "XXXXXXXX"; int count = 9; int ktemp = 12; //Total Outbound int totalPaxOut = 0; BigDecimal totalInvAmountOut = new BigDecimal("0.00"); BigDecimal totalTiccomOut = new BigDecimal("0.00"); BigDecimal totalSalePriceOut = new BigDecimal("0.00"); BigDecimal totalAgentCommOut = new BigDecimal("0.00"); BigDecimal totalProfitOut = new BigDecimal("0.00"); BigDecimal totalCostOut = new BigDecimal("0.00"); // Total Wendy int totalPaxWendy = 0; BigDecimal totalInvAmountWendy = new BigDecimal("0.00"); BigDecimal totalTiccomWendy = new BigDecimal("0.00"); BigDecimal totalSalePriceWendy = new BigDecimal("0.00"); BigDecimal totalAgentCommWendy = new BigDecimal("0.00"); BigDecimal totalProfitWendy = new BigDecimal("0.00"); BigDecimal totalCostWendy = new BigDecimal("0.00"); for (int i = 0; i < ticketSumByStaff.size(); i++) { TicketFareSummaryByAgentStaff data = (TicketFareSummaryByAgentStaff) ticketSumByStaff.get(i); if (!temp.equalsIgnoreCase(data.getOwner())) { if (!"XXXXXXXX".equalsIgnoreCase(temp)) { HSSFRow row = sheet.createRow(count + i); String totalPax = "SUM(C" + ktemp + ":C" + (count + i) + ")"; String totalInvAmount = "SUM(D" + ktemp + ":D" + (count + i) + ")"; String totalSalePrice = "SUM(E" + ktemp + ":E" + (count + i) + ")"; String totalCost = "SUM(F" + ktemp + ":F" + (count + i) + ")"; String totalTiccom = "SUM(G" + ktemp + ":G" + (count + i) + ")"; String totalAgentComm = "SUM(H" + ktemp + ":H" + (count + i) + ")"; String totalProfit = "SUM(I" + ktemp + ":I" + (count + i) + ")"; // Set align Text HSSFCell cellTotal0 = row.createCell(0); cellTotal0.setCellStyle(styleC29); HSSFCell cellTotal = row.createCell(1); cellTotal.setCellValue("Total"); cellTotal.setCellStyle(styleC30); HSSFCell cellTotal02 = row.createCell(2); cellTotal02.setCellFormula(totalPax); cellTotal02.setCellStyle(styleC26); HSSFCell cellTotal03 = row.createCell(3); cellTotal03.setCellFormula(totalInvAmount); cellTotal03.setCellStyle(styleC25); HSSFCell cellTotal04 = row.createCell(4); cellTotal04.setCellFormula(totalSalePrice); cellTotal04.setCellStyle(styleC25); HSSFCell cellTotal05 = row.createCell(5); cellTotal05.setCellFormula(totalCost); cellTotal05.setCellStyle(styleC25); HSSFCell cellTotal06 = row.createCell(6); cellTotal06.setCellFormula(totalTiccom); cellTotal06.setCellStyle(styleC25); HSSFCell cellTotal07 = row.createCell(7); cellTotal07.setCellFormula(totalAgentComm); cellTotal07.setCellStyle(styleC25); HSSFCell cellTotal08 = row.createCell(8); cellTotal08.setCellFormula(totalProfit); cellTotal08.setCellStyle(styleC25); count = count + 2; ktemp = count + 3 + i; } int counts = count + i; int countss = count + 1 + i; // Row Agent Name HSSFRow row008 = sheet.createRow(counts); HSSFCell cell0081 = row008.createCell(1); cell0081.setCellValue("Staff name "); cell0081.setCellStyle(styleC23); HSSFCell cell0082 = row008.createCell(2); cell0082.setCellValue(data.getOwner()); cell0082.setCellStyle(styleC22); temp = data.getOwner(); // Header Table HSSFRow row09 = sheet.createRow(countss); HSSFCell cell091 = row09.createCell(0); cell091.setCellValue("Agent"); cell091.setCellStyle(styleC3); sheet.autoSizeColumn(0); HSSFCell cell092 = row09.createCell(1); cell092.setCellValue("Department"); cell092.setCellStyle(styleC3); sheet.autoSizeColumn(1); HSSFCell cell093 = row09.createCell(2); cell093.setCellValue("Pax"); sheet.autoSizeColumn(2); cell093.setCellStyle(styleC3); HSSFCell cell094 = row09.createCell(3); cell094.setCellValue("Invoice Amount"); cell094.setCellStyle(styleC3); sheet.autoSizeColumn(3); HSSFCell cell095 = row09.createCell(4); cell095.setCellValue("Sales Price"); cell095.setCellStyle(styleC3); sheet.autoSizeColumn(4); HSSFCell cell096 = row09.createCell(5); cell096.setCellValue("Cost"); cell096.setCellStyle(styleC3); sheet.autoSizeColumn(5); HSSFCell cell097 = row09.createCell(6); cell097.setCellValue("Ticket Comm"); cell097.setCellStyle(styleC3); sheet.autoSizeColumn(6); HSSFCell cell098 = row09.createCell(7); cell098.setCellValue("Agent Comm"); cell098.setCellStyle(styleC3); sheet.autoSizeColumn(7); HSSFCell cell099 = row09.createCell(8); cell099.setCellValue("Profit"); cell099.setCellStyle(styleC3); sheet.autoSizeColumn(8); count = count + 2; } //set data HSSFRow row = sheet.createRow(count + i); HSSFCell celldata01 = row.createCell(0); celldata01.setCellValue(data.getAgentname()); celldata01.setCellStyle(styleC29); HSSFCell celldata02 = row.createCell(1); celldata02.setCellValue(data.getDepartment()); celldata02.setCellStyle(styleC29); HSSFCell celldata03 = row.createCell(2); celldata03.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPax())) ? 0 : new BigDecimal(data.getPax()).doubleValue()); celldata03.setCellStyle(styleC26); HSSFCell celldata04 = row.createCell(3); celldata04.setCellValue("".equalsIgnoreCase(String.valueOf(data.getInvamount())) ? 0 : new BigDecimal(data.getInvamount()).doubleValue()); celldata04.setCellStyle(styleC25); HSSFCell celldata05 = row.createCell(4); celldata05.setCellValue("".equalsIgnoreCase(String.valueOf(data.getSaleprice())) ? 0 : new BigDecimal(data.getSaleprice()).doubleValue()); celldata05.setCellStyle(styleC25); HSSFCell celldata06 = row.createCell(5); celldata06.setCellValue("".equalsIgnoreCase(String.valueOf(data.getCost())) ? 0 : new BigDecimal(data.getCost()).doubleValue()); celldata06.setCellStyle(styleC25); HSSFCell celldata07 = row.createCell(6); celldata07.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTiccom())) ? 0 : new BigDecimal(data.getTiccom()).doubleValue()); celldata07.setCellStyle(styleC25); HSSFCell celldata08 = row.createCell(7); celldata08.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAgentcom())) ? 0 : new BigDecimal(data.getAgentcom()).doubleValue()); celldata08.setCellStyle(styleC25); HSSFCell celldata09 = row.createCell(8); celldata09.setCellValue("".equalsIgnoreCase(String.valueOf(data.getProfit())) ? 0 : new BigDecimal(data.getProfit()).doubleValue()); celldata09.setCellStyle(styleC25); if ("outbound".equalsIgnoreCase(data.getDepartment())) { TicketFareSummaryByAgentStaff sum = (TicketFareSummaryByAgentStaff) ticketSumByStaff.get(i); int pax = (!"".equalsIgnoreCase(sum.getPax()) ? Integer.parseInt(sum.getPax()) : 0); BigDecimal invamount = new BigDecimal( !"".equalsIgnoreCase(sum.getInvamount()) ? sum.getInvamount() : "0.00"); BigDecimal ticcom = new BigDecimal( !"".equalsIgnoreCase(sum.getTiccom()) ? sum.getTiccom() : "0.00"); BigDecimal saleprice = new BigDecimal( !"".equalsIgnoreCase(sum.getSaleprice()) ? sum.getSaleprice() : "0.00"); BigDecimal agentcomm = new BigDecimal( !"".equalsIgnoreCase(sum.getAgentcom()) ? sum.getAgentcom() : "0.00"); BigDecimal profit = new BigDecimal( !"".equalsIgnoreCase(sum.getProfit()) ? sum.getProfit() : "0.00"); BigDecimal cost = new BigDecimal(!"".equalsIgnoreCase(sum.getCost()) ? sum.getCost() : "0.00"); totalPaxOut = totalPaxOut + pax; totalInvAmountOut = totalInvAmountOut.add(invamount); totalTiccomOut = totalTiccomOut.add(ticcom); totalSalePriceOut = totalSalePriceOut.add(saleprice); totalAgentCommOut = totalAgentCommOut.add(agentcomm); totalProfitOut = totalProfitOut.add(profit); totalCostOut = totalCostOut.add(cost); } if ("wendy".equalsIgnoreCase(data.getDepartment())) { TicketFareSummaryByAgentStaff sum = (TicketFareSummaryByAgentStaff) ticketSumByStaff.get(i); int pax = (!"".equalsIgnoreCase(sum.getPax()) ? Integer.parseInt(sum.getPax()) : 0); BigDecimal invamount = new BigDecimal( !"".equalsIgnoreCase(sum.getInvamount()) ? sum.getInvamount() : "0.00"); BigDecimal ticcom = new BigDecimal( !"".equalsIgnoreCase(sum.getTiccom()) ? sum.getTiccom() : "0.00"); BigDecimal saleprice = new BigDecimal( !"".equalsIgnoreCase(sum.getSaleprice()) ? sum.getSaleprice() : "0.00"); BigDecimal agentcomm = new BigDecimal( !"".equalsIgnoreCase(sum.getAgentcom()) ? sum.getAgentcom() : "0.00"); BigDecimal profit = new BigDecimal( !"".equalsIgnoreCase(sum.getProfit()) ? sum.getProfit() : "0.00"); BigDecimal cost = new BigDecimal(!"".equalsIgnoreCase(sum.getCost()) ? sum.getCost() : "0.00"); totalPaxWendy = totalPaxWendy + pax; totalInvAmountWendy = totalInvAmountWendy.add(invamount); totalTiccomWendy = totalTiccomWendy.add(ticcom); totalSalePriceWendy = totalSalePriceWendy.add(saleprice); totalAgentCommWendy = totalAgentCommWendy.add(agentcomm); totalProfitWendy = totalProfitWendy.add(profit); totalCostWendy = totalCostWendy.add(cost); } // set total last row if (i == (ticketSumByStaff.size() - 1)) { HSSFRow rows = sheet.createRow(count + 1 + i); String totalPax = "SUM(C" + ktemp + ":C" + (count + i + 1) + ")"; String totalInvAmount = "SUM(D" + ktemp + ":D" + (count + i + 1) + ")"; String totalSalePrice = "SUM(E" + ktemp + ":E" + (count + i + 1) + ")"; String totalCost = "SUM(F" + ktemp + ":F" + (count + i + 1) + ")"; String totalTiccom = "SUM(G" + ktemp + ":G" + (count + i + 1) + ")"; String totalAgentComm = "SUM(H" + ktemp + ":H" + (count + i + 1) + ")"; String totalProfit = "SUM(I" + ktemp + ":I" + (count + i + 1) + ")"; // int totalPax = 0; // BigDecimal totalInvAmount = new BigDecimal("0.00"); // BigDecimal totalTiccom = new BigDecimal("0.00"); // BigDecimal totalSalePrice = new BigDecimal("0.00"); // BigDecimal totalAgentComm = new BigDecimal("0.00"); // BigDecimal totalProfit = new BigDecimal("0.00"); // for(int k=ktemp;k<ticketSumByStaff.size();k++){ // TicketFareSummaryByAgentStaff sum = (TicketFareSummaryByAgentStaff)ticketSumByStaff.get(k); // int pax = (!"".equalsIgnoreCase(sum.getPax()) ? Integer.parseInt(sum.getPax()) : 0); // BigDecimal invamount = new BigDecimal(!"".equalsIgnoreCase(sum.getInvamount()) ? sum.getInvamount() : "0.00"); // BigDecimal ticcom = new BigDecimal(!"".equalsIgnoreCase(sum.getTiccom()) ? sum.getTiccom() : "0.00"); // BigDecimal saleprice = new BigDecimal(!"".equalsIgnoreCase(sum.getSaleprice()) ? sum.getSaleprice() : "0.00"); // BigDecimal agentcomm = new BigDecimal(!"".equalsIgnoreCase(sum.getAgentcom()) ? sum.getAgentcom() : "0.00"); // BigDecimal profit = new BigDecimal(!"".equalsIgnoreCase(sum.getProfit()) ? sum.getProfit() : "0.00"); // totalPax = totalPax+pax; // totalInvAmount = totalInvAmount.add(invamount); // totalTiccom = totalTiccom.add(ticcom); // totalSalePrice = totalSalePrice.add(saleprice); // totalAgentComm = totalAgentComm.add(agentcomm); // totalProfit = totalProfit.add(profit); // } HSSFCell celldatas0 = rows.createCell(0); celldatas0.setCellStyle(styleC29); HSSFCell celldatas01 = rows.createCell(1); celldatas01.setCellValue("Total"); celldatas01.setCellStyle(styleC30); HSSFCell celldatas02 = rows.createCell(2); celldatas02.setCellFormula(totalPax); celldatas02.setCellStyle(styleC26); HSSFCell celldatas03 = rows.createCell(3); celldatas03.setCellFormula(totalInvAmount); celldatas03.setCellStyle(styleC25); HSSFCell celldatas04 = rows.createCell(4); celldatas04.setCellFormula(totalSalePrice); celldatas04.setCellStyle(styleC25); HSSFCell celldatas05 = rows.createCell(5); celldatas05.setCellFormula(totalCost); celldatas05.setCellStyle(styleC25); HSSFCell celldatas06 = rows.createCell(6); celldatas06.setCellFormula(totalTiccom); celldatas06.setCellStyle(styleC25); HSSFCell celldatas07 = rows.createCell(7); celldatas07.setCellFormula(totalAgentComm); celldatas07.setCellStyle(styleC25); HSSFCell celldatas08 = rows.createCell(8); celldatas08.setCellFormula(totalProfit); celldatas08.setCellStyle(styleC25); count = count + 3; rows = sheet.createRow(count + i); HSSFCell celldataOut01 = rows.createCell(1); celldataOut01.setCellValue("Summary Outbound"); celldataOut01.setCellStyle(styleC22); HSSFCell celldataOut02 = rows.createCell(2); celldataOut02.setCellValue("".equalsIgnoreCase(String.valueOf(totalPaxOut)) ? 0 : new BigDecimal(totalPaxOut).doubleValue()); celldataOut02.setCellStyle(styleC28); HSSFCell celldataOut03 = rows.createCell(3); celldataOut03.setCellValue("".equalsIgnoreCase(String.valueOf(totalInvAmountOut)) ? 0 : totalInvAmountOut.doubleValue()); celldataOut03.setCellStyle(styleC27); HSSFCell celldataOut04 = rows.createCell(4); celldataOut04.setCellValue("".equalsIgnoreCase(String.valueOf(totalSalePriceOut)) ? 0 : totalSalePriceOut.doubleValue()); celldataOut04.setCellStyle(styleC27); HSSFCell celldataOut05 = rows.createCell(5); celldataOut05.setCellValue( "".equalsIgnoreCase(String.valueOf(totalCostOut)) ? 0 : totalCostOut.doubleValue()); celldataOut05.setCellStyle(styleC27); HSSFCell celldataOut06 = rows.createCell(6); celldataOut06.setCellValue( "".equalsIgnoreCase(String.valueOf(totalTiccomOut)) ? 0 : totalTiccomOut.doubleValue()); celldataOut06.setCellStyle(styleC27); HSSFCell celldataOut07 = rows.createCell(7); celldataOut07.setCellValue("".equalsIgnoreCase(String.valueOf(totalAgentCommOut)) ? 0 : totalAgentCommOut.doubleValue()); celldataOut07.setCellStyle(styleC27); HSSFCell celldataOut08 = rows.createCell(8); celldataOut08.setCellValue( "".equalsIgnoreCase(String.valueOf(totalProfitOut)) ? 0 : totalProfitOut.doubleValue()); celldataOut08.setCellStyle(styleC27); rows = sheet.createRow(count + 1 + i); HSSFCell celldataWen01 = rows.createCell(1); celldataWen01.setCellValue("Summary Wendy"); celldataWen01.setCellStyle(styleC22); HSSFCell celldataWen02 = rows.createCell(2); celldataWen02.setCellValue("".equalsIgnoreCase(String.valueOf(totalPaxWendy)) ? 0 : new BigDecimal(totalPaxWendy).doubleValue()); celldataWen02.setCellStyle(styleC28); HSSFCell celldataWen03 = rows.createCell(3); celldataWen03.setCellValue("".equalsIgnoreCase(String.valueOf(totalInvAmountWendy)) ? 0 : totalInvAmountWendy.doubleValue()); celldataWen03.setCellStyle(styleC27); HSSFCell celldataWen04 = rows.createCell(4); celldataWen04.setCellValue("".equalsIgnoreCase(String.valueOf(totalSalePriceWendy)) ? 0 : totalSalePriceWendy.doubleValue()); celldataWen04.setCellStyle(styleC27); HSSFCell celldataWen05 = rows.createCell(5); celldataWen05.setCellValue( "".equalsIgnoreCase(String.valueOf(totalCostWendy)) ? 0 : totalCostWendy.doubleValue()); celldataWen05.setCellStyle(styleC27); HSSFCell celldataWen06 = rows.createCell(6); celldataWen06.setCellValue( "".equalsIgnoreCase(String.valueOf(totalTiccomWendy)) ? 0 : totalTiccomWendy.doubleValue()); celldataWen06.setCellStyle(styleC27); HSSFCell celldataWen07 = rows.createCell(7); celldataWen07.setCellValue("".equalsIgnoreCase(String.valueOf(totalAgentCommWendy)) ? 0 : totalAgentCommWendy.doubleValue()); celldataWen07.setCellStyle(styleC27); HSSFCell celldataWen08 = rows.createCell(8); celldataWen08.setCellValue( "".equalsIgnoreCase(String.valueOf(totalProfitWendy)) ? 0 : totalProfitWendy.doubleValue()); celldataWen08.setCellStyle(styleC27); // int totalPaxWI = 0; // BigDecimal totalInvAmountWI = new BigDecimal("0.00"); // BigDecimal totalTiccomWI = new BigDecimal("0.00"); // BigDecimal totalSalePriceWI = new BigDecimal("0.00"); // BigDecimal totalAgentCommWI = new BigDecimal("0.00"); // BigDecimal totalProfitWI = new BigDecimal("0.00"); // totalPaxWI = totalPaxOut+totalPaxWendy; // totalInvAmountWI = totalInvAmountOut.add(totalInvAmountWendy); // totalTiccomWI = totalTiccomOut.add(totalTiccomWendy); // totalSalePriceWI = totalSalePriceOut.add(totalSalePriceWendy); // totalAgentCommWI = totalAgentCommOut.add(totalAgentCommWendy); // totalProfitWI = totalProfitOut.add(totalProfitWendy); rows = sheet.createRow(count + 2 + i); String totalPaxWI = "SUM(C" + (count + i + 1) + ":C" + (count + i + 2) + ")"; String totalInvAmountWI = "SUM(D" + (count + i + 1) + ":D" + (count + i + 2) + ")"; String totalSalePriceWI = "SUM(E" + (count + i + 1) + ":E" + (count + i + 2) + ")"; String totalCostWI = "SUM(F" + (count + i + 1) + ":F" + (count + i + 2) + ")"; String totalTiccomWI = "SUM(G" + (count + i + 1) + ":G" + (count + i + 2) + ")"; String totalAgentCommWI = "SUM(H" + (count + i + 1) + ":H" + (count + i + 2) + ")"; String totalProfitWI = "SUM(I" + (count + i + 1) + ":I" + (count + i + 2) + ")"; HSSFCell celldataWI01 = rows.createCell(1); celldataWI01.setCellValue("Total"); celldataWI01.setCellStyle(styleC22); HSSFCell celldataWI02 = rows.createCell(2); celldataWI02.setCellFormula(totalPaxWI); celldataWI02.setCellStyle(styleC28); HSSFCell celldataWI03 = rows.createCell(3); celldataWI03.setCellFormula(totalInvAmountWI); celldataWI03.setCellStyle(styleC27); HSSFCell celldataWI04 = rows.createCell(4); celldataWI04.setCellFormula(totalSalePriceWI); celldataWI04.setCellStyle(styleC27); HSSFCell celldataWI05 = rows.createCell(5); celldataWI05.setCellFormula(totalCostWI); celldataWI05.setCellStyle(styleC27); HSSFCell celldataWI06 = rows.createCell(6); celldataWI06.setCellFormula(totalTiccomWI); celldataWI06.setCellStyle(styleC27); HSSFCell celldataWI07 = rows.createCell(7); celldataWI07.setCellFormula(totalAgentCommWI); celldataWI07.setCellStyle(styleC27); HSSFCell celldataWI08 = rows.createCell(8); celldataWI08.setCellFormula(totalProfitWI); celldataWI08.setCellStyle(styleC27); } } for (int j = 0; j < 8; j++) { sheet.autoSizeColumn(j); } }
From source file:com.smi.travel.controller.excel.checking.airticket.TicketFareSummary.java
public void genTicketFareSummaryByAgent(HSSFWorkbook wb, List ticketSumByAgent) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); String sheetName = "Sheet1";// name of sheet HSSFSheet sheet = wb.createSheet(sheetName); TicketFareSummaryByAgentStaff dataheader = new TicketFareSummaryByAgentStaff(); 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.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); 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(styleC22.ALIGN_CENTER); if (!ticketSumByAgent.isEmpty()) { dataheader = (TicketFareSummaryByAgentStaff) ticketSumByAgent.get(0); // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheet.createRow(0); HSSFCell cell1 = row1.createCell(0); cell1.setCellValue("List Ticket Summary By Agent"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cell1.setCellStyle(styleC1);// ww w . ja v a 2 s . c om sheet.addMergedRegion(CellRangeAddress.valueOf("A1:G1")); // Row 2 HSSFRow row2 = sheet.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue("Print By : "); cell21.setCellStyle(styleC21); HSSFCell cell22 = row2.createCell(1); cell22.setCellValue(dataheader.getPrintby()); cell22.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2")); HSSFCell cell23 = row2.createCell(4); cell23.setCellValue("Airline Code : "); cell23.setCellStyle(styleC21); HSSFCell cell24 = row2.createCell(5); cell24.setCellValue(dataheader.getAirline()); cell24.setCellStyle(styleC22); // Row 3 HSSFRow row3 = sheet.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Department : "); cell31.setCellStyle(styleC21); HSSFCell cell32 = row3.createCell(1); cell32.setCellValue(dataheader.getHeaddepartment()); cell32.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3")); HSSFCell cell33 = row3.createCell(4); cell33.setCellValue("Ticket Type : "); cell33.setCellStyle(styleC21); HSSFCell cell34 = row3.createCell(5); cell34.setCellValue(dataheader.getHeadtickettype()); cell34.setCellStyle(styleC22); // Row 4 HSSFRow row4 = sheet.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Term Pay : "); cell41.setCellStyle(styleC21); HSSFCell cell42 = row4.createCell(1); cell42.setCellValue(dataheader.getTermpay()); cell42.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4")); HSSFCell cell43 = row4.createCell(4); cell43.setCellValue("Ticket Buy : "); cell43.setCellStyle(styleC21); HSSFCell cell44 = row4.createCell(5); cell44.setCellValue(dataheader.getHeadticketbuy()); cell44.setCellStyle(styleC22); // Row 5 HSSFRow row5 = sheet.createRow(4); HSSFCell cell51 = row5.createCell(0); cell51.setCellValue("Airline : "); cell51.setCellStyle(styleC21); HSSFCell cell52 = row5.createCell(1); cell52.setCellValue(dataheader.getHeaderairline()); cell52.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5")); HSSFCell cell53 = row5.createCell(4); cell53.setCellValue("Sale Staff : "); cell53.setCellStyle(styleC21); HSSFCell cell54 = row5.createCell(5); cell54.setCellValue(dataheader.getHeadsale()); cell54.setCellStyle(styleC22); // Row 6 HSSFRow row6 = sheet.createRow(5); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue("Issue Date : "); cell61.setCellStyle(styleC21); sheet.addMergedRegion(CellRangeAddress.valueOf("A6:D6")); HSSFCell cell62 = row6.createCell(4); if (!"".equalsIgnoreCase(dataheader.getIssuefrom())) { cell62.setCellValue(dataheader.getIssuefrom()); cell62.setCellStyle(styleC22); } HSSFCell cell63 = row6.createCell(5); if (!"".equalsIgnoreCase(dataheader.getIssueto())) { cell63.setCellValue("to " + dataheader.getIssueto()); cell63.setCellStyle(styleC22); } // Row 7 HSSFRow row7 = sheet.createRow(6); HSSFCell cell71 = row7.createCell(0); cell71.setCellValue("Invoice Date : "); cell71.setCellStyle(styleC21); sheet.addMergedRegion(CellRangeAddress.valueOf("A7:D7")); HSSFCell cell72 = row7.createCell(4); if (!"".equalsIgnoreCase(dataheader.getInvdatefrom())) { cell72.setCellValue(dataheader.getInvdatefrom()); cell72.setCellStyle(styleC22); } HSSFCell cell73 = row7.createCell(5); if (!"".equalsIgnoreCase(dataheader.getInvdateto())) { cell73.setCellValue("to " + dataheader.getInvdateto()); cell73.setCellStyle(styleC22); } // Row 8 HSSFRow row8 = sheet.createRow(7); HSSFCell cell81 = row8.createCell(0); cell81.setCellValue("Print on : "); cell81.setCellStyle(styleC21); sheet.addMergedRegion(CellRangeAddress.valueOf("A8:D8")); HSSFCell cell82 = row8.createCell(4); cell82.setCellValue(dataheader.getPrinton()); cell82.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("E8:F8")); } // Header Table style HSSFCellStyle styleC3 = wb.createCellStyle(); styleC3.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3.setAlignment(styleC3.ALIGN_CENTER); styleC3.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC3.setBorderBottom(HSSFCellStyle.BORDER_THIN); // Detail of Table String temp = ""; int count = 9; int ktemp = 12; //Total Outbound int totalPaxOut = 0; BigDecimal totalInvAmountOut = new BigDecimal("0.00"); BigDecimal totalTiccomOut = new BigDecimal("0.00"); BigDecimal totalSalePriceOut = new BigDecimal("0.00"); BigDecimal totalAgentCommOut = new BigDecimal("0.00"); BigDecimal totalProfitOut = new BigDecimal("0.00"); BigDecimal totalCostOut = new BigDecimal("0.00"); //Total Wendy int totalPaxWendy = 0; BigDecimal totalInvAmountWendy = new BigDecimal("0.00"); BigDecimal totalTiccomWendy = new BigDecimal("0.00"); BigDecimal totalSalePriceWendy = new BigDecimal("0.00"); BigDecimal totalAgentCommWendy = new BigDecimal("0.00"); BigDecimal totalProfitWendy = new BigDecimal("0.00"); BigDecimal totalCostWendy = new BigDecimal("0.00"); for (int i = 0; i < ticketSumByAgent.size(); i++) { TicketFareSummaryByAgentStaff data = (TicketFareSummaryByAgentStaff) ticketSumByAgent.get(i); if (!temp.equalsIgnoreCase(data.getAgentname())) { if (!"".equalsIgnoreCase(temp)) { HSSFRow row = sheet.createRow(count + i); String totalPax = "SUM(C" + ktemp + ":C" + (count + i) + ")"; String totalInvAmount = "SUM(D" + ktemp + ":D" + (count + i) + ")"; String totalSalePrice = "SUM(E" + ktemp + ":E" + (count + i) + ")"; String totalCost = "SUM(F" + ktemp + ":F" + (count + i) + ")"; String totalTiccom = "SUM(G" + ktemp + ":G" + (count + i) + ")"; String totalAgentComm = "SUM(H" + ktemp + ":H" + (count + i) + ")"; String totalProfit = "SUM(I" + ktemp + ":I" + (count + i) + ")"; // Set align Text HSSFCell cellTotal0 = row.createCell(0); cellTotal0.setCellStyle(styleC29); HSSFCell cellTotal = row.createCell(1); cellTotal.setCellValue("Total"); cellTotal.setCellStyle(styleC30); HSSFCell cellTotal02 = row.createCell(2); cellTotal02.setCellFormula(totalPax); cellTotal02.setCellStyle(styleC26); HSSFCell cellTotal03 = row.createCell(3); cellTotal03.setCellFormula(totalInvAmount); cellTotal03.setCellStyle(styleC25); HSSFCell cellTotal04 = row.createCell(4); cellTotal04.setCellFormula(totalSalePrice); cellTotal04.setCellStyle(styleC25); HSSFCell cellTotal05 = row.createCell(5); cellTotal05.setCellFormula(totalCost); cellTotal05.setCellStyle(styleC25); HSSFCell cellTotal06 = row.createCell(6); cellTotal06.setCellFormula(totalTiccom); cellTotal06.setCellStyle(styleC25); HSSFCell cellTotal07 = row.createCell(7); cellTotal07.setCellFormula(totalAgentComm); cellTotal07.setCellStyle(styleC25); HSSFCell cellTotal08 = row.createCell(8); cellTotal08.setCellFormula(totalProfit); cellTotal08.setCellStyle(styleC25); count = count + 2; ktemp = count + 3 + i; } int counts = count + i; int countss = count + 1 + i; // Row Agent Name HSSFRow row008 = sheet.createRow(counts); HSSFCell cell0081 = row008.createCell(1); cell0081.setCellValue("Agent name "); cell0081.setCellStyle(styleC23); HSSFCell cell0082 = row008.createCell(2); cell0082.setCellValue(data.getAgentname()); cell0082.setCellStyle(styleC22); temp = data.getAgentname(); // Header Table HSSFRow row09 = sheet.createRow(countss); HSSFCell cell091 = row09.createCell(0); cell091.setCellValue("Staff"); cell091.setCellStyle(styleC3); sheet.autoSizeColumn(0); HSSFCell cell092 = row09.createCell(1); cell092.setCellValue("Department"); cell092.setCellStyle(styleC3); sheet.autoSizeColumn(1); HSSFCell cell093 = row09.createCell(2); cell093.setCellValue("Pax"); sheet.autoSizeColumn(2); cell093.setCellStyle(styleC3); HSSFCell cell094 = row09.createCell(3); cell094.setCellValue("Invoice Amount"); cell094.setCellStyle(styleC3); sheet.autoSizeColumn(3); HSSFCell cell095 = row09.createCell(4); cell095.setCellValue("Sales Price"); cell095.setCellStyle(styleC3); sheet.autoSizeColumn(4); HSSFCell cell096 = row09.createCell(5); cell096.setCellValue("Cost"); cell096.setCellStyle(styleC3); sheet.autoSizeColumn(5); HSSFCell cell097 = row09.createCell(6); cell097.setCellValue("Ticket Comm"); cell097.setCellStyle(styleC3); sheet.autoSizeColumn(6); HSSFCell cell098 = row09.createCell(7); cell098.setCellValue("Agent Comm"); cell098.setCellStyle(styleC3); sheet.autoSizeColumn(7); HSSFCell cell099 = row09.createCell(8); cell099.setCellValue("Profit"); cell099.setCellStyle(styleC3); sheet.autoSizeColumn(8); count = count + 2; } //set data HSSFRow row = sheet.createRow(count + i); HSSFCell celldata01 = row.createCell(0); celldata01.setCellValue(data.getOwner()); celldata01.setCellStyle(styleC29); HSSFCell celldata02 = row.createCell(1); celldata02.setCellValue(data.getDepartment()); celldata02.setCellStyle(styleC29); HSSFCell celldata03 = row.createCell(2); celldata03.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPax())) ? 0 : new BigDecimal(data.getPax()).doubleValue()); celldata03.setCellStyle(styleC26); HSSFCell celldata04 = row.createCell(3); celldata04.setCellValue("".equalsIgnoreCase(String.valueOf(data.getInvamount())) ? 0 : new BigDecimal(data.getInvamount()).doubleValue()); celldata04.setCellStyle(styleC25); HSSFCell celldata05 = row.createCell(4); celldata05.setCellValue("".equalsIgnoreCase(String.valueOf(data.getSaleprice())) ? 0 : new BigDecimal(data.getSaleprice()).doubleValue()); celldata05.setCellStyle(styleC25); HSSFCell celldata06 = row.createCell(5); celldata06.setCellValue("".equalsIgnoreCase(String.valueOf(data.getCost())) ? 0 : new BigDecimal(data.getCost()).doubleValue()); celldata06.setCellStyle(styleC25); HSSFCell celldata07 = row.createCell(6); celldata07.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTiccom())) ? 0 : new BigDecimal(data.getTiccom()).doubleValue()); celldata07.setCellStyle(styleC25); HSSFCell celldata08 = row.createCell(7); celldata08.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAgentcom())) ? 0 : new BigDecimal(data.getAgentcom()).doubleValue()); celldata08.setCellStyle(styleC25); HSSFCell celldata09 = row.createCell(8); celldata09.setCellValue("".equalsIgnoreCase(String.valueOf(data.getProfit())) ? 0 : new BigDecimal(data.getProfit()).doubleValue()); celldata09.setCellStyle(styleC25); if ("outbound".equalsIgnoreCase(data.getDepartment())) { TicketFareSummaryByAgentStaff sum = (TicketFareSummaryByAgentStaff) ticketSumByAgent.get(i); int pax = (!"".equalsIgnoreCase(sum.getPax()) ? Integer.parseInt(sum.getPax()) : 0); BigDecimal invamount = new BigDecimal( !"".equalsIgnoreCase(sum.getInvamount()) ? sum.getInvamount() : "0.00"); BigDecimal ticcom = new BigDecimal( !"".equalsIgnoreCase(sum.getTiccom()) ? sum.getTiccom() : "0.00"); BigDecimal saleprice = new BigDecimal( !"".equalsIgnoreCase(sum.getSaleprice()) ? sum.getSaleprice() : "0.00"); BigDecimal agentcomm = new BigDecimal( !"".equalsIgnoreCase(sum.getAgentcom()) ? sum.getAgentcom() : "0.00"); BigDecimal profit = new BigDecimal( !"".equalsIgnoreCase(sum.getProfit()) ? sum.getProfit() : "0.00"); BigDecimal cost = new BigDecimal(!"".equalsIgnoreCase(sum.getCost()) ? sum.getCost() : "0.00"); totalPaxOut = totalPaxOut + pax; totalInvAmountOut = totalInvAmountOut.add(invamount); totalTiccomOut = totalTiccomOut.add(ticcom); totalSalePriceOut = totalSalePriceOut.add(saleprice); totalAgentCommOut = totalAgentCommOut.add(agentcomm); totalProfitOut = totalProfitOut.add(profit); totalCostOut = totalCostOut.add(cost); } if ("wendy".equalsIgnoreCase(data.getDepartment())) { TicketFareSummaryByAgentStaff sum = (TicketFareSummaryByAgentStaff) ticketSumByAgent.get(i); int pax = (!"".equalsIgnoreCase(sum.getPax()) ? Integer.parseInt(sum.getPax()) : 0); BigDecimal invamount = new BigDecimal( !"".equalsIgnoreCase(sum.getInvamount()) ? sum.getInvamount() : "0.00"); BigDecimal ticcom = new BigDecimal( !"".equalsIgnoreCase(sum.getTiccom()) ? sum.getTiccom() : "0.00"); BigDecimal saleprice = new BigDecimal( !"".equalsIgnoreCase(sum.getSaleprice()) ? sum.getSaleprice() : "0.00"); BigDecimal agentcomm = new BigDecimal( !"".equalsIgnoreCase(sum.getAgentcom()) ? sum.getAgentcom() : "0.00"); BigDecimal profit = new BigDecimal( !"".equalsIgnoreCase(sum.getProfit()) ? sum.getProfit() : "0.00"); BigDecimal cost = new BigDecimal(!"".equalsIgnoreCase(sum.getCost()) ? sum.getCost() : "0.00"); totalPaxWendy = totalPaxWendy + pax; totalInvAmountWendy = totalInvAmountWendy.add(invamount); totalTiccomWendy = totalTiccomWendy.add(ticcom); totalSalePriceWendy = totalSalePriceWendy.add(saleprice); totalAgentCommWendy = totalAgentCommWendy.add(agentcomm); totalProfitWendy = totalProfitWendy.add(profit); totalCostWendy = totalCostWendy.add(cost); } // set total last row if (i == (ticketSumByAgent.size() - 1)) { HSSFRow rows = sheet.createRow(count + 1 + i); String totalPax = "SUM(C" + ktemp + ":C" + (count + i + 1) + ")"; String totalInvAmount = "SUM(D" + ktemp + ":D" + (count + i + 1) + ")"; String totalSalePrice = "SUM(E" + ktemp + ":E" + (count + i + 1) + ")"; String totalCost = "SUM(F" + ktemp + ":F" + (count + i + 1) + ")"; String totalTiccom = "SUM(G" + ktemp + ":G" + (count + i + 1) + ")"; String totalAgentComm = "SUM(H" + ktemp + ":H" + (count + i + 1) + ")"; String totalProfit = "SUM(I" + ktemp + ":I" + (count + i + 1) + ")"; HSSFCell celldatas0 = rows.createCell(0); celldatas0.setCellStyle(styleC29); HSSFCell celldatas01 = rows.createCell(1); celldatas01.setCellValue("Total"); celldatas01.setCellStyle(styleC30); HSSFCell celldatas02 = rows.createCell(2); celldatas02.setCellFormula(totalPax); celldatas02.setCellStyle(styleC26); HSSFCell celldatas03 = rows.createCell(3); celldatas03.setCellFormula(totalInvAmount); celldatas03.setCellStyle(styleC25); HSSFCell celldatas04 = rows.createCell(4); celldatas04.setCellFormula(totalSalePrice); celldatas04.setCellStyle(styleC25); HSSFCell celldatas05 = rows.createCell(5); celldatas05.setCellFormula(totalCost); celldatas05.setCellStyle(styleC25); HSSFCell celldatas06 = rows.createCell(6); celldatas06.setCellFormula(totalTiccom); celldatas06.setCellStyle(styleC25); HSSFCell celldatas07 = rows.createCell(7); celldatas07.setCellFormula(totalAgentComm); celldatas07.setCellStyle(styleC25); HSSFCell celldatas08 = rows.createCell(8); celldatas08.setCellFormula(totalProfit); celldatas08.setCellStyle(styleC25); count = count + 3; rows = sheet.createRow(count + i); HSSFCell celldataOut01 = rows.createCell(1); celldataOut01.setCellValue("Summary Outbound"); celldataOut01.setCellStyle(styleC22); HSSFCell celldataOut02 = rows.createCell(2); celldataOut02.setCellValue("".equalsIgnoreCase(String.valueOf(totalPaxOut)) ? 0 : new BigDecimal(totalPaxOut).doubleValue()); celldataOut02.setCellStyle(styleC28); HSSFCell celldataOut03 = rows.createCell(3); celldataOut03.setCellValue("".equalsIgnoreCase(String.valueOf(totalInvAmountOut)) ? 0 : totalInvAmountOut.doubleValue()); celldataOut03.setCellStyle(styleC27); HSSFCell celldataOut04 = rows.createCell(4); celldataOut04.setCellValue("".equalsIgnoreCase(String.valueOf(totalSalePriceOut)) ? 0 : totalSalePriceOut.doubleValue()); celldataOut04.setCellStyle(styleC27); HSSFCell celldataOut05 = rows.createCell(5); celldataOut05.setCellValue( "".equalsIgnoreCase(String.valueOf(totalCostOut)) ? 0 : totalCostOut.doubleValue()); celldataOut05.setCellStyle(styleC27); HSSFCell celldataOut06 = rows.createCell(6); celldataOut06.setCellValue( "".equalsIgnoreCase(String.valueOf(totalTiccomOut)) ? 0 : totalTiccomOut.doubleValue()); celldataOut06.setCellStyle(styleC27); HSSFCell celldataOut07 = rows.createCell(7); celldataOut07.setCellValue("".equalsIgnoreCase(String.valueOf(totalAgentCommOut)) ? 0 : totalAgentCommOut.doubleValue()); celldataOut07.setCellStyle(styleC27); HSSFCell celldataOut08 = rows.createCell(8); celldataOut08.setCellValue( "".equalsIgnoreCase(String.valueOf(totalProfitOut)) ? 0 : totalProfitOut.doubleValue()); celldataOut08.setCellStyle(styleC27); rows = sheet.createRow(count + 1 + i); HSSFCell celldataWen01 = rows.createCell(1); celldataWen01.setCellValue("Summary Wendy"); celldataWen01.setCellStyle(styleC22); HSSFCell celldataWen02 = rows.createCell(2); celldataWen02.setCellValue("".equalsIgnoreCase(String.valueOf(totalPaxWendy)) ? 0 : new BigDecimal(totalPaxWendy).doubleValue()); celldataWen02.setCellStyle(styleC28); HSSFCell celldataWen03 = rows.createCell(3); celldataWen03.setCellValue("".equalsIgnoreCase(String.valueOf(totalInvAmountWendy)) ? 0 : totalInvAmountWendy.doubleValue()); celldataWen03.setCellStyle(styleC27); HSSFCell celldataWen04 = rows.createCell(4); celldataWen04.setCellValue("".equalsIgnoreCase(String.valueOf(totalSalePriceWendy)) ? 0 : totalSalePriceWendy.doubleValue()); celldataWen04.setCellStyle(styleC27); HSSFCell celldataWen05 = rows.createCell(5); celldataWen05.setCellValue( "".equalsIgnoreCase(String.valueOf(totalCostWendy)) ? 0 : totalCostWendy.doubleValue()); celldataWen05.setCellStyle(styleC27); HSSFCell celldataWen06 = rows.createCell(6); celldataWen06.setCellValue( "".equalsIgnoreCase(String.valueOf(totalTiccomWendy)) ? 0 : totalTiccomWendy.doubleValue()); celldataWen06.setCellStyle(styleC27); HSSFCell celldataWen07 = rows.createCell(7); celldataWen07.setCellValue("".equalsIgnoreCase(String.valueOf(totalAgentCommWendy)) ? 0 : totalAgentCommWendy.doubleValue()); celldataWen07.setCellStyle(styleC27); HSSFCell celldataWen08 = rows.createCell(8); celldataWen08.setCellValue( "".equalsIgnoreCase(String.valueOf(totalProfitWendy)) ? 0 : totalProfitWendy.doubleValue()); celldataWen08.setCellStyle(styleC27); rows = sheet.createRow(count + 2 + i); String totalPaxWI = "SUM(C" + (count + i + 1) + ":C" + (count + i + 2) + ")"; String totalInvAmountWI = "SUM(D" + (count + i + 1) + ":D" + (count + i + 2) + ")"; String totalSalePriceWI = "SUM(E" + (count + i + 1) + ":E" + (count + i + 2) + ")"; String totalCostWI = "SUM(F" + (count + i + 1) + ":F" + (count + i + 2) + ")"; String totalTiccomWI = "SUM(G" + (count + i + 1) + ":G" + (count + i + 2) + ")"; String totalAgentCommWI = "SUM(H" + (count + i + 1) + ":H" + (count + i + 2) + ")"; String totalProfitWI = "SUM(I" + (count + i + 1) + ":I" + (count + i + 2) + ")"; HSSFCell celldataWI01 = rows.createCell(1); celldataWI01.setCellValue("Total"); celldataWI01.setCellStyle(styleC22); HSSFCell celldataWI02 = rows.createCell(2); celldataWI02.setCellFormula(totalPaxWI); celldataWI02.setCellStyle(styleC28); HSSFCell celldataWI03 = rows.createCell(3); celldataWI03.setCellFormula(totalInvAmountWI); celldataWI03.setCellStyle(styleC27); HSSFCell celldataWI04 = rows.createCell(4); celldataWI04.setCellFormula(totalSalePriceWI); celldataWI04.setCellStyle(styleC27); HSSFCell celldataWI05 = rows.createCell(5); celldataWI05.setCellFormula(totalCostWI); celldataWI05.setCellStyle(styleC27); HSSFCell celldataWI06 = rows.createCell(6); celldataWI06.setCellFormula(totalTiccomWI); celldataWI06.setCellStyle(styleC27); HSSFCell celldataWI07 = rows.createCell(7); celldataWI07.setCellFormula(totalAgentCommWI); celldataWI07.setCellStyle(styleC27); HSSFCell celldataWI08 = rows.createCell(8); celldataWI08.setCellFormula(totalProfitWI); celldataWI08.setCellStyle(styleC27); } } for (int j = 0; j < 8; j++) { sheet.autoSizeColumn(j); } }
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 ww w. java2 s . c om*/ 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 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 w w . j ava 2 s. 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);//from www.j a v a 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.migration.MainMigrate.java
public static void ExportTaxinvoiceReport(List reptax) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("TaxInvoice"); 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); HSSFRow row1 = sheet.createRow(0);// w w w .j av a 2 s . com HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("Tax Invoice Report"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row2 = sheet.createRow(2); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("ID"); cell20.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("TAX ID"); cell21.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("TAX NO"); cell22.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("TAX DATE"); cell23.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("CODE AP"); cell24.setCellStyle(styleC3Center); sheet.autoSizeColumn(4); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("DESCRIPTION"); cell25.setCellStyle(styleC3Center); sheet.autoSizeColumn(5); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("GROSS AMOUNT"); cell26.setCellStyle(styleC3Center); sheet.autoSizeColumn(6); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("VAT AMOUNT"); cell27.setCellStyle(styleC3Center); sheet.autoSizeColumn(7); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("AMOUNT"); cell28.setCellStyle(styleC3Center); sheet.autoSizeColumn(8); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("FLAG TYPE"); cell29.setCellStyle(styleC3Center); sheet.autoSizeColumn(9); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("INVOICE TYPE"); cell30.setCellStyle(styleC3Center); sheet.autoSizeColumn(10); HSSFCell cell31 = row2.createCell(11); cell31.setCellValue("TAX NO 1"); cell31.setCellStyle(styleC3Center); sheet.autoSizeColumn(11); HSSFCell cell32 = row2.createCell(12); cell32.setCellValue("BRANCH"); cell32.setCellStyle(styleC3Center); sheet.autoSizeColumn(12); HSSFCell cell33 = row2.createCell(13); cell33.setCellValue("BRANCH NO"); cell33.setCellStyle(styleC3Center); sheet.autoSizeColumn(13); int count = 3; 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); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); 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")); for (int i = 0; i < reptax.size(); i++) { ReportTaxInvoice data = (ReportTaxInvoice) reptax.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getId()); cell0.setCellStyle(styleC23); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getTaxid()); cell1.setCellStyle(styleC23); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getTaxno()); cell13.setCellStyle(styleC23); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(String.valueOf(data.getTaxdate())); cell2.setCellStyle(styleC23); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getCodeap()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getDescription().trim()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(!"null".equalsIgnoreCase(String.valueOf(data.getGrossamount())) ? (data.getGrossamount()).doubleValue() : 0); cell5.setCellStyle(styleC25); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(!"null".equalsIgnoreCase(String.valueOf(data.getVatamount())) ? (data.getVatamount()).doubleValue() : 0); cell6.setCellStyle(styleC25); HSSFCell cell7 = row.createCell(8); cell7.setCellValue( !"null".equalsIgnoreCase(String.valueOf(data.getAmount())) ? (data.getAmount()).doubleValue() : 0); cell7.setCellStyle(styleC25); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getFlagtype()); cell8.setCellStyle(styleC23); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getInvoicetype()); cell9.setCellStyle(styleC24); HSSFCell cell10 = row.createCell(11); cell10.setCellValue(data.getTaxno1()); cell10.setCellStyle(styleC24); HSSFCell cell11 = row.createCell(12); cell11.setCellValue(data.getBranch()); cell11.setCellStyle(styleC24); HSSFCell cell12 = row.createCell(13); cell12.setCellValue(data.getBranchno()); cell12.setCellStyle(styleC23); } for (int j = 0; j < 15; j++) { sheet.autoSizeColumn(j); } sheet.setColumnWidth(5, 256 * 40);//27 exportFileExcel("TaxInvoiceReport", wb); }
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportAgentReport(List repAgent) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Agent"); 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); HSSFRow row1 = sheet.createRow(0);/* w ww . ja v a 2 s .co m*/ HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("Agent Report"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row2 = sheet.createRow(2); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("ID"); cell20.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("SYSTEM DATE"); cell21.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("SYSTEM STAFF"); cell22.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("CODE"); cell23.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("NAME"); cell24.setCellStyle(styleC3Center); sheet.autoSizeColumn(4); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("ADDRESS"); cell25.setCellStyle(styleC3Center); sheet.autoSizeColumn(5); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("TEL"); cell26.setCellStyle(styleC3Center); sheet.autoSizeColumn(6); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("FAX"); cell27.setCellStyle(styleC3Center); sheet.autoSizeColumn(7); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("DESCRIPTION"); cell28.setCellStyle(styleC3Center); sheet.autoSizeColumn(8); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("NAME T"); cell29.setCellStyle(styleC3Center); sheet.autoSizeColumn(9); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("DESCRIPTION T"); cell30.setCellStyle(styleC3Center); sheet.autoSizeColumn(10); HSSFCell cell31 = row2.createCell(11); cell31.setCellValue("ADDRESS T"); cell31.setCellStyle(styleC3Center); sheet.autoSizeColumn(11); HSSFCell cell32 = row2.createCell(12); cell32.setCellValue("EMAIL"); cell32.setCellStyle(styleC3Center); sheet.autoSizeColumn(12); HSSFCell cell33 = row2.createCell(13); cell33.setCellValue("WEB"); cell33.setCellStyle(styleC3Center); sheet.autoSizeColumn(13); HSSFCell cell34 = row2.createCell(14); cell34.setCellValue("REMARKS"); cell34.setCellStyle(styleC3Center); sheet.autoSizeColumn(14); HSSFCell cell35 = row2.createCell(15); cell35.setCellValue("WARNING"); cell35.setCellStyle(styleC3Center); sheet.autoSizeColumn(15); HSSFCell cell36 = row2.createCell(16); cell36.setCellValue("REF ID"); cell36.setCellStyle(styleC3Center); sheet.autoSizeColumn(16); HSSFCell cell37 = row2.createCell(17); cell37.setCellValue("BRANCH"); cell37.setCellStyle(styleC3Center); sheet.autoSizeColumn(17); HSSFCell cell38 = row2.createCell(18); cell38.setCellValue("BRANCH NO"); cell38.setCellStyle(styleC3Center); sheet.autoSizeColumn(18); HSSFCell cell39 = row2.createCell(19); cell39.setCellValue("TAX NO"); cell39.setCellStyle(styleC3Center); sheet.autoSizeColumn(19); int count = 3; 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); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); 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")); for (int i = 0; i < repAgent.size(); i++) { ReportAgent data = (ReportAgent) repAgent.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getId()); cell0.setCellStyle(styleC23); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(String.valueOf(data.getSystemdate())); cell1.setCellStyle(styleC23); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getSystemstaff()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getCode()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getName()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getAddress().trim()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getTel()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getFax()); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getDescription().trim()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getNameT()); cell8.setCellStyle(styleC24); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getDescriptionT().trim()); cell9.setCellStyle(styleC24); HSSFCell cell10 = row.createCell(11); cell10.setCellValue(data.getAddressT().trim()); cell10.setCellStyle(styleC24); HSSFCell cell11 = row.createCell(12); cell11.setCellValue(data.getEmail()); cell11.setCellStyle(styleC24); HSSFCell cell12 = row.createCell(13); cell12.setCellValue(data.getWeb()); cell12.setCellStyle(styleC24); HSSFCell cell14 = row.createCell(14); cell14.setCellValue(data.getRemarks()); cell14.setCellStyle(styleC24); HSSFCell cell15 = row.createCell(15); cell15.setCellValue(data.getWarning()); cell15.setCellStyle(styleC24); HSSFCell cell16 = row.createCell(16); cell16.setCellValue(data.getRefid()); cell16.setCellStyle(styleC24); HSSFCell cell17 = row.createCell(17); cell17.setCellValue(data.getBranch()); cell17.setCellStyle(styleC24); HSSFCell cell18 = row.createCell(18); cell18.setCellValue(data.getBranchno()); cell18.setCellStyle(styleC24); HSSFCell cell19 = row.createCell(19); cell19.setCellValue(data.getTaxno()); cell19.setCellStyle(styleC23); } for (int j = 0; j < 20; j++) { sheet.autoSizeColumn(j); } for (int k = 4; k < 21; k++) { if (k != 6 && k != 7 && k != 12 && k != 13 && k < 16) { sheet.setColumnWidth(k, 256 * 35);//27 } else { sheet.setColumnWidth(k, 256 * 20);//27 } } exportFileExcel("AgentReport", wb); }