I am using Jasper Report API 4.5 (ready to update if required for this solution). With Jasper Report I am exporting report to HTML, Excel, CSV and PDF. A situation I came across is that if data is having valid excel formula then on exporting data to Excel and CSV and viewing file to MS Excel, it executes excel formula. This causes a security issue as some data is having excel formula to perform some action on local computer then it may harm system.
We can not validate data at the time of insertion, doing so may fix the problem. But currently there is no data validation as such to prevent inserting excel formula. Preferred solution to me is to manage from Jasper Report.
My question is, is there any mechanism or export parameter which can prevent excel formula to be executed? Or is there any way to print data as is and ignore excel formula?
For Excel export:
We can use Apache POI or SmartXLS API to set cell type to Text externally in order to get same result from database.
For Apache POI, we can set cell format type as "@", here is the link from Apache POI javadoc.
For SmartXLS, we can set workbook range style to set format type as Text, code snippet for it would be like below:
RangeStyle rangeStyle = workBook.getRangeStyle(1, 0, 50, 0);
rangeStyle.setCustomFormat("@");
For CSV Export:
Upgraded JasperReport 6.3. There is a feature to add enclosure to csv field. By default excel have single quote (') as field enclosure, so that may execute the field as formula. To avoid that we can set enclosure field to (`).
SimpleCsvExporterConfiguration reportExportConf = new SimpleCsvExporterConfiguration();
reportExportConf.setFieldEnclosure("`");
reportExportConf.setForceFieldEnclosure(true);