Example usage for org.apache.poi.ss.usermodel Row getCell

List of usage examples for org.apache.poi.ss.usermodel Row getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getCell

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:br.sp.telesul.service.ExportServiceImpl.java

public void writeExcelFormacao(String templateHead, String[] columns, HSSFWorkbook workbook) {
    try {// ww  w  .  j  a  va  2  s.  c  o m
        List<Funcionario> funcionarios = funcionarioService.search();

        HSSFSheet sheet = workbook.createSheet(templateHead);

        Row rowHeading = sheet.createRow(0);
        for (int i = 0; i < columns.length; i++) {
            rowHeading.createCell(i).setCellValue(columns[i]);
        }
        //Estilizar o Cabealho - Stylesheet the heading
        for (int i = 0; i < columns.length; i++) {
            CellStyle stylerowHeading = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            font.setFontName(HSSFFont.FONT_ARIAL);
            font.setFontHeightInPoints((short) 11);
            font.setColor(HSSFColor.WHITE.index);
            stylerowHeading.setFont(font);
            stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER);
            stylerowHeading.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);
            stylerowHeading.setFillPattern(CellStyle.SOLID_FOREGROUND);
            rowHeading.getCell(i).setCellStyle(stylerowHeading);
        }
        //Preencher linhas
        int r = 1;
        for (Funcionario f : funcionarios) {

            if (!f.getFormacoes().isEmpty()) {

                for (Formacao fmc : f.getFormacoes()) {
                    if (!fmc.getInstituicao().isEmpty() || !fmc.getCurso().isEmpty()
                            || !fmc.getNivel().isEmpty()) {
                        Row row = sheet.createRow(r);

                        try {
                            Cell Nome = row.createCell(0);
                            Nome.setCellValue(f.getNome());
                        } catch (NullPointerException e) {

                        }

                        try {
                            Cell curso = row.createCell(1);
                            curso.setCellValue(fmc.getCurso());
                        } catch (NullPointerException e) {

                        }
                        try {
                            Cell instituicao = row.createCell(2);
                            instituicao.setCellValue(fmc.getInstituicao());
                        } catch (NullPointerException e) {

                        }
                        try {
                            Cell nivel = row.createCell(3);
                            nivel.setCellValue(fmc.getNivel());
                        } catch (NullPointerException e) {

                        }
                        try {
                            Cell copia = row.createCell(4);
                            copia.setCellValue(fmc.getCopiaCertificado());
                        } catch (NullPointerException e) {

                        }

                        r++;
                    }
                }

            }

        }

        for (int i = 0; i < columns.length; i++) {
            sheet.autoSizeColumn(i);
        }

    } catch (Exception e) {
        System.out.println("Error " + e);
    }
}

From source file:br.sp.telesul.service.ExportServiceImpl.java

public void writeExcelCertificacoes(String templateHead, String[] columns, HSSFWorkbook workbook) {
    try {//from  w ww. ja  va 2s . c  o  m
        List<Funcionario> funcionarios = funcionarioService.search();

        HSSFSheet sheet = workbook.createSheet(templateHead);

        Row rowHeading = sheet.createRow(0);
        for (int i = 0; i < columns.length; i++) {
            rowHeading.createCell(i).setCellValue(columns[i]);
        }
        //Estilizar o Cabealho - Stylesheet the heading
        for (int i = 0; i < columns.length; i++) {
            CellStyle stylerowHeading = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            font.setFontName(HSSFFont.FONT_ARIAL);
            font.setFontHeightInPoints((short) 11);
            font.setColor(HSSFColor.WHITE.index);
            stylerowHeading.setFont(font);
            stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER);
            stylerowHeading.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);
            stylerowHeading.setFillPattern(CellStyle.SOLID_FOREGROUND);
            rowHeading.getCell(i).setCellStyle(stylerowHeading);
        }
        //Preencher linhas
        int r = 1;
        for (Funcionario f : funcionarios) {

            if (!f.getCertificacoes().isEmpty()) {

                for (Certificacao ct : f.getCertificacoes()) {
                    Row row = sheet.createRow(r);

                    CellStyle styleDate = workbook.createCellStyle();
                    HSSFDataFormat dfExame = workbook.createDataFormat();
                    styleDate.setDataFormat(dfExame.getFormat("dd/mm/yyyy"));
                    try {
                        Cell Nome = row.createCell(0);
                        Nome.setCellValue(f.getNome());

                    } catch (NullPointerException e) {

                    }
                    try {
                        Cell cod = row.createCell(1);
                        cod.setCellValue(ct.getCodigo());
                    } catch (NullPointerException e) {

                    }
                    try {
                        Cell nome = row.createCell(3);
                        nome.setCellValue(ct.getNome());
                    } catch (NullPointerException e) {

                    }
                    try {
                        Cell empresa = row.createCell(2);
                        empresa.setCellValue(ct.getEmpresa());
                    } catch (NullPointerException e) {

                    }
                    try {
                        Cell dtExame = row.createCell(4);
                        dtExame.setCellValue(ct.getDtExame());
                        dtExame.setCellStyle(styleDate);
                    } catch (NullPointerException e) {

                    }
                    try {
                        Cell dtValidade = row.createCell(5);
                        dtValidade.setCellValue(ct.getDtValidade());
                        dtValidade.setCellStyle(styleDate);
                    } catch (NullPointerException e) {

                    }
                    try {

                        Cell copia = row.createCell(6);
                        copia.setCellValue(ct.getCopia());
                    } catch (NullPointerException e) {

                    }

                    r++;
                }

            }

        }

        for (int i = 0; i < columns.length; i++) {
            sheet.autoSizeColumn(i);
        }
        //            String file = "C:/Users/ebranco.TELESULCORP/new.xls";
        //            FileOutputStream out = new FileOutputStream(file);
        //            workbook.write(out);
        //            out.close();
        //            workbook.close();
        //            System.out.println("Excell write succesfully");
    } catch (Exception e) {
        System.out.println("Error" + e);
    }
}

