An easy way to export an excel file in JavaWeb

When doing system projects, I often need to do the export function, whether it is to export excel or cvs files. The demo I'm following is implemented under the framework of springmvc.

1. In JS, you only need to request export in GET mode:

$('#word-export-btn').parent().on('click',function(){
		var promotionWord = JSON.stringify($('#mainForm').serializeObject());
		location.href="${ctx}/promotionWord/export?promotionWord="+promotionWord; 
});

2. What to do in controller is to output the file in data stream format:

  @RequestMapping("/export")
  public void export(HttpSession session, String promotionWord, HttpServletRequest request, HttpServletResponse response) throws IOException {
    User sessionUser = (User) session.getAttribute("user");
    JSONObject jsonObj = JSONObject.parseObject(promotionWord);
    HSSFWorkbook wb = promotionWordService.export(sessionUser.getId(), jsonObj);
    response.setContentType("application/vnd.ms-excel");
    Calendar cal = Calendar.getInstance();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    String fileName = "word-" + sdf.format(cal.getTime()) + ".xls";
    response.setHeader("Content-disposition", "attachment;filename=" + fileName);
    OutputStream ouputStream = response.getOutputStream();
    wb.write(ouputStream);
    ouputStream.flush();
    ouputStream.close();
  }

3. In service, data needs to be written to the format file:

public HSSFWorkbook export(String userId, JSONObject jsonObj) {
HSSFWorkbook wb = new HSSFWorkbook(); 
HSSFSheet sheet = wb.createSheet("word"); 
HSSFRow row = sheet.createRow(0);
    HSSFCellStyle style = wb.createCellStyle(); 
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
List<PromotionWord> pWordList;
Map<String, Object> map = new HashMap<>();
map.put("userId", userId);
map.put("checkExistRule", jsonObj.getString("checkExistRule"));
map.put("status", jsonObj.getString("status"));
map.put("qsStar", jsonObj.getString("qsStar"));

map.put("impressionCount", jsonObj.getString("impressionCount"));

map.put("selectGroupId", jsonObj.getString("selectGroupId"));
map.put("isCheck", jsonObj.getString("isCheck"));
map.put("word", jsonObj.getString("word"));

Long impression = jsonObj.getLong("impressionCount");
Long click = jsonObj.getLong("clickCount");
if(impression != null){
PromotionWord word = new PromotionWord();
word.setCreatedBy(userId);
word.setImpressionCount7(impression);
pWordList = getTwentyPercentlists(word);
if(pWordList != null && pWordList.size() > 0){
map.put("impressionCount", pWordList.get(pWordList.size()-1).getImpressionCount());
}else{
map.put("impressionCount", 1);
}
}else if(click != null){
PromotionWord word = new PromotionWord();
word.setCreatedBy(userId);
word.setClickCount7(click);
pWordList = getTwentyPercentlists(word);
if(pWordList != null && pWordList.size() > 0){
map.put("clickCount", pWordList.get(pWordList.size()-1).getClickCount());
}else{
map.put("clickCount", 1);
}
}

List<PromotionWord> list = commonDao.queryList(PROMOTION_WORD_DAO + ".queryExportDataByUser", map);


String[] excelHeader = {" keywords ", " The price "," Search the heat "," Promotion of score "," Buy the heat "," exposure "," Click on the quantity "," Click rate "," The promotion time "," cost "," Average click cost "," Number of matched products "," Estimate the ranking "," state "};
for (int i = 0; i < excelHeader.length; i++) { 
      HSSFCell cell = row.createCell(i); 
      cell.setCellValue(excelHeader[i]); 
      cell.setCellStyle(style); 
      if(i == 0){
      sheet.setColumnWidth(0, 30*256);
      }else{      
      sheet.setColumnWidth(i, 10*256);
      }
    } 
if(list != null && list.size() > 0)
for (int i = 0; i < list.size(); i++) { 
      row = sheet.createRow(i + 1); 
      PromotionWord word = list.get(i); 
      row.createCell(0).setCellValue(word.getWord()); 
      row.createCell(1).setCellValue(word.getPrice()+""); 
      row.createCell(2).setCellValue(word.getSearchCount());
      row.createCell(3).setCellValue(word.getQsStar());
      row.createCell(4).setCellValue(word.getBuyCount());
      row.createCell(5).setCellValue(word.getImpressionCount7());
      row.createCell(6).setCellValue(word.getClickCount7());
      if(word.getClickCount7() == 0L){
      row.createCell(7).setCellValue("0.00%");
      }else{
      DecimalFormat df = new DecimalFormat("0.00%");
      row.createCell(7).setCellValue(df.format((Double.valueOf(word.getClickCount7())/Double.valueOf(word.getImpressionCount7()))));
      }
      row.createCell(8).setCellValue(word.getOnlineTime7());
      row.createCell(9).setCellValue(word.getCost7()+"");
      row.createCell(10).setCellValue(word.getAvgCost7()+"");
      row.createCell(11).setCellValue(word.getMatchCount());
      String rank = "";
      if(word.getMatchCount() != null && word.getMatchCount() != 0){
      if(word.getProspectRank() == null || word.getProspectRank() == 0L){       
       rank = " Other location ";
       }else{
       rank = " The first "+word.getProspectRank()+" position ";
       }
      }else{
      rank = "---";
      }
      
      row.createCell(12).setCellValue(rank);
      row.createCell(13).setCellValue(word.getStatus() == 1 ?" suspended ":" Start the ");
    } 

return wb;
}

And then you can just click on the export and it works.