Bicycle to generate Excel documents on the template

Usually, for software generation of complex reports in xls, it is proposed to use a “manually” generated document - a template, in the right places of which, instead of real data, some “tags” of the village are substituted. $ {userName}, $ {userAge}, and in the process of filling in the template, find tags in the text of the document and replace them with corresponding values.

But what if the final document should be missing some pages from the template, and vice versa, can other pages be “cloned” several times and filled with different data? And how to get rid of the tedious filling of the mapping tag-> value in the code?

We describe the sheet model (Sheet) of the document.

public class SheetModel { private String sheetToClone; //,      private String sheetName; //    private Map<String, Object> mappings; //getters and setters } 

')
Next, using Apache POI, run through the book, find a sheet called sheetToClone and create a copy of it. Thus, you can create as many SheetModel objects as you like, run through them in a loop and end up with a document containing the sheets of the original document and their copies. Next, the "original" sheets are deleted.

 private void createNewSheets(List<SheetModel> sheetModelList){ for (SheetModel sheetModel: sheetModelList){ String sheetName=sheetModel.getSheetName(); String sheetToClone=sheetModel.getSheetToClone(); cloneSheet(sheetName, sheetToClone); } } private void cloneSheet(String sheetName,String sheetToClone ){ int sheetToCloneIdx=getSheetIndex(sheetToClone); cloneSheet(sheetToCloneIdx, sheetName); } private int getSheetIndex(String sheetName) throws SheetNotFoundException{ for (int i = 0; i < workbook.getNumberOfSheets(); i++) { if(workbook.getSheetAt(i).getSheetName().equals(sheetName) ) { return i; } } throw new SheetNotFoundException("Sheet '" + sheetName +"' not found" ); } public void cloneSheet(int index, String newSheetName) { HSSFSheet newSheet = workbook.cloneSheet(index); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { if(newSheet.equals(workbook.getSheetAt(i))) { workbook.setSheetName(i, newSheetName); break; } } } 

But the most interesting is the Apache Commons JEXL library .

JSTL, it is a concept that it can be seen in the shell script or ECMAScript.

Here is a slightly converted example from the site:

  // Create or retrieve a JexlEngine JexlEngine jexl = new JexlEngine(); // Create an expression object String jexlExp = "user.name"; Expression e = jexl.createExpression( jexlExp ); // Create a context and add data JexlContext jc = new MapContext(); jc.set("user", new User("") ); // Now evaluate the expression, getting the result Object o = e.evaluate(jc); o.toString(); // "" 

Thus, there is no need to list all the tags in the java code, asking them to match with real data.
It is enough to set a match only for the object; after this, the task of replacing the “tag” with its value is reduced to searching for the “tags”, feeding them with the Commons JEXL and writing to the cell with the tag the result of the library's work. As it turned out messy, I will try to explain with an example.
Let us in the template "tags" will look like this: $ {user.name}, $ {user.age}. And in the java code it will be enough just to place an object of the class User in the map.
After this, let's loop through all the cells in the document, find the lines bounded by '$ {' and '}' and replace the values ​​in them with the output from the Commons JEXL library

 private void fillSheet() { User user=new User("",25); //   Map<String,Object> mappings=new HashMap<String,Object>(); mappings.put("user",user); JexlEngine engine=new JexlEngine(); JexlContext context=new MapContext(mappings); for(Row row : sheet) { for(Cell cell : row) { if(cell.getCellType()==Cell.CELL_TYPE_STRING){ String exp=findExpression(cell); if(exp!=null){ Expression e=engine.createExpression(exp); Object o=e.evaluate(context); if(o!=null){ String result=o.toString(); cell.setCellValue(result); } } } } } } 

The findExpression () method searches a string contained in a cell for a substring between '$ {' and '}'

I learned about the Apache Commons JEXL library by meeting with the JETT project. I didn’t want to add a bunch of libraries to the project (JETT depends on several other libraries), and I don’t need all the functionality of JETT. But it was interesting to find out how it was there, inside :). I will be glad if at least this post helps someone.