简单导入导出

 依赖管理

        <!--  POI依赖  -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version> 5.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version> 5.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.17.0</version> <!-- 确保这里是支持 atTrace() 的版本 -->
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>2.17.0</version> <!-- 保持 API 和 core 版本一致 -->
        </dependency>

实体类使用注解

package satellite.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.FIELD) // 应用于字段
@Retention(RetentionPolicy.RUNTIME) // 在运行时可用
public @interface CsvHeader {
    int index();
    String header(); // 存储字段的中文名称
    String fieldType() default "String";
}

工具类

package satellite.util;

import org.apache.poi.ss.usermodel.*;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import satellite.annotation.CsvHeader;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Comparator;

public class FileUtil {
    /**
     * 导入给字段赋值
     *
     * @param field
     * @param row
     * @param entity
     * @throws IllegalAccessException
     */
    public static void writerUploadData(Field field, Row row, Object entity) throws IllegalAccessException {
        if (field.isAnnotationPresent(CsvHeader.class)) {
            CsvHeader csvHeader = field.getAnnotation(CsvHeader.class);
            Cell cell = row.getCell(csvHeader.index());
            field.setAccessible(true);
            if (cell == null) {
                field.set(entity, null);
                return;
            }// 获取单元格的字符串值,cell解析出来是Double，会出错
            String cellValue = getCellValueAsString(cell);
            try {
                switch (csvHeader.fieldType()) {
                    case "Double":
                        field.set(entity, cellValue.isEmpty() ? null : Double.valueOf(cellValue));
                        break;
                    case "Integer":
                        field.set(entity, cellValue.isEmpty() ? null : Integer.valueOf(cellValue));
                        break;
                    case "Date":
                        try {
                            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                            field.set(entity, cellValue.isEmpty() ? null : dateFormat.parse(cellValue));
                        } catch (Exception e) {
                            field.set(entity, null);
                        }
                        break;
                    case "String":
                    default:
                        field.set(entity, cellValue);
                        break;
                }
            } catch (Exception e) {
                System.err.println("Error setting field value for " + field.getName() + ": " + e.getMessage());
                field.set(entity, null);
            }
        }
    }

    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 如果是日期类型，返回标准格式日期字符串
                    return new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
                } else {
                    // 将数值转换为字符串，去掉小数点后的0
                    double numericValue = cell.getNumericCellValue();
                    if (numericValue % 1 == 0) {
                        return String.valueOf((int) numericValue);
                    } else {
                        return String.valueOf(numericValue);
                    }
                }
            default:
                return cell.toString().trim();
        }
    }

    /**
     * 构建标题
     *
     * @param fields
     * @param sheet
     */
    public static void writerHeader(Field[] fields, Sheet sheet) {
        // 利用反射动态构建标题行
        Arrays.sort(fields, Comparator.comparingInt(f -> f.getAnnotation(CsvHeader.class).index()));
        Row headerRow = sheet.createRow(0);
        for (Field field : fields) {
            CsvHeader header = field.getAnnotation(CsvHeader.class);
            if (header != null) {
                headerRow.createCell(header.index()).setCellValue(header.header());
            }
        }
    }

    /**
     * 写入数据
     *
     * @param station
     * @param sheet
     * @param rowNum
     * @param fields
     * @return
     * @throws IllegalAccessException
     */
    public static int writerData(Object station, Sheet sheet, int rowNum, Field[] fields) throws IllegalAccessException {
        Row row = sheet.createRow(rowNum++);
        for (Field field : fields) {
            CsvHeader header = field.getAnnotation(CsvHeader.class);
            if (header != null) {
                field.setAccessible(true);
                Object value = field.get(station);
                Cell cell = row.createCell(header.index());
                if (value != null) {
                    if ("Double".equals(header.fieldType())) {
                        cell.setCellValue((Double) value);
                    } else {
                        cell.setCellValue(value.toString());
                    }
                }
            }
        }
        return rowNum;
    }

    /**
     * 构建excel
     *
     * @param response
     * @param encodedFileName
     * @param workbook
     * @return
     * @throws IOException
     */
    public static ResponseEntity<byte[]> buildExcel(HttpServletResponse response, String encodedFileName, Workbook workbook) throws IOException {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        byte[] excelBytes = outputStream.toByteArray();
        workbook.close();
        outputStream.close();

        HttpHeaders headers = new HttpHeaders();
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename*=UTF-8''" + encodedFileName);
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);

        return new ResponseEntity<>(excelBytes, headers, HttpStatus.OK);
    }
}

