#导出excel单据

zjj

2023-11-03 8f527cfc93518f5db3b90f31f78a358d79bcbb51

#导出excel单据

 src/main/java/com/zy/asrs/controller/OrderController.java

@@ -1,6 +1,10 @@
package com.zy.asrs.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;

import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
@@ -12,6 +16,7 @@
import com.sun.org.apache.xpath.internal.operations.Or;
import com.zy.asrs.entity.*;
import com.zy.asrs.entity.param.OrderDomainParam;
import com.zy.asrs.entity.result.LocDetlAll;
import com.zy.asrs.entity.result.OrderDetlVo;
import com.zy.asrs.service.*;
import com.zy.common.CodeRes;
@@ -27,7 +32,9 @@
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;
@@ -52,6 +59,78 @@
    @Autowired
    private OrderLogService orderLogService;

    @RequestMapping(value = "/order/all")
    @ManagerAuth
    public R statis2(@RequestParam(defaultValue = "1")Integer curr,
                     @RequestParam(defaultValue = "10")Integer limit,
                     @RequestParam Map<String, Object> param) {



        Page<Order> stockStatis = orderService.getOrderAll(toPage(curr, limit, param, Order.class));

        return R.ok().add(stockStatis);
    }
//    @RequestMapping(value = "/order/export")
//    @ManagerAuth
//    public void orderExport(HttpServletResponse response,OrderExport orderExport) throws IOException {
//        List<OrderDetl> orderNo = orderDetlService.selectList(new EntityWrapper<OrderDetl>().eq("order_no", orderExport.getOrderNo()));
//
//        response.setContentType("application/vnd.ms-excel");
//        response.setCharacterEncoding("utf-8");
//        String templateFileName =
//                this.getClass().getClassLoader().getResource("ru.xlsx").getPath();;
//
//        String fileName =  "complexFill" + System.currentTimeMillis() + ".xlsx";
//
//        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
//        ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
//        WriteSheet writeSheet = EasyExcel.writerSheet().build();
//        // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
//        // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
//        // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
//        // 如果数据量大 list不是最后一行 参照下一个
//        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
//        excelWriter.fill(orderNo, fillConfig, writeSheet);
////        excelWriter.fill(orderNo, fillConfig, writeSheet);
//        Map<String, Object> map = new HashMap<String, Object>();
//        map.put("date", "2019年10月9日13:28:28");
//        map.put("total", 1000);
//        excelWriter.fill(map, writeSheet);
//        excelWriter.finish();
//
//
//    }

    @RequestMapping("/download_excel")
    public void downloadExcel(HttpServletResponse response,@RequestParam String orderNo) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = "Order " + orderNo + ".xlsx";
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);

        OutputStream outputStream = response.getOutputStream();
        orderExport(outputStream, orderNo);
        outputStream.flush();
        outputStream.close();
    }

    public void orderExport(OutputStream outputStream, String orderNo) throws IOException {
        List<OrderDetl> orderDetls = orderDetlService.selectList(new EntityWrapper<OrderDetl>().eq("order_no", orderNo));

        String templateFileName = this.getClass().getClassLoader().getResource("ru.xlsx").getPath();

        ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(templateFileName).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        excelWriter.fill(orderDetls, fillConfig, writeSheet);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("date", "2019年10月9日13:28:28");
        map.put("orderNo", orderNo);
        excelWriter.fill(map, writeSheet);
        excelWriter.finish();
    }

    @RequestMapping(value = "/order/nav/list/auth")
    @ManagerAuth
    public R navList(@RequestParam(required = false) String orderNo){

 src/main/java/com/zy/asrs/entity/FillData.java

New file
@@ -0,0 +1,14 @@
package com.zy.asrs.entity;

import lombok.Data;
import lombok.EqualsAndHashCode;

@Data
@EqualsAndHashCode
public class FillData {
    private String maktx;
    private String model;
    private String speas;
    private Double anfme;

}

 src/main/java/com/zy/asrs/entity/OrderExport.java

New file
@@ -0,0 +1,10 @@
package com.zy.asrs.entity;

import lombok.Data;

@Data
public class OrderExport {
    private String orderNo;
    private Integer docType;
    private  String memo;
}

 src/main/java/com/zy/asrs/mapper/OrderMapper.java

@@ -8,6 +8,7 @@
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Mapper
@Repository
@@ -28,4 +29,8 @@
    List<Order> selectToBeHistoryOrder(@Param("settle")int settle);

    List<OrderListDTO> selectAllorderNo();

    List<Order> getOrderAll(Map<String, Object> condition);

    long getOrderAllCount(Map<String, Object> condition);
}

 src/main/java/com/zy/asrs/service/OrderService.java

