java中Excel导出前端查询数据

1、pom.xml引入jar包

  <!--Excel封装-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>xerces</groupId>
            <artifactId>xercesImpl</artifactId>
            <version>2.11.0</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>4.5.11</version>
        </dependency>

2、Controller中添加export方法

 /**
         * 导出excel
         * @param param
         * @throws Exception
         */

        @RequestMapping(value = "/export",method = RequestMethod.GET)
        @ResponseBody
        public void export( WsPurchase param, HttpServletResponse response){
                List<WsPurchase> list = wsPurchaseService.findAll(param);
                ExcelWriter writer = ExcelUtil.getWriter();
             //   Map<String, Object> row = new LinkedHashMap<>();
                List<Map<String, Object>> rows = new ArrayList<>();
                for (int i=0;i<list.size();i++) {
                    WsPurchase a = list.get(i);
                    Map<String, Object> row = new HashMap<>();
                    row.put("进货日期", a.getCredate());
                    row.put("供应商ID", a.getSupplyid());
                    row.put("供应商", a.getSupplyname());
                    row.put("业务类型", a.getOperationtype());
                    row.put("货品ID", a.getGoodsid());
                    row.put("货品名称", a.getGoodsname());
                    row.put("商品名", a.getCurrencyname());
                    row.put("规格", a.getGoodstype());
                    row.put("单位", a.getGoodsunit());
                    row.put("生产厂家", a.getFactoryname());
                    row.put("产地", a.getProdarea());
                    row.put("批号", a.getLotno());
                    row.put("数量", a.getGoodsqty());
                    row.put("单价", a.getUnitprice());
                    row.put("金额", a.getTotalLine());
                    row.put("有效期", a.getInvaliddate());
                    rows.add(row);
                }
                    writer.addHeaderAlias("credate", "进货日期");
                    writer.addHeaderAlias("supplyid", "供应商ID");
                    writer.addHeaderAlias("supplyname", "供应商");
                    writer.addHeaderAlias("operationtype", "业务类型");
                    writer.addHeaderAlias("goodsid", "货品ID");
                    writer.addHeaderAlias("goodsname", "货品名称");
                    writer.addHeaderAlias("currencyname", "商品名");
                    writer.addHeaderAlias("goodstype", "规格");
                    writer.addHeaderAlias("goodsunit", "单位");
                    writer.addHeaderAlias("factoryname", "生产厂家");
                    writer.addHeaderAlias("prodarea", "产地");
                    writer.addHeaderAlias("lotno", "批号");
                    writer.addHeaderAlias("goodsqty", "数量");
                    writer.addHeaderAlias("unitprice", "单价");
                    writer.addHeaderAlias("totalLine", "金额");
                    writer.addHeaderAlias("invaliddate", "有效期");
              //  List<Map<String, Object>> rows = CollUtil.newArrayList(row);
                   writer.write(rows,true);

                    response.setContentType("application/vnd.ms-excel;charset=utf-8");
                    String filename = Convert.toStr(System.currentTimeMillis())+".xls";
                    response.setHeader("Content-Disposition","attachment="+filename);

                try {
                    ServletOutputStream out = response.getOutputStream();
                    writer.flush(out,true);
                    writer.close();
                    IoUtil.close(out);
                } catch (IOException e) {
                    log.error(e.getMessage());
                    e.printStackTrace();

                }
            }

3、dao和service添加查询数据库方法

dao中代码

 List<WsPurchase> findAll(WsPurchase param);

service中代码

 public List<WsStorage> findAll(WsStorage param){
        return  dao.findAll(param);
    }

4、HTML中关联查询条件

前端请求后台参数
function excel(){
        var startTime=$("#startTime").val();
        var endTime=$("#endTime").val();
        var supplyid=$("#supplyid").val();
        var supplyname=$("#supplyname").val();
        var goodsid=$("#goodsid").val();
        var goodsname=$("#goodsname").val();
        var currencyname=$("#currencyname").val();
        var lotno=$("#lotno").val();
        window.location.href="purchase/export?startTime="+startTime+"&endTime="+endTime+"&supplyid="+supplyid+
            "&supplyname="+supplyname+"&goodsid="+goodsid+"&goodsname="+goodsname+"&currencyname="+currencyname+
            "&lotno="+lotno
    }
    
导出按钮
<td>  <a href="javascript:;" class="easyui-linkbutton" onclick="excel();"> 
导出Excel</a> 
</td>

5、Xml数据库查询语句

<!-- 查询列表 -->
  <select id="findAll" resultMap="BaseResultMap" parameterType="Map">
    select
    CREDATE, SUPPLYID, SUPPLYNAME,
    OPERATIONTYPE, GOODSID, GOODSNAME,
    CURRENCYNAME, GOODSTYPE, GOODSUNIT,
    FACTORYNAME, PRODAREA, GOODSQTY,
    LOTNO, INVALIDDATE, UNITPRICE,
    TOTAL_LINE
    from WS_PURCHASE_V a
    <include refid="Example_Where_Clause" />
  </select>

excel生成参考文档 Excel生成-ExcelWriter