CODEWITHSUNDEEP
javaexcelexcel-formula
robert
robert

Reputation: 516

How to use Excel spreadsheet as a calculation engine for Java application?

The credibility rating engine is captured and maintained in Excel spreadsheet. It accepts following parameters: age, income, liabilities, occupation, education etc. and it returns some score (some number).

The goal is to "execute" this Excel file from back-end (Java).

Is there a tool/framework that can cover one these needs:

  1. convert Excel to Java code (code that represents Excel formulas) so that I can feed parameters and run generated code
  2. load Excel into memory and evaluate formulas (no generated code)

There are plenty of frameworks to manage Excel files: write and read. But there is nothing that would allow to use Excel spreadsheet as a calculation engine in back-end,

Upvotes: 3

Views: 2530

Answers (3)

0_boffin_
0_boffin_

Reputation: 1

It actually shouldn't be too difficult, especially since the layout and data are pretty simple. This will take further research on your part, yet this should serve as a starting point.

Check this out: - Create an Excel file

  • Add your columns from [A1:E1]: income, liabilities, occupations, education.
  • From [A2:E3], enter random numbers.
  • On [A4], enter "=Sum(A1:A3)" and fill right to all columns.
  • Save file as "xlsx"
  • Change ".xlsx" to ".rar" and open the compressed file.
  • You should see "sharedStrings.xml", "calcChain.xml", "worksheets/sheet1.xml".
  • You'll find the column headers in "sharedStrings.xml"
    • no attributes nor references are present in this XML.

  • In "worksheets/sheet1.xml", you'll see the following path => "worksheet/sheetData/row[0]/c" with the attributes "r" & "t"

    • "r" is where it's at
    • "t" is the type it is. "s" means string, which will be in the "sharedStrings.xml" file.
      • the next level deeper "worksheet/sheetData/row[0]/c[0]/v", you'll see the number zero. This is the position in the XML in the "sharedString.xml" at the following path "sst/si[]/t" where "" is the position.

    .

    • For rows [2:3], you'll see the numbers you've entered with their attributes.
    • In row [4], you'll see the "../row[4]/c/f" "SUM(A2:A3)" and for "../row[4]/c/v", you'll see the sum of whatever the numbers you've entered.

Explore all the XML files in there and play around.

You can possibly parse, modify the XML, and save it then change the filetype back to the ".XLSX" or whatever.

Enjoy.

Upvotes: 0

Evan
Evan

Reputation: 2556

Ignoring whether the overall concept is a good idea or not, one option would be to use Excel RTD to have the spreadsheets push string formatted data to your back end.

https://support.microsoft.com/en-us/help/289150/how-to-set-up-and-use-the-rtd-function-in-excel

Upvotes: 1

Tony Roberts
Tony Roberts

Reputation: 417

Have you considered moving all of your calculations to Java, and then exposing them back to Excel -- ie have a function in Excel that calls the same Java code to do the calculation?

You can write Excel functions in Java using Jinx, https://exceljava.com.

Upvotes: 1

Related Questions