基于POI封装的excel实用小工具

发布于:2024-04-30 ⋅ 阅读:(72) ⋅ 点赞:(0)

依赖文件

  		 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
        </dependency>

定义excel注解

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelColumn {
    int value() default 0;

    String name() default "";

    int index() default 1;
}

Excel文件工具类

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Slf4j
public class ExcelMapper {


    /**
     * 通过索引来注入
     *
     * @param file
     * @param clazz
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> mapExcelToObject(File file, Class<T> clazz) throws Exception {
        List<T> objects = new ArrayList<>();
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (row.getRowNum() == 0) {
                continue; // 跳过标题行
            }

            T object = clazz.getDeclaredConstructor().newInstance();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                int columnIndex = cell.getColumnIndex();

                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
                    if (excelColumn != null && excelColumn.value() == columnIndex) {
                        field.setAccessible(true);
                        setFieldValue(field, object, cell);
                        break;
                    }
                }
            }

            objects.add(object);
        }

        workbook.close();
        return objects;
    }




    /**
     * 通过字段名注入(改进测试版)
     * <p>
     * 逻辑,将Cell全部转化为String格式 再读取
     * 根据java实体类的类型再做转化
     *
     * @param file
     * @param clazz
     * @param <T>
     * @return
     * @throws IOException
     */
    public static <T> List<T> readExcel3(File file, Class<T> clazz) throws IOException {
        List<T> dataList = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(file);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0); // 假设只有一个 sheet

