1、POI简介与依赖导入

poi官网：poi.apache.org
03版的数据只能有65536条
07版的则无限制

  1.         <!--xls(03)-->
  2.         <dependency>
  3.             <groupId>org.apache.poi</groupId>
  4.             <artifactId>poi</artifactId>
  5.             <version>3.17</version>
  6.         </dependency>
  7.         <!--xlsx(07)-->
  8.         <dependency>
  9.             <groupId>org.apache.poi</groupId>
  10.             <artifactId>poi-ooxml</artifactId>
  11.             <version>3.17</version>
  12.         </dependency>
  13.         <!--日期格式化工具-->
  14.         <dependency>
  15.             <groupId>junit</groupId>
  16.             <artifactId>junit</artifactId>
  17.             <version>4.13.2</version>
  18.         </dependency>
  19.         <!--单元测试工具-->
  20.         <dependency>
  21.             <groupId>joda-time</groupId>
  22.             <artifactId>joda-time</artifactId>
  23.             <version>2.10.10</version>
  24.         </dependency>

2、POI创建Excel文件

1、poi创建xlsx03==>HSSFWorkbook

  1.     String PATH="D:\\AAjava_project\\parent\\poi";
  2.     @Test
  3.     public void testCreateExcel03() throws IOException {
  5.         Workbook workbook = new HSSFWorkbook();
  6.         Sheet sheet = workbook.createSheet("sheet1");
  7.         FRow row1 = sheet.createRow(0);//创建第一行
  8.         //第一行第一个单元格及其值
  9.         FCell cell11 = row1.createCell(0);
  10.         cell11.setCellValue("创建Excel表格cell11");
  11.         //第二行第二个单元格及其值
  12.         FCell cell12 = row1.createCell(1);
  13.         cell12.setCellValue("创建Excel表格cell12");
  15.         //创建第二行并且设置单元格内的值
  16.         Row row2 = sheet.createRow(1);//创建第一行
  17.         //第一行第一个单元格及其值
  18.         Cell cell21 = row2.createCell(0);
  19.         cell21.setCellValue("创建Excel表格cell21");
  20.         //第二行第二个单元格及其值
  21.         Cell cell22 = row2.createCell(1);
  22.         cell22.setCellValue("创建Excel表格cell22");
  24.         FileOutputStream fileOutputStream = new FileOutputStream(PATH + "创建Excel表格03.xls");
  26.         workbook.write(fileOutputStream);
  27.         fileOutputStream.close();
  29.         System.out.println("Excel生成完毕");
  30.     }

2、poi创建xlsx07==>XSSFWorkbook

  1.      String PATH="D:\\AAjava_project\\parent\\poi";
  2.     @Test
  3.     public void testCreateExcel07() throws IOException {
  5.         Workbook workbook = new XSSFWorkbook();
  6.         Sheet sheet = workbook.createSheet("sheet1");
  7.         FRow row1 = sheet.createRow(0);//创建第一行
  8.         //第一行第一个单元格及其值
  9.         FCell cell11 = row1.createCell(0);
  10.         cell11.setCellValue("创建Excel表格cell11");
  11.         //第二行第二个单元格及其值
  12.         FCell cell12 = row1.createCell(1);
  13.         cell12.setCellValue("创建Excel表格cell12");
  15.         //创建第二行并且设置单元格内的值
  16.         Row row2 = sheet.createRow(1);//创建第一行
  17.         //第一行第一个单元格及其值
  18.         Cell cell21 = row2.createCell(0);
  19.         cell21.setCellValue("创建Excel表格cell21");
  20.         //第二行第二个单元格及其值
  21.         Cell cell22 = row2.createCell(1);
  22.         cell22.setCellValue("创建Excel表格cell22");
  24.         FileOutputStream fileOutputStream = new FileOutputStream(PATH + "创建Excel表格03.xls");
  26.         workbook.write(fileOutputStream);
  27.         fileOutputStream.close();
  29.         System.out.println("Excel生成完毕");
  30.     }

3、HSSFWorkBook导入大量数据

03.xls
当行数超出65536时，程序会出现错误

  1. @Test
  2.     public void testCreateExcel03BigData() throws IOException {
  4.         long startTime = System.currentTimeMillis();
  6.         Workbook workbook = new HSSFWorkbook();
  7.         Sheet sheet = workbook.createSheet();
  8.         Row row;
  9.         for(int i=0;i<65536;i++){
  10.             row = sheet.createRow(i);
  11.             for(int j=0;j<10;j++){
  12.                row.createCell(j).setCellValue(j);
  13.             }
  14.         }
  15.         System.out.println("数据导入完毕");
  16.         FileOutputStream fileOutputStream = new FileOutputStream(PATH + "xls03导入大量数据.xls");
  17.         workbook.write(fileOutputStream);
  18.         fileOutputStream.close();
  19.         System.out.println("xls03导入大量数据完成");
  21.         long endTime = System.currentTimeMillis();
  22.         System.out.println((double)(endTime-startTime)/1000);
  23.     }

4、XSSFWorkBook导入大量数据

07.xlsx
对数据行数没有限制，但是导入效率会降低

  1.     @Test
  2.     public void testCreateExcel07BigData() throws IOException {
  4.         long startTime = System.currentTimeMillis();
  6.         Workbook workbook = new XSSFWorkbook();
  7.         Sheet sheet = workbook.createSheet();
  8.         Row row;
  9.         for(int i=0;i<100000;i++){
  10.             row = sheet.createRow(i);
  11.             for(int j=0;j<10;j++){
  12.                 row.createCell(j).setCellValue(j);
  13.             }
  14.         }
  15.         System.out.println("数据导入完毕");
  16.         FileOutputStream fileOutputStream = new FileOutputStream(PATH + "xls07导入大量数据.xlsx");
  17.         workbook.write(fileOutputStream);
  18.         fileOutputStream.close();
  19.         System.out.println("xls03导入大量数据完成");
  21.         long endTime = System.currentTimeMillis();
  22.         System.out.println((double)(endTime-startTime)/1000);
  23.     }

