1

I'm using Java and OpenXLS to write out an Excel spreadsheet. I want to set a formula for a cell but I haven't got a clue how to do it. Can anybody help me, please? :)

(Can't tag this with "openxls" because I'm a new user...)

James Van Huis
  • 5,481
  • 1
  • 26
  • 25
Brian Beckett
  • 4,742
  • 6
  • 33
  • 52

4 Answers4

2

I don't know about OpenXLS, but it's easy to do with Andy Khan's JExcel. I'd recommend trying it. I think it's far superior to POI; I'm betting that it's better than OpenXLS as well.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • Ultimately I did swap to JExcel. Turns out that OpenXLS doesn't support formulae at all - if you want to use them then you need to upgrade to the paid version :( – Brian Beckett Jul 25 '09 at 14:15
2

OpenXLS support very well formulas. Look at this example. I put a value in the columns A and B of a sheet named "testSheet". In the column C of the same sheet I put the result of SUM (A+B).Don't forget to initialise the column C else you will have a CellNotFoundException

WorkBookHandle workbook = new WorkBookHandle();
workbook.createWorkSheet("testSheet");
WorkSheetHandle sheet = workbook.getWorkSheet("testSheet");
for (int i=1 ;i<=10; i++)
 {
    sheet.add(10*i, "A"+i);
    sheet.add(15*i, "B"+i);
    CellHandle cx = sheet.add(0,"C"+i);
    cx.setFormula("=SUM(A"+i+":B"+i+")");
 }

I hope that that this example will help other people.

kleopatra
  • 51,061
  • 28
  • 99
  • 211
Rostand Richard
  • 31
  • 3
0

Ultimately it turned out that OpenXLS doesn't support formula cells. They are included in the paid for version, though...

Brian Beckett
  • 4,742
  • 6
  • 33
  • 52
0

You can set the formula String directly on the cell in the Worksheet:

CellHandle cell = ws.add( "=SUM(A1:A3)", "A5" );

This adds the SUM(A1:A3) formula in cell A5. Any Cell set with a String value that is prefixed with '=' is considered a Formula.

Updates and maintenance are now happening on github (search for openxls).

user2591854
  • 182
  • 3
  • 15