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

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheetIndex

Introduction

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

Prototype

int getSheetIndex(Sheet sheet);

Source Link

Document

Returns the index of the given sheet

Usage

From source file:org.jreserve.gui.poi.ExcelUtil.java

License:Open Source License

public static boolean isReferenceName(Workbook wb, Name name) {
    return !name.isFunctionName() && !name.isDeleted() && wb.getSheetIndex(name.getNameName()) < 0;
}

From source file:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java

License:Open Source License

/**
 * Clone the sheet at the passed index and replace values as needed
 *///  w ww  . j  av a  2 s  . c  o m
public Sheet addSheet(Workbook wb, SheetToAdd sheetToAdd, Set<String> usedSheetNames, ReportData reportData,
        ReportDesign design, Map<String, String> repeatSections) {

    String prefix = getExpressionPrefix(design);
    String suffix = getExpressionSuffix(design);

    Sheet sheet = sheetToAdd.getSheet();
    sheet.setForceFormulaRecalculation(true);

    int sheetIndex = wb.getSheetIndex(sheet);

    // Configure the sheet name, replacing any values as needed, and ensuring it is unique for the workbook
    String sheetName = EvaluationUtil.evaluateExpression(sheetToAdd.getOriginalSheetName(),
            sheetToAdd.getReplacementData(), prefix, suffix).toString();
    sheetName = ExcelUtil.formatSheetTitle(sheetName, usedSheetNames);
    wb.setSheetName(sheetIndex, sheetName);
    usedSheetNames.add(sheetName);

    log.debug("Handling sheet: " + sheetName + " at index " + sheetIndex);

    // Iterate across all of the rows in the sheet, and configure all those that need to be added/cloned
    List<RowToAdd> rowsToAdd = new ArrayList<RowToAdd>();

    int totalRows = sheet.getPhysicalNumberOfRows();
    int rowsFound = 0;
    for (int rowNum = 0; rowsFound < totalRows && rowNum < 50000; rowNum++) { // check for < 50000 is a hack to prevent infinite loops in edge cases
        Row currentRow = sheet.getRow(rowNum);
        if (log.isDebugEnabled()) {
            log.debug("Handling row: " + ExcelUtil.formatRow(currentRow));
        }
        if (currentRow != null) {
            rowsFound++;
        }
        // If we find that the row that we are on is a repeating row, then add the appropriate number of rows to clone
        String repeatingRowProperty = getRepeatingRowProperty(sheetToAdd.getOriginalSheetNum(), rowNum,
                repeatSections);
        if (repeatingRowProperty != null) {
            String[] dataSetSpanSplit = repeatingRowProperty.split(",");
            String dataSetName = dataSetSpanSplit[0];
            DataSet dataSet = getDataSet(reportData, dataSetName, sheetToAdd.getReplacementData());

            int numRowsToRepeat = 1;
            if (dataSetSpanSplit.length == 2) {
                numRowsToRepeat = Integer.parseInt(dataSetSpanSplit[1]);
            }
            log.debug("Repeating this row with dataset: " + dataSet + " and repeat of " + numRowsToRepeat);
            int repeatNum = 0;
            for (DataSetRow dataSetRow : dataSet) {
                repeatNum++;
                for (int i = 0; i < numRowsToRepeat; i++) {
                    Row row = (i == 0 ? currentRow : sheet.getRow(rowNum + i));
                    if (repeatNum == 1 && row != null && row != currentRow) {
                        rowsFound++;
                    }
                    Map<String, Object> newReplacements = getReplacementData(sheetToAdd.getReplacementData(),
                            reportData, design, dataSetName, dataSetRow, repeatNum);
                    rowsToAdd.add(new RowToAdd(row, newReplacements));
                    if (log.isDebugEnabled()) {
                        log.debug("Adding " + ExcelUtil.formatRow(row) + " with dataSetRow: " + dataSetRow);
                    }
                }
            }
            if (numRowsToRepeat > 1) {
                rowNum += numRowsToRepeat - 1;
            }
        } else {
            rowsToAdd.add(new RowToAdd(currentRow, sheetToAdd.getReplacementData()));
            if (log.isDebugEnabled()) {
                log.debug("Adding row: " + ExcelUtil.formatRow(currentRow));
            }
        }
    }

    // Now, go through all of the collected rows, and add them back in
    for (int i = 0; i < rowsToAdd.size(); i++) {
        RowToAdd rowToAdd = rowsToAdd.get(i);
        if (rowToAdd.getRowToClone() != null && rowToAdd.getRowToClone().cellIterator() != null) {
            Row addedRow = addRow(wb, sheetToAdd, rowToAdd, i, reportData, design, repeatSections);
            if (log.isDebugEnabled()) {
                log.debug("Wrote row " + i + ": " + ExcelUtil.formatRow(addedRow));
            }
        }
    }

    return sheet;
}

