Example usage for org.apache.poi.ss.usermodel Cell getStringCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getStringCellValue

Introduction

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

Prototype

String getStringCellValue();

Source Link

Document

Get the value of the cell as a string

For numeric cells we throw an exception.

Usage

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

private Object getCellValue(Cell cell) {
    Object o = "";
    try {//  w w w.  j a v  a2  s  .  c  o m
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            o = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            o = cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            o = cell.getNumericCellValue();
            break;
        case Cell.CELL_TYPE_BLANK:
            o = "";
            break;
        }
    } catch (Exception e) {
        e.printStackTrace();
        System.err.println(e.getMessage());
    }
    return o;
}

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;//  www.  j  av  a  2s .c o  m
    if (!outputFile.exists()) {
        outputFile.createNewFile();
        outputWorkbook = new HSSFWorkbook();
    } else {
        FileInputStream inputFileStream = new FileInputStream(outputFile);
        outputWorkbook = WorkbookFactory.create(inputFileStream);
    }

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

From source file:br.ufmt.periscope.importer.impl.ESPACENETPatentImporter.java

private void parseLineXLS() {

    patent.setLanguage(lang);//from   www  . j  a v  a  2 s . c o  m

    row = rowIterator.next(); //Percorrendo cada linha (patente)
    // Para cada linha (patente), pega cada atributo (Titulo, Publicao, Autor ...)
    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {

        Cell cell = cellIterator.next(); // Pegando cada coluna (atributo)

        switch (cell.getCellType()) {

        case Cell.CELL_TYPE_STRING:
            fillPatentXLS(cell.getColumnIndex(), cell.getStringCellValue());
            break;
        default:
            break;
        }

    }
}

From source file:br.unesp.rc.desafio.utils.Spreadsheet.java

public static ArrayList<String> ReadXlsSpreadsheet(File spreadsheet) {

    /*//from   w ww. ja  v a  2  s.c om
    Constructing File
    */
    ArrayList values = new ArrayList<String>();
    FileInputStream inputStr = null;
    try {
        inputStr = new FileInputStream(spreadsheet);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex);
    }

    Workbook currentSpreadsheetFile = null;

    try {
        HSSFRow row;
        currentSpreadsheetFile = new HSSFWorkbook(inputStr);
    } catch (IOException ex) {
        Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex);
    }

    Sheet sheet = currentSpreadsheetFile.getSheetAt(0);
    Iterator<Row> rowItr = sheet.rowIterator();

    while (rowItr.hasNext()) {
        row = (HSSFRow) rowItr.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            String cellValue = "";
            switch (cell.getCellTypeEnum()) {
            default:
                // cellValue = cell.getCellFormula();
                cellValue = Double.toString(cell.getNumericCellValue());
                cell.setCellType(CellType.STRING);
                cell.setCellValue(cellValue);
                break;
            case NUMERIC:
                cellValue = Double.toString(cell.getNumericCellValue());
                cell.setCellType(CellType.STRING);
                cell.setCellValue(cellValue);
            case BLANK:
                break;
            case STRING:
                break;
            }
            if (!cell.getStringCellValue().isEmpty()) {
                values.add(cell.getStringCellValue());
                values.add(",");
                // System.out.println("HOLD IT");
            } else {
                values.add("0");
                values.add(",");
                // System.out.println("OBJECTION!!");
            }
            //System.out.print(cell.getStringCellValue() + " \t\t " );
        }
        //System.out.println();
        values.add(";");

    }

    try {
        inputStr.close();
    } catch (IOException ex) {
        Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex);
    }
    //System.out.println(values.get(0));
    return values;
}

From source file:br.unesp.rc.desafio.utils.Spreadsheet.java

