Detailed explanation of ajax implementation of excel report export

Recommended (free): ajax video tutorial

Use ajax to export excel reports [Solving the garbled code problem], for your reference, the specific content is as follows

Background

In the project, I encountered a scenario where I needed to export an excel report. Because token verification is required, the a tag cannot be used; because the page is complex, form submission cannot be used. It is initially considered to use ajax on the front end, return the stream on the back end, and define the specified header.

First version

Main code

Front end

Use jquery ajax

var queryParams = {"test":"xxx"};
var url = "xxx";
$.ajax({
 type : "POST", //提交方式
 url : url,//路径
 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(&#39;导出失败，导出的内容为空！&#39;);
   return
  }
  if(window.navigator.msSaveOrOpenBlob) {
   navigator.msSaveOrOpenBlob(blob, &#39;test.xls&#39;)
  } else {
   const aLink = document.createElement(&#39;a&#39;);
   aLink.style.display = &#39;none&#39;;
   aLink.href = window.URL.createObjectURL(blob);
   aLink.download = &#39;test.xls&#39;;
   document.body.appendChild(aLink);
   aLink.click();
   document.body.removeChild(aLink);
  }
 }
});

Backend

Use easypoi (how to use easypoi please Baidu)

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<>();
 
   // excel总体设置
   ExportParams exportParams = new ExportParams();
   // 指定sheet名字
   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 results

excel can The export is normal, but the downloaded excel is all garbled. After various searches for answers, I sorted out the possible reasons:

1. The character set is not set on the back end, or the character set is uniformly set in the filter of the spring framework;
2. The front end The character set encoding is not set on the page;
3. You need to add request.responseType = "arraybuffer" in ajax;

After continuous testing, mine should be caused by the third point. But it still doesn't work after adding it in jquery ajax, and the garbled code problem cannot be solved.

Second Edition

Main code

Front end, using native ajax. The backend is 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(&#39;导出失败，导出的内容为空！&#39;);
  return;
 }
 if(window.navigator.msSaveOrOpenBlob) {
  navigator.msSaveOrOpenBlob(blob, &#39;test.xls&#39;)
 } else {
  const aLink = document.createElement(&#39;a&#39;);
  aLink.style.display = &#39;none&#39;;
  aLink.href = window.URL.createObjectURL(blob);
  aLink.download = &#39;testxls&#39;;
  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 results

The downloaded excel is no longer garbled, and the use of "arraybuffer" in native ajax is effective.

Summary

"arraybuffer" This parameter causes excel to export garbled characters. The setting in native ajax is valid, but it has not yet been found to be effective in jquery's ajax. The way.

The above is the detailed content of Detailed explanation of ajax implementation of excel report export. For more information, please follow other related articles on the PHP Chinese website!