Example usage for org.apache.poi.ss.usermodel Workbook getSheet

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook getSheet.

Prototype

Sheet getSheet(String name);

Source Link

Document

Get sheet with the given name

Usage

From source file:de.enerko.reports2.engine.Report.java

License:Apache License

/**
 * Create a new {@link Sheet} if the sheet with the given name doesn't exist,
 * otherwise returns the existing sheet.
 * @param workbook/* www .j av a 2  s .  c om*/
 * @param name
 * @return Existing or newly created sheet
 */
private Sheet getSheet(final Workbook workbook, final String name) {
    final String validName = name.replaceAll("[\\\\/\\?\\*\\[\\]]", "_");
    Sheet sheet = workbook.getSheet(validName);
    if (sheet == null)
        sheet = workbook.createSheet(validName);
    return sheet;
}

---

From source file:de.enerko.reports2.engine.Report.java

License:Apache License

/**
 * This method adds a new cell to the sheet of a workbook. It could 
 * (together with {@link #fill(Workbook, Cell, String, String, boolean)}) be moved to
 * the {@link CellDefinition} itself, but that would mean that the {@link CellDefinition} is
 * tied to a specific Excel API. Having those methods here allows the Report to become
 * an interface if a second engine (i.e. JXL) should be added in the future.
 * @param workbook/*  w w w .  j  a v a 2  s.  c om*/
 * @param sheet
 * @param cellDefinition
 */
private void addCell(final Workbook workbook, final Sheet sheet, final CellDefinition cellDefinition) {
    final int columnNum = cellDefinition.column, rowNum = cellDefinition.row;

    Row row = sheet.getRow(rowNum);
    if (row == null)
        row = sheet.createRow(rowNum);

    Cell cell = row.getCell(columnNum);
    // If the cell already exists and is no blank cell
    // it will be used including all formating
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell = fill(workbook, cell, cellDefinition, false);
    }
    // Otherwise a new cell will be created, the datatype set and 
    // optionally a format will be created
    else {
        cell = fill(workbook, row.createCell(columnNum), cellDefinition, true);

        final Sheet referenceSheet;
        if (cellDefinition.getReferenceCell() != null
                && (referenceSheet = workbook.getSheet(cellDefinition.getReferenceCell().sheetname)) != null) {
            final Row referenceRow = referenceSheet.getRow(cellDefinition.getReferenceCell().row);
            final Cell referenceCell = referenceRow == null ? null
                    : referenceRow.getCell(cellDefinition.getReferenceCell().column);
            if (referenceCell != null && referenceCell.getCellStyle() != null)
                cell.setCellStyle(referenceCell.getCellStyle());
        }
    }

    // Add an optional comment      
    if (cellDefinition.hasComment()) {
        final CreationHelper factory = workbook.getCreationHelper();

        final Drawing drawing = sheet.createDrawingPatriarch();
        final ClientAnchor commentAnchor = factory.createClientAnchor();

        final int col1 = cellDefinition.comment.column == null ? cell.getColumnIndex() + 1
                : cellDefinition.comment.column;
        final int row1 = cellDefinition.comment.row == null ? cell.getRowIndex() : cellDefinition.comment.row;

        commentAnchor.setCol1(col1);
        commentAnchor.setRow1(row1);
        commentAnchor.setCol2(col1 + Math.max(1, cellDefinition.comment.width));
        commentAnchor.setRow2(row1 + Math.max(1, cellDefinition.comment.height));

        final Comment comment = drawing.createCellComment(commentAnchor);
        comment.setString(factory.createRichTextString(cellDefinition.comment.text));
        comment.setAuthor(cellDefinition.comment.author);
        comment.setVisible(cellDefinition.comment.visible);

        cell.setCellComment(comment);
    }
}

---

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.util.ExcelUtils.java

License:Open Source License

