前言

博客：http://blog.healerjean.com

一、工具类

1、`ExcelEnums` 枚举

package com.healerjean.proj.utils.file;

import com.healerjean.proj.dto.csv.CsvDemoExcel;

/**
 * ExcelEnums
 *
 * @author HealerJean
 * @date 2023-06-21 02:06:04
 */
public class ExcelEnums {

    /**
     * Excel写入枚举
     */
    public enum Excel {
        /**
         * Excel写入枚举
         */
        CSV_DEMO("CsvDemoExcel", CsvDemoExcel.class) {
            @Override
            public String[] header() {
                // 顺序不能轻易变动
                return new String[]{"名称"};
            }

            @Override
            public String[] column() {
                return new String[]{"name"};
            }
        },
        ;

        /**
         * ExcelWriter
         *
         * @param info info
         * @param dataClass dataClass
         */
        Excel(String info, Class dataClass) {
            this.info = info;
            this.dataClass = dataClass;
        }
        /**
         * getInfo
         */
        public String getInfo() {

            return info;
        }

        /**
         * getDataClass
         */
        public Class getDataClass() {
            return dataClass;
        }

        /**
         * 说明
         */
        private final String info;

        /**
         * 导出数据对应Bean
         */
        private final Class dataClass;

        /**
         * 导出表头名
         * 对应写入的展示名
         *
         * @return 表头名
         */
        public abstract String[] header();

        /**
         * 导出列
         * 对应写入bean字段名称
         *
         * @return 列
         */
        public abstract String[] column();
    }

}

2、工具

1）`CsvReaderUtils`

package com.healerjean.proj.utils.file;

import com.opencsv.ICSVParser;
import com.opencsv.bean.ColumnPositionMappingStrategy;
import com.opencsv.bean.CsvToBean;
import com.opencsv.bean.CsvToBeanBuilder;
import lombok.extern.slf4j.Slf4j;

import java.io.*;
import java.net.URL;
import java.net.URLConnection;
import java.util.Collections;
import java.util.List;

@Slf4j
public final class CsvReaderUtils {

    /**
     * readerCsvFromFile 跳过第一行
     *
     * @param file  文件
     * @param excel {@link  ExcelEnums.Excel}
     * @param <E>   E
     * @return List<E>
     */
    public static <E> List<E> readerCsvFromFile(File file, ExcelEnums.Excel excel) {
        try {
            ColumnPositionMappingStrategy<E> mappingStrategy = new ColumnPositionMappingStrategy<>();
            mappingStrategy.setType(excel.getDataClass());
            mappingStrategy.setColumnMapping(excel.column());
            CsvToBean<E> build = new CsvToBeanBuilder<E>(new FileReader(file))
                    .withSkipLines(1)
                    .withMappingStrategy(mappingStrategy)
                    .withSeparator(ICSVParser.DEFAULT_SEPARATOR)
                    .build();
            return build.parse();
        } catch (Exception e) {
            log.error("[ExcelReaCsvReaderUtilsderUtils#readerCsvFromFile] error", e);
            return Collections.emptyList();
        }
    }

    /**
     * readerCsvFromInputStream 跳过第一行
     *
     * @param inputStream 文件
     * @param excel       {@link  ExcelEnums.Excel}
     * @param <E>         E
     * @return List<E>
     */
    public static <E> List<E> readerCsvFromInputStream(InputStream inputStream, ExcelEnums.Excel excel) {
        try {
            ColumnPositionMappingStrategy<E> mappingStrategy = new ColumnPositionMappingStrategy<>();
            mappingStrategy.setType(excel.getDataClass());
            mappingStrategy.setColumnMapping(excel.column());
            CsvToBean<E> build = new CsvToBeanBuilder<E>(new InputStreamReader(inputStream))
                    .withSkipLines(1)
                    .withMappingStrategy(mappingStrategy)
                    .withSeparator(ICSVParser.DEFAULT_SEPARATOR)
                    .build();
            return build.parse();
        }catch (Exception e){
            log.error("[ExcelReaCsvReaderUtilsderUtils#readerCsvFromInputStream] error", e);
            return Collections.emptyList();
        }
    }

