Example usage for org.apache.poi.hssf.usermodel HSSFSheet getRow

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getRow

Introduction

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

Prototype

@Override
public HSSFRow getRow(int rowIndex)

Source Link

Document

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

Usage

From source file:net.sourceforge.fenixedu.presentationTier.Action.administrativeOffice.scholarship.utl.report.ReportStudentsUTLCandidates.java

License:Open Source License

protected void getStudentLines(HSSFSheet sheet) {
    int i = 2;/*www. j a  va  2s.  c o m*/
    HSSFRow row;
    while ((row = sheet.getRow(i)) != null) {
        StudentLine studentLine = new StudentLine();
        boolean filledWithSuccess = studentLine.fillWithSpreadsheetRow(forExecutionYear, row);

        try {
            testIt(studentLine);
        } catch (Exception e) {
            filledWithSuccess = false;
        }

        if (filledWithSuccess) {
            correctStudentLines.add(studentLine);
        } else {
            erroneousStudentLines.add(studentLine);
        }

        i++;
    }
}

From source file:net.sourceforge.fenixedu.presentationTier.Action.administrativeOffice.scholarship.utl.report.ReportStudentsUTLCandidates.java

License:Open Source License

protected void addHeaders(HSSFSheet sheet) {
    sheet.createRow(0);//from w  ww .j  a  v a 2 s  .c  om
    sheet.createRow(1);

    addHeaderCell(sheet, getHeaderInBundle("institutionCode"), 0);
    addHeaderCell(sheet, getHeaderInBundle("institutionName"), 1);
    addHeaderCell(sheet, getHeaderInBundle("candidacyNumber"), 2);
    addHeaderCell(sheet, getHeaderInBundle("studentNumberForPrint"), 3);
    addHeaderCell(sheet, getHeaderInBundle("studentName"), 4);
    addHeaderCell(sheet, getHeaderInBundle("documentTypeName"), 5);
    addHeaderCell(sheet, getHeaderInBundle("documentNumber"), 6);
    addHeaderCell(sheet, getHeaderInBundle("degreeCode"), 7);
    addHeaderCell(sheet, getHeaderInBundle("degreeName"), 8);
    addHeaderCell(sheet, getHeaderInBundle("degreeTypeName"), 9);
    addHeaderCell(sheet, getHeaderInBundle("code"), 10);
    addHeaderCell(sheet, getHeaderInBundle("countNumberOfDegreeChanges"), 11);
    addHeaderCell(sheet, getHeaderInBundle("hasMadeDegreeChange"), 12);
    addHeaderCell(sheet, getHeaderInBundle("firstEnrolmentOnCurrentExecutionYear"), 13);
    addHeaderCell(sheet, getHeaderInBundle("regime"), 14);
    addHeaderCell(sheet, getHeaderInBundle("code"), 15);

    HSSFRow row = sheet.getRow(0);
    HSSFCell cell = row.createCell(16);
    cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies"));
    cell.setCellStyle(headerStyle);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 16, 18));

    cell = sheet.getRow(1).createCell(16);
    cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies.year"));
    cell.setCellStyle(headerStyle);

    cell = sheet.getRow(1).createCell(17);
    cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies.count"));
    cell.setCellStyle(headerStyle);

    cell = sheet.getRow(1).createCell(18);
    cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies.integral.count"));
    cell.setCellStyle(headerStyle);

    addHeaderCell(sheet, getHeaderInBundle("numberOfDoneECTS"), 19);
    addHeaderCell(sheet, getHeaderInBundle("numberOfDegreeCurricularYears"), 20);
    addHeaderCell(sheet, getHeaderInBundle("curricularYearOneYearAgo"), 21);
    addHeaderCell(sheet, getHeaderInBundle("numberOfEnrolledEctsOneYearAgo"), 22);
    addHeaderCell(sheet, getHeaderInBundle("numberOfApprovedEctsOneYearAgo"), 23);
    addHeaderCell(sheet, getHeaderInBundle("curricularYearInCurrentYear"), 24);
    addHeaderCell(sheet, getHeaderInBundle("numberOfEnrolledECTS"), 25);
    addHeaderCell(sheet, getHeaderInBundle("gratuityAmount"), 26);
    addHeaderCell(sheet, getHeaderInBundle("numberOfMonthsExecutionYear"), 27);
    addHeaderCell(sheet, getHeaderInBundle("firstMonthOfPayment"), 28);
    addHeaderCell(sheet, getHeaderInBundle("ownerOfCETQualification"), 29);
    addHeaderCell(sheet, getHeaderInBundle("degreeQualificationOwner"), 30);
    addHeaderCell(sheet, getHeaderInBundle("masterQualificationOwner"), 31);
    addHeaderCell(sheet, getHeaderInBundle("phdQualificationOwner"), 32);
    addHeaderCell(sheet, getHeaderInBundle("ownerOfCollegeQualification"), 33);
    addHeaderCell(sheet, getHeaderInBundle("observations"), 34);
    addHeaderCell(sheet, getHeaderInBundle("lastEnrolledExecutionYear"), 35);
    addHeaderCell(sheet, getHeaderInBundle("nif"), 36);
    addHeaderCell(sheet, getHeaderInBundle("last.conclusion.academic.facts"), 37);
}

