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

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

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

Introduction

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

Prototype

@Override
public HSSFSheet getSheetAt(int index)

Source Link

Document

Get the HSSFSheet object at the given index.

Usage

From source file:bs.proveedores.web.informe.CuentaCorrienteProveedorBean.java

public void postProcessXLS(Object document) {

    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow header = sheet.getRow(0);/* w w  w  . j av  a 2 s.  co m*/

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.ORANGE.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);
        cell.setCellStyle(cellStyle);
    }
}

From source file:BUS.FileManager.java

private Sheet readXLSFile(File f) throws FileNotFoundException, IOException, InvalidFormatException {
    InputStream ExcelFileToRead = new FileInputStream(f);
    HSSFWorkbook wb = (HSSFWorkbook) WorkbookFactory.create(ExcelFileToRead);
    HSSFSheet sheet = wb.getSheetAt(0);
    return sheet;
}

From source file:ch.astina.hesperid.util.jasper.JasperExcelStreamResponse.java

License:Apache License

@Override
public void exportReportToStream(JasperPrint jasperPrint, OutputStream outputStream) throws Exception {
    JRXlsExporter exporter = new JRXlsExporter();
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    exporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
    exporter.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, baos);
    exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS,
            this.removeEmptySpaceBetweenRows);
    exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS,
            this.removeEmptySpaceBetweenColumns);
    exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, this.printWhitePageBackground);
    exporter.setParameter(JRXlsExporterParameter.IS_FONT_SIZE_FIX_ENABLED, this.fixFontSize);
    exporter.setParameter(JRXlsExporterParameter.IS_IGNORE_GRAPHICS, this.ignoreGraphics);
    exporter.exportReport();//  ww  w  .  jav  a  2  s. c  o m

    HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(baos.toByteArray()));
    workbook.getSheetAt(0).setAutobreaks(true);
    workbook.getSheetAt(0).getPrintSetup().setFitHeight((short) jasperPrint.getPages().size());
    workbook.getSheetAt(0).getPrintSetup().setFitWidth((short) 1);

    SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");

    HSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

    for (Integer x = 0; x < workbook.getSheetAt(0).getPhysicalNumberOfRows(); x++) {
        HSSFRow row = workbook.getSheetAt(0).getRow(x);

        Iterator<Cell> ci = row.cellIterator();

        Cell c = null;
        Date d = null;

        while (ci.hasNext()) {
            c = ci.next();
            try {
                d = sdf.parse(c.getStringCellValue().trim());
                c.setCellValue(d);
                c.setCellStyle(cellStyle);
            } catch (Exception e) {
            }
        }
    }

    workbook.write(outputStream);
}

From source file:ch.elexis.core.importer.div.importers.ExcelWrapper.java

License:Open Source License

/**
 * Load a specific page of the given Excel Spreadsheet
 * /*from ww w  .  j  av a 2  s.  c  om*/
 * @param file
 *            filename of the Excel file
 * @param page
 *            page to use
 * @return true on success
 * @deprecated use load(InputStream) instead
 */
@Deprecated
public boolean load(final String file, final int page) {
    try {
        fs = new POIFSFileSystem(new FileInputStream(file));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        sheet = wb.getSheetAt(page);
        return true;
    } catch (Exception ex) {
        return false;
    }
}

From source file:ch.elexis.core.importer.div.importers.ExcelWrapper.java

License:Open Source License

/**
 * Load a specific page of the given Excel Spreadsheet
 * /*from w  w  w.  j  av a 2 s  .c  om*/
 * @param bytes
 *            Excel content as byte array
 * @param page
 *            page to use
 * @return true on success
 */
public boolean load(final InputStream inputStream, final int page) {
    try {
        fs = new POIFSFileSystem(inputStream);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        sheet = wb.getSheetAt(page);
        return true;
    } catch (Exception ex) {
        return false;
    }
}

From source file:cliente.ReclamoPiezas.GUINuevoReclamoFabrica.java

License:Open Source License

