Example usage for org.apache.poi.hssf.usermodel HSSFSheet getRow

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getRow

Introduction

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

Prototype

@Override
public HSSFRow getRow(int rowIndex)

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:br.solutio.licita.servico.ServicoSessao.java

@Override
public boolean filtraPlanilha(UploadedFile uploadArquivo) {
    HSSFWorkbook wb = null;//from w ww.  j  a  v  a 2 s  .  c  o  m
    try {
        wb = new HSSFWorkbook(uploadArquivo.getInputstream());
    } catch (IOException ex) {
        Logger.getLogger(ControladorSessao.class.getName()).log(Level.SEVERE,
                "Error InputStream ArquivoProposta", ex);
    }

    HSSFSheet planilha = wb.getSheetAt(0);
    Logger.getLogger(ControladorSessao.class.getName()).log(Level.INFO, " TAMANHO DA PLANILHA: {0}",
            planilha.getLastRowNum());

    //Indica o numero da coluna onde inicia os valores que sero retirado
    int indInicioValores = 6;
    // +1, devido a necessidade de contar mais uma coluna
    propostas = new Double[(planilha.getLastRowNum() + 1) - indInicioValores][2];

    for (int i = 6; i <= planilha.getLastRowNum(); i++) {
        HSSFRow linha = planilha.getRow(i);

        String conversao = linha.getCell(0).getStringCellValue();
        Double convertido = Double.parseDouble(conversao);
        propostas[i - indInicioValores][0] = convertido;
        propostas[i - indInicioValores][1] = linha.getCell(5).getNumericCellValue();
        numTable.add(linha.getCell(5).getNumericCellValue());
        Logger.getLogger(ControladorSessao.class.getName()).log(Level.INFO, numTable.toString());
    }
    return !numTable.isEmpty();
}

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);

    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);/*ww  w . j  ava2s  . c o m*/
    }
}

From source file:cdc.impl.datasource.office.ExcelDataSource.java

License:LGPL

private static DataColumnDefinition[] readDataModel(String sourceName, Map params)
        throws IOException, RJException {
    BufferedInputStream is = null;
    try {//w  ww . jav a2 s.c om
        is = new BufferedInputStream(new FileInputStream((String) params.get(PARAM_FILE)));
        HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is));
        String[] sheets;
        if (params.get(PARAM_SHEET) != null) {
            sheets = new String[] { (String) params.get(PARAM_SHEET) };
        } else {
            sheets = new String[wb.getNumberOfSheets()];
            for (int i = 0; i < sheets.length; i++) {
                sheets[i] = wb.getSheetName(i);
            }
        }
        if (sheets.length == 0) {
            throw new RJException("Excel file " + params.get(PARAM_FILE) + " does not provide any sheets.");
        }
        List cols = new ArrayList();
        HSSFSheet sheet = wb.getSheet(sheets[0]);
        if (sheet == null) {
            //System.out.println("Thorwing: " + "Sheet " + sheets[0] + " is not provided by file " + params.get(PARAM_FILE));
            throw new RJException(
                    "Sheet '" + sheets[0] + "' is not provided by file " + params.get(PARAM_FILE));
        }
        HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
        //first row should provide data model
        HSSFRow row = sheet.getRow(0);
        evaluator.setCurrentRow(row);
        for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = row.getCell(i);
            cols.add(new ExcelDataColumnDefinition(decodeValue(cell, evaluator),
                    DataColumnDefinition.TYPE_STRING, sourceName, i));
        }
        for (int i = 1; i < sheets.length; i++) {
            sheet = wb.getSheet(sheets[i]);
            if (sheet == null) {
                throw new RJException("Sheet '" + params.get(PARAM_SHEET) + "' is not provided by file "
                        + params.get(PARAM_FILE));
            }
            evaluator = new HSSFFormulaEvaluator(sheet, wb);
            //first row should provide data model
            row = sheet.getRow(0);
            evaluator.setCurrentRow(row);
            List localCols = new ArrayList();
            for (i = 0; i < row.getPhysicalNumberOfCells(); i++) {
                HSSFCell cell = row.getCell(i);
                DataColumnDefinition col = new ExcelDataColumnDefinition(decodeValue(cell, evaluator),
                        DataColumnDefinition.TYPE_STRING, sourceName, i);
                localCols.add(col);
            }
            List toRemove = new ArrayList();
            for (Iterator iterator = cols.iterator(); iterator.hasNext();) {
                DataColumnDefinition object = (DataColumnDefinition) iterator.next();
                if (!localCols.contains(object)) {
                    toRemove.add(object);
                }
            }
            cols.removeAll(toRemove);
        }

        return (DataColumnDefinition[]) cols.toArray(new DataColumnDefinition[] {});
    } finally {
        if (is != null) {
            is.close();
        }
    }
}

