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:bo.com.offercruzmail.imp.InterpretadorMensajeGenerico.java

protected Multipart enviarPlantilla(boolean plantillaNueva, String idCargar)
        throws MessagingException, IOException {
    String nombreArchivoOrigen;// ww  w . ja  v a 2s.c  om
    String nombreAdjunto;
    List<T> lista = null;
    mensajesError = null;
    T entidad = null;
    getObjetoNegocio().setIdUsuario(idUsuario);
    getObjetoNegocio().setComandoPermiso(nombreEntidad);
    try {
        if (!plantillaNueva) {
            if ("todos".equals(idCargar)) {
                lista = getObjetoNegocio().obtenerTodos();
                nombreArchivoOrigen = nombreEntidad + "-" + "lista";
                nombreAdjunto = "lista_" + nombreEntidad + ".xlsx";
            } else {
                ID id;
                try {
                    id = convertirId(idCargar);
                } catch (Exception ex) {
                    return FormadorMensajes.enviarIdCargarNoValido();
                }
                entidad = getObjetoNegocio().recuperarPorId(id);
                if (entidad == null) {
                    return FormadorMensajes.enviarEntidadNoExiste(idCargar);
                }
                nombreArchivoOrigen = nombreEntidad;
                nombreAdjunto = nombreEntidad + "_" + idCargar + ".xlsx";
            }
        } else {
            nombreArchivoOrigen = nombreEntidad;
            nombreAdjunto = "plantilla_" + nombreEntidad + ".xlsx";
            //            if (this instanceof IInterpretadorFormularioDasometrico) {
            //                if (cargarPlantillaFormularios) {
            //                    nombreArchivoOrigen = "plantillafrm";
            //                }
            //            }
        }
        String nombreArchivoOriginal = "plantillas/" + nombreArchivoOrigen + ".xlsx";
        File archivoCopia = UtilitariosMensajes.reservarNombre(nombreEntidad);
        UtilitariosMensajes.copiarArchivo(new File(nombreArchivoOriginal), archivoCopia);
        archivosTemporales.add(archivoCopia);
        FileInputStream fis = null;
        OutputStream os = null;
        try {
            Workbook libro;
            fis = new FileInputStream(archivoCopia);
            libro = WorkbookFactory.create(fis);
            hojaActual = new HojaExcelHelper(libro.getSheetAt(0));
            if (plantillaNueva) {
                preparPlantillaAntesDeEnviar(libro);
            } else {
                if (lista != null) {
                    mostrarLista(lista);
                } else {
                    mostrarEntidad(entidad, libro);
                }
            }
            if (mensajesError != null) {
                return FormadorMensajes.enviarErroresNegocio(mensajesError);
            }
            //Guardamos cambio
            os = new FileOutputStream(archivoCopia);
            libro.write(os);
        } catch (InvalidFormatException ex) {

        } finally {
            if (fis != null) {
                fis.close();
            }
            if (os != null) {
                os.close();
            }
        }
        String textoMensaje;
        if (plantillaNueva) {
            textoMensaje = escapeHtml4("La plantilla est adjunta a este mensaje.");
        } else if (lista != null) {
            textoMensaje = "La consulta ha devuelto " + lista.size() + " registro(s).";
        } else {
            textoMensaje = escapeHtml4("El registro solicitado est adjunto a este mensaje");
        }
        Multipart cuerpo = new MimeMultipart();
        BodyPart adjunto = new MimeBodyPart();
        DataSource origen = new FileDataSource(archivoCopia);
        adjunto.setDataHandler(new DataHandler(origen));
        adjunto.setFileName(nombreAdjunto);
        cuerpo.addBodyPart(FormadorMensajes.getBodyPartEnvuelto(textoMensaje));
        cuerpo.addBodyPart(adjunto);
        return cuerpo;
    } catch (PermisosInsuficientesException ex) {
        appendException(new BusinessExceptionMessage(ex.getMessage(), "Autentificacion"));
    }
    if (mensajesError != null) {
        return FormadorMensajes.enviarErroresNegocio(mensajesError);
    }
    return null;
}

From source file:bo.com.offercruzmail.LectorBandejaCorreo.java

