POI 操作Excel导入篇2 -- 获取单元格Cell、类型获取数据处理，行列操作

r囧r小猫 2022-04-01 06:56 1030阅读 0赞

> 本篇收录针对于POI导入Excel 操作cell的一些常用步骤。

*  获取逻辑行

Workbook wb = null;
    Sheet sheet = wb.getSheetAt(0);
    Row row = sheet.getRow(r);

*  判断隐藏行和隐藏列

row.getZeroHeight() //判断隐藏行
    sheet.isColumnHidden(j) //判断隐藏列

*  遍历单元格

//遍历行
    for (int r = 0; r <= sheet.getPhysicalNumberOfRows(); r++) {
             Row row = sheet.getRow(r);   
              if(row.getZeroHeight()){
                    System.out.println("第"+(r + 1)+"行隐藏行");
                    continue;
                }
          //遍历列
          for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
              if (sheet.isColumnHidden(j)){
                 System.out.println("第" + j + "列隐藏列");
                 continue;
              } 
              //获取单元格的值
              String value = checkValue(r,j,row);         
            }
     }

*  获取单元格值-数据处理util

public static String checkValue(int r, int j, Row row) {
            Cell cell = row.getCell(j);
            String value = null;
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            if(cell == null){
                cell = row.createCell(0);
                cell.setCellValue("");
            }
            try {
                switch (cell.getCellType()){
                    case Cell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue().trim();
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        value = String.valueOf(cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        //处理数字和公式
                            value = String.valueOf(Double.valueOf(cell.getNumericCellValue()));
                            value = value.replaceAll("0+?$", "");
                            value = value.replaceAll("[.]$", "");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        //处理日期格式的Excel数据
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            Date date = cell.getDateCellValue();
                            value = sdf.format(date);
                        } else {
                            //处理科学计数法
                            double temp = cell.getNumericCellValue();
                            DecimalFormat df = new DecimalFormat("0");
                            value = df.format(temp);
                        }
                        break;
                    default:
                        return "";
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException("导入失败!第"+(i + 1)+"行第"+(j + 1)+"列,格式有误\n");
            }
            return value;
        }

拓展：

*  获取单元格格式和对应的值获取

<table style="width:523px;"> 
 <thead> 
  <tr> 
   <th style="width:91px;">单元格类型</th> 
   <th style="width:199px;">数值类型和java类型</th> 
   <th style="width:232px;">取值（Cell.getCellType()）</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th style="width:91px;">文本</th> 
   <td style="width:199px;">1 / CELL_TYPE_STRING</td> 
   <td style="width:232px;">cell.getStringCellValue()</td> 
  </tr> 
  <tr> 
   <th style="width:91px;">公式</th> 
   <td style="width:199px;">2 / CELL_TYPE_FORMULA</td> 
   <td style="width:232px;">cell.getNumericCellValue())</td> 
  </tr> 
  <tr> 
   <th style="width:91px;">数值</th> 
   <td style="width:199px;">0 / CELL_TYPE_NUMERIC</td> 
   <td style="width:232px;">cell.getNumericCellValue()</td> 
  </tr> 
  <tr> 
   <th style="width:91px;">布尔</th> 
   <td style="width:199px;">4 / CELL_TYPE_BOOLEAN</td> 
   <td style="width:232px;">cell.getBooleanCellValue()</td> 
  </tr> 
  <tr> 
   <th style="width:91px;">日期</th> 
   <td style="width:199px;">0 / CELL_TYPE_NUMERIC</td> 
   <td style="width:232px;">cell.getDateCellValue()</td> 
  </tr> 
  <tr> 
   <th style="width:91px;">异常</th> 
   <td style="width:199px;">5 / CELL_TYPE_ERROR</td> 
   <td style="width:232px;">cell.getErrorCellValue()</td> 
  </tr> 
 </tbody> 
</table>