    /**
     * readerExcelFromUrl  跳过第一行
     *
     * @param url   文件HTTP下载链接
     * @param excel {@link  ExcelEnums.Excel}
     * @param <E>   E
     * @return List<E>
     */
    public static <E> List<E> readerExcelFromUrl(String url, ExcelEnums.Excel excel) {
        try {
            URL httpUrl = new URL(url);
            URLConnection urlConnection = httpUrl.openConnection();
            return readerCsvFromInputStream(urlConnection.getInputStream(), excel);
        } catch (Exception e) {
            log.error("[CsvReaderUtils#readerExcelFromUrl] error, url:{}", url, e);
            return Collections.emptyList();
        }
    }
}

2）`CsvWriterUtils`

package com.healerjean.proj.utils.file;

import com.opencsv.CSVWriter;
import com.opencsv.bean.ColumnPositionMappingStrategy;
import com.opencsv.bean.StatefulBeanToCsv;
import com.opencsv.bean.StatefulBeanToCsvBuilder;
import com.opencsv.exceptions.CsvDataTypeMismatchException;
import com.opencsv.exceptions.CsvRequiredFieldEmptyException;
import lombok.extern.slf4j.Slf4j;

import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public final class CsvWriterUtils {

    /**
     * 默认文件路径
     */
    private static final String DEFAULT_LOCAL_PATH = "/Users/healerjean/Desktop/logs/";


    /**
     * 写入CSV
     *
     * @param dataList   数据列表
     * @param fileName   文件名称
     * @param excel 写入文件类型
     * @return 生成后的路径
     */
    public static <E> String writerCsv(List<E> dataList, String fileName, ExcelEnums.Excel excel) {
        if (null == dataList) {
            dataList = new ArrayList<>();
        }
        // 服务器绝对路径
        String date = DateTimeFormatter.ofPattern("yyyyMMdd").format(LocalDate.now());
        String baseLocalPath = DEFAULT_LOCAL_PATH + excel.name().toLowerCase() + "/" + date + "/";
        // 创建目录
        FileUtils.createDir(baseLocalPath, Boolean.FALSE);
        log.info("ExcelUtils writeCsv begin,size:{}", dataList.size());
        CSVWriter csvWriter;
        // 写入文件的绝对路径
        String writeFilePath = baseLocalPath + System.currentTimeMillis() + "_" + fileName;
        try (Writer writer = new FileWriter(writeFilePath)) {
            // 手动增加BOM标识
            writer.write(new String(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF}));
            // 映射策略
            ColumnPositionMappingStrategy<E> strategy = new ColumnPositionMappingStrategy<>();
            strategy.setType(excel.getDataClass());
            strategy.setColumnMapping(excel.column());
            csvWriter = new CSVWriter(writer,
                    CSVWriter.DEFAULT_SEPARATOR,
                    CSVWriter.NO_QUOTE_CHARACTER,
                    CSVWriter.DEFAULT_ESCAPE_CHARACTER,
                    CSVWriter.DEFAULT_LINE_END);
            // 写表头
            csvWriter.writeNext(excel.header());
            StatefulBeanToCsv<E> beanToCsv = new StatefulBeanToCsvBuilder<E>(writer).
                    withMappingStrategy(strategy).
                    withQuotechar(CSVWriter.NO_QUOTE_CHARACTER).
                    withSeparator(CSVWriter.DEFAULT_SEPARATOR).
                    withEscapechar('\\').build();
            beanToCsv.write(dataList);
            csvWriter.close();
            log.info("FileUtils writeCsv end,size:{}", dataList.size());
            return writeFilePath;
        } catch (IOException e) {
            log.error("FileUtils writeCsv IOException", e);
        } catch (CsvRequiredFieldEmptyException e) {
            log.error("FileUtils writeCsv CsvRequiredFieldEmptyException", e);
        } catch (CsvDataTypeMismatchException e) {
            log.error("FileUtils writeCsv CsvDataTypeMismatchException", e);
        }
        return null;
    }



}

3）`ExcelReaderUtils`

package com.healerjean.proj.utils.file;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * ExcelReaderUtils
 *
 * @author zhangyujin
 * @date 2023/6/21$  15:37$
 */
@Slf4j
public final class ExcelReaderUtils {

    /**
     * readerCsvFromFile 跳过第一行
     *
     * @param file  文件
     * @param excel {@link  ExcelEnums.Excel}
     * @param <E>   E
     * @return List<E>
     */
    public static <E> List<E> readerExcelFromFile(File file, ExcelEnums.Excel excel) {
        try (FileInputStream fileInputStream = new FileInputStream(file)) {
            return readerExcelFromInputStream(fileInputStream, excel);
        } catch (Exception e) {
            log.error("[ExcelReaderUtils#readerExcelFromFile] error", e);
            return Collections.emptyList();
        }
    }


