Example usage for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem

List of usage examples for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.poifs.filesystem.*
  5. POIFSFileSystem
  6. POIFSFileSystem

Introduction

In this page you can find the example usage for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem.

Prototype


public POIFSFileSystem(InputStream stream) throws IOException

Source Link

Document

Create a POIFSFileSystem from an InputStream.

Usage

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   www.j a va 2s  .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);/*from  w w  w  .j  a v  a2  s  . c  o m*/

    if (sheet == null) {
        throw new InvalidSpreadSheetName(bean.getSpreadsheetName(), getAllSpreadsheets(wb));
    }

    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);//from   w w w  .  j  ava  2  s  .  com

    if (sheet == null) {
        throw new InvalidSpreadSheetName(bean.getSpreadsheetName(), getAllSpreadsheets(wb));
    }

    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.// www  .  jav  a 2 s  .c  om
 *
 * @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//from   ww  w .  j  a va 2  s. co  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./*ww  w.  j av a2  s .  c o m*/
 *
 * @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  ww. jav  a  2  s .com*/
 *
 * @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.sourceforge.vaticanfetcher.model.parse.MSExcelParser.java

License:Open Source License

protected String renderText(File file, String filename) throws ParseException {
    InputStream in = null;//from  www.  j  av a  2s . c o m
    try {
        in = new FileInputStream(file);
        ExcelExtractor extractor = null;
        try {
            POIFSFileSystem fs = new POIFSFileSystem(in);
            extractor = new ExcelExtractor(fs);
        } catch (OldExcelFormatException e) {
            /*
             * POI doesn't support the old Excel 5.0/7.0 (BIFF5) format,
             * only the BIFF8 format from Excel 97/2000/XP/2003. Thus, we
             * fall back to another Excel library.
             */
            Closeables.closeQuietly(in);
            return extractWithJexcelAPI(file);
        }
        extractor.setFormulasNotResults(ProgramConf.Bool.IndexExcelFormulas.get());
        return extractor.getText();
    } catch (IOException e) {
        throw new ParseException(e);
    } catch (RuntimeException e) {
        // POI can throw NullPointerExceptions on some odd Excel files
        throw new ParseException(e);
    } finally {
        Closeables.closeQuietly(in);
    }
}

From source file:no.abmu.abmstatistikk.annualstatistic.util.ExcelWithLibraryInformationAndDataParser.java

License:Open Source License

/**
 * Loads the Excel document.// www  .j av a  2 s.  co  m
 */
protected void loadExcelDocument() {
    File file;
    POIFSFileSystem poifsFileSystem = null;

    if (excelFileName == null) {
        logger.error("Can't parse Excel document. No filename specified");
        throw new IllegalStateException("Can't parse Excel document. No filename specified");
    }

    logger.debug("Will load Excel document having filename '" + excelFileName + "'");

    file = new File(excelFileName);
    if (file.exists() == false) {
        logger.error(
                "Can't load Excel document having filename '" + excelFileName + "'. The file does not exist");
        throw new IllegalArgumentException(
                "Can't parse Excel document. File " + excelFileName + " does not exist");
    }

    if (file.canRead() == false) {
        logger.error("Can't load Excel document having filename '" + excelFileName + "'. No read access.");
        throw new IllegalArgumentException("Can't parse Excel document. No access");
    }

    try {
        poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));
    } catch (IOException e) {
        logger.error("Can't load Excel document having filename '" + excelFileName
                + "'. Failed when reading file : '" + e.getMessage() + "'", e);
        throw new IllegalStateException("Can't parse Excel document. Failed to read.");
    }

    try {
        workBook = new HSSFWorkbook(poifsFileSystem);
    } catch (IOException e) {
        logger.error("Can't load Excel document having filename '" + excelFileName
                + "'. Failed when parsing file : '" + e.getMessage() + "'", e);
        throw new IllegalStateException("Can't parse Excel document. Failed to parse Excel data.");
    }

    logger.debug("Successfully loaded and parsed Excel document");
}

From source file:no.abmu.common.excel.BaseExcelParserImpl.java

License:Open Source License

/**
 * Loads the Excel document.//from   w w  w  . ja  v a 2  s. c o m
 */
protected void loadExcelDocument() {
    File file = null;
    POIFSFileSystem poifsFileSystem = null;
    InputStream inputStream = null;

    if (excelFileName == null) {
        logger.error("Can't parse Excel document. No filename specified");
        throw new IllegalStateException("Can't parse Excel document. No filename specified");
    }

    logger.debug("Will load Excel document having filename '" + excelFileName + "'");
    inputStream = this.getClass().getResourceAsStream(excelFileName);
    if (inputStream == null) {
        file = new File(excelFileName);
        if (!file.exists()) {
            logger.error("Can't load Excel document having filename '" + excelFileName
                    + "'. The file does not exist");
            throw new IllegalArgumentException(
                    "Can't parse Excel document. File " + excelFileName + " does not exist");
        }

        if (!file.canRead()) {
            logger.error("Can't load Excel document having filename '" + excelFileName + "'. No read access.");
            throw new IllegalArgumentException("Can't parse Excel document. No access");
        }
        try {
            inputStream = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            logger.error("Can't load Excel document having filename '" + excelFileName
                    + "'. Failed when reading file : '" + e.getMessage() + "'", e);
            throw new IllegalStateException(
                    "Can't parse Excel document. Failed to read file '" + file + "' " + e);
        }
    }
    try {
        poifsFileSystem = new POIFSFileSystem(inputStream);
    } catch (IOException e) {
        logger.error("Can't load Excel document having filename '" + excelFileName
                + "'. Failed when reading file : '" + e.getMessage() + "'", e);
        throw new IllegalStateException("Can't parse Excel document. Failed to read file '" + file + "' " + e);
    }

    try {
        workBook = new HSSFWorkbook(poifsFileSystem);
    } catch (IOException e) {
        logger.error("Can't load Excel document having filename '" + excelFileName
                + "'. Failed when parsing file : '" + e.getMessage() + "'", e);
        throw new IllegalStateException("Can't parse Excel document. Failed to parse Excel data.");
    }

    logger.debug("Successfully loaded and parsed Excel document");

    buildColumnMap();
}