Java之Csv转 Xlsx工具类

1、CsvToXlsxUtil工具类：

import cn.hutool.core.util.ObjectUtil;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jumpmind.symmetric.csv.CsvReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;

/**
 * Csv 转 Xlsx 工具类
 */
public class CsvToXlsxUtil {
    private static final Logger log = LoggerFactory.getLogger(CsvToXlsxUtil.class);
    /**
     * CSV常用分隔符，如需动态扩展设置成配置项
     */
    private static final char[] DELIMITERS = {
            ',',
            ';',
            '\001',
            ' ',
            '\t',
            '|',
            '#',
            '&'
    };

    /**
     * 读取CSV文件并写入到XLSX文件中，默认编码
     *
     * @param csvFileAddress 文件地址
     */
    public static String csvToXlsx(String csvFileAddress) {
        return csvToXlsx(csvFileAddress, "UTF-8");
    }

    /**
     * @param inputStream 输入流
     */
    public static InputStream csv2xlsx(InputStream inputStream, String fileName) {
        return csvStream2xlsxStream(inputStream, fileName);
    }

    /**
     * 读取CSV文件并写入到XLSX文件中，指定CSV文件编码
     *
     * @param csvFileAddress 文件地址
     * @param charset        编码
     */
    public static String csvToXlsx(String csvFileAddress, String charset) {
        String xlsxFileAddress = "";
        FileOutputStream fileOutputStream = null;
        try {
            char delimiter = getDelimiter(csvFileAddress);
            //xlsx file address
            xlsxFileAddress = csvFileAddress.replace("csv", "xlsx");
            XSSFWorkbook workBook = new XSSFWorkbook();
            XSSFSheet sheet = workBook.createSheet(getSheetName(csvFileAddress));
            int rowNum = -1;
            CsvReader csvReader = new CsvReader(csvFileAddress, delimiter, Charset.forName(charset));
            while (csvReader.readRecord()) {
                rowNum++;
                XSSFRow currentRow = sheet.createRow(rowNum);
                for (int i = 0; i < csvReader.getColumnCount(); i++) {
                    currentRow.createCell(i).setCellValue(csvReader.get(i));
                }
            }
            fileOutputStream = new FileOutputStream(xlsxFileAddress);
            workBook.write(fileOutputStream);
            return getFileName(xlsxFileAddress);
        } catch (Exception e) {
            log.error("CsvToXlsxUtil exception :", e);
        } finally {
            try {
                assert fileOutputStream != null;
                fileOutputStream.close();
            } catch (IOException e) {
                log.error("CsvToXlsxUtil close FileOutputStream exception :", e);
            }
        }
        return getFileName(xlsxFileAddress);
    }

    /**
     * @param inputStream 输入流
     */
    public static InputStream csvStream2xlsxStream(InputStream inputStream, String fileName) {
        FileOutputStream fileOutputStream = null;
        try {
            fileName = fileName.replace(".csv", ".xlsx");
            XSSFWorkbook workBook = new XSSFWorkbook();
            XSSFSheet sheet = workBook.createSheet("sheet1");
            int rowNum = -1;
            CsvReader csvReader = new CsvReader(inputStream, StandardCharsets.UTF_8);
            while (csvReader.readRecord()) {
                rowNum++;
                XSSFRow currentRow = sheet.createRow(rowNum);
                for (int i = 0; i < csvReader.getColumnCount(); i++) {
                    currentRow.createCell(i).setCellValue(csvReader.get(i));
                }
            }
            File file = new File("/" + fileName);
            fileOutputStream = new FileOutputStream(file);
            workBook.write(fileOutputStream);
            InputStream input = new FileInputStream(file);
            file.delete();
            return input;
        } catch (Exception e) {
            log.error("CsvToXlsxUtil exception :", e);
        } finally {
            try {
                if (ObjectUtil.isNotNull(fileOutputStream)) {
                    assert fileOutputStream != null;
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                log.error("CsvToXlsxUtil close FileOutputStream exception :", e);
            }
        }
        return null;
    }

    /**
     * 设置excel文件的sheet名称
     * 获取CSV文件名作为Excel文件的sheet名称
     *
     * @param path 资源路径
     */
    private static String getSheetName(String path) {
        try {
            String[] file = getFileName(path).split("\\.");
            return file[0];
        } catch (Exception e) {
            log.error("CsvToXlsxUtil get sheet name exception : ", e);
            return "Sheet";
        }
    }

    /**
     * 根据资源路径切割获取文件名
     *
     * @param path 资源路径
     */
    private static String getFileName(String path) {
        String[] paths = path.contains("\\") ? path.split("\\\\") : path.split("/");
        return paths[paths.length - 1];
    }

    /**
     * 常用CSV分隔符数组遍历资源第一行，分隔的字段数多的为资源分隔符
     * 异常情况下默认用’,‘作为分隔符
     *
     * @param path 资源路径
     */
    private static char getDelimiter(String path) {
        BufferedReader br = null;
        char delimiter = ',';
        try {
            br = new BufferedReader(new FileReader(path));
            String line = br.readLine();
            CsvReader csvReader;
            int columCount = 0;
            for (char delimiterTest : DELIMITERS) {
                csvReader = new CsvReader(getStringStream(line), delimiterTest, StandardCharsets.UTF_8);
                if (csvReader.readRecord()) {
                    int newColumnCount = csvReader.getColumnCount();
                    if (newColumnCount > columCount) {
                        columCount = newColumnCount;
                        delimiter = delimiterTest;
                    }
                }
            }
        } catch (Exception e) {
            log.error("CsvToXlsxUtil get delimiter exception :", e);
        } finally {
            try {
                assert br != null;
                br.close();
            } catch (IOException e) {
                log.error("CsvToXlsxUtil get delimiter close BufferedReader exception :", e);
            }
        }
        return delimiter;
    }

    /**
     * 字符串转输入流
     * 把CSV文件第一行数据转成输入流
     *
     * @param sInputString 字符串
     */
    private static InputStream getStringStream(String sInputString) {
        if (null != sInputString && !"".equals(sInputString)) {
            try {
                return new ByteArrayInputStream(sInputString.getBytes());
            } catch (Exception e) {
                log.error("CsvToXlsxUtil get StringStream exception :", e);
            }
        }
        return null;
    }
}

2、importTest测试类：
 

import CsvToXlsxUtil;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;

@SpringBootTest
@RunWith(SpringRunner.class)
public class importTest {
    private static final String EXT_XLSX = ".xlsx";
    private static final String EXT_XLS = ".xls";
    private static final String EXT_CSV = ".csv";

    @Test
    public void test() {
        //csv 文件 => xlsx 文件
        String csvFile = "D:/test/test.csv";
        System.out.println(CsvToXlsxUtil.csvToXlsx(csvFile));
    }

    @Test
    public void importExcel(MultipartFile file) {
        //csv 流 => xlsx 流
        if (file == null || file.isEmpty() || ObjectUtils.isEmpty(file.getOriginalFilename())) {
            System.out.println("文件不能为空");
        } else {
            String fileName = file.getOriginalFilename();
            if (!(fileName.endsWith(EXT_XLSX) || fileName.endsWith(EXT_XLS) || fileName.endsWith(EXT_CSV))) {
                System.out.println("文件类型不正确");
            }
            try {
                InputStream inputStream = file.getInputStream();
                if (fileName.endsWith(EXT_CSV)) {
                    inputStream = CsvToXlsxUtil.csvStream2xlsxStream(file.getInputStream(), fileName);
                }
            } catch (IOException e) {
                System.out.println(e.getMessage());
            }
        }
    }
}