vue下载excel文件

柔光的暖阳◎ 2021-11-05 03:44 476阅读 0赞

公司后台管理系统前端用iview做的，最近需要做个导出功能，前后台代码写完后联调，发现前台下载excel后是乱码，以下为解决办法

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BxajIyMg_size_16_color_FFFFFF_t_70][]

**1、新开页面直接下载excel**

由于新开页面链接和前台页面的域名不一样，导致后台拿不到登录token,业务代码会报错，所以放弃了这种方式

如果有人采取这种方式可以参考

controller代码：

@GetMapping(value = "inventoryList")
        @ApiOperation(value = "导出库存查询列表", notes = "export")
        public void export(HttpServletRequest request, HttpServletResponse response, @Valid QueryBoundReq queryInfoRequest) {
            HSSFWorkbook workbook = new HSSFWorkbook();
            //通过获取请求头中浏览器信息
            String agent = request.getHeader("User-Agent");
            //根据浏览器不同将文本进行编码
            String  fileName = "库存查询"+ (new Date()).getTime()  + ".xls";//设置要导出的文件的名字
            try {
                fileName = FileUtils.encodeDownloadFilename(fileName, agent);
            } catch (IOException e) {
                e.printStackTrace();
            }
    //业务代码，主要往workbook里填充数据
            exportService.exportList(request,queryInfoRequest,workbook);
            response.setContentType("application/octet-stream");
            response.setHeader("contentType", "application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            try {
                response.flushBuffer();
                workbook.write(response.getOutputStream());
            } catch (IOException e) {
                e.printStackTrace();
                log.error("导出库存查询列表 error:",e);
            }
        }

FileUtils.java

package com.xxx.utils;
    
    import sun.misc.BASE64Encoder;
    
    import java.io.IOException;
    import java.net.URLEncoder;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    
    public  class FileUtils {
        /**
         *          * 下载文件时，针对不同浏览器，进行附件名的编码
         *          *
         *          * @param filename
         *          *            下载文件名
         *          * @param agent
         *          *            客户端浏览器
         *          * @return 编码后的下载附件名
         *          * @throws IOException
         *         
         */
        public static String encodeDownloadFilename(String filename, String agent) throws IOException{
            if(agent.contains("Firefox")) { // 火狐浏览器
                filename = "=?UTF-8?B?"+ new BASE64Encoder().encode(filename.getBytes("utf-8"))+ "?=";
            }else {// IE及其他浏览器
                filename = URLEncoder.encode(filename, "utf-8");
                filename = filename.replace("+"," ");
            }
            return filename;
        }
    
        public static String getFileNameWithDate(String namePrefix){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            return namePrefix+sdf.format(new Date())+ ".xls";
        }
    }

service:

public void exportList(HttpServletRequest request, QueryBoundReq query, HSSFWorkbook workbook) {
            //headers表示excel表中第一行的表头
            String[] headers = {"姓名", "电话",  "地址"};
            List<Map<Integer, String>> dataMapList = this.getCDataMapList(headers.length, query);
    
            ExportUtils.dealHSSFWorkbook(workbook, headers, "清点单", dataMapList,false);
    
        }
    
    
    private List<Map<Integer, String>> getCDataMapList(int length,  QueryBoundReq query) {
            checkInfoListReq.setPageNum(0);
            checkInfoListReq.setPageSize(Integer.MAX_VALUE);
          //查询数据库获得原始列表数据
            List<GetCheckInfoList> list = dataMapper.findInfoList(query);
            //在表中存放查询到的数据放入对应的列
            List<Map<Integer, String>> dataMapList = Lists.newArrayList();
            if (CollectionUtils.isNotEmpty(list)){
                list.forEach(vo->{
                    Map<Integer, String> map = Maps.newHashMap();
                    int columnNumber = 0;
                    map.put(columnNumber++, vo.getCheckNo());
                    map.put(columnNumber++, vo.getDescription());
                    map.put(columnNumber++, new SimpleDateFormat("yyyy-MM-dd").format(vo.getCheckTime()));
                    map.put(columnNumber++, vo.getCreatorName());
                    map.put(columnNumber++, vo.getStatus().status);
                    dataMapList.add(map);
                });
            }
            return dataMapList;
        }

