@Autowired
private UserMapper commonMapper;
@Autowired
private UserService commonService;
private final String id = "User_id";

@Operation(summary = "列表")
@PostMapping("/list")
public Result listUser() {
    return Result.success("数据列表", commonService.list());
}

@Operation(summary = "存在")
@PostMapping("/exist")
public Result existUser(@RequestBody @Validated IdParam param) {
    QueryWrapper<UserEntity> wrapper = new QueryWrapper<>();
    wrapper.eq(id.toLowerCase(Locale.ROOT), param.getId());
    long count = commonService.count(wrapper);
    if (count == 0) return Result.success("ID不存在", false);
    return Result.success("ID已存在", true);
}

@Operation(summary = "保存")
@PostMapping("/insert")
public Result insertUser(@RequestBody @Validated UserEntity param) {
    QueryWrapper<UserEntity> wrapper = new QueryWrapper<>();
    wrapper.eq(id.toLowerCase(Locale.ROOT), param.getUserId());
    if (commonService.count(wrapper) != 0) return Result.failure("ID已存在", new Date());
    if (commonService.save(param)) return Result.success("保存成功", new Date());
    return Result.failure("保存失败", new Date());
}

@Operation(summary = "删除")
@PostMapping("/delete")
public Result deleteUser(@RequestBody @Validated IdParam param) {
    QueryWrapper<UserEntity> wrapper = new QueryWrapper<>();
    wrapper.eq(id.toLowerCase(Locale.ROOT), param.getId());
    if (commonService.count(wrapper) == 0) return Result.failure("ID不存在", param.getId());
    if (commonService.remove(wrapper)) return Result.success("删除成功", param.getId());
    return Result.failure("删除失败", param.getId());
}

@Operation(summary = "修改")
@PostMapping("/update")
public Result updateUser(@RequestBody @Validated UserEntity param) {
    QueryWrapper<UserEntity> wrapper = new QueryWrapper<>();
    wrapper.eq(id.toLowerCase(Locale.ROOT), param.getUserId());
    if (commonService.count(wrapper) == 0) return Result.failure("ID不存在", new Date());
    if (commonService.updateById(param)) return Result.success("修改成功", new Date());
    return Result.failure("修改失败", new Date());
}

@Operation(summary = "查询")
@PostMapping("/select")
public Result selectUser(@RequestBody @Validated IdParam param) {
    QueryWrapper<UserEntity> wrapper = new QueryWrapper<>();
    wrapper.eq(id.toLowerCase(Locale.ROOT), param.getId());
    if (commonService.count(wrapper) == 0) return Result.failure("ID不存在", param.getId());
    return Result.success(commonService.getOne(wrapper));
}


@Operation(summary = "查询byAcc")
@PostMapping("/selectByAcc/{param}")
public Result selectUserByAcc(@PathVariable @Validated String param) {
    QueryWrapper<UserEntity> wrapper = new QueryWrapper<>();
    wrapper.eq("user_acc", param);
    if (commonService.count(wrapper) == 0) return Result.failure("ID不存在", param);
    return Result.success(commonService.getOne(wrapper));
}


@Operation(summary = "列表分页")
@PostMapping("/listPage")
public Result listPageUser(@RequestParam Integer page, @RequestParam Integer pageSize) {
    //分页参数
    Page<UserEntity> rowPage = new Page(page, pageSize);
    //queryWrapper组装查询where条件
    LambdaQueryWrapper<UserEntity> queryWrapper = new LambdaQueryWrapper<>();
    rowPage = commonMapper.selectPage(rowPage, queryWrapper);
    return Result.success("数据列表", rowPage);
}