From source file:org.soulwing.jawb.poi.ApachePoiCellReference.java

License:Apache License

/**
 * {@inheritDoc}/*from  w  ww .  ja  v a 2s .c om*/
 */
@Override
public BoundCellReference applyBias(int sheetOffset, int rowOffset, int columnOffset, BoundWorkbook workbook) {

    Workbook wb = ((ApachePoiWorkbook) workbook).getDelegate();

    int sheetIndex = delegate.getSheetName() != null ? wb.getSheetIndex(delegate.getSheetName()) : 0;
    sheetIndex += sheetOffset;
    String sheetName = wb.getSheetName(sheetIndex);

    int rowIndex = delegate.getRow();
    if (!delegate.isRowAbsolute()) {
        rowIndex += rowOffset;
    }

    short columnIndex = delegate.getCol();
    if (!delegate.isColAbsolute()) {
        columnIndex += columnOffset;
    }

    return new ApachePoiCellReference(new CellReference(sheetName, rowIndex, columnIndex,
            delegate.isRowAbsolute(), delegate.isColAbsolute()));
}

From source file:org.spdx.compare.AbstractFileCompareSheet.java

License:Apache License

/**
 * @param wb/*from   w  w w  .j a  v  a  2  s.  c  o  m*/
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName, int columnWidth) {
    int sheetNum = wb.getSheetIndex(sheetName);
    if (sheetNum >= 0) {
        wb.removeSheetAt(sheetNum);
    }
    Sheet sheet = wb.createSheet(sheetName);
    Row row = sheet.createRow(0);
    CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
    CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
    sheet.setColumnWidth(FILENAME_COL, FILENAME_COL_WIDTH * 256);
    sheet.setDefaultColumnStyle(FILENAME_COL, defaultStyle);
    Cell fileNameHeadercell = row.createCell(FILENAME_COL);
    fileNameHeadercell.setCellStyle(headerStyle);
    fileNameHeadercell.setCellValue(FILENAME_TITLE);
    sheet.setColumnWidth(DIFF_COL, DIFF_COL_WIDTH * 256);
    sheet.setDefaultColumnStyle(DIFF_COL, defaultStyle);
    Cell diffHeaderCell = row.createCell(DIFF_COL);
    diffHeaderCell.setCellStyle(headerStyle);
    diffHeaderCell.setCellValue(DIFF_TITLE);

    for (int i = FIRST_DOCUMENT_COL; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS + FIRST_DOCUMENT_COL; i++) {
        sheet.setColumnWidth(i, columnWidth * 256);
        sheet.setDefaultColumnStyle(i, defaultStyle);
        Cell cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
    }
}

From source file:org.spdx.compare.CreatorSheet.java

License:Apache License

/**
 * @param wb/*from  www  .j  a  v a  2s  .  co m*/
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
    int sheetNum = wb.getSheetIndex(sheetName);
    if (sheetNum >= 0) {
        wb.removeSheetAt(sheetNum);
    }
    Sheet sheet = wb.createSheet(sheetName);
    CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
    CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
    Row row = sheet.createRow(0);
    for (int i = 0; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
        sheet.setColumnWidth(i, COL_WIDTH * 256);
        sheet.setDefaultColumnStyle(i, defaultStyle);
        Cell cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
    }
}

From source file:org.spdx.compare.DocumentAnnotationSheet.java

License:Apache License

