From cddfcdf68ff7fff6b1be82bc5edf822458de6749 Mon Sep 17 00:00:00 2001 From: pang.jiabao <pang_jiabao@163.com> Date: 星期四, 18 四月 2024 10:35:23 +0800 Subject: [PATCH] 商品档案全量导出修改 --- src/main/webapp/static/js/mat/mat.js | 33 ++++++++-- src/main/java/com/zy/asrs/entity/Mat.java | 59 ++++++++++++------- src/main/java/com/zy/asrs/controller/MatController.java | 69 ++++++++++++++++++++-- 3 files changed, 123 insertions(+), 38 deletions(-) diff --git a/src/main/java/com/zy/asrs/controller/MatController.java b/src/main/java/com/zy/asrs/controller/MatController.java index 62f533e..1acf4fe 100644 --- a/src/main/java/com/zy/asrs/controller/MatController.java +++ b/src/main/java/com/zy/asrs/controller/MatController.java @@ -1,6 +1,8 @@ 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.style.column.LongestMatchColumnWidthStyleStrategy; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; @@ -25,6 +27,7 @@ import com.zy.common.utils.BarcodeUtils; import com.zy.common.utils.QrCode; import com.zy.common.web.BaseController; +import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.transaction.annotation.Transactional; import org.springframework.web.bind.annotation.*; @@ -34,10 +37,12 @@ import javax.servlet.http.HttpServletResponse; import java.awt.image.BufferedImage; import java.io.IOException; +import java.io.OutputStream; import java.net.URLEncoder; import java.util.*; @RestController +@Slf4j public class MatController extends BaseController { @Autowired @@ -179,13 +184,63 @@ @RequestMapping(value = "/mat/export/auth") @ManagerAuth - public R export(@RequestBody JSONObject param){ - EntityWrapper<Mat> wrapper = new EntityWrapper<>(); - List<String> fields = JSONObject.parseArray(param.getJSONArray("fields").toJSONString(), String.class); - Map<String, Object> map = excludeTrash(param.getJSONObject("mat")); - convert(map, wrapper); - List<Mat> list = matService.selectList(wrapper); - return R.ok(exportSupport(list, fields)); + public void export(@RequestBody JSONObject param, HttpServletResponse response) throws IOException { + //鏂囦欢鍚� + String fileName =String.valueOf(System.currentTimeMillis()); + + OutputStream outputStream =null; + try { + //璁板綍鎬绘暟:瀹為檯涓渶瑕佹牴鎹煡璇㈡潯浠惰繘琛岀粺璁″嵆鍙�:涓�鍏卞灏戞潯 + int totalCount=matService.selectCount(null); + //姣忎竴涓猄heet瀛樻斁100w鏉℃暟鎹� + Integer sheetDataRows = 400000; + //姣忔鍐欏叆鐨勬暟鎹噺20w,姣忛〉鏌ヨ20W + Integer writeDataRows = 200000; + //璁$畻闇�瑕佺殑Sheet鏁伴噺 + Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1); + //璁$畻涓�鑸儏鍐典笅姣忎竴涓猄heet闇�瑕佸啓鍏ョ殑娆℃暟(涓�鑸儏鍐典笉鍖呭惈鏈�鍚庝竴涓猻heet,鍥犱负鏈�鍚庝竴涓猻heet涓嶇‘瀹氫細鍐欏叆澶氬皯鏉℃暟鎹�) + Integer oneSheetWriteCount = sheetDataRows / writeDataRows; + //璁$畻鏈�鍚庝竴涓猻heet闇�瑕佸啓鍏ョ殑娆℃暟 + Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : ((totalCount % sheetDataRows) / writeDataRows + 1)); + outputStream = response.getOutputStream(); + //蹇呴』鏀惧埌寰幆澶栵紝鍚﹀垯浼氬埛鏂版祦 + ExcelWriter excelWriter = EasyExcel.write(outputStream).build(); + //寮�濮嬪垎鎵规煡璇㈠垎娆″啓鍏� + for (int i = 0; i < sheetNum; i++) { + //鍒涘缓Sheet + WriteSheet sheet = new WriteSheet(); + sheet.setSheetName("Sheet"+i); + sheet.setSheetNo(i); + //寰幆鍐欏叆娆℃暟: j鐨勮嚜澧炴潯浠舵槸褰撲笉鏄渶鍚庝竴涓猄heet鐨勬椂鍊欏啓鍏ユ鏁颁负姝e父鐨勬瘡涓猄heet鍐欏叆鐨勬鏁�,濡傛灉鏄渶鍚庝竴涓氨闇�瑕佷娇鐢ㄨ绠楃殑娆℃暟lastSheetWriteCount + for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) { + //鍒嗛〉鏌ヨ涓�娆�20w + Page page1 = new Page(j + 1 + oneSheetWriteCount * i, writeDataRows); + //鏌ヨ鍒嗛〉鍒楄〃---鎸夌収鑷繁鐨勪笟鍔℃煡鍒楄〃锛屽垎椤佃繖涓竴瀹氳浣跨敤杩欎釜锛歱age1.getPageNum(),page1.getPageSize()锛侊紒锛� + List<Mat> list = matService.selectPage(new Page<>(page1.getCurrent(), page1.getLimit())).getRecords(); +// List<AltitudeMonExportExcelVO > SurfDayList = new ArrayList<>(); + //鍐欏叆鍒癳xcel: + /**************z鍙渶瑕侀�夋嫨涓�绉嶆柟寮忓嵆鍙�*****************/ + //杩欓噷鍙互閫氳繃璁剧疆includeColumnFiledNames銆乪xcludeColumnFiledNames瀵煎嚭浠�涔堝瓧娈碉紝鍙互鍔ㄦ�侀厤缃紝鍓嶇浼犺繃鏉ラ偅浜涘垪锛屽氨瀵煎嚭閭d簺鍒� + //鏂瑰紡3銆佷笉鍋氳缃紝鍏ㄩ儴瀵煎嚭 + WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(Mat.class) + .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build(); + excelWriter.write(list, writeSheet); + } + } + // 涓嬭浇EXCEL锛岃繑鍥炵粰鍓嶆stream娴� + response.setContentType("application/octet-stream"); + response.setCharacterEncoding("utf-8"); + response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); + excelWriter.finish(); + outputStream.flush(); + outputStream.close(); + } catch (Exception e) { + e.printStackTrace(); + }finally { + if (outputStream != null) { + outputStream.close(); + } + } } @RequestMapping(value = "/matQuery/auth") diff --git a/src/main/java/com/zy/asrs/entity/Mat.java b/src/main/java/com/zy/asrs/entity/Mat.java index 891a2d6..4b05b46 100644 --- a/src/main/java/com/zy/asrs/entity/Mat.java +++ b/src/main/java/com/zy/asrs/entity/Mat.java @@ -1,5 +1,6 @@ package com.zy.asrs.entity; +import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import com.baomidou.mybatisplus.annotations.TableField; import com.baomidou.mybatisplus.annotations.TableId; @@ -23,6 +24,7 @@ @TableName("man_mat") public class Mat implements Serializable { + @ExcelIgnore @TableField(exist = false) private Double stock; @@ -39,6 +41,7 @@ /** * ID */ + @ExcelIgnore @ApiModelProperty(value= "ID") @TableId(value = "id", type = IdType.AUTO) private Long id; @@ -46,6 +49,7 @@ /** * 缂栧彿 */ + @ExcelIgnore @ApiModelProperty(value= "缂栧彿") private String uuid; @@ -54,6 +58,7 @@ */ @ApiModelProperty(value= "鎵�灞炲綊绫�") @TableField("tag_id") + @ExcelProperty(value = "褰掔被") private Long tagId; /** @@ -75,7 +80,7 @@ * 鍒悕 */ @ApiModelProperty(value= "鍒悕") - @ExcelProperty(value = "鍒悕") + @ExcelIgnore private String name; /** @@ -89,14 +94,14 @@ * 鍨嬪彿 */ @ApiModelProperty(value= "鍨嬪彿") - @ExcelProperty(value = "鍨嬪彿") + @ExcelIgnore private String model; /** * 棰滆壊 */ @ApiModelProperty(value= "棰滆壊") - @ExcelProperty(value = "棰滆壊") + @ExcelIgnore private String color; /** @@ -104,63 +109,63 @@ */ @ApiModelProperty(value= "绫诲瀷") // @ApiModelProperty(value= "鍝佺墝") - @ExcelProperty(value = "绫诲瀷") + @ExcelIgnore private String brand; /** * 鍗曚綅 */ @ApiModelProperty(value= "鍗曚綅") - @ExcelProperty(value = "鍗曚綅") + @ExcelIgnore private String unit; /** * 鍗曚环 */ @ApiModelProperty(value= "鍗曚环") - @ExcelProperty(value = "鍗曚环") + @ExcelIgnore private Double price; /** * sku */ @ApiModelProperty(value= "sku") - @ExcelProperty(value = "sku") + @ExcelIgnore private String sku; /** * 鍗曚綅閲� */ @ApiModelProperty(value= "鍗曚綅閲�") - @ExcelProperty(value = "鍗曚綅閲�") + @ExcelIgnore private Double units; /** * 鏉$爜 */ @ApiModelProperty(value= "鏉$爜") - @ExcelProperty(value = "鏉$爜") + @ExcelIgnore private String barcode; /** * 浜у湴 */ @ApiModelProperty(value= "浜у湴") - @ExcelProperty(value = "浜у湴") + @ExcelIgnore private String origin; /** * 鍘傚 */ @ApiModelProperty(value= "鍘傚") - @ExcelProperty(value = "鍘傚") + @ExcelIgnore private String manu; /** * 鐢熶骇鏃ユ湡 */ @ApiModelProperty(value= "鐢熶骇鏃ユ湡") - @ExcelProperty(value = "鐢熶骇鏃ユ湡") + @ExcelIgnore @TableField("manu_date") private String manuDate; @@ -168,7 +173,7 @@ * 鍝侀」鏁� */ @ApiModelProperty(value= "鍝侀」鏁�") - @ExcelProperty(value = "鍝侀」鏁�") + @ExcelIgnore @TableField("item_num") private String itemNum; @@ -176,7 +181,7 @@ * 瀹夊叏搴撳瓨閲� */ @ApiModelProperty(value= "瀹夊叏搴撳瓨閲�") - @ExcelProperty(value = "瀹夊叏搴撳瓨閲�") + @ExcelIgnore @TableField("safe_qty") private Double safeQty; @@ -184,28 +189,28 @@ * 閲嶉噺 */ @ApiModelProperty(value= "閲嶉噺") - @ExcelProperty(value = "閲嶉噺") + @ExcelIgnore private Double weight; /** * 闀垮害 */ @ApiModelProperty(value= "闀垮害") - @ExcelProperty(value = "闀垮害") + @ExcelIgnore private Double length; /** * 浣撶Н */ @ApiModelProperty(value= "浣撶Н") - @ExcelProperty(value = "浣撶Н") + @ExcelIgnore private Double volume; /** * 涓夋柟缂栫爜 */ @ApiModelProperty(value= "涓夋柟缂栫爜") - @ExcelProperty(value = "涓夋柟缂栫爜") + @ExcelIgnore @TableField("three_code") private String threeCode; @@ -213,14 +218,14 @@ * 渚涘簲鍟� */ @ApiModelProperty(value= "渚涘簲鍟�") - @ExcelProperty(value = "渚涘簲鍟�") + @ExcelIgnore private String supp; /** * 渚涘簲鍟嗙紪鐮� */ @ApiModelProperty(value= "渚涘簲鍟嗙紪鐮�") - @ExcelProperty(value = "渚涘簲鍟嗙紪鐮�") + @ExcelIgnore @TableField("supp_code") private String suppCode; @@ -228,7 +233,7 @@ * 鏄惁鎵规 1: 鏄� 0: 鍚� */ @ApiModelProperty(value= "鏄惁鎵规 1: 鏄� 0: 鍚� ") - @TableField("be_batch") + @ExcelIgnore private Integer beBatch; /** @@ -236,7 +241,7 @@ */ @ApiModelProperty(value= "淇濊川鏈�") @TableField("dead_time") - @ExcelProperty(value = "淇濊川鏈�") + @ExcelIgnore private String deadTime; /** @@ -244,36 +249,41 @@ */ @ApiModelProperty(value= "棰勮澶╂暟") @TableField("dead_warn") - @ExcelProperty(value = "棰勮澶╂暟") + @ExcelIgnore private Integer deadWarn; /** * 鍒惰喘 1: 鍒堕�� 2: 閲囪喘 3: 澶栧崗 */ + @ExcelIgnore @ApiModelProperty(value= "鍒惰喘 1: 鍒堕�� 2: 閲囪喘 3: 澶栧崗 ") private Integer source; /** * 瑕佹眰妫�楠� 1: 鏄� 0: 鍚� */ + @ExcelIgnore @ApiModelProperty(value= "瑕佹眰妫�楠� 1: 鏄� 0: 鍚� ") private Integer inspect; /** * 鍗遍櫓鍝� 1: 鏄� 0: 鍚� */ + @ExcelIgnore @ApiModelProperty(value= "鍗遍櫓鍝� 1: 鏄� 0: 鍚� ") private Integer danger; /** * 鐘舵�� 1: 姝e父 0: 绂佺敤 */ + @ExcelIgnore @ApiModelProperty(value= "鐘舵�� 1: 姝e父 0: 绂佺敤 ") private Integer status; /** * 娣诲姞浜哄憳 */ + @ExcelIgnore @ApiModelProperty(value= "娣诲姞浜哄憳") @TableField("create_by") private Long createBy; @@ -281,6 +291,7 @@ /** * 娣诲姞鏃堕棿 */ + @ExcelIgnore @ApiModelProperty(value= "娣诲姞鏃堕棿") @TableField("create_time") @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") @@ -289,6 +300,7 @@ /** * 淇敼浜哄憳 */ + @ExcelIgnore @ApiModelProperty(value= "淇敼浜哄憳") @TableField("update_by") private Long updateBy; @@ -296,6 +308,7 @@ /** * 淇敼鏃堕棿 */ + @ExcelIgnore @ApiModelProperty(value= "淇敼鏃堕棿") @TableField("update_time") @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") diff --git a/src/main/webapp/static/js/mat/mat.js b/src/main/webapp/static/js/mat/mat.js index 0fa6ccc..446c41c 100644 --- a/src/main/webapp/static/js/mat/mat.js +++ b/src/main/webapp/static/js/mat/mat.js @@ -167,18 +167,35 @@ url: baseUrl+"/mat/export/auth", headers: {'token': localStorage.getItem('token')}, data: JSON.stringify(param), - dataType:'json', + // dataType:'json', contentType:'application/json;charset=UTF-8', method: 'POST', + xhrFields: { + responseType: "blob" // 璁剧疆鍝嶅簲绫诲瀷涓轰簩杩涘埗鏁版嵁 + }, success: function (res) { + // 鍒涘缓涓�涓复鏃剁殑涓嬭浇閾炬帴 + const url = window.URL.createObjectURL(res); + // 鍒涘缓涓�涓殣钘忕殑 <a> 鍏冪礌骞惰缃笅杞介摼鎺� + const a = document.createElement("a"); + a.style.display = "none"; + a.href = url; + a.download = "export.xlsx"; // 鎸囧畾涓嬭浇鐨勬枃浠跺悕 + document.body.appendChild(a); + + // 瑙﹀彂鐐瑰嚮浜嬩欢浠ュ紑濮嬩笅杞� + a.click(); + + // 娓呯悊涓存椂璧勬簮 + setTimeout(function () { + window.URL.revokeObjectURL(url); + document.body.removeChild(a); + layer.closeAll(); + }, 100); + }, + fail: function (){ + layer.msg('瀵煎嚭澶辫触', {icon: 2}) 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) - } } }); }); -- Gitblit v1.9.1