Example usage for org.apache.poi.ss.usermodel WorkbookFactory create

List of usage examples for org.apache.poi.ss.usermodel WorkbookFactory create

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. WorkbookFactory
  6. create

Introduction

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

Prototype

public static Workbook create(File file) throws IOException, EncryptedDocumentException

Source Link

Document

Creates the appropriate HSSFWorkbook / XSSFWorkbook from the given File, which must exist and be readable.

Usage

From source file:crygetter.gui.MainWindow.java

/**
 * Loads Amino Acid Data/*from   www . j  a  va 2  s. c  o m*/
 */
private void loadAAData() {

    try {

        Workbook wb = WorkbookFactory.create(getClass().getResourceAsStream("/aaData.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        aaData = new LinkedHashMap<>();

        for (int i = 1;; i++) {

            Row aaDataRow = sheet.getRow(i);

            if (aaDataRow != null) {

                String name = aaDataRow.getCell(0).getStringCellValue();
                String threeLetter = aaDataRow.getCell(1).getStringCellValue();
                String oneLetter = aaDataRow.getCell(2).getStringCellValue();
                String group = aaDataRow.getCell(3).getStringCellValue();
                String sideChainGroup = aaDataRow.getCell(4).getStringCellValue();
                String sideChainPolarity = aaDataRow.getCell(5).getStringCellValue();
                String sideChainCharge = aaDataRow.getCell(6).getStringCellValue();
                String hydropathyIndex = aaDataRow.getCell(7).getStringCellValue();
                String molecularMass = aaDataRow.getCell(8).getStringCellValue();
                String vanderWaalsVolume = aaDataRow.getCell(9).getStringCellValue();
                String frequencyInProteins = aaDataRow.getCell(10).getStringCellValue();
                String surfaceArea = aaDataRow.getCell(11).getStringCellValue();
                String[] observations = aaDataRow.getCell(12).getStringCellValue().split("[$]");

                AminoAcid aa = new AminoAcid();
                aa.name = name;
                aa.threeLetter = threeLetter;
                aa.oneLetter = oneLetter;
                aa.group = group;
                aa.sideChainGroup = sideChainGroup;
                aa.sideChainPolarity = sideChainPolarity;
                aa.sideChainCharge = sideChainCharge;
                aa.hydropathyIndex = hydropathyIndex;
                aa.molecularMass = molecularMass;
                aa.vanderWaalsVolume = vanderWaalsVolume;
                aa.frequencyInProteins = frequencyInProteins;
                aa.surfaceArea = surfaceArea;
                aa.observations.addAll(Arrays.asList(observations));
                aa.pdbFile = "/" + threeLetter.toLowerCase() + ".pdb";
                aa.pngFile = "/" + threeLetter.toLowerCase() + ".png";

                aaData.put(oneLetter, aa);

            } else {
                break;
            }

        }

    } catch (IOException | InvalidFormatException exc) {
        Utils.showExceptionMessage(this, exc);
    }

}

From source file:csv.impl.ExcelReader.java

License:Open Source License

/**
 * Opens the stream by retrieving the workbook and selecting the first sheet.
 * @see csv.impl.AbstractStreamTableReader#open()
 *//*from  www  .  ja  v a2 s  .  c o m*/
@Override
public void open() {
    super.open();
    try {
        workbook = WorkbookFactory.create(getInputStream());
        selectSheet(0);
    } catch (Exception e) {
        throw new IllegalStateException("Cannot create Excel workbook", e);
    }
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

public boolean processFile(Path input, boolean openFile) {
    boolean result = false;
    int endRow = 0;

    try {//from  w ww .j  a va2  s  .  com
        updateMessages(String.format("Inicializando el documento: %s", input.toString()));
        Path copy = createCopy(input);

        if (copy != null && Files.exists(copy, LinkOption.NOFOLLOW_LINKS)) {
            Workbook workbook = WorkbookFactory.create(copy.toFile());
            Sheet sheet = workbook.getSheetAt(0);
            Sheet newSheet = workbook.createSheet("Procesado");

            workbook.setSheetName(0, "Crudo");

            endRow = getLasRow(sheet);

            // seccion para la creacion de los encabezados
            updateMessages("Creando la cabecera de los datos");
            createHeaderData(newSheet, getCellStyleHeaderData(workbook));

            // seccion para los values USD
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MTH.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MTH),
                    11, 35, 14);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.QRT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.QRT),
                    35, 49, 38);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.YTD.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.YTD),
                    49, 54, 52);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MAT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MAT),
                    54, 59, 57);

            // seccion para los values units
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MTH.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MTH),
                    59, 83, 63);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.QRT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.QRT),
                    83, 97, 87);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.YTD.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.YTD),
                    97, 102, 101);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MAT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MAT),
                    102, 107, 106);
            //            
            //            // seccion para los values units standars
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.MTH.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MTH),
                    107, 131, 112);
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.QRT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.QRT),
                    131, 145, 136);
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.YTD.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.YTD),
                    145, 150, 150);
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.MAT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MAT),
                    150, 155, 155);

            // fin de la seccion para la creacion de los encabezados

            // seccion para escribir los CT
            updateMessages("Escribiendo las clases terampeuticas...");
            writeCT(newSheet, sheet, 13, endRow);

            // seccion para escribir los productos
            updateMessages("Escribiendo los productos...");
            writeProducts(newSheet, sheet, 14);

            // seccion para escribir los otros valores
            updateMessages("Escribiendo datos en general...");
            writerOthersValues(newSheet, sheet, 15);

            // seccion para escribir los key competitors
            updateMessages("Escribiendo los Key Competitors...");
            writeKeyCompetitors(newSheet, 3, endRow, 9, 5);

            // seccion para escribir el pais
            XmlContry contry = writeContries(newSheet, 3, 0, input);

            // seccion para escribir la region
            writeRegions(contry, newSheet, 3, 1);

            for (int i = 0; i < 155; i++)
                newSheet.autoSizeColumn(i);

            newSheet.setAutoFilter(CellRangeAddress.valueOf(String.format("A3:K%d", newSheet.getLastRowNum())));

            String pathOutput = "DAS PF - " + input.getFileName().toString();

            try (FileOutputStream fos = new FileOutputStream(
                    Paths.get(this.out.toString(), pathOutput).toFile())) {

                updateMessages(String.format("Guadando el trabajo en la ruta: '%s'",
                        Paths.get(this.out.toString(), pathOutput)));

                workbook.write(fos);
            } catch (IOException ex) {
                Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                workbook.close();
            }

            if (openFile && Desktop.isDesktopSupported()
                    && Desktop.getDesktop().isSupported(Desktop.Action.OPEN))
                Desktop.getDesktop().open(Paths.get(this.out.toString(), pathOutput).toFile());

            result = true;

            newSheet = null;
            sheet = null;
            workbook = null;

            Files.delete(copy);
        }
    } catch (IOException | InvalidFormatException ex) {
        Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);

        Util.showException("No se pudo guardar el archivo", ex);
    }

    return result;
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