@@ -1,5 +1,6 @@
package com.zy.asrs.service;

import com.baomidou.mybatisplus.plugins.Page;
import com.zy.asrs.entity.*;
import com.baomidou.mybatisplus.service.IService;

@@ -32,4 +33,6 @@
    List<Order> selectToBeHistoryOrder(boolean isERP);

    List<OrderListDTO> selectAllorderNo();

    Page<Order> getOrderAll(Page<Order> page);
}

 src/main/java/com/zy/asrs/service/impl/OrderServiceImpl.java

@@ -1,6 +1,7 @@
package com.zy.asrs.service.impl;

import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.plugins.Page;
import com.core.common.Cools;
import com.core.common.DateUtils;
import com.core.common.SnowflakeIdWorker;
@@ -214,4 +215,10 @@
        return this.baseMapper.selectAllorderNo();
    }

    @Override
    public Page<Order> getOrderAll(Page<Order> page) {
        page.setRecords(baseMapper.getOrderAll(page.getCondition()));
        page.setTotal(baseMapper.getOrderAllCount(page.getCondition()));
        return page;
    }
}

 src/main/resources/mapper/OrderMapper.xml

@@ -81,6 +81,37 @@
    <select id="selectAllorderNo" resultType="com.zy.asrs.entity.OrderListDTO">
        select order_no from man_order
    </select>
    <sql id="orderSql">
        <if test="order_no != null and order_no!='' ">
            and a.order_no like '%' + #{order_no} + '%'
        </if>
    </sql>
    <select id="getOrderAll" resultMap="BaseResultMap">
        select t.row,order_all.* from
            (
            select
            ROW_NUMBER() over (order by sum(1) desc) as row,id
            FROM order_all
            WHERE 1=1
            <include refid="orderSql"></include>
            GROUP BY id
            ) t
            LEFT JOIN order_all ON t.id = order_all.id
            where t.row between ((#{pageNumber}-1)*#{pageSize}+1) and (#{pageNumber}*#{pageSize})
    </select>
    <select id="getOrderAllCount" resultType="java.lang.Long">
        select SUM(1) from
            (
                select
                    ROW_NUMBER() over (order by sum(1) desc) as row,
                        id
                FROM order_all
                WHERE 1=1
                <include refid="orderSql"></include>
                GROUP BY id
            ) t
                LEFT JOIN order_all ON t.id = order_all.id
    </select>


    <insert id="addToLogTable">

 src/main/resources/ru.xlsx

Binary files differ

 src/main/webapp/static/js/orderExport/orderExport.js

New file
@@ -0,0 +1,265 @@
var pageCurr;
layui.config({
    base: baseUrl + "/static/layui/lay/modules/"
}).use(['table','laydate', 'form', 'admin'], function(){
    var table = layui.table;
    var $ = layui.jquery;
    var layer = layui.layer;
    var layDate = layui.laydate;
    var form = layui.form;
    var admin = layui.admin;

    // 数据渲染
    tableIns = table.render({
        elem: '#orderSettle',
        headers: {token: localStorage.getItem('token')},
        url: baseUrl+'/order/all',
        page: true,
        limit: 15,
        limits: [15, 30, 50, 100, 200, 500],
        toolbar: '#toolbar',
        cellMinWidth: 50,
        height: 'full-120',
        cols: [[

            {field: 'orderNo', align: 'center',title: '单据号'}
            ,{field: 'docType$', align: 'center',title: '单据类型'}
            // ,{field: 'createBy$', align: 'center',title: '添加人员'}
            // ,{field: 'createTime$', align: 'center',title: '添加时间'}


            ,{field: 'settle$', align: 'center', title: '状态', templet: '#settleTpl',  minWidth: 160}
            ,{fixed: 'right', title:'操作', align: 'center', toolbar: '#operate', width:120}
        ]],
        request: {
            pageName: 'curr',
            pageSize: 'limit'
        },
        parseData: function (res) {
            return {
                'code': res.code,
                'msg': res.msg,
                'count': res.data.total,
                'data': res.data.records
            }
        },
        response: {
            statusCode: 200
        },
        done: function(res, curr, count) {
            if (res.code === 403) {
                top.location.href = baseUrl+"/";
            }
            pageCurr=curr;
            limit();
        }
    });

    // 监听排序事件
    table.on('sort(orderSettle)', function (obj) {
        var searchData = {};
        $.each($('#search-box [name]').serializeArray(), function() {
            searchData[this.name] = this.value;
        });
        searchData['orderByField'] = obj.field;
        searchData['orderByType'] = obj.type;
        tableIns.reload({
            where: searchData,
            page: {curr: 1}
        });
    });

    // 监听头工具栏事件
    table.on('toolbar(orderSettle)', function (obj) {
        var checkStatus = table.checkStatus(obj.config.id).data;
        switch(obj.event) {
            case 'addData':
                showEditModel();
                break;
            case 'deleteData':
                if (checkStatus.length === 0) {
                    layer.msg('请选择要删除的数据', {icon: 2});
                    return;
                }
                del(checkStatus.map(function (d) {
                    return d.orderSettle;
                }));
                break;
            case 'exportData':
                admin.confirm('确定导出Excel吗', {shadeClose: true}, function(){
                    var titles=[];
                    var fields=[];
                    obj.config.cols[0].map(function (col) {
                        if (col.type === 'normal' && col.hide === false && col.toolbar == null) {
                            titles.push(col.title);
                            fields.push(col.field);
                        }
                    });
                    var exportData = {};
                    $.each($('#search-box [name]').serializeArray(), function() {
                        exportData[this.name] = this.value;
                    });
                    var param = {
                        'orderSettle': exportData,
                        'fields': fields
                    };
                    $.ajax({
                        url: baseUrl+"/orderSettle/export/auth",
                        headers: {'token': localStorage.getItem('token')},
                        data: JSON.stringify(param),
                        dataType:'json',
                        contentType:'application/json;charset=UTF-8',
                        method: 'POST',
                        success: function (res) {
                            layer.closeAll();
                            if (res.code === 200) {
                                table.exportFile(titles,res.data,'xls');
                            } else if (res.code === 403) {
                                top.location.href = baseUrl+"/";
                            } else {
                                layer.msg(res.msg, {icon: 2})
                            }
                        }
                    });
                });
                break;
        }
    });

    // 监听行工具事件
    table.on('tool(orderSettle)', function(obj){
        var data = obj.data;
        switch (obj.event) {
            case 'edit':
                showEditModel(data);
                break;
            case "del":
                del([data.orderSettle]);
                break;
        }
    });

    /* 弹窗 - 新增、修改 */
    function showEditModel(mData) {
        admin.open({
            type: 1,
            area: '600px',
            title: '导出单据',
            content: $('#editDialog').html(),
            success: function (layero, dIndex) {
                layDateRender(mData);
                form.val('detail', mData);
                form.on('submit(editSubmit)', function (data) {

                    layer.confirm('确定导出Excel吗', {shadeClose: true}, function(){
                        layer.closeAll();
                        layer.load(1, {shade: [0.1,'#fff']});
                        // location.href = "http://192.168.4.121:8082/stwms/orderCheck/printExport/auth?orderNo="+data.orderNo;
                        location.href = baseUrl+"/download_excel?orderNo="+data.field.orderNo;
                        layer.closeAll('loading');
                    });


                    // var loadIndex = layer.load(2);
                    // $.ajax({
                    //     url: baseUrl+"/download_excel",
                    //     headers: {'token': localStorage.getItem('token')},
                    //     data: data.field,
                    //     method: 'POST',
                    //     success: function (res) {
                    //         layer.close(loadIndex);
                    //         if (res.code === 200){
                    //             layer.close(dIndex);
                    //             layer.msg(res.msg, {icon: 1});
                    //             tableReload();
                    //         } else if (res.code === 403){
                    //             top.location.href = baseUrl+"/";
                    //         }else {
                    //             layer.msg(res.msg, {icon: 2});
                    //         }
                    //     }
                    // })
                    return false;
                });
                $(layero).children('.layui-layer-content').css('overflow', 'visible');
                layui.form.render('select');
            }
        });
    }

    /* 删除 */
    function del(ids) {
        layer.confirm('确定要删除选中数据吗？', {
            skin: 'layui-layer-admin',
            shade: .1
        }, function (i) {
            layer.close(i);
            var loadIndex = layer.load(2);
            $.ajax({
                url: baseUrl+"/orderSettle/delete/auth",
                headers: {'token': localStorage.getItem('token')},
                data: {ids: ids},
                method: 'POST',
                success: function (res) {
                    layer.close(loadIndex);
                    if (res.code === 200){
                        layer.msg(res.msg, {icon: 1});
                        tableReload();
                    } else if (res.code === 403){
                        top.location.href = baseUrl+"/";
                    } else {
                        layer.msg(res.msg, {icon: 2});
                    }
                }
            })
        });
    }

    // 搜索
    form.on('submit(search)', function (data) {
        pageCurr = 1;
        tableReload(false);
    });

    // 重置
    form.on('submit(reset)', function (data) {
        pageCurr = 1;
        clearFormVal($('#search-box'));
        tableReload(false);
    });

    // 时间选择器
    function layDateRender(data) {
        setTimeout(function () {
            layDate.render({
                elem: '#createTime\\$',
                type: 'datetime',
                value: data!==undefined?data['createTime\\$']:null
            });
            layDate.render({
                elem: '#updateTime\\$',
                type: 'datetime',
                value: data!==undefined?data['updateTime\\$']:null
            });

        }, 300);
    }
    layDateRender();

});

// 关闭动作
$(document).on('click','#data-detail-close', function () {
    parent.layer.closeAll();
});

function tableReload(child) {
    var searchData = {};
    $.each($('#search-box [name]').serializeArray(), function() {
        searchData[this.name] = this.value;
    });
    tableIns.reload({
        where: searchData,
        page: {curr: pageCurr}
    });
}

 src/main/webapp/views/orderExport/orderExport.html

New file
@@ -0,0 +1,112 @@
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title></title>
  <meta name="renderer" content="webkit">
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <link rel="stylesheet" href="../../static/layui/css/layui.css" media="all">
  <link rel="stylesheet" href="../../static/css/admin.css?v=318" media="all">
  <link rel="stylesheet" href="../../static/css/cool.css" media="all">
</head>
<body>

<div class="layui-fluid">
  <div class="layui-card">
    <div class="layui-card-body">
      <div class="layui-form toolbar" id="search-box">
        <div class="layui-form-item">
          <div class="layui-inline">
            <label class="layui-form-label">状态描述:</label>
            <div class="layui-input-inline">
              <input class="layui-input" type="text" name="order_name" placeholder="状态描述" autocomplete="off">
            </div>
          </div>
          <div class="layui-inline">&emsp;
            <button class="layui-btn icon-btn" lay-filter="search" lay-submit>
              <i class="layui-icon">&#xe615;</i>搜索
            </button>
            <button class="layui-btn icon-btn" lay-filter="reset" lay-submit>
              <i class="layui-icon">&#xe666;</i>重置
            </button>
          </div>
        </div>
      </div>
      <table class="layui-hide" id="orderSettle" lay-filter="orderSettle"></table>
    </div>
  </div>
</div>

<script type="text/html" id="toolbar">
  <div class="layui-btn-container">
<!--    <button class="layui-btn layui-btn-sm" id="btn-add" lay-event="addData">新增</button>-->
<!--    <button class="layui-btn layui-btn-sm layui-btn-danger" id="btn-delete" lay-event="deleteData">删除</button>-->
<!--    <button class="layui-btn layui-btn-primary layui-btn-sm" id="btn-export" lay-event="exportData" style="float: right">导出</button>-->
  </div>
</script>
<script type="text/html" id="settleTpl">
  <span name="settle"
        {{# if( d.settle === 1){ }}
        class="layui-badge layui-badge-red"
        {{# }else if(d.settle === 2){ }}
        class="layui-badge layui-badge-green"
        {{# }else if(d.settle === 3){ }}
        class="layui-badge layui-badge-gray"
        {{# }else if(d.settle === 4){ }}
        class="layui-badge layui-badge-blue"
        {{# }else if(d.settle === 5){ }}
        class="layui-badge layui-badge-gray"
        {{# }else if(d.settle === 6){ }}
        class="layui-badge layui-badge-gray"
        {{# } }}
  >{{d.settle$}}</span>
</script>

<script type="text/html" id="operate">
  <a class="layui-btn layui-btn-primary layui-btn-xs btn-edit" lay-event="edit">导出单据</a>
<!--  <a class="layui-btn layui-btn-danger layui-btn-xs btn-edit" lay-event="del">删除</a>-->
</script>

<script type="text/javascript" src="../../static/js/jquery/jquery-3.3.1.min.js"></script>
<script type="text/javascript" src="../../static/layui/layui.js" charset="utf-8"></script>
<script type="text/javascript" src="../../static/js/common.js" charset="utf-8"></script>
<script type="text/javascript" src="../../static/js/cool.js" charset="utf-8"></script>
<script type="text/javascript" src="../../static/js/orderExport/orderExport.js" charset="utf-8"></script>
</body>
<!-- 表单弹窗 -->
<script type="text/html" id="editDialog">
  <form id="detail" lay-filter="detail" class="layui-form admin-form model-form">
    <input name="id" type="hidden">
    <div class="layui-row">
      <div class="layui-col-md12">
        <div class="layui-form-item">
          <label class="layui-form-label layui-form-required">单据号: </label>
          <div class="layui-input-block">
            <input class="layui-input" name="orderNo" placeholder="请输入单据号" lay-vertype="tips" lay-verify="required">
          </div>
        </div>
        <div class="layui-form-item">
          <label class="layui-form-label">单据类型: </label>
          <div class="layui-input-block">
            <input class="layui-input" name="docType" placeholder="请输入状态描述">
          </div>
        </div>
        <div class="layui-form-item">
          <label class="layui-form-label">备注: </label>
          <div class="layui-input-block">
            <input class="layui-input" name="memo" placeholder="请输入备注">
          </div>
        </div>

      </div>
    </div>
    <hr class="layui-bg-gray">
    <div class="layui-form-item text-right">
      <button class="layui-btn" lay-filter="editSubmit" lay-submit="">保存</button>
      <button class="layui-btn layui-btn-primary" type="button" ew-event="closeDialog">取消</button>
    </div>
  </form>
</script>
</html>