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:com.github.poi.XlsxUtils.java

License:Apache License

public static InputStream decrypt(final InputStream inputStream, final String pwd) throws Exception {
    try {//  ww  w . j ava 2 s.  c om
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        EncryptionInfo info = new EncryptionInfo(fs);
        Decryptor d = Decryptor.getInstance(info);
        if (!d.verifyPassword(pwd)) {
            throw new RuntimeException("incorrect password");
        }
        return d.getDataStream(fs);
    } finally {
        IOUtils.closeQuietly(inputStream);
    }
}

From source file:com.gnadenheimer.mg.frames.admin.FrameEntidadesAdmin.java

private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed
    try {/*from w w w.ja v  a 2  s .  c o m*/
        JFileChooser fc = new JFileChooser();
        Integer returnVal = fc.showOpenDialog(this);

        if (returnVal == JFileChooser.APPROVE_OPTION) {

            File file = fc.getSelectedFile();
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row;
            HSSFCell cell;

            Integer rows; // No of rows
            rows = sheet.getPhysicalNumberOfRows();

            Integer cols = 0; // No of columns
            Integer tmp = 0;

            // This trick ensures that we get the data properly even if it doesn't start from first few rows
            for (Integer i = 0; i < 10 || i < rows; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                    if (tmp > cols) {
                        cols = tmp;
                    }
                }
            }

            for (Integer r = 1; r <= rows; r++) {
                row = sheet.getRow(r);
                if (row != null) {

                    if (!row.getCell(0).getStringCellValue().equals("")) {
                        TblEntidades miembro = new TblEntidades();
                        miembro.setNombres(row.getCell(0).getStringCellValue());

                        if (row.getCell(1) != null) {
                            miembro.setApellidos(row.getCell(1).getStringCellValue());
                        } else {
                            miembro.setApellidos("");
                        }
                        if (row.getCell(2).getCellType() == Cell.CELL_TYPE_STRING) {
                            if (row.getCell(2).getStringCellValue().replaceAll("[^\\d.]", "").equals("")) {
                                miembro.setCtacte(99999);
                            } else {
                                miembro.setCtacte(Integer.valueOf(
                                        row.getCell(2).getStringCellValue().replaceAll("[^\\d.]", "")));
                            }

                        } else if (row.getCell(2).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            miembro.setCtacte((int) (row.getCell(2).getNumericCellValue()));
                        }
                        List<TblEntidades> duplicadoList = entityManager.createQuery(
                                "SELECT t FROM TblEntidades t where t.nombres = '" + miembro.getNombres()
                                        + "' and t.apellidos = '" + miembro.getApellidos() + "' and t.ctacte = "
                                        + miembro.getCtacte().toString(),
                                TblEntidades.class).getResultList();
                        if (duplicadoList.size() > 0) {
                            miembro = null;
                            miembro = duplicadoList.get(0);
                        }

                        if (row.getCell(3) != null) {
                            DecimalFormat df = new DecimalFormat("#0");
                            miembro.setRucSinDv(df.format(row.getCell(3).getNumericCellValue()));
                            if (miembro.getRucSinDv().equals("0")) {
                                miembro.setRucSinDv("44444401");
                            }
                        }
                        if (row.getCell(4) != null) {
                            miembro.setDomicilio(row.getCell(4).getStringCellValue());
                        }
                        if (row.getCell(5) != null) {
                            miembro.setBox((int) row.getCell(5).getNumericCellValue());
                        }
                        if (row.getCell(6) != null) {
                            miembro.setFechaNacimiento(
                                    LocalDateTime.ofInstant(row.getCell(6).getDateCellValue().toInstant(),
                                            ZoneId.systemDefault()).toLocalDate());
                        }
                        if (row.getCell(7) != null) {
                            miembro.setFechaBautismo(
                                    LocalDateTime.ofInstant(row.getCell(7).getDateCellValue().toInstant(),
                                            ZoneId.systemDefault()).toLocalDate());
                            miembro.setIsMiembroActivo(true);
                        } else {
                            miembro.setIsMiembroActivo(false);
                        }
                        if (row.getCell(8) != null) {
                            miembro.setFechaEntradaCongregacion(
                                    LocalDateTime.ofInstant(row.getCell(8).getDateCellValue().toInstant(),
                                            ZoneId.systemDefault()).toLocalDate());
                        }
                        miembro.setAporteMensual(0);
                        miembro.setIdFormaDePagoPreferida(listFormasDePago.get(0));
                        miembro.setIdUser(currentUser.getUser());

                        entityManager.persist(miembro);
                        list.add(miembro);
                    }
                }
            }

        }
    } catch (Exception ex) {
        JOptionPane.showMessageDialog(null,
                Thread.currentThread().getStackTrace()[1].getMethodName() + " - " + ex.getMessage());
        LOGGER.error(Thread.currentThread().getStackTrace()[1].getMethodName(), ex);
    }
}

