List of usage examples for org.apache.poi.hssf.usermodel HSSFHyperlink setAddress
@Override public void setAddress(String address)
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.j a v a2 s . c o 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 w w w. ja v a 2 s . co m 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// w ww.j a v a 2 s . c o 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:gov.nih.nci.evs.app.neopl.CSVtoExcel.java
License:Open Source License
public void runHSSF(String inputfile) { int size = checkSpecialCharacters(inputfile); int n = inputfile.lastIndexOf("."); String outputfile = getOutputFile(inputfile, "xls"); try {/* ww w . j a v a2 s. co m*/ HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle cellStyle = wb.createCellStyle(); //cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //font.setColor(HSSFColor.WHITE.index); cellStyle.setFont(font); HSSFCellStyle linkCellStyle = wb.createCellStyle(); //cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont linkfont = wb.createFont(); linkfont.setColor(HSSFColor.LIGHT_BLUE.index); linkCellStyle.setFont(linkfont); //CreationHelper helper = wb.getCreationHelper(); CSVReader reader = new CSVReader(new FileReader(inputfile));//CSV file String[] line; int r = 0; Cell cell = null; //"C7419","Acanthoma","C0846967","Acanthoma","MDR","Acanthoma","10059394","LT" // skip heading HSSFHyperlink url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); HSSFSheet sheet = null; int page_num = 1; Row row = null; line = reader.readNext(); int lcv = 0; int row_count = 0; Vector w = new Vector(); while ((line = reader.readNext()) != null) { if (lcv % PAGE_SIZE == 0) { r = 0; String sheetLabel = SHEET_LABEL; if (size > PAGE_SIZE) { sheetLabel = sheetLabel + " (Page " + page_num + ")"; } sheet = wb.createSheet(sheetLabel); for (int j = 0; j < HEADINGS.length; j++) { sheet.autoSizeColumn(j); } row = sheet.createRow((short) r); for (int i = 0; i < HEADINGS.length; i++) { String heading = HEADINGS[i]; cell = row.createCell(i); cell.setCellValue(heading); cell.setCellStyle(cellStyle); } sheet.createFreezePane(0, 1); // this will freeze the header row page_num++; } String s4 = (String) line[4]; if (s4.compareTo("NCI") == 0) { w.add("NCI Line number: " + r); } else if (s4.compareTo("NCI") != 0) { r++; row = sheet.createRow((short) r); row_count++; row = sheet.createRow((short) r); row_count++; for (int i = 0; i < HEADINGS.length; i++) { cell = row.createCell(i); int codeCol = codeColumn[i]; cell.setCellValue(line[i]); if (NCIT_LINK && codeCol == LINK_NCIT) { url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); String code = line[i]; url_link.setAddress(getNCItHyperlink(code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } else if (NCIM_LINK && codeCol == LINK_NCIM) { url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); String code = line[i]; url_link.setAddress(getNCImHyperlink(code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } else if (SOURCE_LINK && codeCol == LINK_OTHER) { url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); String code = line[i]; String source = line[SOURCE_INDEX]; if (localNameMap.containsKey(source)) { url_link.setAddress(getSourceHyperlink(source, code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } } } } lcv++; } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(outputfile); wb.write(fileOut); fileOut.close(); System.out.println("Output file " + outputfile + " generated."); System.out.println("row_count: " + row_count); System.out.println("NCI: " + w.size()); for (int i = 0; i < w.size(); i++) { String t = (String) w.elementAt(i); System.out.println(t); } } catch (Exception ex) { ex.printStackTrace(); } }
From source file:gov.nih.nci.evs.app.neopl.CSVtoExcel.java
License:Open Source License
public void runHSSF(Vector data_vec, String outputfile) { if (codeColumn == null) { setDefault();/*w ww . j a v a2 s. c o m*/ } //int size = 0;//checkSpecialCharacters(inputfile); int size = data_vec.size(); String[] line = null; try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle cellStyle = wb.createCellStyle(); //cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //font.setColor(HSSFColor.WHITE.index); cellStyle.setFont(font); HSSFCellStyle linkCellStyle = wb.createCellStyle(); //cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont linkfont = wb.createFont(); linkfont.setColor(HSSFColor.LIGHT_BLUE.index); linkCellStyle.setFont(linkfont); //CreationHelper helper = wb.getCreationHelper(); //CSVReader reader = new CSVReader(new FileReader(inputfile));//CSV file int r = 0; Cell cell = null; //"C7419","Acanthoma","C0846967","Acanthoma","MDR","Acanthoma","10059394","LT" // skip heading HSSFHyperlink url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); HSSFSheet sheet = null; int page_num = 1; Row row = null; //line = reader.readNext(); int lcv = 0; int row_count = 0; Vector w = new Vector(); String nextLine = null; Vector values = null; for (int k = 0; k < data_vec.size(); k++) { nextLine = (String) data_vec.elementAt(k); int k1 = k + 1; line = toArray(nextLine); //if (lcv % PAGE_SIZE == 0) { if (lcv == 0) { r = 0; String sheetLabel = SHEET_LABEL; if (size > PAGE_SIZE) { sheetLabel = sheetLabel;// + " (Page " + page_num + ")"; } sheet = wb.createSheet(sheetLabel); for (int j = 0; j < HEADINGS.length; j++) { sheet.autoSizeColumn(j); } row = sheet.createRow((short) r); for (int i = 0; i < HEADINGS.length; i++) { String heading = HEADINGS[i]; cell = row.createCell(i); cell.setCellValue(heading); cell.setCellStyle(cellStyle); } sheet.createFreezePane(0, 1); // this will freeze the header row page_num++; } String s4 = line[4]; if (s4.compareTo("NCI") == 0) { w.add("NCI Line number: " + r); } else { //if (s4.compareTo("NCI") != 0) { r++; row = sheet.createRow((short) r); row_count++; for (int i = 0; i < HEADINGS.length; i++) { cell = row.createCell(i); int codeCol = codeColumn[i]; cell.setCellValue(line[i]); if (NCIT_LINK && codeCol == LINK_NCIT) { url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); String code = line[i]; url_link.setAddress(getNCItHyperlink(code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } else if (NCIM_LINK && codeCol == LINK_NCIM) { url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); String code = line[i]; url_link.setAddress(getNCImHyperlink(code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } else if (SOURCE_LINK && codeCol == LINK_OTHER) { url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); String code = line[i]; String source = line[SOURCE_INDEX]; if (localNameMap.containsKey(source)) { url_link.setAddress(getSourceHyperlink(source, code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } } } } lcv++; } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(outputfile); wb.write(fileOut); fileOut.close(); System.out.println("Output file " + outputfile + " generated."); } catch (Exception ex) { ex.printStackTrace(); } }
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);// w w w . j av a 2 s . co 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.deployom.core.AuditService.java
License:Open Source License
public HSSFWorkbook saveAudit() { // Create book HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCreationHelper creationHelper = workbook.getCreationHelper(); // Default Style HSSFCellStyle style = workbook.createCellStyle(); style.setWrapText(true);/*from ww w . j a v a 2s . c o m*/ HSSFFont font = workbook.createFont(); font.setFontName("Courier New"); style.setFont(font); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // Header Style HSSFCellStyle styleHeader = workbook.createCellStyle(); styleHeader.cloneStyleFrom(style); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.WHITE.index); styleHeader.setFillForegroundColor(IndexedColors.BLACK.getIndex()); styleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleHeader.setFont(font); // Error Style HSSFCellStyle styleError = workbook.createCellStyle(); styleError.cloneStyleFrom(style); styleError.setFillForegroundColor(IndexedColors.CORAL.getIndex()); styleError.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleError.setWrapText(true); // Link Style HSSFCellStyle styleLink = workbook.createCellStyle(); styleLink.cloneStyleFrom(style); font = workbook.createFont(); font.setUnderline(HSSFFont.U_SINGLE); font.setColor(IndexedColors.BLUE.getIndex()); styleLink.setFont(font); // Create Summary HSSFSheet summarySheet = workbook.createSheet("Summary"); int summaryRownum = 0; int summaryCellnum = 0; //Create a new row in current sheet Row summaryRow = summarySheet.createRow(summaryRownum++); // 0 Cell summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue("Job"); summaryCell.setCellStyle(styleHeader); // 1 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue("Finished"); summaryCell.setCellStyle(styleHeader); // 2 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue("Errors"); summaryCell.setCellStyle(styleHeader); for (Job job : releaseService.getJobs()) { // Open Job JobService jobService = new JobService(siteService.getSiteName(), job.getJobName()); // Create Sheet HSSFSheet sheet = workbook.createSheet(job.getJobName()); int rownum = 0; int cellnum = 0; int errors = 0; //Create a new row in current sheet Row row = sheet.createRow(rownum++); // 0 Cell cell = row.createCell(cellnum++); cell.setCellValue("Host"); cell.setCellStyle(styleHeader); // 1 cell = row.createCell(cellnum++); cell.setCellValue("Service"); cell.setCellStyle(styleHeader); // 2 cell = row.createCell(cellnum++); cell.setCellValue("Command"); cell.setCellStyle(styleHeader); // 3 cell = row.createCell(cellnum++); cell.setCellValue("Executable"); cell.setCellStyle(styleHeader); // 4 cell = row.createCell(cellnum++); cell.setCellValue("Error"); cell.setCellStyle(styleHeader); // 5 cell = row.createCell(cellnum++); cell.setCellValue("Output"); cell.setCellStyle(styleHeader); // Check all hosts for (Host host : jobService.getHosts()) { // Check all services for (Service service : host.getServices()) { // Get a Commands for (Command command : service.getCommands()) { //Create a new row in current sheet row = sheet.createRow(rownum++); cellnum = 0; // 0 cell = row.createCell(cellnum++); cell.setCellValue(host.getHostName()); cell.setCellStyle(style); // 1 cell = row.createCell(cellnum++); cell.setCellValue(service.getServiceName()); cell.setCellStyle(style); // 2 cell = row.createCell(cellnum++); cell.setCellValue(command.getTitle()); cell.setCellStyle(style); // 3 cell = row.createCell(cellnum++); cell.setCellValue(command.getExec()); cell.setCellStyle(style); // 4 cell = row.createCell(cellnum++); cell.setCellValue("N"); cell.setCellStyle(style); // 5 cell = row.createCell(cellnum++); if (command.getOut().length() > 1024) { cell.setCellValue(command.getOut().substring(0, 1024) + "..."); } else { cell.setCellValue(command.getOut()); } cell.setCellStyle(style); // Error if (command.isError() == true) { row.getCell(0).setCellStyle(styleError); row.getCell(1).setCellStyle(styleError); row.getCell(2).setCellStyle(styleError); row.getCell(3).setCellStyle(styleError); row.getCell(4).setCellStyle(styleError); row.getCell(5).setCellStyle(styleError); row.getCell(4).setCellValue("Y"); errors++; } } } } // Set Size sheet.setColumnWidth(0, 6000); sheet.setColumnWidth(1, 4000); sheet.setColumnWidth(2, 8000); sheet.setColumnWidth(3, 14000); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 20000); // Summary summaryRow = summarySheet.createRow(summaryRownum++); summaryCellnum = 0; // 0 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue(job.getJobName()); summaryCell.setCellStyle(style); // Set Link HSSFHyperlink link = creationHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); link.setAddress("" + job.getJobName() + "!A1"); summaryCell.setHyperlink(link); summaryCell.setCellStyle(styleLink); // 1 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue(jobService.getJob().getFinished()); summaryCell.setCellStyle(style); // 2 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue(errors); summaryCell.setCellStyle(style); // If errors found if (errors > 0) { summaryRow.getCell(0).setCellStyle(styleError); summaryRow.getCell(1).setCellStyle(styleError); summaryRow.getCell(2).setCellStyle(styleError); } } // Set Summary Size summarySheet.setColumnWidth(0, 6000); summarySheet.setColumnWidth(1, 10000); summarySheet.setColumnWidth(2, 4000); // Save try { FileOutputStream out = new FileOutputStream(new File(getFileName())); workbook.write(out); out.close(); logger.log(Level.INFO, "{0} generated successfully..", getFileName()); return workbook; } catch (FileNotFoundException ex) { logger.log(Level.WARNING, "Audit: {0}", ex); } catch (IOException ex) { logger.log(Level.WARNING, "Audit: {0}", ex); } return null; }
From source file:org.openmicroscopy.shoola.util.file.ExcelWriter.java
License:Open Source License
/** * Adds hyperlink to the cell.//from w w w.j a v a 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.sakaiproject.signup.tool.downloadEvents.EventWorksheet.java
License:Educational Community License
private HSSFHyperlink setAttachmentURLLinks(SignupAttachment attach) { HSSFHyperlink hsHyperlink = new HSSFHyperlink(HSSFHyperlink.LINK_URL); String link = this.sakaiFacade.getServerConfigurationService().getServerUrl() + attach.getLocation(); hsHyperlink.setAddress(link); hsHyperlink.setLabel(attach.getFilename()); return hsHyperlink; }
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);// ww w.j ava2 s .c om 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); }