Generate Excel Sheet with Formula embedded in it

Recently in our application, we needed to generate an excel sheet where some of the values for the column needed to be calculated on the basis of values of other columns depending upon an embedded formula in the excel sheet.

To work with excel file we need the following in your BuildConfig.groovy:

[java]

dependencies{
compile ‘org.apache.poi:poi:3.7’
}
[/java]

Now lets create an excel sheet :

[java]
HSSFWorkbook workBook = new HSSFWorkbook()
//create sheet with name ‘Demo Sheet’ in excel file
HSSFSheet spreadSheet = workBook.createSheet("Demo Sheet")
[/java]

For convenience, lets  specify width of the columns for our excel sheet :

[java]
spreadSheet.setDefaultColumnWidth(15)
[/java]

Lets name our columns :

[java]

CellStyle style = createStyle(workBook) //we will get back to this later
HSSFRow row = spreadSheet.createRow(0) //adds a new Row to the sheet
HSSFCell cell
List columnTitle = [‘Value 1’, ‘Value 2’, ‘Value 3’,
‘Sum’, ‘Difference’, ‘Multiplication’,
‘Average’, ‘Max’, ‘Min’]
columnTitle.eachWithIndex { String title, Integer index ->
cell = row.createCell(index) //adds a new cell to Row
cell.setCellValue(new HSSFRichTextString(title)) //defining the heading for this sheet
cell.setCellStyle(style) // applying style to cell created
}
[/java]

For adding style to the cells we can create our custom CellStyle:

[java]
def createStyle(HSSFWorkbook wb) {
CellStyle style = wb.createCellStyle()
Font contentFont = wb.createFont()
style.setAlignment(CellStyle.ALIGN_CENTER)
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER)
style.setFont(contentFont)
style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM)
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM)
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM)
style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM)
return style
}
[/java]

To add data in your excel file:

[java]
Integer rowNum = 2
(1..5).each { Integer no ->
row = spreadSheet.createRow(rowNum++)

//creating random values for first 3 columns
cell = row.createCell(0)
cell.setCellValue(50 + no)
cell.setCellStyle(style)
cell = row.createCell(1)
cell.setCellValue(20 + no)
cell.setCellStyle(createStyle(workBook))
cell = row.createCell(2)
cell.setCellValue(10 + no)
cell.setCellStyle(style)

// creating columns with embedded formulas in them

//create a column giving sum of first 3 columns
cell = row.createCell(3)
cell.setCellValue(”)
cell.setCellStyle(style)
cell.setCellFormula("A${rowNum}+B${rowNum}+C${rowNum}")

//subtract second and third column from first column
cell = row.createCell(4)
cell.setCellValue(”)
cell.setCellStyle(style)
cell.setCellFormula("A${rowNum}-B${rowNum}-C${rowNum}")

//multiply first, second and third columns
cell = row.createCell(5)
cell.setCellValue(”)
cell.setCellStyle(style)
cell.setCellFormula("A${rowNum}*B${rowNum}*C${rowNum}")

//calculate average of first 3 columns
cell = row.createCell(6)
cell.setCellValue(”)
cell.setCellStyle(style)
cell.setCellFormula("AVERAGE(A${rowNum}:C${rowNum})")

//calculate max of first 3 columns
cell = row.createCell(7)
cell.setCellValue(”)
cell.setCellStyle(style)
cell.setCellFormula("MAX(A${rowNum}:C${rowNum})")

//calculate Minimum of first 3 columns
cell = row.createCell(8)
cell.setCellValue(”)
cell.setCellStyle(style)
cell.setCellFormula("MIN(A${rowNum}:C${rowNum})")
}
[/java]

Here we are creating 5 rows, with first 3 columns as random values generated and the values for rest of the columns are generated on the basis of embedded formula in each of them.

Now lets add code to download this excel in our action :

[java]
byte[] sourceExcel = workBook?.bytes
response?.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response?.setHeader "Content-disposition", "attachment filename=DemoFile.xls"
response?.contentLength = sourceExcel?.length
response?.outputStream << sourceExcel
[/java]

Now, we should be able to download the excel file with embedded formulas in it, if we change the values of any of the first 3 columns, we should see the results of our other columns change!

Hope this helps!

Tanu Siwag

Tanu[at]intelligrape[dot][com]