How To Recalculate Formula Result On XLS (jxls lib)

If you use template directly from xls including the formula, you will need to recalculate and evaluate the result. It happen because Excel caches previously calculated results and you need to trigger recalculation to updated them.  From POI documentation there are two way to do this recalculation method. But in this article I will use only one method as example.

Here is my snap code:

@Autowired
private ServletContext context;

private void writeXLS(HttpServletResponse response, Books books) {
        Date today = new Date();
        String attch = "attachment; filename=\" + "fileresult.xls\"";
        try {
            try (ServletOutputStream os = response.getOutputStream()) {
                Map dataSource = fillXLSdata(books, today); //this my method just to fill the datasource
                String reportLocation = context.getRealPath("WEB-INF");
                response.setContentType("application/vnd.ms-excel");
                response.setHeader("Content-Disposition", attch);
                XLSTransformer transformer = new XLSTransformer();
                try (FileInputStream fis = new FileInputStream(reportLocation + "/reportTemplate.xls")) {
                    Workbook workbook = transformer.transformXLS(fis, dataSource);
                    workbook.getCreationHelper().createFormulaEvaluator().evaluateAll(); //evaluate all result
                    workbook.write(os);
                    os.flush();
                }
            }
        } catch (IOException | ParsePropertyException | InvalidFormatException e) {
            System.out.println("failed to process file because : " + e.getMessage());
        }
    }

Hope this simple article can help 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s