From source file:ch.javasoft.metabolic.generate.ExcelGenerator.java

License:BSD License

protected static HSSFCell getCell(HSSFSheet sheet, int row, int col, boolean create) {
    HSSFRow xlsRow = sheet.getRow(row);
    if (xlsRow == null) {
        if (!create)
            return null;
        xlsRow = sheet.createRow(row);/*from   w  ww  .jav a2 s  .co  m*/
    }
    HSSFCell xlsCell = xlsRow.getCell((short) col);
    if (xlsCell == null) {
        if (!create)
            return null;
        xlsCell = xlsRow.createCell((short) col);
    }
    return xlsCell;
}

From source file:citibob.reports.PoiXlsWriter.java

License:Open Source License

/** @param row0 example row to copy when inserting... 
 Don't do fixup between xrow0 and xrow1, non-inclusive. */
public void insertRowsFixup(HSSFSheet sheet, int rowIx, int n, int row0Ix, int xcol0, int xcol1, int xrow0,
        int xrow1) {
    sheet.shiftRows(rowIx, sheet.getLastRowNum(), n, true, true);
    HSSFRow row0 = sheet.getRow(row0Ix + n);
    if (row0 != null) {
        for (int r = rowIx; r < rowIx + n; ++r) {
            HSSFRow row1 = sheet.createRow(r);
            copyRow(row0, row1, xcol0, xcol1);
        }//from ww w  . j a v  a2 s  .c o m
    }
    fixupFormulas(sheet, rowIx, n, xrow0, xrow1);
}

From source file:citibob.reports.PoiXlsWriter.java

License:Open Source License

/** Don't do fixup between xrow0 and xrow1, non-inclusive. */
public void fixupFormulas(HSSFSheet sheet, int rowIx, int n, int xrow0, int xrow1) {
    //System.out.println("--------- fixupFormulas(" + rowIx + "," + n + ")");
    int prows = sheet.getPhysicalNumberOfRows();
    int pr = 0;//  w  w  w  .j  ava2 s  .  c  o  m
    for (int r = 0; pr < prows; r++) {
        HSSFRow row = sheet.getRow(r);
        if (row == null)
            continue;
        ++pr;

        int pcells = row.getPhysicalNumberOfCells();
        int pc = 0;
        for (int c = 0; pc < pcells; ++c) {
            HSSFCell cell = row.getCell((short) c);
            if (cell == null)
                continue;
            ++pc;

            // Fixup the formula
            if (cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA)
                continue;
            //System.out.println("Formula cell: " + cell.getCellFormula());
            //System.out.println("    ncells = " + row.getLastCellNum());
            FormulaParser fp = new FormulaParser(cell.getCellFormula(), wb.getWorkbook());
            fp.parse();
            Ptg[] ptg = fp.getRPNPtg();
            for (int i = 0; i < ptg.length; ++i) {
                Ptg pi = ptg[i];
                //               if (pi.getPtgClass() != Ptg.CLASS_REF) continue;
                if (pi instanceof AreaPtg) {
                    //System.out.println("Fixing area: " + pi);
                    AreaPtg pp = (AreaPtg) pi;
                    if (pp.getFirstRow() >= rowIx)
                        pp.setFirstRow((short) (pp.getFirstRow() + n));
                    if (pp.getLastRow() >= rowIx) {
                        pp.setLastRow((short) (pp.getLastRow() + n));
                    }
                } else if (pi instanceof ReferencePtg) {
                    ReferencePtg pp = (ReferencePtg) pi;
                    if (r >= xrow0 && r < xrow1) {
                        if (pp.getRow() <= r && pp.isRowRelative())
                            pp.setRow((short) (r + pp.getRow() - rowIx));
                    } else if (pp.getRow() >= rowIx) {
                        pp.setRow((short) (pp.getRow() + n));
                    }
                }
            }

            // Done fixing the formula; set it back
            String fstr = fp.toFormulaString(wb.getWorkbook(), ptg);
            //System.out.println("replacing formula string (" + r + "," + c + "): " + fstr);
            cell.setCellFormula(fstr);
        }
    }
}

