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

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

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

Introduction

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

Prototype

Row createRow(int rownum);

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

From source file:com.vaadin.addon.spreadsheet.CellSelectionShifter.java

License:Open Source License

private void shiftRowsUpInSelection(int newFirstRow) {
    CellRangeAddress paintedCellRange = spreadsheet.getCellSelectionManager().getSelectedCellRange();
    int r1 = paintedCellRange.getFirstRow() + 1;
    int r2 = paintedCellRange.getLastRow() + 1;
    int c1 = paintedCellRange.getFirstColumn() + 1;
    int c2 = paintedCellRange.getLastColumn() + 1;
    Workbook workbook = spreadsheet.getWorkbook();
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    for (int shiftedRowIndex = r1; shiftedRowIndex <= r2; shiftedRowIndex++) {
        final Row shiftedRow = activeSheet.getRow(shiftedRowIndex - 1);
        int newRowIndex = r1 - 1 - (shiftedRowIndex - r1);
        while (newRowIndex >= newFirstRow) {
            if (shiftedRow != null) {
                Row newRow = activeSheet.getRow(newRowIndex - 1);
                if (newRow == null) {
                    newRow = activeSheet.createRow(newRowIndex - 1);
                }/*  w w w. j av  a2  s .  co  m*/
                for (int c = c1; c <= c2; c++) {
                    Double sequenceIncrement = getColumnSequenceIncrement(c, r1, r2);
                    Cell shiftedCell = shiftedRow.getCell(c - 1);
                    Cell newCell = newRow.getCell(c - 1);
                    if (shiftedCell != null) {
                        if (newCell == null) {
                            newCell = newRow.createCell(c - 1);
                        }
                        shiftCellValue(shiftedCell, newCell, false, sequenceIncrement);
                    } else if (newCell != null) {
                        // update style to 0
                        newCell.setCellStyle(null);
                        spreadsheet.getSpreadsheetStyleFactory().cellStyleUpdated(newCell, true);
                        newCell.setCellValue((String) null);
                        getCellValueManager().cellDeleted(newCell);
                    }
                }
            } else {
                getCellValueManager().removeCells(newRowIndex, c1, newRowIndex, c2, true);
            }
            newRowIndex = newRowIndex - (r2 - r1) - 1;
        }
    }
}

From source file:com.vaadin.addon.spreadsheet.CellValueManager.java

License:Open Source License

/**
 * Updates the cell value and type, causes a recalculation of all the values
 * in the cell./*from  www  . j  a  v a 2s .  c o m*/
 *
 * If there is a {@link CellValueHandler} defined, then it is used.
 *
 * Cells starting with "=" or "+" will be created/changed into FORMULA type.
 *
 * Cells that are existing and are NUMERIC type will be parsed according to
 * their existing format, or if that fails, as Double.
 *
 * Cells not containing any letters and containing at least one number will
 * be created/changed into NUMERIC type (formatting is not changed).
 *
 * Existing Boolean cells will be parsed as Boolean.
 *
 * For everything else and if any of the above fail, the cell will get the
 * STRING type and the value will just be a string, except empty values will
 * cause the cell type to be BLANK.
 *
 * @param col
 *            Column index of target cell, 1-based
 * @param row
 *            Row index of target cell, 1-based
 * @param value
 *            The new value to set to the target cell, formulas will start
 *            with an extra "=" or "+"
 */