@Operation(summary = "导出数据")
@PostMapping("exportExcel")
public void exportExcelUser(HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("轮播图", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
    List<UserEntity> list = commonService.list();
    response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
    EasyExcel.write(response.getOutputStream(), UserEntity.class).sheet("轮播图").doWrite(list);
}

@Operation(summary = "导入数据")
@PostMapping("/importExcel")
public Result importExcelUser(MultipartFile file) {
    try {
        //获取文件的输入流
        InputStream inputStream = file.getInputStream();
        List<UserEntity> list = EasyExcel.read(inputStream) //调用read方法
                //注册自定义监听器，字段校验可以在监听器内实现
                //.registerReadListener(new UserListener())
                .head(UserEntity.class) //对应导入的实体类
                .sheet(0) //导入数据的sheet页编号，0代表第一个sheet页，如果不填，则会导入所有sheet页的数据
                .headRowNumber(1) //列表头行数，1代表列表头有1行，第二行开始为数据行
                .doReadSync();//开始读Excel，返回一个List<T>集合，继续后续入库操作
        //模拟导入数据库操作
        for (UserEntity entity : list) {
            commonService.saveOrUpdate(entity);
        }
        return Result.success("导入成功", new Date());
    } catch (IOException exception) {
        throw new RuntimeException(exception);
    }
}

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.xiaohe.uploadimage.entity.User;

/**
 * 自定义监听器，对下载的excel中的数据进行校验
 */

public class UserListener extends AnalysisEventListener {

    List<String> names = new ArrayList<>();

    /**
     * 每解析一行，回调该方法
     *
     * @param data
     * @param context
     */
    @Override
    public void invoke(Object data, AnalysisContext context) {
        //校验名称
        String name = ((User) data).getU_name();
//        if (StrUtil.isBlank(name)) {
//            throw new RuntimeException(String.format("第%s行名称为空，请核实", context.readRowHolder().getRowIndex() + 1));
//        }
        if (names.contains(name)) {
            throw new RuntimeException(String.format("第%s行名称已重复，请核实", context.readRowHolder().getRowIndex() + 1));
        } else {
            names.add(name);
        }
    }

    /**
     * 出现异常回调
     *
     * @param exception
     * @param context
     * @throws Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        if (exception instanceof ExcelDataConvertException) {
            /**从0开始计算*/
            int columnIndex = ((ExcelDataConvertException) exception).getColumnIndex() + 1;
            int rowIndex = ((ExcelDataConvertException) exception).getRowIndex() + 1;
            String message = "第" + rowIndex + "行，第" + columnIndex + "列" + "数据格式有误，请核实";
            throw new RuntimeException(message);
        } else if (exception instanceof RuntimeException) {
            throw exception;
        } else {
            super.onException(exception, context);
        }
    }

    /**
     * 解析完,全部回调
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //解析完,全部回调逻辑实现
        names.clear();
    }
}

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

// ... 其他代码 ...

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class User {
    // ... 实体类属性和注解 ...
     @ExcelProperty("账号")
    private String u_acc;
    @ExcelProperty("密码")
    private String u_pwd;
    @ExcelProperty("姓名")
    private String u_name;
    @ExcelProperty("性别")
    private String u_sex;
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd")
    @JsonFormat(pattern = "yyyy-MM-dd")
    @ExcelProperty("生日")
    private Date u_birth;
    @ExcelProperty("角色")
    private String u_ide;
    @ExcelProperty("状态")
    private int u_statues;
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @ExcelProperty("创建日期")
    private Date u_create_time;
}

import com.alibaba.excel.EasyExcel;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

// ... 其他代码 ...

@RestController
public class ExcelController {
    
        @Autowired
    private ExcelMapper excelMapper;

    @GetMapping("user")
    public List<User> user() {
        return excelMapper.selectUserAll();
    }
    
     /**
     * 导出数据
     */
    @GetMapping("exportExcel")
    public void exportData(HttpServletResponse response) throws IOException {
        // ... 导出数据代码 ...
         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("用户表", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
        List<User> users = excelMapper.selectUserAll();
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), User.class).sheet("用户表").doWrite(users);
    }
}

import com.alibaba.excel.EasyExcel;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

// ... 其他代码 ...

@RestController
public class ExcelController {
    /**
     * 导入数据
     */
    @PostMapping("/importExcel")
    public Integer importData(MultipartFile file) {
        try {
            //获取文件的输入流
            InputStream inputStream = file.getInputStream();
            List<User> lst = EasyExcel.read(inputStream) //调用read方法
                    //注册自定义监听器，字段校验可以在监听器内实现
                    .registerReadListener(new UserListener())
                    .head(User.class) //对应导入的实体类
                    .sheet(0) //导入数据的sheet页编号，0代表第一个sheet页，如果不填，则会导入所有sheet页的数据
                    .headRowNumber(1) //列表头行数，1代表列表头有1行，第二行开始为数据行
                    .doReadSync(); //开始读Excel，返回一个List<T>集合，继续后续入库操作
            //模拟导入数据库操作
            for (User user : lst) {
                Date date = user.getU_birth();
                String form = String.format("%tF", date);
                System.out.println(form);
            }
            return 1;
        } catch (IOException exception) {
            throw new RuntimeException(exception);
        }
    }
}

import io.swagger.v3.oas.models.ExternalDocumentation;
import io.swagger.v3.oas.models.OpenAPI;
import io.swagger.v3.oas.models.info.Contact;
import io.swagger.v3.oas.models.info.Info;
import io.swagger.v3.oas.models.info.License;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class SwaggerConfig {
    @Bean
    public OpenAPI springShopOpenAPI() {
        return new OpenAPI()
                .info(new Info().title("标题")
                        .contact(new Contact())
                        .description("我的API文档")
                        .version("v1")
                        .license(new License().name("Apache 2.0").url("http://springdoc.org")))
                .externalDocs(new ExternalDocumentation()
                        .description("外部文档")
                        .url("https://springshop.wiki.github.org/docs"));
    }
}

