Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. getSheetAt

Introduction

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

Prototype

@Override
public HSSFSheet getSheetAt(int index)

Source Link

Document

Get the HSSFSheet object at the given index.

Usage

From source file:net.intelliant.tests.ContactListImportTest.java

License:Open Source License

@SuppressWarnings("unchecked")
private void imortedDataTest(String importMapperId, String contactListId, String excelPath)
        throws GenericEntityException, FileNotFoundException, IOException {
    GenericValue importMapper = delegator.findByPrimaryKey("MailerImportMapper",
            UtilMisc.toMap("importMapperId", importMapperId));

    List<GenericValue> importMapperColumns = delegator.findByAnd("MailerImportColumnMapper",
            UtilMisc.toMap("importMapperId", importMapperId));
    List<GenericValue> mailerRecipients = delegator.findByAnd("MailerRecipientAndContactListView",
            UtilMisc.toMap("contactListId", contactListId), UtilMisc.toList("recipientListId"));

    HSSFWorkbook excelWorkbook = new HSSFWorkbook(new FileInputStream(excelPath));
    HSSFSheet excelSheet = excelWorkbook.getSheetAt(0);

    Iterator<?> rows = excelSheet.rowIterator();
    HSSFRow row = null;// w  w w. j av  a  2s . c  om

    if (importMapper.get("isFirstRowHeader").equals("Y")) {
        rows.next();
    }

    int counter = 0;

    Timestamp testTimeStamp = null;
    Timestamp getTimestamp = null;
    String testString = null;
    String getString = null;
    int testNumber = 0;
    int getNumber = 0;

    ModelReader reader = delegator.getModelReader();
    ModelEntity modelEntity = reader.getModelEntity("MailerRecipient");
    ModelField modelField = null;

    while (rows.hasNext()) {
        row = (HSSFRow) rows.next();

        if (validateRow(row, importMapperColumns, modelEntity)) {
            GenericValue mailerRecipient = mailerRecipients.get(counter++);

            Debug.log("##### [Row] : " + toStringHssfRow(row));
            Debug.log("##### [Entity] : " + mailerRecipient);

            for (GenericValue importMapperColumn : importMapperColumns) {
                short columnIndex = Short.valueOf(String.valueOf(importMapperColumn.get("importFileColIdx")));
                String columnName = (String) importMapperColumn.get("entityColName");
                modelField = modelEntity.getField(columnName);
                String columnType = modelField.getType();

                HSSFCell cell = row.getCell(columnIndex);

                if (columnType.equals("date") || columnType.equals("date-time")) {
                    testTimeStamp = new Timestamp(mailerRecipient.getDate(columnName).getTime());
                    getTimestamp = new Timestamp(cell.getDateCellValue().getTime());
                    assertEquals(testTimeStamp, getTimestamp);
                } else if (columnName.equals("numeric")) {
                    testNumber = mailerRecipient.getInteger(columnName);
                    getNumber = (int) cell.getNumericCellValue();
                    assertEquals(testNumber, getNumber);
                } else {
                    testString = mailerRecipient.getString(columnName);
                    getString = getStringData(cell);
                    Debug.log(testString + " # " + getString);
                    assertEquals(testString, getString);
                }
            }
        }
    }
}

From source file:net.intelliant.util.UtilImport.java

License:Open Source License

public static List<String> readExcelFirstRow(String excelFilePath, boolean isFirstRowHeader, int sheetIndex)
        throws FileNotFoundException, IOException {
    List<String> columnIndices = new ArrayList<String>();
    File file = new File(excelFilePath);
    if (file != null && file.canRead()) {
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        // HSSFSheet sheet = wb.getSheet(wb.getActiveSheetIndex());
        HSSFSheet sheet = wb.getSheetAt(sheetIndex);
        if (sheet != null) {
            HSSFRow firstRow = sheet.getRow(sheet.getFirstRowNum());
            if (firstRow != null) {
                firstRow.getPhysicalNumberOfCells();
                Iterator<?> cells = firstRow.cellIterator();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    if (isFirstRowHeader) {
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            columnIndices.add(cell.toString());
                        } else {
                            columnIndices.add("N/A - " + cell.getCellNum());
                        }/*from ww w  . ja  v a  2  s  . c o  m*/
                    } else {
                        columnIndices.add(String.valueOf(cell.getCellNum()));
                    }
                }
            }
        }
    }
    return columnIndices;
}

From source file:net.sourceforge.fenixedu.presentationTier.Action.academicAdministration.executionCourseManagement.CourseLoadOverviewBean.java

License:Open Source License

