Example usage for org.apache.poi.ss.usermodel Sheet getWorkbook

List of usage examples for org.apache.poi.ss.usermodel Sheet getWorkbook

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getWorkbook

Introduction

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

Prototype

Workbook getWorkbook();

Source Link

Document

Return the parent workbook

Usage

From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.SubmissionProjectSummaryXLS.java

License:Open Source License

/**
 * This method is used to add a project with its corresponding gender contribution
 * //  ww  w .  j ava  2s  . co  m
 * @param sheet is the workbook sheet where the information is going to be presented
 * @param informationList is the list with the projects related to each institution
 */
private void addContent(List<Map<String, Object>> informationList, Sheet sheet) {

    int projectID;
    CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();
    Map<String, Object> projectMap;
    XSSFHyperlink link;
    DateFormat formatter = new SimpleDateFormat(APConstants.DATE_TIME_FORMAT_TIMEZONE);
    // ************************* Project Level Submission Project ***********************
    for (int i = 0; i < informationList.size(); i++) {
        projectMap = informationList.get(i);

        projectID = (int) projectMap.get("project_id");
        link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
        link.setAddress(config.getBaseUrl() + "/planning/projects/description.do?projectID=" + projectID);

        // Project id
        xls.writeHyperlink(sheet, "P" + String.valueOf(projectID), link);
        xls.nextColumn();

        xls.writeString(sheet, (String) projectMap.get("project_title"));
        xls.nextColumn();

        xls.writeString(sheet, (String) projectMap.get("project_summary"));
        xls.nextColumn();

        xls.writeString(sheet, (String) projectMap.get("project_type"));
        xls.nextColumn();

        xls.writeString(sheet, (String) projectMap.get("submmited_by"));
        xls.nextColumn();

        xls.writeString(sheet, formatter.format((Date) projectMap.get("submmited_on")));

        xls.nextRow();
    }
}

From source file:org.cytoscape.tableimport.internal.reader.ExcelAttributeSheetReader.java

License:Open Source License

public ExcelAttributeSheetReader(final Sheet sheet, final AttributeMappingParameters mapping,
        final CyServiceRegistrar serviceRegistrar) {
    this.sheet = sheet;
    this.mapping = mapping;
    this.startLineNumber = mapping.getStartLineNumber();
    this.parser = new AttributeLineParser(mapping, serviceRegistrar);
    this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
    this.formatter = new DataFormatter();
}

From source file:org.cytoscape.tableimport.internal.reader.ExcelNetworkSheetReader.java

License:Open Source License

/**
 * Creates a new ExcelNetworkSheetReader object.
 *///ww  w .  j  a v  a  2s.c  o m
public ExcelNetworkSheetReader(final String networkName, final Sheet sheet,
        final NetworkTableMappingParameters nmp, final Map<Object, CyNode> nMap,
        final CyRootNetwork rootNetwork, final CyServiceRegistrar serviceRegistrar) {
    super(networkName, null, nmp, nMap, rootNetwork, serviceRegistrar);
    this.sheet = sheet;
    this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
    this.formatter = new DataFormatter();
}

From source file:org.cytoscape.tableimport.internal.ui.PreviewTablePanel.java

License:Open Source License

private PreviewTableModel parseExcel(final Sheet sheet, int startLine) throws IOException {
    int size = getPreviewSize();

    if (size == -1)
        size = Integer.MAX_VALUE;

    int maxCol = 0;
    final Vector<Vector<String>> data = new Vector<>();

    int rowCount = 0;
    int validRowCount = 0;
    FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
    DataFormatter formatter = new DataFormatter();
    Row row;/*from  w  ww. j av  a2  s  .  c om*/

    while (((row = sheet.getRow(rowCount)) != null) && (validRowCount < size)) {
        if (rowCount >= startLine) {
            final Vector<String> rowVector = new Vector<>();

            if (maxCol < row.getLastCellNum())
                maxCol = row.getLastCellNum();

            for (short j = 0; j < maxCol; j++) {
                Cell cell = row.getCell(j);
                if (cell == null || cell.getCellType() == Cell.CELL_TYPE_ERROR
                        || (cell.getCellType() == Cell.CELL_TYPE_FORMULA
                                && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR)) {
                    rowVector.add(null);
                } else {
                    rowVector.add(formatter.formatCellValue(cell, evaluator));
                }
            }

            data.add(rowVector);
            validRowCount++;
        }

        rowCount++;
    }

    final boolean firstRowNames = importType == NETWORK_IMPORT || importType == TABLE_IMPORT;

    return new PreviewTableModel(data, new Vector<String>(), firstRowNames);
}

