Execl读取问题处理 数据Number类型以字符串读取 , 读取时间返回数字问题 ,工具类
1.Execl的number类型以字符串读取
cell.setCellType(Cell.CELL_TYPE_STRING);
String value=cell.getStringCellValue();
2.Execl时间日期读出来为数字类型, 修改返回格式为时间格式
private static final DateFormat FORMAT = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
int cellType1 = cell.getCellType();
if(cellType1==0){ //时间读取格式是0 number格式
if (HSSFDateUtil.isCellDateFormatted(cell)) { //判断是否为时间格式
Date date = cell.getDateCellValue();
return FORMAT.format(date);
}
}
3.实际应用代码
int cellType1 = cell.getCellType();
if(cellType1==0){ //为数字
if (HSSFDateUtil.isCellDateFormatted(cell)) { //判断是否时间格式
Date date = cell.getDateCellValue();
return FORMAT.format(date);
}else { //数字其他格式字符串读取
cell.setCellType(Cell.CELL_TYPE_STRING);
return cell.getStringCellValue();
}
}else { //其他字符串读取
cell.setCellType(Cell.CELL_TYPE_STRING);
return cell.getStringCellValue();
}
4.单元格文件类型判断
public static void main(String[] args) throws Exception {
FileInputStream inputStream =
new FileInputStream("C:\\Users\\Administrator\\Desktop\\123456.xlsx");
//Workbook workbook =new HSSFWorkbook(inputStream); //03版
Workbook workbook = new XSSFWorkbook(inputStream); //07版
//Workbook workbook = new SXSSFWorkbook(inputStream); //07超级版
//获取sheet
Sheet sheet = workbook.getSheetAt(0);
//获取标题内容
Row rowTitle = sheet.getRow(0);
if(rowTitle!=null){
//获取列总数 ,遍历第一行,获取表头
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount ; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if(cell!=null){
cell.setCellType(CellType.STRING);
String cellValue = cell.getStringCellValue();
System.out.print(cellValue+" | ");
}
}
System.out.println();
}
//获取表中的内容
//获取行的总数
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 0; rowNum <rowCount ; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if(rowData!= null){
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowData.getCell(cellNum);
if(cell!=null){
CellType cellType = cell.getCellTypeEnum();
String cellValue="";
switch (cellType){
case STRING:
cellValue=cell.getStringCellValue();
break;
case BOOLEAN:
cellValue= String.valueOf(cell.getBooleanCellValue());
break;
case BLANK:
break;
case NUMERIC: //数字
if(HSSFDateUtil.isCellDateFormatted(cell)){ //判断是否日期数字
Date date = cell.getDateCellValue();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cellValue = format.format(date);
}else {
//不是日期格式,防止数字过长
cell.setCellType(CellType.STRING);
cellValue= cell.toString();
}
break;
case ERROR:
break;
}
System.out.print(cellValue+" | ");
}
}
}
System.out.println();
//计算公式
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
CellType cellType = cell.getCellTypeEnum(); //获取类型
switch (cellType){
case FORMULA: //公式
String formula = cell.getCellFormula(); //公式字符串
System.out.println(formula);
//计算
CellValue evaluate = formulaEvaluator.evaluate(cell);
String cellValue = evaluate.formatAsString();
System.out.println(cellValue);
break;
}
}
inputStream.close();
}
5.参考地址 https://blog.csdn.net/qq_34926773/article/details/90633178
还没有评论,来说两句吧...