ExportUtils.java

public class ExportUtils {
        /**
         *
         * @param workbook 工作簿
         * @param headers headers表示excel表中第一行的表头
         * @param sheetName sheet名称
         * @param dataMapList 表格数据
         * @param lastRowSetBold 最后一行是否加粗
         */
        public static void dealHSSFWorkbook(HSSFWorkbook workbook, String[] headers, String sheetName, List<Map<Integer,String>> dataMapList,boolean lastRowSetBold){
            HSSFSheet sheet = workbook.createSheet(sheetName);
    
            //新增数据行，并且设置单元格数据
            int rowNum = 1;
    
            HSSFRow row = sheet.createRow(0);
            //在excel表中添加表头
    
            //修改列宽
    //        sheet.setColumnWidth(1,1000*10);
    //        sheet.setColumnWidth(2,1000*10);
            sheet.setDefaultColumnWidth(17);
            // 定义单元格格式，添加单元格表样式，并添加到工作簿
            HSSFCellStyle headerCellStyle =getHeaderStyle(workbook);
            // 定义单元格格式，添加单元格表样式，并添加到工作簿
            HSSFCellStyle normalCellStyle =getNormalTextStyle(workbook);
    
            for(int i=0;i<headers.length;i++){
                HSSFCell cell = row.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellValue(text);
                cell.setCellStyle(headerCellStyle);
            }
    
            //在表中存放查询到的数据放入对应的列
            for ( int i = 0; i < dataMapList.size(); i++) {
                Map<Integer,String> columnMap=dataMapList.get(i);
                HSSFRow row1 = sheet.createRow(rowNum);
               final int rowNumber=i;
                columnMap.forEach((k,v)->{
                    if (columnMap.get(k)!=null) {
                        HSSFCell cell =row1.createCell(k);
                        cell.setCellValue(v);
                        if (lastRowSetBold
                                && rowNumber==dataMapList.size()-1){//最后一行加粗
                            cell.setCellStyle(headerCellStyle);
                        }else {
                            cell.setCellStyle(normalCellStyle);
                        }
                    }
                });
                rowNum++;
            }
        }
    
        public static HSSFCellStyle getHeaderStyle(HSSFWorkbook workbook){
            // 定义单元格格式，添加单元格表样式，并添加到工作簿
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            // 设置单元格水平对齐类型
            cellStyle.setAlignment(HorizontalAlignment.CENTER); // 指定单元格居中对齐
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 指定单元格垂直居中对齐
            cellStyle.setWrapText(true);// 指定单元格自动换行
            // 设置单元格字体
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            font.setFontName("微软雅黑");//字体
            font.setFontHeightInPoints((short)10);//设置字体
            cellStyle.setFont(font);
            return cellStyle;
        }
    
        public static HSSFCellStyle getNormalTextStyle(HSSFWorkbook workbook){
            // 定义单元格格式，添加单元格表样式，并添加到工作簿
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            // 设置单元格水平对齐类型
            cellStyle.setAlignment(HorizontalAlignment.CENTER); // 指定单元格居中对齐
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 指定单元格垂直居中对齐
            cellStyle.setWrapText(true);// 指定单元格自动换行
    //        // 设置单元格字体
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            font.setFontName("微软雅黑");//字体
            font.setFontHeightInPoints((short)10);//设置字体
            cellStyle.setFont(font);
            return cellStyle;
        }
    }

前端代码如下,点击导出时调用下面方法：

exportList(){
    let searchList= {        
            startDate: util.getFormatTime(new Date().getTime() - 7 * 24 * 60 * 60 * 1000, 'y-m-d'),
            endDate: util.getFormatTime(new Date().getTime(), 'y-m-d')        
          };
    window.location.href="http://localhost:8106/**/export/inventoryList"+"?"+this.serialize(args);
    
    }
    
     serialize(data){
              const queryList = Object.keys(data).map((key) => {
                  return key + '=' + encodeURIComponent(data[key]);
              });
              return queryList.join('&');
          },

**2、第二种方法参考了网上别人的经验**，这里也把代码贴出来

（1）后台大致同1里的export方法，但是response的contentType需要重新设置下，