From source file:net.sourceforge.fenixedu.presentationTier.Action.administrativeOffice.scholarship.utl.report.ReportStudentsUTLCandidates.java

License:Open Source License

protected void addHeaderCell(HSSFSheet sheet, String value, int columnNumber) {
    HSSFRow row = sheet.getRow(0);
    HSSFCell cell = row.createCell(columnNumber);

    cell.setCellValue(value);/*from w  w  w. ja  v  a2 s.co  m*/
    cell.setCellStyle(headerStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber));
}

From source file:net.sourceforge.fenixedu.presentationTier.Action.residenceManagement.ResidenceManagementDispatchAction.java

License:Open Source License

private List<ResidenceEventBean> process(ImportResidenceEventBean bean)
        throws IOException, InvalidSpreadSheetName {
    List<ResidenceEventBean> beans = new ArrayList<ResidenceEventBean>();

    POIFSFileSystem fs = new POIFSFileSystem(bean.getFile());
    HSSFWorkbook wb = new HSSFWorkbook(fs);

    HSSFSheet sheet = wb.getSheetAt(0);

    if (sheet == null) {
        throw new InvalidSpreadSheetName(bean.getSpreadsheetName(), getAllSpreadsheets(wb));
    }/* w ww  .j  a  v  a  2s .c  o m*/

    int i = 2;
    HSSFRow row;
    while ((row = sheet.getRow(i)) != null) {
        String room = row.getCell((short) 0).getStringCellValue();
        if (StringUtils.isEmpty(room)) {
            break;
        }

        String userName = getValueFromColumnMayBeNull(row, 1);
        String fiscalNumber = getValueFromColumnMayBeNull(row, 2);
        String name = getValueFromColumnMayBeNull(row, 3);
        Double roomValue = new Double(row.getCell((short) 4).getNumericCellValue());
        beans.add(new ResidenceEventBean(userName, fiscalNumber, name, roomValue, room));

        i++;
    }
    return beans;
}

From source file:net.sourceforge.fenixedu.presentationTier.Action.residenceManagement.ResidenceManagementDispatchAction.java

License:Open Source License

private List<ResidenceEventBean> processCurrentDebts(ImportResidenceEventBean bean)
        throws IOException, InvalidSpreadSheetName {
    List<ResidenceEventBean> beans = new ArrayList<ResidenceEventBean>();

    POIFSFileSystem fs = new POIFSFileSystem(bean.getFile());
    HSSFWorkbook wb = new HSSFWorkbook(fs);

    HSSFSheet sheet = wb.getSheetAt(0);

    if (sheet == null) {
        throw new InvalidSpreadSheetName(bean.getSpreadsheetName(), getAllSpreadsheets(wb));
    }/*w  w w . j  a  va2s . c  o m*/

    int i = 2;
    HSSFRow row;
    while ((row = sheet.getRow(i)) != null) {
        String room = row.getCell((short) 0).getStringCellValue();
        if (StringUtils.isEmpty(room)) {
            break;
        }

        String userName = getValueFromColumnMayBeNull(row, 1);
        String fiscalNumber = getValueFromColumnMayBeNull(row, 2);
        String name = getValueFromColumnMayBeNull(row, 3);
        Double roomValue = new Double(row.getCell((short) 4).getNumericCellValue());
        String paidDate = getDateFromColumn(row, 5);
        Double roomValuePaid = new Double(row.getCell((short) 6).getNumericCellValue());
        ResidenceDebtEventBean residenceDebtEventBean = new ResidenceDebtEventBean(userName, fiscalNumber, name,
                roomValue, room, paidDate, roomValuePaid);
        residenceDebtEventBean.setMonth(bean.getResidenceMonth());
        beans.add(residenceDebtEventBean);

        i++;
    }
    return beans;
}

From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java

License:Apache License

