From 4a9a9fa60f52be1e7c91f01df54b3cbae8c11d6c Mon Sep 17 00:00:00 2001
From: baizonghao <1719256278@qq.com>
Date: 星期日, 23 四月 2023 13:11:21 +0800
Subject: [PATCH] 用户导出
---
src/main/java/com/example/jz/modle/dto/UserExpDto.java | 51 ++++++++++++
src/main/java/com/example/jz/utils/EasyExcelUtils.java | 41 ++++++++++
src/main/java/com/example/jz/controller/UserController.java | 20 +++++
src/main/java/com/example/jz/excel/ExcelWidthStyleStrategy.java | 96 ++++++++++++++++++++++++
4 files changed, 208 insertions(+), 0 deletions(-)
diff --git a/src/main/java/com/example/jz/controller/UserController.java b/src/main/java/com/example/jz/controller/UserController.java
index e955c8e..5b4cf3b 100644
--- a/src/main/java/com/example/jz/controller/UserController.java
+++ b/src/main/java/com/example/jz/controller/UserController.java
@@ -9,17 +9,23 @@
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.jz.modle.R;
import com.example.jz.modle.dto.ManagerDto;
+import com.example.jz.modle.dto.UserExpDto;
import com.example.jz.modle.entity.*;
import com.example.jz.service.*;
+import com.example.jz.utils.EasyExcelUtils;
import com.example.jz.utils.Md5Utils;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
+import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
+import javax.servlet.http.HttpServletResponse;
import java.io.Serializable;
import java.util.*;
import java.util.function.Consumer;
+import java.util.function.Function;
+import java.util.stream.Collectors;
/**
* 鐢ㄦ埛琛�(User)琛ㄦ帶鍒跺眰
@@ -238,4 +244,18 @@
user.setRole(1);
return R.ok(userService.updateById(user));
}
+
+
+ @ApiOperation("鐢ㄦ埛淇℃伅瀵煎嚭")
+ @PostMapping("/exp")
+ public void UserExp(HttpServletResponse response){
+ List<User> list = userService.list(Wrappers.<User>lambdaQuery().eq(User::getRole, 0).eq(User::getStatus, 1));
+ List<UserExpDto> userExpDtos = list.stream().map(user -> {
+ UserExpDto userExpDto = new UserExpDto();
+ BeanUtils.copyProperties(user, userExpDto);
+ return userExpDto;
+ }).collect(Collectors.toList());
+ String sheetName = "鐢ㄦ埛瀵煎嚭";
+ EasyExcelUtils.export1(response, sheetName, UserExpDto.class, userExpDtos, "鐢ㄦ埛瀵煎嚭");
+ }
}
diff --git a/src/main/java/com/example/jz/excel/ExcelWidthStyleStrategy.java b/src/main/java/com/example/jz/excel/ExcelWidthStyleStrategy.java
new file mode 100644
index 0000000..735647a
--- /dev/null
+++ b/src/main/java/com/example/jz/excel/ExcelWidthStyleStrategy.java
@@ -0,0 +1,96 @@
+package com.example.jz.excel;
+
+import com.alibaba.excel.enums.CellDataTypeEnum;
+import com.alibaba.excel.metadata.Head;
+import com.alibaba.excel.metadata.data.WriteCellData;
+import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
+import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
+import org.apache.poi.ss.usermodel.Cell;
+import org.springframework.util.CollectionUtils;
+
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
+
+ // 鍗曞厓鏍肩殑鏈�澶у搴�
+ /*private static final int MAX_COLUMN_WIDTH = 50;
+ // 缂撳瓨锛堢涓�涓狹ap鐨勯敭鏄痵heet鐨刬ndex, 绗簩涓狹ap鐨勯敭鏄垪鐨刬ndex, 鍊兼槸鏁版嵁闀垮害锛�
+ private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);
+
+ // 閲嶅啓璁剧疆鍒楀鐨勬柟娉�
+ @Override
+ protected void setColumnWidth(WriteSheetHolder writeSheetHolder,
+ List<WriteCellData<?>> cellDataList,
+ Cell cell,
+ Head head,
+ Integer relativeRowIndex,
+ Boolean isHead) {
+ boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
+ // 褰撴椂琛ㄥご鎴栬�呭崟鍏冩牸鏁版嵁鍒楄〃鏈夋暟鎹椂鎵嶈繘琛屽鐞�
+ if (needSetWidth) {
+ Map<Integer, Integer> maxColumnWidthMap =
+ CACHE.get(writeSheetHolder.getSheetNo());
+
+ if (maxColumnWidthMap == null) {
+ maxColumnWidthMap = new HashMap(16);
+ CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
+ }
+ // 鑾峰彇鏁版嵁闀垮害
+ Integer columnWidth = this.getLength(cellDataList, cell, isHead);
+ if (columnWidth >= 0) {
+ if (columnWidth > MAX_COLUMN_WIDTH) {
+ columnWidth = MAX_COLUMN_WIDTH;
+ }
+ // 纭繚涓�涓垪鐨勫垪瀹戒互琛ㄥご涓轰富锛屽鏋滆〃澶村凡缁忚缃簡鍒楀锛屽崟鍏冩牸灏嗕細璺熼殢琛ㄥご鐨勫垪瀹�
+ Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
+
+ if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
+ maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
+ // 濡傛灉浣跨敤EasyExcel榛樿琛ㄥご锛岄偅涔堜娇鐢╟olumnWidth * 512
+ // 濡傛灉涓嶄娇鐢‥asyExcel榛樿琛ㄥご锛岄偅涔堜娇鐢╟olumnWidth * 256
+ // 濡傛灉鏄嚜宸卞畾涔夌殑瀛椾綋澶у皬锛屽彲浠ュ啀鍘绘祴璇曡繖涓弬鏁板父閲�
+ writeSheetHolder
+ .getSheet()
+ .setColumnWidth(cell.getColumnIndex(), columnWidth * 512);
+ }
+
+ }
+ }
+ }
+
+ *//**
+ * 鑾峰彇褰撳墠鍗曞厓鏍肩殑鏁版嵁闀垮害
+ * @param cellDataList
+ * @param cell
+ * @param isHead
+ * @return
+ *//*
+ private Integer dataLength(List<WriteCellData<?>> cellDataList,
+ Cell cell,
+ Boolean isHead) {
+ if (isHead) {
+ return cell.getStringCellValue().getBytes().length;
+ } else {
+ WriteCellData cellData = cellDataList.get(0);
+ CellDataTypeEnum type = cellData.getType();
+ if (type == null) {
+ return -1;
+ } else {
+ switch(type) {
+ case STRING:
+ return cellData.getStringValue().getBytes().length;
+ case BOOLEAN:
+ return cellData.getBooleanValue().toString().getBytes().length;
+ case NUMBER:
+ return cellData.getNumberValue().toString().getBytes().length;
+ default:
+ return -1;
+ }
+ }
+ }
+ }*/
+
+}
+
diff --git a/src/main/java/com/example/jz/modle/dto/UserExpDto.java b/src/main/java/com/example/jz/modle/dto/UserExpDto.java
new file mode 100644
index 0000000..5c2f0f8
--- /dev/null
+++ b/src/main/java/com/example/jz/modle/dto/UserExpDto.java
@@ -0,0 +1,51 @@
+package com.example.jz.modle.dto;
+
+import com.alibaba.excel.annotation.ExcelProperty;
+import com.alibaba.excel.annotation.format.DateTimeFormat;
+import io.swagger.annotations.ApiModelProperty;
+import lombok.AllArgsConstructor;
+import lombok.Builder;
+import lombok.Data;
+import lombok.NoArgsConstructor;
+
+import java.util.Date;
+
+@Data
+@AllArgsConstructor
+@NoArgsConstructor
+@Builder
+public class UserExpDto {
+ @ApiModelProperty(value = "濮撳悕",dataType = "String", position = 0)
+ @ExcelProperty(value = "鎶ユ浜�",index = 0)
+ private String realName;
+
+ @ApiModelProperty(value = "鎵嬫満鍙风爜",dataType = "String", position = 1)
+ @ExcelProperty(value = "鎵嬫満鍙风爜",index = 1)
+ private String userMobile;
+
+ @ApiModelProperty(value = "韬唤璇�",dataType = "String", position = 2)
+ @ExcelProperty(value = "韬唤璇�",index = 2)
+ private String userIdcard;
+
+ @ApiModelProperty(value = "鎬у埆",dataType = "String", position = 3)
+ @ExcelProperty(value = "鎬у埆",index = 3)
+ private String sex;
+
+ @ApiModelProperty(value = "鐜板眳鍦�",dataType = "String", position = 4)
+ @ExcelProperty(value = "鐜板眳鍦�",index = 4)
+ private String location;
+
+ @ApiModelProperty(value = "宸ヤ綔鍗曚綅鍦板潃",dataType = "String", position = 5)
+ @ExcelProperty(value = "宸ヤ綔鍗曚綅鍦板潃",index = 5)
+ private String workingLocation;
+
+ @ApiModelProperty(value = "鍒涘缓鏃堕棿",dataType = "Date",position = 6)
+ @ExcelProperty(value = "鍒涘缓鏃堕棿", index = 6)
+ @DateTimeFormat("yyyy/MM/dd")
+ private Date ctime;
+
+ @ApiModelProperty(value = "澶囨敞",dataType = "String", position = 7)
+ @ExcelProperty(value = "澶囨敞",index = 7)
+ private String userMemo;
+
+}
diff --git a/src/main/java/com/example/jz/utils/EasyExcelUtils.java b/src/main/java/com/example/jz/utils/EasyExcelUtils.java
new file mode 100644
index 0000000..f13ea1e
--- /dev/null
+++ b/src/main/java/com/example/jz/utils/EasyExcelUtils.java
@@ -0,0 +1,41 @@
+package com.example.jz.utils;
+
+import com.alibaba.excel.EasyExcel;
+import lombok.SneakyThrows;
+
+import javax.servlet.http.HttpServletResponse;
+import java.net.URLEncoder;
+import java.time.LocalDateTime;
+import java.time.format.DateTimeFormatter;
+import java.util.Collection;
+
+public class EasyExcelUtils {
+
+ /**
+ * @Description 瀵煎嚭
+ * @Param [response , sheetName sheet鍚嶅瓧, className 绫诲悕, collection data]
+ * @return void
+ **/
+ @SneakyThrows
+ public static void export(HttpServletResponse response, String sheetName, Class className, Collection<?> collection) {
+ response.setContentType("application/vnd.ms-excel");
+ response.setCharacterEncoding("utf-8");
+ response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")) + ".xlsx");
+ EasyExcel.write(response.getOutputStream(), className)
+ .sheet(sheetName)
+ .doWrite(collection);
+ }
+
+ @SneakyThrows
+ public static void export1(HttpServletResponse response, String sheetName, Class className, Collection<?> collection, String fileName) {
+ fileName = URLEncoder.encode(fileName, "UTF-8");
+ response.setContentType("application/vnd.ms-excel");
+ response.setCharacterEncoding("utf-8");
+ response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")) + fileName + ".xlsx");
+ EasyExcel.write(response.getOutputStream(), className)
+ .sheet(sheetName)
+ .doWrite(collection);
+ }
+
+
+}
\ No newline at end of file
--
Gitblit v1.8.0