response.setContentType("application/vnd.ms-excel;charset=utf-8");
    //        response.setHeader("contentType", "application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);

（2）前台

如果后台为get接口，如下传参调用接口：

主要是添加了responseType: 'arraybuffer'  和  使用了blob，blob设置的type和后台接口的一致：application/vnd.ms-excel;charset=utf-8

this.$swaggerApi.$get(this.exportUrl, {params: args,responseType: 'arraybuffer'}).then((res) => {
           
                 let blob = new Blob([res], {type: "application/vnd.ms-excel;charset=utf-8"});
                
               var link = document.createElement('a');
                 link.href = window.URL.createObjectURL(blob);
                link.download = "自己文档名字"+ util.getFormatTime(new Date(), 'y-m-d h:m:s');
            
                 link.click();
            
             }).catch((e) => this.$swaggerApi.$onError(e));

如果是后台为post接口，responseType的传法有点不一样： \{responseType: 'arraybuffer'\}，其他都是保持一致

this.$swaggerApi.$post(this.exportUrl, args, {responseType: 'arraybuffer'}).then((res) => {
                let blob = new Blob([res], {type: "application/vnd.ms-excel;charset=utf-8"});
                var link = document.createElement('a');
                link.href = window.URL.createObjectURL(blob);
                link.download = "xx列表" + util.getFormatTime(new Date(), 'y-m-d h:m:s');
                link.click();
            }).catch((e) => this.$swaggerApi.$onError(e))

***最近测试时，发现第二种方法没有考虑到异常的情况。由于前台请求时，responseType设置的是'arraybuffer'，在后台报异常的情况下，前台拿到的错误结果也会被转为二进制数据流，此时excel会下载成功，但是内容是报错信息。测试希望有错误时弹出具体的错误信息，因此需要想其他办法。***

如果返回的以下信息，excel里的内容则是以下内容：

\{    "code": 4000,    "message": "用户未登录",    "data": null\}

前端将拿到的数据流转成json,如果转json成功，有message字段则表示后台抛出异常，前端直接提示导出错误

this.$swaggerApi.$post(this.exportUrl, args, {responseType: 'arraybuffer'}).then((res) => {
            let reader = new FileReader()
            reader.onload = (evt) => {
              try {
                let data = JSON.parse(evt.target.result)
                if (data.message) {
                  this.tips(`导出失败: ${data.message}`, 'warning')
                }
              } catch (err) {
                let blob = new Blob([res], {type: "application/vnd.ms-excel;charset=utf-8"});
                var link = document.createElement('a');
                link.href = window.URL.createObjectURL(blob);
                link.download = "xx列表" + util.getFormatTime(new Date(), 'y-m-d h:m:s');
                link.click();
              }
    
            }).catch((e) => this.$swaggerApi.$onError(e))

3、**在第二种方法上优化**：

第2种方法，是自己先拿到所有的excel数据，然后将每一条数据的各个字段放进了map，key是当前列序号，value是当前列的值。这里需要往map put值时，自己根据key来控制各列的顺序。如下，从第一列开始往map里放每列的数据。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BxajIyMg_size_16_color_FFFFFF_t_70 1][]

现在来看看工具类已经实现了的一种方式，通过注解的方式来实现每列数据的顺序控制。

（1）jar包引入

<dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>4.0.0</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>4.0.0</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>4.0.0</version>
    </dependency>
    
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>${poi.version}</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>${poi.version}</version>
    </dependency>

（2）controller

@GetMapping(value = "export3")
       @ApiOperation(value = "导出列表") 
        public void export3(HttpServletRequest request, HttpServletResponse response) {
            iExportService.export3(request, response);
        }

(3)service

新增vo类，ExcelVO.java

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td> <p style="text-indent:33px;">属性名称</p> </td> 
   <td> <p style="text-indent:33px;">备注</p> </td> 
  </tr> 
  <tr> 
   <td> <p style="text-indent:33px;">name</p> </td> 
   <td> <p style="text-indent:33px;">导出excel列头名称</p> </td> 
  </tr> 
  <tr> 
   <td> <p style="text-indent:33px;">orderNum</p> </td> 
   <td> <p style="text-indent:33px;">列序号</p> </td> 
  </tr> 
  <tr> 
   <td>exportFormat</td> 
   <td>如果为Date型字段，导出时自动按照此字段format</td> 
  </tr> 
 </tbody> 
