导入导出接口

导出接口

package com.yu.cloudpicturebackend.controller;

import cn.hutool.core.util.ObjUtil;
import cn.hutool.core.util.StrUtil;
import com.yu.cloudpicturebackend.common.ResultUtils;
import com.yu.cloudpicturebackend.exception.BaseResponse;
import com.yu.cloudpicturebackend.exception.BusinessException;
import com.yu.cloudpicturebackend.exception.ErrorCode;
import com.yu.cloudpicturebackend.model.dto.export.UserExportDTO;
import com.yu.cloudpicturebackend.model.entity.User;
import com.yu.cloudpicturebackend.service.UserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;

@RestController
@RequestMapping("/export")
@Api(tags = "导出功能接口")
@Slf4j
public class ExportController {

    @Resource
    private UserService userService;

    private static final DateTimeFormatter DATE_TIME_FORMATTER =
            DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    /**
     * 用户账号导出
     *
     * @param userExportDTO
     * @param response
     * @throws IOException
     */
    @GetMapping("/user")
    @ApiOperation("导出用户账号")
    public void exportUsers(UserExportDTO userExportDTO, HttpServletResponse response) throws IOException {

        String fileName = "用户信息表_" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("MMdd")) + ".xlsx";
        String encodedFileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\+", "%20");

        // 设置响应头
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=" + encodedFileName);

        // 获取数据
        List<User> userList = userService.lambdaQuery()
                .eq(ObjUtil.isNotEmpty(userExportDTO.getId()), User::getId, userExportDTO.getId())
                .like(StrUtil.isNotBlank(userExportDTO.getUserName()), User::getUserName, userExportDTO.getUserName())
                .eq(StrUtil.isNotBlank(userExportDTO.getUserRole()), User::getUserRole, userExportDTO.getUserRole())
                .eq(StrUtil.isNotBlank(userExportDTO.getEmail()), User::getEmail, userExportDTO.getEmail())
                .like(StrUtil.isNotBlank(userExportDTO.getMobile()), User::getMobile, userExportDTO.getMobile())
                .last("LIMIT " + userExportDTO.getSize())
                .list();

        if (userList.isEmpty()) {
            response.sendError(HttpServletResponse.SC_NO_CONTENT, "无导出数据");
            return;
        }

        // 创建Excel并直接写入响应流
        try (Workbook workbook = new XSSFWorkbook();
             ServletOutputStream out = response.getOutputStream();
        ) {

            Sheet sheet = workbook.createSheet("用户账号");

            // 创建表头
            Row headerRow = sheet.createRow(0);
            String[] headers = {"ID", "用户名", "角色", "邮箱", "手机号", "创建时间"};
            for (int i = 0; i < headers.length; i++) {
                headerRow.createCell(i).setCellValue(headers[i]);
            }

            // 填充数据 - 简化处理
            int rowNum = 1;
            for (User user : userList) {
                Row row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(user.getId() != null ? user.getId().toString() : "");
                row.createCell(1).setCellValue(StrUtil.isNotBlank(user.getUserName()) ? user.getUserName() : "");
                row.createCell(2).setCellValue(StrUtil.isNotBlank(user.getUserRole()) ? user.getUserRole() : "");
                row.createCell(3).setCellValue(StrUtil.isNotBlank(user.getEmail()) ? user.getEmail() : "");
                row.createCell(4).setCellValue(StrUtil.isNotBlank(user.getMobile()) ? user.getMobile() : "");

                // 日期处理 - 使用字符串格式避免Excel序列值问题
                Cell dateCell = row.createCell(5);
                if (user.getCreateTime() != null) {
                    String formattedDate = formatDateTime(user.getCreateTime());
                    dateCell.setCellValue(formattedDate);
                } else {
                    dateCell.setCellValue("");
                }

            }

            // 自动调整列宽
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }

            workbook.write(out);
            out.flush(); // 确保数据全部写入