    /**
     * 处理Excel数据
     *
     * @param inputStream InputStream
     * @param excel       ExcelEnums.Excel
     * @param <E>         E
     * @return List<E>
     */
    public static <E> List<E> readerExcelFromInputStream(InputStream inputStream, ExcelEnums.Excel excel) {
        List<E> list = new ArrayList<>();
        EasyExcel.read(inputStream, excel.getDataClass(), new ReadListener<E>() {
            @Override
            public void invoke(E data, AnalysisContext context) {
                list.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
            }
        }).sheet().doRead();
        return list;
    }

    /**
     * 处理Excel数据
     *
     * @param url   文件HTTP下载链接
     * @param excel ExcelEnums.Excel
     * @param <E>   E
     * @return List<E>
     */
    public static <E> List<E> readerExcelFromUrl(String url, ExcelEnums.Excel excel) {
        try {
            URL httpUrl = new URL(url);
            URLConnection urlConnection = httpUrl.openConnection();
            return readerExcelFromInputStream(urlConnection.getInputStream(), excel);
        } catch (Exception e) {
            log.error("[ExcelReaderUtils#readerExcelFromUrl] error, url:{}", url, e);
            return Collections.emptyList();
        }
    }

}

4）`ExcelWriterrUtils`

package com.healerjean.proj.utils.file;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.data.DataFormatData;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * ExcelWriterrUtils
 *
 * @author zhangyujin
 * @date 2023/6/21$  15:38$
 */
@Slf4j
public final class ExcelWriterrUtils {

    /**
     * 默认文件路径
     */
    private static final String DEFAULT_LOCAL_PATH = "/Users/healerjean/Desktop/logs/";

    /**
     * 写入Excel
     *
     * @param dataList 数据列表
     * @param fileName 文件名称
     * @param excel    写入文件类型
     * @return 生成后的路径
     */
    public static <E> String writerExcel(List<E> dataList, String fileName, ExcelEnums.Excel excel) {
        ExcelWriter excelWriter = null;
        try {
            if (null == dataList) {
                dataList = new ArrayList<>();
            }
            String date = DateTimeFormatter.ofPattern("yyyyMMdd").format(LocalDate.now());
            String baseLocalPath = DEFAULT_LOCAL_PATH + excel.name().toLowerCase() + "/" + date + "/";

            // 创建目录
            FileUtils.createDir(baseLocalPath, Boolean.FALSE);
            String writeFilePath = baseLocalPath + System.currentTimeMillis() + "_" + fileName;
            excelWriter = EasyExcel.write(writeFilePath, excel.getDataClass()).build();
            // 头的策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 设置表头居中对齐
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            // 颜色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
            headWriteCellStyle.setWrapped(true);
            // 内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 设置内容靠中对齐
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
            // 设置文本类型
            DataFormatData dataFormatData = new DataFormatData();
            dataFormatData.setIndex((short) 49);
            contentWriteCellStyle.setDataFormatData(dataFormatData);
            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            WriteSheet writeSheet = EasyExcel.writerSheet().registerWriteHandler(horizontalCellStyleStrategy).
                    registerWriteHandler(new AbstractColumnWidthStyleStrategy() {
                        @Override
                        protected void setColumnWidth(CellWriteHandlerContext context) {
                            Sheet sheet = context.getWriteSheetHolder().getSheet();
                            Cell cell = context.getCell();
                            sheet.setColumnWidth(cell.getColumnIndex(), 5000);
                        }
                    }).
                    includeColumnFiledNames(Arrays.asList(excel.column())).build();
            excelWriter.write(dataList, writeSheet);
            return writeFilePath;
        } catch (Exception e) {
            log.error("ExcelUtils writerExcel exception", e);
        } finally {
            //关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        return null;
    }


}

3、验证

1）`DemoExcel`

package com.healerjean.proj.dto.csv;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * CsvDemoExcel
 * @author zhangyujin
 * @date 2022/3/28  17:55.
 */
@Data
public class DemoExcel {

    /**
     * name
     */
    @ExcelProperty(value = "名称")
    private String name;


}

2）`MainTest`

package com.healerjean.proj.utils.file;

import com.healerjean.proj.dto.csv.DemoExcel;
import com.healerjean.proj.utils.json.JsonUtils;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;

import java.io.File;
import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zhangyujin
 * @date 2022/3/28  17:53.
 * @description
 */
@Slf4j
public class MainTest {

