Java 文件流csv转xlsx工具类@杨章隐

最近跟进了一个导入文档的项目

但是在导入csv的时候突然报错了

Your InputStream was neither an OLE2 stream, nor an OOXML stream

Your InputStream was neither an OLE2 stream, nor an OOXML stream

这是因为POi不处理非excel类型文件,如果不是excel类型文件或者不是二进制类型文件直接就抛出异常"Your InputStream was neither an OLE2 stream, nor an OOXML stream"

这里提供一个工具类给大家处理解决这个问题

1. maven依赖

<dependency>
    <groupId>org.jumpmind.symmetric</groupId>
    <artifactId>symmetric</artifactId>
    <version>2.0.15</version>
</dependency>

2. 工具类代码

import cn.hutool.core.util.ObjectUtil;
import com.csvreader.CsvReader;
import lombok.extern.log4j.Log4j2;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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

/**
 * Csv 转 Xlsx 工具类
 */
@Log4j2
public class XlsxUtil {
    /**
     * 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;
    }
}

好了，这样之后你可以先转成合适的格式在进行导入数据等操作