public boolean consolidateFiles() {
    boolean result = false;
    AtomicInteger rowIndex = new AtomicInteger(3);

    String outputName = "DAS - " + this.out.getFileName().toString() + " consolidate.xlsx";
    Workbook consolidateWb = new XSSFWorkbook();

    try {//www . j  av  a  2s.  c o m
        Sheet sheetConsolidate = consolidateWb.createSheet("Consolidado");

        Files.list(this.out).filter((p) -> {
            String name = p.toString();

            return (name.endsWith(".xlsx") || name.endsWith(".xls"))
                    && !p.getFileName().toString().equals(outputName);
        }).sorted((p1, p2) -> {
            String acronym = getAcromynName(p1);
            String acronym2 = getAcromynName(p2);

            return acronym.compareToIgnoreCase(acronym2);
        }).forEach(p -> {
            try {
                Workbook wb = WorkbookFactory.create(p.toFile());
                Sheet sheet = wb.getSheet("Procesado");

                updateMessages(String.format("Copiando los datos del archivo: %s dentro del archivo: %s",
                        p.toString(), outputName));

                for (int index = 3; index < sheet.getLastRowNum(); index++) {
                    Row row = sheet.getRow(index);
                    Row r = sheetConsolidate.createRow(rowIndex.getAndIncrement());

                    row.forEach(c -> {
                        if (c != null && c.getCellType() != Cell.CELL_TYPE_BLANK) {
                            final Cell cell = r.createCell(c.getColumnIndex(), c.getCellType());

                            updateMessages(
                                    String.format("Copiando los datos de la fila: #%d", c.getRowIndex()));

                            switch (c.getCellType()) {
                            case Cell.CELL_TYPE_NUMERIC:
                                cell.setCellValue(c.getNumericCellValue());

                                break;

                            case Cell.CELL_TYPE_STRING:
                                cell.setCellValue(c.getRichStringCellValue());

                                break;
                            }
                        }
                    });

                    row = null;
                }

                sheet = null;
                wb.close();
                wb = null;
            } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) {
                Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);
            }
        });

        Path path = Files.list(this.out).filter((p) -> {
            String name = p.toString();

            return (name.endsWith(".xlsx") || name.endsWith(".xls"))
                    && !p.getFileName().toString().equals(outputName);
        }).findFirst().get();

        createHeadersConsolidateFile(consolidateWb, path);

        for (int i = 0; i < 155; i++)
            sheetConsolidate.autoSizeColumn(i);

        sheetConsolidate.setAutoFilter(
                CellRangeAddress.valueOf(String.format("A3:K%d", sheetConsolidate.getLastRowNum())));

        try (FileOutputStream fos = new FileOutputStream(Paths.get(out.toString(), outputName).toFile())) {
            updateMessages(String.format("Guadando el trabajo en la ruta: '%s'",
                    Paths.get(this.out.toString(), outputName)));

            consolidateWb.write(fos);

            result = true;
        } catch (IOException ex) {
            Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE,
                    "Ocurrio un error al intenatr guardar el archivo consolidado", ex);
        } finally {
            consolidateWb.close();
        }

    } catch (IOException ex) {
        Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);
    }

    consolidateWb = null;

    return result;
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

