List of usage examples for org.apache.poi.ss.usermodel Cell getCellFormula
String getCellFormula();
SUM(C4:E4) From source file:uk.ac.liverpool.spreadsheet.ToXML.java
License:Apache License
private void printSheetContent(Sheet sheet) { ensureColumnBounds(sheet);//from w ww . j a va2 s . co m printColumnHeads(); cellsToFormula = new HashMap<String, List<String>>(); cellToFormulaConverted = new HashMap<String, String>(); crToParent = new HashMap<String, List<String>>(); FormulaParsingWorkbook fpwb; FormulaRenderingWorkbook frwb; if (xswb != null) { XSSFEvaluationWorkbook w = XSSFEvaluationWorkbook.create(xswb); frwb = w; fpwb = w; } else if (hswb != null) { HSSFEvaluationWorkbook w = HSSFEvaluationWorkbook.create(hswb); frwb = w; fpwb = w; } else return; // first we need to determine all the dependencies ofr each formula Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); for (int i = firstColumn; i < endColumn; i++) { if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) try { parseFormula(cell, fpwb, frwb); } catch (Exception x) { } } } } } rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); int rowNumber = row.getRowNum() + 1; out.format(" <TableRow>%n"); out.format(" <RowHeader>%d</RowHeader>%n", rowNumber); out.format(" <TableCells>%n"); for (int i = firstColumn; i < endColumn; i++) { String content = "0"; String attrs = ""; CellStyle style = null; String valueType = "float"; Cell cell = row.getCell(i); CellReference c = new CellReference(rowNumber - 1, i); attrs += " cellID=\"." + c.formatAsString() + "\""; String cr = c.formatAsString(); // if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { if (cell != null && cell.getCellType() == Cell.CELL_TYPE_FORMULA) { attrs += " readOnly=\"readOnly\""; try { attrs += " cellFormula=\"" + StringEscapeUtils.escapeXml(cell.getCellFormula()) + "\""; } catch (Exception x) { attrs += " cellFormula=\"FORMULA ERROR\""; } } else { List<String> cfrl = cellsToFormula.get(cr); StringBuffer formula = new StringBuffer(""); if (cfrl != null) { List<String> refs = new LinkedList<String>(); visit(cfrl, refs); System.out.println(refs); cleanup(refs); for (String s : refs) { formula.append(StringEscapeUtils.escapeXml(cellToFormulaConverted.get(s))); formula.append(" || "); } } if (formula.length() > 0) attrs += " formula=\"" + formula.substring(0, formula.length() - 4) + "\""; } if (cell != null) { style = cell.getCellStyle(); // Set the value that is rendered for the cell // also applies the format try { CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; } catch (Exception x) { content = "DATA FORMULA ERROR "; } } // } attrs += " value_type=\"" + valueType + "\""; attrs += " value=\"" + StringEscapeUtils.escapeXml(content) + "\""; out.format(" <TableCell %s>%s</TableCell>%n", // class=%s // styleName(style), attrs, StringEscapeUtils.escapeXml(content)); } out.format(" </TableCells> </TableRow>%n%n"); } }
From source file:uk.ac.liverpool.spreadsheet.ToXML.java
License:Apache License
private void parseFormula(Cell cell, FormulaParsingWorkbook fpwb, FormulaRenderingWorkbook frwb) { CellReference c = new CellReference(cell); String cr = c.formatAsString(); Ptg[] pp = FormulaParser.parse(cell.getCellFormula(), fpwb, FormulaType.CELL, currentSheet); for (Ptg p : pp) { if (p instanceof RefPtg) { RefPtg a = (RefPtg) p;/*from w ww .j ava 2 s .co m*/ Cell dest = cell.getSheet().getRow(a.getRow()).getCell(a.getColumn()); if (dest != null && dest.getCellType() == Cell.CELL_TYPE_FORMULA) { String cr2 = new CellReference(dest).formatAsString(); List<String> ls = crToParent.get(cr2); if (ls == null) { ls = new LinkedList<String>(); crToParent.put(cr2, ls); } ls.add(cr); } List<String> ls = cellsToFormula.get(a.toFormulaString()); if (ls == null) { ls = new LinkedList<String>(); ls.add(cr); cellsToFormula.put(a.toFormulaString(), ls); } else ls.add(cr); } if (p instanceof AreaPtg) { AreaPtg a = (AreaPtg) p; for (int i = a.getFirstColumn(); i <= a.getLastColumn(); i++) { for (int k = a.getFirstRow(); k <= a.getLastRow(); k++) { String cc = new CellReference(k, i).formatAsString(); Cell dest = cell.getSheet().getRow(k).getCell(i); if (dest != null && dest.getCellType() == Cell.CELL_TYPE_FORMULA) { String cr2 = new CellReference(dest).formatAsString(); List<String> ls = crToParent.get(cr2); if (ls == null) { ls = new LinkedList<String>(); crToParent.put(cr2, ls); } ls.add(cr); } List<String> ls = cellsToFormula.get(cc); if (ls == null) { ls = new LinkedList<String>(); ls.add(cr); cellsToFormula.put(cc, ls); } else ls.add(cr); } } } } String cellF = "[." + cr + "]=" + FormulaRenderer.toFormulaString(frwb, pp); System.out.println(cellF); cellToFormulaConverted.put(cr, cellF); }
From source file:Util.exemploLendoXLSX.java
/** * @param args the command line arguments *//*from w w w . j a v a 2 s .c om*/ public static void main(String[] args) { FileInputStream fisPlanilha = null; try { File file = new File("D:\\planilhas\\planilhaDaAula.xlsx"); fisPlanilha = new FileInputStream(file); //cria um workbook = planilha toda com todas as abas XSSFWorkbook workbook = new XSSFWorkbook(fisPlanilha); //recuperamos apenas a primeira aba ou primeira planilha XSSFSheet sheet = workbook.getSheetAt(0); //retorna todas as linhas da planilha 0 (aba 1) Iterator<Row> rowIterator = sheet.iterator(); //varre todas as linhas da planilha 0 while (rowIterator.hasNext()) { //recebe cada linha da planilha Row row = rowIterator.next(); //pegamos todas as celulas desta linha Iterator<Cell> cellIterator = row.iterator(); //varremos todas as celulas da linha atual while (cellIterator.hasNext()) { //criamos uma celula Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.println("TIPO STRING: " + cell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.println("TIPO NUMERICO: " + cell.getNumericCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println("TIPO FORMULA: " + cell.getCellFormula()); } } } } catch (FileNotFoundException ex) { Logger.getLogger(exemploLendoXLSX.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(exemploLendoXLSX.class.getName()).log(Level.SEVERE, null, ex); } finally { try { fisPlanilha.close(); } catch (IOException ex) { Logger.getLogger(exemploLendoXLSX.class.getName()).log(Level.SEVERE, null, ex); } } }