            log.info("✅ 导出成功，数据量: " + userList.size() + "条");
        }

    }

    /**
     * 用户导入模板下载
     *
     * @param response
     * @return
     */
    @GetMapping("/user/import/template")
    @ApiOperation("下载用户导入模板")
    public BaseResponse<Boolean> exportUserImportTemplate(HttpServletResponse response) throws IOException {
        String fileName = "用户导入模板.xlsx";
        String encodedFileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\+", "%20");

        // 设置响应头
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=" + encodedFileName);

        // 创建Excel并直接写入响应流
        try (Workbook workbook = new XSSFWorkbook();
             ServletOutputStream out = response.getOutputStream();
        ) {

            Sheet sheet = workbook.createSheet("用户账号导入");

            // 创建第一行的合并单元格
            Row noticeRow = sheet.createRow(0);
            Cell noticeCell = noticeRow.createCell(0);
            // 使用Excel的换行符
            String noticeText = "注意：" + (char) 10 +
                    "① 用户名不能超过15个字" + (char) 10 +
                    "② 邮箱、手机号不能重复" + (char) 10 +
                    "③ 用户角色只能为 admin 或者 user" + (char) 10 +
                    "④ 密码、邮箱、角色不能为空";
            noticeCell.setCellValue(noticeText);

            // 合并第一行的5个单元格
            CellRangeAddress mergedRegion = new CellRangeAddress(0, 0, 0, 4);
            sheet.addMergedRegion(mergedRegion);

            // 设置单元格样式 - 黄色背景
            CellStyle yellowStyle = workbook.createCellStyle();
            yellowStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            // 设置左对齐
            yellowStyle.setAlignment(HorizontalAlignment.LEFT);
            // 启用自动换行
            yellowStyle.setWrapText(true);

            noticeCell.setCellStyle(yellowStyle);

            // 设置合适的行高
            noticeRow.setHeightInPoints(60);

            // 创建表头（第二行）
            Row headerRow = sheet.createRow(1);
            String[] headers = {"用户名", "密码", "邮箱", "手机号", "角色"};

            // 创建表头样式 - 字体加粗
            CellStyle headerStyle = workbook.createCellStyle();
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);  // 设置字体加粗
            headerStyle.setFont(headerFont);
            headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);  // 应用头部样式
            }

            // 自定义列宽（按字符数）
            sheet.setColumnWidth(0, 15 * 256);  // 用户名列：15个字符宽度
            sheet.setColumnWidth(1, 12 * 256);  // 密码列：12个字符宽度
            sheet.setColumnWidth(2, 25 * 256);  // 邮箱列：25个字符宽度
            sheet.setColumnWidth(3, 15 * 256);  // 手机号列：15个字符宽度
            sheet.setColumnWidth(4, 10 * 256);  // 角色列：10个字符宽度

            workbook.write(out);
            out.flush(); // 确保数据全部写入
            return ResultUtils.success(true);
        } catch (Exception e) {
            throw new BusinessException(ErrorCode.SYSTEM_ERROR);
        }
    }

    /**
     * 统一日期格式化方法
     */
    private String formatDateTime(Object dateTime) {
        if (dateTime == null) return "";
        if (dateTime instanceof Date) {
            return LocalDateTime.ofInstant(((Date) dateTime).toInstant(), ZoneId.systemDefault())
                    .format(DATE_TIME_FORMATTER);
        } else if (dateTime instanceof LocalDateTime) {
            return ((LocalDateTime) dateTime).format(DATE_TIME_FORMATTER);
        } else if (dateTime instanceof LocalDate) {
            return ((LocalDate) dateTime).format(DateTimeFormatter.ISO_DATE);
        }
        return dateTime.toString();
    }

}

导入接口

Controller

package com.yu.cloudpicturebackend.controller;

import cn.hutool.core.util.ObjUtil;
import cn.hutool.core.util.StrUtil;
import com.yu.cloudpicturebackend.common.ResultUtils;
import com.yu.cloudpicturebackend.exception.BaseResponse;
import com.yu.cloudpicturebackend.exception.BusinessException;
import com.yu.cloudpicturebackend.exception.ErrorCode;
import com.yu.cloudpicturebackend.model.dto.export.UserExportDTO;
import com.yu.cloudpicturebackend.model.dto.user.AddUserRequest;
import com.yu.cloudpicturebackend.model.entity.User;
import com.yu.cloudpicturebackend.model.vo.UserImportResult;
import com.yu.cloudpicturebackend.service.ImportService;
import com.yu.cloudpicturebackend.service.UserService;
import com.yu.cloudpicturebackend.utils.SaTokenUtils;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/import")
@Api(tags = "导入功能接口")
@Slf4j
public class ImportController {