private void createHeadersConsolidateFile(Workbook consolidadteWb, Path source) {
    try {//from w  w w.  j  a  v  a 2 s .c  o m
        Workbook wb = WorkbookFactory.create(source.toFile());
        Sheet sheet = wb.getSheetAt(0);
        Sheet consolidaeSheet = consolidadteWb.getSheetAt(0);

        updateMessages("Creando la cabecera de los datos del archivo consolidado");
        createHeaderData(consolidaeSheet, getCellStyleHeaderData(consolidadteWb));

        // seccion para los values USD
        updateMessages(String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MTH.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.VALUES, TypeUnits.MTH), 11, 35, 14);
        updateMessages(String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.QRT.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.VALUES, TypeUnits.QRT), 35, 49, 38);
        updateMessages(String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.YTD.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.VALUES, TypeUnits.YTD), 49, 54, 52);
        updateMessages(String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MAT.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.VALUES, TypeUnits.MAT), 54, 59, 57);

        // seccion para los values units
        updateMessages(String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MTH.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.UNITS, TypeUnits.MTH), 59, 83, 63);
        updateMessages(String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.QRT.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.UNITS, TypeUnits.QRT), 83, 97, 87);
        updateMessages(String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.YTD.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.UNITS, TypeUnits.YTD), 97, 102, 101);
        updateMessages(String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MAT.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.UNITS, TypeUnits.MAT), 102, 107, 106);
        //            
        //            // seccion para los values units standars
        updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                TypeUnits.MTH.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.U_E, TypeUnits.MTH), 107, 131, 112);
        updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                TypeUnits.QRT.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.U_E, TypeUnits.QRT), 131, 145, 136);
        updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                TypeUnits.YTD.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.U_E, TypeUnits.YTD), 145, 150, 150);
        updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                TypeUnits.MAT.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.U_E, TypeUnits.MAT), 150, 155, 155);
    } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) {
        Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:DB.TopStockDescriptionList.java

public static void writeToFileExcel(String fileName, List<TopStockDescription> tsdData) {
    int SHORTNAME = 1;
    int LONGNAME = 0;
    int TEXT = 2;

    try {//from  ww  w .j  av  a 2s  . c  om
        FileInputStream fileIn = new FileInputStream(fileName);

        Workbook wb = WorkbookFactory.create(fileIn);
        Sheet sheet = wb.getSheetAt(0);

        for (TopStockDescription tsd : tsdData) {
            if (tsd.getContentText().isEmpty())
                continue;
            //?  ? or Create
            int rowInt = 1;
            while (true) {
                Row row = sheet.getRow(rowInt);

                if (row == null) {// THE END OF THE ROW not found
                    //Create
                    row = sheet.createRow(rowInt);
                    Cell cellShortName = row.createCell(SHORTNAME);
                    cellShortName.setCellType(Cell.CELL_TYPE_STRING);
                    cellShortName.setCellValue(tsd.getShortName());

                    String longName = tsd.getShortName();
                    int endIndex = tsd.getContentText().indexOf(tsd.getShortName());
                    if (endIndex > 0 && endIndex < tsd.getContentText().length())
                        longName = tsd.getContentText().substring(0, endIndex - 1).trim();

                    Cell cellLongName = row.createCell(LONGNAME);
                    cellLongName.setCellType(Cell.CELL_TYPE_STRING);
                    cellLongName.setCellValue(longName);

                    Cell cellText = row.createCell(TEXT);
                    cellText.setCellType(Cell.CELL_TYPE_STRING);
                    cellText.setCellValue(tsd.getContentText());
                    break;
                }
                Cell cellShortName = row.getCell(SHORTNAME);
                String shortName = cellShortName.getRichStringCellValue().getString();
                if (shortName.equalsIgnoreCase(tsd.getShortName())) {//If Match, Update
                    //Cell cellLongName = row.getCell(LONGNAME);
                    //cellLongName.setCellType(Cell.CELL_TYPE_STRING);
                    //cellLongName.setCellValue(tsd.getLongName());

                    Cell cellText = row.getCell(TEXT);
                    cellText.setCellType(Cell.CELL_TYPE_STRING);
                    cellText.setCellValue(tsd.getContentText());
                    break;
                }
                rowInt++;
            }
        }

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream(fileName);
        wb.write(fileOut);
        fileOut.close();
        fileIn.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception ex) {
        Logger.getLogger(TopStockDescriptionList.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:de.bund.bfr.knime.pmm.common.XLSReader.java

License:Open Source License

private Workbook getWorkbook(File file) throws IOException, InvalidFormatException {
    if (file.exists()) {
        try (InputStream in = new FileInputStream(file)) {
            return WorkbookFactory.create(in);
        }/*from www  .  j a  v  a 2s. co  m*/
    } else {
        try (InputStream in = new URL(file.getPath()).openStream()) {
            return WorkbookFactory.create(in);
        }
    }
}

From source file:de.bund.bfr.knime.pmmlite.io.XlsReader.java

License:Open Source License

public void setFile(String fileName) throws InvalidFormatException, IOException {
    wb = null;/*from w  w  w.ja va 2  s  .  c o  m*/

    try (InputStream in = new FileInputStream(KnimeUtils.getFile(fileName))) {
        wb = WorkbookFactory.create(in);
    }
}

From source file:de.interactive_instruments.ShapeChange.SBVR.SbvrRuleLoader.java

License:Open Source License

public SbvrRuleLoader(String sbvrFileLocation, Options options, ShapeChangeResult result, Model model) {

    this.options = options;
    this.result = result;
    this.model = model;

    if (sbvrFileLocation != null) {

        /*//from  www  . j ava 2  s.  co m
         * Before loading the rules, load model verbs and nouns because we might
         * need to parse the main class name for a rule from the rule text.
         */
        helper = SbvrUtil.createParserHelper(model);

        java.io.File sbvrFile = new java.io.File(sbvrFileLocation);
        boolean ex = true;
        if (!sbvrFile.exists()) {
            ex = false;
            if (!sbvrFileLocation.toLowerCase().endsWith(".xlsx")) {
                sbvrFileLocation += ".xlsx";
                sbvrFile = new java.io.File(sbvrFileLocation);
                ex = sbvrFile.exists();
            }
        }
        if (!ex) {

            result.addError(null, 36, sbvrFileLocation);

        } else {

            try {

                Workbook sbvrXls = WorkbookFactory.create(sbvrFile);
                sbvrRules = parseSBVRRuleInfos(sbvrXls);

            } catch (InvalidFormatException e) {

                result.addError(this, 1, e.getMessage());

            } catch (IOException e) {

                result.addError(this, 2, e.getMessage());
            }
        }
    }
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.util.ExcelUtils.java

License:Open Source License

/**
 * @param in InputStream to read.//from   w w w .j  a v  a  2s  . c  om
 * 
 * @return the {@link Workbook}.
 * 
 * @throws IteraplanTechnicalException if opening the file failed for any reason.
 */
public static Workbook openExcelFile(InputStream in) {
    Workbook workbook = null;

    try {
        workbook = WorkbookFactory.create(in);
    } catch (InvalidFormatException e) {
        String msg = MessageFormat.format("Error reading excel workbook: InvalidFormatException {0}",
                e.getMessage());
        LOGGER.error(msg);
        throw new IteraplanTechnicalException(IteraplanErrorMessages.GENERAL_TECHNICAL_ERROR, msg);
    } catch (IOException e) {
        String msg = MessageFormat.format("Error reading excel workbook: IOException {0}", e.getMessage());
        LOGGER.error(msg);
        throw new IteraplanTechnicalException(IteraplanErrorMessages.GENERAL_TECHNICAL_ERROR, msg);
    } finally {
        IOUtils.closeQuietly(in);
    }

    return workbook;
}