public void onCellValueChange(int col, int row, String value) {
    Workbook workbook = spreadsheet.getWorkbook();
    // update cell value
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    Row r = activeSheet.getRow(row - 1);
    if (r == null) {
        r = activeSheet.createRow(row - 1);
    }
    Cell cell = r.getCell(col - 1);
    String formattedCellValue = null;
    int oldCellType = -1;
    // capture cell value to history
    CellValueCommand command = new CellValueCommand(spreadsheet);
    command.captureCellValues(new CellReference(row - 1, col - 1));
    spreadsheet.getSpreadsheetHistoryManager().addCommand(command);
    boolean updateHyperlinks = false;

    if (getCustomCellValueHandler() == null || getCustomCellValueHandler().cellValueUpdated(cell, activeSheet,
            col - 1, row - 1, value, getFormulaEvaluator(), formatter)) {
        Exception exception = null;
        try {
            // handle new cell creation
            SpreadsheetStyleFactory styler = spreadsheet.getSpreadsheetStyleFactory();
            final Locale spreadsheetLocale = spreadsheet.getLocale();
            if (cell == null) {
                cell = r.createCell(col - 1);
            } else {
                // modify existing cell, possibly switch type
                formattedCellValue = getFormattedCellValue(cell);
                final String key = SpreadsheetUtil.toKey(col, row);
                oldCellType = cell.getCellType();
                if (!sentCells.remove(key)) {
                    sentFormulaCells.remove(key);
                }

                // Old value was hyperlink => needs refresh
                if (cell.getCellType() == Cell.CELL_TYPE_FORMULA
                        && cell.getCellFormula().startsWith("HYPERLINK")) {
                    updateHyperlinks = true;
                }
            }
            if (formulaFormatter.isFormulaFormat(value)) {
                if (formulaFormatter.isValidFormulaFormat(value, spreadsheetLocale)) {
                    spreadsheet.removeInvalidFormulaMark(col, row);
                    getFormulaEvaluator().notifyUpdateCell(cell);
                    cell.setCellType(Cell.CELL_TYPE_FORMULA);
                    cell.setCellFormula(
                            formulaFormatter.unFormatFormulaValue(value.substring(1), spreadsheetLocale));
                    getFormulaEvaluator().notifySetFormula(cell);
                    if (value.startsWith("=HYPERLINK(")
                            && cell.getCellStyle().getIndex() != hyperlinkStyleIndex) {
                        // set the cell style to link cell
                        CellStyle hyperlinkCellStyle;
                        if (hyperlinkStyleIndex == -1) {
                            hyperlinkCellStyle = styler.createHyperlinkCellStyle();
                            hyperlinkStyleIndex = -1;
                        } else {
                            hyperlinkCellStyle = workbook.getCellStyleAt(hyperlinkStyleIndex);
                        }
                        cell.setCellStyle(hyperlinkCellStyle);
                        styler.cellStyleUpdated(cell, true);
                        updateHyperlinks = true;
                    }
                } else {
                    // it's formula but invalid
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue(value);
                    spreadsheet.markInvalidFormula(col, row);
                }
            } else {
                spreadsheet.removeInvalidFormulaMark(col, row);
                Double percentage = SpreadsheetUtil.parsePercentage(value, spreadsheetLocale);
                Double numVal = SpreadsheetUtil.parseNumber(cell, value, spreadsheetLocale);
                if (value.isEmpty()) {
                    cell.setCellType(Cell.CELL_TYPE_BLANK);
                } else if (percentage != null) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    CellStyle cs = cell.getCellStyle();
                    if (cs == null) {
                        cs = workbook.createCellStyle();
                        cell.setCellStyle(cs);
                    }

                    if (cs.getDataFormatString() != null && !cs.getDataFormatString().contains("%")) {
                        cs.setDataFormat(workbook.createDataFormat()
                                .getFormat(spreadsheet.getDefaultPercentageFormat()));
                        styler.cellStyleUpdated(cell, true);
                    }
                    cell.setCellValue(percentage);
                } else if (numVal != null) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(numVal);
                } else if (oldCellType == Cell.CELL_TYPE_BOOLEAN) {
                    cell.setCellValue(Boolean.parseBoolean(value));
                } else {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue(value);
                }
                getFormulaEvaluator().notifyUpdateCell(cell);
            }

        } catch (FormulaParseException fpe) {
            try {
                exception = fpe;

                // parses formula
                cell.setCellFormula(value.substring(1).replace(" ", ""));
            } catch (FormulaParseException fpe2) {
                exception = fpe2;
                /*
                 * We could force storing the formula even if it is invalid.
                 * Instead, just store it as the value. Clearing the formula
                 * makes sure the value is displayed as-is.
                 */
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(value);
                spreadsheet.markInvalidFormula(col, row);
            }
        } catch (NumberFormatException nfe) {
            exception = nfe;
            cell.setCellValue(value);
        } catch (Exception e) {
            exception = e;
            cell.setCellValue(value);
        }
        if (cell != null) {
            markCellForUpdate(cell);
            if (formattedCellValue == null || !formattedCellValue.equals(getFormattedCellValue(cell))
                    || oldCellType != cell.getCellType()) {
                fireCellValueChangeEvent(cell);
            }
        }
        if (exception != null) {
            LOGGER.log(Level.FINE, "Failed to parse cell value for cell at col " + col + " row " + row + " ("
                    + exception.getMessage() + ")", exception);
        }
    }

    spreadsheet.updateMarkedCells();

    if (updateHyperlinks) {
        spreadsheet.loadHyperLinks();
    }
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