From source file:com.gnadenheimer.mg.frames.operaciones.ingresos.FrameColectasDetalle.java

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
    try {/*from   www  .j a  v  a  2 s.  com*/
        JFileChooser fc = new JFileChooser();
        Integer returnVal = fc.showOpenDialog(this);

        if (returnVal == JFileChooser.APPROVE_OPTION) {

            File file = fc.getSelectedFile();
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row;

            Integer rows; // No of rows
            rows = sheet.getPhysicalNumberOfRows();

            Integer cols = 0; // No of columns
            Integer tmp = 0;

            // This trick ensures that we get the data properly even if it doesn't start from first few rows
            for (Integer i = 0; i < 10 || i < rows; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                    if (tmp > cols) {
                        cols = tmp;
                    }
                }
            }

            for (Integer r = 1; r <= rows; r++) {
                row = sheet.getRow(r);
                if (row != null) {

                    TblEventoDetalle t = new TblEventoDetalle();
                    TblEventos currEvento = (TblEventos) cboFechaColecta.getSelectedItem();
                    t.setFechahora(currEvento.getFecha().atStartOfDay());
                    t.setIdCategoriaArticulo(entityManager.find(TblCategoriasArticulos.class, 1));
                    t.setIdEvento(currEvento);
                    t.setIdUser(currentUser.getUser());

                    Integer ctaCte = 0;
                    if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) {
                        if (!row.getCell(0).getStringCellValue().replaceAll("[^\\d.]", "").equals("")) {
                            ctaCte = Integer
                                    .valueOf(row.getCell(0).getStringCellValue().replaceAll("[^\\d.]", ""));
                        }
                    } else if (row.getCell(0).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        ctaCte = (int) (row.getCell(0).getNumericCellValue());
                    }
                    if (ctaCte != 0) {
                        final Integer cc = ctaCte;
                        List<TblEntidades> list = listMiembros;
                        Optional<TblEntidades> value = list.stream().filter(a -> a.getCtacte().equals(cc))
                                .findFirst();
                        if (value.isPresent()) {
                            t.setIdEntidad(value.get());
                            t.setIdFormaDePagoPreferida(tblFormasDePagoList.get(0));
                            if (row.getCell(1).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                t.setMonto((int) (row.getCell(1).getNumericCellValue()));
                                entityManager.persist(t);
                                listEventoDetalle.add(t);
                            }
                        } else {
                            JOptionPane.showMessageDialog(null,
                                    "No hay entidad registrada con Cta.Cte N " + FormatCtaCte.format(ctaCte));
                        }
                    }
                }
            }

        }
    } catch (Exception ex) {
        JOptionPane.showMessageDialog(null,
                Thread.currentThread().getStackTrace()[1].getMethodName() + " - " + ex.getMessage());
        LOGGER.error(Thread.currentThread().getStackTrace()[1].getMethodName(), ex);
    }
}

From source file:com.hl7.main.ImportDataFromXLStoXML.java