public static ArrayList<String> ReadXlsxSpreadsheet(File spreadsheet) {

    /*/*from   w w w .  j a v  a 2 s  .co  m*/
    Constructing File
    */
    ArrayList values = new ArrayList<String>();
    FileInputStream inputStr = null;
    try {
        inputStr = new FileInputStream(spreadsheet);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex);
    }

    Workbook currentSpreadsheetFile = null;

    try {
        XSSFRow row1;
        currentSpreadsheetFile = new XSSFWorkbook(inputStr);
    } catch (IOException ex) {
        Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex);
    }

    Sheet sheet = currentSpreadsheetFile.getSheetAt(0);
    Iterator<Row> rowItr = sheet.rowIterator();

    while (rowItr.hasNext()) {
        row1 = (XSSFRow) rowItr.next();
        Iterator<Cell> cellIterator = row1.cellIterator();
        while (cellIterator.hasNext()) {
            String cellValue = "";
            Cell cell = cellIterator.next();

            switch (cell.getCellTypeEnum()) {
            default:
                cellValue = cell.getCellFormula();
                cell.setCellType(CellType.STRING);
                cell.setCellValue(cellValue);
                break;
            case BLANK:
                break;
            case STRING:
                break;
            }
            values.add(cell.getStringCellValue());
            System.out.print(cell.getStringCellValue() + " \t\t ");
        }
        System.out.println();
    }
    try {
        inputStr.close();
    } catch (IOException ex) {
        Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex);
    }

    return values;
}

From source file:businessCharts.readExcell.java

private boolean isContain(Cell cell, String keyword) throws Exception {
    try {/*from w w  w.ja  va2 s. co  m*/
        try {
            if (cell.getStringCellValue().contains(keyword) || cell.getStringCellValue().startsWith(keyword)
                    || cell.getStringCellValue().endsWith(keyword))
                return true;
            return false;
        } catch (NullPointerException ex) {
            return false;
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return false;
}

From source file:businessCharts.readExcell.java

private int getColNo(String colName, XSSFRow row) {
    Iterator<Cell> topCell = row.cellIterator();
    int cellNo = 0;
    colName = colName.toLowerCase();/*from w  w w .  ja v  a 2s. c  o  m*/
    while (topCell.hasNext()) {
        Cell cell = topCell.next();
        if (cell.getStringCellValue().toLowerCase().contentEquals(colName)) {
            return cellNo;
        }
        cellNo++;
    }
    return -1;
}

From source file:businessCharts.readExcell.java

public TreeMap<String, TreeMap<String, Pair<Integer, Integer>>> groupBycount(String par1, String par2)
        throws Exception {
    //so that mapping could be done in O(n*log n*log n)
    initExcel("excel.xlsx");
    XSSFSheet spreadsheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = spreadsheet.iterator();
    XSSFRow tempRow = (XSSFRow) rowIterator.next();
    int cell_branch = getColNo(par1, tempRow);
    int cell_assignee = getColNo(par2, tempRow);
    int cell_keyword = getColNo("Keywords", tempRow);
    System.out.println("columns no" + cell_assignee + " " + cell_branch + " " + cell_keyword);
    TreeMap<String, TreeMap<String, Pair<Integer, Integer>>> list = new TreeMap<>();
    while (rowIterator.hasNext()) {
        XSSFRow row = (XSSFRow) rowIterator.next();
        Cell Branchcell = row.getCell(cell_branch);
        Cell Assigneecell = row.getCell(cell_assignee);
        if (list.containsKey(Branchcell.getStringCellValue())) {
            if (list.get(Branchcell.getStringCellValue()).containsKey(Assigneecell.getStringCellValue())) {
                try {
                    Cell keycell = row.getCell(cell_keyword);
                    if (isContain(keycell, "clone")) {
                        int key = list.get(Branchcell.getStringCellValue())
                                .get(Assigneecell.getStringCellValue()).getKey();
                        int value = list.get(Branchcell.getStringCellValue())
                                .get(Assigneecell.getStringCellValue()).getValue();
                        //System.out.println("key->" + key);
                        key++;/*from w w  w  .  java 2s .  co  m*/
                        list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(),
                                new Pair<>(key, value));
                    } else {
                        int key = list.get(Branchcell.getStringCellValue())
                                .get(Assigneecell.getStringCellValue()).getKey();
                        int value = list.get(Branchcell.getStringCellValue())
                                .get(Assigneecell.getStringCellValue()).getValue();
                        //System.out.println("value->" + value);
                        value++;
                        list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(),
                                new Pair<>(key, value));
                    }
                } catch (NoSuchElementException ex) {
                    int key = list.get(Branchcell.getStringCellValue()).get(Assigneecell.getStringCellValue())
                            .getKey();
                    int value = list.get(Branchcell.getStringCellValue()).get(Assigneecell.getStringCellValue())
                            .getValue();
                    value++;
                    list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(),
                            new Pair<>(key, value));
                }
            } else {
                try {
                    Cell keycell = row.getCell(cell_keyword);
                    if (isContain(keycell, "clone"))
                        list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(),
                                new Pair<>(1, 0));
                    else
                        list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(),
                                new Pair<>(0, 1));
                } catch (NoSuchElementException ex) {
                    list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(),
                            new Pair<>(0, 1));
                }
            }
        } else {
            if (isContain(row.getCell(cell_keyword), "clone")) {
                TreeMap<String, Pair<Integer, Integer>> tp = new TreeMap<>();
                tp.put(Assigneecell.getStringCellValue(), new Pair<>(1, 0));
                list.put(Branchcell.getStringCellValue(), tp);
            } else {
                TreeMap<String, Pair<Integer, Integer>> tp = new TreeMap<>();
                tp.put(Assigneecell.getStringCellValue(), new Pair<>(0, 1));
                list.put(Branchcell.getStringCellValue(), tp);
            }
        }
    }
    return list;
}

