前端导出excel

一、导出步骤：

1. 后端返回需要的数据列表。
2. 前端去组装。
3. 使用oper-excel.js函数导出。

二、附上oper-excel.js文件
链接：https://pan.baidu.com/s/1YzX6CWRHR1gVWxW80NO56w
提取码：65zi

三、前端实现过程

<template>
	<el-button icon="el-icon-download" type="primary" @click="exportExcel">导出</el-button>
</template>        

import {export_json_to_excel} from "@/components/common/oper-excel";
export default{
  data() {
    return {
    	saveTitle:['test1','test2','test3','test4','test5'],
    }
  },
  methods: {
			 //导出数据
            exportExcel(){
                this.$confirm('确认导出文件吗？', '提示', {}).then(() => {
                    let fileName = formatDate(new Date(),'yyyyMMdd')+"-记录";
                    let pageNo = this.page;
                    let pageSize = 99999;
                    let type = this.filters.type;
                    let number = this.filters.number;
                    getTestList({pageNo, pageSize,type, number}).then( //从后端获取数据列表
                        (response) => {
                            this.list = response.body.content;                      
                        },
                        (response) => {
                            this.$message.error("导出文件失败！");
                        }
                    );           
                    this.getExcelData(this.list); //将列表组装到excel
                    export_json_to_excel(this.saveTitle, JSON.parse(JSON.stringify(this.excelData)),fileName);//导出
                })
            },
            // excel组装
            getExcelData(list){
                this.excelData = [];
                for(let i = 0;i < list.length;i++){
                    this.excelData[i] = new Array(11);
                    this.excelData[i][0] = list[i].test1;
                    this.excelData[i][1] = list[i].test2;
                    this.excelData[i][2] = list[i].test3;
                    this.excelData[i][3] = formatDate(new Date(list[i].time) , 'yyyy-MM-dd hh:mm');
                    this.excelData[i][4] = list[i].dime ? formatDate(new Date(list[i].dime ) ,'yyyy-MM-dd hh:mm') : '';
                }
            },
  }
}


		

后端导出excel

一、导出步骤

1. 浏览器点击导出方法后直接从后端获取数据并组装，通过流写入返回给浏览器。
2. 前端拿到数据后再简单封装一下

二、实现过程

1. 前端代码：

<template>
	<el-button icon="el-icon-download" type="primary" @click="exportExcel">导出</el-button>
</template>   
methods: {
	//导出数据
            exportExcel(){
                  this.$confirm('确认导出文件吗？', '提示', {}).then(() => {
                     let fileName = formatDate(new Date(),'yyyyMMdd')+"-记录";
                    let type = this.filters.type;
                    let number = this.filters.number;
                    excelExport ({type, number}).then(
                        resp =>{
                            let blob = new Blob([resp.data],{type:"application/vnd.ms-excel;charset=UTF-8"});
                            let objUrl = URL.createObjectURL(blob);
                            let fileLink = document.createElement('a');
                            fileLink.href = objUrl;
                            fileLink.download = '记录.xls';
                            fileLink.click();
                        }
                   ).catch(response=>{
                       this.$message.error('导出文件出错!');
                   });
                })
            },
}
     

这里有个关键点，从后端获取数据后，返回前端时，一定要设置responseType:"arraybuffer”，否则就是中文乱码。
```javascript
export const excelExport = function(param) {
  return Vue.http({
    url: Utils.uriToUrl(
      "/repairplan-service/plancompilation/export/allPlans",
      param
    ),
    responseType: "arraybuffer",
  });
};

2. 后端代码：

   1） controller层
```java
@GetMapping("/export")
	public void exportList(
			@RequestParam(value = "type", required = false, defaultValue = "") final String type,
			@RequestParam(value = "number", required = false, defaultValue = "") final String number
	)throws Exception {
		service.exportList(type,number);

	}

2）service层

  //导出列表
    void exportList(String type,String number) throws Exception;

3）serviceImpl层

 @Override
    public void exportList(String type,String number) throws Exception {

        List<Test> resultList = testDao.findAllById(ids); //这步之前的省略，主要是获取导出的数据列表
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("记录列表");
        int rowNo = 0;

        HSSFRow titleRow = sheet.createRow(rowNo++);

        for (int i = 0; i < excelFileCols.length; i++) {
            HSSFCell titleCell = titleRow.createCell(i);
            titleCell.setCellValue(excelFileCols[i]);
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm");
        for (Test test : resultList){               
            HSSFRow row = sheet.createRow(rowNo++);
            for (int i = 0; i < excelFileCols.length; i++) {
                HSSFCell cell = row.createCell(i);
                switch (excelFileCols[i]) {
                    case "测试1":
                        cell.setCellValue(test.getTest1());
                        break;
                    case "测试2":
                        cell.setCellValue(test.getTest2());
                        break;
                    case "测试3":
                        cell.setCellValue(test.getTest3());
                        break;              
                    default:
                        break;
                }
            }
        }

        try{
            String fileName =URLEncoder.encode( "test.xls","utf-8");
            httpResponse.setContentType("application/x-msdownload; charset=UTF-8");
            httpResponse.setHeader("Content-disposition", "attachment; filename=" + fileName);
            OutputStream os = httpResponse.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    private static final String[] excelFileCols = {
            "测试1",
            "测试2",
            "测试3"
    };

具体实现过程就这些。

补充：第二种方法下载文档时需要注意，请求时需要添加responseType:blob 或者
responseType:arraybuffer
blob: 是包含二进制数据的blob对象
arraybuffer: 是包含二进制数据的javascript的Arraybuffer
标签下载默认是同源下载，若跨域的话，需要将资源当成二进制blob，再进行下载处理，这里生成blob的代码如下：

 let blob = new Blob([resp.data],{type:"application/vnd.ms-excel;charset=UTF-8"});
 let objUrl = URL.createObjectURL(blob);

参考MDN XMLHttpRequest.responseType