public void displayFromExcel(String xlsPath) {
    InputStream inputStream = null;
    try {//from  www  .j  av a2s .c  om
        inputStream = new FileInputStream(xlsPath);
    } catch (FileNotFoundException e) {
        System.out.println("File not found in the specified path.");
        e.printStackTrace();
    }

    POIFSFileSystem fileSystem = null;

    try {
        // Initializing the XML document
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        Document document = builder.newDocument();
        Element rootElement = document.createElement("categories");
        document.appendChild(rootElement);
        fileSystem = new POIFSFileSystem(inputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        Integer count = workBook.getNumberOfSheets();
        ArrayList<ArrayList<String>> data = new ArrayList<ArrayList<String>>();
        for (int i = 0; i < count; i++) {
            HSSFSheet sheet = workBook.getSheetAt(i);
            Iterator<?> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                Iterator<?> cells = row.cellIterator();
                ArrayList<String> rowData = new ArrayList<String>();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    String richTextString = cell.getStringCellValue();
                    System.out.println("String: " + richTextString);
                    rowData.add(richTextString);
                } // end while
                data.add(rowData);
            } // end while
        }

        int numOfProduct = data.size();
        for (int i = 0; i < numOfProduct; i++) {
            Element productElement = document.createElement("categoryName");
            rootElement.appendChild(productElement);

            int index = 0;
            for (String s : data.get(i)) {
                String headerString = data.get(0).get(index);
                if (data.get(0).get(index).equals("image link")) {
                    headerString = "image_link";
                }

                if (data.get(0).get(index).equals("product type")) {
                    headerString = "product_type";
                }

                Element headerElement = document.createElement(headerString);
                productElement.appendChild(headerElement);
                headerElement.appendChild(document.createTextNode(s));
                index++;
            }
        }

        TransformerFactory tFactory = TransformerFactory.newInstance();

        Transformer transformer = tFactory.newTransformer();
        // Add indentation to output
        transformer.setOutputProperty(OutputKeys.INDENT, "yes");
        transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2");

        DOMSource source = new DOMSource(document);
        StreamResult result = new StreamResult(new File("products.xml"));
        // StreamResult result = new StreamResult(System.out);
        transformer.transform(source, result);

    } catch (IOException e) {
        System.out.println("IOException " + e.getMessage());
    } catch (ParserConfigurationException e) {
        System.out.println("ParserConfigurationException " + e.getMessage());
    } catch (TransformerConfigurationException e) {
        System.out.println("TransformerConfigurationException " + e.getMessage());
    } catch (TransformerException e) {
        System.out.println("TransformerException " + e.getMessage());
    }
}

From source file:com.hp.action.CustomerAction.java

