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

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

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

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:com.abixen.platform.service.businessintelligence.multivisualisation.application.service.file.reader.ExcelReaderService.java

License:Open Source License

private DataFileDto parseWorkbook(final Sheet sheet, final FileParserMessage<DataFileColumn> msg,
        final Boolean readFirstColumnAsColumnName) {
    final DataFileDto dataFileDto = new DataFileDto();
    dataFileDto.setRowTypes(buildRowTypes(sheet.getRow(readFirstColumnAsColumnName ? 1 : 0)));
    final Iterator<Row> rowIterator = sheet.rowIterator();
    while (rowIterator.hasNext()) {
        dataFileDto.addRow(readRowAsRowInMemory(rowIterator.next(), dataFileDto.getRowTypes()));
    }//from  ww w  .  ja va 2s.  c o  m
    return dataFileDto;
}

From source file:com.abixen.platform.service.businessintelligence.multivisualisation.service.impl.parser.ExcelParserServiceImpl.java

License:Open Source License

private void parseWorkbook(Sheet sheet, FileParserMessage<DataFileColumn> msg,
        Boolean readFirstColumnAsColumnName) {
    Row headerRow = sheet.getRow(headerRowIndex);
    if (validate(msg, headerRow, sheet, readFirstColumnAsColumnName)) {
        msg.setData(prepareData(msg, headerRow, sheet, readFirstColumnAsColumnName));
    }/*  w  w w  .  jav a  2 s .  c  o m*/

}

From source file:com.abixen.platform.service.businessintelligence.multivisualisation.service.impl.parser.ExcelParserServiceImpl.java

License:Open Source License

private List<DataFileColumn> getColumns(Row headerRow, Sheet sheet, Boolean readFirstColumnAsColumnName) {
    List<DataFileColumn> dataFileColumns = new ArrayList<>();
    for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
        DataFileColumn dataFileColumn = new DataFileColumn();
        int firstDataRow = readFirstColumnAsColumnName ? headerRowIndex + 1 : headerRowIndex;
        DataValueType columnType = getColumnTypeAsDataValueType(sheet.getRow(firstDataRow).getCell(i), i);
        dataFileColumn.setDataValueType(columnType);
        dataFileColumn.setPosition(i);//ww  w.j  av a 2s  .co  m
        dataFileColumn.setName(getColumnNames(sheet, i, readFirstColumnAsColumnName));
        dataFileColumn.setValues(getValues(sheet, i, columnType, firstDataRow));
        dataFileColumns.add(dataFileColumn);
    }
    return dataFileColumns;
}

From source file:com.abixen.platform.service.businessintelligence.multivisualisation.service.impl.parser.ExcelParserServiceImpl.java

License:Open Source License

private String getColumnNames(Sheet sheet, int i, Boolean readFirstColumnAsColumnName) {
    return readFirstColumnAsColumnName ? sheet.getRow(headerRowIndex).getCell(i).getStringCellValue() : null;
}

From source file:com.abixen.platform.service.businessintelligence.multivisualisation.service.impl.parser.ExcelParserServiceImpl.java

License:Open Source License

private List<DataValue> getValues(Sheet sheet, int i, DataValueType columnType, int firstDataRow) {
    List<DataValue> values = new ArrayList<>();
    for (int j = firstDataRow; j < sheet.getPhysicalNumberOfRows(); j++) {
        Cell cell = sheet.getRow(j).getCell(i);
        values.add(getValueAsDataValue(cell, columnType));
    }/*from  ww w  .j a v a2 s.  com*/
    return values;
}

From source file:com.abixen.platform.service.businessintelligence.multivisualisation.service.impl.parser.ExcelParserServiceImpl.java

License:Open Source License

private boolean validate(FileParserMessage<DataFileColumn> msg, Row headerRow, Sheet sheet,
        Boolean readFirstColumnAsColumnName) {
    for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
        int firstDataRow = readFirstColumnAsColumnName ? headerRowIndex + 1 : headerRowIndex;
        Cell columnCell = sheet.getRow(firstDataRow).getCell(i);
        if (columnCell == null) {
            msg.addFileParseError(new FileParseError(1, String.format(
                    "[Line %d, column %d] Cell is empty. Column can't be validated", headerRowIndex + 1, i)));
        } else {/*from   ww w. j  a v  a2  s  .  c  o m*/
            DataValueType columnType = getColumnTypeAsDataValueType(columnCell, i);
            for (int j = firstDataRow; j < sheet.getPhysicalNumberOfRows(); j++) {
                Cell cell = sheet.getRow(j).getCell(i);
                if (cell == null) {
                    msg.addFileParseError(
                            new FileParseError(1, String.format("[Line %d, column %d] Cell is empty", j, i)));
                } else {
                    if (!columnType.equals(getColumnTypeAsDataValueType(cell, i))) {
                        msg.addFileParseError(new FileParseError(1, String.format(
                                "[Line %d, column %d] Cell type is diffrent that first cell in this column", j,
                                i)));
                    }
                }
            }
        }
    }
    return msg.getFileParseErrors().isEmpty();
}

