How to export to Excel from Spring MVC

Using AbstractExcelView and ModalAndView is possible in SpringMVC. See below for details.

http://learnfromexamples.com/generate-excel-in-spring-mvc-application-using-apache-poi/

without using a view

      

        
        
        
      

    

To not use the view, you must make the return type of your request mapping method void

@Controller
public class MyController{

  @RequestMapping("/xyz")
  public void getExcel(HttpServletRequest request, HttpServletResponse response){
     // 1. Fetch your data
     // 2. Create your excel
     // 3. write excel file to your response.
  }

}

      

        
        
        
      

    

I believe you have already done part 1. Part 2 is completely different and you need to use a third party api for that. Apache POI is very simple and efficient. https://poi.apache.org/spreadsheet/ . Their quickguide is nice to go with.

Once you have created your file, now you need to write it back so that it can be downloaded to the client computer. Here's how you can do it. Let's say you createdxyz.xls

    response.setContentType("application/octet-stream");    // set content attributes for the response

    FileInputStream inputStream = new FileInputStream(new File("xyz.xls"));

    OutputStream outputStream = response.getOutputStream();             // get output stream of the response

    byte[] buffer = new byte[1024];
    int bytesRead = -1;
    while ((bytesRead = inputStream.read(buffer)) != -1) {  // write bytes read from the input stream into the output stream
        outputStream.write(buffer, 0, bytesRead);
    }

    outputStream.flush();

      

        
        
        
      

    

works

In your controller

@RequestMapping(value = "/downloadExcel", method = RequestMethod.GET)
public ModelAndView downloadExcel(Model model) {

    List<String> usersGateways = uDAO.GetGwRoleUser();

    List<User> users = gatewayManagedDAO.findAll();
    return new ModelAndView(new ExcelView(), "users ", users );
    }
}

      

        
        
        
      

    

In your ExcelView

public class ExcelView extends AbstractXlsView{

@Override
public void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    // TODO Auto-generated method stub


    // change the file name
    response.setHeader("Content-Disposition", "attachment; filename=\"my-exported-file.xls\"");

    @SuppressWarnings("unchecked")
    List<User> users= (List<GatewayManage>) model.get("users");

    // create excel xls sheet
    Sheet sheet = workbook.createSheet("Users Detail");
    sheet.setDefaultColumnWidth(30);

    // create style for header cells
    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setFontName("Arial");
    style.setFillForegroundColor(HSSFColor.BLUE.index);
    //style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    //font.setBold(true);
    font.setColor(HSSFColor.BLACK.index);
    style.setFont(font);


    // create header row
    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("First Name");
    header.getCell(0).setCellStyle(style);
    header.createCell(1).setCellValue("Last Name");
    header.getCell(1).setCellStyle(style);
    header.createCell(2).setCellValue("Number");
    header.getCell(2).setCellStyle(style);
    header.createCell(3).setCellValue("Age");
    header.getCell(3).setCellStyle(style);



    int rowCount = 1;
    for(User user : users){
        Row userRow =  sheet.createRow(rowCount++);
        gatewayRow.createCell(0).setCellValue(user.getFirstName());
        gatewayRow.createCell(1).setCellValue(gateway.getLastName());
        gatewayRow.createCell(2).setCellValue(gateway.getNumber());
        gatewayRow.createCell(3).setCellValue(gateway.getAge());

        }

}
}

      

        
        
        
      

    

You can replace your User class with yours (Studen, aBook ...) and it works!

Prepare your Excel sheet like this one (using apache poi). And then (for example) in your controller, you can easily write it to the body:

@GetMapping(value = "/alluserreportExcel")
public ResponseEntity<InputStreamResource> excelCustomersReport() throws IOException {
    List<AppUser> users = (List<AppUser>) userService.findAllUsers();
    ByteArrayInputStream in = GenerateExcelReport.usersToExcel(users);
    // return IO ByteArray(in);
    HttpHeaders headers = new HttpHeaders();
    // set filename in header
    headers.add("Content-Disposition", "attachment; filename=users.xlsx");
    return ResponseEntity.ok().headers(headers).body(new InputStreamResource(in));
}

      

        
        
        
      

    

The complete example is here .