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.<\/p>\n
To work with excel file we need the following in your BuildConfig.groovy:<\/p>\n
[java]<\/p>\n
dependencies{
\ncompile ‘org.apache.poi:poi:3.7’
\n}
\n[\/java]<\/p>\n
Now lets create an excel sheet :<\/p>\n
[java]
\nHSSFWorkbook workBook = new HSSFWorkbook()
\n\/\/create sheet with name ‘Demo Sheet’ in excel file
\nHSSFSheet spreadSheet = workBook.createSheet("Demo Sheet")
\n[\/java]<\/p>\n
For convenience, lets \u00a0specify width of the columns for our excel sheet :<\/p>\n
[java]
\nspreadSheet.setDefaultColumnWidth(15)
\n[\/java]<\/p>\n
Lets name our columns :<\/p>\n
[java]<\/p>\n
CellStyle style = createStyle(workBook) \/\/we will get back to this later
\nHSSFRow row = spreadSheet.createRow(0) \/\/adds a new Row to the sheet
\nHSSFCell cell
\nList columnTitle = [‘Value 1’, ‘Value 2’, ‘Value 3’,
\n ‘Sum’, ‘Difference’, ‘Multiplication’,
\n ‘Average’, ‘Max’, ‘Min’]
\ncolumnTitle.eachWithIndex { String title, Integer index ->
\ncell = row.createCell(index) \/\/adds a new cell to Row
\ncell.setCellValue(new HSSFRichTextString(title)) \/\/defining the heading for this sheet
\ncell.setCellStyle(style) \/\/ applying style to cell created
\n}
\n[\/java]<\/p>\n
For adding style to the cells we can create our custom CellStyle:<\/p>\n
[java]
\ndef createStyle(HSSFWorkbook wb) {
\nCellStyle style = wb.createCellStyle()
\nFont contentFont = wb.createFont()
\nstyle.setAlignment(CellStyle.ALIGN_CENTER)
\nstyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER)
\nstyle.setFont(contentFont)
\nstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM)
\nstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM)
\nstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM)
\nstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM)
\nreturn style
\n}
\n[\/java]<\/p>\n
To add data in your excel file:<\/p>\n
[java]
\nInteger rowNum = 2
\n(1..5).each { Integer no ->
\nrow = spreadSheet.createRow(rowNum++)<\/p>\n
\/\/creating random values for first 3 columns
\ncell = row.createCell(0)
\ncell.setCellValue(50 + no)
\ncell.setCellStyle(style)
\ncell = row.createCell(1)
\ncell.setCellValue(20 + no)
\ncell.setCellStyle(createStyle(workBook))
\ncell = row.createCell(2)
\ncell.setCellValue(10 + no)
\ncell.setCellStyle(style)<\/p>\n
\/\/ creating columns with embedded formulas in them<\/p>\n
\/\/create a column giving sum of first 3 columns
\ncell = row.createCell(3)
\ncell.setCellValue(”)
\ncell.setCellStyle(style)
\ncell.setCellFormula("A${rowNum}+B${rowNum}+C${rowNum}")<\/p>\n
\/\/subtract second and third column from first column
\ncell = row.createCell(4)
\ncell.setCellValue(”)
\ncell.setCellStyle(style)
\ncell.setCellFormula("A${rowNum}-B${rowNum}-C${rowNum}")<\/p>\n
\/\/multiply first, second and third columns
\ncell = row.createCell(5)
\ncell.setCellValue(”)
\ncell.setCellStyle(style)
\ncell.setCellFormula("A${rowNum}*B${rowNum}*C${rowNum}")<\/p>\n
\/\/calculate average of first 3 columns
\ncell = row.createCell(6)
\ncell.setCellValue(”)
\ncell.setCellStyle(style)
\ncell.setCellFormula("AVERAGE(A${rowNum}:C${rowNum})")<\/p>\n
\/\/calculate max of first 3 columns
\ncell = row.createCell(7)
\ncell.setCellValue(”)
\ncell.setCellStyle(style)
\ncell.setCellFormula("MAX(A${rowNum}:C${rowNum})")<\/p>\n
\/\/calculate Minimum of first 3 columns
\ncell = row.createCell(8)
\ncell.setCellValue(”)
\ncell.setCellStyle(style)
\ncell.setCellFormula("MIN(A${rowNum}:C${rowNum})")
\n}
\n[\/java]<\/p>\n
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.<\/p>\n
Now lets add code to download this excel in our action :<\/p>\n
[java]
\nbyte[] sourceExcel = workBook?.bytes
\nresponse?.setContentType("application\/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
\nresponse?.setHeader "Content-disposition", "attachment filename=DemoFile.xls"
\nresponse?.contentLength = sourceExcel?.length
\nresponse?.outputStream << sourceExcel
\n[\/java]<\/p>\n
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!<\/p>\n
Hope this helps!<\/p>\n
Tanu Siwag<\/p>\n
Tanu[at]intelligrape[dot][com]<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":74,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"gallery","meta":{"iawp_total_views":6},"categories":[7],"tags":[1431,1429,1430,4840],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/13334"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/74"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=13334"}],"version-history":[{"count":0,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/13334\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=13334"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=13334"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=13334"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}