Export excel report with Ajax

Time:2020-9-27

Using Ajax to achieve excel report export [solve the problem of garbled code], for your reference, the specific content is as follows

background

There is a scenario in the project. You want to export an excel report. Because token verification is required, the a tag cannot be used; due to the complexity of the page, form submission cannot be used. The front-end uses Ajax, and the back-end returns the stream, and defines the specified header.

first edition

Main code

front end

Ajax using jquery

var queryParams = {"test":"xxx"};
var url = "xxx";
$.ajax({
 Type: "post", // submission method
 URL: URL, // path
 contentType: "application/json",
 data: JSON.stringify(queryParams),
 beforeSend: function (request) {
  request.setRequestHeader("Authorization", "xxx");
 },
 success : function(result) {
  const blob = new Blob([result], {type:"application/vnd.ms-excel"});
  if(blob.size < 1) {
   Alert ('export failed, the exported content is empty! ).
   return
  }
  if(window.navigator.msSaveOrOpenBlob) {
   navigator.msSaveOrOpenBlob(blob, 'test.xls')
  } else {
   const aLink = document.createElement('a');
   aLink.style.display = 'none';
   aLink.href = window.URL.createObjectURL(blob);
   aLink.download = 'test.xls';
   document.body.appendChild(aLink);
   aLink.click();
   document.body.removeChild(aLink);
  }
 }
});

back-end

How to use easypoi

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;

@PostMapping(value = "/download")
public void downloadList(@RequestBody Objct obj, HttpServletResponse response) {

 ......

 List<Custom> excelList = new ArrayList<>();
 
   //Overall setting of Excel
   ExportParams exportParams = new ExportParams();
   //Specify the sheet name
   exportParams.setSheetName("test");
   Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Custom.class, excelList);
 
   response.setContentType("application/vnd.ms-excel");
   response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("test", "utf-8") + ".xls");
   OutputStream outputStream = response.getOutputStream();
   workbook.write(outputStream);
   outputStream.flush();
   outputStream.close();
   
 ......
 
}

test result

Excel can be exported normally, but the downloaded excel is all garbled. After looking for the answers, I sorted out the following reasons:

1. The character set is not set in the back end, or the character set is set uniformly in the spring framework filter;
2. The character set code is not set on the front page;
3. It needs to be added in Ajax request.responseType = “arraybuffer”;

After continuous testing, I should be led by the third point. But it still doesn’t work after adding it in jQuery Ajax, and the problem of garbled code can’t be solved.

Second Edition

Main code

Front end, using native Ajax. Back end unchanged.

var xhr = new XMLHttpRequest();
xhr.responseType = "arraybuffer"; 
xhr.open("POST", url, true);
xhr.onload = function () {
 const blob = new Blob([this.response], {type:"application/vnd.ms-excel"});
 if(blob.size < 1) {
  Alert ('export failed, the exported content is empty! ).
  return;
 }
 if(window.navigator.msSaveOrOpenBlob) {
  navigator.msSaveOrOpenBlob(blob, 'test.xls')
 } else {
  const aLink = document.createElement('a');
  aLink.style.display = 'none';
  aLink.href = window.URL.createObjectURL(blob);
  aLink.download = 'testxls';
  document.body.appendChild(aLink);
  aLink.click();
  document.body.removeChild(aLink);
  return;
 }
}
xhr.setRequestHeader("Authorization", "xxx");
xhr.setRequestHeader("Content-Type", "application/json");
xhr.send(JSON.stringify(queryParams));

test result

The downloaded excel is no longer garbled, and the use of “arraybuffer” in native AJAX is effective.

summary

The “arraybuffer” parameter results in the export garbled code of Excel. It is effective to set it in the native Ajax, but the effective method has not yet been found in the Ajax of jQuery.

The above is the whole content of this article, I hope to help you in your study, and I hope you can support developeppaer more.