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

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