JavaWeb-POI realize excel form download

Posted May 27, 20206 min read

Page effect

image.png

  • Functional Overview:

    Select the column that needs to be exported to excel(you can also get some id) and then find the data in the background to generate excel, the user clicks export to Excel to download the excle file

Front page

<%@ 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 = "New TMC contract"
            cssClass = "nui-mainbtn" onclick = "constractGrid.create();" />
        <isperp:button id = "aaa" text = "Export to 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"> Number:</td>
                <td class = "td2"> <input type = "text" field = "code" op = "cn">
                    </td>
                <td class = "td1"> Title:</td>
                <td class = "td2"> <input type = "text" field = "title" op = "cn">
                    </td>
                <td class = "td1"> Categories:</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; \ "> special order number </div>" align = "center"> </isperp:column>

            <isperp:column name = "constract_code" title = "<div style = \" text-align:center; \ "> Contract Number </div>" width = "100" align = "center"> </isperp:column >

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

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



<script type = "text/javascript">

    function aaa() {
        //1. Get the data of the selected row(special menu 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);
        //Send Get request to download the excel file transferred from Java backend interface
        window.location = './constract/tmc/export/excel?' + bbb;
    } ~~~~
</script>
    1. This page uses the front-end framework package, ordinary
    1. window.location = url can send GET request

Java Controller

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. *;

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

    @Autowired
    protected TmcConstractService tmcConstractService;

    @Override
    public TmcConstractService getService() {
        return this.tmcConstractService;
    }

    @Override
    public String getPageTitle() {
        return "TMC contract";
    }

    @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", "Export succeeded!");

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

        AaaRes a2 = new AaaRes();
        a2.setId("111");
        a2.setName("People follow the crowd");

        AaaRes a1 = new AaaRes();
        a1.setId("111");
        a1.setName("again double chattering");
        ExcelModel <AaaRes> excelModel = new ExcelModel <>();

        List <String> ths = new ArrayList <String>();
        ths.add("Number");
        ths.add("Name");

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


        //TODO calls the Excel generation tool class and returns the stream data
        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;
    }

    //encapsulate request data
    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("People follow the crowd");

        AaaRes a1 = new AaaRes();
        a1.setId("111");
        a1.setName("again double chattering");
        ExcelModel <AaaRes> excelModel = new ExcelModel <>();

        List <String> ths = new ArrayList <String>();
        ths.add("Number");
        ths.add("Name");

        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 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 test
* @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;
    }
}

Postscript:First off work