JavaWebPOI实现excel表格下载

页面效果

功能概述:

选中要需要导出到excel的列(也可以是获取一些id)然后后台查找数据生成excel,用户点击导出到Excel就下载excle文件

前端页面

<%@ page pageEncoding="UTF-8"%>
<%@ taglib prefix="isperp" uri="/WEB-INF/tlds/isperp.tld"%>

<isperp:grid url="constract/tmc/listDetail" gridId="constractGrid"
    multiselect="true" multikey="true"    ondbClickRowEvent=""
    cusEditUrl="constract/tmc/edit">

    <jsp:attribute name="toolbar">
    <isperp:toolbar id="constractToolbar">
        <isperp:button id="constractCreate" icon="icon-plus" text="新增TMC合同"
            cssClass="nui-mainbtn" onclick="constractGrid.create();"/>
        <isperp:button id="aaa" text="导出到Excel" icon="icon-"
            cssClass="nui-mainbtn" onclick="aaa();"/>

    </isperp:toolbar>
    </jsp:attribute>

    <jsp:attribute name="searchbar">
    <table class="search-table">
        <tbody>
            <tr>
                <td class="td1">编号：</td>
                <td class="td2"><input type="text" field="code" op="cn">
                    </td>
                <td class="td1">标题：</td>
                <td class="td2"><input type="text" field="title" op="cn">
                    </td>
                <td class="td1">分类：</td>
                <td class="td2"><input type="text" field="changeType" op="cn">
                </td>
            </tr>
            
        </tbody>
    </table>
    </jsp:attribute>
    <jsp:body>
        <table id="grid-table">
            <isperp:column name="id" title="id" hidden="true"></isperp:column>

            <isperp:column name="tc_order_id" width="100" title="<div style=\"text-align:center;\">特采单号</div>"  align="center"></isperp:column>

            <isperp:column name="constract_code" title="<div style=\"text-align:center;\">合同编号</div>" width="100" align="center"></isperp:column>

            <isperp:column name="principal" title="<div style=\"text-align:center;\">担当</div>" width="100" align="center"></isperp:column>

            <isperp:column name="create_date" title="<div style=\"text-align:center;\">创建时间</div>" width="100" align="center"></isperp:column>
        </table>
    </jsp:body>
</isperp:grid>



<script type="text/javascript">

    function aaa() {
        // 1.获取选中行的数据(特菜单id)
        var ids = [];
        var selecteds = $("tr[aria-selected=true]");

        for(var i=0; i<selecteds.length; i++) {
            console.log(selecteds[i]);
            var tcOrderId = selecteds[i].children[3].getAttribute("title");
            console.log("id[" + i + "]=" + tcOrderId);
            ids.push(tcOrderId);
        };
        console.log(ids);
        var data = {
            "aaa": ids
        }
        var bbb = $.param(data, true)
        console.log('./constract/tmc/export/excel?' + bbb);
        // 发送Get请求,下载Java后端接口传输过来的excel文件
        window.location = './constract/tmc/export/excel?' + bbb;
    }~~~~
</script>

1.这个页面使用了前端框架的封装,普通的<button> 也是可以定义click事件
2.window.location = url是可以发送GET请求的

Java控制器

package itsm.isperp.module.controller.constract;

import com.fr.web.core.A.E;
import itsm.isperp.framework.web.controller.BaseController;
import itsm.isperp.module.entity.constract.TmcConstract;
import itsm.isperp.module.response.AaaRes;
import itsm.isperp.module.response.ExcelModel;
import itsm.isperp.module.service.constract.TmcConstractService;
import itsm.isperp.module.utils.ExcelUtils;
import org.apache.http.HttpRequest;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.InvocationTargetException;
import java.util.*;

/**
 * 控制器
 * 
 * @author lizx
 * @date 2020-05-18 16:45
 */
@Controller
@RequestMapping("constract/tmc")
public class TmcConstractController extends BaseController<TmcConstract> {

    @Autowired
    protected TmcConstractService tmcConstractService;

    @Override
    public TmcConstractService getService() {
        return this.tmcConstractService;
    }
    
    @Override
    public String getPageTitle() {
        return "TMC合同";
    }