public static String makeSheetNameValid(String originalName, Workbook workbook) {
    String resultName = originalName;
    if (originalName.length() >= SHEET_NAME_MAX_LENGTH) {
        resultName = resultName.substring(0, SHEET_NAME_MAX_LENGTH);
    }//from  ww  w.  j av  a  2 s. co m

    resultName = resultName.replace('[', '(');
    resultName = resultName.replace(']', ')');
    resultName = resultName.replace('*', '+');
    resultName = resultName.replace('/', '-');
    resultName = resultName.replace('\\', '-');
    resultName = resultName.replace('?', '!');
    resultName = resultName.replace(':', ';');

    if (workbook.getSheet(resultName) == null) {
        return resultName;
    }

    String namePrefix = resultName;
    if (namePrefix.length() > SHEET_NAME_MAX_LENGTH - 4) {
        namePrefix = namePrefix.substring(0, SHEET_NAME_MAX_LENGTH - 4);
    }
    for (int count = 2; count < 1000; count++) {
        resultName = namePrefix + "-" + String.format("%03d", Integer.valueOf(count));
        if (workbook.getSheet(resultName) == null) {
            return resultName;
        }
    }
    // very unlikely, needs more than 999 names starting with the same 31 characters
    throw new IteraplanTechnicalException(IteraplanErrorMessages.GENERAL_TECHNICAL_ERROR);
}

---

From source file:de.ks.idnadrev.expimp.xls.XlsxExporterTest.java

License:Apache License

@Test
public void testExportToManyRelation() throws Exception {
    File tempFile = File.createTempFile("taskExportTest", ".xlsx");
    EntityExportSource<Task> tasks = new EntityExportSource<>(PersistentWork.idsFrom(Task.class), Task.class);
    EntityExportSource<Tag> tags = new EntityExportSource<>(PersistentWork.idsFrom(Tag.class), Tag.class);
    XlsxExporter exporter = new XlsxExporter();
    exporter.export(tempFile, tasks, tags);

    Workbook wb = WorkbookFactory.create(tempFile);
    Sheet taskSheet = wb.getSheet(Task.class.getName());
    Sheet tagSheet = wb.getSheet(Tag.class.getName());
    assertNotNull(taskSheet);/*from   w  w  w.j  ava 2s  . c o m*/
    assertNotNull(tagSheet);

    Row firstRow = taskSheet.getRow(0);
    int pos = 0;
    Iterator<Cell> cellIterator = firstRow.cellIterator();

    String property = PropertyPath.property(Task.class, t -> t.getTags());
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        if (cell.getStringCellValue().equals(property)) {
            break;
        }
        pos++;
    }
    assertNotEquals(Task.class.getSimpleName() + "." + property + " not exported", firstRow.getLastCellNum(),
            pos);

    Cell cell = taskSheet.getRow(1).getCell(pos);
    String[] split = StringUtils.split(cell.getStringCellValue(), ToManyColumn.SEPARATOR);
    assertEquals(2, split.length);
    assertTrue(Arrays.asList(split).contains("tag" + ToManyColumn.SEPARATOR_REPLACEMENT + "1"));
    assertTrue(Arrays.asList(split).contains("tag2"));
}

---

From source file:de.quamoco.qm.editor.export.ResultCalibrationImporter.java

License:Apache License

/** Import an Excel file with a certain name. */
public void importFile(String filename, IProgressMonitor monitor) throws IOException {

    FileInputStream in = new FileInputStream(filename);
    Workbook workbook = filename.endsWith("." + EExcelVersion.EXCEL_2007.getExtension()) ? new XSSFWorkbook(in)
            : new HSSFWorkbook(in);

    Sheet measuresSheet = workbook.getSheet("Measures");
    Sheet aggregationWeightSheet = workbook.getSheet("AggregationWeight");
    Sheet aggregationRankSheet = workbook.getSheet("AggregationRank");

    monitor.beginTask("Import " + filename,
            measuresSheet.getRow(0).getLastCellNum() - 1 + aggregationWeightSheet.getRow(0).getLastCellNum() - 1
                    + aggregationRankSheet.getRow(0).getLastCellNum() - 1);

    importMeasureEvaluationCalibration(measuresSheet, monitor);
    importFactorAggregationCalibration(aggregationWeightSheet, QmPackage.eINSTANCE.getRanking_Weight(),
            monitor);//w ww  .j  ava 2  s .  c  om
    importFactorAggregationCalibration(aggregationRankSheet, QmPackage.eINSTANCE.getRanking_Rank(), monitor);

    monitor.done();
}

---

From source file:de.teststory.jspwiki.worksheetplugin.WorksheetPlugin.java

