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/RatingDetailP
ane.java120 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()).l
og(Level.SEVERE, null, ex);
243 }
244 } catch (Exception ex) {
245 Logger.getLogger(RatingDetailPane.class.getName()).log(Leve
l.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
> asimple possibility to make the users to add formulas for calculation
> ofscores and relevance (to show ore hide) of questions.
> I thought: Why should I implement my own calculation language when I
> canuse a language every user may already know?
> So I came to the idea to use POI. I thought of parsing throw a
> formulaand replacing variables within that formula with values - and
> thenletting POI evaluate it.
> Is this feasible? Is it possible to only evaluate a formula
> withoutcreating a temporary spreadsheet?
> Where in the javadoc of POI should I begin? Or does someone already
> hasan example?
> Thank you,Philipp
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