From source file:org.databene.platform.xls.SingleSheetXLSEntityIterator.java

License:Open Source License

public SingleSheetXLSEntityIterator(Sheet sheet, Converter<String, ?> preprocessor,
        ComplexTypeDescriptor entityDescriptor, BeneratorContext context, boolean rowBased, boolean formatted,
        String emptyMarker) {// ww  w.jav  a  2 s .c o m
    this.workbook = sheet.getWorkbook();
    this.preprocessor = preprocessor;
    this.context = context;
    this.rowBased = rowBased;
    this.formatted = formatted;
    this.emptyMarker = emptyMarker;
    this.source = createRawIterator(sheet, rowBased, preprocessor);

    // if not specified explicitly, determine entity type by sheet name
    this.entityDescriptor = entityDescriptor;
    if (this.entityDescriptor == null) {
        String entityTypeName = sheet.getSheetName();
        if (context != null) {
            DataModel dataModel = context.getDataModel();
            this.entityDescriptor = (ComplexTypeDescriptor) dataModel.getTypeDescriptor(entityTypeName);
            if (this.entityDescriptor != null)
                this.entityDescriptor = new ComplexTypeDescriptor(entityTypeName + "_",
                        context.getLocalDescriptorProvider());
            else
                this.entityDescriptor = createDescriptor(entityTypeName);
        } else
            this.entityDescriptor = createDescriptor(entityTypeName);
    }

    // parse headers
    parseHeaders();
    if (headers == null) {
        this.source = null; // empty sheet
        return;
    }

    // parse first data row
    DataContainer<Object[]> tmp = this.source.next(sourceContainer.get());
    if (tmp == null) {
        this.source = null; // no data in sheet
        return;
    }
    this.buffer = tmp.getData();
    converter = new Array2EntityConverter(this.entityDescriptor, headers, false);
}

From source file:org.drools.decisiontable.parser.xls.ExcelParser.java

License:Apache License

private void processSheet(Sheet sheet, List<? extends DataListener> listeners) {
    int maxRows = sheet.getLastRowNum();

    CellRangeAddress[] mergedRanges = getMergedCells(sheet);
    DataFormatter formatter = new DataFormatter(Locale.ENGLISH);
    FormulaEvaluator formulaEvaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();

    for (int i = 0; i <= maxRows; i++) {
        Row row = sheet.getRow(i);/*from  w w w  .ja v  a2s  .  c  o m*/
        int lastCellNum = row != null ? row.getLastCellNum() : 0;
        newRow(listeners, i, lastCellNum);

        for (int cellNum = 0; cellNum < lastCellNum; cellNum++) {
            Cell cell = row.getCell(cellNum);
            if (cell == null) {
                continue;
            }
            double num = 0;

            CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges);

            if (merged != null) {
                Cell topLeft = sheet.getRow(merged.getFirstRow()).getCell(merged.getFirstColumn());
                newCell(listeners, i, cellNum, formatter.formatCellValue(topLeft), topLeft.getColumnIndex());

            } else {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_FORMULA:
                    String cellValue = null;
                    try {
                        CellValue cv = formulaEvaluator.evaluate(cell);
                        cellValue = getCellValue(cv);
                        newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED);
                    } catch (RuntimeException e) {
                        // This is thrown if an external link cannot be resolved, so try the cached value
                        log.warn("Cannot resolve externally linked value: " + formatter.formatCellValue(cell));
                        String cachedValue = tryToReadCachedValue(cell);
                        newCell(listeners, i, cellNum, cachedValue, DataListener.NON_MERGED);
                    }
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    num = cell.getNumericCellValue();
                default:
                    if (num - Math.round(num) != 0) {
                        newCell(listeners, i, cellNum, String.valueOf(num), DataListener.NON_MERGED);
                    } else {
                        newCell(listeners, i, cellNum, formatter.formatCellValue(cell),
                                DataListener.NON_MERGED);
                    }
                }
            }
        }
    }
    finishSheet(listeners);
}