/**
 * Exportiert die bergebene excel-Datei in eine Liste mit zweidimensionalen Arrays fr jeweils
 * ein sheet in der excel-Datei.//  w  w  w .j a  v a 2  s  . c  o m
 *
 * @param excelSheet
 *            Die excel-Datei.
 * @return Gibt eine Liste mit zweidimensionalen Arrays fr jeweils ein sheet in der excel-Datei
 *         zurck.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<String[][]> exportWorkbook(File excelSheet) throws IOException, FileNotFoundException {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    HSSFWorkbook wb = new HSSFWorkbook(fs);

    int numberOfSheets = wb.getNumberOfSheets();
    List<String[][]> sheetList = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        int rows = sheet.getLastRowNum();

        int columns = sheet.getRow(0).getLastCellNum();
        String[][] excelSheetInTDArray = null;
        excelSheetInTDArray = new String[rows][columns];
        for (int i = 0; i < rows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        excelSheetInTDArray[i][j] = "";
                    } else {
                        int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            excelSheetInTDArray[i][j] = Boolean.toString(cell.getBooleanCellValue());
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            excelSheetInTDArray[i][j] = cell.getCellFormula();
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            excelSheetInTDArray[i][j] = Double.toString(cell.getNumericCellValue());
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            excelSheetInTDArray[i][j] = cell.getRichStringCellValue().getString();
                        }
                    }
                }
            }
        }
        sheetList.add(excelSheetInTDArray);
    }
    return sheetList;
}

From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java

License:Apache License

/**
 * Exportiert die bergebene excel-Datei in eine geschachtelte Liste mit Listen von sheets und
 * Listen von den Zeilen der sheets von der excel-Datei.
 *
 * @param excelSheet//w ww  .j av  a 2 s. c  o m
 *            Die excel-Datei.
 * @return Gibt eine Liste mit Listen von den sheets in der excel-Datei zurck. Die Listen mit
 *         den sheets beinhalten weitere Listen mit String die jeweils eine Zeile
 *         reprsentieren.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<List<List<String>>> exportWorkbookAsStringList(File excelSheet)
        throws IOException, FileNotFoundException {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    int numberOfSheets = wb.getNumberOfSheets();
    List<List<List<String>>> sl = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        int rows = sheet.getLastRowNum();
        int columns = sheet.getRow(0).getLastCellNum();
        List<List<String>> excelSheetList = new ArrayList<>();
        for (int i = 0; i < rows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (null != row) {
                List<String> reihe = new ArrayList<>();
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        reihe.add("");
                    } else {
                        int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            reihe.add("");
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            reihe.add(Boolean.toString(cell.getBooleanCellValue()));
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            reihe.add("");
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            reihe.add(cell.getCellFormula());
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            reihe.add(Double.toString(cell.getNumericCellValue()));
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            reihe.add(cell.getRichStringCellValue().getString());
                        }
                    }
                }
                excelSheetList.add(reihe);
            }
        }
        sl.add(excelSheetList);
    }
    return sl;
}

From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java

License:Apache License

/**
 * Replace null cells into empty cells./*from  w w  w  . j a v  a2 s  .c  om*/
 *
 * @param excelSheet
 *            the excel sheet
 * @return the HSSF workbook
 * @throws IOException
 *             Signals that an I/O exception has occurred.
 * @throws FileNotFoundException
 *             the file not found exception
 */
public static HSSFWorkbook replaceNullCellsIntoEmptyCells(File excelSheet)
        throws IOException, FileNotFoundException {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    int numberOfSheets = wb.getNumberOfSheets();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        int rows = sheet.getLastRowNum();
        int columns = sheet.getRow(0).getLastCellNum();
        for (int i = 0; i < rows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        cell = row.createCell(j, Cell.CELL_TYPE_BLANK);
                    }
                }
            }
        }
    }
    return wb;
}

From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java

License:Apache License

/**
 * Save workbook./*from w w  w  .  j  a va2s. co  m*/
 *
 * @param excelSheet
 *            the excel sheet
 * @throws IOException
 *             Signals that an I/O exception has occurred.
 * @throws FileNotFoundException
 *             the file not found exception
 */
public static void saveWorkbook(File excelSheet) throws IOException, FileNotFoundException {
    FileOutputStream out = new FileOutputStream(excelSheet);
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    int numberOfSheets = wb.getNumberOfSheets();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        int rows = sheet.getLastRowNum();
        int columns = sheet.getRow(0).getLastCellNum();
        for (int i = 0; i < rows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        cell = row.createCell(j);
                        cell.setCellValue(new HSSFRichTextString(""));
                    }
                }
            }
        }
    }
    // write the workbook to the output stream
    // close our file (don't blow out our file handles
    wb.write(out);
    out.close();
}

From source file:net.sqs2.hssf.HSSFAdapter.java

License:Apache License

public HSSFRow getRow(String sheetName, int rowIndex) {
    if (sheetName.equals(this.currentSheetName) && this.currentRowIndex == rowIndex) {
        return this.currentRow;
    }/*from  www.j  a va 2 s. co m*/
    HSSFSheet sheet = getSheet(sheetName);
    HSSFRow row = sheet.getRow(rowIndex);
    if (row == null) {
        row = sheet.createRow(rowIndex);
    }
    this.currentSheetName = sheetName;
    this.currentRowIndex = rowIndex;
    return this.currentRow = row;
}