Springboot实现前后端分离excel下载

springboot前后端分离excel下载

现在公司的技术栈是springboot作为后端，前端是vue， 现在要做excel的导出功能， 之前没做过，写一下记录下.

springboot版本是2.0.6 poi 3.14 ,jdk1.8

类上面的注解是： @restcontroller

/**
     * 导出excel
     * 
     */
    @getmapping("export")
    public void exportexcel() {
        xssfworkbook workbook = placestatservice.exportexcel();
        // 设置生成的excel的文件名，并以中文进行编码
        string filename = null;
        try {
            filename = urlencoder.encode("房间预约使用统计表" + ".xlsx", "utf-8").replaceall("\\+", "%20");
        } catch (unsupportedencodingexception e) {
            e.printstacktrace();
        }
        response.setcharacterencoding("utf-8");
        response.setheader("content-type", "application/vnd.ms-excel");
        response.setheader("content-disposition", "attachment;filename=" + filename);
        // 响应类型,编码
        response.setcontenttype("application/octet-stream;charset=utf-8");
        try {
            // 形成输出流
            outputstream osout = response.getoutputstream();
            // 将指定的字节写入此输出流
            workbook.write(osout);
            // 刷新此输出流并强制将所有缓冲的输出字节被写出
            osout.flush();
            // 关闭流
            osout.close();
            workbook.close();
        } catch (ioexception e) {
            e.printstacktrace();
        }
    }

@override
    public xssfworkbook exportexcel) {
        list<roomorderdetailmodel> roomorderslist = getroomorderlist();
        xssfworkbook data = excelutil.setexceldata(roomorderslist);
        return data;
    }
 

package com.util; 
import com.curefun.place.model.roomorderdetailmodel;
import org.apache.poi.ss.usermodel.horizontalalignment;
import org.apache.poi.ss.usermodel.verticalalignment;
import org.apache.poi.xssf.usermodel.xssfcell;
import org.apache.poi.xssf.usermodel.xssfcellstyle;
import org.apache.poi.xssf.usermodel.xssfrow;
import org.apache.poi.xssf.usermodel.xssfsheet;
import org.apache.poi.xssf.usermodel.xssfworkbook; 
import java.text.simpledateformat;
import java.util.date;
import java.util.list;
 
/**
 * @author excel 工具类. 导出功能
 */
public class excelutil { 
 
    /**
     * 数据导出, 获取一个excel对象
     *
     * @param
     */
    public static xssfworkbook setexceldata(list<roomorderdetailmodel> orderdetailmodels) {
        //创建一个book，对应一个excel文件
        xssfworkbook workbook = new xssfworkbook();
        //在book中添加一个sheet,对应excel文件中的sheet
        xssfsheet sheet = workbook.createsheet("教室预约使用记录");
        //设置六列的宽度
        sheet.setcolumnwidth(0, 4000);
        sheet.setcolumnwidth(1, 3000);
        sheet.setcolumnwidth(2, 3800);
        sheet.setcolumnwidth(3, 2800);
        sheet.setcolumnwidth(4, 3200);
        sheet.setcolumnwidth(5, 3600);
        sheet.setcolumnwidth(6, 2850);
 
        //居中的样式
        xssfcellstyle centerstyle = getcenterstyle(workbook);
 
        // 第三步，在sheet中添加表头第0行
        xssfrow row0 = sheet.createrow(0);
        setfirstrow(centerstyle, row0);
 
        int rownum = 1;
        for (roomorderdetailmodel model : orderdetailmodels) {
            xssfrow row = sheet.createrow(rownum);
            row.createcell(0).setcellvalue(rownum);
            rownum++;
            row.createcell(1).setcellvalue(model.getbuildingname());
            row.createcell(2).setcellvalue(model.getroomno());
            row.createcell(3).setcellvalue(model.getroomname());
            row.createcell(4).setcellvalue(model.geteventtype());
            row.createcell(5).setcellvalue(model.geteventname());
            row.createcell(6).setcellvalue(model.getuserrealname());
        }
        return workbook;
    }  
 
    /**
     * 获取居中的样式.
     *
     * @param workbook
     * @return
     */
    private static xssfcellstyle getcenterstyle(xssfworkbook workbook) {
        xssfcellstyle cellstyle = workbook.createcellstyle();
        //设置水平对齐的样式为居中对齐;
        cellstyle.setalignment(horizontalalignment.center);
        //垂直居中
        cellstyle.setverticalalignment(verticalalignment.center);
        return cellstyle;
    } 
 
    /**
     * 设置第一行的表头
     *
     * @param centerstyle
     * @param row
     */
    private static void setfirstrow(xssfcellstyle centerstyle, xssfrow row) {
        xssfcell cell0 = row.createcell(0);
        cell0.setcellvalue("序号");
        cell0.setcellstyle(centerstyle);
        xssfcell cell1 = row.createcell(1);
        cell1.setcellvalue("楼栋信息");
        cell1.setcellstyle(centerstyle);
        xssfcell cell2 = row.createcell(2);
        cell2.setcellvalue("房号");
        cell2.setcellstyle(centerstyle);
        xssfcell cell3 = row.createcell(3);
        cell3.setcellvalue("房间名称");
        cell3.setcellstyle(centerstyle);
        xssfcell cell4 = row.createcell(4);
        cell4.setcellvalue("活动类型");
        cell4.setcellstyle(centerstyle);
        xssfcell cell5 = row.createcell(5);
        cell5.setcellvalue("活动名称");
        cell5.setcellstyle(centerstyle);
        xssfcell cell6 = row.createcell(6);
        cell6.setcellvalue("使用人");
        cell6.setcellstyle(centerstyle); 
/**
其实完全使用这种方式, 会更加的简单,便于修改
list<string> title = stream.of("序号", "专业", "班级", "课程名称", "课程内容", "授课教师", "授课时长", "授课时间", "学分", "授课房间")
                .collect(collectors.tolist());
        for (int i = 0; i < title.size(); i++) {
            xssfcell cell = row.createcell(i);
            cell.setcellvalue(title.get(i));
            cell.setcellstyle(centerstyle);
        }
*/
    } 
}

其实使用很简单，就是excel的文件名需要进行编码，这个需要注意，其他没啥的了.

前后端分离excle下载乱码问题

前端：vue+elementui

后端：springcloud

前端请求方式 : ajax请求

this.$.ajax({
                url :this.url + "/",
                type : 'post',
                data : formdata,
                contenttype: false,
                processdata: false,
                xhrfields: {withcredentials: true, responsetype:'arraybuffer'},
                headers : {'access-control-allow-origin': '*', "authorization": this.ajaxrequest.gettoken()},
                success: function (res, textstatus, request) {
                    var downloadfile = document.createelement('a');
                    let blob = new blob([res], {type : "application/vnd.ms-excel;charset=utf-8"});
                    downloadfile.href = window.url.createobjecturl(blob);
                    console.log(request.getresponseheader('content-disposition'));
                    downloadfile.download = 
                    decodeuri(request.getresponseheader('content-disposition').split('filename=')[1] );
                    downloadfile.click();
                    window.url.revokeobjecturl(downloadfile.href);
                },
                error : function (res) {
                  console.log(res)
                }
        })

后端处理：导出文件处理

// 输出excel文件
outputstream out = null;
out = response.getoutputstream();
response.reset();
response.setcharacterencoding("utf-8");
response.setheader("content-disposition", "attachment;filename=" + urlencoder.encode(filename, "utf8"));
response.setcontenttype("application/vnd.ms-excel; charset=utf-8");
// 输出excel内容，生成excel文件
wb.write(out);

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。