The Internet's Simplest Tutorial on Rendering an Excel Workbook with Spring

Brendan Benson

This dead-simple tutorial demonstrates how to render a Microsoft Excel file in Spring.

Bookmark or star the completed version of this tutorial on GitHub: SpringExcelWorkbookExample

  1. Add the following dependencies to build.gradle:

    compile('org.springframework.boot:spring-boot-starter-web')
    compile('org.apache.poi:poi:3.15')
    compile('org.apache.poi:poi-ooxml:3.15')
    

  2. Create a controller that returns the object you would like converted to xlsx format. In this case, we’d like to render a list of grades.

    @RestController
    public class GradesController {
        @RequestMapping(
                value = "/grades.xlsx",
                method = RequestMethod.GET,
                produces = "application/xlsx"
        )
        public ModelAndView listGrades() {
            return new ModelAndView(
                    new GradesView(),
                    "grades",
                    asList(
                            new Grade("Jimmy", "B+"),
                            new Grade("Kelly", "A-")
                    )
            );
        }
    }
    
    public class Grade {
        private String name;
        private String grade;
        ...
    }
    

  3. Create the GradesView class, which will take the list of grades and render them into a Workbook.

    class GradesView extends AbstractXlsxStreamingView {
        @Override
        protected void buildExcelDocument(
                Map<String, Object> model,
                Workbook workbook,
                HttpServletRequest request,
                HttpServletResponse response
        ) throws Exception {
            @SuppressWarnings("unchecked")
            List<Grade> grades = (List<Grade>) model.get("grades");
    
            Sheet sheet = workbook.createSheet("Grades");
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("Name");
            headerRow.createCell(1).setCellValue("Grade");
    
            int rowNum = 1;
    
            for (Grade grade : grades) {
                Row valueRow = sheet.createRow(rowNum);
                valueRow.createCell(0).setCellValue(grade.getName());
                valueRow.createCell(1).setCellValue(grade.getGrade());
                rowNum++;
            }
        }
    }
    

And that’s it! Spring will automatically handle writing the modified Workbook to the response.

Browse the completed code on GitHub: SpringExcelWorkbookExample.

comments powered by Disqus