From source file:ch.admin.isb.hermes5.business.userszenario.projektstrukturplan.ProjektstrukturplanGeneratorExcelTest.java

License:Apache License

private String buildProjektstrukturPlan(Szenario szenario) throws IOException {
    ZipOutputBuilder zipBuilder = new ZipOutputBuilder();

    AnwenderloesungRenderingContainer container = new AnwenderloesungRenderingContainer("model", szenario,
            new SzenarioUserData(), Arrays.asList("de"), true, true, true, true);

    projektstrukturplanGeneratorExcel.addProjektstrukturPlan("workbreakdownstructure", container, zipBuilder,
            new LocalizationEngine(projektstrukturplanGeneratorExcel.translationRepository, "model", "de"));

    byte[] result = zipBuilder.getResult();
    ZipInputStream zipInputStream = new ZipInputStream(new ByteArrayInputStream(result));
    ZipEntry nextEntry;//from   ww  w  .ja v  a 2 s . c o m

    while ((nextEntry = zipInputStream.getNextEntry()) != null) {
        assertEquals(nextEntry.getName(), "workbreakdownstructure/de/Workbreakdownstructure_de.xlsx");
        byte[] byteArray = zipUtil.readZipEntry(zipInputStream);
        java.io.FileOutputStream fileOutputStream = new java.io.FileOutputStream(
                "target/projektstrukturplan.xlsx");
        fileOutputStream.write(byteArray);
        fileOutputStream.close();
        assertNotNull(byteArray);
        XSSFWorkbook wb = new XSSFWorkbook(new ByteArrayInputStream(byteArray));
        XSSFSheet sheet = wb.getSheetAt(0);
        ByteArrayOutputStream out2 = new ByteArrayOutputStream();
        PrintStream out = new PrintStream(out2);

        for (Row row : sheet) {
            out.print("|");
            // assertTrue("" + row.getLastCellNum(), row.getLastCellNum() < 3);
            for (Cell cell : row) {
                out.print(cell.getStringCellValue());
                out.print("|");
            }
            out.println("");
        }
        return new String(out2.toByteArray());
    }
    throw new AssertionError("workbook not found");
}

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();//  w w w  . j  a  v a  2 s  .  co  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);
}