</table>

@Data
    public class ExcelVO {
        /**
         * 商品编码
         */
        @Excel(name = "商品编码", isWrap = false, width = 20, orderNum="1")
        private String skuCode;
        /**
         * 商品名称
         */
        @Excel(name = "商品名称", isWrap = false, width = 20, orderNum="2")
        private String skuName;
    
        @Excel(name = "开始时间", isWrap = false, width = 20, orderNum="3",exportFormat="yyyy-MM-dd HH:mm:ss")
        private Date beginTime; 
    
    }

service

public void export3(HttpServletRequest request,  HttpServletResponse response) {
           
    
            // 构建导出数据
            List<ExcelVo> list = Lists.newArrayList();
            ExcelVo vo1=new ExcelVo();
            vo1.setSkuCode("Code1111");
            vo1.setSkuName("测试名称");
            vo1.setBeginTime(new Date());
            ExcelVo vo2=new ExcelVo();
            vo2.setSkuCode("Code2222");
            vo2.setSkuName("这是一个哈哈哈");
            vo2.setBeginTime(new Date());
            list.add(vo1);
            list.add(vo2);
            // 导出数据流
            ExportUtils.downloadExcel(response,  list, ExcelVO.class);
        }

ExportUtils添加如下方法

/**
         * 下载
         *
         * @param response  HttpServletResponse
         * @param excelName excelName
         */
        public static void downloadExcel(HttpServletResponse response, String excelName, Object list, Class<?> pojoClass) {
            ExportParams params = new ExportParams();
            //此处设置ExcelType HSSF为excel2003版本，XSSF为excel2007版本
            params.setType(ExcelType.XSSF);
            params.setFixedTitle(true);
            params.setHeight((short)15);
            params.setStyle(ExcelExportStyleImpl.class);
            OutputStream os = null;
            Workbook workbook = null;
            try  {
                workbook = ExcelExportUtil.exportExcel(params, pojoClass, (Collection<?>) list);
    
                response.reset();
                if (excelName == null) {
                    excelName = UUID.randomUUID().toString();
                }
                response.setHeader("Content-Disposition", "attachment; filename=" + new String(excelName.getBytes("gb2312"), "ISO8859-1") + ".xlsx");
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setHeader("Pragma", "No-cache");
                response.setHeader("Cache-Control", "No-cache");
                response.setDateHeader("Expires", 0);
                response.flushBuffer();
                os = response.getOutputStream();
                
                workbook.write(os);
                os.flush();
            } catch (IOException e) {
                throw new Exception("导出异常");
            } finally {
                try {
                    if (os != null) {
                        os.close();
                    }
                    if (workbook != null) {
                        workbook.close();
                    }
                } catch (IOException e) {
                    log.error(e.getMessage(), e);
                }
            }
    
        }

ExcelExportStyleImpl.java

import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler;
    import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
    import org.apache.poi.ss.usermodel.*;
    
    /**
     * 导出Excel的style
     * 
     * 
     */
    public class ExcelExportStyleImpl extends AbstractExcelExportStyler
            implements IExcelExportStyler {
    
        public ExcelExportStyleImpl(Workbook workbook) {
            super.createStyles(workbook);
        }
    
        @Override
        public CellStyle getHeaderStyle(short color) {
            CellStyle titleStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) 12);
            titleStyle.setFont(font);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            return titleStyle;
        }
    
        @Override
        public CellStyle getTitleStyle(short color) {
            CellStyle titleStyle = workbook.createCellStyle();
            // 设置单元格字体
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setBold(true);
            font.setFontName("微软雅黑");//字体
            titleStyle.setFont(font);
            titleStyle.setAlignment(HorizontalAlignment.LEFT);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle.setWrapText(true);
            return titleStyle;
        }
    
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BxajIyMg_size_16_color_FFFFFF_t_70]: /images/20211104/852ee639274d4d7e96690f5144be4b5d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BxajIyMg_size_16_color_FFFFFF_t_70 1]: /images/20211104/374f5a4939e544c6a81363f44b3f0f8c.png