    @Test
    public void testCsvWrite() {
        List<DemoExcel> list = new ArrayList<>();
        for (int i = 0; i < 500000; i++) {
            DemoExcel demo = new DemoExcel();
            demo.setName("11111111111" + i);
            list.add(demo);
        }
        String filePath = CsvWriterUtils.writerCsv(list, "test.csv", ExcelEnums.Excel.DEMO_EXCEL);
        log.info("[CsvWriterUtils#writerCsv] filePath:{}", filePath);
    }

    @Test
    public void testCsvRead(){
        File file = new File("/Users/healerjean/Desktop/logs/demo_excel/test.csv");
        List<DemoExcel> list = CsvReaderUtils.readerCsvFromFile(file, ExcelEnums.Excel.DEMO_EXCEL);
        log.info("[CsvWriterUtils#readerCsvFromFile] size:{}, lineOne:{}", list.size(), 
                 JsonUtils.toJsonString(list.get(0)));
    }


    @Test
    public void testExcelWrite() {
        List<DemoExcel> list = new ArrayList<>();
        for (int i = 0; i < 500000; i++) {
            DemoExcel demo = new DemoExcel();
            demo.setName("11111111111" + i);
            list.add(demo);
        }
        String filePath = ExcelWriterrUtils.writerExcel(list, "test.xlsx", ExcelEnums.Excel.DEMO_EXCEL);
        log.info("[ExcelWriterrUtils#writerExcel] filePath:{}", filePath);
    }

    @Test
    public void testExcelRead() {
        File file = new File("/Users/healerjean/Desktop/logs/demo_excel/test.xlsx");
        List<DemoExcel> list = ExcelReaderUtils.readerExcelFromFile(file, ExcelEnums.Excel.DEMO_EXCEL);
        log.info("[CsvTest#readerExcelFromFile] size:{}, lineOne:{}", list.size(), 
                 JsonUtils.toJsonString(list.get(0)));
    }


}

2、说明

2.1、`@CsvBindByPosition`

@CsvBindByPosition 是根据文件中字段的位置来映射

@CsvBindByPosition(position = 1)
private String name;

2.1.1、`@CsvCustomBindByPosition`

@CsvCustomBindByPosition(position = 2, converter = StringToClaimMetaDate.class)
private Date dataTime;

2.1.1.1、`converter`

public class StringToClaimMetaDate extends AbstractBeanField<Date> {

    @SneakyThrows
    @Override
    protected Object convert(String value) {
        if (StringUtils.isBlank(value)) {
            return null;
        }
        return DateUtils.parseDate(value, "yyyyMMdd");
    }
}

2.2、`@CsvBindByName`

@ CsvBindByName 根据表头的名称来映射；@CsvBindByPosition是根据文件中字段的位置来映射。当 2 个注解一起使用时则基于位置的方式有效。

@CsvBindByPosition(column = "name")
private String name;

ContactAuthor

HealeJean的梦想博客

一个高级软件开发工程师的成长之路

Excel读取和写入工具

一、工具类

1、`ExcelEnums` 枚举

2、工具

1）`CsvReaderUtils`

2）`CsvWriterUtils`

3）`ExcelReaderUtils`

4）`ExcelWriterrUtils`

3、验证

1）`DemoExcel`

2）`MainTest`

2、说明

2.1、`@CsvBindByPosition`

2.1.1、`@CsvCustomBindByPosition`

2.1.1.1、`converter`

2.2、`@CsvBindByName`

一、工具类

1、ExcelEnums 枚举

2、工具

1）CsvReaderUtils

2）CsvWriterUtils

3）ExcelReaderUtils

4）ExcelWriterrUtils

3、验证

1）DemoExcel

2）MainTest

2、说明

2.1、@CsvBindByPosition

2.1.1、@CsvCustomBindByPosition

2.1.1.1、converter

2.2、@CsvBindByName

1、`ExcelEnums` 枚举

1）`CsvReaderUtils`

2）`CsvWriterUtils`

3）`ExcelReaderUtils`

4）`ExcelWriterrUtils`

1）`DemoExcel`

2）`MainTest`

2.1、`@CsvBindByPosition`

2.1.1、`@CsvCustomBindByPosition`

2.1.1.1、`converter`

2.2、`@CsvBindByName`