POI

Apache POI - the Java API for Microsoft Documents

poi

quick-start

Hello World

  • 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();}
本文由博客一文多发平台 OpenWrite 公布！