    @Resource
    private SaTokenUtils saTokenUtils;

    @Resource
    private UserService userService;

    @Resource
    private ImportService importService;

    /**
     * 用户账号导入
     *
     * @param file
     * @return
     */
    @PostMapping("/user")
    @ApiOperation("导入用户账号")
    public BaseResponse<UserImportResult> importUsers(@RequestParam("file") MultipartFile file) {
        saTokenUtils.getLoginUser();

        try {
            List<User> users = importService.parseUserImportExcel(file);
            List<Integer> errorRowList = new ArrayList<>();

            for (int i = 0; i < users.size(); i++) {
                AddUserRequest addUserRequest = new AddUserRequest();
                BeanUtils.copyProperties(users.get(i), addUserRequest);
                Boolean isSuccess = userService.addUserByImport(addUserRequest);
                if (!isSuccess) {
                    errorRowList.add(i + 1 + 2);
                }
            }

            UserImportResult userImportResult = new UserImportResult();
            userImportResult.setTotal(users.size());
            if (!errorRowList.isEmpty()) {
                userImportResult.setErrorCount(errorRowList.size());
//                String errorMessage = "出错行号：" + errorRowList.toString().replace("[", "").replace("]", "");
//                userImportResult.setErrorMessage(errorMessage);
                userImportResult.setErrorRowList(errorRowList);
            }
            return ResultUtils.success(userImportResult);
        } catch (IOException e) {
            log.error("文件解析失败", e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "文件解析失败，请检查单元格格式");
        }
    }

}

Service

Service 接口

package com.yu.cloudpicturebackend.service;

import com.yu.cloudpicturebackend.model.entity.User;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.List;

public interface ImportService {

    /**
     * 读取单元格文本值
     *
     * @param row
     * @param cellIndex
     * @return
     */
    String getCellStringValue(Row row, int cellIndex);

    /**
     * 解析用户导入表格
     *
     * @param file
     * @return
     * @throws IOException
     */
    List<User> parseUserImportExcel(MultipartFile file) throws IOException;
}

Service 实现类

package com.yu.cloudpicturebackend.service.impl;

import com.yu.cloudpicturebackend.exception.BusinessException;
import com.yu.cloudpicturebackend.exception.ErrorCode;
import com.yu.cloudpicturebackend.model.entity.User;
import com.yu.cloudpicturebackend.service.ImportService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

@Service
@Slf4j
public class ImportServiceImpl implements ImportService {

    /**
     * 读取单元格文本值（使用DataFormatter，最安全的方式）
     *
     * @param row
     * @param cellIndex
     * @return
     */
    @Override
    public String getCellStringValue(Row row, int cellIndex) {
        if (row == null) {
            return "";
        }

        Cell cell = row.getCell(cellIndex);
        if (cell == null) {
            return "";
        }

        // 使用DataFormatter可以按照Excel中显示的样子格式化单元格值
        DataFormatter formatter = new DataFormatter();
        return formatter.formatCellValue(cell).trim();
    }

    /**
     * 解析用户导入表格
     *
     * @param file
     * @return
     */
    @Override
    public List<User> parseUserImportExcel(MultipartFile file) {
        List<User> users = new ArrayList<>();

        try (Workbook workbook = new XSSFWorkbook(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(0);

            for (int i = 2; i <= sheet.getLastRowNum(); i++) { // 跳过表头
                Row row = sheet.getRow(i);
                if (row == null) continue;

                User user = new User();
                user.setUserName(getCellStringValue(row, 0));
                user.setPassword(getCellStringValue(row, 1));
                user.setEmail(getCellStringValue(row, 2));
                user.setMobile(getCellStringValue(row, 3));
                user.setUserRole(getCellStringValue(row, 4));

                users.add(user);
            }
        } catch (IOException e) {
            log.error("文件解析失败", e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "文件解析失败，请检查单元格格式");
        }

        return users;
    }
}