实体类

package satellite.models.data;

import com.mybatisflex.annotation.Id;
import com.mybatisflex.annotation.Table;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import satellite.annotation.CsvHeader;


@Table(value = "zd_tide")
@Accessors(chain = true)
@NoArgsConstructor
@Data(staticConstructor = "create")

public class Tide {
    @Id
    @ApiModelProperty(value = "主键")
    @CsvHeader(index = 0,header ="主键")
    private Integer id;
    @ApiModelProperty(value = "站名")
    @CsvHeader(index = 1,header ="站名")
    private String name;
    @ApiModelProperty(value = "纬度")
    @CsvHeader(index = 2,header ="纬度",fieldType = "Double")
    private Double latitude;
    @ApiModelProperty(value = "经度")
    @CsvHeader(index = 3,header ="经度",fieldType = "Double")
    private Double longitude;
}

业务代码

package satellite.web.api;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiResponse;
import io.swagger.annotations.ApiResponses;
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.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import satellite.annotation.CsvHeader;
import satellite.models.data.Tide;
import satellite.util.FileUtil;
import satellite.web.api.base.BaseDataApi;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;


@RestController
@RequestMapping("/api/v1/tide")
@CrossOrigin(origins = "*", methods = {RequestMethod.GET, RequestMethod.POST, RequestMethod.PUT, RequestMethod.DELETE, RequestMethod.OPTIONS})
public class TideApi extends BaseDataApi<Tide> {
    

    /**
     * 下载excel模板
     *
     * @param response
     * @throws IOException
     */
    @GetMapping("/template")
    public void downloadExcelTemplate(HttpServletResponse response) throws IOException {
        String encodedFileName = URLEncoder.encode("模板.xlsx", StandardCharsets.UTF_8.toString());
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFileName);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("模板");
        Field[] fields = Tide.class.getDeclaredFields();
        FileUtil.writerHeader(fields, sheet);

        workbook.write(response.getOutputStream());
        workbook.close();
    }

    /**
     * 导出
     *
     * @return
     * @throws IOException
     */
    @GetMapping("/export")
    public String exportToExcel(HttpServletResponse response) throws IOException, IllegalAccessException {
        // 从数据库获取数据
//        try {
            List<Tide> stations = service.list();
            String encodedFileName = URLEncoder.encode("导出.xlsx", StandardCharsets.UTF_8.toString());
            response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFileName);
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            Workbook workbook = new XSSFWorkbook();
            Field[] fields = Tide.class.getDeclaredFields();
            Sheet sheet = workbook.createSheet("导出");
            // 利用反射动态构建标题行
            FileUtil.writerHeader(fields, sheet);
            // 填充数据行
            int rowNum = 1;
            for (Tide station : stations) {
                rowNum = FileUtil.writerData(station, sheet, rowNum, fields);
            }
            workbook.write(response.getOutputStream());
            workbook.close();
            return"文件导出成功";
    }

    /**
     * 导入
     *
     * @param file
     * @return
     * @throws IOException
     */
    @PostMapping("/upload")
    public String uploadCSVFile(@RequestParam("file") MultipartFile file) {
        if (file.isEmpty()) {
            return "上传的文件为空";
        }
        try {
            Set<Integer> existingStations = new HashSet<>();
            for (Tide tide : service.list()) {
                existingStations.add(tide.getId());
            }
            List<Tide> createList = new ArrayList<>();
            List<Tide> updateList = new ArrayList<>();
            Field[] fields = Tide.class.getDeclaredFields();

            Workbook workbook = new XSSFWorkbook(file.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = sheet.rowIterator();
            iterator.next();  // 跳过标题行

            while (iterator.hasNext()) {
                Row row = iterator.next();
                Tide entity = new Tide();

                for (Field field : fields) {
                    if (field.isAnnotationPresent(CsvHeader.class)) {
                        FileUtil.writerUploadData(field, row, entity);
                    }
                }
                if (existingStations.contains(entity.getId())) {
                    updateList.add(entity);
                } else {
                    createList.add(entity);
                }
            }
            workbook.close();
            service.saveBatch(createList);
            service.updateBatch(updateList);
            return "文件上传成功并导入完成";
        } catch (Exception e) {
            return "处理文件时出错:请检查文件或向技术人员提供帮助: " + e.getMessage();
        }
    }
}