List of usage examples for org.apache.poi.hssf.usermodel HSSFCell setHyperlink
@Override public void setHyperlink(Hyperlink hyperlink)
From source file:data.services.EventService.java
public void saveLog(Long evId, List<EntityCar> carList, List<IdealEntityParam> ieplist, List<EntityCar> cutCarList, Sequence seq) throws Exception { Logger log = Logger.getLogger(this.getClass()); //Event ev = eventDao.find(evId); try {//from w ww.ja v a 2 s .co m HashSet<String> iepuids = new HashSet(); for (IdealEntityParam iep : ieplist) { iepuids.add(iep.getBaseParam().getUid()); } List<BaseParam> bpList = baseParamService.getParams(); HashMap<Long, String> genMap = subModelService.getGenerationUrls(); HSSFWorkbook workbook = new HSSFWorkbook(); Event ev = eventDao.find(evId); HashMap<Long, SubModel> bodyMap = subModelService.getBodies(); HSSFCellStyle style = workbook.createCellStyle(); //style.setFillBackgroundColor(HSSFColor.ORANGE.index); style.setFillForegroundColor(HSSFColor.ORANGE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFSheet idealEntitySheet = workbook.createSheet("? ??"); HSSFSheet chosenSheet = workbook.createSheet(" ?"); HSSFSheet cutedSheet = workbook.createSheet(" ?"); HSSFSheet moneySheet = workbook.createSheet(" "); HSSFSheet instChosenSheet = workbook.createSheet(""); int complectList = 1; int ierownum = 1; idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getName() + "; " + ev.getId() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue("?: " + ev.getAuthor() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getAddDate() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getScene().getName() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getMoney() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(" ???: " + ev.getPerception().getName() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getRadical() + "(" + ev.getColor() + ")" + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue("?: " + seq.getRadCore() + ";"); HSSFRow ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? A1: "); ierowhead.createCell(1).setCellValue(ev.getStrategyA1().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? B1: "); ierowhead.createCell(1).setCellValue(ev.getStrategyB1().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? : "); ierowhead.createCell(1).setCellValue(ev.getResourceType().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? -: "); ierowhead.createCell(1).setCellValue(ev.getParamTox1().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? ?: "); ierowhead.createCell(1).setCellValue(ev.getValueTox1().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue(" ?: "); ierowhead.createCell(1).setCellValue(seq.getAllowance() + "%"); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? A2: "); ierowhead.createCell(1).setCellValue(ev.getStrategyA2().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? B2: "); ierowhead.createCell(1).setCellValue(ev.getStrategyB2().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? -, . ?: "); ierowhead.createCell(1).setCellValue(ev.getParamTox2().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? ?, . ?: "); ierowhead.createCell(1).setCellValue(ev.getValueTox2().getName()); ierownum++; int phn = 1; HSSFRow paramsierowhead = idealEntitySheet.createRow(ierownum++); paramsierowhead.createCell(phn++).setCellValue("UID"); paramsierowhead.createCell(phn++).setCellValue("?"); paramsierowhead.createCell(phn++).setCellValue("?"); paramsierowhead.createCell(phn++).setCellValue(" ."); paramsierowhead.createCell(phn++).setCellValue(""); paramsierowhead.createCell(phn++).setCellValue("?"); paramsierowhead.createCell(phn++).setCellValue(" ?."); paramsierowhead.createCell(phn++).setCellValue(""); int A = 0; int B = 0; for (IdealEntityParam iep : ieplist) { int pbn = 0; HSSFRow paramsierowbody = idealEntitySheet.createRow(ierownum++); paramsierowbody.createCell(pbn++).setCellValue(iep.getNumber()); paramsierowbody.createCell(pbn++).setCellValue(iep.getBaseParam().getUid()); paramsierowbody.createCell(pbn++).setCellValue(iep.getBaseParam().getName()); paramsierowbody.createCell(pbn++).setCellValue(iep.getIepClass().getName()); paramsierowbody.createCell(pbn++).setCellValue(iep.getAmin().doubleValue()); paramsierowbody.createCell(pbn++).setCellValue(iep.getValueMin().doubleValue()); paramsierowbody.createCell(pbn++).setCellValue(iep.getValueMax().doubleValue()); paramsierowbody.createCell(pbn++).setCellValue(iep.getAmax().doubleValue()); paramsierowbody.createCell(pbn++).setCellValue(StringAdapter.getString(iep.getRank())); if (Rank.A.equals(iep.getRank())) { A++; } else if (Rank.B.equals(iep.getRank())) { B++; } } ierownum++; ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? - ?:"); ierowhead.createCell(1).setCellValue((double) A); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? - B"); ierowhead.createCell(1).setCellValue((double) B); ierownum++; ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? :"); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(1).setCellValue("UID"); ierowhead.createCell(2).setCellValue("?"); for (SequenceValueRange svr : seq.getSequenceValueRanges()) { String uid = svr.getUid(); if (!iepuids.contains(uid)) { ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(1).setCellValue(svr.getUid()); ierowhead.createCell(2).setCellValue(baseParamService.getBaseParam(bpList, uid).getName()); } } int rowheadNumCell = 0; HSSFRow rowhead = chosenSheet.createRow(0); rowhead.createCell(rowheadNumCell++).setCellValue(""); rowhead.createCell(rowheadNumCell++).setCellValue("?"); rowhead.createCell(rowheadNumCell++).setCellValue(""); rowhead.createCell(rowheadNumCell++).setCellValue("?"); rowhead.createCell(rowheadNumCell++).setCellValue("?"); rowhead.createCell(rowheadNumCell++).setCellValue(" ?"); rowhead.createCell(rowheadNumCell++).setCellValue("??"); rowhead.createCell(rowheadNumCell++).setCellValue("?? ??"); rowhead.createCell(rowheadNumCell++).setCellValue("? A"); rowhead.createCell(rowheadNumCell++).setCellValue(" A"); rowhead.createCell(rowheadNumCell++).setCellValue("? ? A "); rowhead.createCell(rowheadNumCell++).setCellValue("? B"); rowhead.createCell(rowheadNumCell++).setCellValue(" "); rowhead.createCell(rowheadNumCell++).setCellValue("? ? B"); rowhead.createCell(rowheadNumCell++).setCellValue("? ???"); rowhead.createCell(rowheadNumCell++).setCellValue("? ?? ??"); rowhead.createCell(rowheadNumCell++).setCellValue("? ?? ???"); rowhead.createCell(rowheadNumCell++).setCellValue("? ?? ???"); int numscell = 0; HSSFRow instrowhead = instChosenSheet.createRow(0); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue(" ?"); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue("??? 1"); instrowhead.createCell(numscell++).setCellValue("??? 2"); instrowhead.createCell(numscell++).setCellValue("? ???"); instrowhead.createCell(numscell++).setCellValue("? ??"); moneySheet.addMergedRegion(new CellRangeAddress(0, 0, 6, 13)); moneySheet.addMergedRegion(new CellRangeAddress(0, 0, 14, 18)); moneySheet.addMergedRegion(new CellRangeAddress(0, 0, 19, 26)); moneySheet.addMergedRegion(new CellRangeAddress(0, 0, 27, 32)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5)); HSSFRow firstRowMoneyHead = moneySheet.createRow(0); HSSFRow secondRowMoneyHead = moneySheet.createRow(1); int frn = 0; firstRowMoneyHead.createCell(frn++).setCellValue(""); firstRowMoneyHead.createCell(frn++).setCellValue("?"); firstRowMoneyHead.createCell(frn++).setCellValue(""); firstRowMoneyHead.createCell(frn++).setCellValue("?"); firstRowMoneyHead.createCell(frn++).setCellValue("?"); firstRowMoneyHead.createCell(frn++).setCellValue(" ?"); firstRowMoneyHead.createCell(frn++).setCellValue("??? I"); firstRowMoneyHead.createCell(frn + 7).setCellValue("?? I"); firstRowMoneyHead.createCell(frn + 12).setCellValue("??? II"); firstRowMoneyHead.createCell(frn + 20).setCellValue("?? II"); int srn = 6; secondRowMoneyHead.createCell(srn++) .setCellValue("- ?? ??? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue("- ?? ??? "); secondRowMoneyHead.createCell(srn++).setCellValue(" ?"); secondRowMoneyHead.createCell(srn++) .setCellValue(" ?? ?? ??? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue(" ?? ?? ??? "); secondRowMoneyHead.createCell(srn++).setCellValue("? ??"); secondRowMoneyHead.createCell(srn++).setCellValue("? ?"); secondRowMoneyHead.createCell(srn++).setCellValue("? ??"); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? "); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? ???"); secondRowMoneyHead.createCell(srn++).setCellValue("- ?? "); secondRowMoneyHead.createCell(srn++).setCellValue(""); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue("- ?? ??? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue("- ?? ??? "); secondRowMoneyHead.createCell(srn++).setCellValue(" ?"); secondRowMoneyHead.createCell(srn++) .setCellValue(" ?? ?? ??? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue(" ?? ?? ??? "); secondRowMoneyHead.createCell(srn++).setCellValue("? ??"); secondRowMoneyHead.createCell(srn++).setCellValue("? ?"); secondRowMoneyHead.createCell(srn++).setCellValue("? ??"); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? "); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? ???"); secondRowMoneyHead.createCell(srn++).setCellValue("- ?? "); secondRowMoneyHead.createCell(srn++).setCellValue(""); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? ?"); int n = 1; int optrownum = 1; if (!carList.isEmpty()) { //while (optrownum < 50000) { for (EntityCar car : carList) { if (optrownum > 60000) { instChosenSheet = workbook.createSheet(" " + complectList++); optrownum = 1; numscell = 0; instrowhead = instChosenSheet.createRow(0); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue(" ?"); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue("??? 1"); instrowhead.createCell(numscell++).setCellValue("??? 2"); instrowhead.createCell(numscell++).setCellValue("? ???"); instrowhead.createCell(numscell++).setCellValue("? ??"); } Car realCar = car.car; SubModel sm = bodyMap.get(car.car.getCmsqId()); String body = ""; if (sm != null) { body = sm.getBody(); } int m = n + 1; int bpropsCount = 0; int cn = 0; HSSFRow carRow = instChosenSheet.createRow(optrownum); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.car.getCarId())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.getMarkTitle())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.getModelTitle())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.getCarTitle())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(body)); carRow.createCell(cn++).setCellValue(car.fullPrice.doubleValue()); carRow.createCell(cn++).setCellValue(""); carRow.createCell(cn + 6).setCellValue(car.dinamicRateA.add(car.dinamicRateB).doubleValue()); optrownum++; /*carRow.createCell(3).setCellValue(renderDecimalNoPoint(car.basePrice).replace(".", ",")); carRow.createCell(6).setCellValue(StringAdapter.getString(car.staticRate).replace(".", ",")); carRow.createCell(7).setCellValue(StringAdapter.getString(car.staticRate.add(car.dinamicRate)).replace(".", ","));*/ for (String uid : car.aprops.keySet()) { EntityPropertyHolder eph = car.aprops.get(uid); boolean alternatives = false; List<EntityProperty> insttalledElems = eph.getActiveProperty(); if (insttalledElems.size() > 1) { alternatives = true; } for (EntityProperty ep : insttalledElems) { HSSFRow optRow = instChosenSheet.createRow(optrownum); int on = 0; optRow.createCell(on++).setCellValue(StringAdapter.getString(car.car.getCarId())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getMarkTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getModelTitle())); optRow.createCell(on++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getCarTitle())); optRow.createCell(on++).setCellValue(body); //optRow.createCell(on++).setCellValue(RenderSupport.renderDecimalNoPoint(ep.getPrice()).replace(".", ",")); optRow.createCell(on++).setCellValue(" - "); HSSFCell uidCell = optRow.createCell(on++); uidCell.setCellValue(uid); if (alternatives) { uidCell.setCellStyle(style); } optRow.createCell(on++).setCellValue("A"); optRow.createCell(on++).setCellValue(ep.sign); optRow.createCell(on++).setCellValue(ep.type.getName()); optRow.createCell(on++).setCellValue(ep.name); optRow.createCell(on++).setCellValue(ep.title); optRow.createCell(on++).setCellValue(ep.value.doubleValue()); optRow.createCell(on++).setCellValue(eph.dinamicRate.doubleValue()); optrownum++; /*optRow.createCell(1).setCellValue("A"); optRow.createCell(2).setCellValue(ep.type.getName()); optRow.createCell(3).setCellValue(ep.name); optRow.createCell(4).setCellValue(ep.title); optRow.createCell(5).setCellValue(StringAdapter.getString(ep.value).replace(".", ",")); optRow.createCell(6).setCellValue(StringAdapter.getString(eph.dinamicRate).replace(".", ",")); optRow.createCell(7).setCellValue(StringAdapter.getString(eph.staticRate).replace(".", ","));*/ } } for (String uid : car.bprops.keySet()) { EntityPropertyHolder eph = car.bprops.get(uid); boolean alternatives = false; List<EntityProperty> insttalledElems = eph.getActiveProperty(); if (insttalledElems.size() > 1) { alternatives = true; } if (!insttalledElems.isEmpty()) { bpropsCount++; for (EntityProperty ep : eph.getActiveProperty()) { HSSFRow optRow = instChosenSheet.createRow(optrownum); int on = 0; optRow.createCell(on++).setCellValue(StringAdapter.getString(car.car.getCarId())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getMarkTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getModelTitle())); optRow.createCell(on++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getCarTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(body)); //optRow.createCell(on++).setCellValue(RenderSupport.renderDecimalNoPoint(ep.getPrice()).replace(".", ",")); //optRow.createCell(on++).setCellValue(RenderSupport.renderDecimalNoPoint(ep.price)); optRow.createCell(on++).setCellValue(" - "); HSSFCell uidCell = optRow.createCell(on++); uidCell.setCellValue(uid); if (alternatives) { uidCell.setCellStyle(style); } optRow.createCell(on++).setCellValue("B"); optRow.createCell(on++).setCellValue(ep.sign); optRow.createCell(on++).setCellValue(ep.type.getName()); optRow.createCell(on++).setCellValue(ep.name); optRow.createCell(on++).setCellValue(ep.title); optRow.createCell(on++).setCellValue(ep.value.doubleValue()); optRow.createCell(on++).setCellValue(eph.dinamicRate.doubleValue()); optrownum++; /*HSSFRow optRow = instChosenSheet.createRow((short) optrownum); HSSFCell uidCell = optRow.createCell(0); uidCell.setCellValue(uid); if(alternatives){ //optRow.createCell(8).setCellValue(""); uidCell.setCellStyle(style); } optRow.createCell(1).setCellValue("B"); optRow.createCell(2).setCellValue(ep.type.getName()); optRow.createCell(3).setCellValue(ep.name); optRow.createCell(4).setCellValue(ep.title); optRow.createCell(5).setCellValue(StringAdapter.getString(ep.value).replace(".", ",")); optRow.createCell(6).setCellValue(StringAdapter.getString(eph.dinamicRate).replace(".", ",")); optRow.createCell(7).setCellValue(StringAdapter.getString(eph.staticRate).replace(".", ",")); optrownum++;*/ } } else { BaseParam bp = baseParamService.getBaseParam(uid); HSSFRow optRow = instChosenSheet.createRow(optrownum); int on = 0; optRow.createCell(on++).setCellValue(StringAdapter.getString(car.car.getCarId())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getMarkTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getModelTitle())); optRow.createCell(on++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getCarTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(body)); //optRow.createCell(on++).setCellValue(RenderSupport.renderDecimalNoPoint(ep.getPrice()).replace(".", ",")); optRow.createCell(on++).setCellValue(" - "); HSSFCell uidCell = optRow.createCell(on++); uidCell.setCellValue(uid); optRow.createCell(on++).setCellValue("B"); optRow.createCell(on++).setCellValue(" - "); optRow.createCell(on++).setCellValue(" - "); optRow.createCell(on++).setCellValue(bp.getName()); optRow.createCell(on++).setCellValue(" - "); optRow.createCell(on++).setCellValue("null"); optRow.createCell(on++).setCellValue(" - "); optrownum++; } } /*String aRate = " - "; if(car.dinamicRateA.compareTo(BigDecimal.valueOf(0))>0){ aRate = StringAdapter.getString(car.dinamicRateA).replace(".", ","); } String acount = " - "; if(car.aprops.keySet().size()>0){ acount = StringAdapter.getString(car.aprops.keySet().size()); }*/ String msA = " - "; BigDecimal medA = BigDecimal.valueOf(0); if (car.aprops.keySet().size() > 0) { medA = car.dinamicRateA.divide(BigDecimal.valueOf(car.aprops.keySet().size()), 2, RoundingMode.HALF_UP); msA = StringAdapter.getString(medA).replace(".", ","); } /* String bRate = " - "; if(car.dinamicRateB.compareTo(BigDecimal.valueOf(0))>0){ bRate = StringAdapter.getString(car.dinamicRateB).replace(".", ","); }*/ String bcount = " - "; if (bpropsCount > 0) { bcount = StringAdapter.getString(bpropsCount); } String msB = " - "; BigDecimal medB = BigDecimal.valueOf(0); if (bpropsCount > 0) { medB = car.dinamicRateB.divide(BigDecimal.valueOf(bpropsCount), 2, RoundingMode.HALF_UP); msB = StringAdapter.getString(medB).replace(".", ","); } /*String bsr = " - "; if(car.baseStaticRate2.compareTo(BigDecimal.valueOf(0))>0){ bsr = StringAdapter.getString(car.baseStaticRate2).replace(".", ","); } String fsr = " - "; if(car.freeStaticRate2.compareTo(BigDecimal.valueOf(0))>0){ fsr = StringAdapter.getString(car.freeStaticRate2).replace(".", ","); } String msr = " - "; if(car.monetaryStaticRate2.compareTo(BigDecimal.valueOf(0))>0){ msr = StringAdapter.getString(car.monetaryStaticRate2).replace(".", ","); }*/ Model model = realCar.getModel(); HSSFRow rowbody = chosenSheet.createRow(n); int rowbodynum = 0; rowbody.createCell(rowbodynum++).setCellValue(StringAdapter.getString(car.car.getCarId())); rowbody.createCell(rowbodynum++).setCellValue(StringAdapter.getString(car.getMarkTitle())); rowbody.createCell(rowbodynum++).setCellValue(StringAdapter.getString(car.getModelTitle())); rowbody.createCell(rowbodynum++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); HSSFCell nameCellWithLink = rowbody.createCell(rowbodynum++); nameCellWithLink.setCellValue(StringAdapter.getString(car.getCarTitle())); HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); link.setAddress("http://quto.ru/" + model.getMark().getUrl() + "/" + model.getUrl() + "/" + genMap.get(realCar.getCmgqId()) + "/" + bodyMap.get(realCar.getCmsqId()).getUrl() + "/" + realCar.getUrl()); nameCellWithLink.setHyperlink(link); rowbody.createCell(rowbodynum++).setCellValue(StringAdapter.getString(body)); rowbody.createCell(rowbodynum++).setCellValue(car.basePrice.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.fullPrice.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.dinamicRateA.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue((double) car.aprops.keySet().size()); rowbody.createCell(rowbodynum++).setCellValue(medA.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.dinamicRateB.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue((double) bpropsCount); rowbody.createCell(rowbodynum++).setCellValue(medB.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(medA.add(medB).doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.baseStaticRate2.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.freeStaticRate2.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.monetaryStaticRate2.doubleValue()); int rmbn = 0; HSSFRow rowMoneyBody = moneySheet.createRow(m); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(car.car.getId())); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(car.getMarkTitle())); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(car.getModelTitle())); rowMoneyBody.createCell(rmbn++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(car.getCarTitle())); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(body)); rowMoneyBody.createCell(rmbn++).setCellValue((double) car.aprops.keySet().size()); rowMoneyBody.createCell(rmbn++).setCellValue((double) bpropsCount); rowMoneyBody.createCell(rmbn++).setCellValue(car.basePrice.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.aFund1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.bFund1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.aFund1.add(car.bFund1).doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue( ev.getMoney().subtract(car.aFund1.add(car.bFund1).add(car.basePrice)).doubleValue()); rowMoneyBody.createCell(rmbn++) .setCellValue(car.dinamicRateA.add(car.dinamicRateB).doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.baseStaticRate1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.freeStaticRate1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.payStaticCount1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.staticFund1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.monetaryStaticRate1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue((double) car.aCount2); rowMoneyBody.createCell(rmbn++).setCellValue((double) car.bCount2); rowMoneyBody.createCell(rmbn++).setCellValue(car.basePrice.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.aFund2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.bFund2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.aFund2.add(car.bFund2).doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue( ev.getMoney().subtract(car.aFund2.add(car.bFund2).add(car.basePrice)).doubleValue()); rowMoneyBody.createCell(rmbn++) .setCellValue(car.dinamicRateA.add(car.dinamicRateB).doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.baseStaticRate2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.freeStaticRate2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.payStaticCount2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.staticFund2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.monetaryStaticRate2.doubleValue()); n++; HSSFRow carStaticRowHead = instChosenSheet.createRow(optrownum); int csn = 0; carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(car.car.getCarId())); carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(car.getMarkTitle())); carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(car.getModelTitle())); carStaticRowHead.createCell(csn++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(car.getCarTitle())); carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(body)); //carStaticRowHead.createCell(3).setCellValue(RenderSupport.renderDecimalNoPoint(ep.getPrice())); carStaticRowHead.createCell(csn++).setCellValue(":"); carStaticRowHead.createCell(csn + 7).setCellValue(car.staticRate.doubleValue()); optrownum++; Map<String, List<EntityProperty>> StaticMap = car.staticProps; for (String uid : StaticMap.keySet()) { List<EntityProperty> insttalledElems = StaticMap.get(uid); if (insttalledElems != null && !insttalledElems.isEmpty()) { boolean alternatives = false; if (insttalledElems.size() > 1) { alternatives = true; } for (EntityProperty ep : insttalledElems) { String title = ep.title; if (!ep.description.trim().equals("")) { title += " - " + ep.description; } HSSFRow carStaticRow = instChosenSheet.createRow(optrownum); int cson = 0; carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.car.getCarId())); carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.getMarkTitle())); carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.getModelTitle())); carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.getCarTitle())); carStaticRow.createCell(cson++).setCellValue(StringAdapter.getString(body)); //carStaticRow.createCell(cson++).setCellValue(StringAdapter.getString(ep.getPrice()).replace(".", ",")); carStaticRow.createCell(cson++).setCellValue(" - "); HSSFCell uidCell = carStaticRow.createCell(cson++); uidCell.setCellValue(uid); if (alternatives) { uidCell.setCellStyle(style); } //carStaticRow.createCell(5).setCellValue(uid); carStaticRow.createCell(cson++).setCellValue("S"); carStaticRow.createCell(cson++).setCellValue(ep.sign); carStaticRow.createCell(cson++).setCellValue(ep.type.getName()); carStaticRow.createCell(cson++).setCellValue(ep.name); carStaticRow.createCell(cson++).setCellValue(title); carStaticRow.createCell(cson++).setCellValue(ep.radicalValue); carStaticRow.createCell(cson + 1).setCellValue(ep.value.doubleValue()); /*carStaticRow.createCell(1).setCellValue(ep.name); carStaticRow.createCell(2).setCellValue(ep.title); carStaticRow.createCell(3).setCellValue(ep.type.getName()); carStaticRow.createCell(5).setCellValue(StringAdapter.getString(ep.value).replace(".", ","));*/ optrownum++; } } } /*break;*/ } } int k = 0; HSSFRow rowhead20 = cutedSheet.createRow(k++); HSSFRow rowhead21 = cutedSheet.createRow(k++); HSSFRow rowhead22 = cutedSheet.createRow(k++); int ccn = 0; rowhead20.createCell(0).setCellValue("UID ??"); rowhead21.createCell(ccn++).setCellValue(""); rowhead21.createCell(ccn++).setCellValue("?"); rowhead21.createCell(ccn++).setCellValue(""); rowhead21.createCell(ccn++).setCellValue("?"); rowhead21.createCell(ccn++).setCellValue("?"); rowhead21.createCell(ccn++).setCellValue(" ?"); rowhead22.createCell(0).setCellValue(" ???"); int cutoptrownum = 0; for (IdealEntityParam iep : ieplist) { rowhead20.createCell(ccn).setCellValue(StringAdapter.getString(iep.baseParam.getUid())); rowhead21.createCell(ccn).setCellValue(iep.number + "." + iep.baseParam.getName()); rowhead22.createCell(ccn++).setCellValue(iep.aimBefore.doubleValue()); } if (!cutCarList.isEmpty()) { for (EntityCar car : cutCarList) { SubModel sm = bodyMap.get(car.car.getCmsqId()); String body = ""; if (sm != null) { body = sm.getBody(); } HSSFRow rowbody = cutedSheet.createRow(k++); int con = 0; rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.car.getCarId())); rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.getMarkTitle())); rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.getModelTitle())); rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.getCarTitle())); rowbody.createCell(con).setCellValue(body); int num = 0; if (car.cutNum != null) { num = car.cutNum; } String cutValue = car.cutVal; if (cutValue == null || cutValue.equals("null")) { cutValue = "? "; } try { Double cutedValue = Double.valueOf(car.cutVal); rowbody.createCell(con + num).setCellValue(cutedValue); } catch (Exception e) { rowbody.createCell(con + num).setCellValue(cutValue.replace(".", ",")); } /*HSSFRow carRow = instCutedSheet.createRow(cutoptrownum); carRow.createCell(0).setCellValue(StringAdapter.getString(car.car.getCarId())); carRow.createCell(1).setCellValue(StringAdapter.getString(car.getMarkTitle())); carRow.createCell(2).setCellValue(StringAdapter.getString(car.getModelTitle())); carRow.createCell(3).setCellValue(StringAdapter.getString(car.getCarTitle())); carRow.createCell(4).setCellValue(RenderSupport.renderDecimalNoPoint(car.basePrice).replace(".", ",")); carRow.createCell(5).setCellValue(RenderSupport.renderDecimalNoPoint(car.fixPrice).replace(".", ",")); carRow.createCell(6).setCellValue(StringAdapter.getString(car.dinamicRateA.add(car.dinamicRateB)).replace(".", ",")); carRow.createCell(7).setCellValue(StringAdapter.getString(car.staticRate).replace(".", ",")); carRow.createCell(8).setCellValue(StringAdapter.getString(car.staticRate.add(car.dinamicRateA.add(car.dinamicRateB))).replace(".", ",")); cutoptrownum++; HSSFRow carRowHead = instCutedSheet.createRow(cutoptrownum); carRowHead.createCell(0).setCellValue(StringAdapter.getString("")); carRowHead.createCell(1).setCellValue(StringAdapter.getString("")); carRowHead.createCell(2).setCellValue(StringAdapter.getString("??")); carRowHead.createCell(3).setCellValue(StringAdapter.getString("?????")); carRowHead.createCell(4).setCellValue(StringAdapter.getString("???")); carRowHead.createCell(5).setCellValue(StringAdapter.getString("%")); carRowHead.createCell(6).setCellValue(StringAdapter.getString("??")); cutoptrownum++;*/ /*for (PropertyName pn : car.car.getPropertyNames()) { CarProperty p = pn.getCarProperty(); if (p != null) { HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); if (p.getUid() != null) { optRow.createCell(0).setCellValue(p.getUid()); } else { optRow.createCell(0).setCellValue("? "); } optRow.createCell(1).setCellValue("?"); if (p.getTitle() != null) { optRow.createCell(2).setCellValue(p.getTitle()); } else { optRow.createCell(2).setCellValue("? "); } if (pn.getPropertyNameValue() != null && !pn.getPropertyNameValue().equals("")) { optRow.createCell(3).setCellValue(pn.getPropertyNameValue()); } else { optRow.createCell(3).setCellValue("? "); } if (pn.getParamValue() != null && !pn.getParamValue().equals("")) { optRow.createCell(4).setCellValue(pn.getParamValue()); } else { optRow.createCell(4).setCellValue("? "); } if (pn.getPercentValue() != null && !pn.getPercentValue().equals("")) { optRow.createCell(5).setCellValue(pn.getPercentValue()); } else { optRow.createCell(5).setCellValue("? "); } if (pn.getRadical() != null && !pn.getRadical().equals("")) { optRow.createCell(6).setCellValue(pn.getRadical()); } else { optRow.createCell(6).setCellValue("? "); } cutoptrownum++; } } List<Feature> flist = carService.getFeatures(car.car); for (Feature f : flist) { HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); if (f.getUid() != null) { optRow.createCell(0).setCellValue(f.getUid()); } else { optRow.createCell(0).setCellValue("? "); } optRow.createCell(1).setCellValue("??"); optRow.createCell(2).setCellValue(""); if (f.getTitle() != null) { optRow.createCell(3).setCellValue(f.getTitle()); } else { optRow.createCell(3).setCellValue("? "); } if (f.getParamValue() != null) { optRow.createCell(4).setCellValue(f.getParamValue()); } else { optRow.createCell(4).setCellValue("? "); } if (f.getPercentValue() != null) { optRow.createCell(5).setCellValue(f.getPercentValue()); } else { optRow.createCell(5).setCellValue("? "); } if (f.getRadical() != null) { optRow.createCell(6).setCellValue(f.getRadical()); } else { optRow.createCell(6).setCellValue("? "); } cutoptrownum++; }*/ /*for (CarOptionValue cov : car.car.getCarOptionValues()) { CarCompletionOption cco = cov.getCCO(); if (cco != null) { HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); if (cco.getUid() != null) { optRow.createCell(0).setCellValue(cco.getUid()); } else { optRow.createCell(0).setCellValue("? "); } optRow.createCell(1).setCellValue("?"); if (cco.getTitle() != null) { optRow.createCell(2).setCellValue(cco.getTitle()); } else { optRow.createCell(2).setCellValue("? "); } if (cov.getTitle() != null) { optRow.createCell(3).setCellValue(cov.getTitle()); } else { optRow.createCell(3).setCellValue("? "); } if (cco.getParamValue() != null) { optRow.createCell(4).setCellValue(cco.getParamValue()); } else { optRow.createCell(4).setCellValue("? "); } if (cco.getPercentValue() != null) { optRow.createCell(5).setCellValue(cco.getPercentValue()); } else { optRow.createCell(5).setCellValue("? "); } if (cco.getRadical() != null) { optRow.createCell(6).setCellValue(cco.getRadical()); } else { optRow.createCell(6).setCellValue("? "); } cutoptrownum++; } } for (CarColorValue ccv : car.car.getCarColorValues()) { Color col = ccv.getColor(); if (col != null) { HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); if (col.getUid() != null) { optRow.createCell(0).setCellValue(col.getUid()); } else { optRow.createCell(0).setCellValue("? "); } optRow.createCell(1).setCellValue(""); if (col.getTitle() != null) { optRow.createCell(2).setCellValue(col.getTitle()); } else { optRow.createCell(2).setCellValue("? "); } if (col.getName() != null) { optRow.createCell(3).setCellValue(col.getName()); } else { optRow.createCell(3).setCellValue("? "); } if (col.getParamValue() != null) { optRow.createCell(4).setCellValue(col.getParamValue()); } else { optRow.createCell(4).setCellValue("? "); } if (col.getPercentValue() != null) { optRow.createCell(5).setCellValue(col.getPercentValue()); } else { optRow.createCell(5).setCellValue("? "); } if (col.getRadical() != null) { optRow.createCell(6).setCellValue(col.getRadical()); } else { optRow.createCell(6).setCellValue("? "); } cutoptrownum++; } }*/ /*for(String uid:car.aprops.keySet()){ for(EntityProperty ep:car.aprops.get(uid).getPropList()){ HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); optRow.createCell(0).setCellValue(uid); optRow.createCell(1).setCellValue(ep.name); optRow.createCell(2).setCellValue(ep.title); optRow.createCell(3).setCellValue(ep.type.getName()); optRow.createCell(4).setCellValue(StringAdapter.getString(ep.value)); cutoptrownum++; } } for(String uid:car.bprops.keySet()){ for(EntityProperty ep:car.bprops.get(uid).getPropList()){ HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); optRow.createCell(0).setCellValue(uid); optRow.createCell(1).setCellValue(ep.name); optRow.createCell(2).setCellValue(ep.title); optRow.createCell(3).setCellValue(ep.type.getName()); optRow.createCell(4).setCellValue(StringAdapter.getString(ep.value)); cutoptrownum++; } }*/ } } File f = new File("/usr/local/etc/xls/" + evId + ".xls"); if (f.exists()) { f.delete(); } workbook.write(new FileOutputStream("/usr/local/etc/xls/" + evId + ".xls")); } catch (Exception e) { //log.warn("HSSFWorkbook.getXls()", e); throw new Exception(e); } }
From source file:edu.jhu.cvrg.services.nodeDataService.DataStaging.java
License:Open Source License
private HSSFSheet consolidateAlgorithmFiles(int algorithmCode, HSSFSheet sheet, String subjectIds, String userId, String fileNames, boolean isPublic, ApacheCommonsFtpWrapper ftpClient) { boolean firstToken = true; StringTokenizer tokenizer = new StringTokenizer(subjectIds, "^"); StringTokenizer fileTokenizer = new StringTokenizer(fileNames, "^"); String currentSubject = null; int lineNumber = 0; String headerIndicator = null; switch (algorithmCode) { case 0://from www.j a va 2 s.c om headerIndicator = CHESNOKOV_HEADER_INDICATOR; break; case 1: headerIndicator = BERGER_HEADER_INDICATOR; break; } String directory = localFtpRoot + sep + userId + utils.generateTimeStamp(); while (tokenizer.hasMoreTokens() && fileTokenizer.hasMoreTokens()) { currentSubject = tokenizer.nextToken(); System.out.println(currentSubject); String file = fileTokenizer.nextToken(); String bareFile = file.substring(file.lastIndexOf("/") + 1); try { ftpClient.downloadFile(file, directory + bareFile); FileReader fi = new FileReader(directory + bareFile); BufferedReader br = new BufferedReader(fi); String thisLine = null, value = null; int inputLineNumber = 0; while ((thisLine = br.readLine()) != null) { boolean isHeader = false; isHeader = thisLine.contains(headerIndicator); if (thisLine != null && thisLine.length() > 0 && (firstToken || !isHeader)) { HSSFRow rowOut = sheet.createRow(lineNumber); StringTokenizer rowTokenizer = new StringTokenizer(thisLine, ","); HSSFCell cellOut; int colNumber = 0; while (rowTokenizer.hasMoreTokens()) { cellOut = rowOut.createCell(colNumber); String replacePipes = rowTokenizer.nextToken(); if (isHeader) { replacePipes = replacePipes.replaceAll("\\|", ","); value = replacePipes; StringTokenizer temp = new StringTokenizer(replacePipes, ","); if (temp.countTokens() > 1) { HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); link.setAddress(temp.nextToken()); cellOut.setHyperlink(link); value = temp.nextToken().trim(); } cellOut.setCellValue(value); sheet.setColumnWidth(colNumber, (value.length() + 3) * 256); } else { cellOut.setCellValue(replacePipes); if (sheet.getColumnWidth(colNumber) / 256 < replacePipes.length()) sheet.setColumnWidth(colNumber, replacePipes.length() * 256); } colNumber++; } lineNumber++; } inputLineNumber++; } fi.close(); br.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } //} firstToken = false; } return sheet; }
From source file:gov.nih.nci.cananolab.util.ExportUtils.java
License:BSD License
/** * Create a HSSFCell in row with hyperlink, index and value. * * @param row HSSFRow/*from w w w .j a va 2 s. co m*/ * @param index int * @param value String * @param url String * @return HSSFCell */ public static HSSFCell createCell(HSSFRow row, int index, HSSFCellStyle cellStyle, String value, String url) { HSSFCell cell = createCell(row, index, value); HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); link.setAddress(url); cell.setHyperlink(link); cell.setCellStyle(cellStyle); return cell; }
From source file:opn.greenwebs.Hyperlinks.java
public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); //cell style for hyperlinks //by default hyperlinks are blue and underlined HSSFCellStyle hlink_style = wb.createCellStyle(); HSSFFont hlink_font = wb.createFont(); hlink_font.setUnderline(HSSFFont.U_SINGLE); hlink_font.setColor(HSSFColor.BLUE.index); hlink_style.setFont(hlink_font);//from ww w . j a v a 2 s . c o m HSSFCell cell; HSSFSheet sheet = wb.createSheet("Hyperlinks"); //URL cell = sheet.createRow(0).createCell(0); cell.setCellValue("URL Link"); HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); link.setAddress("http://poi.apache.org/"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //link to a file in the current directory cell = sheet.createRow(1).createCell(0); cell.setCellValue("File Link"); link = new HSSFHyperlink(HSSFHyperlink.LINK_FILE); link.setAddress("link1.xls"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //e-mail link cell = sheet.createRow(2).createCell(0); cell.setCellValue("Email Link"); link = new HSSFHyperlink(HSSFHyperlink.LINK_EMAIL); //note, if subject contains white spaces, make sure they are url-encoded link.setAddress("mailto:poi@apache.org?subject=Hyperlinks"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //link to a place in this workbook //create a target sheet and cell HSSFSheet sheet2 = wb.createSheet("Target Sheet"); sheet2.createRow(0).createCell(0).setCellValue("Target Cell"); cell = sheet.createRow(3).createCell(0); cell.setCellValue("Worksheet Link"); link = new HSSFHyperlink(HSSFHyperlink.LINK_DOCUMENT); link.setAddress("'Target Sheet'!A1"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); FileOutputStream out = new FileOutputStream("hssf-links.xls"); wb.write(out); out.close(); }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { // Get the source / new row HSSFRow newRow = worksheet.getRow(destinationRowNum); HSSFRow sourceRow = worksheet.getRow(sourceRowNum); // If the row exist in destination, push down all rows by 1 else create a new row if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {// w ww .j a v a 2 s. c o m newRow = worksheet.createRow(destinationRowNum); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell HSSFCell oldCell = sourceRow.getCell(i); HSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell HSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (newCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getFirstRow() - cellRangeAddress.getLastRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } }
From source file:org.openmicroscopy.shoola.util.file.ExcelWriter.java
License:Open Source License
/** * Adds hyperlink to the cell./*from w w w. java 2 s . c om*/ * * @param row see above. * @param col see above. * @param description contents of cell. * @param URL address. */ public void addHyperlink(int row, int col, String description, String URL) { HSSFCell cell; cell = getCell(row, col); cell.setCellValue(new HSSFRichTextString(description)); HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); link.setAddress(URL); cell.setHyperlink(link); //setCellStyle(cell, HYPERLINK); }
From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.ExcelUtils.java
License:Open Source License
public void createLinkCell(HSSFCell cell, String value, String target, boolean bordered) { cell.setCellValue(value);//w w w . j a v a 2s .c o m CellStyle style = wb.createCellStyle(); if (bordered) style = createBorderedStyle(wb); Font hlink_font = wb.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); style.setFont(hlink_font); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setIndention((short) 1); style.setWrapText(true); HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_DOCUMENT); link.setAddress("'" + normalizeAsLink(target) + "'!A1"); cell.setHyperlink(link); cell.setCellStyle(style); }
From source file:ro.nextreports.engine.exporter.XlsExporter.java
License:Apache License
private void renderCell(BandElement bandElement, String bandName, Object value, int gridRow, int sheetRow, int sheetColumn, int rowSpan, int colSpan, boolean image) { if (bandElement instanceof ReportBandElement) { colSpan = 1;//w w w . j a v a 2 s.co m } HSSFCellStyle cellStyle = buildBandElementStyle(bandElement, value, gridRow, sheetColumn, colSpan); // if we have a subreport on the current grid row we have to take care of the sheetColumn if (ReportLayout.HEADER_BAND_NAME.equals(bandName) && (gridRow == prevSubreportFirstRow) && (prevSubreportLastColumn != -1)) { sheetColumn = prevSubreportLastColumn - prevSubreportFirstColumn - 1 + sheetColumn; } HSSFCell c = xlsRow.createCell(sheetColumn); if (image) { if ((value == null) || "".equals(value)) { c.setCellType(HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString(IMAGE_NOT_FOUND)); } else { try { ImageBandElement ibe = (ImageBandElement) bandElement; byte[] imageBytes = getImage((String) value, ibe.getWidth(), ibe.getHeight()); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) sheetColumn, sheetRow, (short) (sheetColumn + colSpan), (sheetRow + rowSpan)); int index = wb.addPicture(imageBytes, HSSFWorkbook.PICTURE_TYPE_JPEG); // image is created over the cells, so if it's height is bigger we set the row height short height = xlsRow.getHeight(); int realImageHeight = getRealImageSize((String) value)[1]; if (ibe.isScaled()) { realImageHeight = ibe.getHeight(); } short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5); if (imageHeight > height) { xlsRow.setHeight(imageHeight); } HSSFPicture picture = patriarch.createPicture(anchor, index); picture.resize(); anchor.setAnchorType(2); } catch (Exception ex) { c.setCellType(HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString(IMAGE_NOT_LOADED)); } } if (cellStyle != null) { c.setCellStyle(cellStyle); } } else { if (bandElement instanceof HyperlinkBandElement) { Hyperlink hyp = ((HyperlinkBandElement) bandElement).getHyperlink(); HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); link.setAddress(hyp.getUrl()); c.setHyperlink(link); c.setCellValue(new HSSFRichTextString(hyp.getText())); c.setCellType(HSSFCell.CELL_TYPE_STRING); } else if (bandElement instanceof ReportBandElement) { Report report = ((ReportBandElement) bandElement).getReport(); ExporterBean eb = null; try { eb = getSubreportExporterBean(report, true); XlsExporter subExporter = new XlsExporter(eb, cellStyle); subExporter.export(); HSSFSheet subreportSheet = subExporter.getSubreportSheet(); if (ReportLayout.HEADER_BAND_NAME.equals(bandName) && (gridRow == prevSubreportFirstRow)) { // other subreports on the same header line after the first sheetColumn = prevSubreportLastColumn; sheetRow -= addedPageRows; pageRow -= addedPageRows; addedPageRows = 0; } else { addedPageRows = subreportSheet.getLastRowNum(); pageRow += addedPageRows; // if subreport is not on the first column we merge all cells in the columns before, between the rows subreport occupies if (sheetColumn > 0) { for (int i = 0; i <= sheetColumn - 1; i++) { CellRangeAddress cra = new CellRangeAddress(sheetRow, pageRow, i, i); regions.add(new XlsRegion(cra, null)); } } } int cols = XlsUtil.copyToSheet(xlsSheet, sheetRow, sheetColumn, subreportSheet); addRegions(xlsSheet, subExporter.getSubreportRegions(), wb); if (ReportLayout.HEADER_BAND_NAME.equals(bandName)) { prevSubreportFirstRow = gridRow; prevSubreportFirstColumn = sheetColumn; prevSubreportLastColumn = sheetColumn + cols; } } catch (Exception e) { e.printStackTrace(); } finally { if ((eb != null) && (eb.getResult() != null)) { eb.getResult().close(); } } } else if (bandElement instanceof ImageColumnBandElement) { try { ImageColumnBandElement icbe = (ImageColumnBandElement) bandElement; String v = StringUtil.getValueAsString(value, null); if (StringUtil.BLOB.equals(v)) { c.setCellType(HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString(StringUtil.BLOB)); } else { byte[] imageD = StringUtil.decodeImage(v); byte[] imageBytes = getImage(imageD, icbe.getWidth(), icbe.getHeight()); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) sheetColumn, sheetRow, (short) (sheetColumn + colSpan), (sheetRow + rowSpan)); int index = wb.addPicture(imageBytes, HSSFWorkbook.PICTURE_TYPE_JPEG); // image is created over the cells, so if it's height is bigger we set the row height short height = xlsRow.getHeight(); int realImageHeight = getRealImageSize(imageBytes)[1]; if (icbe.isScaled()) { realImageHeight = icbe.getHeight(); } short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5); if (imageHeight > height) { xlsRow.setHeight(imageHeight); } HSSFPicture picture = patriarch.createPicture(anchor, index); picture.resize(); anchor.setAnchorType(2); } } catch (Exception e) { e.printStackTrace(); c.setCellType(HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString(IMAGE_NOT_LOADED)); } } else { if (value == null) { c.setCellType(HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString("")); } else if (value instanceof Number) { c.setCellType(HSSFCell.CELL_TYPE_NUMERIC); c.setCellValue(((Number) value).doubleValue()); } else { String pattern = null; if (bandElement instanceof FieldBandElement) { FieldBandElement fbe = (FieldBandElement) bandElement; pattern = fbe.getPattern(); } if ((value instanceof java.sql.Date) || (value instanceof java.sql.Timestamp)) { Date date; if (value instanceof java.sql.Date) { date = new Date(((java.sql.Date) value).getTime()); } else { date = (java.sql.Timestamp) value; } if (cellStyle != null) { if (pattern == null) { // use default pattern if none selected Locale locale = Locale.getDefault(); pattern = ((SimpleDateFormat) DateFormat.getDateInstance(SimpleDateFormat.MEDIUM, locale)).toPattern(); } else { pattern = StringUtil.getI18nString(pattern, getReportLanguage()); } cellStyle.setDataFormat(wb.createDataFormat().getFormat(pattern)); } c.setCellValue(date); } else { c.setCellType(HSSFCell.CELL_TYPE_STRING); String text = StringUtil.getValueAsString(value, pattern); if ((bandElement != null) && bandElement.isWrapText()) { // try to interpret new line characters // \\n is used here to be possible to add in designer grid cell with \n if (text.contains("\\n") || text.contains("\n") || text.contains("\r") || text.contains("\r\n")) { String crLf = Character.toString((char) 13) + Character.toString((char) 10); int lines = countLines(text); if (text.contains("\r\n")) { text = text.replaceAll("\r\n", crLf); } else { text = text.replaceAll("(\n)|(\r)|(\\\\n)", crLf); } c.setCellValue(text); cellStyle.setWrapText(true); xlsRow.setHeightInPoints( lines * (cellStyle.getFont(wb).getFontHeightInPoints() + 3)); } else { c.setCellValue(new HSSFRichTextString(text)); } } else { c.setCellValue(new HSSFRichTextString(text)); } } } } if (cellStyle != null) { if (bandElement != null) { cellStyle.setRotation(bandElement.getTextRotation()); } if (!(bandElement instanceof ReportBandElement)) { c.setCellStyle(cellStyle); } } if ((rowSpan > 1) || (colSpan > 1)) { CellRangeAddress cra = new CellRangeAddress(sheetRow, sheetRow + rowSpan - 1, sheetColumn, sheetColumn + colSpan - 1); Border beBorder = bandElement.getBorder(); if (hasRowRenderConditions(bandElement, gridRow, value)) { // for row render conditions we must keep the row border beBorder = border; } regions.add(new XlsRegion(cra, beBorder)); } } }