相关链接:
https://blog.csdn.net/fz13768884254/article/details/82706936
https://blog.csdn.net/fz13768884254/article/details/82703959
https://blog.csdn.net/fz13768884254/article/details/82707117
项目中需要导入8000多条数据,而数据都是excel格式的,其中相关的数字单元格均已经设置为文本格式,但是在使用poi时,任然会报类型转换异常,相关异常和解决方案如下:
异常:文本格式转数字格式类型异常
数字格式转文本格式类型异常
处理方案:将获取的单元格格式提前设置为对应的类型格式,然后在根据类型进行取值就可
Cell类,相关单元格类型:
public interface Cell {
/**
* Numeric Cell type (0)
* @see #setCellType(int)
* @see #getCellType()
*/
int CELL_TYPE_NUMERIC = 0;
/**
* String Cell type (1)
* @see #setCellType(int)
* @see #getCellType()
*/
int CELL_TYPE_STRING = 1;
/**
* Formula Cell type (2)
* @see #setCellType(int)
* @see #getCellType()
*/
int CELL_TYPE_FORMULA = 2;
/**
* Blank Cell type (3)
* @see #setCellType(int)
* @see #getCellType()
*/
int CELL_TYPE_BLANK = 3;
/**
* Boolean Cell type (4)
* @see #setCellType(int)
* @see #getCellType()
*/
int CELL_TYPE_BOOLEAN = 4;
/**
* Error Cell type (5)
* @see #setCellType(int)
* @see #getCellType()
*/
int CELL_TYPE_ERROR = 5;
}
代码:
Cell cell_0 = row.getCell(0);
//设置单元格格式为数字类型
cell_0.setCellType(Cell.CELL_TYPE_NUMERIC);
Map<String, String> map = Maps.newHashMap();
map.put("hotelId", Double.valueOf(cell_0.getNumericCellValue()).longValue()+"");
Cell cell_1 = row.getCell(1);
map.put("hotelName", cell_1.getStringCellValue());
Cell cell_2 = row.getCell(2);
map.put("proName", cell_2.getStringCellValue());
Cell cell_3 = row.getCell(3);
map.put("cityName", cell_3.getStringCellValue());
Cell cell_4 = row.getCell(4);
map.put("business", cell_4.getStringCellValue());
Cell cell_5 = row.getCell(5);
map.put("addr", cell_5.getStringCellValue());
//设置单元格格式为文本类型
Cell cell_6 = row.getCell(6);
cell_6.setCellType(Cell.CELL_TYPE_STRING);
map.put("star", cell_6.getStringCellValue());
//设置单元格格式为文本类型
Cell cell_7 = row.getCell(7);
cell_7.setCellType(Cell.CELL_TYPE_STRING);
map.put("grade", cell_7.getStringCellValue());
//设置单元格格式为文本类型
Cell cell_8 = row.getCell(8);
cell_8.setCellType(Cell.CELL_TYPE_STRING);
map.put("score", cell_8.getStringCellValue()+"");
Cell cell_9 = row.getCell(9);
map.put("name", cell_9.getStringCellValue());
Cell cell_10 = row.getCell(10);
map.put("account", cell_10.getStringCellValue());
如果导入数据后存在excel表数据与数据库数据不一致时,请查看: