Detailed explanation of ajax implementation of excel report export

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

Background

I encountered a scenario in the project 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.

Related learning recommendations: javascript video tutorial

First edition

Main code

Front-end

Use jquery's 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);
  }
 }
});

Back-end

Use easypoi (please ask yourself how to use easypoi 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 be exported normally, but the downloaded excel is all garbled. After searching for answers, I sorted out the possible reasons:

1. The character set is not set on the back end, or the character set is set uniformly 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!