用户导出

baizonghao

2023-04-23 4a9a9fa60f52be1e7c91f01df54b3cbae8c11d6c

用户导出

 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, "用户导出");
    }
}

 src/main/java/com/example/jz/excel/ExcelWidthStyleStrategy.java

New file
@@ -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;
    // 缓存（第一个Map的键是sheet的index, 第二个Map的键是列的index, 值是数据长度）
    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默认表头，那么使用columnWidth * 512
                    // 如果不使用EasyExcel默认表头，那么使用columnWidth * 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;
                }
            }
        }
    }*/

}


 src/main/java/com/example/jz/modle/dto/UserExpDto.java

New file
@@ -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;

}

 src/main/java/com/example/jz/utils/EasyExcelUtils.java

New file
@@ -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);
    }


}