POI入门
什么是POI?
官网地址:poi.apache.org
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档 案读和写的功能。
结构
- HSSF——提供读写Microsoft Excel格式档案的功能 03版 .xls (65536行)
- XSSF——提供读写Microsoft Excel OOXML格式档案的功能 07版 .xlsx (无上限)
- HWPF——提供读写Microsoft Word格式档案的功能
- HSLF——提供读写Microsoft PowerPoint格式档案的功能
- HDGF——提供读写Microsoft Visio格式档案的功能
本篇以Excel为例
Excel 导入导出的场景
- 数据导入:减轻录入工作量
- 数据导出:信息归档
- 数据传输:数据传输(异构系统)
- 盗取数据:email + POI + new(不多说)
用的比较多的-阿里巴巴Easy Excel(封装)
地址:https://github.com/alibaba/easyexcel
POI使用
- 找对象
- 使用
- 看结果
测试使用POI
- 创建maven项目
- 导入依赖
- 测试
- 写测试(03,07)
- 读测试(03,07)
- 注 测试文件自行编一个就好
1 <dependencies> 2 <!--xls--> 3 <dependency> 4 <groupId>org.apache.poi</groupId> 5 <artifactId>poi</artifactId> 6 <version>3.9</version> 7 </dependency> 8 <!--xlsx--> 9 <dependency> 10 <groupId>org.apache.poi</groupId> 11 <artifactId>poi-ooxml</artifactId> 12 <version>3.9</version> 13 </dependency> 14 15 <!--日期时间工具--> 16 <dependency> 17 <groupId>joda-time</groupId> 18 <artifactId>joda-time</artifactId> 19 <version>2.10.1</version> 20 </dependency> 21 22 <dependency> 23 <groupId>junit</groupId> 24 <artifactId>junit</artifactId> 25 <version>4.12</version> 26 </dependency> 27 </dependencies>
1 package com.coding; 2 3 import org.apache.poi.hssf.usermodel.HSSFSheet; 4 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 5 import org.apache.poi.ss.usermodel.Cell; 6 import org.apache.poi.ss.usermodel.Row; 7 import org.apache.poi.xssf.usermodel.XSSFSheet; 8 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 9 import org.joda.time.DateTime; 10 import org.junit.Test; 11 12 import java.io.FileNotFoundException; 13 import java.io.FileOutputStream; 14 import java.io.IOException; 15 16 public class ExcelWriteTest { 17 @Test 18 public void testWrite03(){ 19 // 新建一个 excel 工作簿 20 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); 21 // 创建一个默认的工作表 sheet0 22 HSSFSheet sheet = hssfWorkbook.createSheet("会员统计"); 23 // 创建一行记录 下标从 0 开始,代表row 1 行 1-1 24 Row row1 = sheet.createRow(0); 25 // 填写每一个格子的数据 下标从 0 开始,代表cell 1 列 26 Cell cell11 = row1.createCell(0); 27 cell11.setCellValue("今日注册"); 28 // 1-2 29 Cell cell12 = row1.createCell(1); 30 cell12.setCellValue("999"); 31 32 // row 2 33 Row row2 = sheet.createRow(1); 34 // 2-1 35 Cell cell21 = row2.createCell(0); 36 cell21.setCellValue("统计时间"); 37 // 2-2 38 Cell cell22 = row2.createCell(1); 39 String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss"); 40 cell22.setCellValue(time); 41 42 // 文件编写完毕,然后需要数据导出成为xls 43 FileOutputStream fileOutputStream = null; 44 try { 45 fileOutputStream = new FileOutputStream("D:\\IdeaProjects\\coding_edu_api\\test-excel-poi\\testwrite03.xls"); 46 hssfWorkbook.write(fileOutputStream); 47 } catch (FileNotFoundException e) { 48 e.printStackTrace(); 49 } catch (IOException e) { 50 e.printStackTrace(); 51 } finally { 52 try { 53 fileOutputStream.close(); 54 } catch (IOException e) { 55 e.printStackTrace(); 56 } 57 } 58 System.out.println("文件生成完毕"); 59 } 60 @Test 61 public void testWrite07(){ 62 // 新建一个 excel 工作簿 63 XSSFWorkbook xssfWorkbook = new XSSFWorkbook(); 64 // 创建一个默认的工作表 sheet0 65 XSSFSheet sheet = xssfWorkbook.createSheet("会员统计"); 66 // 创建一行记录 下标从 0 开始,代表row 1 行 1-1 67 Row row1 = sheet.createRow(0); 68 // 填写每一个格子的数据 下标从 0 开始,代表cell 1 列 69 Cell cell11 = row1.createCell(0); 70 cell11.setCellValue("今日注册"); 71 // 1-2 72 Cell cell12 = row1.createCell(1); 73 cell12.setCellValue("999"); 74 75 // row 2 76 Row row2 = sheet.createRow(1); 77 // 2-1 78 Cell cell21 = row2.createCell(0); 79 cell21.setCellValue("统计时间"); 80 // 2-2 81 Cell cell22 = row2.createCell(1); 82 String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss"); 83 cell22.setCellValue(time); 84 85 // 文件编写完毕,然后需要数据导出成为xls 86 FileOutputStream fileOutputStream = null; 87 try { 88 fileOutputStream = new FileOutputStream("D:\\IdeaProjects\\coding_edu_api\\test-excel-poi\\testwrite07.xlsx"); 89 xssfWorkbook.write(fileOutputStream); 90 } catch (FileNotFoundException e) { 91 e.printStackTrace(); 92 } catch (IOException e) { 93 e.printStackTrace(); 94 } finally { 95 try { 96 fileOutputStream.close(); 97 } catch (IOException e) { 98 e.printStackTrace(); 99 } 100 } 101 System.out.println("文件生成完毕"); 102 } 103 }
1 package com.coding; 2 3 import org.apache.poi.hssf.usermodel.*; 4 import org.apache.poi.ss.usermodel.*; 5 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 6 import org.joda.time.DateTime; 7 import org.junit.Test; 8 9 import java.io.FileInputStream; 10 import java.io.IOException; 11 import java.util.Date; 12 13 public class ExcelReadTest { 14 15 private final static String PATH = "D:\\IdeaProjects\\coding_edu_api\\test-excel-poi\\"; 16 17 @Test 18 public void testRead03() throws IOException { 19 FileInputStream stream = new FileInputStream(PATH + "testwrite03.xls"); 20 21 Workbook workbook = new HSSFWorkbook(stream); 22 Sheet sheet = workbook.getSheetAt(0); 23 Row row1 = sheet.getRow(1); 24 Cell cell11 = row1.getCell(1); 25 System.out.println(cell11.toString()); 26 stream.close(); 27 } 28 29 @Test 30 public void testRead07() throws IOException { 31 FileInputStream stream = new FileInputStream(PATH + "testwrite07.xlsx"); 32 33 Workbook workbook = new XSSFWorkbook(stream); 34 Sheet sheet = workbook.getSheetAt(0); 35 Row row1 = sheet.getRow(1); 36 Cell cell11 = row1.getCell(1); 37 System.out.println(cell11.toString()); 38 stream.close(); 39 } 40 41 @Test 42 public void testCellType() throws IOException { 43 FileInputStream stream = new FileInputStream(PATH + "会员消费商品明细表.xls"); 44 45 Workbook workbook = new HSSFWorkbook(stream); 46 Sheet sheet = workbook.getSheetAt(0); 47 Row rowTitle = sheet.getRow(0); 48 // 读取标题 49 if (rowTitle != null) { 50 int cellCount = rowTitle.getPhysicalNumberOfCells(); 51 for (int cellNum = 0; cellNum < cellCount; cellNum++) { 52 Cell cell = rowTitle.getCell(cellNum); 53 if (cell != null){ 54 System.out.println(cell.getStringCellValue()); 55 } 56 } 57 System.out.println(); 58 } 59 // 读取内容 60 int rowCount = sheet.getPhysicalNumberOfRows(); 61 for (int rowNum = 1; rowNum < rowCount; rowNum++) { 62 // 行 63 Row rowData = sheet.getRow(rowNum); 64 // 列 65 if (rowData!=null){ 66 int cellCount = rowData.getPhysicalNumberOfCells(); 67 for (int cellNum = 0; cellNum < cellCount; cellNum++) { 68 Cell cell = rowData.getCell(cellNum); 69 if (cell!=null){ 70 int cellType = cell.getCellType(); 71 // 判断 单元格的数据类型 72 String cellValue = ""; 73 switch (cellType) { 74 case HSSFCell.CELL_TYPE_STRING: // 字符串 75 System.out.print("【String】"); 76 cellValue = cell.getStringCellValue(); 77 break; 78 case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔值 79 System.out.print("【BOOLEAN】"); 80 cellValue = String.valueOf(cell.getBooleanCellValue()); 81 break; 82 case HSSFCell.CELL_TYPE_BLANK: // 空 83 System.out.print("【BLANK】"); 84 break; 85 case HSSFCell.CELL_TYPE_NUMERIC: // 日期 86 System.out.print("【NUMERIC】"); 87 // cellValue = cell.getNumericCellValue(); 88 // 日期判断 , 如果不是日期 89 if (HSSFDateUtil.isCellDateFormatted(cell)){ // 日期 90 System.out.println("【日期】"); 91 Date date = cell.getDateCellValue(); 92 cellValue = new DateTime(date).toString("yyyy-MM-dd"); 93 }else { 94 System.out.println("【转换为字符串】"); 95 cell.setCellType(HSSFCell.CELL_TYPE_STRING); 96 cellValue = cell.toString(); 97 } 98 break; 99 case Cell.CELL_TYPE_ERROR: // 错误 100 System.out.print("【数据类型错误】"); 101 break; 102 } 103 System.out.println(cellValue); 104 } 105 } 106 } 107 } 108 stream.close(); 109 } 110 // 代码实现单元格统计 (数据=> 汇总计算) 111 @Test 112 public void testFormula() throws Exception { 113 114 // 要读取的文件 115 FileInputStream is = new FileInputStream(PATH+"计算公式.xls"); 116 // 读取到Workbook 03 117 Workbook workbook = new HSSFWorkbook(is); 118 Sheet sheet = workbook.getSheetAt(0); // 获取表 119 120 // 读取 5,1 题库(全部都是Excel录入的) 121 Row row = sheet.getRow(4); 122 Cell cell = row.getCell(0); 123 124 // 公式计算器 eval 125 HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook); 126 127 // formulaEvaluator 输出单元的内容 128 int cellType = cell.getCellType(); 129 switch (cellType){ 130 case Cell.CELL_TYPE_FORMULA: //非公式的就不需要计算了! 131 // 得到公式 132 String formula = cell.getCellFormula(); 133 System.out.println(formula); 134 135 CellValue evaluate = formulaEvaluator.evaluate(cell); 136 String cellValue = evaluate.formatAsString(); 137 System.out.println(cellValue); 138 break; 139 } 140 141 } 142 }