Using POI only as a formula evaluator

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Using POI only as a formula evaluator

Philipp
Hi,

I am currently working with forms for surveys and I want to implement a
simple possibility to make the users to add formulas for calculation of
scores and relevance (to show ore hide) of questions.

I thought: Why should I implement my own calculation language when I can
use a language every user may already know?

So I came to the idea to use POI. I thought of parsing throw a formula
and replacing variables within that formula with values - and then
letting POI evaluate it.

Is this feasible? Is it possible to only evaluate a formula without
creating a temporary spreadsheet?

Where in the javadoc of POI should I begin? Or does someone already has
an example?

Thank you,
Philipp


smime.p7s (7K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Using POI only as a formula evaluator

Andreas Reichel
Hi Philipp,

we have done something similar, but we explicitely use a Excel Template:

1) create the Excel template with 2 areas: the parameter area and the formula/result area

The advantage is, that you can test and visualize your calculations.

2) open the Excel template in Apache POI, fill the parameter area with the parameters, evaluate and fetch the results

We have managed to implement quite complex calculations this way. Performance is good. Memory requirements are quite high though.
Another advantage is: you can implement easily many different calculations and scenarios based on the same parameters. Just define/use various similar templates.

You can study the attached sample for reference. It contains all needed steps.

Best regards
Andreas

/home/are/Documents/src/RISKBox/src/com/manticore/risk/ui/RatingDetailPane.java
120 private final Action calculateRatingAction
121           = new AbstractAction("Calculate Rating", ICON_MIMETYPES_64.APPLICATION_VND_MS_EXCEL) {
122     @Override
123     public void actionPerformed(ActionEvent e) {
124       try {
125         RatingModel ratingModel = getObject(RatingModel.class, ratingModelBox);
126         File f = File.createTempFile(ratingModel.modelDefinitionFileName, ".xlsx");
127         FileOutputStream fileOutputStream = new FileOutputStream(f);
128 
129         try {
130           ByteArrayInputStream inputStream
131                   = new ByteArrayInputStream(ratingModel.modelDefinition);
132           Workbook wb = WorkbookFactory.create(inputStream);
133           Sheet sheet = wb.getSheet("ratio");
134 
135           // fill the ratios
136           Map<String, Object> map
137                   = ETLConnectionHelper.map("id_counterparty_ref", object.idCounterPartyRef);
138           TreeMap<Object, Object[]> ratioMap = new TreeMap<>();
139           Object[] valueDates = RiskDataManager
140                   .getColumnData("GET RATIO COLLECTION VALUE DATES PER COUNTERPARTY", map);
141           Object[] labels = RiskDataManager.getColumnData("GET RATIOS PER COUNTERPARTY", map);
142 
143           for (Object l : labels) {
144             if (!ratioMap.containsKey(l)) {
145               ratioMap.put(l, new String[valueDates.length]);
146             }
147           }
148 
149           Object[][] data = (Object[][]) RiskDataManager
150                   .getResultSetData("GET RATIOS VALUES PER COUNTERPARTY", map)[1];
151           for (Object[] r : data) {
152             Date valueDate = (Date) r[0];
153             Object l = r[1];
154             Object v = r[2];
155 
156             int pos = Arrays.binarySearch(valueDates, valueDate);
157             ratioMap.get(l)[pos] = v;
158           }
159 
160           int rowIndex = 3;
161           if (sheet.getRow(rowIndex) == null) {
162             sheet.createRow(rowIndex);
163           }
164           for (int c = 0; c < valueDates.length; c++) {
165             sheet.getRow(rowIndex).createCell(2 + c).setCellValue((Date) valueDates[c]);
166           }
167 
168           rowIndex = 5;
169           for (Entry<Object, Object[]> entry : ratioMap.entrySet()) {
170             Row row = sheet.getRow(rowIndex);
171             if (row == null) {
172               row = sheet.createRow(rowIndex);
173             }
174 
175             row.createCell(1).setCellValue(entry.getKey().toString());
176             for (int c = 0; c < valueDates.length; c++) {
177               row.createCell(2 + c).setCellValue((String) entry.getValue()[c]);
178             }
179 
180             rowIndex++;
181           }
182 
183           if (wb instanceof HSSFWorkbook) {
184             HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
185             evaluator.evaluateAll();
186           } else if (wb instanceof XSSFWorkbook) {
187             XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
188             evaluator.evaluateAll();
189           }
190 
191           Double ratingResultValue = null;
192           Double statusCellValue = null;
193 
194           try {
195 
196             Cell ratingResultCell = sheet.getRow(0).getCell(1);
197             Cell statusCodeCell = sheet.getRow(1).getCell(1);
198 
199             ratingResultValue = ratingResultCell.getNumericCellValue();
200             statusCellValue = statusCodeCell.getNumericCellValue();
201 
202             if (statusCellValue > 0f) {
203               throw new Exception(
204                       "Rating Model shows Errors. (Status Code = " + statusCellValue + ")");
205             } else {
206               object.ratingClass = ratingModel.getRatingClass(ratingResultValue.shortValue());
207               object.ratingPoints = ratingResultValue.shortValue();
208 
209               set(ratingPointsField, object.ratingPoints);
210               set(ratingClassBox, object.ratingClass);
211             }
212 
213           } catch (Exception ex1) {
214             ErrorDialog.show(RatingDetailPane.this, ex1);
215           }
216 
217           System.out.println("Rating result " + ratingResultValue);
218           System.out.println("Status " + statusCellValue);
219 
220           wb.write(fileOutputStream);
221           if (Desktop.isDesktopSupported()) {
222             if (Desktop.getDesktop().isSupported(Desktop.Action.EDIT)) {
223               try {
224                 Desktop.getDesktop().edit(f);
225               } catch (Exception ex) {
226                 ErrorDialog.show(RatingDetailPane.this, ex);
227               }
228             } else if (Desktop.getDesktop().isSupported(Desktop.Action.OPEN)) {
229               try {
230                 Desktop.getDesktop().open(f);
231               } catch (Exception ex) {
232                 ErrorDialog.show(RatingDetailPane.this, ex);
233               }
234             }
235           }
236 
237           /*
238                * Process p = Runtime.getRuntime() .exec("rundll32 url.dll,FileProtocolHandler " +
239                * this.outputFilePath);
240            */
241         } catch (IOException ex) {
242           Logger.getLogger(RatingModelDetailPane.class.getName()).log(Level.SEVERE, null, ex);
243         }
244       } catch (Exception ex) {
245         Logger.getLogger(RatingDetailPane.class.getName()).log(Level.SEVERE, null, ex);
246       }
247     }
248   };


