JavaWebPOI实现excel表格下载

28次阅读

共计 6960 个字符，预计需要花费 18 分钟才能阅读完成。

功能概述:

选中要需要导出到 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 请求的

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");
    }

}

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);
    }
}

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;}
}

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;}
}

后记: 下班了先这样

正文完

发表至： javascript

2020-05-27

0

关于vue.js:简易VueRouter版源码实现

关于javascript:Javascript-获取下拉框选中元素的值

关于javascript:Node-地下铁武汉热干面专场来啦开启报名中

已购买极客时间前端课程

vue-手机触屏滑动

MAC安装向日葵软件允许他人远程协助