    @RequestMapping(value = "export/excel", method = RequestMethod.GET)
    @ResponseBody
    public Map<String,String> exportExcel(@RequestParam(value="aaa") String ids[],
          HttpServletRequest request, HttpServletResponse response) throws NoSuchMethodException,
          IllegalAccessException, InvocationTargetException, IOException {

        Map<String,String> map= new HashMap<>();
        map.put("statusCode","200");
        map.put("message","导出成功！");

        String[] idss = request.getParameterValues("aaa");
        System.out.println(Arrays.toString(idss));

        AaaRes a2 = new AaaRes();
        a2.setId("111");
        a2.setName("人从众");

        AaaRes a1 = new AaaRes();
        a1.setId("111");
        a1.setName("又双叒叕");
        ExcelModel<AaaRes> excelModel = new ExcelModel<>();

        List<String> ths = new ArrayList<String>();
        ths.add("编号");
        ths.add("姓名");

        List<AaaRes> aaaRes = new ArrayList<>();
        aaaRes.add(a1);
        aaaRes.add(a2);
        excelModel.setThs(ths);
        excelModel.setTrs(aaaRes);


        // TODO 调用Excel生成工具类,返回流数据
        Workbook workbook = ExcelUtils.excelStream(excelModel);
        String fileName = "aaa"+System.currentTimeMillis()+".xlsx";
        setResponseHeader(response, fileName);
        OutputStream os = response.getOutputStream();
        workbook.write(os);

        os.flush();
        os.close();~~~~
        return map;
    }

    // 封装请求数据
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        response.setContentType("application/octet-stream;charset=ISO8859-1");
        response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
    }

}

ExcelUtil

package utils;

import itsm.isperp.module.response.AaaRes;
import itsm.isperp.module.response.ExcelModel;
import itsm.isperp.module.utils.ExcelUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;

import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description
 * @Project isperp-itss
 * @Author ZhiYue
 * @Date 2020/5/27 13:27
 */
public class ExcelUtilTest {

    @Test
    public void test() throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException {
        AaaRes a2 = new AaaRes();
        a2.setId("111");
        a2.setName("人从众");

        AaaRes a1 = new AaaRes();
        a1.setId("111");
        a1.setName("又双叒叕");
        ExcelModel<AaaRes> excelModel = new ExcelModel<>();

        List<String> ths = new ArrayList<String>();
        ths.add("编号");
        ths.add("姓名");

        List<AaaRes> aaaRes = new ArrayList<>();
        aaaRes.add(a1);
        aaaRes.add(a2);
        excelModel.setThs(ths);
        excelModel.setTrs(aaaRes);

        Workbook workbook = ExcelUtils.excelStream(excelModel);
    }
}

ExcelModel

package itsm.isperp.module.response;

import itsm.isperp.module.base.Model;

import java.util.List;

/**
 * @Description
 * @Project isperp-itss
 * @Author ZhiYue
 * @Date 2020/5/26 17:39
 */
public class ExcelModel<T> extends Model {

    private Integer columnWidth;

    private Integer columnHeight;

    private List<String> ths;

    private List<T> trs;

    public List<T> getTrs() {
        return trs;
    }

    public void setTrs(List<T> trs) {
        this.trs = trs;
    }

    public List<String> getThs() {
        return ths;
    }

    public void setThs(List<String> ths) {
        this.ths = ths;
    }

    public Integer getColumnWidth() {
        return columnWidth;
    }

    public void setColumnWidth(Integer columnWidth) {
        this.columnWidth = columnWidth;
    }

    public Integer getColumnHeight() {
        return columnHeight;
    }

    public void setColumnHeight(Integer columnHeight) {
        this.columnHeight = columnHeight;
    }
}

AaaRes

package itsm.isperp.module.response;

/**
 * @Description 测试
 * @Project isperp-itss
 * @Author ZhiYue
 * @Date 2020/5/26 17:48
 */
public class AaaRes extends ExcelModel{

    private String id;

    private String name;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

后记: 下班了先这样

JavaWebPOI实现excel表格下载

页面效果

前端页面

Java控制器

ExcelUtil

ExcelModel

AaaRes

评论

发表回复取消回复

更多文章

DDN HPC 存储硬件架构设计深度分析

探秘IO500：从Lustre并行文件系统出发，开启HPC存储性能新征程

苹果iOS打包的ipa应用无法安装？一篇文章带你了解可能的原因及排查方法

图解Golang：从零开始实现简易版过期LRU缓存

JavaWebPOI实现excel表格下载

页面效果

前端页面

Java控制器

ExcelUtil

ExcelModel

AaaRes

评论

发表回复 取消回复

更多文章

DDN HPC 存储硬件架构设计深度分析

探秘IO500：从Lustre并行文件系统出发，开启HPC存储性能新征程

苹果iOS打包的ipa应用无法安装？一篇文章带你了解可能的原因及排查方法

图解Golang：从零开始实现简易版过期LRU缓存

发表回复取消回复