From source file:citibob.reports.PoiXlsWriter.java

License:Open Source License

/** Creates a new instance of PoiTest */
public void replaceHolders(java.util.Map<String, Object> models)
//throws Exception
{
    for (int k = 0; k < wb.getNumberOfSheets(); k++) {
        HSSFSheet sheet = wb.getSheetAt(k);

        // Iterate through all rows and cols of the sheet
        int prows = sheet.getPhysicalNumberOfRows();
        int pr = 0;
        for (int r = 0; pr < prows; r++) {
            System.out.println(r + ", " + pr + ", " + prows);
            HSSFRow row = sheet.getRow(r);
            if (row == null)
                continue;
            ++pr;/*  ww  w. j a  v a  2s.  c  o m*/
            int pcells = row.getPhysicalNumberOfCells();
            int pc = 0;
            for (int c = 0; pc < pcells; ++c) {
                HSSFCell cell = row.getCell((short) c);
                if (cell == null)
                    continue;
                ++pc;

                // Look for cells like ${var}
                if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING)
                    continue;

                String value = cell.getRichStringCellValue().getString().trim();
                if (!value.startsWith("${"))
                    continue;
                String rsname = value.substring(2, value.length() - 1);

                int n = replaceOneHolder(sheet, r, c, models, rsname);
                if (n != NOROWCHANGE) {
                    r += n;
                    break; // We just deleted the whole line!
                }
            }
        }
    }
}

From source file:citibob.reports.PoiXlsWriter.java

License:Open Source License

int replaceOneHolder(HSSFSheet sheet, int row, int col, Map<String, Object> models, String rsname) {
    // Do the replacement
    Object mod = (models.size() == 1 ? models.values().iterator().next() : models.get(rsname));
    if (mod == null)
        return NOROWCHANGE;
    if (mod instanceof TableModel)
        return replaceOneHolder(sheet, row, col, (TableModel) mod);

    // It's just a simple item; put it in
    HSSFRow row0 = sheet.getRow(row);
    HSSFCell c0 = row0.getCell((short) col);
    HSSFComment comment = c0.getCellComment();
    HSSFCellStyle style = c0.getCellStyle();
    row0.removeCell(c0);/*from w ww  .  jav  a2  s.  c  o  m*/
    HSSFCell c1 = row0.createCell((short) col);
    if (comment != null)
        c1.setCellComment(comment);
    if (style != null)
        c1.setCellStyle(style);
    setValue(c1, mod);
    return NOROWCHANGE;
}

From source file:citibob.reports.PoiXlsWriter.java

License:Open Source License

/** @returns net number of rows inserted */
int replaceOneHolder(HSSFSheet sheet, int row, int col, TableModel mod) {
    int n = mod.getRowCount();

    // Set up proper number of rows
    insertRowsFixup(sheet, row, n, row, col, col + mod.getColumnCount(), row, row + mod.getRowCount());
    HSSFRow r0 = sheet.getRow(row + n); // Our model row

    // Fill in the data, iterating through the model...
    for (int r = 0; r < mod.getRowCount(); ++r) {
        //System.out.println("r=" + r);
        HSSFRow r2 = sheet.getRow(row + r);
        if (r2 == null)
            r2 = sheet.createRow(row + r);

        for (int c = 0; c < mod.getColumnCount(); ++c) {
            //System.out.println("  c=" + c);
            HSSFCell c2 = r2.getCell((short) (col + c));
            if (c2 == null)
                c2 = r2.createCell((short) (col + c));
            Object val = mod.getValueAt(r, c);
            setValue(c2, val);
        }//from  w w w.  j a v a  2 s  .  co  m
    }
    deleteRowsFixup(sheet, row + n, 1, 0, 0);
    return n - 1;
}

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 w w  . ja v  a2 s.c o  m*/

        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();
    }

}