public String addCustomerFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize// ww w .j  a v  a  2  s  . co  m
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    int total = 0;
    int totalFail = 0;
    String reason = "";
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

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

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the customer id null
                if (row.getCell(1) == null || row.getCell(1).getStringCellValue().compareTo("") == 0

                ) {
                    continue;
                }

                //Init Customer Object
                Customer customer = new Customer();
                //                    customer.setmStt((int)row.getCell(tmp++).getNumericCellValue());

                //System.out.println(row.getCell(1).getStringCellValue());

                if (row.getCell(1) != null && !row.getCell(1).getStringCellValue().equals("")) {
                    Customer customer2 = customerDAO.loadCustomer(row.getCell(1).getStringCellValue());
                    if (customer2 != null) {
                        reason += "Khch hng <b>" + row.getCell(1).getStringCellValue()
                                + "</b>  tn ti. ";
                    }
                } else
                    reason += "M Khch hng hng " + (i + 1) + " khng c trng. ";

                if (row.getCell(9) != null && !row.getCell(9).getStringCellValue().equals("")) {
                    Staff staff = staffDAO.loadStaff(row.getCell(9).getStringCellValue());
                    if (staff == null)
                        reason += "M Nhn vin <b>" + row.getCell(9).getStringCellValue()
                                + "</b> khng tn ti. ";
                } else
                    reason += "M Nhn vin hng " + (i + 1) + " khng c trng.";

                try {
                    if (row.getCell(1) != null)
                        customer.setMaDoiTuong(row.getCell(1).getStringCellValue());
                    if (row.getCell(2) != null)
                        customer.setDoiTuong(row.getCell(2).getStringCellValue());
                    if (row.getCell(3) != null)
                        customer.setTinhThanh(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        customer.setX(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        customer.setDiaChi(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null)
                        customer.setTuyenBanHangThu(row.getCell(6).getStringCellValue());

                    if (row.getCell(7) != null)
                        customer.setDienThoai(row.getCell(7).getStringCellValue());
                    //System.out.println(row.getCell(7).getStringCellValue());

                    if (row.getCell(8) != null)
                        customer.setFax(row.getCell(8).getStringCellValue());
                    if (row.getCell(9) != null)
                        customer.setMaNhanVien(row.getCell(9).getStringCellValue());

                    if (row.getCell(10) != null)
                        customer.setGhiChu(row.getCell(10).getStringCellValue());

                    if (row.getCell(11) != null) {
                        if (row.getCell(11).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            customer.setCoordinateX(row.getCell(11).getNumericCellValue());
                        else
                            customer.setCoordinateX(Double.parseDouble(row.getCell(12).getStringCellValue()));
                    }

                    if (row.getCell(12) != null) {
                        if (row.getCell(12).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            customer.setCoordinateY(row.getCell(12).getNumericCellValue());
                        else
                            customer.setCoordinateY(Double.parseDouble(row.getCell(12).getStringCellValue()));
                    }

                    //Add to database
                    if (customerDAO.saveOrUpdate(customer)) {
                        System.out.println("Add Object " + i);
                        total++;
                        customersTotal = total;
                    } else {
                        totalFail++;
                        continue;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    totalFail++;
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }

    reasonFail = reason;
    customersTotal = total;
    importFail = totalFail;

    return SUCCESS;
}

From source file:com.hp.action.ProductsAction.java

public String addProductFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize//ww w  . ja  v a2s  . c o m
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file-product");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    int total = 0;
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

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

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the product id null
                if (row.getCell(1) == null ||

                        (row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getStringCellValue().compareTo("") == 0)
                        || (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getNumericCellValue() <= 0)

                ) {
                    continue;
                }

                //Init Product Object
                Product product = new Product();
                //                  
                try {

                    if (row.getCell(1) != null) {
                        if (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setProductID(
                                    (new BigDecimal(row.getCell(1).getNumericCellValue())).toString());
                        else
                            product.setProductID(row.getCell(1).getStringCellValue());
                    }
                    if (row.getCell(2) != null) {
                        if (row.getCell(2).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setBarcode(
                                    (new BigDecimal(row.getCell(2).getNumericCellValue())).toString());
                        else
                            product.setBarcode(row.getCell(2).getStringCellValue());
                    }

                    if (row.getCell(3) != null)
                        product.setProductName(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        product.setBrand(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        product.setOrigin(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null) {
                        if (row.getCell(6).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setPackingSpecifications(row.getCell(6).getNumericCellValue() + "");
                        else
                            product.setPackingSpecifications(row.getCell(6).getStringCellValue());

                    }
                    if (row.getCell(7) != null) {

                        product.setQuantification(row.getCell(7).getStringCellValue());

                    }

                    if (row.getCell(8) != null) {
                        if (row.getCell(8).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setVatTax((float) row.getCell(8).getNumericCellValue());
                        else
                            product.setVatTax(Float.parseFloat(row.getCell(8).getStringCellValue()));

                    }

                    if (row.getCell(9) != null) {
                        if (row.getCell(9).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setImportPrices((float) row.getCell(9).getNumericCellValue());
                        else
                            product.setImportPrices(Float.parseFloat(row.getCell(9).getStringCellValue()));

                    }

                    if (row.getCell(10) != null) {
                        if (row.getCell(10).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setExportPrices((float) row.getCell(10).getNumericCellValue());
                        else
                            product.setExportPrices(Float.parseFloat(row.getCell(10).getStringCellValue()));

                    }
                    if (row.getCell(11) != null)
                        product.setProvider(row.getCell(11).getStringCellValue());

                    if (row.getCell(12) != null)
                        product.setDescription(row.getCell(12).getStringCellValue());
                    if (row.getCell(13) != null)
                        product.setProductImage(row.getCell(13).getStringCellValue());

                    //product.setMProvider("nhacungcap1");
                    //Add to database
                    if (productDAO.saveOrUpdate(product)) {
                        System.out.println("Add Object " + (i + 1));
                        total++;
                        productsTotal = total;
                    } else {
                        int t = 0;
                        continue;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }
    productsTotal = total;
    return SUCCESS;
}

From source file:com.hp.action.ProviderAction.java

public String addProviderFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize//ww w  .  jav  a  2s .c om
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file-provider");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    int total = 0;
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

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

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the product id null
                if (row.getCell(1) == null ||

                        (row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getStringCellValue().compareTo("") == 0)
                        || (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getNumericCellValue() <= 0)

                ) {
                    continue;
                }

                //Init Product Object
                Provider provider = new Provider();
                //                  
                try {

                    if (row.getCell(1) != null) {
                        if (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            provider.setId((new BigDecimal(row.getCell(1).getNumericCellValue())).toString());
                        else
                            provider.setId(row.getCell(1).getStringCellValue());
                    }
                    if (row.getCell(2) != null) {
                        if (row.getCell(2).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            provider.setName((new BigDecimal(row.getCell(2).getNumericCellValue())).toString());
                        else
                            provider.setName(row.getCell(2).getStringCellValue());
                    }

                    if (row.getCell(3) != null)
                        provider.setAddress(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        provider.setPhoneNumber(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        provider.setFax(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null) {
                        if (row.getCell(6).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            provider.setNote(row.getCell(6).getNumericCellValue() + "");
                        else
                            provider.setNote(row.getCell(6).getStringCellValue());

                    }

                    //Add to database
                    if (providerDAO.saveOrUpdate(provider)) {
                        System.out.println("Add Object " + (i + 1));
                        total++;
                        providersTotal = total;
                    } else {
                        int t = 0;
                        continue;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }
    providersTotal = total;
    return SUCCESS;
}

From source file:com.hp.action.StaffsAction.java

public String addStaffFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize/*from   w  w w .  j  a  v a 2 s  . co m*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file-staff");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    int total = 0;
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

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

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the staff id null
                if (row.getCell(1) == null || row.getCell(1).getStringCellValue().compareTo("") == 0

                ) {
                    continue;
                }

                //Init Staff Object                   
                Staff staff = new Staff();
                try {

                    if (row.getCell(1) != null)
                        staff.setId(row.getCell(1).getStringCellValue());
                    if (row.getCell(2) != null)
                        staff.setPw(row.getCell(2).getStringCellValue());
                    if (row.getCell(3) != null)
                        staff.setName(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        staff.setAdress(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        staff.setJob(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null)
                        staff.setPhone(row.getCell(6).getStringCellValue());

                    if (row.getCell(7) != null && row.getCell(7).getStringCellValue().compareTo("") != 0) {

                        staff.setDate(df.parse(row.getCell(7).getStringCellValue()));

                    }
                    if (row.getCell(8) != null)
                        staff.setManager(row.getCell(8).getStringCellValue());
                    if (row.getCell(9) != null) {
                        if (row.getCell(9).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            staff.setStatus(row.getCell(9).getNumericCellValue() == 1 ? true : false);
                        else
                            staff.setStatus(
                                    row.getCell(9).getStringCellValue().compareTo("1") == 0 ? true : false);
                    }

                    if (row.getCell(10) != null) {
                        if (row.getCell(10).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            staff.setPermission((int) row.getCell(10).getNumericCellValue());
                        else
                            staff.setPermission(Integer.parseInt(row.getCell(10).getStringCellValue()));
                    }

                    //Add to database
                    if (staffDAO.saveOrUpdate(staff)) {
                        System.out.println("Add Object " + (i + 1));
                        total++;
                        staffsTotal = total;
                    } else
                        continue;
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }

    staffsTotal = total;
    return SUCCESS;
}

From source file:com.hp.application.automation.tools.octane.actions.UFTParameterFactory.java

License:Apache License

public static String convertResourceMtrAsJSON(InputStream resourceMtrInputStream) throws IOException {

    //TODO: Check is exists
    poiFS = new POIFSFileSystem(resourceMtrInputStream);
    DirectoryNode root = poiFS.getRoot();

    for (Entry entry : root) {
        String name = entry.getName();
        if (name.equals("ComponentInfo")) {
            if (entry instanceof DirectoryEntry) {
                System.out.println(entry);
            } else if (entry instanceof DocumentEntry) {
                byte[] content = new byte[((DocumentEntry) entry).getSize()];
                poiFS.createDocumentInputStream("ComponentInfo").read(content);
                String fromUnicodeLE = StringUtil.getFromUnicodeLE(content);
                xmlData = fromUnicodeLE.substring(fromUnicodeLE.indexOf('<')).replaceAll("\u0000", "");
                //                    System.out.println(xmlData);
            }//from  w w  w .j av  a  2  s  .c  o  m
        }
    }
    try {
        SAXBuilder saxBuilder = new SAXBuilder(XMLReaders.NONVALIDATING, (SAXHandlerFactory) null,
                (JDOMFactory) null);
        Document document = null;
        document = saxBuilder.build(new StringReader(xmlData));
        Element classElement = document.getRootElement();
        List<Element> studentList = classElement.getChildren();
        ObjectMapper mapper = new ObjectMapper();
        ArrayList<UFTParameter> uftParameters = new ArrayList<UFTParameter>();
        UFTParameter uftParameter = new UFTParameter();
        for (int temp = 0; temp < studentList.size(); temp++) {
            Element tag = studentList.get(temp);
            if ("ArgumentsCollection".equalsIgnoreCase(tag.getName())) {
                List<Element> children = tag.getChildren();
                for (int i = 0; i < children.size(); i++) {
                    Element element = children.get(i);
                    List<Element> elements = element.getChildren();

                    for (int j = 0; j < elements.size(); j++) {

                        Element element1 = elements.get(j);
                        switch (element1.getName()) {
                        case "ArgName":
                            uftParameter.setArgName(element1.getValue());
                            break;
                        case "ArgDirection":
                            uftParameter.setArgDirection(Integer.parseInt(element1.getValue()));
                            break;
                        case "ArgDefaultValue":
                            uftParameter.setArgDefaultValue(element1.getValue());
                            break;
                        case "ArgType":
                            uftParameter.setArgType(element1.getValue());
                            break;
                        case "ArgIsExternal":
                            uftParameter.setArgIsExternal(Integer.parseInt(element1.getValue()));
                            break;
                        default:
                            logger.warning(
                                    String.format("Element name %s didn't match any case", element1.getName()));
                            break;
                        }
                    }
                    uftParameters.add(uftParameter);
                }
                return mapper.writerWithDefaultPrettyPrinter().writeValueAsString(uftParameters);
            }
        }
    } catch (Exception e) {
        logger.severe(e.getMessage());
    }
    return null;
}

From source file:com.hp.excelhandle.GetData.java

public void readExcel() {
    try {/* ww w .j  a va2  s  .  c  om*/
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("database/customer.xls"));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getPhysicalNumberOfRows();

        int cols = 0; // No of columns (max)
        int tmp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (tmp > cols)
                    cols = tmp;
            }
        }

        //            for(int r = 0; r < rows; r++) {
        //                row = sheet.getRow(r);
        //                if(row != null) {
        //                    for(int c = 0; c < cols; c++) {
        //                        cell = row.getCell((short)c);
        //                        if(cell != null) {
        //                            // Your code here
        //                        }
        //                    }
        //                }
        //            }

        row = sheet.getRow(8);
        if (row != null) {
            cell = row.getCell(1);
            if (cell != null)
                System.out.println("Row: " + 9 + ", Data: " + cell.getStringCellValue());
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
    }
}