Execl读取问题处理 数据Number类型以字符串读取 , 读取时间返回数字问题 ,工具类

叁歲伎倆 2022-12-10 07:14 182阅读 0赞

1.Execl的number类型以字符串读取

  1. cell.setCellType(Cell.CELL_TYPE_STRING);
  2. String value=cell.getStringCellValue();

2.Execl时间日期读出来为数字类型, 修改返回格式为时间格式

  1. private static final DateFormat FORMAT = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
  2. int cellType1 = cell.getCellType();
  3. if(cellType1==0){ //时间读取格式是0 number格式
  4. if (HSSFDateUtil.isCellDateFormatted(cell)) { //判断是否为时间格式
  5. Date date = cell.getDateCellValue();
  6. return FORMAT.format(date);
  7. }
  8. }

3.实际应用代码

  1. int cellType1 = cell.getCellType();
  2. if(cellType1==0){ //为数字
  3. if (HSSFDateUtil.isCellDateFormatted(cell)) { //判断是否时间格式
  4. Date date = cell.getDateCellValue();
  5. return FORMAT.format(date);
  6. }else { //数字其他格式字符串读取
  7. cell.setCellType(Cell.CELL_TYPE_STRING);
  8. return cell.getStringCellValue();
  9. }
  10. }else { //其他字符串读取
  11. cell.setCellType(Cell.CELL_TYPE_STRING);
  12. return cell.getStringCellValue();
  13. }

4.单元格文件类型判断

  1. public static void main(String[] args) throws Exception {
  2. FileInputStream inputStream =
  3. new FileInputStream("C:\\Users\\Administrator\\Desktop\\123456.xlsx");
  4. //Workbook workbook =new HSSFWorkbook(inputStream); //03版
  5. Workbook workbook = new XSSFWorkbook(inputStream); //07版
  6. //Workbook workbook = new SXSSFWorkbook(inputStream); //07超级版
  7. //获取sheet
  8. Sheet sheet = workbook.getSheetAt(0);
  9. //获取标题内容
  10. Row rowTitle = sheet.getRow(0);
  11. if(rowTitle!=null){
  12. //获取列总数 ,遍历第一行,获取表头
  13. int cellCount = rowTitle.getPhysicalNumberOfCells();
  14. for (int cellNum = 0; cellNum < cellCount ; cellNum++) {
  15. Cell cell = rowTitle.getCell(cellNum);
  16. if(cell!=null){
  17. cell.setCellType(CellType.STRING);
  18. String cellValue = cell.getStringCellValue();
  19. System.out.print(cellValue+" | ");
  20. }
  21. }
  22. System.out.println();
  23. }
  24. //获取表中的内容
  25. //获取行的总数
  26. int rowCount = sheet.getPhysicalNumberOfRows();
  27. for (int rowNum = 0; rowNum <rowCount ; rowNum++) {
  28. Row rowData = sheet.getRow(rowNum);
  29. if(rowData!= null){
  30. int cellCount = rowTitle.getPhysicalNumberOfCells();
  31. for (int cellNum = 0; cellNum < cellCount; cellNum++) {
  32. Cell cell = rowData.getCell(cellNum);
  33. if(cell!=null){
  34. CellType cellType = cell.getCellTypeEnum();
  35. String cellValue="";
  36. switch (cellType){
  37. case STRING:
  38. cellValue=cell.getStringCellValue();
  39. break;
  40. case BOOLEAN:
  41. cellValue= String.valueOf(cell.getBooleanCellValue());
  42. break;
  43. case BLANK:
  44. break;
  45. case NUMERIC: //数字
  46. if(HSSFDateUtil.isCellDateFormatted(cell)){ //判断是否日期数字
  47. Date date = cell.getDateCellValue();
  48. SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  49. cellValue = format.format(date);
  50. }else {
  51. //不是日期格式,防止数字过长
  52. cell.setCellType(CellType.STRING);
  53. cellValue= cell.toString();
  54. }
  55. break;
  56. case ERROR:
  57. break;
  58. }
  59. System.out.print(cellValue+" | ");
  60. }
  61. }
  62. }
  63. System.out.println();
  64. //计算公式
  65. Row row = sheet.getRow(0);
  66. Cell cell = row.getCell(0);
  67. FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
  68. CellType cellType = cell.getCellTypeEnum(); //获取类型
  69. switch (cellType){
  70. case FORMULA: //公式
  71. String formula = cell.getCellFormula(); //公式字符串
  72. System.out.println(formula);
  73. //计算
  74. CellValue evaluate = formulaEvaluator.evaluate(cell);
  75. String cellValue = evaluate.formatAsString();
  76. System.out.println(cellValue);
  77. break;
  78. }
  79. }
  80. inputStream.close();
  81. }

5.参考地址 https://blog.csdn.net/qq_34926773/article/details/90633178

发表评论

表情:
评论列表 (有 0 条评论,182人围观)

还没有评论,来说两句吧...

相关阅读