private Multipart procesarPorAdjunto(Message mensaje, Integer idUsuario) throws MessagingException {
    adjunto = null;//w  w  w  .  ja v  a  2  s.  c  o  m
    try {
        //No se pudo procesar por asunto, leer el adjunto si tiene
        adjunto = UtilitariosMensajes.bajarPrimerAdjunto(mensaje);
    } catch (IOException ex) {
        LOG.log(Level.SEVERE, null, ex);
        return FormadorMensajes.getMensajeUsuarioAyuda();
    }
    if (adjunto == null) {
        return FormadorMensajes.getMensajeUsuarioAyuda();
    }
    FileInputStream fis = null;
    try {

        Workbook libro;
        fis = new FileInputStream(adjunto);
        libro = WorkbookFactory.create(fis);
        Sheet hoja = libro.getSheetAt(0);
        Row fila = hoja.getRow(0);
        if (fila == null) {
            return FormadorMensajes.getMensajeUsuarioAyuda();
        }
        Cell celda = fila.getCell(0);
        if (celda == null) {
            return FormadorMensajes.getMensajeUsuarioAyuda();
        }
        String nombreEntidad = HojaExcelHelper.getValorCelda(celda).toLowerCase();
        interprete = InterpretadorMensajeGenerico.getMapaObjetos().get(nombreEntidad);
        if (interprete == null) {
            return FormadorMensajes.getMensajeUsuarioAyuda();
        }
        interprete.setLectorBandejaCorreo(this);
        interprete.setIdUsuario(idUsuario);
        interprete.setNombreEntidad(nombreEntidad);
        return interprete.interpretarHojaExcel(hoja);
    } catch (IOException | InvalidFormatException ex) {
        LOG.log(Level.SEVERE, "Error Leyendo adjunto", ex);
        return FormadorMensajes.getMensajeUsuarioAyuda();
    } finally {
        if (fis != null) {
            try {
                fis.close();
            } catch (IOException ex) {
                Logger.getLogger(LectorBandejaCorreo.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}

From source file:bouttime.fileinput.ExcelFileInput.java

License:Open Source License

/**
 * Input wrestlers from a MS Excel formatted file.
 *
 * @param file File to read data from/*from   ww w.  ja  va  2s.c  om*/
 * @param config Map of config parameters for column indexes, start and stop
 * row indexes, and the sheet number
 * @param dao Data access object
 *
 * @return A FileInputResult object with the results of the input operation
 */
private FileInputResult addWrestlersFromFile(File file, Map config, Dao dao) {
    Integer recordsProcessed = Integer.valueOf(0);
    Integer recordsAccepted = Integer.valueOf(0);
    Integer recordsRejected = Integer.valueOf(0);
    List<String> rejects = new ArrayList<String>();

    try {
        int sheetNumber = Integer.parseInt((String) config.get("sheet")) - 1;
        int startRow = Integer.parseInt((String) config.get("startRow")) - 1;
        int endRow = Integer.parseInt((String) config.get("endRow"));
        int fNameCol = Integer.parseInt((String) config.get("firstName")) - 1;
        int lNameCol = Integer.parseInt((String) config.get("lastName")) - 1;
        int tNameCol = Integer.parseInt((String) config.get("teamName")) - 1;
        int gNameCol = Integer.parseInt((String) config.get("geo")) - 1;
        int classCol = Integer.parseInt((String) config.get("classification")) - 1;
        int divCol = Integer.parseInt((String) config.get("division")) - 1;
        int wtClassCol = Integer.parseInt((String) config.get("weightClass")) - 1;
        int actWtCol = Integer.parseInt((String) config.get("actualWeight")) - 1;
        int levelCol = Integer.parseInt((String) config.get("level")) - 1;
        int idCol = Integer.parseInt((String) config.get("serialNumber")) - 1;
        InputStream inp = new FileInputStream(file);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(sheetNumber);

        logger.info("Excel File Input configuration :" + "\n    sheet=" + sheetNumber + "\n    startRow="
                + startRow + "\n    endRow=" + endRow + "\n    first=" + fNameCol + "\n    last=" + lNameCol
                + "\n    team=" + tNameCol + "\n    geo=" + gNameCol + "\n    class=" + classCol + "\n    div="
                + divCol + "\n" + "\n    wtClass=" + wtClassCol + "\n    actWt=" + actWtCol + "\n    level="
                + levelCol + "\n    id=" + idCol);

        int i = startRow;
        while (i < endRow) {
            Wrestler w = new Wrestler();
            Row row = sheet.getRow(i);

            if (row == null) {
                logger.warn("Row is null : " + i);
                i++;
                continue;
            }

            if (fNameCol >= 0) {
                String fName = row.getCell(fNameCol).getRichStringCellValue().getString();

                // Should always have a first name, so this is a
                // reasonable check for the end of data.
                if (fName.isEmpty())
                    break; // found end of data

                w.setFirstName(fName.trim());
            }

            if (lNameCol >= 0) {
                String lName = row.getCell(lNameCol).getRichStringCellValue().getString();

                // Should always have a last name, so this is a
                // reasonable check for the end of data.
                if (lName.isEmpty())
                    break; // found end of data

                w.setLastName(lName.trim());
            }

            if (divCol >= 0) {
                String div;
                if (row.getCell(divCol).getCellType() == Cell.CELL_TYPE_STRING) {
                    div = row.getCell(divCol).getRichStringCellValue().getString();
                } else {
                    div = Long.valueOf(Double.valueOf(row.getCell(divCol).getNumericCellValue()).longValue())
                            .toString();
                }

                w.setAgeDivision(div.trim());
            }

            if (wtClassCol >= 0) {
                String wtClass;
                if (row.getCell(wtClassCol).getCellType() == Cell.CELL_TYPE_STRING) {
                    wtClass = row.getCell(wtClassCol).getRichStringCellValue().getString();
                } else {
                    wtClass = Long
                            .valueOf(Double.valueOf(row.getCell(wtClassCol).getNumericCellValue()).longValue())
                            .toString();
                }

                w.setWeightClass(wtClass.trim());
            }

            if (actWtCol >= 0) {
                String actWt;
                if (row.getCell(actWtCol).getCellType() == Cell.CELL_TYPE_STRING) {
                    actWt = row.getCell(actWtCol).getRichStringCellValue().getString();
                } else {
                    actWt = Long
                            .valueOf(Double.valueOf(row.getCell(actWtCol).getNumericCellValue()).longValue())
                            .toString();
                }

                w.setActualWeight(actWt.trim());
            }

            if (classCol >= 0) {
                String classification;
                if (row.getCell(classCol).getCellType() == Cell.CELL_TYPE_STRING) {
                    classification = row.getCell(classCol).getRichStringCellValue().getString();
                } else {
                    classification = Long
                            .valueOf(Double.valueOf(row.getCell(classCol).getNumericCellValue()).longValue())
                            .toString();
                }

                w.setClassification(classification.trim());
            }

            if (tNameCol >= 0) {
                String tName;
                if (row.getCell(tNameCol).getCellType() == Cell.CELL_TYPE_STRING) {
                    tName = row.getCell(tNameCol).getRichStringCellValue().getString();
                } else {
                    tName = Long
                            .valueOf(Double.valueOf(row.getCell(tNameCol).getNumericCellValue()).longValue())
                            .toString();
                }

                w.setTeamName(tName.trim());
            }

            if (gNameCol >= 0) {
                String gName;
                if (row.getCell(gNameCol).getCellType() == Cell.CELL_TYPE_STRING) {
                    gName = row.getCell(gNameCol).getRichStringCellValue().getString();
                } else {
                    gName = Long
                            .valueOf(Double.valueOf(row.getCell(gNameCol).getNumericCellValue()).longValue())
                            .toString();
                }

                w.setGeo(gName.trim());
            }

            if (idCol >= 0) {
                String id;
                if (row.getCell(idCol).getCellType() == Cell.CELL_TYPE_STRING) {
                    id = row.getCell(idCol).getRichStringCellValue().getString();
                } else {
                    id = Long.valueOf(Double.valueOf(row.getCell(idCol).getNumericCellValue()).longValue())
                            .toString();
                }

                w.setSerialNumber(id.trim());
            }

            if (levelCol >= 0) {
                String level;
                if (row.getCell(levelCol).getCellType() == Cell.CELL_TYPE_STRING) {
                    level = row.getCell(levelCol).getRichStringCellValue().getString();
                } else {
                    level = Long
                            .valueOf(Double.valueOf(row.getCell(levelCol).getNumericCellValue()).longValue())
                            .toString();
                }

                w.setLevel(level.trim());
            }

            recordsProcessed++;

            if (dao.addWrestler(w)) {
                recordsAccepted++;
                logger.debug("Added wrestler : " + w);
            } else {
                recordsRejected++;
                rejects.add(String.format("%s %s", w.getFirstName(), w.getLastName()));
                logger.warn("Duplicate: " + w.getFirstName() + " " + w.getLastName());
            }

            i++;
        }
    } catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException ife) {
        JFrame mainFrame = BoutTimeApp.getApplication().getMainFrame();
        JOptionPane.showMessageDialog(mainFrame,
                "Error while handling the spreadsheet file.\n\n" + "This is not a file in an Excel file.",
                "Spreadsheet file error", JOptionPane.ERROR_MESSAGE);
        logger.error(ife.getLocalizedMessage() + "\n" + Arrays.toString(ife.getStackTrace()));
    } catch (Exception e) {
        JFrame mainFrame = BoutTimeApp.getApplication().getMainFrame();
        JOptionPane.showMessageDialog(mainFrame, "Error while handling the spreadsheet file.\n\n" + e,
                "Spreadsheet file error", JOptionPane.ERROR_MESSAGE);
        logger.error(e.getLocalizedMessage() + "\n" + Arrays.toString(e.getStackTrace()));
    }

    return (new FileInputResult(recordsProcessed, recordsAccepted, recordsRejected, rejects));
}

From source file:br.com.gartech.nfse.integrador.util.ExcelHelper.java

public File convertXMLFile(String sourceFilePath) throws InvalidFormatException, IOException {

    File file = new File(sourceFilePath);

    if (file != null && file.isFile() && file.exists()) {
        this.sourceFile = file;
    } else {//w w w.  j a  v a 2  s . c o m
        String baseDir = Launcher.class.getResource("/").getPath();
        String url = baseDir + sourceFilePath;

        file = new File(url);

        if (file != null && file.isFile() && file.exists()) {
            this.sourceFile = file;
        }
    }

    this.targetFile = new File(this.sourceFile.getCanonicalPath() + ".xml");
    this.workbook = WorkbookFactory.create(this.sourceFile);
    this.xml = workbook2xml(workbook);

    BufferedWriter bw = null;
    out = new FileOutputStream(this.targetFile);
    osw = new OutputStreamWriter(out, "UTF-8");

    bw = new BufferedWriter(osw);
    bw.write(xml);
    bw.flush();
    bw.close();

    return this.targetFile;
}

From source file:br.com.gartech.nfse.integrador.util.ExcelHelper.java

public String convertXML(String sourceFilePath) throws InvalidFormatException, IOException {

    File file = new File(sourceFilePath);

    if (file != null && file.exists() && file.isFile()) {
        this.sourceFile = file;
    } else {//  w ww.jav a 2 s.  c  om
        String baseDir = Launcher.class.getResource("/").getPath();
        String url = baseDir + sourceFilePath;

        file = new File(url);

        if (file != null && file.isFile() && file.exists()) {
            this.sourceFile = file;
        }
    }

    this.workbook = WorkbookFactory.create(this.sourceFile);
    this.xml = workbook2xml(workbook);
    return this.xml;
}

From source file:br.com.gartech.nfse.integrador.util.ExcelHelper.java

public File exportExcel(String sourceFilePath, String targetFilePath, boolean hasTemplete) throws IOException,
        SAXException, ParserConfigurationException, XPathExpressionException, InvalidFormatException {
    sourceFile = new File(sourceFilePath);
    targetFile = new File(targetFilePath);

    InputStream xmlInputStream = new FileInputStream(sourceFile);
    InputStream excelInputStream = new FileInputStream(targetFile);

    Document document = DocumentBuilderFactory.newInstance().newDocumentBuilder().parse(xmlInputStream);
    Workbook workbook = WorkbookFactory.create(excelInputStream);

    workbook = bindXml(document, workbook);

    document = null;//from ww w.  j a  v a 2  s.c om
    xmlInputStream.close();
    xmlInputStream = null;
    excelInputStream.close();
    excelInputStream = null;

    OutputStream excelOutputStream = new FileOutputStream(targetFile);
    workbook.write(excelOutputStream);
    excelOutputStream.close();
    excelOutputStream = null;
    return targetFile;
}

From source file:br.com.objectos.comuns.io.xls.XlsFile.java

License:Apache License

public static XlsFile parse(InputStream inputStream) {
    try {/*w ww.  j  a v  a 2 s . c om*/
        Workbook workbook = WorkbookFactory.create(inputStream);
        return new XlsFile(workbook);
    } catch (IOException e) {
        throw new ComunsIOException(e);
    } catch (InvalidFormatException e) {
        throw new ComunsIOException(e);
    }
}

From source file:br.ufal.cideei.util.count.MetricsTable.java

License:Open Source License

public MetricsTable(int max, File output) throws FileNotFoundException, IOException, InvalidFormatException {
    MAX_SIZE = max;/* w  w w  .  ja  v a 2 s. c o  m*/
    this.output = output;
    workBook = WorkbookFactory.create(new FileInputStream(output));
    this.sheet = workBook.createSheet();
}

From source file:br.ufal.cideei.util.count.MetricsTable.java

License:Open Source License

public MetricsTable(File output) throws FileNotFoundException, IOException, InvalidFormatException {
    MAX_SIZE = Integer.MAX_VALUE;
    this.output = output;
    if (output.exists()) {
        workBook = WorkbookFactory.create(new FileInputStream(output));
    } else {/*  w w w . j  av a  2 s . c om*/
        workBook = new HSSFWorkbook();
    }
    this.sheet = workBook.createSheet();
}

From source file:br.ufal.cideei.util.count.SummaryBuilder.java

License:Open Source License

public static void buildSummary(String splShortName)
        throws InvalidFormatException, FileNotFoundException, IOException {

    // final String userHomeFolder = System.getProperty("user.home").substring(3);
    String userHomeFolder = "C:\\tst";
    final String output = userHomeFolder + File.separator + "summ.xls";
    File outputFile = new File(output);
    Workbook outputWorkbook;/*from   ww  w. j a va  2 s .  c o  m*/
    if (!outputFile.exists()) {
        outputFile.createNewFile();
        outputWorkbook = new HSSFWorkbook();
    } else {
        FileInputStream inputFileStream = new FileInputStream(outputFile);
        outputWorkbook = WorkbookFactory.create(inputFileStream);
    }

    {
        List<String> referencesForRDA3 = new ArrayList<String>();
        List<String> referencesForUVA3 = new ArrayList<String>();
        List<String> referencesForRDA2 = new ArrayList<String>();
        List<String> referencesForUVA2 = new ArrayList<String>();
        String fileName = "fs-" + splShortName + ".xls";
        String filePath = userHomeFolder + File.separator;
        String fullFileName = filePath + File.separator + "fs-" + splShortName + ".xls";
        Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(fullFileName)));
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Row headerRow = sheet.getRow(0);
            for (Cell cell : headerRow) {
                String stringCellValue = cell.getStringCellValue();
                if (stringCellValue.equals("rd")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForRDA2.add(cellRefForAnotherSheet);
                } else if (stringCellValue.equals("uv")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForUVA2.add(cellRefForAnotherSheet);
                } else if (stringCellValue.equals("rd (a3)")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForRDA3.add(cellRefForAnotherSheet);
                } else if (stringCellValue.equals("uv (a3)")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForUVA3.add(cellRefForAnotherSheet);
                }
            }
        }
        if (outputWorkbook.getSheet(splShortName) != null) {
            outputWorkbook.removeSheetAt(outputWorkbook.getSheetIndex(splShortName));
        }
        Sheet outputSheet = outputWorkbook.createSheet(splShortName);
        Row RDA2Row = outputSheet.createRow(0);
        RDA2Row.createCell(0).setCellValue("RD A2");
        for (int i = 0; i < referencesForRDA2.size(); i++) {
            Cell createdCell = RDA2Row.createCell(i + 1);
            System.out.println(referencesForRDA2.get(i));
            createdCell.setCellType(Cell.CELL_TYPE_FORMULA);
            createdCell.setCellValue(referencesForRDA2.get(i));
        }
        Row UVA2Row = outputSheet.createRow(1);
        UVA2Row.createCell(0).setCellValue("UV A2");
        for (int i = 0; i < referencesForUVA2.size(); i++) {
            Cell createdCell = UVA2Row.createCell(i + 1);
            createdCell.setCellFormula(referencesForUVA2.get(i));
        }
        Row RDA3Row = outputSheet.createRow(2);
        RDA3Row.createCell(0).setCellValue("RD A3");
        for (int i = 0; i < referencesForRDA3.size(); i++) {
            Cell createdCell = RDA3Row.createCell(i + 1);
            createdCell.setCellFormula(referencesForRDA3.get(i));
        }
        Row UVA3Row = outputSheet.createRow(3);
        UVA3Row.createCell(0).setCellValue("UV A3");
        for (int i = 0; i < referencesForUVA3.size(); i++) {
            Cell createdCell = UVA3Row.createCell(i + 1);
            createdCell.setCellFormula(referencesForUVA3.get(i));
        }
    }
    FileOutputStream fileOutputStream = new FileOutputStream(outputFile);
    outputWorkbook.write(fileOutputStream);
    fileOutputStream.close();
}