public StyledExcelSpreadsheet getInconsistencySpreadsheet() {
    final StyledExcelSpreadsheet spreadsheet = new StyledExcelSpreadsheet(
            BundleUtil.getString(Bundle.ACADEMIC, "label.course.load.inconsistency.filename") + "_"
                    + executionSemester.getExecutionYear().getYear().replace('/', '_') + "_"
                    + executionSemester.getSemester());
    HSSFCellStyle normalStyle = spreadsheet.getExcelStyle().getValueStyle();
    normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFWorkbook wb = spreadsheet.getWorkbook();
    HSSFFont font = wb.createFont();// ww w .ja va2s .  c om
    font.setColor(HSSFColor.BLACK.index);
    font.setFontHeightInPoints((short) 8);
    HSSFCellStyle redStyle = wb.createCellStyle();
    redStyle.setFont(font);
    redStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    redStyle.setFillForegroundColor(HSSFColor.ORANGE.index);
    redStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    HSSFCellStyle yellowStyle = wb.createCellStyle();
    yellowStyle.setFont(font);
    yellowStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
    yellowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    spreadsheet.newHeaderRow();
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.department"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.degree"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.executionCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.shift"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.shiftType"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.competenceCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.curricularCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.executionCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lessonInstances"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lesson.count"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lessonInstances.count"));

    for (final ExecutionCourse executionCourse : executionSemester.getAssociatedExecutionCoursesSet()) {
        for (final CourseLoad courseLoad : executionCourse.getCourseLoadsSet()) {
            for (final Shift shift : courseLoad.getShiftsSet()) {
                spreadsheet.newRow();
                spreadsheet.addCell(getDepartmentString(executionCourse));
                spreadsheet.addCell(executionCourse.getDegreePresentationString());
                spreadsheet.addCell(executionCourse.getName());
                spreadsheet.addCell(shift.getNome());
                spreadsheet.addCell(courseLoad.getType().getFullNameTipoAula());
                final BigDecimal competenceCourseLoad = new BigDecimal(getCompetenceCourseLoad(courseLoad))
                        .setScale(2, RoundingMode.HALF_EVEN);
                final BigDecimal curricularCourseLoad = new BigDecimal(getCurricularCourseLoad(courseLoad))
                        .setScale(2, RoundingMode.HALF_EVEN);
                final BigDecimal executionLoad = courseLoad.getTotalQuantity().setScale(2,
                        RoundingMode.HALF_EVEN);
                final BigDecimal shiftCourseLoad = getShiftCourseLoad(shift).setScale(2,
                        RoundingMode.HALF_EVEN);
                if (competenceCourseLoad.signum() < 0) {
                    spreadsheet.addCell(getCompetenceCourseLoadStrings(courseLoad), redStyle);
                } else {
                    spreadsheet.addCell(competenceCourseLoad);
                }
                if (!competenceCourseLoad.equals(curricularCourseLoad) || curricularCourseLoad.signum() < 0) {
                    spreadsheet.addCell(getCurricularCourseLoadString(courseLoad), redStyle);
                } else {
                    spreadsheet.addCell(curricularCourseLoad);
                }
                if (!executionLoad.equals(curricularCourseLoad)) {
                    spreadsheet.addCell(executionLoad, redStyle);
                } else {
                    spreadsheet.addCell(executionLoad);
                }
                if (!shiftCourseLoad.equals(executionLoad)) {
                    if (isLargeDifference(shiftCourseLoad, executionLoad,
                            competenceCourseLoad.divide(new BigDecimal(14), 2, RoundingMode.HALF_EVEN))) {
                        spreadsheet.addCell(shiftCourseLoad, redStyle);
                    } else {
                        spreadsheet.addCell(shiftCourseLoad, yellowStyle);
                    }
                } else {
                    spreadsheet.addCell(shiftCourseLoad);
                }
                spreadsheet.addCell(shift.getAssociatedLessonsSet().size());
                spreadsheet.addCell(getLessonInstanceCount(shift));
            }
        }
    }

    final HSSFSheet sheet = wb.getSheetAt(0);
    sheet.createFreezePane(0, 1, 0, 1);
    sheet.autoSizeColumn(1, true);
    sheet.autoSizeColumn(2, true);
    sheet.autoSizeColumn(3, true);
    sheet.autoSizeColumn(4, true);
    sheet.autoSizeColumn(5, true);
    sheet.autoSizeColumn(6, true);
    sheet.autoSizeColumn(7, true);
    sheet.autoSizeColumn(8, true);
    sheet.autoSizeColumn(9, true);

    return spreadsheet;
}

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

License:Open Source License

public ActionForward showReport(final ActionMapping mapping, final ActionForm actionForm,
        final HttpServletRequest request, final HttpServletResponse response) throws IOException {
    ReportStudentsUTLCandidatesBean bean = getRenderedObject("bean");

    if (bean == null || bean.getXlsFile() == null) {
        return prepare(mapping, actionForm, request, response);
    }//from ww  w.  j  a  v  a2  s  .c  om

    POIFSFileSystem fs = new POIFSFileSystem(bean.getXlsFile());
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);

    if (sheet == null) {
        addErrorMessage(request, "error",
                "error.academicAdminOffice.scholarship.utl.report.invalid.spreadsheet", new String[0]);
        return prepare(mapping, actionForm, request, response);
    }

    ReportStudentsUTLCandidates report = null;

    if (bean.getForFirstYear()) {
        report = new ReportStudentsUTLCandidatesForFirstYear(bean.getExecutionYear(), sheet);
    } else {
        report = new ReportStudentsUTLCandidates(bean.getExecutionYear(), sheet);
    }

    request.setAttribute("report", report);

    List<StudentLine> correctStudentLines = new ArrayList<StudentLine>();
    List<StudentLine> erroneousStudentLines = new ArrayList<StudentLine>();

    erroneousStudentLines.addAll(report.getErroneousStudentLines());

    for (StudentLine studentLine : report.getCorrectStudentLines()) {
        if (studentLine.isAbleToReadAllValues()) {
            correctStudentLines.add(studentLine);
        } else {
            erroneousStudentLines.add(studentLine);
        }
    }

    request.setAttribute("correctStudentLines", correctStudentLines);
    request.setAttribute("erroneousStudentLines", erroneousStudentLines);

    return mapping.findForward("showReport");
}

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  w  w. j a va2 s .  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));
    }//from   ww  w . j a v a2  s.  com

    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.//from  ww w.ja va 2 s . com
 *
 * @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 a  v  a2s  .  c om*/
 *            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.//ww  w.j  av  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;
}