/**
 * Creates a new Formula type cell with the given formula.
 * //from   w  w  w.ja  v  a 2  s.  c o m
 * After all editing is done, call {@link #refreshCells(Cell...)()} or
 * {@link #refreshAllCellValues()} to make sure client side is updated.
 * 
 * @param row
 *            Row index of the new cell, 0-based
 * @param col
 *            Column index of the new cell, 0-based
 * @param formula
 *            The formula to set to the new cell (should NOT start with "="
 *            nor "+")
 * @return The newly created cell
 * @throws IllegalArgumentException
 *             If columnIndex < 0 or greater than the maximum number of
 *             supported columns (255 for *.xls, 1048576 for *.xlsx)
 */
public Cell createFormulaCell(int row, int col, String formula) throws IllegalArgumentException {
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    Row r = activeSheet.getRow(row);
    if (r == null) {
        r = activeSheet.createRow(row);
    }
    Cell cell = r.getCell(col);
    if (cell == null) {
        cell = r.createCell(col, Cell.CELL_TYPE_FORMULA);
    } else {
        final String key = SpreadsheetUtil.toKey(col + 1, row + 1);
        valueManager.clearCellCache(key);
        cell.setCellType(Cell.CELL_TYPE_FORMULA);
    }
    cell.setCellFormula(formula);
    valueManager.cellUpdated(cell);
    return cell;
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

/**
 * Create a new cell (or replace existing) with the given value, the type of
 * the value parameter will define the type of the cell. The value may be of
 * the following types: Boolean, Calendar, Date, Double or String. The
 * default type will be String, value of ({@link #toString()} will be given
 * as the cell value.//  ww w  .  j  ava  2 s  .com
 * 
 * For formula cells, use {@link #createFormulaCell(int, int, String)}.
 * 
 * After all editing is done, call {@link #refreshCells(Cell...)} or
 * {@link #refreshAllCellValues()} to make sure the client side is updated.
 * 
 * @param row
 *            Row index of the new cell, 0-based
 * @param col
 *            Column index of the new cell, 0-based
 * @param value
 *            Object representing the type and value of the Cell
 * @return The newly created cell
 * @throws IllegalArgumentException
 *             If columnIndex < 0 or greater than the maximum number of
 *             supported columns (255 for *.xls, 1048576 for *.xlsx)
 */
public Cell createCell(int row, int col, Object value) throws IllegalArgumentException {
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    Row r = activeSheet.getRow(row);
    if (r == null) {
        r = activeSheet.createRow(row);
    }
    Cell cell = r.getCell(col);
    if (cell == null) {
        cell = r.createCell(col);
    } else {
        final String key = SpreadsheetUtil.toKey(col + 1, row + 1);
        valueManager.clearCellCache(key);
    }
    if (value instanceof Double) {
        cell.setCellValue((Double) value);
    } else if (value instanceof Boolean) {
        cell.setCellValue((Boolean) value);
    } else if (value instanceof Date) {
        cell.setCellValue((Date) value);
    } else if (value instanceof Calendar) {
        cell.setCellValue((Calendar) value);
    } else {
        cell.setCellValue(value.toString());
    }
    valueManager.cellUpdated(cell);
    return cell;
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

/**
 * Hides or shows the given row, see {@link Row#setZeroHeight(boolean)}.
 * /* w  w w  .j  a v  a 2  s.  c o  m*/
 * @param rowIndex
 *            Index of the target row, 0-based
 * @param hidden
 *            True to hide the target row, false to show it.
 */
public void setRowHidden(int rowIndex, boolean hidden) {
    final Sheet activeSheet = getActiveSheet();
    Row row = activeSheet.getRow(rowIndex);
    if (row == null) {
        row = activeSheet.createRow(rowIndex);
    }
    row.setZeroHeight(hidden);
    if (hidden && !getState().hiddenRowIndexes.contains(rowIndex + 1)) {
        getState().hiddenRowIndexes.add(rowIndex + 1);
        getState().rowH[rowIndex] = 0.0F;
    } else if (!hidden && getState().hiddenRowIndexes.contains(rowIndex + 1)) {
        getState().hiddenRowIndexes.remove(getState().hiddenRowIndexes.indexOf(rowIndex + 1));
        getState().rowH[rowIndex] = row.getHeightInPoints();
    }

    if (hasSheetOverlays()) {
        reloadImageSizesFromPOI = true;
        loadOrUpdateOverlays();
    }
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java

License:Open Source License

/**
 * Sets the size, default row height and default column width for the given
 * new Sheet in the target Spreadsheet. Finally loads the sheet.
 *
 * @param spreadsheet/*from  w ww.  j  a v  a2  s . c  o  m*/
 *            Target spreadsheet
 * @param sheet
 *            Target sheet
 * @param rows
 *            Amount of rows
 * @param columns
 *            Amount of columns
 */
static void generateNewSpreadsheet(final Spreadsheet spreadsheet, final Sheet sheet, int rows, int columns) {
    sheet.createRow(rows - 1).createCell(columns - 1);
    setDefaultRowHeight(spreadsheet, sheet);
    // use excel default column width instead of Apache POI default (8)
    sheet.setDefaultColumnWidth(DEFAULT_COL_WIDTH_UNITS);
    reloadSpreadsheetData(spreadsheet, sheet);
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetHandlerImpl.java

@Override
public void onPaste(String text) {
    Workbook workbook = spreadsheet.getWorkbook();
    Sheet activesheet = workbook.getSheetAt(workbook.getActiveSheetIndex());

    CellReference selectedCellReference = spreadsheet.getSelectedCellReference();

    String[] lines;//from www  .j  a v a  2 s  .  co m
    if (text.indexOf("\r\n") > -1) {
        lines = text.split("\r\n");
    } else if (text.indexOf("\n") > -1) {
        lines = text.split("\n");
    } else {
        lines = text.split("\r");
    }

    int pasteHeight = lines.length;
    int pasteWidth = 1;
    for (String line : lines) {
        String[] tokens = splitOnTab(line);
        pasteWidth = Math.max(pasteWidth, tokens.length);
    }

    int rowIndex = selectedCellReference.getRow();
    int colIndex = selectedCellReference.getCol();

    // Check for protected cells at target
    for (int i = 0; i < pasteHeight; i++) {
        Row row = activesheet.getRow(rowIndex + i);
        if (row != null) {
            for (int j = 0; j < pasteWidth; j++) {
                Cell cell = row.getCell(colIndex + j);
                if (spreadsheet.isCellLocked(cell)) {
                    protectedCellWriteAttempted();
                    return;
                }
            }
        }
    }

    CellValueCommand command = new CellValueCommand(spreadsheet);
    CellRangeAddress affectedRange = new CellRangeAddress(rowIndex, rowIndex + pasteHeight - 1, colIndex,
            colIndex + pasteWidth - 1);
    command.captureCellRangeValues(affectedRange);

    for (int i = 0; i < pasteHeight; i++) {
        String line = lines[i];
        Row row = activesheet.getRow(rowIndex + i);
        if (row == null) {
            row = activesheet.createRow(rowIndex + i);
        }
        String[] tokens = splitOnTab(line);
        for (int j = 0; j < pasteWidth; j++) {
            Cell cell = row.getCell(colIndex + j);
            if (cell == null) {
                cell = row.createCell(colIndex + j);
            }
            if (j < tokens.length) {
                String cellContent = tokens[j];
                Double numVal = SpreadsheetUtil.parseNumber(cell, cellContent, spreadsheet.getLocale());
                if (numVal != null) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(numVal);
                } else {
                    cell.setCellValue(cellContent);
                }
            } else {
                cell.setCellType(Cell.CELL_TYPE_BLANK);
                spreadsheet.markCellAsDeleted(cell, true);
            }

            spreadsheet.getCellValueManager().markCellForUpdate(cell);
            spreadsheet.getCellValueManager().getFormulaEvaluator().notifyUpdateCell(cell);
        }
    }

    spreadsheet.getSpreadsheetHistoryManager().addCommand(command);
    spreadsheet.updateMarkedCells();
    // re-set selection to copied area
    spreadsheet.setSelectionRange(rowIndex, colIndex, rowIndex + pasteHeight - 1, colIndex + pasteWidth - 1);

    fireCellValueChangeEvent(affectedRange);
}

From source file:com.validation.manager.core.tool.requirement.importer.RequirementImporter.java

License:Apache License

public static File exportTemplate() throws FileNotFoundException, IOException, InvalidFormatException {
    File template = new File("Template.xls");
    template.createNewFile();// ww w .j a va  2  s.co m
    org.apache.poi.ss.usermodel.Workbook wb = new HSSFWorkbook();
    org.apache.poi.ss.usermodel.Sheet sheet = wb.createSheet();
    wb.setSheetName(0, "Requirements");
    int column = 0;
    CellStyle cs = wb.createCellStyle();
    cs.setDataFormat(getBuiltinFormat("text"));
    Font f = wb.createFont();
    f.setFontHeightInPoints((short) 12);
    f.setBold(true);
    f.setColor((short) Font.COLOR_NORMAL);
    cs.setFont(f);
    Row newRow = sheet.createRow(0);
    for (String label : COLUMNS) {
        Cell newCell = newRow.createCell(column);
        newCell.setCellStyle(cs);
        newCell.setCellValue(label);
        column++;
    }

    try (FileOutputStream out = new FileOutputStream(template)) {
        wb.write(out);
        out.close();
    } catch (FileNotFoundException e) {
        LOG.log(Level.SEVERE, null, e);
    } catch (IOException e) {
        LOG.log(Level.SEVERE, null, e);
    }
    return template;
}

From source file:com.validation.manager.core.tool.step.importer.StepImporter.java

License:Apache License

public static File exportTemplate() throws FileNotFoundException, IOException, InvalidFormatException {
    File template = new File("Template.xls");
    template.createNewFile();/*from   ww  w  .j a v  a2 s.c  o  m*/
    org.apache.poi.ss.usermodel.Workbook wb = new HSSFWorkbook();
    org.apache.poi.ss.usermodel.Sheet sheet = wb.createSheet();
    wb.setSheetName(0, "Steps");
    int column = 0;
    CellStyle cs = wb.createCellStyle();
    cs.setDataFormat(getBuiltinFormat("text"));
    Font f = wb.createFont();
    f.setFontHeightInPoints((short) 12);
    f.setBold(true);
    f.setColor((short) Font.COLOR_NORMAL);
    cs.setFont(f);
    Row newRow = sheet.createRow(0);
    for (String label : COLUMNS) {
        Cell newCell = newRow.createCell(column);
        newCell.setCellStyle(cs);
        newCell.setCellValue(label);
        column++;
    }

    try (FileOutputStream out = new FileOutputStream(template)) {
        wb.write(out);
        out.close();
    } catch (FileNotFoundException e) {
        LOG.log(Level.SEVERE, null, e);
    } catch (IOException e) {
        LOG.log(Level.SEVERE, null, e);
    }
    return template;
}

From source file:com.vincestyling.apkinfoextractor.core.export.ExportToExcel.java

License:Apache License

@Override
public void export() throws Exception {
    Workbook wb = new HSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet(Constancts.APP_NAME);
    sheet.setHorizontallyCenter(true);/*from   ww  w .j a  va2  s .  co m*/
    sheet.setFitToPage(true);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue(
            "  File generated by ApkInfoExtractor (https://github.com/vince-styling/ApkInfoExtractor), Copyright (C) 2014 Vince Styling");
    titleCell.setCellStyle(styles.get("title"));

    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);

    int cellNum = 0;
    String[] fields = solution.getExtractFields().split(",");
    for (String field : fields) {
        if (field.equals(Constancts.ICON))
            continue;
        Cell headerCell = headerRow.createCell(cellNum);
        headerCell.setCellValue(field);
        headerCell.setCellStyle(styles.get("header"));
        sheet.setColumnWidth(cellNum, ApkInfo.getFieldCharacterCount(field) * 256);
        cellNum++;
    }

    int rowNum = 2;
    for (int i = 0; i < solution.getResultCount(); i++) {
        ResultDataProvider provider = solution.getResultList().get(i);
        postProgress(i + 1);

        cellNum = 0;
        Row row = sheet.createRow(rowNum++);
        for (String field : fields) {
            if (field.equals(Constancts.ICON))
                continue;
            Cell cell = row.createCell(cellNum);
            cell.setCellStyle(styles.get("cell"));
            String value = getFieldValue(provider.getApkInfo(), field);
            cell.setCellValue(value);
            cellNum++;
        }
        row.setHeight((short) (5 * 256));
    }

    File outputFile = new File(solution.getWorkingFolder(), solution.generateOutputFileName() + ".xls");
    FileOutputStream out = new FileOutputStream(outputFile);
    wb.write(out);
    out.close();

    callback.onProcessSuccess(outputFile);
}