ExcelUtils导入excel，自动对负数金额格式化的问题

大坑

今日开发中遇到一个问题，使用ExcelUtils导入excel，excel中有一列为金额字段，有负数，例如-2.94，发现在导入后被自动转换为了-3.00，只有负数会这样

负数会进行以下操作

(new DecimalFormat("0")).format(o)

解决方案：

1.手写ExcelUtils

java">package com.chinaums.common.utils.sql;import com.chinaums.common.annotation.Excel;
import com.chinaums.tools.DateUtils;
import com.chinaums.tools.StringUtils;
import com.chinaums.tools.converter.Convert;
import com.chinaums.tools.reflect.ReflectUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;import static com.chinaums.tools.poi.ExcelUtils.reverseByExp;/*** Bean 工具类*/
public class ExcelUtils<T> {private static final Logger log = LoggerFactory.getLogger(com.chinaums.tools.poi.ExcelUtils.class);public static final int sheetSize = 65536;private String sheetName;private Excel.Type type;private Workbook wb;private Sheet sheet;private List<T> list;private List<Object[]> fields;public final Class<T> clazz;public ExcelUtils(Class<T> clazz) {this.clazz = clazz;}public List<T> importExcel(String sheetName, InputStream is) throws Exception {this.type = Excel.Type.IMPORT;this.wb = WorkbookFactory.create(is);List<T> list = new ArrayList();Sheet sheet;if (StringUtils.isNotEmpty(sheetName)) {sheet = this.wb.getSheet(sheetName);} else {sheet = this.wb.getSheetAt(0);}if (sheet == null) {throw new IOException("文件sheet不存在");} else {int rows = sheet.getPhysicalNumberOfRows();if (rows > 0) {Map<String, Integer> cellMap = new HashMap();Row heard = sheet.getRow(0);for (int i = 0; i < heard.getPhysicalNumberOfCells(); ++i) {String value = this.getCellValue(heard, i).toString();cellMap.put(value, i);}Field[] allFields = this.clazz.getDeclaredFields();Map<Integer, Field> fieldsMap = new HashMap();Field[] var10 = allFields;int var11 = allFields.length;for (int var12 = 0; var12 < var11; ++var12) {Field field = var10[var12];Excel attr = (Excel) field.getAnnotation(Excel.class);if (attr != null && (attr.type() == Excel.Type.ALL || attr.type() == this.type)) {field.setAccessible(true);Integer column = (Integer) cellMap.get(attr.name());fieldsMap.put(column, field);}}for (int i = 1; i < rows; ++i) {Row row = sheet.getRow(i);T entity = null;Iterator var25 = fieldsMap.entrySet().iterator();while (var25.hasNext()) {Map.Entry<Integer, Field> entry = (Map.Entry) var25.next();Object val = this.getCellValue(row, (Integer) entry.getKey());entity = entity == null ? this.clazz.newInstance() : entity;Field field = (Field) fieldsMap.get(entry.getKey());Class<?> fieldType = field.getType();if (String.class == fieldType) {String s = Convert.toStr(val);if (StringUtils.endsWith(s, ".0")) {val = StringUtils.substringBefore(s, ".0");} else {val = Convert.toStr(val);}} else if (Integer.TYPE != fieldType && Integer.class != fieldType) {if (Long.TYPE != fieldType && Long.class != fieldType) {if (Double.TYPE != fieldType && Double.class != fieldType) {if (Float.TYPE != fieldType && Float.class != fieldType) {if (BigDecimal.class == fieldType) {val = Convert.toBigDecimal(val);} else if (Date.class == fieldType) {if (val instanceof String) {val = DateUtils.parseDate(val);} else if (val instanceof Double) {val = DateUtil.getJavaDate((Double) val);}}} else {val = Convert.toFloat(val);}} else {val = Convert.toDouble(val);}} else {val = Convert.toLong(val);}} else {val = Convert.toInt(val);}if (StringUtils.isNotNull(fieldType)) {Excel attr = (Excel) field.getAnnotation(Excel.class);String propertyName = field.getName();if (StringUtils.isNotEmpty(attr.targetAttr())) {propertyName = field.getName() + "." + attr.targetAttr();} else if (StringUtils.isNotEmpty(attr.readConverterExp())) {val = reverseByExp(String.valueOf(val), attr.readConverterExp());}ReflectUtils.invokeSetter(entity, propertyName, val);}}list.add(entity);}}return list;}}public Object getCellValue(Row row, int column) {if (row == null) {return null;} else {Object val = "";try {Cell cell = row.getCell(column);if (cell != null) {if (cell.getCellTypeEnum() != CellType.NUMERIC && cell.getCellTypeEnum() != CellType.FORMULA) {if (cell.getCellTypeEnum() == CellType.STRING) {val = cell.getStringCellValue();} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {val = cell.getBooleanCellValue();} else if (cell.getCellTypeEnum() == CellType.ERROR) {val = cell.getErrorCellValue();}} else {val = cell.getNumericCellValue();if (HSSFDateUtil.isCellDateFormatted(cell)) {val = DateUtil.getJavaDate((Double) val);} else if ((Double) val % 1.0 > 0.0) {val = (new DecimalFormat("0.00")).format(val);}}}return val;} catch (Exception var5) {return val;}}}
}

2.优化getCellValue方法使其不对负数金额进行处理，原样返回

3.最后附上导入excel代码

java"> public R elmImport(MultipartFile multipartFile) {//1、文件上传fileInfoService.uploadFile(1, 1, null, "", userId, Constants.OTHERS, multipartFile);//2、excel文件读取ExcelUtils<ElmImportVo> excelUtils = new ExcelUtils<>(ElmImportVo.class);//待存储数量List<RealTransInfoEntity> waitSaveList = new ArrayList<>();List<ElmImportVo> elmList;//3、读取校验excel信息try {elmList = excelUtils.importExcel("账单明细", multipartFile.getInputStream());} catch (Exception e) {e.printStackTrace();throw new ServiceException("--------导入失败---------");}
}