/**
 * @param wb//from w w  w  . j a  v  a 2s  .  c  o  m
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
    int sheetNum = wb.getSheetIndex(sheetName);
    if (sheetNum >= 0) {
        wb.removeSheetAt(sheetNum);
    }
    Sheet sheet = wb.createSheet(sheetName);
    CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
    CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
    Row row = sheet.createRow(0);
    sheet.setColumnWidth(ANNOTATOR_COL, ANNOTATOR_COL_WIDTH * 256);
    sheet.setDefaultColumnStyle(ANNOTATOR_COL, defaultStyle);
    Cell annotatorHeaderCell = row.createCell(ANNOTATOR_COL);
    annotatorHeaderCell.setCellStyle(headerStyle);
    annotatorHeaderCell.setCellValue(ANNOTATOR_COL_TEXT_TITLE);

    sheet.setColumnWidth(TYPE_COL, TYPE_COL_WIDTH * 256);
    sheet.setDefaultColumnStyle(TYPE_COL, defaultStyle);
    Cell typeHeaderCell = row.createCell(TYPE_COL);
    typeHeaderCell.setCellStyle(headerStyle);
    typeHeaderCell.setCellValue(TYPE_COL_TEXT_TITLE);

    sheet.setColumnWidth(COMMENT_COL, COMMENT_COL_WIDTH * 256);
    sheet.setDefaultColumnStyle(COMMENT_COL, defaultStyle);
    Cell commentHeaderCell = row.createCell(COMMENT_COL);
    commentHeaderCell.setCellStyle(headerStyle);
    commentHeaderCell.setCellValue(COMMENT_COL_TEXT_TITLE);

    for (int i = FIRST_DATE_COL; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
        sheet.setColumnWidth(i, DATE_COL_WIDTH * 256);
        sheet.setDefaultColumnStyle(i, defaultStyle);
        Cell cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
    }
}

From source file:org.spdx.compare.DocumentRelationshipSheet.java

License:Apache License

/**
 * @param wb//from  ww w . jav  a 2s. co m
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
    int sheetNum = wb.getSheetIndex(sheetName);
    if (sheetNum >= 0) {
        wb.removeSheetAt(sheetNum);
    }
    Sheet sheet = wb.createSheet(sheetName);
    CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
    CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
    Row row = sheet.createRow(0);

    sheet.setColumnWidth(TYPE_COL, TYPE_COL_WIDTH * 256);
    sheet.setDefaultColumnStyle(TYPE_COL, defaultStyle);
    Cell typeHeaderCell = row.createCell(TYPE_COL);
    typeHeaderCell.setCellStyle(headerStyle);
    typeHeaderCell.setCellValue(TYPE_COL_TEXT_TITLE);

    for (int i = FIRST_RELATIONSHIP_COL; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
        sheet.setColumnWidth(i, FIRST_RELATIONSHIP_COL_WIDTH * 256);
        sheet.setDefaultColumnStyle(i, defaultStyle);
        Cell cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
    }
}

From source file:org.spdx.compare.DocumentSheet.java

License:Apache License

/**
 * @param wb//  ww  w . ja va 2 s.  co  m
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
    int sheetNum = wb.getSheetIndex(sheetName);
    if (sheetNum >= 0) {
        wb.removeSheetAt(sheetNum);
    }
    Sheet sheet = wb.createSheet(sheetName);
    CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
    CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
    Row row = sheet.createRow(0);
    for (int i = 0; i < HEADER_TITLES.length; i++) {
        sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256);
        sheet.setDefaultColumnStyle(i, defaultStyle);
        Cell cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
        cell.setCellValue(HEADER_TITLES[i]);
    }
}

From source file:org.spdx.compare.ExternalReferencesSheet.java

License:Apache License

/**
 * @param wb/* w ww . j  a  v a  2s.c o  m*/
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
    int sheetNum = wb.getSheetIndex(sheetName);
    if (sheetNum >= 0) {
        wb.removeSheetAt(sheetNum);
    }
    Sheet sheet = wb.createSheet(sheetName);
    CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
    CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
    Row row = sheet.createRow(0);
    sheet.setColumnWidth(NAMESPACE_COL, NAMESPACE_COL_WIDTH * 256);
    sheet.setDefaultColumnStyle(NAMESPACE_COL, defaultStyle);
    Cell extractedHeaderCell = row.createCell(NAMESPACE_COL);
    extractedHeaderCell.setCellStyle(headerStyle);
    extractedHeaderCell.setCellValue(NAMESPACE_TEXT_TITLE);

    sheet.setColumnWidth(CHECKSUM_COL, CHECKSUM_COL_WIDTH * 256);
    sheet.setDefaultColumnStyle(CHECKSUM_COL, defaultStyle);
    Cell checksumHeaderCell = row.createCell(CHECKSUM_COL);
    checksumHeaderCell.setCellStyle(headerStyle);
    checksumHeaderCell.setCellValue(CHECKSUM_TEXT_TITLE);

    for (int i = FIRST_DOC_ID_COL; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
        sheet.setColumnWidth(i, DOC_ID_COL_WIDTH * 256);
        sheet.setDefaultColumnStyle(i, defaultStyle);
        Cell cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
    }
}

From source file:org.spdx.compare.ExtractedLicenseSheet.java

License:Apache License

/**
 * @param wb//from  w ww.ja va 2 s  . com
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
    int sheetNum = wb.getSheetIndex(sheetName);
    if (sheetNum >= 0) {
        wb.removeSheetAt(sheetNum);
    }
    Sheet sheet = wb.createSheet(sheetName);
    CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
    CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
    Row row = sheet.createRow(0);
    sheet.setColumnWidth(EXTRACTED_TEXT_COL, EXTRACTED_TEXT_WIDTH * 256);
    sheet.setDefaultColumnStyle(EXTRACTED_TEXT_COL, defaultStyle);
    Cell extractedHeaderCell = row.createCell(EXTRACTED_TEXT_COL);
    extractedHeaderCell.setCellStyle(headerStyle);
    extractedHeaderCell.setCellValue(EXTRACTED_TEXT_TITLE);
    for (int i = FIRST_LIC_ID_COL; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
        sheet.setColumnWidth(i, LIC_ID_COL_WIDTH * 256);
        sheet.setDefaultColumnStyle(i, defaultStyle);
        Cell cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
    }
}