Page 1 of 1

How to read formula string from excel?

PostPosted: Fri Jul 17, 2015 2:14 am
by 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.

Re: How to read formula string from excel?

PostPosted: Sat Aug 01, 2015 4:55 am
by 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 :)

Re: How to read formula string from excel?

PostPosted: Sat Aug 01, 2015 4:22 pm
by jason