From source file:com.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

public void populateReport(Study study) throws Exception {
    assert study != null;

    this.study = study;

    initWorkbook();/*from  w w  w . ja  va  2s .  com*/

    //Create the workbook
    populateWorkBook();

    //Post processing
    //Add Table borders (between different styles of cells)
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);

        for (int r = 4; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null)
                continue;

            Row rowUp = sheet.getRow(r - 1);
            Row rowDown = sheet.getRow(r + 1);
            for (int c = 0; c <= row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                Cell cellLeft = c == 0 ? null : row.getCell(c - 1);
                boolean borderLeftAbove = cellLeft != null && cellLeft.getCellStyle().getBorderTop() == 1;
                boolean borderLeftUnder = cellLeft != null && cellLeft.getCellStyle().getBorderBottom() == 1;

                if ((cell != null
                        && cell.getCellStyle().getBorderLeft() + cell.getCellStyle().getBorderRight() > 0)
                        || (cell == null && c + 1 <= row.getLastCellNum() && row.getCell(c + 1) != null)) {
                    if (borderLeftAbove)
                        drawLineAbove(sheet, r, c, c, (short) 1);
                    if (borderLeftUnder)
                        drawLineUnder(sheet, r, c, c, (short) 1);
                }

                if (cell != null) {
                    Font font = wb.getFontAt(cell.getCellStyle().getFontIndex());
                    if (font.getFontHeightInPoints() >= 12)
                        continue;

                    Cell cellUp = rowUp != null && c < rowUp.getLastCellNum() ? rowUp.getCell(c) : null;
                    Cell cellDown = rowDown != null && c < rowDown.getLastCellNum() ? rowDown.getCell(c) : null;

                    if (cellUp == null /*|| (cell.getCellType()!=0 && cellUp.getCellType()!=0 && cellUp.getCellType()!=cell.getCellType())*/ ) {
                        //Border above
                        drawLineAbove(sheet, r, c, c, (short) 1);
                    }
                    if (cellDown == null /*|| (cell.getCellType()!=0 && cellDown.getCellType()!=0 && cellDown.getCellType()!=cell.getCellType())*/) {
                        //Border under
                        drawLineUnder(sheet, r, c, c, (short) 1);
                    }
                }
            }
        }
    }

}

From source file:com.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

protected Cell set(Sheet sheet, int row, int col, Object text, Style style, int rowspan, int colspan) {
    Row r = sheet.getRow(row);
    if (r == null)
        r = sheet.createRow(row);//from w w  w.j av  a 2 s  .  co m
    Cell c = r.getCell(col);
    if (c == null)
        c = r.createCell(col);
    c.setCellStyle(styles.get(style));
    if (text == null) {
        if (c.getCellStyle().getDataFormatString().startsWith("0")) {
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue("");
        } else {
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue("");
        }
    } else if (text instanceof String) {
        try {
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(Integer.parseInt((String) text));
        } catch (Exception e) {
            try {
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(Double.parseDouble((String) text));
            } catch (Exception e2) {
                c.setCellType(Cell.CELL_TYPE_STRING);
                c.setCellValue((String) text);
            }
        }
    } else if (text instanceof Double) {
        c.setCellValue((Double) text);
        c.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else if (text instanceof Integer) {
        c.setCellValue((Integer) text);
        c.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else if (text instanceof Date) {
        c.setCellValue((Date) text);
    }
    if (rowspan > 1 || colspan > 1) {
        sheet.addMergedRegion(new CellRangeAddress(row, row + rowspan - 1, col, col + colspan - 1));
        for (int i = 0; i < rowspan; i++) {
            for (int j = 0; j < colspan; j++) {
                if (i > 0 || j > 0)
                    set(sheet, row + i, col + j, "", style);
            }
        }
    }
    return c;
}

From source file:com.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

protected Cell get(Sheet sheet, int row, int col) {
    Row r = sheet.getRow(row);
    if (r == null)
        r = sheet.createRow(row);//ww w  .j av a 2s. c  o  m
    Cell c = r.getCell(col);
    return c;
}

From source file:com.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

protected Cell setFormula(Sheet sheet, int row, int col, String text, Style style) {
    Row r = sheet.getRow(row);
    if (r == null)
        r = sheet.createRow(row);//w ww .ja  v  a2s . c  o m
    Cell c = r.getCell(col);
    if (c == null)
        c = r.createCell(col);
    c.setCellStyle(styles.get(style));
    c.setCellType(Cell.CELL_TYPE_STRING);
    try {
        c.setCellFormula(text);
    } catch (Exception e) {
        e.printStackTrace();
        c.setCellValue("Err. " + e.getMessage());
    }
    return c;
}