5、解决XSSFWorkBook效率问题

解决效率问题：使用SXSSFWorkBook
写数据更快，占用更少的内存
会产生临时文件，需要清理临时文件

  1.     @Test
  2.     public void testCreateExcel07BigDatas() throws IOException {
  4.         long startTime = System.currentTimeMillis();
  6.         Workbook workbook = new SXSSFWorkbook();
  7.         Sheet sheet = workbook.createSheet();
  8.         Row row;
  9.         for(int i=0;i<100000;i++){
  10.             row = sheet.createRow(i);
  11.             for(int j=0;j<10;j++){
  12.                 row.createCell(j).setCellValue(j);
  13.             }
  14.         }
  15.         System.out.println("数据导入完毕");
  16.         FileOutputStream fileOutputStream = new FileOutputStream(PATH + "xls07导入大量数据s.xlsx");
  17.         workbook.write(fileOutputStream);
  21.         fileOutputStream.close();
  22.         //清理临时文件
  23.         ((SXSSFWorkbook)workbook).dispose();
  25.         System.out.println("xls03导入大量数据完成");
  27.         long endTime = System.currentTimeMillis();
  28.         System.out.println((double)(endTime-startTime)/1000);
  29.     }

3、POI读取Excel文件内容

1、HSSFWorkbook读取Excel

03.xls读取xls文件

  1. @Test
  2.     public void testgetData03() throws IOException {
  3.         FileInputStream fileInputStream = new FileInputStream("D:\\AAjava_project\\parent\\poi创建Excel表格.xls");
  4.         Workbook workbook = new HSSFWorkbook(fileInputStream);
  5.         Sheet sheet = workbook.getSheetAt(0);
  6.         Row row = sheet.getRow(0);
  7.         //获取为字符串的值
  8.         //获取的类型值与表格中的值的类型不匹配则报错
  9.         System.out.println(row.getCell(0).getStringCellValue());
  11.         fileInputStream.close();
  12.     }

2、HSSFWorkbook读取Excel

07.xlsx读取xlsx文件

  1. @Test
  2.     public void testgetData07() throws IOException {
  3.         FileInputStream fileInputStream = new FileInputStream("D:\\AAjava_project\\parent\\poi创建Excel表格07.xlsx");
  4.         Workbook workbook = new XSSFWorkbook(fileInputStream);
  5.         Sheet sheet = workbook.getSheetAt(0);
  6.         Row row = sheet.getRow(0);
  7.         //获取为字符串的值
  8.         //获取的类型值与表格中的值的类型不匹配则报错
  9.         //System.out.println(row.getCell(0).getStringCellValue());
  10.         System.out.println(row.getCell(1).getStringCellValue());
  12.         fileInputStream.close();
  13.     }

3、读取不同类型的数据（重点）

4、了解计算公式

  1. 100
  2. 200
  3. 400
  4. 700
  1. @Test
  2.     public void testFormula() throws IOException {
  3.         FileInputStream fileInputStream = new FileInputStream("D:\\AAjava_project\\parent\\公式.xls");
  4.        Workbook workbook = new HSSFWorkbook(fileInputStream);
  5.         Sheet sheet = workbook.getSheetAt(0);
  7.         Row row = sheet.getRow(4);
  8.         Cell cell = row.getCell(0);
  10.         //拿到计算公式
  11.        FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
  13.        //输出单元内容
  14.         int cellType = cell.getCellType();
  15.         switch (cellType){
  16.             case Cell.CELL_TYPE_FORMULA:
  17.                 String cellFormula = cell.getCellFormula();
  18.                 System.out.println(cellFormula);
  20.                 //计算
  21.                 CellValue evaluate = formulaEvaluator.evaluate(cell);
  22.                 String cellValue = evaluate.formatAsString();
  23.                 System.out.println(cellValue);
  24.         }
  25.     }

4、EasyExcel的使用

1、导入依赖

  1.  <dependency>
  2.             <groupId>com.alibaba</groupId>
  3.             <artifactId>easyexcel</artifactId>
  4.             <version>2.2.0-beta2</version>
  5.         </dependency>

2、创建excel表格并写入数据

1、DemoData

  1. @Data
  2. public class DemoData {
  4.     @ExcelProperty("字符串标题")
  5.     private String string;
  6.     @ExcelProperty("日期标题")
  7.     private Date date;
  8.     @ExcelProperty("数字标题")
  9.     private Double doubleData;
  11.     @ExcelIgnore//忽略该字段
  12.     private String ignore;
  13. }

2、EasyTest

  1. public class EasyTest {
  3.     private List<DemoData> data(){
  4.         ArrayList<DemoData> list = new ArrayList<>();
  5.         for(int i=0;i<10;i++){
  6.             DemoData data = new DemoData();
  7.             data.setString("字符串"+i);
  8.             data.setDate(new Date());
  9.             data.setDoubleData(0.56);
  10.             list.add(data);
  11.         }
  12.         return list;
  13.     }
  15.     @Test
  16.     public void testeasy(){
  17.         String fileName="D:\\AAjava_project\\parent\\EasyTest.xlsx";
  18.         EasyExcel.write(fileName,DemoData.class).sheet("模板").doWrite(data());
  19.     }
  20. }

3、从