License:Apache License

/**
 * Try to find sheet by id, then by name. Default is first sheet.
 * //from  ww w.  j  a v a  2 s  .  com
 * @param wb
 * @param sheetId
 * @param sheetName
 * @return
 */
protected Sheet findSheet(Workbook wb, int sheetId, String sheetName) {
    Sheet ret = null;
    // sheet ID
    if (sheetId >= 0 && sheetId < wb.getNumberOfSheets()) {
        ret = wb.getSheetAt(sheetId);
    }
    // sheet name
    if (ret == null && sheetName != null) {
        ret = wb.getSheet(sheetName);
    }
    if (ret == null && wb.getNumberOfSheets() > 0) {
        ret = wb.getSheetAt(0);
    }
    return ret;
}

---

From source file:de.topicmapslab.jexc.eXql.grammar.expression.FromExpression.java

License:Apache License

/**
 * {@inheritDoc}/*  ww w.  ja  v  a 2 s.c  om*/
 */
public Collection<Row> interpret(Workbook workBook, Object... input) throws JeXcException {
    /*
     * get sheet by name
     */
    String sheetName = getTokens().get(1).token();
    if (sheetName.startsWith("\"")) {
        sheetName = sheetName.substring(1, sheetName.length() - 1);
    }
    Sheet sheet = workBook.getSheet(sheetName);
    if (sheet == null) {
        return Collections.emptyList();
    }
    Collection<Row> rows = new LinkedList<Row>();
    /*
     * is a TO b syntax
     */
    if (containsToken(ExqlTokens.tokenInstance(To.TOKEN))) {
        if (getNumberOfExpressions() != 2) {
            throw new JeXcException(
                    "Invalid number of numeric indexes by using the keyword 'TO'! Expected 2 but was "
                            + getNumberOfExpressions() + ".");
        }
        int from = Integer.parseInt(getExpressions().get(0).getTokens().get(0).token());
        int to;
        ExqlToken token = getExpressions().get(1).getTokens().get(0);
        if (token instanceof Last) {
            to = sheet.getLastRowNum();
        } else {
            to = Integer.parseInt(token.token());
        }
        for (int i = from; i <= to; i++) {
            Row row = sheet.getRow(i);
            /*
             * ignore non existing cells
             */
            if (row == null) {
                continue;
            }
            rows.add(row);
        }
    }
    /*
     * is index syntax
     */
    else {
        for (ExqlExpression e : getExpressions()) {
            int index = Integer.parseInt(e.getTokens().get(0).token());
            Row row = sheet.getRow(index);
            /*
             * ignore non existing cells
             */
            if (row == null) {
                continue;
            }
            rows.add(row);
        }
    }
    return rows;
}

---

From source file:de.topicmapslab.jexc.utility.JeXcAddress.java

License:Apache License

/**
 * Accessing the cell of the given workbook by the internal represented
 * address./*from  ww  w  . j  av a 2s.  c  o  m*/
 * 
 * @param workbook
 *            the workbook
 * @return the cell and never <code>null</code>
 * @throws JeXcException
 *             thrown if address is invalid for the given workbook
 */
public Cell accessCell(Workbook workbook) throws JeXcException {
    try {
        return workbook.getSheet(sheetName).getRow(rowNumber).getCell(columnNumber);
    } catch (Exception e) {
        throw new JeXcException("Invalid cell address.", e);
    }
}

---

From source file:dk.cubing.liveresults.action.admin.ScoresheetAction.java

License:Open Source License

/**
 * @param workBook/*  w  ww  .j  a va2s .  c  om*/
 * @param format
 * @param timeFormat
 * @return
 */