protected void llenarFormulario() {
    String archivo = "Formulario_SRC.xls";
    Date hoy = new Date();
    String nombre_archivo = pedido_pieza.getPedido().getReclamo().getReclamante().getNombre_apellido()
            + "_pedido_" + pedido_pieza.getNumero_pedido();
    Boolean inmovilizado = pedido_pieza.getPedido().getReclamo().getInmovilizado();
    String vin = pedido_pieza.getPedido().getReclamo().getVehiculo().getVin();
    String dominio = pedido_pieza.getPedido().getReclamo().getVehiculo().getDominio();
    String nombre_cliente = pedido_pieza.getPedido().getReclamo().getReclamante().getNombre_apellido();
    String telefono = mediador.obtenerTelefono(pedido_pieza);
    try {//from  w ww. j a v a  2s.  com

        InputStream ExcelFileToRead = new FileInputStream("Formulario_SRC.xls");

        HSSFWorkbook formulario_src = new HSSFWorkbook(ExcelFileToRead);

        HSSFSheet sheet = formulario_src.getSheetAt(0);

        HSSFRow row;
        HSSFCell cell;
        //tipo reclamo
        row = sheet.getRow(10);
        cell = row.getCell(4);
        cell.setCellValue("Repuestos");
        //inmovilizado
        if (inmovilizado) {
            row = sheet.getRow(11);
            cell = row.getCell(4);
            cell.setCellValue("Si");
        } else {
            row = sheet.getRow(11);
            cell = row.getCell(4);
            cell.setCellValue("No");
        }
        //garantia
        row = sheet.getRow(12);
        cell = row.getCell(4);
        cell.setCellValue("Si");
        //Vin
        row = sheet.getRow(15);
        cell = row.getCell(4);
        cell.setCellValue(vin);
        //dominio
        row = sheet.getRow(16);
        cell = row.getCell(4);
        cell.setCellValue(dominio);
        //modelo
        LinkedList<String> modelos = new LinkedList<String>();
        modelos.add("FLUENCE");
        modelos.add("CLIO");
        modelos.add("KANGOO");
        modelos.add("SYMBOL");
        modelos.add("LOGAN");
        modelos.add("SANDERO");
        modelos.add("MASTER");
        modelos.add("KOLEOS");
        modelos.add("MEGANE III");
        modelos.add("DUSTER");
        modelos.add("LATITUDE");
        if (modelos.contains(pedido_pieza.getPedido().getReclamo().getVehiculo().getModelo().getNombre_modelo()
                .toUpperCase())) {
            row = sheet.getRow(17);
            cell = row.getCell(4);
            cell.setCellValue(pedido_pieza.getPedido().getReclamo().getVehiculo().getModelo().getNombre_modelo()
                    .toUpperCase());
        } else {
            row = sheet.getRow(17);
            cell = row.getCell(4);
            cell.setCellValue("OTROS");
        }
        //nombre cliente
        row = sheet.getRow(11);
        cell = row.getCell(8);
        cell.setCellValue(nombre_cliente);
        //telefono
        row = sheet.getRow(12);
        cell = row.getCell(8);
        cell.setCellValue(telefono);
        //motivo
        row = sheet.getRow(21);
        cell = row.getCell(2);
        cell.setCellValue(ePMotivo.getText());
        //pieza
        row = sheet.getRow(33);
        cell = row.getCell(3);
        cell.setCellValue(pedido_pieza.getPieza().getNumero_pieza());
        row = sheet.getRow(33);
        cell = row.getCell(4);
        cell.setCellValue(pedido_pieza.getPieza().getDescripcion());
        row = sheet.getRow(33);
        cell = row.getCell(7);
        cell.setCellValue("1");
        row = sheet.getRow(33);
        cell = row.getCell(8);
        cell.setCellValue(pedido_pieza.getNumero_pedido());

        //write this workbook to an Outputstream.
        int i = 0;
        String aux = nombre_archivo;
        File formulario_guardado = new File(RootAndIp.getPath_reportes() + aux + ".xls");
        while (formulario_guardado.exists()) {
            aux = nombre_archivo + "_" + i;
            formulario_guardado = new File(RootAndIp.getPath_reportes() + aux + ".xls");
            i++;
        }

        FileOutputStream fileOut = new FileOutputStream(formulario_guardado);
        formulario_src.write(fileOut);
        fileOut.flush();
        fileOut.close();

        Desktop.getDesktop().open(formulario_guardado);

        formulario = formulario_guardado.getPath();
        btnVerFormulario.setEnabled(true);
        btnEnviarFormulario.setEnabled(true);
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:cn.vlabs.umt.common.xls.UserXLSParser.java

License:Apache License

public UserXLSParser(InputStream in) throws XLSException {
    HSSFWorkbook workbook;
    try {//from   www  . j  a  v a  2  s. co m
        workbook = new HSSFWorkbook(in);
        sheet = workbook.getSheetAt(0);
        total = sheet.getLastRowNum();
    } catch (IOException e) {
        throw new XLSException(e.getMessage());
    }
}

From source file:co.com.smartcode.bitcom.managedbeans.crud.utils.ExcelUtils.java

public static List<Producto> getProductosFromXls(byte[] bytes) {
    List<Producto> productos = new ArrayList<>();
    try {//from w  w w  . j  a v a 2s . c om
        HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytes));
        HSSFSheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Producto producto = new Producto();
            Row row = rowIterator.next();
            String nombre = row.getCell(0).getStringCellValue();
            producto.setNombre(nombre);
            String referencia = getString(row, 1);
            producto.setReferencia(referencia);
            if (row.getCell(2) != null) {
                String nombreMarca = row.getCell(2).getStringCellValue();
                if (nombreMarca != null && nombreMarca.trim().length() > 0) {
                    Marca marca = new Marca();
                    marca.setNombre(nombreMarca);
                    producto.setMarca(marca);
                }
            }
            if (row.getCell(3) != null) {
                String imagen = row.getCell(3).getStringCellValue();
                producto.setImg(imagen);
            }
            BigDecimal precio = null;
            try {
                String precioString = getString(row, 4);
                precio = new BigDecimal(precioString);
                producto.setPrecio(precio);
            } catch (Exception e) {
                continue;
            }
            if (row.getCell(5) != null) {
                BigDecimal precioOferta = null;
                try {
                    String precioString = getString(row, 5);
                    precioOferta = new BigDecimal(precioString);
                    producto.setPrecioOferta(precioOferta);
                } catch (Exception e) {
                }
            }

            if (producto.getPrecioOferta() == null) {
                producto.setPrecioOferta(new BigDecimal("0"));
            }

            if (row.getCell(6) != null) {
                String descripcion = row.getCell(6).getStringCellValue();
                producto.setDescripcion(descripcion);
            }

            if (row.getCell(7) != null) {
                String especificaciones = row.getCell(7).getStringCellValue();
                producto.setEspecificaciones(especificaciones);
            }
            if (row.getCell(8) != null) {
                String nombreSeccion = row.getCell(8).getStringCellValue();
                if (nombreSeccion != null && nombreSeccion.trim().length() > 0) {
                    String[] split = nombreSeccion.split("-");
                    if (split.length == 2) {
                        Seccion seccion = new Seccion();
                        seccion.setNombre(split[0]);
                        Subseccion subseccion = new Subseccion();
                        subseccion.setNombre(split[1]);
                        subseccion.setSeccion(seccion);
                        producto.setSubseccion(subseccion);
                    }
                }
            }
            productos.add(producto);
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
    return productos;
}

From source file:com.accenture.control.GerenciaPlanilhaTS.java

public void geraNovaPlanilhaTS(String dir, List<TesteCaseTSBean> listTS)
        throws FileNotFoundException, IOException {

    FileInputStream arquivo = new FileInputStream(new File(dir));
    HSSFWorkbook workbook = new HSSFWorkbook(arquivo);
    HSSFSheet sheetTS = workbook.getSheetAt(0);

    HSSFDataFormat format = workbook.createDataFormat();
    HSSFCellStyle estilo = workbook.createCellStyle();
    String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);//from  ww  w  . jav a 2s.  com

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);

    for (int i = 0; i < listTS.size(); i++) {

        estilo.setDataFormat(format.getFormat(formatData));
        estilo.setFillBackgroundColor(HSSFColor.GREEN.index);

        row = sheetTS.getRow(linha);

        descriptionPlan = row.getCell(0);
        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);

        descriptionPlan.setCellValue(listTS.get(i).getTestPlan());
        prj.setCellValue(listTS.get(i).getSTIPRJ());
        fase.setCellValue(listTS.get(i).getFASE());
        testPhase.setCellValue(listTS.get(i).getTestPhase());
        testScriptName.setCellValue(listTS.get(i).getTestScriptName());
        testScriptDescription.setCellValue(listTS.get(i).getTestScriptDescription());
        stepNo.setCellValue(listTS.get(i).getSTEP_NUMERO());
        stepDescription.setCellValue(listTS.get(i).getStepDescription());
        expectedResults.setCellValue(listTS.get(i).getExpectedResults());
        product.setCellValue(listTS.get(i).getProduct());
        dataPlanejada.setCellValue(listTS.get(i).getDataPlanejada());

        dataPlanejada.setCellStyle(estilo);

        linha = linha + 2;

    }

    FileOutputStream fileOut = new FileOutputStream(new File(dir));
    workbook.write(fileOut);
    fileOut.close();
    arquivo.close();

}

From source file:com.accounting.accountMBean.DifferentAccReports.java

public void postProcessXLS(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");

    ndc = new NepaliDateConverter();
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);
    HSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(0).getLastCellNum();

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);//from w w w  .  j av a  2  s .co  m
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue(PageName);
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String generatedDate = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + generatedDate + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    HSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("To Date: " + startDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 4));

    //        HSSFRow lastRow;
    //        double totalDr = 0;
    //        for (Row row : sheet) {
    //            if (row.getRowNum() > 4) {
    //                String cost = row.getCell(3).getStringCellValue();
    //                if (cost != null && !cost.isEmpty()) {
    //                    row.getCell(3).setCellType(HSSFCell.CELL_TYPE_BLANK);
    //                    row.getCell(3).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    //                    row.getCell(3).setCellValue(Double.parseDouble(cost.replace(",", "")));
    //                    totalDr += Double.parseDouble(cost.replace(",", ""));
    //                }
    //            }
    //        }

}