// 实体类注解示例
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

import java.util.Date;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@Schema(name = "Employee", description = "$!{table.comment}")
public class Emp {
    @ExcelProperty("ID")
    @Schema(description = "ID")
    private int id;
    @ExcelProperty("用户名")
    @Schema(description = "用户名")
    private String names;
    @ExcelProperty("工资")
    @Schema(description = "工资")
    private double salary;
    @ExcelProperty("生日")
    @Schema(description = "生日")
    private Date birthday;
    @ColumnWidth(20)
    @ExcelProperty("头像")
    @Schema(description = "头像")
    private String photo;
    
//    @ColumnWidth(20)
//    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
//    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
//    @ExcelProperty("创建日期")
//    private Date u_create_time;
}


// 控制层注解示例
import io.swagger.v3.oas.annotations.Operation;

@Operation(summary = "获取所有员工信息")
@GetMapping("/selectAll")
public List<Emp> selectAll() {
    // ...
}

import io.swagger.v3.oas.annotations.media.Schema;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;

@Builder(toBuilder = true)
@AllArgsConstructor
@Setter
@Getter
@Slf4j
public class Result<T> {
    /**
     * 提示信息
     */
    @Schema(description = "提示信息")
    private String message;
    /**
     * 是否成功
     */
    @Schema(description = "是否成功")
    private boolean success;
    /**
     * 返回状态码
     */
    @Schema(description = "返回状态码")
    private Integer code;
    /**
     * 数据
     */
    @Schema(description = "数据")
    private T data;

    public Result() {
    }

    public static Result success() {
        Result Result = new Result();
        Result.setSuccess(Boolean.TRUE);
        Result.setCode(ResultCode.SUCCESS.getCode());
        Result.setMessage(ResultCode.SUCCESS.getMsg());
        return Result;
    }

    public static Result success(String msg) {
        Result Result = new Result();
        Result.setMessage(msg);
        Result.setSuccess(Boolean.TRUE);
        Result.setCode(ResultCode.SUCCESS.getCode());
        return Result;
    }

    public static Result success(Object data) {
        Result Result = new Result();
        Result.setData(data);
        Result.setSuccess(Boolean.TRUE);
        Result.setCode(ResultCode.SUCCESS.getCode());
        Result.setMessage(ResultCode.SUCCESS.getMsg());
        return Result;
    }

    /**
     * 返回失败 消息
     *
     * @return Result
     */
    public static Result failure() {
        Result Result = new Result();
        Result.setSuccess(Boolean.FALSE);
        Result.setCode(ResultCode.FAILURE.getCode());
        Result.setMessage(ResultCode.FAILURE.getMsg());
        return Result;
    }

    /**
     * 返回失败 消息
     *
     * @param msg 失败信息
     * @return Result
     */
    public static Result failure(String msg) {
        Result Result = new Result();
        Result.setSuccess(Boolean.FALSE);
        Result.setCode(ResultCode.FAILURE.getCode());
        Result.setMessage(msg);
        return Result;
    }

    public static Result failure(Integer code, String msg) {
        Result Result = new Result();
        Result.setSuccess(Boolean.FALSE);
        Result.setCode(code);
        Result.setMessage(msg);
        return Result;
    }


    public static Result failure(String msg, ResultCode exceptionCode) {
        Result Result = new Result();
        Result.setMessage(msg);
        Result.setSuccess(Boolean.FALSE);
        Result.setCode(exceptionCode.getCode());
        Result.setData(exceptionCode.getMsg());
        return Result;
    }

    /**
     * 返回失败 消息
     *
     * @param exceptionCode 错误信息枚举
     * @return Result
     */
    public static Result failure(ResultCode exceptionCode) {
        Result Result = new Result();
        Result.setSuccess(Boolean.FALSE);
        Result.setCode(exceptionCode.getCode());
        Result.setMessage(exceptionCode.getMsg());
        return Result;
    }

    /**
     * 返回失败 消息
     *
     * @param exceptionCode 错误信息枚举
     * @param msg           自定义错误提示信息
     * @return Result
     */
    public static Result failure(ResultCode exceptionCode, String msg) {
        Result Result = new Result();
        Result.setMessage(msg);
        Result.setSuccess(Boolean.FALSE);
        Result.setCode(exceptionCode.getCode());
        return Result;
    }

}