On Thu, 2020-02-27 at 11:12 +0100, Philipp wrote:
Hi,

I am currently working with forms for surveys and I want to implement a
simple possibility to make the users to add formulas for calculation of
scores and relevance (to show ore hide) of questions.

I thought: Why should I implement my own calculation language when I can
use a language every user may already know?

So I came to the idea to use POI. I thought of parsing throw a formula
and replacing variables within that formula with values - and then
letting POI evaluate it.

Is this feasible? Is it possible to only evaluate a formula without
creating a temporary spreadsheet?

Where in the javadoc of POI should I begin? Or does someone already has
an example?

Thank you,
Philipp




---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Using POI only as a formula evaluator

Philipp
Hi Andreas,

> 1) create the Excel template with 2 areas: the parameter area and the
> formula/result area
>
> The advantage is, that you can test and visualize your calculations.
>
> 2) open the Excel template in Apache POI, fill the parameter area with
> the parameters, evaluate and fetch the results

thank you for the fast reply and your example. The calculation would be
done on a headless Server as a part of a webservice...

Is there really no possibility to only use the formular evaluator?
Replacing the variables to constant values would done in our preparations...

Philipp



smime.p7s (7K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Using POI only as a formula evaluator

Andreas Reichel
Hi Philipp.

On Fri, 2020-02-28 at 10:24 +0100, Philipp wrote:
> thank you for the fast reply and your example. The calculation would
> be
>
> done on a headless Server as a part of a webservice...
Install XVFB for that purpose.

>
> Is there really no possibility to only use the formular evaluator?
>
> Replacing the variables to constant values would done in our
> preparations...

I am quite sure, it would be possible but there are much better formula
parsers/evaluators available for that case:

https://github.com/dkellenb/formula-evaluator
https://lallafa.objecthunter.net/exp4j/
http://mathparser.org/

Good luck!
Reply | Threaded
Open this post in threaded view
|

Re: Using POI only as a formula evaluator

nospam
Hi Philipp,

if you are looking for a small formula expression evaluator I can recommend EvalEx. It is really small and
lightweight

https://github.com/uklimaschewski/EvalEx

Best Regards

Kai

Andreas Reichel schrieb am 28.02.20 um 10:40:

> Hi Philipp.
>
> On Fri, 2020-02-28 at 10:24 +0100, Philipp wrote:
> > thank you for the fast reply and your example. The calculation would
> > be
> >
> > done on a headless Server as a part of a webservice...
> Install XVFB for that purpose.
>
> >
> > Is there really no possibility to only use the formular evaluator?
> >
> > Replacing the variables to constant values would done in our
> > preparations...
>
> I am quite sure, it would be possible but there are much better formula
> parsers/evaluators available for that case:
>
> https://github.com/dkellenb/formula-evaluator
> https://lallafa.objecthunter.net/exp4j/
> http://mathparser.org/
>
> Good luck!
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]