一、 概要

Apache POI [1] 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程序对Microsoft Office格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写，意为“简洁版的模糊实现”。

二、编写poi

1、导入依赖

  1. <dependency>
  2.             <groupId>org.apache.poi</groupId>
  3.             <artifactId>poi</artifactId>
  4.             <version>4.0.1</version>
  5.         </dependency>
  6.         <dependency>
  7.             <groupId>org.apache.poi</groupId>
  8.             <artifactId>poi-ooxml</artifactId>
  9.             <version>4.0.1</version>
  10.         </dependency>
  11.         <dependency>
  12.             <groupId>org.apache.poi</groupId>
  13.             <artifactId>poi-ooxml-schemas</artifactId>
  14.             <version>4.0.1</version>
  15.         </dependency>

2、编写工具类

  1. private static Workbook workbook = null;
  3. /**
  4.      *  判断属于2003  还是2007
  5.      * @param path
  6.      * @throws Exception
  7.      */
  8.     private static void isSuf(String path) throws Exception {
  9.         if(null != path){
  10.             String[] split = path.split("\\.");
  11.             String suf = split[split.length - 1];
  12.             if("xls".equals(suf)){
  13.                 workbook = new HSSFWorkbook(new FileInputStream(path));
  14.             }
  16.             if("xlsx".equals(suf)){
  17.                 workbook = new XSSFWorkbook(new FileInputStream(path));
  18.             }
  19.         }else{
  20.             workbook = new XSSFWorkbook();
  21.         }
  22.     }

3、编写读取excel

  1. /**
  2.      * 导入方法
  3.      * @param path
  4.      * @throws Exception
  5.      */
  6.     public static void  readExcel(String path) throws Exception {
  7.         isSuf(path);
  8.         //获取我们的sheet表
  9.         Sheet sheet = workbook.getSheetAt(0);
  11.         for(int rowNum = 0;rowNum <= sheet.getLastRowNum(); rowNum++){
  12.             //获取我们每一行
  13.             Row row = sheet.getRow(rowNum);
  15.             for (int cellNum = row.getFirstCellNum();cellNum <= row.getLastCellNum();cellNum++){
  17.                 Cell cell = row.getCell(cellNum);
  19.                 Object cellValue = getCellValue(cell);
  20.                 System.out.println(cellValue.toString());
  21.             }
  23.         }
  24.     }

4、编写导出excel

  1. /**
  2.      * 导出execl
  3.      * @param list
  4.      * @throws Exception
  5.      */
  6.     public static void upload(List<Object> list) throws Exception {
  7.         isSuf(null);
  8.         Sheet sheet = workbook.createSheet("账号信息表");
  9.         Row row = sheet.createRow(list.size());
  10.         Cell cell = row.createCell(3);
  11.         cell.setCellValue("你好");
  13.         CellStyle cellStyle = workbook.createCellStyle();
  14.         Font font = workbook.createFont();
  16.         font.setFontName("黑体");
  17.         font.setBold(true);
  18.         font.setFontHeightInPoints((short) 28);
  19.         font.setColor((short) 10);
  20.         cellStyle.setFont(font);
  22.         cell.setCellStyle(cellStyle);
  24.         FileOutputStream fileOutputStream = new FileOutputStream("d:\\账号信息表.xlsx");
  25.         workbook.write(fileOutputStream);
  26.         fileOutputStream.close();
  27.     }

5、其他处理类

  1. private static Object getCellValue(Cell cell) {
  2.         //1.获取到单元格的属性类型
  3.         CellType cellType = cell.getCellType();
  4.         //2.根据单元格数据类型获取数据
  5.         Object value = null;
  6.         switch (cellType) {
  7.             case STRING:
  8.                 value = cell.getStringCellValue();
  9.                 break;
  10.             case BOOLEAN:
  11.                 value = cell.getBooleanCellValue();
  12.                 break;
  13.             case NUMERIC:
  14.                 if (DateUtil.isCellDateFormatted(cell)) {
  15.                     //日期格式
  16.                     value = cell.getDateCellValue();
  17.                 } else {
  18.                     //数字
  19.                     value = cell.getNumericCellValue();
  20.                 }
  21.                 break;
  22.             case FORMULA: //公式
  23.                 value = cell.getCellFormula();
  24.                 break;
  25.             default:
  26.                 break;
  27.         }
  28.         return value;
  29.     }