detecting and replacing variables in a string by values

Question

I have an excel sheet whose first column contains following data "What is ${v1} % of ${v2}?", two more columns (v1 and v2) in this sheet contains {"type":"int", "minimum":15, "maximum":58} and {"type":"int", "minimum":30, "maximum":100}, these are the ranges of variable v1 and v2. I need to replace v1 and v2 in the expression with a random value from the given range and store the expression in another spread sheet using JAVA. How can I do this by making use of JETT?

For example: I should store "What is 25% of 50?"

This is what I have done,I am able to read the column in my java program but not replace the values

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ACGS {

public static void main(String[] args) throws Exception {  
//test file is located in your project path         
FileInputStream fileIn = new FileInputStream("C://users/user/Desktop/Content.xls");
//read file 
POIFSFileSystem fs = new POIFSFileSystem(fileIn);  
HSSFWorkbook filename = new HSSFWorkbook(fs);
//open sheet 0 which is first sheet of your worksheet
HSSFSheet sheet = filename.getSheetAt(0);

//we will search for column index containing string "Your Column Name" in the row 0 (which is first row of a worksheet
String columnWanted = "${v1}";
Integer columnNo = null;
//output all not null values to the list
List<Cell> cells = new ArrayList<Cell>();
Row firstRow = sheet.getRow(0);

for(Cell cell:firstRow){
if (cell.getStringCellValue().contains(columnWanted)){
    columnNo = cell.getColumnIndex();
    System.out.println("cell contains "+cell.getStringCellValue());
    }
}

if (columnNo != null){
 for (Row row : sheet) {
Cell c = row.getCell(columnNo);
if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK) {
  // Nothing in the cell in this row, skip it
} else {
  cells.add(c);
      }
}
}   else{
System.out.println("could not find column " + columnWanted + " in first row of " + fileIn.toString());
     }
   }
}

Answer 1

First, it looks like you aren't using JETT at all. You appear to be attempting to read the spreadsheet yourself and do some processing.

Here is how you would do this in JETT. JETT doesn't provide its own random number support, but together with its Apache Commons JEXL expression support, and Java's own Random, you can publish the expected ranges of your random variables as beans to JETT, and you can calculate a random variable with an expression.

First, create your template spreadsheet, populating it with expressions (between ${ and }) that JETT will evaluate. One cell might contain something like this.

What is ${rnd.nextInt(v1Max - v1Min + 1) + v1Min}% of ${rnd.nextInt(v2Max - v2Min + 1) + v2Min}?

Next, create beans to be supplied to JETT. These beans are the named objects that are available to JEXL expressions in your spreadsheet template.

Map<String, Object> beans = new HashMap<String, Object>();
beans.put("v1Min", 15);
beans.put("v1Max", 58);
beans.put("v2Min", 30);
beans.put("v2Max", 100);
beans.put("rnd", new Random());

Next, create your code that invokes the JETT ExcelTransformer.

try
{
   ExcelTransformer transformer = new ExcelTransformer();
   // template file name, destination file name, beans
   transformer.transform("Content.xls", "Populated.xls", beans);
}
catch (IOException e)
{
   System.err.println("IOException caught: " + e.getMessage());
}
catch (InvalidFormatException e)
{
   System.err.println("InvalidFormatException caught: " + e.getMessage());
}

In the resultant spreadsheet, you will see the expressions evaluated. In the cell that contained the expressions above, you will see for example:

What is 41% of 38?

(Or you will see different numbers, depending on the random numbers generated.)