List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook setSheetOrder
@Override public void setSheetOrder(String sheetname, int pos)
From source file:com.pureinfo.srm.reports.table.data.pinggu.ExcelExporterMultiSheetImpl.java
License:Open Source License
public void export(HSSFWorkbook workbook, IExportGoods _goods) throws PureException { // 1. to create sheet HSSFSheet sheet = workbook.createSheet(_goods.getName()); workbook.setSheetOrder(_goods.getName(), 0); // 2. to export headers if (_goods.hasHeader()) { HSSFCellStyle headerStyle = getHeaderStyle(workbook); exportHeaders(sheet, _goods.getHeaders(), headerStyle); }/* www .j a va 2 s . com*/ // 3. to export data Object[] values; HSSFCellStyle dateStyle = getDateStyle(workbook); int nRowNum = 1; Iterator iter = _goods.iterator(); while (iter.hasNext()) { values = _goods.unpackGoods(iter.next()); exportRow(sheet, values, nRowNum++, dateStyle); } }
From source file:com.selfsoft.business.service.impl.TbBusinessBalanceServiceImpl.java
public void printTbBusinessBalanceTemplate(OutputStream os, String tpl, Long id, String companyName) { // ??/* ww w .j a v a 2s. co 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:org.eclipse.titanium.markers.export.ExportedProblemMerger.java
License:Open Source License
/** * Creates a workbook with the output file given in the constructor. * /*w ww . ja v a2 s . com*/ * @return A new workbook on the outfile */ private HSSFWorkbook createWorkbook() { HSSFWorkbook workbook = null; try { final InputStream in = ExportedProblemMerger.class.getResourceAsStream("ProblemMarkers.xlt"); if (in == null) { if (!outfile.exists()) { outfile.createNewFile(); } workbook = new HSSFWorkbook(); workbook.createSheet("Summary"); workbook.setSheetOrder("Summary", 0); } else { workbook = new HSSFWorkbook(new POIFSFileSystem(in), true); in.close(); } } catch (Exception e) { ErrorReporter.logExceptionStackTrace("Error while creating merged excel", e); } return workbook; }
From source file:org.eclipse.titanium.markers.export.XlsProblemExporter.java
License:Open Source License
/** * Export the code smells of a project to an excel workbook. * <p>/*from w ww . ja v a 2 s . com*/ * The first sheet of the workbook is a summary page, showing the number of * hits for each code smell, and an expressive bar chart of these data. The * further sheets enumerate the specific code smells of each kind, including * the message of the code smell, and the file name and line where it * occurred. * <p> * Note: All code smell types are used in the analysis and are written in * the output. Some code smells use external settings, which can be fine * tuned on the preference page. * * @param filename * the file to save the xls * @param date * the time stamp to write on the summary page * * @throws IOException * when writing the file fails */ @Override // Flow analysis thinks 'sheet' may be referenced as null, but it is // guaranteed to be initialized first. public void exportMarkers(final IProgressMonitor monitor, final String filename, final Date date) throws IOException { final SubMonitor progress = SubMonitor.convert(monitor, 100); final File file = new File(filename); POIFSFileSystem fs = null; HSSFWorkbook workbook = null; try { fs = new POIFSFileSystem(XlsProblemExporter.class.getResourceAsStream("ProblemMarkers.xlt")); workbook = new HSSFWorkbook(fs, true); } catch (IOException e) { ErrorReporter.logExceptionStackTrace("Error while exporting to excel", e); // Error on opening the template xls. Create an empty // one (without the chart). if (reportDebugInformation) { TITANDebugConsole.println("Error on opening ProblemMarkers.xlt. Chartless xls will be generated"); } workbook = new HSSFWorkbook(new FileInputStream(file)); workbook.createSheet("Summary"); workbook.setSheetOrder("Summary", 0); } catch (Exception e) { ErrorReporter.logExceptionStackTrace("Error while exporting to excel", e); return; } progress.worked(10); try { final HSSFSheet summarySheet = workbook.getSheetAt(0); createTimeSheet(workbook); final Map<String, Integer> smellCount = new HashMap<String, Integer>(); int summaryRow = 4; Cell label = null; Cell numberCell = null; final Map<TaskType, List<IMarker>> markers = collectMarkers(); // export the task markers: for (final TaskType t : TaskType.values()) { createTaskSheet(workbook, t, markers.get(t)); final Row row1 = summarySheet.createRow(summaryRow++); label = row1.createCell(0); label.setCellValue(t.getHumanReadableName()); final int nofMarkers = markers.get(t).size(); numberCell = row1.createCell(1); numberCell.setCellValue(nofMarkers); // row-1 is the number of found markers smellCount.put(t.name(), nofMarkers); } progress.worked(20); final MarkerHandler mh = AnalyzerCache.withAll().analyzeProject(progress.newChild(30), project); progress.setWorkRemaining(CodeSmellType.values().length + 1); // export the semantic problem markers: for (final CodeSmellType t : CodeSmellType.values()) { createCodeSmellSheet(workbook, mh, t); final Row row1 = summarySheet.createRow(summaryRow++); label = row1.createCell(0); label.setCellValue(t.getHumanReadableName()); smellCount.put(t.name(), mh.numberOfOccurrences(t)); numberCell = row1.createCell(1); numberCell.setCellValue(mh.numberOfOccurrences(t)); progress.worked(1); } final Row row0 = summarySheet.createRow(0); row0.createCell(0).setCellValue("Project: " + project.getName()); final Row row1 = summarySheet.createRow(1); row1.createCell(0).setCellValue("Code smell \\ date"); final CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("yyyy.mm.dd")); label = row1.createCell(1); label.setCellValue(date); label.setCellStyle(cellStyle); final Row row2 = summarySheet.createRow(2); row2.createCell(0).setCellValue("Commulative Project Risk Factor"); final int riskFactor = new RiskFactorCalculator().measure(project, smellCount); row2.createCell(1).setCellValue(riskFactor); summarySheet.autoSizeColumn(0); summarySheet.autoSizeColumn(1); progress.worked(1); } catch (Exception e) { ErrorReporter.logExceptionStackTrace("Error while exporting to excel", e); } finally { FileOutputStream fileOutputStream = null; try { fileOutputStream = new FileOutputStream(file); workbook.write(fileOutputStream); } catch (Exception e) { ErrorReporter.logExceptionStackTrace("Error while closing the generated excel", e); } finally { IOUtils.closeQuietly(fileOutputStream); } } }
From source file:org.eclipse.titanium.markers.export.XlsProblemExporter.java
License:Open Source License
/** * Create the summary sheet in the exported document. * /*from w w w . ja va 2s . co m*/ * @param workbook the workbook to work in. * */ private void createTimeSheet(final HSSFWorkbook workbook) { final HSSFSheet timeSheet = workbook.createSheet("Repair times"); workbook.setSheetOrder("Repair times", 1); final Row headerRow = timeSheet.createRow(0); headerRow.createCell(1).setCellValue("Minimal repair time"); headerRow.createCell(2).setCellValue("Average repair time"); headerRow.createCell(3).setCellValue("Maximal repair time"); int summaryRow = 4; Cell label; for (final TaskType t : TaskType.values()) { final Row row2 = timeSheet.createRow(summaryRow); label = row2.createCell(0); label.setCellValue(t.getHumanReadableName()); final Cell minTimeCell = row2.createCell(1); minTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA); minTimeCell.setCellFormula(t.getMinRepairTime() + "*Summary!$B" + (summaryRow + 1)); final Cell avgTimeCell = row2.createCell(2); avgTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA); avgTimeCell.setCellFormula(t.getAvgRepairTime() + "*Summary!$B" + (summaryRow + 1)); final Cell maxTimeCell = row2.createCell(3); maxTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA); maxTimeCell.setCellFormula(t.getMaxRepairTime() + "*Summary!$B" + (++summaryRow)); } for (final CodeSmellType t : CodeSmellType.values()) { final Row row2 = timeSheet.createRow(summaryRow); label = row2.createCell(0); label.setCellValue(t.getHumanReadableName()); final Cell minTimeCell = row2.createCell(1); minTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA); minTimeCell.setCellFormula(t.getMinRepairTime() + "*Summary!$B" + (summaryRow + 1)); final Cell avgTimeCell = row2.createCell(2); avgTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA); avgTimeCell.setCellFormula(t.getAvgRepairTime() + "*Summary!$B" + (summaryRow + 1)); final Cell maxTimeCell = row2.createCell(3); maxTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA); maxTimeCell.setCellFormula(t.getMaxRepairTime() + "*Summary!$B" + (++summaryRow)); } final Row totalRow = timeSheet.createRow(1); totalRow.createCell(0).setCellValue("Total"); final Cell cell1 = totalRow.createCell(1); cell1.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell1.setCellFormula("SUM($B4:$B" + summaryRow + ")"); final Cell cell2 = totalRow.createCell(2); cell2.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell2.setCellFormula("SUM($C4:$C" + summaryRow + ")"); final Cell cell3 = totalRow.createCell(3); cell3.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell3.setCellFormula("SUM($D4:$D" + summaryRow + ")"); timeSheet.autoSizeColumn(0); timeSheet.autoSizeColumn(1); timeSheet.autoSizeColumn(2); timeSheet.autoSizeColumn(3); }