List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java
License:Open Source License
/** * Method used to export the bank wise detailed report in to Excel for both Inward and Outward. *//from w w w . j a v a 2 s.co m * @param out ServletOutputStream * */ public void generateNEFTInwBankDetailedReportToExcel(ServletOutputStream out) throws Exception { try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(reportTitle); HSSFCellStyle titleStyle = wb.createCellStyle(); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle fontStyle = wb.createCellStyle(); HSSFFont fontSize = wb.createFont(); fontSize.setFontHeightInPoints((short) 10); fontSize.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontStyle.setFont(fontSize); fontStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); sheet.setColumnWidth((short) 0, (short) 2000); sheet.setColumnWidth((short) 1, (short) 4000); sheet.setColumnWidth((short) 2, (short) 6000); sheet.setColumnWidth((short) 3, (short) 5000); sheet.setColumnWidth((short) 4, (short) 3000); sheet.setColumnWidth((short) 5, (short) 6000); sheet.setColumnWidth((short) 6, (short) 3000); sheet.setColumnWidth((short) 7, (short) 6000); sheet.setColumnWidth((short) 8, (short) 2500); sheet.setColumnWidth((short) 9, (short) 6000); sheet.setColumnWidth((short) 10, (short) 3000); sheet.setColumnWidth((short) 11, (short) 6000); sheet.setColumnWidth((short) 12, (short) 4500); HSSFCellStyle contentStyle = wb.createCellStyle(); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle stringStyle = wb.createCellStyle(); stringStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); stringStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); stringStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); stringStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); stringStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFFont headingFont = wb.createFont(); headingFont.setFontHeightInPoints((short) 9); headingFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headingStyle = wb.createCellStyle(); headingStyle.setFont(headingFont); headingStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headingStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); headingStyle.setFillPattern(HSSFCellStyle.BIG_SPOTS); headingStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); headingStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headingStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headingStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headingStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headingStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle titleRightStyle = wb.createCellStyle(); titleRightStyle.setFont(headingFont); titleRightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); titleRightStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleRightStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleRightStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleRightStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle titleLeftStyle = wb.createCellStyle(); titleLeftStyle.setFont(headingFont); titleLeftStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); titleLeftStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleLeftStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleLeftStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleLeftStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); int rowCount = 2; String dateForm = currentReportPrintTime.substring(0, 11); String time = currentReportPrintTime.substring(11); HSSFRow reportTimeRow = sheet.createRow((short) rowCount); HSSFCell reportTimeRowCell = reportTimeRow.createCell((short) 0); reportTimeRowCell.setCellStyle(titleRightStyle); reportTimeRowCell.setCellValue( "Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time); sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 12)); rowCount++; HSSFRow stTitle = sheet.createRow((short) rowCount); HSSFCell title = stTitle.createCell((short) 0); title.setCellStyle(fontStyle); title.setCellValue(reportTitle); sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 12)); rowCount++; HSSFRow stTitleTwo = sheet.createRow((short) rowCount); HSSFCell stTitleCellOne = stTitleTwo.createCell((short) 0); stTitleCellOne.setCellStyle(fontStyle); stTitleCellOne.setCellValue("from " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to " + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate())); sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 12)); rowCount = rowCount + 2; if (getReportMap().size() != 0) { Set set = getReportMap().entrySet(); for (Iterator z = set.iterator(); z.hasNext();) { Map.Entry<String, List<TransactionInfo>> entry = (Map.Entry<String, List<TransactionInfo>>) z .next(); List exportXLS = new ArrayList(1); exportXLS.addAll(entry.getValue()); String bankName = entry.getKey().substring(0, 4); String sumTxnAmount = entry.getKey().substring(5); HSSFRow titleRowOne = sheet.createRow((short) rowCount); HSSFCell bankNameCell = titleRowOne.createCell((short) 0); bankNameCell.setCellStyle(titleRightStyle); if (getTranType().equals("inward")) { bankNameCell.setCellValue("Sender Bank : "); } else { bankNameCell.setCellValue(" Receiver Bank : "); } sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 6)); HSSFCell bankNameValCell = titleRowOne.createCell((short) 7); bankNameValCell.setCellStyle(titleLeftStyle); bankNameValCell.setCellValue(bankName); sheet.addMergedRegion(new Region(rowCount, (short) 7, rowCount, (short) 12)); rowCount++; HSSFRow titleRowTwo = sheet.createRow((short) rowCount); HSSFCell txnCountCell = titleRowTwo.createCell((short) 0); txnCountCell.setCellStyle(titleRightStyle); txnCountCell.setCellValue("Txn Count : "); sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 6)); HSSFCell txnCountValCell = titleRowTwo.createCell((short) 7); txnCountValCell.setCellStyle(titleLeftStyle); txnCountValCell.setCellValue(exportXLS.size()); sheet.addMergedRegion(new Region(rowCount, (short) 7, rowCount, (short) 12)); rowCount++; HSSFRow titleRowThree = sheet.createRow((short) rowCount); HSSFCell sumTxnAmountCell = titleRowThree.createCell((short) 0); sumTxnAmountCell.setCellStyle(titleRightStyle); sumTxnAmountCell.setCellValue("Sum of Txn Amount : "); sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 6)); HSSFCell sumTxnAmountValCell = titleRowThree.createCell((short) 7); sumTxnAmountValCell.setCellStyle(titleLeftStyle); sumTxnAmountValCell.setCellValue(sumTxnAmount); sheet.addMergedRegion(new Region(rowCount, (short) 7, rowCount, (short) 12)); rowCount++; HSSFRow titleRow = sheet.createRow((short) rowCount); HSSFCell beneficiaryTitle = titleRow.createCell((short) 4); beneficiaryTitle.setCellStyle(headingStyle); beneficiaryTitle.setCellValue("Beneficiary Details"); sheet.addMergedRegion(new Region(rowCount, (short) 4, rowCount, (short) 7)); HSSFCell senderTitle = titleRow.createCell((short) 8); senderTitle.setCellStyle(headingStyle); senderTitle.setCellValue("Sender's Details"); sheet.addMergedRegion(new Region(rowCount, (short) 8, rowCount, (short) 11)); rowCount++; HSSFRow rowhead = sheet.createRow((short) rowCount); HSSFCell snoHead = rowhead.createCell((short) 0); snoHead.setCellStyle(headingStyle); snoHead.setCellValue("S .NO"); HSSFCell valueDateHead = rowhead.createCell((short) 1); valueDateHead.setCellStyle(headingStyle); valueDateHead.setCellValue("Value Date"); HSSFCell tranRefHead = rowhead.createCell((short) 2); tranRefHead.setCellStyle(headingStyle); tranRefHead.setCellValue("Transaction Ref. No"); HSSFCell amountHead = rowhead.createCell((short) 3); amountHead.setCellStyle(headingStyle); amountHead.setCellValue("Amount(Rs)"); HSSFCell benIfscHead = rowhead.createCell((short) 4); benIfscHead.setCellStyle(headingStyle); benIfscHead.setCellValue("IFSC"); HSSFCell benACNameHead = rowhead.createCell((short) 5); benACNameHead.setCellStyle(headingStyle); benACNameHead.setCellValue("A/c Name"); HSSFCell benACTypeHead = rowhead.createCell((short) 6); benACTypeHead.setCellStyle(headingStyle); benACTypeHead.setCellValue("A/c Type"); HSSFCell benACNoHead = rowhead.createCell((short) 7); benACNoHead.setCellStyle(headingStyle); benACNoHead.setCellValue("A/c No"); HSSFCell senIfscHead = rowhead.createCell((short) 8); senIfscHead.setCellStyle(headingStyle); senIfscHead.setCellValue("IFSC"); HSSFCell senACNameHead = rowhead.createCell((short) 9); senACNameHead.setCellStyle(headingStyle); senACNameHead.setCellValue("A/c Name"); HSSFCell senACTypeHead = rowhead.createCell((short) 10); senACTypeHead.setCellStyle(headingStyle); senACTypeHead.setCellValue("A/c Type"); HSSFCell senACNoHead = rowhead.createCell((short) 11); senACNoHead.setCellStyle(headingStyle); senACNoHead.setCellValue("A/c No"); HSSFCell tranStatusHead = rowhead.createCell((short) 12); tranStatusHead.setCellStyle(headingStyle); tranStatusHead.setCellValue("Transaction Status"); HSSFRow row = null; for (int i = exportXLS.size(), rowIndex = 0; rowIndex < i; rowIndex++) { TransactionInfo ti = (TransactionInfo) exportXLS.get(rowIndex); rowCount++; row = sheet.createRow(rowCount); HSSFCell snoCell = row.createCell((short) 0); snoCell.setCellStyle(contentStyle); snoCell.setCellValue(rowIndex + 1); HSSFCell valueDateCell = row.createCell((short) 1); valueDateCell.setCellStyle(stringStyle); if (ti.getValueDate() != null) { valueDateCell.setCellValue(InstaReportUtil.getDateInSpecificFormat(dateFormat, ti.getValueDate().toString())); } else { valueDateCell.setCellValue(""); } HSSFCell tranRefCell = row.createCell((short) 2); tranRefCell.setCellStyle(stringStyle); if (ti.getUtrNo() != null) { tranRefCell.setCellValue(ti.getUtrNo()); } else { tranRefCell.setCellValue(""); } HSSFCell amountCell = row.createCell((short) 3); amountCell.setCellStyle(stringStyle); if (ti.getAmount() != null) { amountCell.setCellValue(ti.getAmount().toString()); } else { amountCell.setCellValue(""); } HSSFCell benIfscCell = row.createCell((short) 4); benIfscCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccIfsc() != null) { benIfscCell.setCellValue(ti.getBeneficiaryInfo().getAccIfsc()); } else { benIfscCell.setCellValue(""); } HSSFCell benACNameCell = row.createCell((short) 5); benACNameCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccName() != null) { benACNameCell.setCellValue(ti.getBeneficiaryInfo().getAccName()); } else { benACNameCell.setCellValue(""); } HSSFCell benACTypeCell = row.createCell((short) 6); benACTypeCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccType() != null) { benACTypeCell.setCellValue(ti.getBeneficiaryInfo().getAccType()); } else { benACTypeCell.setCellValue(""); } HSSFCell benACNoCell = row.createCell((short) 7); benACNoCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccNo() != null) { benACNoCell.setCellValue(ti.getBeneficiaryInfo().getAccNo()); } else { benACNoCell.setCellValue(""); } HSSFCell senIfscCell = row.createCell((short) 8); senIfscCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccIfsc() != null) { senIfscCell.setCellValue(ti.getSenderInfo().getAccIfsc()); } else { senIfscCell.setCellValue(""); } HSSFCell senACNameCell = row.createCell((short) 9); senACNameCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccName() != null) { senACNameCell.setCellValue(ti.getSenderInfo().getAccName()); } else { senACNameCell.setCellValue(""); } HSSFCell senACTypeCell = row.createCell((short) 10); senACTypeCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccType() != null) { senACTypeCell.setCellValue(ti.getSenderInfo().getAccType()); } else { senACTypeCell.setCellValue(""); } HSSFCell senACNoCell = row.createCell((short) 11); senACNoCell.setCellStyle(stringStyle); if (ti.getBeneficiaryInfo().getAccNo() != null) { senACNoCell.setCellValue(ti.getSenderInfo().getAccNo()); } else { senACNoCell.setCellValue(""); } HSSFCell tranStatusCell = row.createCell((short) 12); tranStatusCell.setCellStyle(stringStyle); if (ti != null) { tranStatusCell.setCellValue(ti.getStatusShortDesc()); } else { tranStatusCell.setCellValue(""); } } rowCount = rowCount + 3; } } wb.write(out); out.flush(); out.close(); } catch (Exception e) { logger.error("Exception while creating Excel sheet file" + e.getMessage()); throw new Exception("Exception while creating Excel sheet file" + e); } }
From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsService.java
License:Open Source License
private int fillColumnWithStaffForWorkOnLine(final HSSFSheet sheet, int rowNum, final Entity assignmentToShiftReport, final List<DateTime> days, final List<Entity> productionLines, final Entity dictionaryItem) { if ((assignmentToShiftReport != null) && (days != null) && (productionLines != null)) { for (Entity productionLine : productionLines) { int rowNumFromLastSection = rowNum; int numberOfColumnsForWorkers = getNumberOfRowsForWorkers(assignmentToShiftReport, days, productionLine, dictionaryItem); for (int i = 0; i < numberOfColumnsForWorkers; i++) { HSSFRow row = sheet.createRow(rowNum); rowNum++;/*www .j a v a2s . c o m*/ } String productionLineValue = null; if (productionLine.getStringField(PLACE) == null) { productionLineValue = productionLine.getStringField(NUMBER); } else { productionLineValue = productionLine.getStringField(NUMBER) + "-" + productionLine.getStringField(ProductionLineFields.PLACE); } HSSFRow firstRowInSection = null; if (sheet.getRow(rowNumFromLastSection) == null) { firstRowInSection = sheet.createRow(rowNumFromLastSection); rowNum++; } else { firstRowInSection = sheet.getRow(rowNumFromLastSection); } HSSFCell cell = firstRowInSection.createCell(0); cell.setCellValue(productionLineValue); sheet.addMergedRegion(new CellRangeAddress(rowNumFromLastSection, rowNum - 1, 0, 0)); int columnNumber = 1; int maxLength = 0; for (DateTime day : days) { Entity assignmentToShift = assignmentToShiftXlsHelper .getAssignmentToShift(assignmentToShiftReport.getBelongsToField(SHIFT), day.toDate()); if (assignmentToShift == null) { columnNumber += 3; continue; } List<Entity> staffs = assignmentToShiftXlsHelper.getStaffsList(assignmentToShift, dictionaryItem.getStringField(NAME), productionLine); if (staffs.isEmpty()) { columnNumber += 3; continue; } String staffsValue = assignmentToShiftXlsHelper.getListOfWorkers(staffs); List<String> workers = assignmentToShiftXlsHelper.getListOfWorker(staffs); int rowIndex = rowNumFromLastSection; for (String worker : workers) { sheet.getRow(rowIndex).createCell(columnNumber).setCellValue(worker); rowIndex++; } if (workers.isEmpty()) { sheet.getRow(rowIndex).createCell(columnNumber).setCellValue(" "); } if (maxLength < staffsValue.length()) { maxLength = staffsValue.length(); } // row.setHeightInPoints(assignmentToShiftXlsStyleHelper.getHeightForRow(maxLength, 22, 14)); columnNumber += 3; } for (int i = rowNumFromLastSection; i < rowNum; i++) { assignmentToShiftXlsStyleHelper.addMarginsAndStylesForSeries(sheet, i, assignmentToShiftXlsHelper.getNumberOfDaysBetweenGivenDates(assignmentToShiftReport)); } } } return rowNum; }
From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsService.java
License:Open Source License
private int fillColumnWithStaffForOtherTypes(final HSSFSheet sheet, int rowNum, final Entity assignmentToShiftReport, final List<DateTime> days, final Entity occupationType) { if ((assignmentToShiftReport != null) && (days != null) && (occupationType != null)) { int rowNumFromLastSection = rowNum; int numberOfColumnsForWorkers = getNumberOfRowsForWorkersForOtherTypes(assignmentToShiftReport, days, occupationType);//from w ww . j a va 2 s .c o m for (int i = 0; i < numberOfColumnsForWorkers; i++) { HSSFRow row = sheet.createRow(rowNum); rowNum++; } String occupationTypeValue = occupationType.getStringField(NAME); HSSFRow firstRowInSection = null; if (sheet.getRow(rowNumFromLastSection) == null) { firstRowInSection = sheet.createRow(rowNumFromLastSection); rowNum++; } else { firstRowInSection = sheet.getRow(rowNumFromLastSection); } HSSFCell cell = firstRowInSection.createCell(0); cell.setCellValue(occupationTypeValue); sheet.addMergedRegion(new CellRangeAddress(rowNumFromLastSection, rowNum - 1, 0, 0)); int columnNumber = 1; int maxLength = 0; for (DateTime day : days) { Entity assignmentToShift = assignmentToShiftXlsHelper .getAssignmentToShift(assignmentToShiftReport.getBelongsToField(SHIFT), day.toDate()); if (assignmentToShift == null) { columnNumber += 3; continue; } List<Entity> staffs = assignmentToShiftXlsHelper.getStaffsList(assignmentToShift, occupationType.getStringField(NAME), null); List<String> workers = Lists.newArrayList(); if (OccupationType.OTHER_CASE.getStringValue() .equals(occupationType.getStringField(TECHNICAL_CODE))) { workers = assignmentToShiftXlsHelper.getListOfWorkerWithOtherCases(staffs); } else { workers = assignmentToShiftXlsHelper.getListOfWorker(staffs); } int rowIndex = rowNumFromLastSection; for (String worker : workers) { sheet.getRow(rowIndex).createCell(columnNumber).setCellValue(worker); rowIndex++; } if (workers.isEmpty()) { sheet.getRow(rowIndex).createCell(columnNumber).setCellValue(" "); } columnNumber += 3; } for (int i = rowNumFromLastSection; i < rowNum; i++) { assignmentToShiftXlsStyleHelper.addMarginsAndStylesForSeries(sheet, i, assignmentToShiftXlsHelper.getNumberOfDaysBetweenGivenDates(assignmentToShiftReport)); } } return rowNum; }
From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsStyleHelper.java
License:Open Source License
public void addMarginsAndStylesForAuthor(final HSSFSheet sheet, final int rowNumber, final int numberOfDays) { int firstColumnNumber = 0; int lastColumnNumber; int margin = 3; if (numberOfDays < 3) { lastColumnNumber = 10;// w w w .ja va 2 s . co m } else { lastColumnNumber = (numberOfDays + 1) * margin; } for (int columnNumber = firstColumnNumber; columnNumber <= lastColumnNumber; columnNumber++) { if (sheet.getRow(rowNumber).getCell(columnNumber) == null) { sheet.getRow(rowNumber).createCell(columnNumber); } if (columnNumber == firstColumnNumber) { setGreyDataStyleBorderLeftAlignLeftBold(sheet, sheet.getRow(rowNumber).getCell(columnNumber)); } else if (columnNumber == lastColumnNumber) { setGreyDataStyleBorderRightAlignLeftBold(sheet, sheet.getRow(rowNumber).getCell(columnNumber)); } else { setGreyDataStyleAlignLeftBold(sheet, sheet.getRow(rowNumber).getCell(columnNumber)); } } sheet.addMergedRegion( new CellRangeAddress(rowNumber, rowNumber, firstColumnNumber, firstColumnNumber + margin - 1)); sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, firstColumnNumber + margin, firstColumnNumber + (margin * 2) - 1)); sheet.addMergedRegion( new CellRangeAddress(rowNumber, rowNumber, firstColumnNumber + (margin * 2), lastColumnNumber)); }
From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsStyleHelper.java
License:Open Source License
public void addMarginsAndStylesForAssignmentToShift(final HSSFSheet sheet, final int rowNumber, final int numberOfDays) { int margin = 3; int firstColumn = 0; int lastColumn = (numberOfDays + 1) * margin; for (int columnNumber = firstColumn; columnNumber <= lastColumn; columnNumber++) { if (sheet.getRow(rowNumber).getCell(columnNumber) == null) { sheet.getRow(rowNumber).createCell(columnNumber); }/* ww w.ja v a 2 s.c o m*/ setWhiteDataStyleBorderBoxAlignCenterBold(sheet, sheet.getRow(rowNumber).getCell(columnNumber)); } for (int columnNumber = 1; columnNumber <= lastColumn; columnNumber += margin) { sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, columnNumber, columnNumber + 2)); } }
From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsStyleHelper.java
License:Open Source License
public void addMarginsAndStylesForSeries(final HSSFSheet sheet, final int rowNumber, final int numberOfDays) { int margin = 3; int firstColumn = 0; int lastColumn = (numberOfDays + 1) * margin; for (int columnNumber = firstColumn; columnNumber <= lastColumn; columnNumber++) { if (sheet.getRow(rowNumber).getCell(columnNumber) == null) { sheet.getRow(rowNumber).createCell(columnNumber); }/*from w w w . ja v a2 s . c om*/ if (columnNumber == firstColumn) { setWhiteDataStyleBorderBoxAlignCenterBold(sheet, sheet.getRow(rowNumber).getCell(columnNumber)); } else { setWhiteDataStyleBorderBoxAlignLeft(sheet, sheet.getRow(rowNumber).getCell(columnNumber)); } } for (int columnNumber = 1; columnNumber <= lastColumn; columnNumber += margin) { sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, columnNumber, columnNumber + 2)); } }
From source file:com.selfsoft.business.service.impl.TbBusinessBalanceServiceImpl.java
public void printTbBusinessBalanceTemplate(OutputStream os, String tpl, Long id, String companyName) { // ??/*from w ww . j a v a 2 s . c o m*/ TbBusinessBalance tbBusinessBalance = this.findById(id); // ? TbFixEntrust tbFixEntrust = tbFixEntrustService.findById(tbBusinessBalance.getTbFixEntrust().getId()); // ? TbCustomer tbCustomer = tbCustomerService.findById(tbFixEntrust.getTbCustomer().getId()); // ? TbCarInfo tbCarInfo = tbCarInfoService.findById(tbFixEntrust.getTbCarInfo().getId()); // ?? TmCompany tmCompany = tmCompanyService.acquireUniqueTmCompany(); // ? List<TbFixEntrustContent> tbFixEntrustContentList = tbFixEntrustContentService .findTbFixEnTrustContentListByTbFixEntrustId(tbFixEntrust.getId()); List<TbFixEntrustContent> tbFixEntrustContentListPage = new ArrayList<TbFixEntrustContent>(); List<TbFixEntrustContent> tbFixEntrustContentListTemp = new ArrayList<TbFixEntrustContent>(); List<TbFixEntrustContent> tbFixEntrustContentListAdd = new ArrayList<TbFixEntrustContent>(); if (null != tbFixEntrustContentList && tbFixEntrustContentList.size() > 0) { for (int i = 0; i < tbFixEntrustContentList.size(); i++) { boolean flag = false; if (tbFixEntrustContentListTemp.size() == 0) { tbFixEntrustContentListTemp.add(tbFixEntrustContentList.get(i)); } else { if (tbFixEntrustContentListTemp.size() > 1) { for (TbFixEntrustContent _tbFixEntrustContent : tbFixEntrustContentListTemp) { if (_tbFixEntrustContent.getStationName() .equals(tbFixEntrustContentList.get(i).getStationName()) && _tbFixEntrustContent.getTbWorkingInfo().getId() .equals(tbFixEntrustContentList.get(i).getTbWorkingInfo().getId()) && _tbFixEntrustContent.getFreesymbol() .equals(tbFixEntrustContentList.get(i).getFreesymbol()) ) { flag = true; break; } } } } if (flag) { continue; } else { tbFixEntrustContentListTemp.add(tbFixEntrustContentList.get(i)); } TbFixEntrustContent temp = tbFixEntrustContentList.get(i); BigDecimal d = new BigDecimal(temp.getFixHourAll()); BigDecimal d2 = new BigDecimal(temp.getFixHour()); for (int j = i + 1; j < tbFixEntrustContentList.size(); j++) { if (temp.getStationName().equals(tbFixEntrustContentList.get(j).getStationName()) && temp.getTbWorkingInfo().getId() .equals(tbFixEntrustContentList.get(j).getTbWorkingInfo().getId()) && temp.getFreesymbol().equals(tbFixEntrustContentList.get(j).getFreesymbol())) { d = d.add(new BigDecimal(tbFixEntrustContentList.get(j).getFixHourAll())); d2 = d2.add(new BigDecimal(tbFixEntrustContentList.get(j).getFixHour())); } } temp.setFixHourAll(d.doubleValue()); temp.setFixHour(d2.doubleValue()); if (!temp.getFreesymbol().equals(1d) || !temp.getFixHourAll().equals(0d)) { tbFixEntrustContentListAdd.add(temp); } } } // ?? List<TbMaintianVo> maintianvos = tbMaintainPartContentService .getTbMaintianDetailVosByEntrustId(tbFixEntrust.getId(), Constants.BALANCE_ALL); // ? List<TmStockOutDetVo> tmStockOutDetVos = tmStockOutService .getSellDetailByEntrustCode(tbFixEntrust.getEntrustCode(), Constants.BALANCE_ALL); // List<TbBusinessBalanceItem> tbBusinessBalanceItemList = tbBusinessBalanceItemService .findGroupTbBusinessBalanceItemListByTbBusinessBalanceId(tbBusinessBalance.getId()); // ??? if (null == maintianvos) { maintianvos = new ArrayList<TbMaintianVo>(); } if (null != tmStockOutDetVos && tmStockOutDetVos.size() > 0) { for (TmStockOutDetVo tmStockOutDetVo : tmStockOutDetVos) { TbMaintianVo tbMaintianVo = new TbMaintianVo(); tbMaintianVo.setPartId(tmStockOutDetVo.getPartinfoId()); tbMaintianVo.setHouseName(tmStockOutDetVo.getHouseName()); tbMaintianVo.setPartCode(tmStockOutDetVo.getPartCode()); tbMaintianVo.setPartName(tmStockOutDetVo.getPartName()); tbMaintianVo.setUnitName(tmStockOutDetVo.getUnitName()); tbMaintianVo.setPrice(tmStockOutDetVo.getPrice()); tbMaintianVo.setPartQuantity(tmStockOutDetVo.getQuantity()); tbMaintianVo.setTotal(tmStockOutDetVo.getTotal()); tbMaintianVo.setIsFree(tmStockOutDetVo.getIsFree()); tbMaintianVo.setProjectType(tmStockOutDetVo.getProjectType()); tbMaintianVo.setZl(tmStockOutDetVo.getZl()); tbMaintianVo.setXmlx(tmStockOutDetVo.getXmlx()); maintianvos.add(tbMaintianVo); } } /** * add by ccr 2010-12-18 */ List<TbMaintianVo> maintianvosTemp = new ArrayList<TbMaintianVo>(); List<TbMaintianVo> maintianvosAdd = new ArrayList<TbMaintianVo>(); if (maintianvos.size() > 0) { for (int i = 0; i < maintianvos.size(); i++) { boolean flag = false; if (maintianvosTemp.size() == 0) { maintianvosTemp.add(maintianvos.get(i)); } else { if (maintianvosTemp.size() > 1) { int l = 0; for (TbMaintianVo _tbMaintianVo : maintianvosTemp) { if (_tbMaintianVo.getPartId().equals(maintianvos.get(i).getPartId()) && _tbMaintianVo.getIsFree().equals(maintianvos.get(i).getIsFree()) && _tbMaintianVo.getPrice().equals(maintianvos.get(i).getPrice())) { // maintianvosTemp.set(l, maintianvos.get(i)); flag = true; break; } l++; } } } if (flag) { continue; } else { maintianvosTemp.add(maintianvos.get(i)); } TbMaintianVo temp = maintianvos.get(i); BigDecimal d1 = new BigDecimal(temp.getPartQuantity()); BigDecimal d2 = new BigDecimal(temp.getTotal()); for (int j = i + 1; j < maintianvos.size(); j++) { if (temp.getPartId().equals(maintianvos.get(j).getPartId()) && temp.getIsFree().equals(maintianvos.get(j).getIsFree()) && temp.getPrice().equals(maintianvos.get(j).getPrice())) { temp.setPrice(maintianvos.get(j).getPrice()); d1 = d1.add(new BigDecimal(maintianvos.get(j).getPartQuantity())); d2 = d2.add(new BigDecimal(maintianvos.get(j).getTotal())); } } temp.setPartQuantity(d1.doubleValue()); temp.setTotal(d2.doubleValue()); if (!temp.getIsFree().equals(1L) || !temp.getPartQuantity().equals(0d)) { /* * temp.setPrice(new BigDecimal(temp.getTotal()).divide(new * BigDecimal(temp.getPartQuantity()),2, * BigDecimal.ROUND_HALF_UP).setScale(2, * BigDecimal.ROUND_HALF_UP).doubleValue()); */ maintianvosAdd.add(temp); } } } int fixSize = (tbFixEntrustContentListAdd == null ? 0 : tbFixEntrustContentListAdd.size()); int partSize = (maintianvosAdd == null ? 0 : maintianvosAdd.size()); try { HSSFWorkbook workbook = new HSSFWorkbook(this.getClass().getResourceAsStream(tpl)); HSSFSheet sheet = workbook.getSheetAt(0); HSSFCellStyle style = workbook.createCellStyle(); style.setWrapText(true); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 9); style.setFont(font); HSSFCellStyle styleRight = workbook.createCellStyle(); styleRight.setWrapText(true); styleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleRight.setFont(font); HSSFCellStyle styleBorderThinAll = workbook.createCellStyle(); styleBorderThinAll.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setWrapText(true); styleBorderThinAll.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleBorderThinAll.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThinAll.setFont(font); HSSFCellStyle styleBorderThickLeft = workbook.createCellStyle(); styleBorderThickLeft.setBorderLeft(HSSFCellStyle.BORDER_THICK); styleBorderThickLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThickLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickLeft.setWrapText(true); styleBorderThickLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleBorderThickLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickLeft.setFont(font); HSSFCellStyle styleBorderThickRight = workbook.createCellStyle(); styleBorderThickRight.setBorderRight(HSSFCellStyle.BORDER_THICK); styleBorderThickRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThickRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickRight.setWrapText(true); styleBorderThickRight.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleBorderThickRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickRight.setFont(font); HSSFCellStyle styleBorderThinAllCenter = workbook.createCellStyle(); styleBorderThinAllCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setWrapText(true); styleBorderThinAllCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleBorderThinAllCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThinAllCenter.setFont(font); HSSFCellStyle styleBorderThickLeftCenter = workbook.createCellStyle(); styleBorderThickLeftCenter.setBorderLeft(HSSFCellStyle.BORDER_THICK); styleBorderThickLeftCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThickLeftCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickLeftCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickLeftCenter.setWrapText(true); styleBorderThickLeftCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleBorderThickLeftCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickLeftCenter.setFont(font); HSSFCellStyle styleBorderThickRightCenter = workbook.createCellStyle(); styleBorderThickRightCenter.setBorderRight(HSSFCellStyle.BORDER_THICK); styleBorderThickRightCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThickRightCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickRightCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickRightCenter.setWrapText(true); styleBorderThickRightCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleBorderThickRightCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickRightCenter.setFont(font); HSSFCellStyle styleCenter = workbook.createCellStyle(); styleCenter.setWrapText(true); styleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCenter.setFont(font); HSSFRow row = null; HSSFCell cell = null; row = sheet.getRow(2); cell = row.getCell(4); // cell.setCellStyle(style); if ("dfbz".equals(companyName)) { cell.setCellValue(Constants.getCompanyMap().get("dfbzCode")); } else if ("xtl".equals(companyName)) { cell.setCellValue(Constants.getCompanyMap().get("xtlCode")); } row = sheet.getRow(2); cell = row.getCell(17); if ("xtl".equals(companyName)) { cell.setCellValue(/* tmCompany.getCompanyName() */"?"); } else { cell.setCellValue(tmCompany.getCompanyName()); } row = sheet.getRow(2); cell = row.getCell(33); cell.setCellValue(tmCompany.getCompanyPhone() == null ? "" : tmCompany.getCompanyPhone()); row = sheet.getRow(4); cell = row.getCell(4); if ("xtl".equals(companyName)) { cell.setCellValue(""); } else { cell.setCellValue(tmCompany.getCompanyZipCode() == null ? "" : tmCompany.getCompanyZipCode()); } row = sheet.getRow(4); cell = row.getCell(17); cell.setCellValue(tmCompany.getCompanyAddress() == null ? "" : tmCompany.getCompanyAddress()); row = sheet.getRow(4); cell = row.getCell(33); cell.setCellValue(tmCompany.getCompanyFax() == null ? "" : tmCompany.getCompanyFax()); row = sheet.getRow(10); cell = row.getCell(2); // cell.setCellStyle(style); String entrustCode = tbFixEntrust.getEntrustCode(); String[] es = entrustCode.split("-"); String newCode = "RO" + es[0].substring(2, 6) + es[1]; cell.setCellValue(newCode); row = sheet.getRow(10); cell = row.getCell(24); // cell.setCellStyle(style); cell.setCellValue((tbFixEntrust.getTmUser().getUserRealName() == null || "".equals(tbFixEntrust.getTmUser().getUserRealName())) ? tbFixEntrust.getTmUser().getUserName() : tbFixEntrust.getTmUser().getUserRealName()); row = sheet.getRow(10); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue( CommonMethod.parseDateToString(tbBusinessBalance.getBananceDate(), "yyyy-MM-dd HH:mm")); row = sheet.getRow(8); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(CommonMethod.parseDateToString(tbFixEntrust.getFixDate(), "yyyy-MM-dd HH:mm")); row = sheet.getRow(12); cell = row.getCell(4); cell.setCellValue(tbCustomer.getCustomerName()); row = sheet.getRow(12); cell = row.getCell(24); cell.setCellValue(tbCarInfo.getLicenseCode()); row = sheet.getRow(12); cell = row.getCell(41); cell.setCellValue(tbCarInfo.getTmCarModelType().getModelName()); row = sheet.getRow(14); cell = row.getCell(4); cell.setCellValue(tbCustomer.getTelephone() == null ? "" : tbCustomer.getTelephone()); row = sheet.getRow(18); cell = row.getCell(12); cell.setCellValue(tbCustomer.getTelephone() == null ? "" : tbCustomer.getTelephone()); row = sheet.getRow(14); cell = row.getCell(24); cell.setCellValue(tbCarInfo.getChassisCode()); row = sheet.getRow(16); cell = row.getCell(4); cell.setCellValue(tbCustomer.getAddress() == null ? "" : tbCustomer.getAddress()); row = sheet.getRow(16); cell = row.getCell(24); cell.setCellValue(CommonMethod.parseDateToString(tbCarInfo.getPurchaseDate(), "yyyy-MM-dd")); row = sheet.getRow(16); cell = row.getCell(41); if ("xtl".equals(companyName)) { cell.setCellValue( tbFixEntrust.getEnterStationKilo() == null ? "" : new BigDecimal(tbFixEntrust.getEnterStationKilo()) .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP).toString() + " Km"); } else { cell.setCellValue( tbFixEntrust.getEnterStationKilo() == null ? "" : new BigDecimal(tbFixEntrust.getEnterStationKilo()) .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP).toString() + " Km"); } row = sheet.getRow(18); cell = row.getCell(4); cell.setCellValue(tbCustomer.getContractPerson() == null ? "" : tbCustomer.getContractPerson()); row = sheet.getRow(18); cell = row.getCell(24); cell.setCellValue(tbCarInfo.getEngineCode() == null ? "" : tbCarInfo.getEngineCode()); row = sheet.getRow(18); cell = row.getCell(41); cell.setCellValue(tbCarInfo.getColor() == null ? "" : tbCarInfo.getColor()); /* * if (null != tbBusinessBalanceItemList && * tbBusinessBalanceItemList.size() > 0) { * * for (TbBusinessBalanceItem tbBusinessBalanceItem : * tbBusinessBalanceItemList) { * * if ("XLCLF".equals(tbBusinessBalanceItem .getBalanceItemCode())) * { * * row = sheet.getRow(77); * * cell = row.getCell(24); * * cell.setCellValue(new BigDecimal(tbBusinessBalanceItem * .getBalanceItemTotal()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * continue; } * * if ("XLGSF".equals(tbBusinessBalanceItem .getBalanceItemCode())) * { * * row = sheet.getRow(75); * * cell = row.getCell(24); * * cell.setCellValue(new BigDecimal(tbBusinessBalanceItem * .getBalanceItemTotal()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * continue; } * * if ("ZJE" .equals(tbBusinessBalanceItem.getBalanceItemCode())) { * * row = sheet.getRow(81); * * cell = row.getCell(24); * * cell.setCellValue(new BigDecimal(tbBusinessBalanceItem * .getBalanceItemTotal()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * continue; } * * } * * row = sheet.getRow(79); * * cell = row.getCell(24); * * cell.setCellValue("0.00"); * * } */ BigDecimal d_f_w = new BigDecimal("0.00"); BigDecimal d_f_p = new BigDecimal("0.00"); BigDecimal d_f_i = new BigDecimal("0.00"); BigDecimal d_f_c = new BigDecimal("0.00"); if (fixSize > 0) { for (int i = 0; i < fixSize; i++) { TbFixEntrustContent t = tbFixEntrustContentListAdd.get(i); if ("W".equals(t.getZl())) { d_f_w = d_f_w.add(new BigDecimal(t.getFixHourAll())); } else if ("P".equals(t.getZl())) { d_f_p = d_f_p.add(new BigDecimal(t.getFixHourAll())); } else if ("I".equals(t.getZl())) { d_f_i = d_f_i.add(new BigDecimal(t.getFixHourAll())); } else if ("C".equals(t.getZl())) { d_f_c = d_f_c.add(new BigDecimal(t.getFixHourAll())); } } } BigDecimal d_p_w = new BigDecimal("0.00"); BigDecimal d_p_p = new BigDecimal("0.00"); BigDecimal d_p_i = new BigDecimal("0.00"); BigDecimal d_p_c = new BigDecimal("0.00"); if (partSize > 0) { for (int i = 0; i < partSize; i++) { TbMaintianVo t = maintianvosAdd.get(i); if ("W".equals(t.getZl())) { d_p_w = d_p_w.add(new BigDecimal(t.getTotal())); } else if ("P".equals(t.getZl())) { d_p_p = d_p_p.add(new BigDecimal(t.getTotal())); } else if ("I".equals(t.getZl())) { d_p_i = d_p_i.add(new BigDecimal(t.getTotal())); } else if ("C".equals(t.getZl())) { d_p_c = d_p_c.add(new BigDecimal(t.getTotal())); } } } row = sheet.getRow(75); cell = row.getCell(6); cell.setCellValue(d_f_w.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(77); cell = row.getCell(6); cell.setCellValue(d_p_w.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(79); cell = row.getCell(6); cell.setCellValue("0.00"); row = sheet.getRow(81); cell = row.getCell(6); cell.setCellValue( d_f_w.add(d_p_w).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(75); cell = row.getCell(12); cell.setCellValue(d_f_p.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(77); cell = row.getCell(12); cell.setCellValue(d_p_p.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(79); cell = row.getCell(12); cell.setCellValue("0.00"); row = sheet.getRow(81); cell = row.getCell(12); cell.setCellValue( d_f_p.add(d_p_p).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(75); cell = row.getCell(18); cell.setCellValue(d_f_i.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(77); cell = row.getCell(18); cell.setCellValue(d_p_i.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(79); cell = row.getCell(18); cell.setCellValue("0.00"); row = sheet.getRow(81); cell = row.getCell(18); cell.setCellValue( d_f_i.add(d_p_i).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(75); cell = row.getCell(24); cell.setCellValue(d_f_c.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(77); cell = row.getCell(24); cell.setCellValue(d_p_c.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(79); cell = row.getCell(24); cell.setCellValue("0.00"); row = sheet.getRow(81); cell = row.getCell(24); cell.setCellValue( d_f_c.add(d_p_c).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); Double wt = tbBusinessBalance.getWorkingHourTotalAll() == null ? 0d : tbBusinessBalance.getWorkingHourTotalAll(); Double wf = tbBusinessBalance.getWorkingHourFavourRate() == null ? 0d : tbBusinessBalance.getWorkingHourFavourRate(); Double pt = tbBusinessBalance.getFixPartTotalAll() == null ? 0d : tbBusinessBalance.getFixPartTotalAll(); Double pf = tbBusinessBalance.getFixPartFavourRate() == null ? 0d : tbBusinessBalance.getFixPartFavourRate(); Double st = tbBusinessBalance.getSolePartTotalAll() == null ? 0d : tbBusinessBalance.getSolePartTotalAll(); Double sf = tbBusinessBalance.getSolePartFavourRate() == null ? 0d : tbBusinessBalance.getSolePartFavourRate(); BigDecimal fixF = new BigDecimal(wt).divide(new BigDecimal(1 - wf), 2, BigDecimal.ROUND_HALF_UP) .multiply(new BigDecimal(wf)); BigDecimal partF = new BigDecimal(pt).divide(new BigDecimal(1 - pf), 2, BigDecimal.ROUND_HALF_UP) .multiply(new BigDecimal(pf)); BigDecimal soleF = new BigDecimal(st).divide(new BigDecimal(1 - sf), 2, BigDecimal.ROUND_HALF_UP) .multiply(new BigDecimal(sf)); row = sheet.getRow(83); cell = row.getCell(4); // cell.setCellStyle(styleCenter); cell.setCellValue(fixF.add(partF).add(soleF).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP) .toString()); row = sheet.getRow(83); cell = row.getCell(14); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbBusinessBalance.getPayedAmount()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(68); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(CommonMethod .parseDateToString(tbBusinessBalance.getTbFixEntrust().getRemindMaintainDate(), "yyyy-MM-dd")); row = sheet.getRow(70); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(tbBusinessBalance.getTbFixEntrust().getRemindMaintainKilo() == null ? "" : new BigDecimal(tbBusinessBalance.getTbFixEntrust().getRemindMaintainKilo()) .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP) + ""); row = sheet.getRow(77); cell = row.getCell(30); // cell.setCellStyle(styleCenter); cell.setCellValue(tbBusinessBalance.getTbFixEntrust().getRemark()); ByteArrayOutputStream byteArrayOutImgLion = new ByteArrayOutputStream(); String pic = ""; if ("dfbz".equals(companyName)) { pic = "/lion_jsd.png"; } else if ("xtl".equals(companyName)) { pic = "/xtl_jsd.png"; } BufferedImage bufferImgLion = ImageIO.read(this.getClass().getResourceAsStream(pic)); ImageIO.write(bufferImgLion, "png", byteArrayOutImgLion); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFClientAnchor anchorLion = new HSSFClientAnchor(0, 0, 1023, 200, (short) 41, 0, (short) 46, 7); patriarch.createPicture(anchorLion, workbook.addPicture(byteArrayOutImgLion.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)); /* * int maxSize = 19; * * int maxFixSize = (fixSize > 19 ? 19 : fixSize); * * int maxPartSize = maxSize - maxFixSize > partSize ? partSize : * maxSize - maxFixSize; * * int partStartRow = (fixSize == 0 ? 21 : 21 + maxFixSize * 2 + 4); * * int partStartRowClone = ((fixSize - maxFixSize) == 0 ? 21 : 21 + * (fixSize - maxFixSize) * 2 + 4); * * int minBlank = 9; * * if ((fixSize + partSize) > 17) { * * HSSFSheet sheetClone = workbook.cloneSheet(0); * * HSSFRow rowClone = sheetClone.getRow(87); * * HSSFCell cellClone = rowClone.getCell(34); * * cellClone.setCellValue("2 2 "); * * HSSFPatriarch patriarchClone = sheetClone * .createDrawingPatriarch(); * * HSSFClientAnchor anchorLionClone = new HSSFClientAnchor(0, 0, * 1023, 200, (short) 41, 0, (short) 47, 8); * * patriarchClone.createPicture(anchorLionClone, workbook * .addPicture(byteArrayOutImgLion.toByteArray(), * HSSFWorkbook.PICTURE_TYPE_PNG)); * * if ((fixSize - maxFixSize) > 0) { * * * sheetClone.addMergedRegion(new Region(21, (short) 0, 21, (short) * 49)); * * sheetClone.addMergedRegion(new Region(21, (short) 0, 21, (short) * 0)); * * * sheetClone.addMergedRegion(new CellRangeAddress(21, 21, 0, 49)); * * rowClone = sheetClone.getRow(21); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(style); * * cellClone.setCellValue(""); * * cellClone.setCellStyle(styleBorderThickLeft); * * rowClone = sheetClone.getRow(21); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeft); * * rowClone = sheetClone.getRow(24); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * rowClone = sheetClone.getRow(23); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * cellClone.setCellValue("??"); * * cellClone = rowClone.getCell(2); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(10); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(21); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(24); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(30); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(34); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(40); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(44); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * for (int i = 0; i < (fixSize - maxFixSize); i++) { * * TbFixEntrustContent tbFixEntrustContent = * tbFixEntrustContentListAdd .get(i + maxFixSize); * * rowClone = sheetClone.getRow(25 + i * 2); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(i + 1); * * cellClone = rowClone.getCell(2); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbFixEntrustContent .getStationCode()); * * cellClone = rowClone.getCell(10); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbFixEntrustContent .getStationName()); * * cellClone = rowClone.getCell(21); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbFixEntrustContent.getZl()); * * cellClone = rowClone.getCell(24); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbFixEntrustContent .getProjectType()); * * cellClone = rowClone.getCell(30); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal( * tbFixEntrustContent.getFixHour()).divide( new BigDecimal("1.00"), * 2, BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(34); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal( * tbFixEntrustContent.getWorkingHourPrice()) .divide(new * BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(40); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal(tbBusinessBalance * .getWorkingHourFavourRate()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(44); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal( * tbFixEntrustContent.getFixHourAll()).divide( new * BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } * * } * * if ((partSize - maxPartSize) > 0) { * * * sheetClone.addMergedRegion(new Region(partStartRowClone, (short) * 0, partStartRowClone, (short) 49)); * * sheetClone.addMergedRegion(new Region(partStartRowClone, (short) * 0, partStartRowClone, (short) 49)); * * * sheetClone.addMergedRegion(new CellRangeAddress( * partStartRowClone, partStartRowClone, 0, 49)); * * rowClone = sheetClone.getRow(partStartRowClone); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(style); * * cellClone.setCellStyle(styleBorderThickLeft); * * cellClone.setCellValue(""); * * rowClone = sheetClone.getRow(partStartRowClone + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeft); * * rowClone = sheetClone.getRow(partStartRowClone + 2 + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * rowClone = sheetClone.getRow(partStartRowClone + 2); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * cellClone.setCellStyle(styleBorderThickLeftCenter); * * cellClone.setCellValue("??"); * * cellClone = rowClone.getCell(2); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(10); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("??"); * * cellClone = rowClone.getCell(21); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(24); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(30); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(34); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(40); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(44); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * for (int i = 0; i < (partSize - maxPartSize); i++) { * * TbMaintianVo tbMaintianVo = maintianvosAdd.get(i + maxPartSize); * * rowClone = sheetClone.getRow(partStartRowClone + 4 + i 2); * * if(null == rowClone) break; * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(i + 1); * * cellClone = rowClone.getCell(2); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbMaintianVo.getPartCode()); * * cellClone = rowClone.getCell(10); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbMaintianVo.getPartName()); * * cellClone = rowClone.getCell(21); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbMaintianVo.getZl()); * * cellClone = rowClone.getCell(24); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbMaintianVo.getProjectType()); * * cellClone = rowClone.getCell(30); * * cellClone.setCellStyle(styleRight); * * cellClone.setCellValue(new BigDecimal(tbMaintianVo * .getPartQuantity()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(34); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal(tbMaintianVo * .getPrice()).divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(40); * * // .setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal(tbBusinessBalance * .getFixPartFavourRate()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(44); * * // cellClone.setCellStyle(styleCenter); * * cellClone .setCellValue(new BigDecimal(tbMaintianVo * .getPartQuantity()) .multiply( new BigDecimal(tbMaintianVo * .getPrice())) .divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP) .toString()); } * * } * * int attachStartRowClone = 21; * * if ((fixSize - maxFixSize + partSize - maxPartSize) <= 17) { * * if ((fixSize - maxFixSize) != 0 && (partSize - maxPartSize) == 0 * && (fixSize - maxFixSize) < 18) { * * attachStartRowClone = 21 + 2 + 2 + (fixSize - maxFixSize) * 2; * * } * * if ((partSize - maxPartSize) != 0 && (fixSize - maxFixSize) == 0 * && (partSize - maxPartSize) < 18) { * * attachStartRowClone = 21 + 2 + 2 + 2 + (partSize - maxPartSize) * * 2; } * * if ((fixSize - maxFixSize) != 0 && (partSize - maxPartSize) != 0 * && (fixSize - maxFixSize + partSize - maxPartSize < 18)) { * * attachStartRowClone = 21 + 2 + 2 + 2 + 2 + (fixSize - maxFixSize) * * 2 + (partSize - maxPartSize) * 2; * * } * * * sheetClone.addMergedRegion(new Region(attachStartRowClone, * (short) 0, attachStartRowClone, (short) 49)); * * sheetClone.addMergedRegion(new Region(attachStartRowClone, * (short) 0, attachStartRowClone, (short) 49)); * * * sheetClone.addMergedRegion(new CellRangeAddress( * attachStartRowClone, attachStartRowClone, 0, 49)); * * rowClone = sheetClone.getRow(attachStartRowClone); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(style); * * cellClone.setCellStyle(styleBorderThickLeft); * * cellClone.setCellValue(""); * * rowClone = sheetClone.getRow(attachStartRowClone + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeft); * * rowClone = sheetClone.getRow(attachStartRowClone + 2 + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * rowClone = sheetClone.getRow(attachStartRowClone + 2); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * cellClone.setCellValue("??"); * * if ("dfbz".equals(companyName)) { * * cellClone = rowClone.getCell(10); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("??"); * * cellClone = rowClone.getCell(21); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(24); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(30); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(34); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(40); * * cellClone.setCellStyle(styleCenter); * * } else if ("xtl".equals(companyName)) { * * * sheetClone.addMergedRegion(new Region(attachStartRowClone + 2, * (short) 2, attachStartRowClone + 2, (short) 39)); * * sheetClone.addMergedRegion(new Region(attachStartRowClone + 2, * (short) 2, attachStartRowClone + 2, (short) 39)); * * * sheetClone.addMergedRegion(new CellRangeAddress( * attachStartRowClone + 2, attachStartRowClone + 2, 2, 39)); * * cellClone = rowClone.getCell(2); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * } * * cellClone = rowClone.getCell(40); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(44); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * rowClone = sheetClone.getRow(attachStartRowClone + 4); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * cellClone.setCellValue("1"); * * rowClone = sheetClone.getRow(attachStartRowClone + 4 + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * * sheetClone.addMergedRegion(new Region(attachStartRowClone + 6, * (short) 0, 66, (short) 49)); * * sheetClone.addMergedRegion(new Region(attachStartRowClone + 6, * (short) 0, 66, (short) 49)); * * * sheetClone.addMergedRegion(new CellRangeAddress( * attachStartRowClone + 6, 66, 0, 49)); * * } * * } else { * * row = sheet.getRow(87); * * cell = row.getCell(34); * * cell.setCellValue(" 1 1 "); * * } * * if (fixSize > 0) { * * * sheet.addMergedRegion(new Region(21, (short) 0, 21, (short) 49)); * * sheet.addMergedRegion(new Region(21, (short) 0, 21, (short) 49)); * * * sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 49)); * * row = sheet.getRow(21); * * cell = row.getCell(0); * * // cell.setCellStyle(style); * * cell.setCellValue(""); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(22); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(24); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(23); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue("??"); * * cell.setCellStyle(styleBorderThickLeftCenter); * * cell = row.getCell(2); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(10); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(21); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(24); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(30); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(34); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(40); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(44); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * for (int i = 0; i < maxFixSize; i++) { * * TbFixEntrustContent tbFixEntrustContent = * tbFixEntrustContentListAdd .get(i); * * row = sheet.getRow(25 + i * 2); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(i + 1); * * cell = row.getCell(2); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbFixEntrustContent.getStationCode()); * * cell = row.getCell(10); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbFixEntrustContent.getStationName()); * * cell = row.getCell(21); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbFixEntrustContent.getZl()); * * cell = row.getCell(24); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbFixEntrustContent.getProjectType()); * * cell = row.getCell(30); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbFixEntrustContent * .getFixHour()).divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(34); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbFixEntrustContent * .getWorkingHourPrice()) .divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(40); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal((1 - tbBusinessBalance * .getWorkingHourFavourRate()) * 100) .divide(new * BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(44); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbFixEntrustContent * .getFixHourAll()).divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); } * * } * * if ((fixSize <= maxSize - 1) && partSize > 0) { * * * sheet.addMergedRegion(new Region(partStartRow, (short) 0, * partStartRow, (short) 49)); * * sheet.addMergedRegion(new Region(partStartRow, (short) 0, * partStartRow, (short) 49)); * * * sheet.addMergedRegion(new CellRangeAddress(partStartRow, * partStartRow, 0, 49)); * * row = sheet.getRow(partStartRow); * * cell = row.getCell(0); * * // cell.setCellStyle(style); * * cell.setCellValue(""); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(partStartRow + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(partStartRow + 2 + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeftCenter); * * row = sheet.getRow(partStartRow + 2); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeftCenter); * * cell.setCellValue("??"); * * cell = row.getCell(2); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(10); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("??"); * * cell = row.getCell(21); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(24); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(30); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(34); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(40); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(44); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * if (partSize > 0) { * * for (int i = 0; i < maxPartSize; i++) { * * TbMaintianVo tbMaintianVo = maintianvosAdd.get(i); * * row = sheet.getRow(partStartRow + 4 + i * 2); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(i + 1); * * cell = row.getCell(2); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbMaintianVo.getPartCode()); * * cell = row.getCell(10); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbMaintianVo.getPartName()); * * cell = row.getCell(21); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbMaintianVo.getZl()); * * cell = row.getCell(24); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbMaintianVo.getProjectType()); * * cell = row.getCell(30); * * cell.setCellStyle(styleRight); * * cell.setCellValue(new BigDecimal(tbMaintianVo * .getPartQuantity()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(34); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbMaintianVo * .getPrice()).divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(40); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal((1 - tbBusinessBalance * .getFixPartFavourRate()) * 100).divide( new BigDecimal("1.00"), * 2, BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(44); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbMaintianVo .getPartQuantity()) * .multiply( new BigDecimal(tbMaintianVo.getPrice())) .divide(new * BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } * * } * * } * * int attachStartRow = 21; * * if ((fixSize + partSize) <= 17) { * * if (fixSize != 0 && partSize == 0 && fixSize < 18) { * * attachStartRow = 19 + 2 + 2 + 2 + fixSize * 2; * * } * * if (partSize != 0 && fixSize == 0 && partSize < 18) { * * attachStartRow = 19 + 2 + 2 + 2 + partSize * 2; } * * if (fixSize != 0 && partSize != 0 && (fixSize + partSize < 18)) { * * attachStartRow = 19 + 2 + 2 + 2 + 2 + 2 + fixSize * 2 + partSize * * 2; * * } * * * sheet.addMergedRegion(new Region(attachStartRow, (short) 0, * attachStartRow, (short) 49)); * * sheet.addMergedRegion(new Region(attachStartRow, (short) 0, * attachStartRow, (short) 49)); * * * sheet.addMergedRegion(new CellRangeAddress(attachStartRow, * attachStartRow, 0, 49)); * * row = sheet.getRow(attachStartRow); * * cell = row.getCell(0); * * // cell.setCellStyle(style); * * cell.setCellStyle(styleBorderThickLeft); * * cell.setCellValue(""); * * row = sheet.getRow(attachStartRow + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(attachStartRow + 2 + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeftCenter); * * row = sheet.getRow(attachStartRow + 2); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * cell.setCellStyle(styleBorderThickLeftCenter); * * cell.setCellValue("??"); * * if ("dfbz".equals(companyName)) { * * cell = row.getCell(10); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("??"); * * cell = row.getCell(21); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(24); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(30); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(34); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(40); * * cell.setCellStyle(styleCenter); * * } else if ("xtl".equals(companyName)) { * * * sheet.addMergedRegion(new Region(attachStartRow + 2, (short) 2, * attachStartRow + 2, (short) 39)); * * sheet.addMergedRegion(new Region(attachStartRow + 2, (short) 2, * attachStartRow + 2, (short) 39)); * * * sheet.addMergedRegion(new CellRangeAddress( attachStartRow + 2, * attachStartRow + 2, 2, 39)); * * cell = row.getCell(2); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * } * * * sheet.addMergedRegion(new Region(attachStartRow + 2, (short) 2, * attachStartRow + 2, (short) 39)); * * sheet.addMergedRegion(new Region(attachStartRow + 2, (short) 2, * attachStartRow + 2, (short) 39)); * * cell = row.getCell(2); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * * cell = row.getCell(40); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(44); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * row = sheet.getRow(attachStartRow + 4); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * cell.setCellStyle(styleBorderThickLeftCenter); * * cell.setCellValue("1"); * * row = sheet.getRow(attachStartRow + 4 + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeftCenter); * * if ("xtl".equals(companyName)) { * * * sheet.addMergedRegion(new Region(attachStartRow + 6, (short) 0, * 66, (short) 49)); * * sheet.addMergedRegion(new Region(attachStartRow + 6, (short) 0, * 66, (short) 49)); * * if (attachStartRow + 6 < 66) { * * sheet.addMergedRegion(new CellRangeAddress( attachStartRow + 6, * 66, 0, 49)); } * * } } */ int pageSize = 0; if ((fixSize > 0 && partSize == 0) || (partSize > 0 && fixSize == 0)) { if ((fixSize + partSize) <= 18) { pageSize = 1; } else { pageSize = (fixSize + partSize - 19) / 21 + 2; } for (int p = pageSize - 1; p >= 0; p--) { int maxSize = (p == pageSize - 1) ? (fixSize + partSize - p * 21) : 21; int startRow = 21; if (p == 0) { sheet = workbook.getSheetAt(0); for (int k = 1; k <= pageSize / 2; k++) { workbook.setSheetOrder(workbook.getSheetName(k), pageSize - k); } for (int k = 1; k < pageSize; k++) { workbook.setSheetName(k, "" + (k + 1) + ""); } workbook.setSheetName(0, "" + 1 + ""); } else { sheet = workbook.cloneSheet(0); //this.clearSheet(sheet, 21, 0, 66, 49); } if (fixSize > 0) { sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 49)); row = sheet.getRow(21); cell = row.getCell(0); cell.setCellValue(""); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(22); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(24); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(23); cell = row.getCell(0); cell.setCellValue("??"); cell.setCellStyle(styleBorderThickLeftCenter); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); for (int i = 0; i < maxSize; i++) { TbFixEntrustContent tbFixEntrustContent = tbFixEntrustContentListAdd.get(p * 21 + i); row = sheet.getRow(25 + i * 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue(p * 21 + i + 1); cell = row.getCell(2); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getStationCode()); cell = row.getCell(10); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getStationName()); cell = row.getCell(21); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getZl()); cell = row.getCell(24); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getProjectType()); cell = row.getCell(30); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHour()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(34); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getWorkingHourPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue( new BigDecimal((1 - tbBusinessBalance.getWorkingHourFavourRate()) * 100) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP) .toString()); cell = row.getCell(44); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHourAll()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } else if (partSize > 0) { sheet.addMergedRegion(new CellRangeAddress(startRow, startRow, 0, 49)); row = sheet.getRow(startRow); cell = row.getCell(0); // cell.setCellStyle(style); cell.setCellValue(""); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(startRow + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(startRow + 2 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); row = sheet.getRow(startRow + 2); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("??"); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); for (int i = 0; i < maxSize; i++) { TbMaintianVo tbMaintianVo = maintianvosAdd.get(p * 21 + i); row = sheet.getRow(startRow + 4 + i * 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue(p * 21 + i + 1); cell = row.getCell(2); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getPartCode()); cell = row.getCell(10); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getPartName()); cell = row.getCell(21); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getZl()); cell = row.getCell(24); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getProjectType()); cell = row.getCell(30); cell.setCellStyle(styleRight); cell.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(34); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal((1 - tbBusinessBalance.getFixPartFavourRate()) * 100) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(44); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .multiply(new BigDecimal(tbMaintianVo.getPrice())) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } row = sheet.getRow(87); cell = row.getCell(34); cell.setCellValue(" " + (p + 1) + " " + pageSize + " "); if (p == pageSize - 1) { int attachStartRow = 21 + 2 + 2 + (fixSize + partSize - (pageSize - 1) * 21) * 2; sheet.addMergedRegion(new CellRangeAddress(attachStartRow, attachStartRow, 0, 49)); row = sheet.getRow(attachStartRow); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); cell.setCellValue(""); row = sheet.getRow(attachStartRow + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(attachStartRow + 2 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); row = sheet.getRow(attachStartRow + 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("??"); if ("dfbz".equals(companyName)) { cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); } else if ("xtl".equals(companyName)) { cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue(""); } cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); row = sheet.getRow(attachStartRow + 4); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("1"); row = sheet.getRow(attachStartRow + 4 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); if ("xtl".equals(companyName)) { if (attachStartRow + 6 < 66) { sheet.addMergedRegion(new CellRangeAddress(attachStartRow + 6, 66, 0, 49)); } } } } } if (fixSize > 0 && partSize > 0) { if ((fixSize + partSize) <= 16) { pageSize = 1; } else { pageSize = (fixSize + partSize - 17) / 19 + 2; } int fixPageSize = fixSize / pageSize; int partPageSize = partSize / pageSize; int fixLastPageSize = fixSize - (pageSize - 1) * fixPageSize; int partLastPageSize = partSize - (pageSize - 1) * partPageSize; if ((fixPageSize + partPageSize) < 19 && fixPageSize != 0) { //if(fixLastPageSize >= partSize){ if (fixPageSize >= partPageSize) { fixPageSize = (19 - partPageSize) > fixSize ? fixSize : (19 - partPageSize); } else { partPageSize = (19 - fixPageSize) > partSize ? partSize : (19 - fixPageSize); } } fixLastPageSize = fixSize - (pageSize - 1) * fixPageSize; partLastPageSize = (partSize - (pageSize - 1) * partPageSize) < 0 ? 0 : (partSize - (pageSize - 1) * partPageSize); for (int p = pageSize - 1; p >= 0; p--) { int partStartRow = 21 + 2 + 2 + ((p == pageSize - 1) ? fixLastPageSize : fixPageSize) * 2; if (p == 0) { sheet = workbook.getSheetAt(0); if (pageSize > 1) { for (int k = 1; k <= pageSize / 2; k++) { workbook.setSheetOrder(workbook.getSheetName(k), pageSize - k); } for (int k = 1; k < pageSize; k++) { workbook.setSheetName(k, "" + (k + 1) + ""); } } workbook.setSheetName(0, "" + 1 + ""); } else { sheet = workbook.cloneSheet(0); } if (fixSize > 0) { sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 49)); row = sheet.getRow(21); cell = row.getCell(0); cell.setCellValue(""); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(22); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(24); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(23); cell = row.getCell(0); cell.setCellValue("??"); cell.setCellStyle(styleBorderThickLeftCenter); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); for (int i = 0; i < ((p == pageSize - 1) ? fixLastPageSize : fixPageSize); i++) { TbFixEntrustContent tbFixEntrustContent = tbFixEntrustContentListAdd .get(p * fixPageSize + i); row = sheet.getRow(25 + i * 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue(p * fixPageSize + i + 1); cell = row.getCell(2); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getStationCode()); cell = row.getCell(10); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getStationName()); cell = row.getCell(21); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getZl()); cell = row.getCell(24); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getProjectType()); cell = row.getCell(30); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHour()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(34); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getWorkingHourPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue( new BigDecimal((1 - tbBusinessBalance.getWorkingHourFavourRate()) * 100) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP) .toString()); cell = row.getCell(44); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHourAll()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } if (partSize > 0) { sheet.addMergedRegion(new CellRangeAddress(partStartRow, partStartRow, 0, 49)); row = sheet.getRow(partStartRow); cell = row.getCell(0); // cell.setCellStyle(style); cell.setCellValue(""); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(partStartRow + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(partStartRow + 2 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); row = sheet.getRow(partStartRow + 2); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("??"); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); for (int i = 0; i < ((p == pageSize - 1) ? partLastPageSize : partPageSize); i++) { if (p * partPageSize + i > partSize - 1) { break; } TbMaintianVo tbMaintianVo = maintianvosAdd.get(p * partPageSize + i); row = sheet.getRow(partStartRow + 4 + i * 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue(p * partPageSize + i + 1); cell = row.getCell(2); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getPartCode()); cell = row.getCell(10); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getPartName()); cell = row.getCell(21); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getZl()); cell = row.getCell(24); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getProjectType()); cell = row.getCell(30); cell.setCellStyle(styleRight); cell.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(34); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal((1 - tbBusinessBalance.getFixPartFavourRate()) * 100) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(44); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .multiply(new BigDecimal(tbMaintianVo.getPrice())) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } row = sheet.getRow(87); cell = row.getCell(34); cell.setCellValue(" " + (p + 1) + " " + pageSize + " "); if (p == pageSize - 1) { int attachStartRow = 21 + 2 + 2 + 2 + 2 + (fixLastPageSize + partLastPageSize) * 2; sheet.addMergedRegion(new CellRangeAddress(attachStartRow, attachStartRow, 0, 49)); row = sheet.getRow(attachStartRow); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); cell.setCellValue(""); row = sheet.getRow(attachStartRow + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(attachStartRow + 2 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); row = sheet.getRow(attachStartRow + 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("??"); if ("dfbz".equals(companyName)) { cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); } else if ("xtl".equals(companyName)) { cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue(""); } cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); row = sheet.getRow(attachStartRow + 4); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("1"); row = sheet.getRow(attachStartRow + 4 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); if ("xtl".equals(companyName)) { if (attachStartRow + 6 < 66) { sheet.addMergedRegion(new CellRangeAddress(attachStartRow + 6, 66, 0, 49)); } } } } } workbook.write(os); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } // 8 41 }
From source file:com.selfsoft.business.service.impl.TbBusinessSpecialBalanceServiceImpl.java
public void printTbBusinessBalanceTemplate(OutputStream os, String tpl, Long id, String companyName) { // ??/*from ww w. j a va 2s . c o m*/ TbBusinessSpecialBalance tbBusinessSpecialBalance = this.findById(id); // ? TbFixEntrust tbFixEntrust = null; if (null != tbBusinessSpecialBalance.getTbBusinessBalance()) { tbFixEntrust = tbFixEntrustService .findById(tbBusinessSpecialBalance.getTbBusinessBalance().getTbFixEntrust().getId()); } else { tbFixEntrust = tbFixEntrustService.findById(tbBusinessSpecialBalance.getEntrustId()); } // ? TbCustomer tbCustomer = tbCustomerService.findById(tbFixEntrust.getTbCustomer().getId()); // ? TbCarInfo tbCarInfo = tbCarInfoService.findById(tbFixEntrust.getTbCarInfo().getId()); // ?? TmCompany tmCompany = tmCompanyService.acquireUniqueTmCompany(); /*// ? List<TbFixEntrustContent> tbFixEntrustContentList = tbFixEntrustContentService .findTbFixEnTrustContentListByTbFixEntrustId(tbFixEntrust .getId()); // ?? List<TbMaintianVo> maintianvos = tbMaintainPartContentService .getTbMaintianDetailVosByEntrustId(tbFixEntrust.getId(), Constants.BALANCE_ALL); // ? List<TmStockOutDetVo> tmStockOutDetVos = tmStockOutService .getSellDetailByEntrustCode(tbFixEntrust.getEntrustCode(), Constants.BALANCE_ALL);*/ // ? List<TbSpecialWorkingContent> tbSpecialWorkingContentList = tbSpecialWorkingContentService .findBySpecialId(id); // ?? List<TbSpecialPartContent> tbSpecialPartContentList = tbSpecialPartContentService.findBySpecialId(id); // /*List<TbBusinessBalanceItem> tbBusinessBalanceItemList = tbBusinessBalanceItemService .findGroupTbBusinessBalanceItemListByTbBusinessBalanceId(tbBusinessBalance .getId());*/ List<TbBusinessSpecialBalanceItem> tbBusinessSpecialBalanceItemList = tbBusinessSpecialBalanceItemService .findBySpecialId(id); // ??? /*if (null == maintianvos) { maintianvos = new ArrayList<TbMaintianVo>(); } if (null != tmStockOutDetVos && tmStockOutDetVos.size() > 0) { for (TmStockOutDetVo tmStockOutDetVo : tmStockOutDetVos) { TbMaintianVo tbMaintianVo = new TbMaintianVo(); tbMaintianVo.setPartId(tmStockOutDetVo.getPartinfoId()); tbMaintianVo.setHouseName(tmStockOutDetVo.getHouseName()); tbMaintianVo.setPartCode(tmStockOutDetVo.getPartCode()); tbMaintianVo.setPartName(tmStockOutDetVo.getPartName()); tbMaintianVo.setUnitName(tmStockOutDetVo.getUnitName()); tbMaintianVo.setPrice(tmStockOutDetVo.getPrice()); tbMaintianVo.setPartQuantity(tmStockOutDetVo.getQuantity()); tbMaintianVo.setTotal(tmStockOutDetVo.getTotal()); tbMaintianVo.setIsFree(tmStockOutDetVo.getIsFree()); maintianvos.add(tbMaintianVo); } }*/ int fixSize = (tbSpecialWorkingContentList == null ? 0 : tbSpecialWorkingContentList.size()); int partSize = (tbSpecialPartContentList == null ? 0 : tbSpecialPartContentList.size()); try { HSSFWorkbook workbook = new HSSFWorkbook(this.getClass().getResourceAsStream(tpl)); HSSFSheet sheet = workbook.getSheetAt(0); HSSFCellStyle style = workbook.createCellStyle(); style.setWrapText(true); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 9); style.setFont(font); HSSFCellStyle styleBorderThinAll = workbook.createCellStyle(); styleBorderThinAll.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setWrapText(true); styleBorderThinAll.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleBorderThinAll.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThinAll.setFont(font); HSSFCellStyle styleBorderThickLeft = workbook.createCellStyle(); styleBorderThickLeft.setBorderLeft(HSSFCellStyle.BORDER_THICK); styleBorderThickLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThickLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickLeft.setWrapText(true); styleBorderThickLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleBorderThickLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickLeft.setFont(font); HSSFCellStyle styleBorderThickRight = workbook.createCellStyle(); styleBorderThickRight.setBorderRight(HSSFCellStyle.BORDER_THICK); styleBorderThickRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThickRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickRight.setWrapText(true); styleBorderThickRight.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleBorderThickRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickRight.setFont(font); HSSFCellStyle styleBorderThinAllCenter = workbook.createCellStyle(); styleBorderThinAllCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setWrapText(true); styleBorderThinAllCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleBorderThinAllCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThinAllCenter.setFont(font); HSSFCellStyle styleBorderThickLeftCenter = workbook.createCellStyle(); styleBorderThickLeftCenter.setBorderLeft(HSSFCellStyle.BORDER_THICK); styleBorderThickLeftCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThickLeftCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickLeftCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickLeftCenter.setWrapText(true); styleBorderThickLeftCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleBorderThickLeftCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickLeftCenter.setFont(font); HSSFCellStyle styleBorderThickRightCenter = workbook.createCellStyle(); styleBorderThickRightCenter.setBorderRight(HSSFCellStyle.BORDER_THICK); styleBorderThickRightCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThickRightCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickRightCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickRightCenter.setWrapText(true); styleBorderThickRightCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleBorderThickRightCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickRightCenter.setFont(font); HSSFCellStyle styleCenter = workbook.createCellStyle(); styleCenter.setWrapText(true); styleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCenter.setFont(font); HSSFRow row = null; HSSFCell cell = null; row = sheet.getRow(2); cell = row.getCell(4); // cell.setCellStyle(style); if ("dfbz".equals(companyName)) { cell.setCellValue(Constants.getCompanyMap().get("dfbzCode")); } else if ("xtl".equals(companyName)) { cell.setCellValue(Constants.getCompanyMap().get("xtlCode")); } row = sheet.getRow(2); cell = row.getCell(17); if ("xtl".equals(companyName)) { cell.setCellValue(/* tmCompany.getCompanyName() */"??"); } else { cell.setCellValue(tmCompany.getCompanyName()); } row = sheet.getRow(2); cell = row.getCell(33); cell.setCellValue(tmCompany.getCompanyPhone() == null ? "" : tmCompany.getCompanyPhone()); row = sheet.getRow(4); cell = row.getCell(4); if ("xtl".equals(companyName)) { cell.setCellValue(""); } else { cell.setCellValue(tmCompany.getCompanyZipCode() == null ? "" : tmCompany.getCompanyZipCode()); } row = sheet.getRow(4); cell = row.getCell(17); cell.setCellValue(tmCompany.getCompanyAddress() == null ? "" : tmCompany.getCompanyAddress()); row = sheet.getRow(4); cell = row.getCell(33); cell.setCellValue(tmCompany.getCompanyFax() == null ? "" : tmCompany.getCompanyFax()); row = sheet.getRow(10); cell = row.getCell(2); // cell.setCellStyle(style); String entrustCode = tbFixEntrust.getEntrustCode(); String[] es = entrustCode.split("-"); String newCode = "RO" + es[0].substring(2, 6) + es[1]; cell.setCellValue(newCode); row = sheet.getRow(10); cell = row.getCell(24); // cell.setCellStyle(style); cell.setCellValue((tbFixEntrust.getTmUser().getUserRealName() == null || "".equals(tbFixEntrust.getTmUser().getUserRealName())) ? tbFixEntrust.getTmUser().getUserName() : tbFixEntrust.getTmUser().getUserRealName()); row = sheet.getRow(10); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue( CommonMethod.parseDateToString(tbBusinessSpecialBalance.getBananceDate(), "yyyy-MM-dd HH:mm")); row = sheet.getRow(8); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(CommonMethod.parseDateToString(tbFixEntrust.getFixDate(), "yyyy-MM-dd HH:mm")); row = sheet.getRow(12); cell = row.getCell(4); cell.setCellValue(tbCustomer.getCustomerName()); row = sheet.getRow(12); cell = row.getCell(24); cell.setCellValue(tbCarInfo.getLicenseCode()); row = sheet.getRow(12); cell = row.getCell(41); cell.setCellValue(tbCarInfo.getTmCarModelType().getModelName()); row = sheet.getRow(14); cell = row.getCell(4); cell.setCellValue(tbCustomer.getTelephone() == null ? "" : tbCustomer.getTelephone()); row = sheet.getRow(18); cell = row.getCell(12); cell.setCellValue(tbCustomer.getTelephone() == null ? "" : tbCustomer.getTelephone()); row = sheet.getRow(14); cell = row.getCell(24); cell.setCellValue(tbCarInfo.getChassisCode()); row = sheet.getRow(16); cell = row.getCell(4); cell.setCellValue(tbCustomer.getAddress() == null ? "" : tbCustomer.getAddress()); row = sheet.getRow(16); cell = row.getCell(24); cell.setCellValue(CommonMethod.parseDateToString(tbCarInfo.getPurchaseDate(), "yyyy-MM-dd")); row = sheet.getRow(16); cell = row.getCell(41); if ("xtl".equals(companyName)) { cell.setCellValue( tbFixEntrust.getEnterStationKilo() == null ? "" : new BigDecimal(tbFixEntrust.getEnterStationKilo()) .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP).toString() + " Km"); } else { cell.setCellValue( tbFixEntrust.getEnterStationKilo() == null ? "" : new BigDecimal(tbFixEntrust.getEnterStationKilo()) .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP).toString() + " Km"); } row = sheet.getRow(18); cell = row.getCell(4); cell.setCellValue(tbCustomer.getContractPerson() == null ? "" : tbCustomer.getContractPerson()); row = sheet.getRow(18); cell = row.getCell(24); cell.setCellValue(tbCarInfo.getEngineCode() == null ? "" : tbCarInfo.getEngineCode()); row = sheet.getRow(18); cell = row.getCell(41); cell.setCellValue(tbCarInfo.getColor() == null ? "" : tbCarInfo.getColor()); if (null != tbBusinessSpecialBalanceItemList && tbBusinessSpecialBalanceItemList.size() > 0) { for (TbBusinessSpecialBalanceItem tbBusinessSpecialBalanceItem : tbBusinessSpecialBalanceItemList) { if ("XLCLF".equals(tbBusinessSpecialBalanceItem.getBalanceItemCode())) { row = sheet.getRow(77); cell = row.getCell(24); cell.setCellValue(new BigDecimal(tbBusinessSpecialBalanceItem.getBalanceItemTotal()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); continue; } if ("XLGSF".equals(tbBusinessSpecialBalanceItem.getBalanceItemCode())) { row = sheet.getRow(75); cell = row.getCell(24); cell.setCellValue(new BigDecimal(tbBusinessSpecialBalanceItem.getBalanceItemTotal()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); continue; } if ("ZJE".equals(tbBusinessSpecialBalanceItem.getBalanceItemCode())) { row = sheet.getRow(81); cell = row.getCell(24); cell.setCellValue(new BigDecimal(tbBusinessSpecialBalanceItem.getBalanceItemTotal()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); continue; } } row = sheet.getRow(79); cell = row.getCell(24); cell.setCellValue("0.00"); } Double wt = tbBusinessSpecialBalance.getWorkingHourTotalAll() == null ? 0d : tbBusinessSpecialBalance.getWorkingHourTotalAll(); Double wf = tbBusinessSpecialBalance.getWorkingHourFavourRate() == null ? 0d : tbBusinessSpecialBalance.getWorkingHourFavourRate(); Double pt = tbBusinessSpecialBalance.getFixPartTotalAll() == null ? 0d : tbBusinessSpecialBalance.getFixPartTotalAll(); Double pf = tbBusinessSpecialBalance.getFixPartFavourRate() == null ? 0d : tbBusinessSpecialBalance.getFixPartFavourRate(); Double st = tbBusinessSpecialBalance.getSolePartTotalAll() == null ? 0d : tbBusinessSpecialBalance.getSolePartTotalAll(); Double sf = tbBusinessSpecialBalance.getSolePartFavourRate() == null ? 0d : tbBusinessSpecialBalance.getSolePartFavourRate(); BigDecimal fixF = new BigDecimal(wt).divide(new BigDecimal(1 - wf), 2, BigDecimal.ROUND_HALF_UP) .multiply(new BigDecimal(wf)); BigDecimal partF = new BigDecimal(pt).divide(new BigDecimal(1 - pf), 2, BigDecimal.ROUND_HALF_UP) .multiply(new BigDecimal(pf)); BigDecimal soleF = new BigDecimal(st).divide(new BigDecimal(1 - sf), 2, BigDecimal.ROUND_HALF_UP) .multiply(new BigDecimal(sf)); row = sheet.getRow(83); cell = row.getCell(4); // cell.setCellStyle(styleCenter); cell.setCellValue(fixF.add(partF).add(soleF).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP) .toString()); row = sheet.getRow(83); cell = row.getCell(14); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbBusinessSpecialBalance.getPayedAmount() == null ? 0d : tbBusinessSpecialBalance.getPayedAmount()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(68); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(CommonMethod.parseDateToString(tbFixEntrust.getRemindMaintainDate(), "yyyy-MM-dd")); row = sheet.getRow(70); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrust.getRemindMaintainKilo() == null ? "" : new BigDecimal(tbFixEntrust.getRemindMaintainKilo()).divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP) + " Km"); row = sheet.getRow(77); cell = row.getCell(30); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrust.getRemark()); ByteArrayOutputStream byteArrayOutImgLion = new ByteArrayOutputStream(); String pic = ""; if ("dfbz".equals(companyName)) { pic = "/lion_jsd.png"; } else if ("xtl".equals(companyName)) { pic = "/xtl_jsd.png"; } BufferedImage bufferImgLion = ImageIO.read(this.getClass().getResourceAsStream(pic)); ImageIO.write(bufferImgLion, "png", byteArrayOutImgLion); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFClientAnchor anchorLion = new HSSFClientAnchor(0, 0, 1023, 200, (short) 41, 0, (short) 47, 7); patriarch.createPicture(anchorLion, workbook.addPicture(byteArrayOutImgLion.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)); int maxSize = 19; int maxFixSize = (fixSize > 19 ? 19 : fixSize); int maxPartSize = maxSize - maxFixSize > partSize ? partSize : maxSize - maxFixSize; int partStartRow = (fixSize == 0 ? 21 : 21 + maxFixSize * 2 + 4); int partStartRowClone = ((fixSize - maxFixSize) == 0 ? 21 : 21 + (fixSize - maxFixSize) * 2 + 4); int minBlank = 9; if ((fixSize + partSize) > 17) { HSSFSheet sheetClone = workbook.cloneSheet(0); HSSFRow rowClone = sheetClone.getRow(87); HSSFCell cellClone = rowClone.getCell(34); cellClone.setCellValue("2 2 "); HSSFPatriarch patriarchClone = sheetClone.createDrawingPatriarch(); HSSFClientAnchor anchorLionClone = new HSSFClientAnchor(0, 0, 1023, 200, (short) 41, 0, (short) 47, 8); patriarchClone.createPicture(anchorLionClone, workbook.addPicture(byteArrayOutImgLion.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)); if ((fixSize - maxFixSize) > 0) { /* * sheetClone.addMergedRegion(new Region(21, (short) 0, 21, * (short) 49)); * * sheetClone.addMergedRegion(new Region(21, (short) 0, 21, * (short) 0)); */ sheetClone.addMergedRegion(new CellRangeAddress(21, 21, 0, 49)); rowClone = sheetClone.getRow(21); cellClone = rowClone.getCell(0); // cellClone.setCellStyle(style); cellClone.setCellValue(""); cellClone.setCellStyle(styleBorderThickLeft); rowClone = sheetClone.getRow(21); cellClone = rowClone.getCell(0); cellClone.setCellStyle(styleBorderThickLeft); rowClone = sheetClone.getRow(24); cellClone = rowClone.getCell(0); cellClone.setCellStyle(styleBorderThickLeftCenter); rowClone = sheetClone.getRow(23); cellClone = rowClone.getCell(0); // cellClone.setCellStyle(styleCenter); cellClone.setCellStyle(styleBorderThickLeftCenter); cellClone.setCellValue("??"); cellClone = rowClone.getCell(2); cellClone.setCellStyle(styleCenter); cellClone.setCellValue("?"); cellClone = rowClone.getCell(10); cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); cellClone = rowClone.getCell(21); cellClone.setCellStyle(styleCenter); cellClone.setCellValue("?"); cellClone = rowClone.getCell(24); cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); cellClone = rowClone.getCell(30); cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); cellClone = rowClone.getCell(34); cellClone.setCellStyle(styleCenter); cellClone.setCellValue("?"); cellClone = rowClone.getCell(40); cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); cellClone = rowClone.getCell(44); cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); for (int i = 0; i < (fixSize - maxFixSize); i++) { TbSpecialWorkingContent tbFixEntrustContent = tbSpecialWorkingContentList.get(i); rowClone = sheetClone.getRow(25 + i * 2); cellClone = rowClone.getCell(0); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(i + 1); cellClone = rowClone.getCell(2); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(tbFixEntrustContent.getStationCode()); cellClone = rowClone.getCell(10); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(tbFixEntrustContent.getStationName()); cellClone = rowClone.getCell(21); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); cellClone = rowClone.getCell(24); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); cellClone = rowClone.getCell(30); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHour()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cellClone = rowClone.getCell(34); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(new BigDecimal(tbFixEntrustContent.getWorkingHourPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cellClone = rowClone.getCell(40); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(new BigDecimal(tbBusinessSpecialBalance.getWorkingHourFavourRate()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cellClone = rowClone.getCell(44); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHourAll()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } if ((partSize - maxPartSize) > 0) { /* * sheetClone.addMergedRegion(new Region(partStartRowClone, * (short) 0, partStartRowClone, (short) 49)); * * sheetClone.addMergedRegion(new Region(partStartRowClone, * (short) 0, partStartRowClone, (short) 49)); */ sheetClone.addMergedRegion(new CellRangeAddress(partStartRowClone, partStartRowClone, 0, 49)); rowClone = sheetClone.getRow(partStartRowClone); cellClone = rowClone.getCell(0); // cellClone.setCellStyle(style); cellClone.setCellStyle(styleBorderThickLeft); cellClone.setCellValue(""); rowClone = sheetClone.getRow(partStartRowClone + 1); cellClone = rowClone.getCell(0); cellClone.setCellStyle(styleBorderThickLeft); rowClone = sheetClone.getRow(partStartRowClone + 2 + 1); cellClone = rowClone.getCell(0); cellClone.setCellStyle(styleBorderThickLeftCenter); rowClone = sheetClone.getRow(partStartRowClone + 2); cellClone = rowClone.getCell(0); // cellClone.setCellStyle(styleCenter); cellClone.setCellStyle(styleBorderThickLeftCenter); cellClone.setCellValue("??"); cellClone = rowClone.getCell(2); cellClone.setCellStyle(styleCenter); cellClone.setCellValue("?"); cellClone = rowClone.getCell(10); cellClone.setCellStyle(styleCenter); cellClone.setCellValue("??"); cellClone = rowClone.getCell(21); cellClone.setCellStyle(styleCenter); cellClone.setCellValue("?"); cellClone = rowClone.getCell(24); cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); cellClone = rowClone.getCell(30); cellClone.setCellStyle(styleCenter); cellClone.setCellValue("?"); cellClone = rowClone.getCell(34); cellClone.setCellStyle(styleCenter); cellClone.setCellValue("?"); cellClone = rowClone.getCell(40); cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); cellClone = rowClone.getCell(44); cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); for (int i = 0; i < (partSize - maxPartSize); i++) { TbSpecialPartContent tbMaintianVo = tbSpecialPartContentList.get(i); rowClone = sheetClone.getRow(partStartRowClone + 4 + i * 2); cellClone = rowClone.getCell(0); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(i + 1); cellClone = rowClone.getCell(2); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(tbMaintianVo.getPartCode()); cellClone = rowClone.getCell(10); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(tbMaintianVo.getPartName()); cellClone = rowClone.getCell(21); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); cellClone = rowClone.getCell(24); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); cellClone = rowClone.getCell(30); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cellClone = rowClone.getCell(34); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(new BigDecimal(tbMaintianVo.getPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cellClone = rowClone.getCell(40); // .setCellStyle(styleCenter); cellClone.setCellValue(new BigDecimal(tbBusinessSpecialBalance.getFixPartFavourRate()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cellClone = rowClone.getCell(44); // cellClone.setCellStyle(styleCenter); cellClone.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .multiply(new BigDecimal(tbMaintianVo.getPrice())) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } int attachStartRowClone = 21; if ((fixSize - maxFixSize + partSize - maxPartSize) <= 17) { if ((fixSize - maxFixSize) != 0 && (partSize - maxPartSize) == 0 && (fixSize - maxFixSize) < 18) { attachStartRowClone = 21 + 2 + 2 + (fixSize - maxFixSize) * 2; } if ((partSize - maxPartSize) != 0 && (fixSize - maxFixSize) == 0 && (partSize - maxPartSize) < 18) { attachStartRowClone = 21 + 2 + 2 + 2 + (partSize - maxPartSize) * 2; } if ((fixSize - maxFixSize) != 0 && (partSize - maxPartSize) != 0 && (fixSize - maxFixSize + partSize - maxPartSize < 18)) { attachStartRowClone = 21 + 2 + 2 + 2 + 2 + (fixSize - maxFixSize) * 2 + (partSize - maxPartSize) * 2; } /* * sheetClone.addMergedRegion(new * Region(attachStartRowClone, (short) 0, * attachStartRowClone, (short) 49)); * * sheetClone.addMergedRegion(new * Region(attachStartRowClone, (short) 0, * attachStartRowClone, (short) 49)); */ sheetClone .addMergedRegion(new CellRangeAddress(attachStartRowClone, attachStartRowClone, 0, 49)); rowClone = sheetClone.getRow(attachStartRowClone); cellClone = rowClone.getCell(0); // cellClone.setCellStyle(style); cellClone.setCellStyle(styleBorderThickLeft); cellClone.setCellValue(""); rowClone = sheetClone.getRow(attachStartRowClone + 1); cellClone = rowClone.getCell(0); cellClone.setCellStyle(styleBorderThickLeft); rowClone = sheetClone.getRow(attachStartRowClone + 2 + 1); cellClone = rowClone.getCell(0); // cellClone.setCellStyle(styleCenter); cellClone.setCellStyle(styleBorderThickLeftCenter); rowClone = sheetClone.getRow(attachStartRowClone + 2); cellClone = rowClone.getCell(0); // cellClone.setCellStyle(styleCenter); cellClone.setCellStyle(styleBorderThickLeftCenter); cellClone.setCellValue("??"); if ("dfbz".equals(companyName)) { cellClone = rowClone.getCell(10); cellClone.setCellStyle(styleCenter); cellClone.setCellValue("??"); cellClone = rowClone.getCell(21); cellClone.setCellStyle(styleCenter); cellClone.setCellValue("?"); cellClone = rowClone.getCell(24); cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); cellClone = rowClone.getCell(30); cellClone.setCellStyle(styleCenter); cellClone.setCellValue("?"); cellClone = rowClone.getCell(34); cellClone.setCellStyle(styleCenter); cellClone.setCellValue("?"); cellClone = rowClone.getCell(40); cellClone.setCellStyle(styleCenter); } else if ("xtl".equals(companyName)) { /* * sheetClone.addMergedRegion(new * Region(attachStartRowClone + 2, (short) 2, * attachStartRowClone + 2, (short) 39)); * * sheetClone.addMergedRegion(new * Region(attachStartRowClone + 2, (short) 2, * attachStartRowClone + 2, (short) 39)); */ sheetClone.addMergedRegion( new CellRangeAddress(attachStartRowClone + 2, attachStartRowClone + 2, 2, 39)); cellClone = rowClone.getCell(2); cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); } cellClone = rowClone.getCell(40); cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); cellClone = rowClone.getCell(44); cellClone.setCellStyle(styleCenter); cellClone.setCellValue(""); rowClone = sheetClone.getRow(attachStartRowClone + 4); cellClone = rowClone.getCell(0); // cellClone.setCellStyle(styleCenter); cellClone.setCellStyle(styleBorderThickLeftCenter); cellClone.setCellValue("1"); rowClone = sheetClone.getRow(attachStartRowClone + 4 + 1); cellClone = rowClone.getCell(0); cellClone.setCellStyle(styleBorderThickLeftCenter); /* * sheetClone.addMergedRegion(new Region(attachStartRowClone * + 6, (short) 0, 66, (short) 49)); * * sheetClone.addMergedRegion(new Region(attachStartRowClone * + 6, (short) 0, 66, (short) 49)); */ sheetClone.addMergedRegion(new CellRangeAddress(attachStartRowClone + 6, 66, 0, 49)); } } else { row = sheet.getRow(87); cell = row.getCell(34); cell.setCellValue(" 1 1 "); } if (fixSize > 0) { /* * sheet.addMergedRegion(new Region(21, (short) 0, 21, (short) * 49)); * * sheet.addMergedRegion(new Region(21, (short) 0, 21, (short) * 49)); */ sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 49)); row = sheet.getRow(21); cell = row.getCell(0); // cell.setCellStyle(style); cell.setCellValue(""); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(22); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(24); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(23); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell.setCellStyle(styleBorderThickLeftCenter); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); for (int i = 0; i < maxFixSize; i++) { TbSpecialWorkingContent tbFixEntrustContent = tbSpecialWorkingContentList.get(i); row = sheet.getRow(25 + i * 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue(i + 1); cell = row.getCell(2); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getStationCode()); cell = row.getCell(10); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getStationName()); cell = row.getCell(21); // cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(24); // cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHour()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(34); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getWorkingHourPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue( new BigDecimal((1 - tbBusinessSpecialBalance.getWorkingHourFavourRate()) * 100) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(44); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHourAll()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } if ((fixSize <= maxSize - 1) && partSize > 0) { /* * sheet.addMergedRegion(new Region(partStartRow, (short) 0, * partStartRow, (short) 49)); * * sheet.addMergedRegion(new Region(partStartRow, (short) 0, * partStartRow, (short) 49)); */ sheet.addMergedRegion(new CellRangeAddress(partStartRow, partStartRow, 0, 49)); row = sheet.getRow(partStartRow); cell = row.getCell(0); // cell.setCellStyle(style); cell.setCellValue(""); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(partStartRow + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(partStartRow + 2 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); row = sheet.getRow(partStartRow + 2); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("??"); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); if (partSize > 0) { for (int i = 0; i < maxPartSize; i++) { TbSpecialPartContent tbMaintianVo = tbSpecialPartContentList.get(i); row = sheet.getRow(partStartRow + 4 + i * 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue(i + 1); cell = row.getCell(2); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getPartCode()); cell = row.getCell(10); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getPartName()); cell = row.getCell(21); // cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(24); // cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(34); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue( new BigDecimal((1 - tbBusinessSpecialBalance.getFixPartFavourRate()) * 100) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(44); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .multiply(new BigDecimal(tbMaintianVo.getPrice())) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } } int attachStartRow = 21; if ((fixSize + partSize) <= 17) { if (fixSize != 0 && partSize == 0 && fixSize < 18) { attachStartRow = 19 + 2 + 2 + 2 + fixSize * 2; } if (partSize != 0 && fixSize == 0 && partSize < 18) { attachStartRow = 19 + 2 + 2 + 2 + partSize * 2; } if (fixSize != 0 && partSize != 0 && (fixSize + partSize < 18)) { attachStartRow = 19 + 2 + 2 + 2 + 2 + 2 + fixSize * 2 + partSize * 2; } /* * sheet.addMergedRegion(new Region(attachStartRow, (short) 0, * attachStartRow, (short) 49)); * * sheet.addMergedRegion(new Region(attachStartRow, (short) 0, * attachStartRow, (short) 49)); */ sheet.addMergedRegion(new CellRangeAddress(attachStartRow, attachStartRow, 0, 49)); row = sheet.getRow(attachStartRow); cell = row.getCell(0); // cell.setCellStyle(style); cell.setCellStyle(styleBorderThickLeft); cell.setCellValue(""); row = sheet.getRow(attachStartRow + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(attachStartRow + 2 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); row = sheet.getRow(attachStartRow + 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("??"); if ("dfbz".equals(companyName)) { cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); } else if ("xtl".equals(companyName)) { /* * sheet.addMergedRegion(new Region(attachStartRow + 2, * (short) 2, attachStartRow + 2, (short) 39)); * * sheet.addMergedRegion(new Region(attachStartRow + 2, * (short) 2, attachStartRow + 2, (short) 39)); */ sheet.addMergedRegion(new CellRangeAddress(attachStartRow + 2, attachStartRow + 2, 2, 39)); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue(""); } /* * sheet.addMergedRegion(new Region(attachStartRow + 2, (short) * 2, attachStartRow + 2, (short) 39)); * * sheet.addMergedRegion(new Region(attachStartRow + 2, (short) * 2, attachStartRow + 2, (short) 39)); * * cell = row.getCell(2); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); */ cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); row = sheet.getRow(attachStartRow + 4); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("1"); row = sheet.getRow(attachStartRow + 4 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); if ("xtl".equals(companyName)) { /* * sheet.addMergedRegion(new Region(attachStartRow + 6, * (short) 0, 66, (short) 49)); * * sheet.addMergedRegion(new Region(attachStartRow + 6, * (short) 0, 66, (short) 49)); */ try { sheet.addMergedRegion(new CellRangeAddress(attachStartRow + 6, 66, 0, 49)); } catch (Exception e) { sheet.addMergedRegion(new CellRangeAddress(attachStartRow + 6, 67, 0, 49)); e.printStackTrace(); } } } workbook.write(os); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } // 8 41 }
From source file:com.sevenorcas.openstyle.app.service.spreadsheet.SpreadSheetServiceImp.java
/** * Export sheet to workbook//w ww .j a v a 2s . c om * @param spreadSheet * @param workbook * @throws Exception */ private void exportSpreadSheet(List<SpreadSheet> sheets, HSSFWorkbook wb) throws Exception { for (SpreadSheet ss : sheets) { HSSFSheet sheet = wb.createSheet(ss.getSheetname()); ss.setWorkBook(wb); ss.createFreezePane(sheet); /*-*************************************************************** * Default column widths ****************************************************************/ for (int column = 0; column <= ss.getLastColumn(); column++) { if (ss.getColumnWidth(column) != -1) { sheet.setColumnWidth(column, ss.getColumnWidth(column)); } } /*-*************************************************************** * Output row data ****************************************************************/ for (int row = 0; row <= ss.getLastRow(); row++) { for (int column = 0; column <= ss.getLastColumn(); column++) { HSSFRow sheetRow = sheet.getRow(row); if (sheetRow == null) { sheetRow = sheet.createRow(row); } HSSFCell cell = sheetRow.createCell(column); SpreadsheetCell cellX = ss.getCell(column, row); if (cellX != null) { if (cellX.getCellRangeAddress() != null) { sheet.addMergedRegion(cellX.getCellRangeAddress()); } //Ex HSSFCellStyle style = cellX.getCellStyle(wb); cell.setCellStyle(style); boolean set = ss.getColumnWidth(column) == -1; if (set && cellX.isHeader() && cellX.getWidth() != null) { sheet.setColumnWidth(column, cellX.getWidth()); } cellX.setCellValue(cell, wb); } else { HSSFCellStyle style = ss.getCellStyleDefault(wb, row, column); cell.setCellStyle(style); } } } } }
From source file:com.smi.travel.controller.excel.account.AccountReportSummary.java
public void getChangeARReport(HSSFWorkbook wb, List changeARReport) { List<ARNirvana> listAR = changeARReport; String sheetName = "Sheet1";// name of sheet HSSFSheet sheet = wb.createSheet(sheetName); UtilityExcelFunction excelFunction = new UtilityExcelFunction(); // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheet.createRow(0);// w ww. ja v a2s . co m HSSFCell cell1 = row1.createCell(1); cell1.setCellValue("CHANGE AR REPORT"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cell1.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("B1:I1")); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); HSSFDataFormat currency = wb.createDataFormat(); // Set align Text HSSFCellStyle styleNumber = wb.createCellStyle(); styleNumber.setAlignment(styleC21.ALIGN_RIGHT); styleNumber.setDataFormat(currency.getFormat("#,##0.00")); // styleNumber.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0")); ARNirvana arTemp = new ARNirvana(); if (!listAR.isEmpty()) { arTemp = (ARNirvana) listAR.get(0); } // Row 2 HSSFRow row2 = sheet.createRow(1); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("Print on : "); cell21.setCellStyle(styleC21); HSSFCell cell22 = row2.createCell(2); Date date = new Date(); SimpleDateFormat sm = new SimpleDateFormat("dd/MM/yyyy"); String strDate = sm.format(date); cell22.setCellValue(strDate); cell22.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("C2:E2")); // Row 3 HSSFRow row3 = sheet.createRow(2); HSSFCell cell31 = row3.createCell(1); cell31.setCellValue("Report Of : "); cell31.setCellStyle(styleC21); HSSFCell cell32 = row3.createCell(2); cell32.setCellValue(arTemp.getPrintofdatePage()); cell32.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("C3:D3")); // Row 4 HSSFRow row4 = sheet.createRow(3); HSSFCell cell41 = row4.createCell(1); cell41.setCellValue("Department : "); cell41.setCellStyle(styleC21); HSSFCell cell42 = row4.createCell(2); cell42.setCellValue(arTemp.getDepartmentPage()); cell42.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("C4:E4")); // Header Table HSSFCellStyle styleHeader = wb.createCellStyle(); styleHeader.setFont(excelFunction.getHeaderTable(wb.createFont())); styleHeader.setAlignment(styleHeader.ALIGN_CENTER); styleHeader.setBorderTop(styleHeader.BORDER_THIN); styleHeader.setBorderLeft(styleHeader.BORDER_THIN); styleHeader.setBorderBottom(styleHeader.BORDER_THIN); styleHeader.setBorderRight(styleHeader.BORDER_THIN); HSSFCellStyle styleDetailTable = wb.createCellStyle(); styleDetailTable.setAlignment(styleDetailTable.ALIGN_LEFT); styleDetailTable.setBorderLeft(styleDetailTable.BORDER_THIN); styleDetailTable.setBorderRight(styleDetailTable.BORDER_THIN); HSSFCellStyle styleDetailTableNumber = wb.createCellStyle(); styleDetailTableNumber.setAlignment(styleDetailTableNumber.ALIGN_RIGHT); styleDetailTableNumber.setBorderLeft(styleDetailTableNumber.BORDER_THIN); styleDetailTableNumber.setBorderRight(styleDetailTableNumber.BORDER_THIN); styleDetailTableNumber.setDataFormat(currency.getFormat("#,##0.00")); HSSFCellStyle styleDetailTableBorderBottom = wb.createCellStyle(); styleDetailTableBorderBottom.setBorderTop(styleDetailTableBorderBottom.BORDER_THIN); HSSFRow row6 = sheet.createRow(8); HSSFCell cell61 = row6.createCell(1); cell61.setCellValue("No."); cell61.setCellStyle(styleHeader); sheet.autoSizeColumn(1); HSSFCell cell62 = row6.createCell(2); cell62.setCellValue("Inv No"); cell62.setCellStyle(styleHeader); sheet.autoSizeColumn(2); HSSFCell cell63 = row6.createCell(3); cell63.setCellValue("AR Code"); cell63.setCellStyle(styleHeader); sheet.autoSizeColumn(3); HSSFCell cell64 = row6.createCell(4); cell64.setCellValue("Inv To"); cell64.setCellStyle(styleHeader); sheet.autoSizeColumn(4); HSSFCell cell65 = row6.createCell(5); cell65.setCellValue("Acc Code"); cell65.setCellStyle(styleHeader); sheet.autoSizeColumn(5); HSSFCell cell66 = row6.createCell(6); cell66.setCellValue("Gross"); cell66.setCellStyle(styleHeader); sheet.autoSizeColumn(6); HSSFCell cell67 = row6.createCell(7); cell67.setCellValue("Amount"); cell67.setCellStyle(styleHeader); sheet.autoSizeColumn(7); HSSFCell cell68 = row6.createCell(8); cell68.setCellValue("Cur"); cell68.setCellStyle(styleHeader); sheet.autoSizeColumn(8); int count = 9 + listAR.size(); int start = 11; int end = 0; int num = 0; for (int r = 9; r < count; r++) { if (num <= (listAR.size() - 1)) { HSSFRow row = sheet.createRow(r); HSSFCell cell5 = row.createCell(1); cell5.setCellValue((num + 1)); cell5.setCellStyle(styleDetailTable); sheet.autoSizeColumn(1); HSSFCell cell6 = row.createCell(2); cell6.setCellValue(listAR.get(num).getIntreference()); cell6.setCellStyle(styleDetailTable); sheet.autoSizeColumn(2); HSSFCell cell7 = row.createCell(3); if (listAR.get(num).getCustomerid() != null) { cell7.setCellValue(listAR.get(num).getCustomerid()); sheet.autoSizeColumn(3); } else { cell7.setCellValue(""); sheet.autoSizeColumn(3); } cell7.setCellStyle(styleDetailTable); sheet.autoSizeColumn(3); HSSFCell cell8 = row.createCell(4); if (listAR.get(num).getCustomername() != null) { cell8.setCellValue(listAR.get(num).getCustomername()); sheet.autoSizeColumn(4); } else { cell8.setCellValue(""); sheet.autoSizeColumn(4); } cell8.setCellStyle(styleDetailTable); sheet.autoSizeColumn(4); HSSFCell cell9 = row.createCell(5); if (listAR.get(num).getSalesaccount1() != null) { cell9.setCellValue(listAR.get(num).getSalesaccount1()); sheet.autoSizeColumn(5); } else { cell9.setCellValue(""); sheet.autoSizeColumn(5); } cell9.setCellStyle(styleDetailTable); sheet.autoSizeColumn(5); HSSFCell cell10 = row.createCell(6); if (listAR.get(num).getSalesamt() != null) { cell10.setCellValue(listAR.get(num).getSalesamt().doubleValue()); sheet.autoSizeColumn(6); } else { cell10.setCellValue(0.00); sheet.autoSizeColumn(6); } cell10.setCellStyle(styleDetailTableNumber); sheet.autoSizeColumn(6); HSSFCell cell11 = row.createCell(7); if (listAR.get(num).getAramt() != null) { cell11.setCellValue(listAR.get(num).getAramt().doubleValue()); sheet.autoSizeColumn(7); } else { cell11.setCellValue(0.00); sheet.autoSizeColumn(7); } cell11.setCellStyle(styleDetailTableNumber); HSSFCell cell12 = row.createCell(8); cell12.setCellValue(listAR.get(num).getCurrencyid()); cell12.setCellStyle(styleDetailTable); sheet.autoSizeColumn(8); num++; } } HSSFRow row = sheet.createRow(count); row.createCell(1).setCellStyle(styleDetailTableBorderBottom); row.createCell(2).setCellStyle(styleDetailTableBorderBottom); row.createCell(3).setCellStyle(styleDetailTableBorderBottom); row.createCell(4).setCellStyle(styleDetailTableBorderBottom); row.createCell(5).setCellStyle(styleDetailTableBorderBottom); row.createCell(6).setCellStyle(styleDetailTableBorderBottom); row.createCell(7).setCellStyle(styleDetailTableBorderBottom); row.createCell(8).setCellStyle(styleDetailTableBorderBottom); }