From source file:org.drugepi.table.ExcelUtils.java

License:Mozilla Public License

public static boolean cellIsBold(Cell cell) {
    if (cell == null)
        return false;

    Row row = cell.getRow();/*from w  w w  . j  a  v  a  2  s .c om*/
    Sheet sheet = row.getSheet();
    Workbook workbook = sheet.getWorkbook();
    Font font = workbook.getFontAt(cell.getCellStyle().getFontIndex());

    if (font.getBoldweight() == Font.BOLDWEIGHT_BOLD)
        return true;

    return false;
}

From source file:org.eclipse.lyo.samples.excel.adapter.dao.internal.ExcelDaoImpl.java

License:Open Source License

private Cell getNamedCell(Sheet sheet, String cellRowString, int defaultRowIndex) {
    Name name = sheet.getWorkbook().getName(cellRowString);
    if (name != null) {
        AreaReference areaRef = new AreaReference(name.getRefersToFormula());
        CellReference firstCell = areaRef.getFirstCell();
        CellReference lastCell = areaRef.getLastCell();
        int rowIndex = defaultRowIndex;
        if (rowIndex < firstCell.getRow() || lastCell.getRow() < rowIndex) {
            rowIndex = firstCell.getRow();
        }/*from ww  w.ja  v  a 2s . c om*/
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            return row.getCell(firstCell.getCol());
        }
    }
    return null;
}

From source file:org.generationcp.middleware.util.PoiUtil.java

License:Open Source License

public static void setCellAlignment(final Sheet sheet, final int rowIndex, final int columnIndex,
        final short alignment) {
    final Cell cell = PoiUtil.getCell(sheet, columnIndex, rowIndex);

    if (cell == null) {
        throw new IllegalArgumentException(
                "Cell with col=" + columnIndex + " and row=" + rowIndex + " is null.");
    }// w  w  w  .  j a  va2 s.  c om

    CellUtil.setAlignment(cell, sheet.getWorkbook(), alignment);
}

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

@SuppressWarnings("rawtypes")
private static void writeHead(boolean useTemplate, Sheet sheet, ExcelWriteSheetProcessor sheetProcessor) {
    Integer headRowIndex = sheetProcessor.getHeadRowIndex();
    if (headRowIndex == null) {
        return;/*from w w w .  ja  v  a  2  s.  co m*/
    }
    Workbook wookbook = sheet.getWorkbook();
    // use theme
    CellStyle style = null;
    if (!useTemplate && sheetProcessor.getTheme() != null) {
        int theme = sheetProcessor.getTheme();
        if (theme == ExcelWriteTheme.BASE) {
            style = wookbook.createCellStyle();
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
            style.setFillForegroundColor((short) 44);
            style.setBorderBottom(CellStyle.BORDER_THIN);
            style.setBorderLeft(CellStyle.BORDER_THIN);
            style.setBorderRight(CellStyle.BORDER_THIN);
            style.setBorderTop(CellStyle.BORDER_THIN);
            // style.setBottomBorderColor((short) 44);
            style.setAlignment(CellStyle.ALIGN_CENTER);
        }
        // freeze Pane
        if (sheetProcessor.getHeadRowIndex() != null && sheetProcessor.getHeadRowIndex() == 0) {
            sheet.createFreezePane(0, 1, 0, 1);
        }
    }

    Row row = sheet.getRow(headRowIndex);
    if (row == null) {
        row = sheet.createRow(headRowIndex);
    }
    for (Map.Entry<String, Map<Integer, ExcelWriteFieldMappingAttribute>> entry : sheetProcessor
            .getFieldMapping().export().entrySet()) {
        Map<Integer, ExcelWriteFieldMappingAttribute> map = entry.getValue();
        if (map != null) {
            for (Map.Entry<Integer, ExcelWriteFieldMappingAttribute> entry2 : map.entrySet()) {
                String head = entry2.getValue().getHead();
                Integer colIndex = entry2.getKey();
                Cell cell = row.getCell(colIndex);
                if (cell == null) {
                    cell = row.createCell(colIndex);
                }
                // use theme
                if (!useTemplate && sheetProcessor.getTheme() != null) {
                    cell.setCellStyle(style);

                }
                cell.setCellValue(head);
            }
        }
    }

}