Cannot get a NUMERIC value from a STRING cell? 已解决
最近在写项目中用到了excel的导入,遇到了Cannot get a NUMERIC value from a STRING cell的报错。原因是无法从纯数字的单元格用获取String的方式获取。跟了一下蛋码,但是我明明做了处理了啊,看这里
真是奇了个怪。。。。
网上百度了一堆,基本都指出了这个错误的原因,但。。。可能是我这个新手看不懂别人的蛋码吧,其实我觉得他们的蛋码没有抽出来真的难看。
好了,看看我之前的代码:
再看看我修改之后的代码:
总结起来就是,我已经把读取到的单元格内容的类型(NUMERIC)改为了STRING了,但我不知道为什么switch还是走了NUMERIC(可能是我没有转换格式成功),然后我又用NUMERIC的取值方式 getNumericCellValue(),所以就报错咯。
这是全部代码
Controller
/** * 兑换码导入 * * @return * @throws ParseException * @author lmh */ @PostMapping(value = "/readExcel") // @ApiOperation(value = "兑换码导入") @Transactional // @RequiresPermissions("/lotteryPrize/readExcel") // @ApiImplicitParams({ // @ApiImplicitParam(name = "prizeId", value = "奖品id", required = true, dataType = "String"), // @ApiImplicitParam(name = "prizeUrl", value = "兑换URI", required = true, dataType = "String")}) public ResponseEntity<Map<String, Object>> readExcel(/*String prizeId, String prizeUrl,*/ @RequestParam(value = "excelFile") MultipartFile excelFile) throws ParseException { Map<String, Object> resultMap = new HashMap<>(); try { // 修改奖品表 // LotteryPrize prize = new LotteryPrize(); // prize.setId(prizeId); // prize.setPrizeUrl(prizeUrl); // prize.setUpdateTime(new Date(System.currentTimeMillis())); // boolean ret = this.prizeService.updateById(prize); // 导入兑换码 List<LotteryRedeemCode> redeemCodes = new ArrayList<LotteryRedeemCode>(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); List<String[]> codeList = ReadExcelUtil.readExcel(excelFile); Map<String, Integer> titleMap = new HashMap<String, Integer>(); for (int i = 0; i < codeList.size(); i++) { String[] code = codeList.get(i); if (i == 0) { for (int j = 0; j < code.length; j++) { titleMap.put(code[j], j); } continue; } LotteryRedeemCode redeemCode = new LotteryRedeemCode(); redeemCode.setCreateTime(new Date(System.currentTimeMillis())); redeemCode.setRedeemCode(code[titleMap.get("兑换码")]); // redeemCode.setPrizeId(prizeId); redeemCodes.add(redeemCode); } if (redeemCodes.size() > 0) {
// 批量新增(这里是Mybatis-Plus框架),你们可以用for进行新增操作 this.redeemCodeService.insertBatch(redeemCodes); } if (!ret) { // 删除失败, 500 resultMap.put("status", 500); resultMap.put("message", "上传失败!"); return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(resultMap); } resultMap.put("status", 201); resultMap.put("message", "上传成功!"); return ResponseEntity.status(HttpStatus.CREATED).body(resultMap); } catch (IOException e) { logger.info("读取excel文件失败", e); } return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(null); }
package 这里自己写; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.web.multipart.MultipartFile; /** * 导入excel工具类 * * @author lmh */ public class ReadExcelUtil { private static Logger logger = LoggerFactory.getLogger(ReadExcelUtil.class); private final static String xls = "xls"; private final static String xlsx = "xlsx"; /** * 读入excel文件,解析后返回 * * @param file * @throws IOException */ public static List<String[]> readExcel(MultipartFile file) throws IOException { // 检查文件 checkFile(file); // 获得Workbook工作薄对象 Workbook workbook = getWorkBook(file); // 创建返回对象,把每行中的值作为一个数组,所有行作为一个集合返回 List<String[]> list = new ArrayList<String[]>(); if (workbook != null) { for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { // 获得当前sheet工作表 Sheet sheet = workbook.getSheetAt(sheetNum); if (sheet == null) { continue; } // 获得当前sheet的开始行 int firstRowNum = sheet.getFirstRowNum(); // 获得当前sheet的结束行 int lastRowNum = sheet.getLastRowNum(); if (firstRowNum <= 0 && lastRowNum < 1) { break; } // 获取头部的长度 Row tiRow = sheet.getRow(firstRowNum); int totalClumNum = tiRow.getPhysicalNumberOfCells(); // 循环所有行 for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) { // 获得当前行 Row row = sheet.getRow(rowNum); if (row == null) { continue; } int lastCellNum = row.getPhysicalNumberOfCells(); if (lastCellNum <= 0) { continue; } // 获得当前行的开始列 // int firstCellNum = row.getFirstCellNum(); 区第一个有值的列 // //获得当前行的列数 // int lastCellNum = row.getPhysicalNumberOfCells(); String[] cells = new String[totalClumNum]; // 循环当前行 for (int cellNum = 0; cellNum < totalClumNum; cellNum++) { Cell cell = row.getCell(cellNum); cells[cellNum] = getCellValue(cell); } list.add(cells); } } workbook.close(); } return list; } public static void checkFile(MultipartFile file) throws IOException { // 判断文件是否存在 if (null == file) { logger.error("文件不存在!"); throw new FileNotFoundException("文件不存在!"); } // 获得文件名 String fileName = file.getOriginalFilename(); // 判断文件是否是excel文件 if (!fileName.endsWith(xls) && !fileName.endsWith(xlsx)) { logger.error(fileName + "不是excel文件"); throw new IOException(fileName + "不是excel文件"); } } public static Workbook getWorkBook(MultipartFile file) { // 获得文件名 String fileName = file.getOriginalFilename(); // 创建Workbook工作薄对象,表示整个excel Workbook workbook = null; try { // 获取excel文件的io流 InputStream is = file.getInputStream(); // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象 if (fileName.endsWith(xls)) { // 2003 workbook = new HSSFWorkbook(is); } else if (fileName.endsWith(xlsx)) { // 2007 workbook = new XSSFWorkbook(is); } } catch (IOException e) { logger.info(e.getMessage()); } return workbook; } public static String getCellValue(Cell cell) { SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd"); String cellValue = ""; if (cell == null) { return cellValue; } CellType cellType = cell.getCellTypeEnum(); // 把数字当成String来读,避免出现1读成1.0的情况 if (cellType == CellType.NUMERIC) { cell.setCellType(CellType.STRING); } // 判断数据的类型 switch (cellType) { case NUMERIC: // 数字、日期 if (DateUtil.isCellDateFormatted(cell)) { cellValue = fmt.format(cell.getDateCellValue()); // 日期型 } else { cellValue = String.valueOf(cell.getStringCellValue()); // cellValue = String.valueOf(cell.getNumericCellValue()); // 数字 if (cellValue.contains("E")) { cellValue = String.valueOf(new Double(cell.getNumericCellValue()).longValue()); // 数字 } } break; case STRING: // 字符串 cellValue = String.valueOf(cell.getStringCellValue()); break; case BOOLEAN: // Boolean cellValue = String.valueOf(cell.getBooleanCellValue()); break; case FORMULA: // 公式 cellValue = String.valueOf(cell.getCellFormula()); break; case BLANK: // 空值 cellValue = cell.getStringCellValue(); break; case ERROR: // 故障 cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } return cellValue; } }
上面是工具类,下面是实体类:
@TableName("lottery_redeem_code") public class LotteryRedeemCode extends Model<LotteryRedeemCode> { private static final long serialVersionUID = 1L; @TableId(value = "id", type = IdType.UUID) private String id; /** * 奖品ID */ @ApiModelProperty(value = "奖品ID") @TableField("prize_id") private String prizeId; /** * 兑换码 */ @ApiModelProperty(value = "兑换码") @TableField("redeem_code") private String redeemCode; /** * 0-已抽中 1-未抽中 */ @ApiModelProperty(value = "0-已抽中 1-未抽中") private Integer status; /** * 创建时间 */ @ApiModelProperty(value = "创建时间") @TableField("create_time") private Date createTime; /** * 修改时间 */ @ApiModelProperty(value = "修改时间", hidden = true) @TableField("update_time") private Date updateTime; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getPrizeId() { return prizeId; } public void setPrizeId(String prizeId) { this.prizeId = prizeId; } public String getRedeemCode() { return redeemCode; } public void setRedeemCode(String redeemCode) { this.redeemCode = redeemCode; } public Integer getStatus() { return status; } public void setStatus(Integer status) { this.status = status; } public Date getCreateTime() { return createTime; } public void setCreateTime(Date createTime) { this.createTime = createTime; } public Date getUpdateTime() { return updateTime; } public void setUpdateTime(Date updateTime) { this.updateTime = updateTime; } @Override protected Serializable pkVal() { return this.id; } @Override public String toString() { return "LotteryRedeemCode{" + ", id=" + id + ", prizeId=" + prizeId + ", redeemCode=" + redeemCode + ", status=" + status + ", createTime=" + createTime + ", updateTime=" + updateTime + "}"; } }
MySQL:
CREATE TABLE `lottery_redeem_code` ( `id` CHAR(32) NOT NULL, `redeem_code` VARCHAR(100) NOT NULL COMMENT '兑换码', `status` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '0-已抽中 1-未抽中', `create_time` DATETIME NOT NULL COMMENT '创建时间', `update_time` DATETIME NULL DEFAULT NULL COMMENT '修改时间', PRIMARY KEY (`id`) ) COMMENT='奖品兑换码表' COLLATE='utf8_general_ci' ENGINE=InnoDB ;
这是excel表的格式,格式要求不高,只要有一列的表头是“兑换码”就行了,可以随意更改,只要和Controller中的
redeemCode.setRedeemCode(code[titleMap.get("兑换码")]);对应起来就行了,假如我把”兑换码“换成”牛逼“,excel的表头也要换成”牛逼“。多个表头的操作也一样。