#导出

luxiaotao1123

2020-06-06 1c3c68d3dc45e4d519088de17001284858de5eb9

#导出

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

@@ -101,18 +101,10 @@

    // 导出
    @RequestMapping(value = "/matCode/export/auth")
    @ManagerAuth
    public void export(@RequestBody JSONObject param, HttpServletResponse response) throws Exception{
//        List<String> fields = JSONObject.parseArray(param.getJSONArray("fields").toJSONString(), String.class);
//        EntityWrapper<MatCode> wrapper = new EntityWrapper<>();
//        Map<String, Object> map = excludeTrash(param.getJSONObject("matCode"));
//        convert(map, wrapper);
//        List<MatCode> list = matCodeService.selectList(wrapper);


    public void export(@RequestParam(required = false) String fileName,
                       @RequestParam(required = false) Integer rowCount,
                       HttpServletResponse response) throws Exception {
        List<MatCode> list = matCodeService.selectList(new EntityWrapper<>());


        List<MatCodeExcel> excels = new ArrayList<>();
        for (MatCode matCode : list) {
            MatCodeExcel excel = new MatCodeExcel();
@@ -122,12 +114,15 @@
            excel.setStr1(matCode.getStr1());
            excel.setStr2(matCode.getStr2());
            excels.add(excel);
            if (rowCount != null && excels.size() >= rowCount){
                break;
            }
        }
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("测试", "UTF-8");
        fileName = URLEncoder.encode(Cools.isEmpty(fileName)?"物料编码":fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), MatCodeExcel.class).sheet("模板").doWrite(excels);
        EasyExcel.write(response.getOutputStream(), MatCodeExcel.class).sheet("表1").doWrite(excels);
    }

    // 导入

 src/main/java/com/zy/common/utils/excel/ExcelUtils.java

@@ -14,13 +14,11 @@
public class ExcelUtils {

    public static void main(String[] args) {
//        new ExcelUtils().simpleRead();
        new ExcelUtils().simpleWrite();
    }

    public void simpleRead(){
        ExcelReaderBuilder read = EasyExcel.read("/Users/vincent/Desktop/3869.xls", MatCodeExcel.class, new MatCodeExcelListener());
        // 读取第一个sheet(表),文件流会自动关闭
        read.sheet().doRead();
    }

@@ -32,13 +30,15 @@
        List<MatCodeExcel> list = new ArrayList<>();
        for (int i = 0; i<1000; i++) {
            MatCodeExcel data = new MatCodeExcel();
            data.setMatNo("1");
            data.setBarcode("2");
            data.setMatName("3");
            data.setStr1("5");
            data.setStr2("6");
            data.setMatNo("1902997-5/6");
            data.setBarcode("80001854");
            data.setMatName("铝带|1100-O|0.33*475|");
            data.setStr1("701010015907");
            data.setStr2("鼎胜铝业香港_马来西亚UPT");
            list.add(data);
        }
        return list;
    }


}

 src/main/java/com/zy/common/web/FileController.java

@@ -1,16 +1,21 @@
package com.zy.common.web;

import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.core.common.R;
import com.zy.common.utils.excel.ExcelUtils;
import com.zy.asrs.entity.MatCode;
import com.zy.asrs.service.MatCodeService;
import com.zy.common.utils.excel.matcode.MatCodeExcel;
import com.zy.common.utils.excel.matcode.MatCodeExcelListener;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * todo
@@ -20,6 +25,8 @@
@RequestMapping("excel/")
public class FileController {

    @Autowired
    private MatCodeService matCodeService;
    /**
     * 文件上传
     */
@@ -35,13 +42,25 @@
     */
    @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
        List<MatCode> list = matCodeService.selectList(new EntityWrapper<>());

        List<MatCodeExcel> excels = new ArrayList<>();
        for (MatCode matCode : list) {
            MatCodeExcel excel = new MatCodeExcel();
            excel.setMatNo(matCode.getMatNo());
            excel.setBarcode(matCode.getBarcode());
            excel.setMatName(matCode.getMatName());
            excel.setStr1(matCode.getStr1());
            excel.setStr2(matCode.getStr2());
            excels.add(excel);
        }
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), MatCodeExcel.class).sheet("模板").doWrite(ExcelUtils.data());
        EasyExcel.write(response.getOutputStream(), MatCodeExcel.class).sheet("模板").doWrite(excels);
    }

}

 src/main/webapp/static/js/matCode/matCode.js

@@ -171,42 +171,19 @@
                break;
            // 导出
            case 'exportData':
                layer.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);
                layer.open({
                    type: 1,
                    title: '数据导出',
                    shadeClose: true,
                    content: $('#exportDataDiv'),
                    success: function(layero, index){

                    },
                    end: function () {
                        $("#exportFileName").val("");
                        $("#exportRowCount").val("");
                        }
                    });
                    var exportData = {};
                    $.each($('#search-box [name]').serializeArray(), function() {
                        exportData[this.name] = this.value;
                    });
                    var param = {
                        'matCode': exportData,
                        'fields': fields
                    };
                    $.ajax({
                        url: baseUrl+"/matCode/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)
                            // }
                        }
                    });
                });
                })
                break;
            // 导入
            case 'intoData':
@@ -228,6 +205,15 @@
        }
    });

    // 导出excel
    form.on('submit(exportDo)', function(data) {
        layer.closeAll();
        layer.load(1, {shade: [0.1,'#fff']});
        location.href = baseUrl + "/matCode/export/auth?fileName="+$("#exportFileName").val()+"&rowCount="+$("#exportRowCount").val();
        layer.closeAll('loading');
        return false;
    });

    // 导入excel
    var uploader = upload.render({
        elem: '#uploadEx'

 src/main/webapp/views/matCode/matCode.html

@@ -52,6 +52,28 @@

<iframe id="detail-iframe" scrolling="auto" style="display:none;"></iframe>

<!-- 导出操作弹窗 -->
<div id="exportDataDiv" style="display: none;padding: 20px">
    <div class="layui-form">
        <div class="layui-form-item">
            <label class="layui-form-label" style="padding: 9px 0">设置文件名</label>
            <div class="layui-input-block" style="margin-left: 90px;">
                <input type="text" id="exportFileName" placeholder="选填" autocomplete="off" class="layui-input">
            </div>
        </div>
        <div class="layui-form-item">
            <label class="layui-form-label"  style="padding: 9px 0">导出行数</label>
            <div class="layui-input-block" style="margin-left: 90px;">
                <input type="text" id="exportRowCount" placeholder="选填" autocomplete="off" class="layui-input" lay-verify="number" lay-reqtext="请输入数字">
            </div>
        </div>
        <div style="text-align: center; margin-top: 20px">
            <button class="layui-btn layui-btn-radius layui-btn-normal" id="exportDo" lay-submit lay-filter="exportDo">开始导出</button>
        </div>
    </div>
</div>


<!-- 导入操作弹窗 -->
<div id="importDataDiv" style="display: none;padding: 20px 40px">
    <div class="layui-upload-drag" id="uploadEx" style="width: 200px; font-size: x-small; padding: 10px; text-align: center; box-sizing: border-box">