From source file:br.sp.telesul.service.ExportServiceImpl.java

public void writeExcelIdiomas(String templateHead, String[] columns, HSSFWorkbook workbook) {
    try {/*from   w ww . j av a 2 s.  c  o  m*/
        List<Funcionario> funcionarios = funcionarioService.search();

        HSSFSheet sheet = workbook.createSheet(templateHead);

        Row rowHeading = sheet.createRow(0);
        for (int i = 0; i < columns.length; i++) {
            rowHeading.createCell(i).setCellValue(columns[i]);
        }
        //Estilizar o Cabealho - Stylesheet the heading
        for (int i = 0; i < columns.length; i++) {
            CellStyle stylerowHeading = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            font.setFontName(HSSFFont.FONT_ARIAL);
            font.setFontHeightInPoints((short) 11);
            font.setColor(HSSFColor.WHITE.index);
            stylerowHeading.setFont(font);
            stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER);
            stylerowHeading.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);
            stylerowHeading.setFillPattern(CellStyle.SOLID_FOREGROUND);
            rowHeading.getCell(i).setCellStyle(stylerowHeading);
        }
        //Preencher linhas
        int r = 1;
        for (Funcionario f : funcionarios) {

            if (!f.getIdiomas().isEmpty()) {

                for (Idioma idm : f.getIdiomas()) {
                    try {
                        if (idm.getNivel() != null || idm.getNome() != null) {
                            Row row = sheet.createRow(r);
                            Cell Nome = row.createCell(0);
                            Nome.setCellValue(f.getNome());

                            Cell language = row.createCell(1);
                            language.setCellValue(idm.getNome().toString());

                            Cell nivel = row.createCell(2);
                            nivel.setCellValue(idm.getNivel().toString());
                            r++;
                        }
                    } catch (NullPointerException ne) {
                        System.out.println("Error " + ne);
                        break;
                    }

                }

            }

        }

        for (int i = 0; i < columns.length; i++) {
            sheet.autoSizeColumn(i);
        }

    } catch (Exception e) {
        System.out.println("Error" + e);
    }
}

From source file:br.sp.telesul.service.ExportServiceImpl.java

public void stylizeHeader(Row rowHeading, HSSFWorkbook workbook, String[] columns) {
    for (int i = 0; i < columns.length; i++) {
        CellStyle stylerowHeading = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);/*ww  w .ja va  2 s  .  co m*/
        font.setFontName(HSSFFont.FONT_ARIAL);
        font.setFontHeightInPoints((short) 11);
        font.setColor(HSSFColor.WHITE.index);
        stylerowHeading.setFont(font);
        stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER);
        stylerowHeading.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);
        stylerowHeading.setFillPattern(CellStyle.SOLID_FOREGROUND);
        rowHeading.getCell(i).setCellStyle(stylerowHeading);
    }
}

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

License:Open Source License

private void printFooters() {
    int columns = columnMapping.size();
    Row firstRow = sheet.getRow(1);
    Row lastRow = sheet.getRow(rowCount - 1);

    Row sumFooterRow = sheet.createRow(rowCount++);
    Cell sumFooterLabelCell = sumFooterRow.createCell(0);
    sumFooterLabelCell.setCellValue("SUM");

    Row averageFooterRow = sheet.createRow(rowCount++);
    Cell averageFooterLabelCell = averageFooterRow.createCell(0);
    averageFooterLabelCell.setCellValue("AVERAGE");

    for (int index = 0; index <= columns; index++) {
        Cell cell = firstRow.getCell(index);
        if (cell == null) {
            cell = firstRow.createCell(index);
        }//w ww  .  j av a 2 s .  c  om
        Cell sumFooterCell = sumFooterRow.createCell(index);
        Cell averageFooterCell = averageFooterRow.createCell(index);

        CellReference firstCell = new CellReference(firstRow.getCell(index));
        Cell lastRowCell = lastRow.getCell(index);
        if (lastRowCell == null) {
            lastRowCell = lastRow.createCell(index);
        }
        CellReference lastCell = new CellReference(lastRowCell);

        sumFooterCell
                .setCellFormula("SUM(" + firstCell.formatAsString() + ":" + lastCell.formatAsString() + ")");
        averageFooterCell.setCellFormula(
                "AVERAGE(" + firstCell.formatAsString() + ":" + lastCell.formatAsString() + ")");
    }
}

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  v a  2  s . com
    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();
}