            // 读取表头
            Row headerRow = sheet.getRow(0);
            Map<Integer, String> columnIndexToFieldName = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                String fieldName = cell.getStringCellValue().trim();
                columnIndexToFieldName.put(i, fieldName);
            }

            // 读取数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row dataRow = sheet.getRow(i);
                try {
                    T instance = clazz.getDeclaredConstructor().newInstance();
                    for (int j = 0; j < dataRow.getLastCellNum(); j++) {
                        Cell cell = dataRow.getCell(j);
                        String fieldName = columnIndexToFieldName.get(j);
                        Field[] fields = clazz.getDeclaredFields();
                        for (Field field : fields) {
                            ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
                            if (excelColumn != null && excelColumn.name().equals(fieldName)) {
                                field.setAccessible(true);
                                setFieldValue3(field, instance, cell);
                                break;
                            }
                        }
                    }
                    dataList.add(instance);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

        return dataList;
    }


    /**
     * 判断属性的类型并且注入
     *
     * @param field
     * @param object
     * @param cell
     * @throws IllegalAccessException
     */
    private static void setFieldValue(Field field, Object object, Cell cell) throws IllegalAccessException {
        Class<?> fieldType = field.getType();

        if (fieldType.isAssignableFrom(String.class)) {
            field.set(object, cell.getStringCellValue().trim());
        } else if (fieldType == int.class || fieldType.isAssignableFrom(Integer.class)) {
            field.set(object, Integer.valueOf((int) cell.getNumericCellValue()));
        } else if (fieldType == long.class || fieldType.isAssignableFrom(Long.class)) {
            field.set(object, Long.valueOf((long) cell.getNumericCellValue()));
        } else if (fieldType == double.class || fieldType.isAssignableFrom(Double.class)) {
            field.set(object, cell.getNumericCellValue());
        }

    }


    /**
     * 转为cell格式 读取方式
     *
     * @param field
     * @param object
     * @param cell
     * @throws IllegalAccessException
     */
    private static void setFieldValue3(Field field, Object object, Cell cell) throws IllegalAccessException {
        Class<?> fieldType = field.getType();
        if (fieldType.isAssignableFrom(String.class)) {
            field.set(object, getCellValue3(cell).trim());
        } else if (fieldType == int.class || fieldType.isAssignableFrom(Integer.class)) {

            String vaule = getCellValue3(cell);
            if (StringUtils.isBlank(vaule)) {
                field.set(object, 0);
            } else {
                field.set(object, Integer.parseInt(vaule));
            }

        } else if (fieldType == long.class || fieldType.isAssignableFrom(Long.class)) {
            String vaule = getCellValue3(cell);
            if (StringUtils.isBlank(vaule)) {
                field.set(object, 0L);
            } else {
                field.set(object, Long.parseLong(vaule));
            }

        } else if (fieldType == double.class || fieldType.isAssignableFrom(Double.class)) {
            String vaule = getCellValue3(cell);
            if (StringUtils.isBlank(vaule)) {
                field.set(object, 0.0);
            }
            if (vaule.matches("-?\\d+(\\.\\d+)?")) {
                field.set(object, Double.valueOf(vaule));
                ;
            } else {
                field.set(object, 0.0);
            }
        }
    }



    private static Object getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }

        CellType cellType = cell.getCellType();

        if (cellType == CellType.STRING) {
            return cell.getStringCellValue();
        } else if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            } else {
                return cell.getNumericCellValue();
            }
        } else if (cellType == CellType.BOOLEAN) {
            return cell.getBooleanCellValue();
        } else if (cellType == CellType.FORMULA) {
            return cell.getCellFormula();
        } else if (cellType == CellType.ERROR) {
            return cell.getErrorCellValue();
        } else {
            return "";
        }
    }

   

    private static String getCellValue3(Cell cell) {
        if (cell == null) {
            return "";
        }

        CellType cellType = cell.getCellType();

        if (cellType == CellType.STRING) {
            return cell.getStringCellValue();
        } else if (cellType == CellType.NUMERIC) {
          /*  cell.setCellType(CellType.STRING);
            return cell.getStringCellValue();*/
            DataFormatter dataFormatter = new DataFormatter();
            return dataFormatter.formatCellValue(cell);
        }
        return "";

    }


    public static <T> void exportToExcel(List<T> dataList, HttpServletResponse response) {
        response.reset();
        try {
            // 创建工作簿
            Workbook workbook = new XSSFWorkbook();
            // 创建工作表
            Sheet sheet = workbook.createSheet("Sheet1");

            // 创建标题行
            Row headerRow = sheet.createRow(0);
            CellStyle headerCellStyle = workbook.createCellStyle();
            headerCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            // 获取数据对象的类型
            Class<?> dataType = dataList.get(0).getClass();

            // 获取所有字段
            Field[] fields = dataType.getDeclaredFields();

            for (Field field : fields) {
                // 检查字段是否有@ExcelColumn注解
                if (field.isAnnotationPresent(ExcelColumn.class)) {
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    int columnIndex = annotation.index();
                    String columnName = annotation.name();

                    // 创建标题单元格
                    Cell cell = headerRow.createCell(columnIndex);
                    cell.setCellValue(columnName);
                    cell.setCellStyle(headerCellStyle);
                }
            }

            // 填充数据行
            for (int i = 0; i < dataList.size(); i++) {
                Row dataRow = sheet.createRow(i + 1);
                T dataItem = dataList.get(i);

                for (Field field : fields) {
                    if (field.isAnnotationPresent(ExcelColumn.class)) {
                        ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                        int columnIndex = annotation.index();

                        // 设置单元格值
                        Cell cell = dataRow.createCell(columnIndex);
                        field.setAccessible(true);
                        try {
                            Object value = field.get(dataItem);
                            cell.setCellValue(value != null ? value.toString() : "");
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }

            // 生成临时文件
            File tempFile = File.createTempFile("temp", ".xlsx");
            try (FileOutputStream outputStream = new FileOutputStream(tempFile)) {
                workbook.write(outputStream);
            }

            // 设置响应头信息
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment; filename=output.xlsx");


            // 将临时文件内容写入响应输出流
            try (InputStream inputStream = new FileInputStream(tempFile);
                 OutputStream outputStream = response.getOutputStream()) {
                byte[] buffer = new byte[8192];
                int bytesRead;
                while ((bytesRead = inputStream.read(buffer)) != -1) {
                    outputStream.write(buffer, 0, bytesRead);
                }
            }
            System.out.println("Excel导出成功!");
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("Excel导出失败:" + e.getMessage());
        }
    }


    /**
     * 导出Excel文件
     * 此方法通过缓存的方式,多次读取,避免内存占用过大
     * 适合大文件到处
     *
     * @param filePath 文件路径
     * @param response
     * @param <T>
     */
    public static <T> void exportToExcel(String filePath, HttpServletResponse response) {

        String fileName = filePath.substring(filePath.lastIndexOf(File.separator) + 1);
        response.reset();
        // 设置响应头信息
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        try {
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
        response.setCharacterEncoding("utf-8");
        // 设置文件长度
        File file = new File(filePath);
        long fileLength = file.length();
        response.setContentLength((int) fileLength);
        // 将临时文件内容写入响应输出流
        try (InputStream inputStream = Files.newInputStream(file.toPath());

             OutputStream outputStream = response.getOutputStream()) {
            byte[] buffer = new byte[8192];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.info("Excel导出失败:" + e.getMessage());
        }
    }


    public static <T> void exportToExcel2(String file, HttpServletResponse response) {
        // 读取表格文件内容为字节数组
        Path path = Paths.get(file);
        String fileName = path.getFileName().toString();
//        fileName = file.startsWith()
//
        // 设置文件下载响应的相关信息
        try (OutputStream os = new BufferedOutputStream(response.getOutputStream())) {
            byte[] fileBytes = Files.readAllBytes(path);
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.addHeader("Content-Length", "" + fileBytes.length);
            response.setCharacterEncoding("utf-8");
            os.write(fileBytes);
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

定义一个Excel实体类

@Data
public class SeparateLinesExcel {

    /**
     * 姓名
     */
    @ApiModelProperty(value="姓名")
    @ExcelColumn(name = "姓名")
    private String name;

    /**
     * 账号
     */
    @ApiModelProperty(value="账号")
    @ExcelColumn(name = "账号")
    private String protal;

    /**
     * 地市
     */
    @ApiModelProperty(value="地市")
    @ExcelColumn(name = "地市")
    private String city;

    /**
     * 条线
     */
    @ApiModelProperty(value="条线")
    @ExcelColumn(name = "条线")
    private String line;


    /**
     * 条线ID
     */
    @ApiModelProperty(value="条线ID")
    @ExcelColumn(name = "条线ID")
    private Long lineId;

    /**
     * 工单号
     */
    @ApiModelProperty(value="工单号")
    @ExcelColumn(name = "工单号")
    private String workNum;
}

只需要将excel实体注入进方法里面就可以自动解析文件并且得到对应的List

   // 保存文件
	String  newFileName = FileUtil.saveFile(file, loginId, uploadDir);

   // 读取文件
    List<T> excels = readExcel(newFileName,clazz);