easyexcel将csv转为excel处理数字问题

使用easyexcel可以将csv格式的文件转为.xlsx文件，但是csv中有很多数字，比如："123","12.34","-111"，默认情况下会将其作为字符串写入.xlsx文件，就如同下面一样，字符类型的数字无法直接在excel中做统计和计算，因此需要将其写入excel时转为数字存储。

我们可以使用自定义转换器识别每个单元格中的内容是否是数字，然后将其转为数值类型。

1.csv转为excel程序

@Async("asyncExcelAnalysisServiceExecutor")
    public Future<String> csv2Excel(InputStream inputStream, OutputStream outputStream, String sheetName) {
        try {
            ExcelWriter writer = EasyExcel.write(outputStream).excelType(ExcelTypeEnum.XLSX)
                    //注册写入时类型转换器
                    .registerConverter(new BigDecimalConvert())
                    .build();

            EasyExcel.read(inputStream, new EasyExcelGeneralCsvListener(writer, new WriteSheet()))
                    .excelType(ExcelTypeEnum.CSV)
                    .charset(Charset.forName("UTF-8"))
                    .sheet()
                    .sheetNo(0)
                    .sheetName(sheetName)
                    .doRead();

            writer.finish();
            outputStream.flush();
        } catch (Exception e) {
            log.error("ExcelWriter.finish() -> csv转为excel出错！", e.getMessage());
            e.printStackTrace();
        } catch (Error x) {
            log.error("ExcelWriter.finish() -> csv转为excel出错！", x.getMessage());
            x.printStackTrace();
        } catch (Throwable x) {
            log.error("ExcelWriter.finish() -> csv转为excel出错！", x.getMessage());
            x.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (Exception e) {
                    log.error("outputStream.close() -> csv转为excel出错！", e.getMessage());
                    e.printStackTrace();
                }
            }
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (Exception e) {
                    log.error("inputStream.close() -> csv转为excel出错！", e.getMessage());
                    e.printStackTrace();
                }
            }
        }
        return new AsyncResult<>("success");
    }

2.自定义类型转换器

import java.math.BigDecimal;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.xxx.xxx.common.utils.ObjectUtil;

/**
 * description:BigDecimalConvert
 *
 * @author: lgq
 * @create: 2024-06-04 09:22
 */
public class BigDecimalConvert  implements Converter<String> {

    /**
     * 支持的Java类型
     *
     * @return
     */
    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }

    /**
     * 支持的excel类型
     *
     * @return
     */
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 转换Java支持的类型
     *
     * @param cellData
     * @param excelContentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public String convertToJavaData(ReadCellData cellData, ExcelContentProperty excelContentProperty,
                                    GlobalConfiguration globalConfiguration) throws Exception {
        return cellData.getStringValue();
    }

    /**
     * 转换为Excel支持的类型
     *
     * @param s
     * @param excelContentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public WriteCellData convertToExcelData(String s, ExcelContentProperty excelContentProperty,
                                       GlobalConfiguration globalConfiguration) throws Exception {
        String convertS = convertBigDecimal(s);
        if (!ObjectUtil.isEmpty(convertS)) {
            BigDecimal bigDecimal = new BigDecimal(convertS);
            return new WriteCellData(bigDecimal);
        } else {
            return new WriteCellData(s);
        }
    }

    public static String convertBigDecimal(String str) {
        if (str == null || str.trim().length() == 0) {
            return null;
        }
        StringBuilder convertS = new StringBuilder();
        char[] chars = str.toCharArray();
        int sz = chars.length;
        int i = (chars[0] == '-') ? 1 : 0;
        if (i == sz) {
            return null;
        }
        //除了负号，第一位不能为'小数点'
        if (chars[i] == '.') {
            return null;
        }

        boolean radixPoint = false;
        for (; i < sz; i++) {
            boolean isComma = false;
            if (chars[i] == '.') {
                if (radixPoint) {
                    return null;
                }
                radixPoint = true;
            } else if (!(chars[i] >= '0' && chars[i] <= '9') && !(isComma = (",".equals(String.valueOf(chars[i]))))) {
                return null;
            }
            //删除数字中间的英文逗号
            if (!isComma) {
                convertS.append(chars[i]);
            }
        }
        return convertS.toString();
    }
}