当前位置：首页 > news >正文

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

news 2025/7/18 6:55:02

使用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*/@Overridepublic Class supportJavaTypeKey() {return String.class;}/*** 支持的excel类型** @return*/@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}/*** 转换Java支持的类型** @param cellData* @param excelContentProperty* @param globalConfiguration* @return* @throws Exception*/@Overridepublic String convertToJavaData(ReadCellData cellData, ExcelContentProperty excelContentProperty,GlobalConfiguration globalConfiguration) throws Exception {return cellData.getStringValue();}/*** 转换为Excel支持的类型** @param s* @param excelContentProperty* @param globalConfiguration* @return* @throws Exception*/@Overridepublic 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();}
}