List of usage examples for org.apache.poi.ss.usermodel Workbook setSheetName
void setSheetName(int sheet, String name);
From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetriever.java
License:Open Source License
private void createSummary(Workbook wb, List<String> metricNames, String metricsDir, long startTime, long endTime, SUMMARY_INTERVALS summaryInterval) throws IOException, MetricsGraphException { // convert seconds to milliseconds startTime = TimeUnit.SECONDS.toMillis(startTime); endTime = TimeUnit.SECONDS.toMillis(endTime); DateTime reportStart = new DateTime(startTime, DateTimeZone.UTC); DateTime reportEnd = new DateTime(endTime, DateTimeZone.UTC); Sheet sheet = wb.createSheet();//from ww w. jav a2s . com wb.setSheetName(0, reportStart.toString(SUMMARY_TIMESTAMP) + " to " + reportEnd.toString(SUMMARY_TIMESTAMP)); Row headingRow = sheet.createRow(0); int columnMax = 1; for (String metricName : metricNames) { MutableDateTime chunkStart = new MutableDateTime(reportStart); MutableDateTime chunkEnd = new MutableDateTime(chunkStart); Row row = sheet.createRow(metricNames.indexOf(metricName) + 1); int columnCounter = 1; Boolean isSum = null; while (reportEnd.compareTo(chunkEnd) > 0 && columnCounter < EXCEL_MAX_COLUMNS) { increment(chunkEnd, summaryInterval); if (chunkEnd.isAfter(reportEnd)) { chunkEnd.setMillis(reportEnd); } // offset range by one millisecond so rrd will calculate granularity correctly chunkEnd.addMillis(-1); MetricData metricData = getMetricData(getRrdFilename(metricsDir, metricName), TimeUnit.MILLISECONDS.toSeconds(chunkStart.getMillis()), TimeUnit.MILLISECONDS.toSeconds(chunkEnd.getMillis())); isSum = metricData.hasTotalCount(); chunkEnd.addMillis(1); if (headingRow.getCell(columnCounter) == null) { Cell headingRowCell = headingRow.createCell(columnCounter); headingRowCell.getCellStyle().setWrapText(true); headingRowCell.setCellValue(getTimestamp(chunkStart, chunkEnd, columnCounter, summaryInterval)); } Cell sumOrAvg = row.createCell(columnCounter); if (isSum) { sumOrAvg.setCellValue((double) metricData.getTotalCount()); } else { sumOrAvg.setCellValue(cumulativeRunningAverage(metricData.getValues())); } chunkStart.setMillis(chunkEnd); columnCounter++; } columnMax = columnCounter; if (isSum != null) { row.createCell(0).setCellValue(convertCamelCase(metricName) + " (" + (isSum ? "sum" : "avg") + ")"); } } for (int i = 0; i < columnMax; i++) { sheet.autoSizeColumn(i); } }
From source file:de.fraunhofer.sciencedataamanager.datamanager.SearchDefinitonExecutionDataManager.java
/** * * @param searchDefinitonExecutionList//w ww . jav a 2 s . c om * @param outputStream * @throws Exception */ public void exportToExcel(LinkedList<SearchDefinitonExecution> searchDefinitonExecutionList, OutputStream outputStream) throws Exception { Workbook currentWorkBook = new HSSFWorkbook(); int currenSheetCount = 0; for (SearchDefinitonExecution searchDefinitonExecution : searchDefinitonExecutionList) { Sheet currentSheet = currentWorkBook.createSheet(); currentSheet.setFitToPage(true); currentSheet.setHorizontallyCenter(true); currentSheet.createFreezePane(0, 1); currentWorkBook.setSheetName(currenSheetCount, searchDefinitonExecution.getSystemInstance().getName()); Row headerRow = currentSheet.createRow(0); headerRow.setHeightInPoints(12.75f); headerRow.createCell(0).setCellValue("ID"); headerRow.createCell(1).setCellValue("Title"); headerRow.createCell(2).setCellValue("Identifier 1"); headerRow.createCell(3).setCellValue("Identifier 2"); headerRow.createCell(4).setCellValue("Identifier 3"); headerRow.createCell(5).setCellValue("Identifier 4"); headerRow.createCell(6).setCellValue("Url 1"); headerRow.createCell(7).setCellValue("Url 2"); headerRow.createCell(8).setCellValue("Text 1"); headerRow.createCell(9).setCellValue("Publication Name"); headerRow.createCell(10).setCellValue("Issue Name"); headerRow.createCell(11).setCellValue("Publish Date"); headerRow.createCell(12).setCellValue("Volume"); headerRow.createCell(13).setCellValue("Start Page"); headerRow.createCell(14).setCellValue("Issue Identifier"); CellStyle style = currentWorkBook.createCellStyle(); Font headerFont = currentWorkBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); headerRow.setRowStyle(style); Row currentRow = null; int rowNum = 1; for (ScientificPaperMetaInformation scientificPaperMetaInformation : searchDefinitonExecution .getScientificPaperMetaInformation()) { currentRow = currentSheet.createRow(rowNum); currentRow.createCell(0).setCellValue(scientificPaperMetaInformation.getID()); currentRow.createCell(1).setCellValue(scientificPaperMetaInformation.getTitle()); currentRow.createCell(2).setCellValue(scientificPaperMetaInformation.getIdentifier_1()); currentRow.createCell(3).setCellValue(scientificPaperMetaInformation.getIdentifier_2()); currentRow.createCell(4).setCellValue(scientificPaperMetaInformation.getIdentifier_3()); currentRow.createCell(5).setCellValue(scientificPaperMetaInformation.getIdentifier_4()); currentRow.createCell(6).setCellValue(scientificPaperMetaInformation.getUrl_1()); currentRow.createCell(7).setCellValue(scientificPaperMetaInformation.getUrl_2()); currentRow.createCell(8).setCellValue(scientificPaperMetaInformation.getText_1()); currentRow.createCell(9).setCellValue(scientificPaperMetaInformation.getSrcTitle()); currentRow.createCell(10).setCellValue(scientificPaperMetaInformation.getScrPublisherName()); currentRow.createCell(11).setCellValue(scientificPaperMetaInformation.getSrcPublicationDate()); currentRow.createCell(12).setCellValue(scientificPaperMetaInformation.getSrcVolume()); currentRow.createCell(13).setCellValue(scientificPaperMetaInformation.getSrcStartPage()); currentRow.createCell(14).setCellValue(scientificPaperMetaInformation.getScrIdentifier_1()); rowNum++; } currenSheetCount++; } currentWorkBook.write(outputStream); outputStream.close(); }
From source file:de.fraunhofer.sciencedataamanager.exampes.export.ExcelDataExport.java
/** * * @param dataToExport The objects gets all the values, which should * exported./*from w w w .jav a 2 s. co m*/ * @param outputStream * @throws Exception */ @Override public void export(Map<String, Map<String, List<Object>>> allConnectorsToExport, OutputStream outputStream) throws Exception { Workbook currentWorkBook = new HSSFWorkbook(); int currenSheetCount = 0; for (String currentKey : allConnectorsToExport.keySet()) { Map<String, List<Object>> dataToExport = allConnectorsToExport.get(currentKey); List<String> columns = new ArrayList<String>(dataToExport.keySet()); List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>(); int size = dataToExport.values().iterator().next().size(); for (int i = 0; i < size; i++) { Map<String, Object> row = new HashMap<String, Object>(); for (String column : columns) { row.put(column, dataToExport.get(column).get(i)); } rows.add(row); } //for (SearchDefinitonExecution searchDefinitonExecution : searchDefinitonExecutionList) { Sheet currentSheet = currentWorkBook.createSheet(); currentSheet.setFitToPage(true); currentSheet.setHorizontallyCenter(true); currentSheet.createFreezePane(0, 1); currentWorkBook.setSheetName(currenSheetCount, currentKey); Row headerRow = currentSheet.createRow(0); headerRow.setHeightInPoints(12.75f); int headerColumnIndex = 0; for (String currentColumn : columns) { headerRow.createCell(headerColumnIndex).setCellValue(currentColumn); headerColumnIndex++; } CellStyle style = currentWorkBook.createCellStyle(); Font headerFont = currentWorkBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); headerRow.setRowStyle(style); Row currentRow = null; int rowNum = 1; int currentColum = 0; for (Map<String, Object> currentRow2 : rows) { currentRow = currentSheet.createRow(rowNum); for (String column : columns) { if (currentRow2.get(column) != null) { currentRow.createCell(currentColum).setCellValue(currentRow2.get(column).toString()); } currentColum++; } currentColum = 0; rowNum++; } currenSheetCount++; } currentWorkBook.write(outputStream); outputStream.close(); }
From source file:de.iteratec.iteraplan.businesslogic.exchange.nettoExport.AbstractNettoTransformer.java
License:Open Source License
/** * Set the sheet name like "Information System (IS)". * @param sheet A POI {@link Sheet}/*from w ww . ja v a 2 s . c om*/ * @param typeOfBuildingBlock The used type of building block. */ protected final void configSheetName(Sheet sheet, TypeOfBuildingBlock typeOfBuildingBlock) { String msgLookupKey = typeOfBuildingBlock.getValue(); String msgLookupKeyAbbr = typeOfBuildingBlock.getAbbreviationValue(); String tobName = MessageAccess.getString(msgLookupKey); String tobAbbrName = MessageAccess.getString(msgLookupKeyAbbr); Workbook workbook = sheet.getWorkbook(); int sheetIndex = workbook.getSheetIndex(sheet); workbook.setSheetName(sheetIndex, tobName + " (" + tobAbbrName + ")"); }
From source file:dk.cubing.liveresults.action.admin.ScoresheetAction.java
License:Open Source License
/** * @param workBook// w w w . j av a2 s . com * @param template * @param competition * @param event * @param round * @param includeCompetitors */ private void createResultSheetFromTemplate(Workbook workBook, Sheet template, Competition competition, Event event, String round, boolean includeCompetitors) { Sheet resultSheet = workBook.cloneSheet(workBook.getSheetIndex(template)); String sheetName = null; String eventNameFormatted = null; if (round == null) { sheetName = event.getName(); // TODO: format to short sheetname eventNameFormatted = event.getName(); } else { sheetName = event.getName() + " - " + getRoundTypesMap().get(round); eventNameFormatted = getText("admin.scoresheet.eventname." + event.getName().toLowerCase()) + " - " + getRoundTypesMap().get(round); } log.debug("Building result sheet: {}", sheetName); workBook.setSheetName(workBook.getSheetIndex(resultSheet), sheetName); workBook.setSheetOrder(sheetName, 1); // first sheet is the registration sheet, let's put results directly after that Cell eventName = getCell(resultSheet, 0, 0, Cell.CELL_TYPE_STRING); eventName.setCellValue(eventNameFormatted); // get cell styles from template List<CellStyle> cellStyles = new ArrayList<CellStyle>(); Row startRow = template.getRow(4); int numberOfColumns = template.getRow(3).getPhysicalNumberOfCells(); if (startRow != null) { log.debug("Start row contains {} cells.", numberOfColumns); for (int i = 0; i < numberOfColumns; i++) { Cell cell = startRow.getCell(i); if (cell != null) { cellStyles.add(cell.getCellStyle()); } else { cellStyles.add(workBook.createCellStyle()); } } } // adjust formulas int numberOfCompetitors = (round == null) ? event.getResults().size() : competition.getCompetitors().size(); for (int i = 0; i < numberOfCompetitors; i++) { for (int j = 0; j < numberOfColumns; j++) { if (SHEET_TYPE_AVERAGE5S.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "I" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(M" + (i + 4) + "=M" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 9: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=5,\"\",\"DNF\"))"); break; // worst case 11: Cell worst = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); worst.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",MAX(" + range + ")))"); break; // average case 12: Cell average = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); average.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>1,\"DNF\",ROUND(IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,(SUM(" + range + ")-J" + (i + 5) + ")/3,(SUM(" + range + ")-J" + (i + 5) + "-L" + (i + 5) + ")/3),2)))"); break; } } else if (SHEET_TYPE_AVERAGE5M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "I" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(M" + (i + 4) + "=M" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 9: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=5,\"\",\"DNF\"))"); break; // worst case 11: Cell worst = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); worst.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",MAX(" + range + ")))"); break; // average case 12: Cell average = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); average.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>1,\"DNF\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,(SUM(" + range + ")-J" + (i + 5) + ")/3,(SUM(" + range + ")-J" + (i + 5) + "-L" + (i + 5) + ")/3)))"); break; } } else if (SHEET_TYPE_MEAN3S.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "G" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(H" + (i + 4) + "=H" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 7: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=3,\"\",\"DNF\"))"); break; // mean case 9: Cell mean = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); mean.setCellFormula( "IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",ROUND(AVERAGE(" + range + "),2)))"); break; } } else if (SHEET_TYPE_MEAN3M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "G" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(H" + (i + 4) + "=H" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 7: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=3,\"\",\"DNF\"))"); break; // mean case 9: Cell mean = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); mean.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",AVERAGE(" + range + ")))"); break; } } else if (SHEET_TYPE_BEST1S.equals(template.getSheetName()) || SHEET_TYPE_BEST1M.equals(template.getSheetName()) || SHEET_TYPE_BEST1N.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(E" + (i + 4) + "=E" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; } } else if (SHEET_TYPE_TEAMBEST1M.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(H" + (i + 4) + "=H" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; } } else if (SHEET_TYPE_BEST2S.equals(template.getSheetName()) || SHEET_TYPE_BEST2M.equals(template.getSheetName()) || SHEET_TYPE_BEST2N.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "F" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(G" + (i + 4) + "=G" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 6: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=2,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_TEAMBEST2M.equals(template.getSheetName())) { String range = "H" + (i + 5) + ":" + "I" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(J" + (i + 4) + "=J" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 9: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=2,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_BEST3S.equals(template.getSheetName()) || SHEET_TYPE_BEST3M.equals(template.getSheetName()) || SHEET_TYPE_BEST3N.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "G" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(H" + (i + 4) + "=H" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 7: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=3,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_TEAMBEST3M.equals(template.getSheetName())) { String range = "H" + (i + 5) + ":" + "J" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(K" + (i + 4) + "=K" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 10: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=2,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_MULTIBF1.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(I" + (i + 4) + "=I" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // result case 8: Cell result = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result.setCellFormula("IF(E" + (i + 5) + "-F" + (i + 5) + ">F" + (i + 5) + ",-1,(99-F" + (i + 5) + "+E" + (i + 5) + "-F" + (i + 5) + ")*10000000+G" + (i + 5) + "*100+E" + (i + 5) + "-F" + (i + 5) + ")"); break; } } else if (SHEET_TYPE_MULTIBF2.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(I" + (i + 4) + "=I" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // result1 case 7: Cell result1 = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result1.setCellFormula("IF(E" + (i + 5) + "=\"DNS\",-2,IF(E" + (i + 5) + "-F" + (i + 5) + ">F" + (i + 5) + ",-1,(99-F" + (i + 5) + "+E" + (i + 5) + "-F" + (i + 5) + ")*10000000+G" + (i + 5) + "*100+E" + (i + 5) + "-F" + (i + 5) + "))"); break; // result2 case 11: Cell result2 = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result2.setCellFormula("IF(I" + (i + 5) + "=\"DNS\",-2,IF(I" + (i + 5) + "-J" + (i + 5) + ">J" + (i + 5) + ",-1,(99-J" + (i + 5) + "+I" + (i + 5) + "-J" + (i + 5) + ")*10000000+K" + (i + 5) + "*100+I" + (i + 5) + "-J" + (i + 5) + "))"); break; // best case 12: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(AND(H" + (i + 5) + "<0,L" + (i + 5) + "<0),-1,IF(" + (i + 5) + "<0,L" + (i + 5) + ",IF(L" + (i + 5) + "<0,H" + (i + 5) + ",MIN(H" + (i + 5) + ",L" + (i + 5) + "))))"); break; } } else { log.error("Unsupported sheet type: {}", template.getSheetName()); } // set cell style Row row = resultSheet.getRow(i + 4); if (row != null) { Cell cell = row.getCell(j); if (cell != null) { cell.setCellStyle(cellStyles.get(j)); } } } } // fill sheet with competitors for this event if (includeCompetitors) { if (round == null) { try { generateCompetitorResultsRows(workBook, resultSheet, event); } catch (Exception e) { log.error("[{}] " + e.getLocalizedMessage(), e); throw new RuntimeException("Could not include competitors and results in this sheet.", e); } } else { try { generateCompetitorRows(workBook, resultSheet, competition.getCompetitorsByEvent(event), 4); } catch (Exception e) { log.error("[{}] " + e.getLocalizedMessage(), e); throw new RuntimeException("Could not include competitors in this sheet.", e); } } } }
From source file:dk.cubing.wcaspreadsheet.action.ScoresheetAction.java
License:Open Source License
/** * @param workBook//w w w. java 2 s. c o m * @param template * @param competition * @param event * @param round * @param includeCompetitors */ private void createResultSheetFromTemplate(Workbook workBook, Sheet template, Competition competition, Event event, String round, boolean includeCompetitors) { Sheet resultSheet = workBook.cloneSheet(workBook.getSheetIndex(template)); String sheetName = event.getName() + " - " + getRoundTypesMap().get(round); log.debug("Building result sheet: {}", sheetName); String eventNameFormatted = getText("admin.scoresheet.eventname." + event.getName().toLowerCase()) + " - " + getRoundTypesMap().get(round); workBook.setSheetName(workBook.getSheetIndex(resultSheet), sheetName); workBook.setSheetOrder(sheetName, 1); // first sheet is the registration sheet, let's put results directly after that Cell eventName = getCell(resultSheet, 0, 0, Cell.CELL_TYPE_STRING); eventName.setCellValue(eventNameFormatted); // get cell styles from template List<CellStyle> cellStyles = new ArrayList<CellStyle>(); Row startRow = template.getRow(4); int numberOfColumns = template.getRow(3).getPhysicalNumberOfCells(); if (startRow != null) { log.debug("Start row contains {} cells.", numberOfColumns); for (int i = 0; i < numberOfColumns; i++) { Cell cell = startRow.getCell(i); if (cell != null) { cellStyles.add(cell.getCellStyle()); } else { cellStyles.add(workBook.createCellStyle()); } } } // adjust formulas int numberOfCompetitors = competition.getCompetitors().size(); for (int i = 0; i < numberOfCompetitors; i++) { for (int j = 0; j < numberOfColumns; j++) { if (SHEET_TYPE_AVERAGE5S.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "I" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(M" + (i + 4) + "=M" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 9: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=5,\"\",\"DNF\"))"); break; // worst case 11: Cell worst = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); worst.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",MAX(" + range + ")))"); break; // average case 12: Cell average = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); average.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>1,\"DNF\",ROUND(IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,(SUM(" + range + ")-J" + (i + 5) + ")/3,(SUM(" + range + ")-J" + (i + 5) + "-L" + (i + 5) + ")/3),2)))"); break; } } else if (SHEET_TYPE_AVERAGE5M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "I" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(M" + (i + 4) + "=M" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 9: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=5,\"\",\"DNF\"))"); break; // worst case 11: Cell worst = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); worst.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",MAX(" + range + ")))"); break; // average case 12: Cell average = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); average.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>1,\"DNF\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,(SUM(" + range + ")-J" + (i + 5) + ")/3,(SUM(" + range + ")-J" + (i + 5) + "-L" + (i + 5) + ")/3)))"); break; } } else if (SHEET_TYPE_MEAN3S.equals(template.getSheetName()) || SHEET_TYPE_MEAN3M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "G" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(H" + (i + 4) + "=H" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 7: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=3,\"\",\"DNF\"))"); break; // mean case 9: Cell mean = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); mean.setCellFormula( "IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",ROUND(AVERAGE(" + range + "),2)))"); break; } } else if (SHEET_TYPE_BEST1S.equals(template.getSheetName()) || SHEET_TYPE_BEST1M.equals(template.getSheetName()) || SHEET_TYPE_BEST1N.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(E" + (i + 4) + "=E" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; } } else if (SHEET_TYPE_BEST2S.equals(template.getSheetName()) || SHEET_TYPE_BEST2M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "F" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(G" + (i + 4) + "=G" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 6: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=2,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_BEST3S.equals(template.getSheetName()) || SHEET_TYPE_BEST3M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "G" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(H" + (i + 4) + "=H" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 7: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=3,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_MULTIBF1.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(I" + (i + 4) + "=I" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // result case 8: Cell result = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result.setCellFormula("IF(E" + (i + 5) + "-F" + (i + 5) + ">F" + (i + 5) + ",-1,(99-F" + (i + 5) + "+E" + (i + 5) + "-F" + (i + 5) + ")*10000000+G" + (i + 5) + "*100+E" + (i + 5) + "-F" + (i + 5) + ")"); break; } } else if (SHEET_TYPE_MULTIBF2.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(I" + (i + 4) + "=I" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // result1 case 7: Cell result1 = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result1.setCellFormula("IF(E" + (i + 5) + "=\"DNS\",-2,IF(E" + (i + 5) + "-F" + (i + 5) + ">F" + (i + 5) + ",-1,(99-F" + (i + 5) + "+E" + (i + 5) + "-F" + (i + 5) + ")*10000000+G" + (i + 5) + "*100+E" + (i + 5) + "-F" + (i + 5) + "))"); break; // result2 case 11: Cell result2 = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result2.setCellFormula("IF(I" + (i + 5) + "=\"DNS\",-2,IF(I" + (i + 5) + "-J" + (i + 5) + ">J" + (i + 5) + ",-1,(99-J" + (i + 5) + "+I" + (i + 5) + "-J" + (i + 5) + ")*10000000+K" + (i + 5) + "*100+I" + (i + 5) + "-J" + (i + 5) + "))"); break; // best case 12: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(AND(H" + (i + 5) + "<0,L" + (i + 5) + "<0),-1,IF(" + (i + 5) + "<0,L" + (i + 5) + ",IF(L" + (i + 5) + "<0,H" + (i + 5) + ",MIN(H" + (i + 5) + ",L" + (i + 5) + "))))"); break; } } else { log.error("Unsupported sheet type: {}", template.getSheetName()); } // set cell style Row row = resultSheet.getRow(i + 4); if (row != null) { Cell cell = row.getCell(j); if (cell != null) { cell.setCellStyle(cellStyles.get(j)); } } } } // fill sheet with competitors for this event if (includeCompetitors) { try { generateCompetitorRows(workBook, resultSheet, competition.getCompetitorsByEvent(event), 4); } catch (Exception e) { log.error("[{}] " + e.getLocalizedMessage(), e); throw new RuntimeException("Could not include competitors in this sheet.", e); } } }
From source file:edu.ucsd.hep.roofitexplorer.ExcelWorkbookProducer.java
License:Apache License
/** make a spreadsheet from the table model. Note that this does NOT * take into account any sorting / filtering applied when displaying * the table./*ww w . ja v a 2 s. c o m*/ * @param model * @param sheet_name * @return */ public static Workbook makeHSSFWorkbook(TableModel model, String sheet_name) { Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet(); wb.setSheetName(0, sheet_name); Cell cell = null; int numCols = model.getColumnCount(); // set title row Row row = sheet.createRow(0); for (int col = 0; col < numCols; ++col) { cell = row.createCell(col); cell.setCellValue(model.getColumnName(col)); } // now fill in data content int first_data_row_num = 2; int num_rows = model.getRowCount(); for (int rowIndex = 0; rowIndex < num_rows; rowIndex++) { row = sheet.createRow(rowIndex + first_data_row_num); for (int col = 0; col < numCols; ++col) { cell = row.createCell(col); Object value = model.getValueAt(rowIndex, col); if (value == null) continue; if (value instanceof Double) cell.setCellValue((Double) value); else cell.setCellValue(value.toString()); } // loop over all columns of this row } // loop over all data rows return wb; }
From source file:edu.ucsd.hep.roofitexplorer.ExcelWorkbookProducer.java
License:Apache License
/** similar to the above but running on the table itself (i.e. should * take into account filtering and sorting) *///from ww w . ja v a2 s .c o m public static Workbook makeHSSFWorkbook(JTable table, String sheetName) { Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet(); wb.setSheetName(0, sheetName); Cell cell = null; int num_columns = table.getColumnCount(); // set title row Row row = sheet.createRow(0); for (int col = 0; col < num_columns; ++col) { cell = row.createCell(col); cell.setCellValue(table.getColumnName(col)); } // now fill in data content int first_data_row_num = 2; int numRows = table.getRowCount(); for (int rowIndex = 0; rowIndex < numRows; ++rowIndex) { row = sheet.createRow(rowIndex + first_data_row_num); for (int col = 0; col < num_columns; ++col) { cell = row.createCell(col); Object value = table.getValueAt(rowIndex, col); if (value == null) continue; if (value instanceof Double) cell.setCellValue((Double) value); else cell.setCellValue(value.toString()); } // loop over all columns of this row } // loop over all data rows return wb; }
From source file:eu.esdihumboldt.hale.io.xls.writer.XLSAlignmentMappingWriter.java
License:Open Source License
@Override protected IOReport execute(ProgressIndicator progress, IOReporter reporter) throws IOProviderConfigurationException, IOException { super.execute(progress, reporter); Workbook workbook; // write xls file if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xls")) { workbook = new HSSFWorkbook(); }/*from w w w.java 2 s. c o m*/ // write xlsx file else if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xlsx")) { workbook = new XSSFWorkbook(); } else { reporter.error(new IOMessageImpl("Content type is invalid!", null)); reporter.setSuccess(false); return reporter; } Sheet sheet = workbook.createSheet(); workbook.setSheetName(0, "Mapping table"); Row row = null; Cell cell = null; // create cell style of the header CellStyle headerStyle = XLSCellStyles.getHeaderStyle(workbook); // create cell style CellStyle cellStyle = XLSCellStyles.getNormalStyle(workbook, false); // create highlight style for type cells CellStyle highlightStyle = XLSCellStyles.getHighlightedStyle(workbook, false); // create disabled style CellStyle disabledStyle = XLSCellStyles.getNormalStyle(workbook, true); // create disabled highlight style CellStyle disabledTypeStyle = XLSCellStyles.getHighlightedStyle(workbook, true); List<Map<CellType, CellInformation>> mapping = getMappingList(); // determine if cells are organized by type cell boolean byTypeCell = isByTypeCell(); int rownum = 0; // write header row = sheet.createRow(rownum++); for (int i = 0; i < getMappingHeader().size(); i++) { cell = row.createCell(i); cell.setCellValue(getMappingHeader().get(i)); cell.setCellStyle(headerStyle); } // write all mappings for (Map<CellType, CellInformation> entry : mapping) { boolean disabled = false; if (getParameter(TRANSFORMATION_AND_DISABLED_FOR).as(Boolean.class)) { List<String> transformationDisabled = entry.get(CellType.TRANSFORMATION_AND_DISABLED).getText(); disabled = !transformationDisabled.isEmpty() && !transformationDisabled.contains(TransformationMode.active.displayName()); } // create a row row = sheet.createRow(rownum); CellStyle rowStyle = cellStyle; String targetProp = getCellValue(entry, CellType.TARGET_PROPERTIES); boolean isTypeCell = targetProp == null || targetProp.isEmpty(); if (isTypeCell && byTypeCell) { // organized by type cells and this is a type cell if (disabled) { // disabled type cell rowStyle = disabledTypeStyle; } else { // normal type cell rowStyle = highlightStyle; } } else if (disabled) { // disabled property cell rowStyle = disabledStyle; } List<CellType> celltypes = getCellTypes(); for (int i = 0; i < celltypes.size(); i++) { cell = row.createCell(i); cell.setCellValue(getCellValue(entry, celltypes.get(i))); cell.setCellStyle(rowStyle); } rownum++; } // could be integrated in configuration page // int maxColWidth = calculateWidth(getParameter(MAX_COLUMN_WIDTH).as(Integer.class)); int maxColWidth = calculateWidth(maxWidth); // autosize all columns for (int i = 0; i < getMappingHeader().size(); i++) { sheet.autoSizeColumn(i); if (sheet.getColumnWidth(i) > maxColWidth) sheet.setColumnWidth(i, maxColWidth); } // write file FileOutputStream out = new FileOutputStream(getTarget().getLocation().getPath()); workbook.write(out); out.close(); reporter.setSuccess(true); return reporter; }
From source file:eu.esdihumboldt.hale.io.xls.writer.XLSLookupTableWriter.java
License:Open Source License
/** * @see eu.esdihumboldt.hale.common.core.io.impl.AbstractIOProvider#execute(eu.esdihumboldt.hale.common.core.io.ProgressIndicator, * eu.esdihumboldt.hale.common.core.io.report.IOReporter) *///from w ww . j a v a 2s. c o m @Override protected IOReport execute(ProgressIndicator progress, IOReporter reporter) throws IOProviderConfigurationException, IOException { Workbook workbook; // write xls file if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xls")) { workbook = new HSSFWorkbook(); } // write xlsx file else if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xlsx")) { workbook = new XSSFWorkbook(); } else { reporter.error(new IOMessageImpl("Content type is invalid!", null)); reporter.setSuccess(false); return reporter; } Sheet sheet = workbook.createSheet(); workbook.setSheetName(0, "Lookup table"); Row row = null; Cell cell = null; DataFormat df = workbook.createDataFormat(); // create cell style of the header CellStyle headerStyle = workbook.createCellStyle(); Font headerFont = workbook.createFont(); // use bold font headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerStyle.setFont(headerFont); // set a medium border headerStyle.setBorderBottom(CellStyle.BORDER_MEDIUM); // set cell data format to text headerStyle.setDataFormat(df.getFormat("@")); // create cell style CellStyle rowStyle = workbook.createCellStyle(); // set thin border around the cell rowStyle.setBorderBottom(CellStyle.BORDER_THIN); rowStyle.setBorderLeft(CellStyle.BORDER_THIN); rowStyle.setBorderRight(CellStyle.BORDER_THIN); // set cell data format to text rowStyle.setDataFormat(df.getFormat("@")); // display multiple lines rowStyle.setWrapText(true); Map<Value, Value> table = getLookupTable().getTable().asMap(); int rownum = 0; // write header row = sheet.createRow(rownum++); cell = row.createCell(0); cell.setCellValue(getParameter(LookupTableExportConstants.PARAM_SOURCE_COLUMN).as(String.class)); cell.setCellStyle(headerStyle); cell = row.createCell(1); cell.setCellValue(getParameter(LookupTableExportConstants.PARAM_TARGET_COLUMN).as(String.class)); cell.setCellStyle(headerStyle); for (Value key : table.keySet()) { // create a row row = sheet.createRow(rownum); cell = row.createCell(0); cell.setCellValue(key.as(String.class)); cell.setCellStyle(rowStyle); Value entry = table.get(key); cell = row.createCell(1); cell.setCellValue(entry.as(String.class)); cell.setCellStyle(rowStyle); rownum++; } // write file FileOutputStream out = new FileOutputStream(getTarget().getLocation().getPath()); workbook.write(out); out.close(); reporter.setSuccess(true); return reporter; }