关于后端:Aapche-POI-java-excel-操作工具包入门

41次阅读

共计 2414 个字符，预计需要花费 7 分钟才能阅读完成。

Apache POI – the Java API for Microsoft Documents

poi

quick-start

jar

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <verison>${poi.version}</verison>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <verison>${poi-ooxml.version}</verison>
</dependency>

get first sheet

/**
* 获取 Excel 第一个 Sheet
* @param file excel 文件
* @param fileSuffix  excel 类型 xls/xlsx
*/
public static Sheet getFirstSheet(File file, String fileSuffix) throws IOException {InputStream stream = new FileInputStream(file);
    Workbook wb = null;
    if (fileSuffix.equals("xls")) {wb = new HSSFWorkbook(stream);
    } else if (fileSuffix.equals("xlsx")) {wb = new XSSFWorkbook(stream);
    }
    return wb.getSheetAt(0);
}

get cell value

/**
* 依据列类型，取得对应的 String 类型
* @return 不存在 / 不反对的类型，则返回 ""
*/
public static String getCellValueStr(Cell cell, String dateFormatStr) {
    String cellValueStr = "";
    if (null != cell) {
      Object cellValue = null;
      switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
          cellValueStr = cell.getRichStringCellValue().getString();
          break;
        case Cell.CELL_TYPE_NUMERIC:
          if (DateUtil.isCellDateFormatted(cell)) {cellValue= cell.getDateCellValue();
            SimpleDateFormat formatter = new SimpleDateFormat(dateFormatStr);
            cellValueStr = formatter.format(cellValue);
          } else {cellValue=cell.getNumericCellValue();
            cellValueStr = String.valueOf(cellValue);
          }
          break;
        case Cell.CELL_TYPE_BOOLEAN:
          cellValue = cell.getBooleanCellValue();
          cellValueStr = String.valueOf(cellValue);
          break;
        case Cell.CELL_TYPE_FORMULA:
          cellValue = cell.getCellFormula();
          cellValueStr = String.valueOf(cellValue);
          break;
        default:
          System.out.println("不反对的 excel 单元格类型");
      }
    }
    return cellValueStr;
}

get excel content –> CSV

/**
* 获取 Excel 工作区的文件内容 - 字符串模式
* - 须要置换 excel 每列的数据（除了每行的完结）以外所有换行符 "\n"
* - 所有 CEll 都视为 String 类型
*/
public static String getSheetContent(Sheet sheet, String charset) throws UnsupportedEncodingException {StringBuffer stringBuffer = new StringBuffer();
    String dateTimeFormatStr = "yyyy-MM-dd HH:mm:ss";
    String lineSeparator = System.getProperty("line.separator", "\n");  // 换行符

    for(Row row : sheet) {for(Cell cell : row) {cell.setCellType(Cell.CELL_TYPE_STRING);  // 全副以 String 类型读取
        String cellStr = new String(getCellValueStr(cell, dateTimeFormatStr).getBytes(), charset);
        String trimCellStr = cellStr.replaceAll(lineSeparator, StringUtils.EMPTY);
        stringBuffer.append(trimCellStr).append(",");
      }

      // 此行有内容
      if(row.getFirstCellNum() != CommonConstant.INVALID_NUMBER) {stringBuffer.deleteCharAt(stringBuffer.lastIndexOf(","));  // 最初一个“,”stringBuffer.append(lineSeparator);
      }
    }

    return stringBuffer.toString();}