如何做淘宝直播教学视频网站,买外链网站,南海最新消息,西安是哪个省使用easyexcel可以将csv格式的文件转为.xlsx文件#xff0c;但是csv中有很多数字#xff0c;比如#xff1a;123,12.34,-111#xff0c;默认情况下会将其作为字符串写入.xlsx文件#xff0c;就如同下面一样#xff0c;字符类型的数字… 使用easyexcel可以将csv格式的文件转为.xlsx文件但是csv中有很多数字比如123,12.34,-111默认情况下会将其作为字符串写入.xlsx文件就如同下面一样字符类型的数字无法直接在excel中做统计和计算因此需要将其写入excel时转为数字存储。 我们可以使用自定义转换器识别每个单元格中的内容是否是数字然后将其转为数值类型。
1.csv转为excel程序
Async(asyncExcelAnalysisServiceExecutor)public FutureString 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 ConverterString {/*** 支持的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();}
}3.实现效果
