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.exilant.exility.core.XlxUtil.java

License:Open Source License

/***
 * extract fields from spread sheet into dc
 * //from   w w w.  j av  a 2 s.c o m
 * @param table
 *            table element of spread sheet
 * @param dc
 *            dc
 * @param useDictionaryForDataType
 *            refer to data dictionary or use DataType as present in spread
 *            sheet
 */
private void extractValues(Sheet sheet, DataCollection dc, boolean useDictionaryForDataType) {
    int n = sheet.getLastRowNum();

    // if there are no values, following for loop will not execute..
    for (int i = 1; i <= n; i++) // first row is header
    {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        // value row should have just two cells in it
        int nbrCells = row.getLastCellNum();
        if (nbrCells < 1) {
            continue;
        }

        String fieldName = row.getCell(0, Row.CREATE_NULL_AS_BLANK).getStringCellValue();
        if (fieldName.length() == 0) {
            continue; // no name
        }

        Cell dataCell = null;
        String fieldValue = EMPTY_STRING;
        if (nbrCells > 1) // value is present
        {
            dataCell = row.getCell(1, Row.CREATE_NULL_AS_BLANK);
            fieldValue = this.getTextValue(dataCell);
        }

        if (useDictionaryForDataType) {
            dc.addValueAfterCheckingInDictionary(fieldName, fieldValue);
        } else {
            dc.addValue(fieldName, fieldValue, this.getExilityType(dataCell));
        }
    }
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/***
 * get data types of column based on actual values in the sheet
 * //from w w w. j  a  v a  2s .c  o  m
 * @param sheet
 * @param nbrCells
 * @param rowStart
 * @param rowEnd
 * @return
 */
private DataValueType[] getExilityTypes(Sheet sheet, int nbrCells) {
    DataValueType[] types = new DataValueType[nbrCells];

    // though NULL is default (as of now that is the first one in ENUM) let
    // us explicitly populate it
    for (int i = 0; i < nbrCells; i++) {
        types[i] = DataValueType.NULL;
    }

    int rowStart = sheet.getFirstRowNum();
    int rowEnd = sheet.getLastRowNum();
    int nbrFound = 0;

    // which cell to start? We will go by the first cell of the first
    // physucal row
    Row firstRow = sheet.getRow(sheet.getFirstRowNum());
    int startingCellIdx = firstRow.getFirstCellNum();
    int endCellIdx = startingCellIdx + nbrCells;
    for (int i = rowStart; i <= rowEnd; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }

        for (int j = startingCellIdx; j < endCellIdx; j++) {
            // do we already know this type?
            if (types[j] != DataValueType.NULL) {
                continue;
            }

            Cell cell = row.getCell(j, Row.RETURN_BLANK_AS_NULL);
            if (cell == null) {
                continue;
            }
            types[j] = this.getExilityType(cell);
            nbrFound++;
            if (nbrFound == nbrCells) {
                return types;
            }
        }
    }

    // we will treat unknown ones as text
    for (int i = 0; i < nbrCells; i++) {
        if (types[i] == DataValueType.NULL) {
            types[i] = DataValueType.TEXT;
        }
    }

    return types;
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

private void addColumn(Sheet sheet, int colIdx, ValueList values, String header) {
    sheet.getRow(0).createCell(colIdx).setCellValue(header);
    switch (values.getValueType()) {
    case BOOLEAN:
        this.addColumn(sheet, colIdx, values.getBooleanList());
        return;/*from w w  w.j a v a  2  s  .  c om*/
    case DATE:
    case TIMESTAMP:
        this.addColumn(sheet, colIdx, values.getDateList());
        return;

    case DECIMAL:
        this.addColumn(sheet, colIdx, values.getDecimalList());
        return;

    case INTEGRAL:
        this.addColumn(sheet, colIdx, values.getIntegralList());
        return;

    default:
        this.addColumn(sheet, colIdx, values.getTextList());
        break;
    }
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

private void addColumn(Sheet sheet, int colIdx, Date[] values) {
    int rowIdx = 1;
    for (Date date : values) {
        sheet.getRow(rowIdx).createCell(colIdx).setCellValue(date);
        rowIdx++;/*from   ww w.  ja v  a2s  . c  om*/
    }
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

private void addColumn(Sheet sheet, int colIdx, String[] values) {
    int rowIdx = 1;
    for (String value : values) {
        sheet.getRow(rowIdx).createCell(colIdx).setCellValue(value);
        rowIdx++;/*w ww .ja  v  a  2 s .  c  o m*/
    }
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

private void addColumn(Sheet sheet, int colIdx, double[] values) {
    int rowIdx = 1;
    for (double value : values) {
        sheet.getRow(rowIdx).createCell(colIdx).setCellValue(value);
        rowIdx++;//from w w w . j  a v a2s  .  c  om
    }
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

private void addColumn(Sheet sheet, int colIdx, long[] values) {
    int rowIdx = 1;
    for (long value : values) {
        sheet.getRow(rowIdx).createCell(colIdx).setCellValue(value);
        rowIdx++;//  w w w  .  ja  v a  2s .  c  o  m
    }
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

private void addColumn(Sheet sheet, int colIdx, boolean[] values) {
    int rowIdx = 1;
    for (boolean value : values) {
        sheet.getRow(rowIdx).createCell(colIdx).setCellValue(value);
        rowIdx++;/*from   www  .ja v a2s .  c  o m*/
    }
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

private void addMissingRows(Sheet sheet, String[][] rows) {
    /**//from w w  w.  j ava2 s.  c  o m
     * create a set of existing labels
     */
    Set<String> existingEntries = new HashSet<String>();
    int lastRow = sheet.getLastRowNum();
    for (int i = 0; i <= lastRow; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        Cell cell = row.getCell(0);
        if (cell == null) {
            continue;
        }
        existingEntries.add(cell.getStringCellValue());
    }
    /**
     * now, add rows, only if they are not there already
     */
    for (String[] row : rows) {
        if (existingEntries.contains(row[0])) {
            continue;
        }
        lastRow++;
        Row xlRow = sheet.createRow(lastRow);
        int colIdx = 0;
        for (String columnValue : row) {
            xlRow.createCell(colIdx).setCellValue(columnValue);
            colIdx++;
        }
    }
}

From source file:com.ferid.app.classroom.statistics.StatisticsFragment.java

License:Apache License

/**
 * Converts all attendances into excel format
 *///  www.j  av a 2  s. co m
private void convertToExcel() {
    //create workbook
    Workbook wb = new HSSFWorkbook();

    for (Classroom classroom : classroomArrayList) { //each sheet

        //create sheet
        Sheet sheet = wb.createSheet(classroom.getName());

        //header
        HashMap<String, Integer> date_column_map = new HashMap<>();
        ArrayList<String> dates = new ArrayList<>();
        int rowNumber = 0;
        int colNumber = 1;
        Row row = sheet.createRow(rowNumber);

        //dates columns
        for (Attendance attendance : attendanceArrayList) {

            if (classroom.getId() == attendance.getClassroomId() && !dates.contains(attendance.getDateTime())) {

                Cell cellDate = row.createCell(colNumber);
                cellDate.setCellStyle(ExcelStyleManager.getHeaderCellStyle(wb));

                cellDate.setCellValue(attendance.getDateTime());

                dates.add(attendance.getDateTime());
                date_column_map.put(attendance.getDateTime(), colNumber);

                //set width of the dates columns
                sheet.setColumnWidth(colNumber,
                        getResources().getInteger(R.integer.statistics_excel_column_width_dates));

                colNumber++;
            }
        }

        //set width of the students column
        //it is always the first column
        sheet.setColumnWidth(0, getResources().getInteger(R.integer.statistics_excel_column_width_students));

        //students list at the left column
        HashMap<Integer, Integer> student_row_map = new HashMap<>();
        ArrayList<Integer> studentIds = new ArrayList<>();
        rowNumber = 1;
        for (Attendance attendance : attendanceArrayList) {

            if (classroom.getId() == attendance.getClassroomId()
                    && !studentIds.contains(attendance.getStudentId())) { //another student

                row = sheet.createRow(rowNumber);

                Cell cellStudent = row.createCell(0);
                cellStudent.setCellStyle(ExcelStyleManager.getHeaderCellStyle(wb));

                cellStudent.setCellValue(attendance.getStudentName());

                studentIds.add(attendance.getStudentId());
                student_row_map.put(attendance.getStudentId(), rowNumber);

                rowNumber++;
            }
        }

        //now get column number from date columns
        //and get row number from student rows
        //match row-column pair and print into cell
        for (Attendance attendance : attendanceArrayList) {

            if (classroom.getId() == attendance.getClassroomId()) {
                rowNumber = student_row_map.get(attendance.getStudentId());
                colNumber = date_column_map.get(attendance.getDateTime());

                row = sheet.getRow(rowNumber);

                Cell cellPresence = row.createCell(colNumber);
                cellPresence.setCellStyle(ExcelStyleManager.getContentCellStyle(wb));

                cellPresence.setCellValue(attendance.getPresent());
            }
        }
    }

    if (classroomArrayList.size() > 0) {
        writeIntoFile(wb);
    }

    swipeRefreshLayout.setRefreshing(false);
}