From source file:bs.compra.proceso.ActualizarPreciosCostoBean.java

public void leerDatosExcel(Iterator<Row> rowIterator) {

    Row row;
    while (rowIterator.hasNext()) {

        row = rowIterator.next();//from w  w  w .  java2s  . com
        Cell celda;

        String codigo;
        BigDecimal precio;

        try {
            codigo = row.getCell(0).getStringCellValue();
            precio = new BigDecimal(row.getCell(2).getNumericCellValue());
        } catch (Exception e) {
            continue;
        }

        if (codigo == null || codigo.isEmpty()) {
            continue;
        }
        if (precio == null) {
            continue;
        }

        Producto p = productoRN.getProducto(codigo);

        if (p == null) {
            continue;
        }

        ItemListaPrecioCosto i = new ItemListaPrecioCosto();
        i.setCodlis(listaCostoActualizar.getCodigo());
        i.setListaCosto(listaCostoActualizar);
        i.setArtcod(p.getCodigo());
        i.setProducto(p);
        i.setFechaVigencia(new Date());
        i.setPrecio(precio);
        i.setPrecioOriginal(
                itemListaPrecioCostoRN.getPrecioByProducto(i.getCodlis(), i.getArtcod(), i.getFechaVigencia()));

        if (listaCostoActualizar.getPriorizaMonedaProducto().equals("S")) {
            i.setMonedaRegistracion(p.getMonedaDeReferencia().getCodigo());
        } else {
            i.setMonedaRegistracion(listaCostoActualizar.getMoneda().getCodigo());
        }

        precios.add(i);
    }
}

From source file:bs.ventas.proceso.ActualizarPreciosVentaBean.java

public void leerDatosExcel(Iterator<Row> rowIterator) {

    Row row;
    while (rowIterator.hasNext()) {

        row = rowIterator.next();/* w  w  w. j  ava 2 s  .  c  om*/
        Cell celda;

        String codigo;
        BigDecimal precio;

        try {
            codigo = row.getCell(0).getStringCellValue();
            precio = new BigDecimal(row.getCell(2).getNumericCellValue());
        } catch (Exception e) {
            continue;
        }

        if (codigo == null || codigo.isEmpty()) {
            continue;
        }
        if (precio == null) {
            continue;
        }

        Producto p = productoRN.getProducto(codigo);

        if (p == null) {
            continue;
        }

        ItemListaPrecioVenta i = new ItemListaPrecioVenta();
        i.setCodlis(listaVentaActualizar.getCodigo());
        i.setListaDePrecio(listaVentaActualizar);
        i.setArtcod(p.getCodigo());
        i.setProducto(p);
        i.setFechaVigencia(new Date());
        i.setPrecio(precio);
        i.setPrecioOriginal(
                itemListaPrecioVentaRN.getPrecioByProducto(i.getCodlis(), i.getArtcod(), i.getFechaVigencia()));

        if (listaVentaActualizar.getPriorizaMonedaProducto().equals("S")) {
            i.setMonedaRegistracion(p.getMonedaDeReferencia().getCodigo());
        } else {
            i.setMonedaRegistracion(listaVentaActualizar.getMoneda().getCodigo());
        }

        precios.add(i);
    }
}

From source file:business.SongExcelParser.java

private SongFile getSongFileFromRow(Row row) {
    try {/*from www .ja v  a  2 s  .  co m*/
        String albumName = row.getCell(0).getRichStringCellValue().getString();
        String songName = row.getCell(1).getRichStringCellValue().getString();
        String singer = row.getCell(2).getRichStringCellValue().getString();
        Double duration = row.getCell(3).getNumericCellValue();
        String musicalGenre = row.getCell(4).getRichStringCellValue().getString();
        String location = row.getCell(5).getRichStringCellValue().getString();
        return new SongFile(albumName, singer, musicalGenre, location, songName, duration.intValue());
    } catch (Exception e) {
        return null;
    }
}

From source file:business.SongExcelParser.java

private Song getSongFromRow(Row row) {
    try {/*from   www. j a  v a2  s  .c  om*/
        String songName = row.getCell(0).getRichStringCellValue().getString();
        String singer = row.getCell(1).getRichStringCellValue().getString();
        Double duration = row.getCell(2).getNumericCellValue();
        String musicalGenre = row.getCell(3).getRichStringCellValue().getString();
        return new Song(songName, musicalGenre, singer, duration.intValue());
    } catch (Exception e) {
        return null;
    }
}