Java实现数据库数据导出到Excel中并下载到浏览器本地

项目中很多都会用到将数据导出到Excel,然后对数据进行整理分析,在之前的项目中,多处用到此功能,

项目框架

1,后台:springboot+springmvc+mybatis

2,前台: bootstrap+jQury+ajax

说明.Excel处理函数需要引入hutool的jar包,在pom.xml引入一下代码。相信大家都会问到怎么不用poi、HSSFWorkbook、XSSFWorkbook、

tableExcel. 首先我要介绍下Hutool、他是对目前大多好用的工具类的一个封装。里面很好的继承了Poi及要用到的ExcelUtil。不像HSSFWorkbook、XSSFWorkbook需要写那么多行的代码。总之，很好用。

<dependency>
   <groupId>commons-io</groupId>
   <artifactId>commons-io</artifactId>
   <version>2.5</version>
</dependency>
<dependency>
   <groupId>cn.hutool</groupId>
   <artifactId>hutool-all</artifactId>
   <version>4.0.12</version>
</dependency>

在html中加上导出按钮、并在htlm中引入jsyemi：

<button id="export" type="button" class="btn btn-outline btn-sm btn-default"
        shiro:hasPermission="oms:order:export">
    <i class="fa fa-share-square-o"> 数据导出</i>
</button>

<script src="/oms/order/export.js"></script>

js页面如下：

/**
 * 订单数据导出
 */
$("#export").click(function () {

    var rows = $.getSelections("id");
    if (rows.length == 0) {
        $.modalMsg("请选择要导出的数据", "warning");
        return;
    }
    var index = $.modalConfirm("确认要导出这:" + rows.length + " 条数据吗?", function () {
        location.href = "/oms/operate/export?ids=" + JSON.stringify(rows);
    });
    layer.close(index);
});

创建一个实体类OrderExport.java 里面是要导出到Excel的字段。字段名可以随意点（见名知意原则），反正都是从数据库里面一个个set的。取对值就行了。这个代码就不用贴了吧。

接下来是Service层：

/**
 * 订单数据导出
 */
@Override
public byte[] exportOrderData(Long[] ids) throws Exception {
    ExcelWriter writer = ExcelUtil.getWriter(); // 通过工具类创建writer，默认创建xls格式
    Map<String, String> map = MapUtil.newHashMap(true);
    map.put("orderNo", "订单号");
    map.put("createTime", "创建时间");
    map.put("logisticsNo", "运单号");
    map.put("invtNo", "清单号");
    map.put("payNo", "支付单号");
    map.put("ebcCompanyName", "电商企业名称");
    map.put("companyName", "支付企业名称");
    map.put("buyerName", "订购人姓名");
    map.put("buyerIdNumber", "订购人证件号码");
    map.put("consigneeName", "收件人");
    map.put("consigneeAddress", "收件人地址");
    map.put("consigneePhone", "收件人电话");
    map.put("taxTotal", "代扣税款");
    map.put("acturalPaid", "订单支付金额"); 
    map.put("itemCode", "商品货号");
    map.put("qty", "商品申报数量");
    map.put("itemName", "商品名称");
    map.put("price", "商品申报单价");
    map.put("grossWeight", "订单毛重(Kg)");
    map.put("newStatus", "最新状态");
    writer.setHeaderAlias(map);
    List<OrderExport> exports = new ArrayList<>();// 一次性写出内容，使用默认样式
    for (int i = 0; i < ids.length; i++) {
        Order order = IOrderService.selectOrderById(ids[i]);
        CompanyRegisterinfo payInfo = companyRegisterInfoService.selectCompanyRegisterInfo(order.getPayOmsCode());
        CompanyRegisterinfo ebcCompanyName = companyRegisterInfoService.selectCompanyRegisterInfo(order.getEbcOmsCode());
        OrderStatus status = orderStatusService.selectStatusCode(order.getOrderNo(), order.getEbcOmsCode());
        List<OrderGoodsEntity> orderGoodsEntities = orderGoodsService.selectOrderGoodsByOrder(order);
        for (int j = 0; j < orderGoodsEntities.size(); j++) {
            OrderExport export = new OrderExport();
            export.setOrderNo(order.getOrderNo());
            export.setCreateTime(DateTime.parse(order.getCreateTime(), formatter).toString("yyyy:MM:dd HH:mm:ss"));
            export.setLogisticsNo(order.getLogisticsNo());
            export.setInvtNo(order.getInvtNo());
            export.setPayNo(order.getPayNo());
            export.setCompanyName(payInfo.getCompanyName());
            export.setEbcCompanyName(ebcCompanyName.getCompanyName());
            export.setBuyerName(order.getBuyerName());
            export.setBuyerIdNumber(order.getBuyerIdNumber());
            export.setConsigneeName(order.getConsigneeName());
            export.setConsigneeAddress(order.getConsigneeAddress());
            export.setConsigneePhone(order.getConsigneePhone());
            export.setTaxTotal(order.getTaxTotal());
            export.setActuralPaid(new BigDecimal(order.getActuralPaid() + ""));
            OrderGoodsEntity orderGoodsEntity = orderGoodsEntities.get(j);
            export.setItemCode(orderGoodsEntity.getItemCode());
            export.setQty(new BigDecimal(orderGoodsEntity.getQty()));
            export.setItemName(orderGoodsEntity.getItemName());
            export.setPrice(orderGoodsEntity.getPrice());
            export.setGrossWeight(new BigDecimal(order.getGrossWeight() + ""));
            export.setNewStatus(status.getStatusCode());
            exports.add(export);
        }

    }
    writer.write(exports);
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    writer.flush(out);
    // 关闭writer，释放内存
    writer.close();
    IOUtils.closeQuietly(out);
    return out.toByteArray();
}

从上面的代码大家会觉得不也写了很多行嘛、不是。仔细看大家会看到map里面放的是Excel中的表头和对应的字段、而后通过ExcelWriter对象writer.setHeaderAlias(map)把map放进去就行了。for循环就是我要通过id从数据库里面取到我想要放入Excel的值。最后就是关闭资源喽。

控制层Controller：

/**
 * 数据导出
 */
@ResponseBody
@RequestMapping("/export")
@RequiresPermissions("oms:order:export")
public void export(HttpServletResponse response, @RequestParam("ids") String ids) throws Exception {
    Object[] oArray = JSON.parseArray(ids).toArray();
    Long[] idArray = new Long[oArray.length];
    for (int i = 0; i < oArray.length; i++) {
        idArray[i] = Long.valueOf(oArray[i].toString());
    }
    byte[] data = orderOperateService.exportOrderData(idArray);
    response.reset();
    String fileName = new DateTime().toString("yyyyMMddHHmm") + "订单数据" + ".xls";
    response.setContentType("application/octet-stream; charset=UTF-8");
    response.setHeader("Content-Disposition", "attachment; fileName=" + fileName + ";filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8"));
    response.addHeader("Content-Length", "" + data.length);
    IOUtils.write(data, response.getOutputStream());
}

要注意对下载出来的Excel名字格式进行转码。注意看setHeader。忽略上面@RequiresPermissions注释、这是给的权限。

红色部分代码着重看一下。