How to read formula string from excel?

[mehul149]

Hello

I am using the following code and its returning empty string.

Code: Select all


    spread = SpreadsheetMLPackage.load(new File(mainFile));
     WorksheetPart sheetpart = (WorksheetPart) spread.getParts().get(new PartName("/xl/worksheets/sheet1.xml"));
    List<Row> rows = sheetpart.getContents().getSheetData().getRow();
    r = rows.get(i);
     for (Cell cr : r.getC())
     {
        if (cr.getF() != null)
        {
         System.out.println(cr.getF().getValue());
        }
       }


Its is returning empty string.

When I write the whole pakage using

Code: Select all

spread.save(new File(output));


All the formulas are retained.

Is this not the correct place to look for formula strings? Please help.

[keeferlaic]

Too late to answer?

OBS:
pkg = SpreadsheetMLPackage
I have a method to get the formulas from a workSheet.

Code: Select all

   public List<CTCellFormula> getFormulas(int workSheetIndex) throws Xlsx4jException, Docx4JException{
      WorksheetPart sheet = pkg.getWorkbookPart().getWorksheet(workSheetIndex);
      List<Row> rows = sheet.getContents().getSheetData().getRow();
      List<CTCellFormula> listFormulas = new ArrayList<CTCellFormula>();
      for (int i = 0; i < rows.size(); i++) {
         List<Cell> cells = rows.get(i).getC();
         for (int j = 0; j < cells.size(); j++) {
            Cell cell = cells.get(j);
            if (cell.getF() != null) {
               listFormulas.add(cell.getF());
            }
         }
      }
      return listFormulas;
   }


To get the formulas as string you can just getValue() from the CTCellFormula, but it returns somenthing like SUM(A5,B5). However, you can change it with replace(regex) String method.

Good luck, and may the source be with you

[jason]

and see also http://webapp.docx4java.org/OnlineDemo/ ... tML/f.html