private Sheet getResultSheet(Workbook workBook, String format, String timeFormat) {
    Sheet sheet = null;
    if (Event.Format.AVERAGE.getValue().equals(format)) {
        if (Event.TimeFormat.SECONDS.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_AVERAGE5S);
        } else if (Event.TimeFormat.MINUTES.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_AVERAGE5M);
        } else if (Event.TimeFormat.NUMBER.getValue().equals(timeFormat)) {
            log.error("Unsupported format: Fewest moves uses Best of and not Average.");
        } else if (Event.TimeFormat.MULTI_BLD.getValue().equals(timeFormat)) {
            log.error("Unsupported format: Multi BLD uses Best of and not Average.");
        } else if (Event.TimeFormat.TEAM.getValue().equals(timeFormat)) {
            log.error("Unsupported format: Team events uses Best of and not Average.");
        } else {
            log.error("Unknown format: {}, time format: {}", format, timeFormat);
        }
    } else if (Event.Format.MEAN.getValue().equals(format)) {
        if (Event.TimeFormat.SECONDS.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_MEAN3S);
        } else if (Event.TimeFormat.MINUTES.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_MEAN3M);
        } else if (Event.TimeFormat.NUMBER.getValue().equals(timeFormat)) {
            log.error("Unsupported format: Fewest moves uses Best of and not Mean.");
        } else if (Event.TimeFormat.MULTI_BLD.getValue().equals(timeFormat)) {
            log.error("Unsupported format: Multi BLD uses Best of and not Mean.");
        } else if (Event.TimeFormat.TEAM.getValue().equals(timeFormat)) {
            log.error("Unsupported format: Team events uses Best of and not Mean.");
        } else {
            log.error("Unknown format: {}, time format: {}", format, timeFormat);
        }
    } else if (Event.Format.BEST_OF_1.getValue().equals(format)) {
        if (Event.TimeFormat.SECONDS.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_BEST1S);
        } else if (Event.TimeFormat.MINUTES.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_BEST1M);
        } else if (Event.TimeFormat.NUMBER.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_BEST1N);
        } else if (Event.TimeFormat.MULTI_BLD.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_MULTIBF1);
        } else if (Event.TimeFormat.TEAM.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_TEAMBEST1M);
        } else {
            log.error("Unknown format: {}, time format: {}", format, timeFormat);
        }
    } else if (Event.Format.BEST_OF_2.getValue().equals(format)) {
        if (Event.TimeFormat.SECONDS.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_BEST2S);
        } else if (Event.TimeFormat.MINUTES.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_BEST2M);
        } else if (Event.TimeFormat.NUMBER.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_BEST2N);
        } else if (Event.TimeFormat.MULTI_BLD.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_MULTIBF2);
        } else if (Event.TimeFormat.TEAM.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_TEAMBEST2M);
        } else {
            log.error("Unknown format: {}, time format: {}", format, timeFormat);
        }
    } else if (Event.Format.BEST_OF_3.getValue().equals(format)) {
        if (Event.TimeFormat.SECONDS.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_BEST3S);
        } else if (Event.TimeFormat.MINUTES.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_BEST3M);
        } else if (Event.TimeFormat.NUMBER.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_BEST3N);
        } else if (Event.TimeFormat.MULTI_BLD.getValue().equals(timeFormat)) {
            log.error("Unsupported format: Multi BLD uses Best of 1 or Best of 2 and not Best of 3.");
        } else if (Event.TimeFormat.TEAM.getValue().equals(timeFormat)) {
            sheet = workBook.getSheet(SHEET_TYPE_TEAMBEST3M);
        } else {
            log.error("Unknown format: {}, time format: {}", format, timeFormat);
        }
    }
    return sheet;
}

---

From source file:dk.cubing.liveresults.action.admin.ScoresheetAction.java

License:Open Source License

/**
 * @param workBook/*from w w  w  .j  a  v  a2 s. c  o  m*/
 * @param competition
 * @throws RuntimeException
 */
private void generateRegistrationSheet(Workbook workBook, Competition competition) throws RuntimeException {
    Sheet sheet = workBook.getSheet(SHEET_TYPE_REGISTRATION);
    if (sheet != null) {
        log.debug("Building registration sheet. Number of competitors: {}",
                competition.getCompetitors().size());

        // competition name
        Cell competitionName = getCell(sheet, 0, 0, Cell.CELL_TYPE_STRING);
        competitionName.setCellValue(competition.getName());

        // competitors data and registered events
        generateCompetitorRows(workBook, sheet, competition.getCompetitors(), 3);
    } else {
        log.error("Could not find sheet: {}", SHEET_TYPE_REGISTRATION);
        throw new RuntimeException("Could not find sheet: " + SHEET_TYPE_REGISTRATION);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• »