POI&EasyExcel

目前最流行Apache和阿里巴巴EasyExcel
pox.xml

POI:写入数据

        <!--03版本-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!--07版本-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

对Exce进行分析
工作簿：相当于整个Excel
工作表：sheet
行：
列：
03版本.xls

• 最大行数为6536

07版本.xlsx

• 最大行数没有限制

//写入03版本.xls
//创建工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建工作表---参数可有可无
        Sheet sheet = workbook.createSheet("monster");
        //创建行
        Row row = sheet.createRow(0);
        //创建列
        Cell cell = row.createCell(0);
        cell.setCellValue("今天的不开就止于此吧，明天依旧光芒万丈");
        Cell cell1 = row.createCell(1);
        String date = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell1.setCellValue(date);
        //生成一张表
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"kuang03.xls");
        workbook.write(fileOutputStream);

   //写入07版本.xlsx
  //创建工作簿
        Workbook workbook = new XSSFWorkbook();//使用XSSFWorkbook面向接口编程
        //创建工作表---参数可有可无
        Sheet sheet = workbook.createSheet("monster");
        //创建行
        Row row = sheet.createRow(0);
        //创建列
        Cell cell = row.createCell(0);
        cell.setCellValue("今天的不开就止于此吧，明天依旧光芒万丈");
        Cell cell1 = row.createCell(1);
        String date = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell1.setCellValue(date);
        //生成一张表
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"kuang03.xlsx");
        workbook.write(fileOutputStream);

---

---

大文件写入：

• 03版本（HSSFWorkbook）
  注：因为最多写入65536如果超出报错如下：

java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)

 public  void testWriteBigData() throws IOException {
        //创建工作簿
       Workbook workbook = new HSSFWorkbook();
        //创建文件
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int cell = 0; cell < 10; cell++) {
                Cell cell1 = row.createCell(cell);
                cell1.setCellValue(cell);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"big03.xls");
        workbook.write(fileOutputStream);


    }

• 07版本（XSSFWorkbook）

  • 优点：可以写入较大的数据量如20万条
  • 缺点：写入时数据非常慢，内存消耗大，会发生内存溢出

  public  void testWriteBigData07() throws IOException {
        //创建工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建文件
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int cell = 0; cell < 10; cell++) {
                Cell cell1 = row.createCell(cell);
                cell1.setCellValue(cell);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"big03.xlsx");
        workbook.write(fileOutputStream);


    }

• 07升级版（SXSSFWorkbook）
  

 public  void testWriteBigData07Super() throws IOException {
        //创建工作簿
        Workbook workbook = new SXSSFWorkbook();
        //创建文件
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int cell = 0; cell < 10; cell++) {
                Cell cell1 = row.createCell(cell);
                cell1.setCellValue(cell);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"big03.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
      



    }

POI:读数据

03版本读取

 public void ReadTest03() throws Exception {
        String PATH ="D:\\线上上课名单.xls";
        //获取输入流
        FileInputStream fileInputStream = new FileInputStream(new File(PATH));
        //创建工作簿读取
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        //获取表格
        Sheet sheet = workbook.getSheetAt(0);
        //获取到行(参数表示第几行)
        Row row = sheet.getRow(0);
        //根据行去获取列,最终就是每个单元格(参数表示第几列)
        Cell cell = row.getCell(1);
        Cell cell1 = row.getCell(2);
        Cell cell2 = row.getCell(4);
        //获取值：在获取值的时候需要注意获取值得类型
        cell.getStringCellValue();
        cell1.getStringCellValue();
        cell2.getStringCellValue();


    }

07版本读取

 public void ReadTest07() throws Exception {
        String PATH ="D:\\线上上课名单.xlsx";
        //获取输入流
        FileInputStream fileInputStream = new FileInputStream(new File(PATH));
        //创建工作簿读取
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        //获取表格
        Sheet sheet = workbook.getSheetAt(0);
        //获取到行(参数表示第几行)
        Row row = sheet.getRow(0);
        //根据行去获取列,最终就是每个单元格(参数表示第几列)
        Cell cell = row.getCell(1);
        Cell cell1 = row.getCell(2);
        Cell cell2 = row.getCell(4);
        //获取值：在获取值的时候需要注意获取值得类型
        cell.getStringCellValue();
        cell1.getStringCellValue();
        cell2.getStringCellValue();


    }

* 读取不同的数据类型
注意：类型判断

 public void ReadCellType () throws Exception {
       //获取输入流
        FileInputStream fileInputStream = new FileInputStream(new File(PATH));
        //创建工作簿读取
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        //获取表头
        Row rowTitle = sheet.getRow(0);
        //先去判断是否为空
        if(rowTitle!=null){
            //获取表头有多少个(数量)
            int number = rowTitle.getPhysicalNumberOfCells();
            for (int i = 0; i <number ; i++) {
                Cell cell = rowTitle.getCell(i);
                if(cell!=null){
                    int cellType = cell.getCellType();
                    String stringCellValue = cell.getStringCellValue();
                    System.out.print(stringCellValue+"|");
                }
            }
        }
        //获取表中所有行
        int numberRow = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i <numberRow ; i++) {
            //获取行
            Row row = sheet.getRow(i);
            //判断行是否为空
            if(row!=null){
                //获取到当前行的所有列
                int numberCell = rowTitle.getPhysicalNumberOfCells();
                for (int j = 0; j <numberCell ; j++) {
                    Cell cell = row.getCell(j);
                    //匹配列的数据类型
                    if(cell!=null){
                        int cellType = cell.getCellType();
                        String cellValue="";
                        switch (cellType){
                            case HSSFCell.CELL_TYPE_STRING://字符串
                                System.out.print("【STRING】");
                                  cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN://布尔                          System.out.print("【BOOLEAN】");
                                  cellValue = cell.getBooleanCellValue()+"";
                                break;
                            case HSSFCell.CELL_TYPE_BLANK://空
                                System.out.print("【BLANK】");
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC://数字又划分（日期/普通数字）
                                System.out.print("【NUMERIC】");
                                if(HSSFDateUtil.isCellDateFormatted(cell)){//日期
                                    System.out.print("【日期】");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");

                                }else {
                                    //不是日期转换成字符串防止数字过长
                                    System.out.print("【数字转为字符串输出】");
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR://字符串
                                System.out.print("【ERROR】");
                                break;

                        }
                        System.out.print(cellValue);
                    }

                }
                System.out.println();
            }
        }

        fileInputStream.close();
    }

EasyExcel

第一步：导入依赖

 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>easyexcel</artifactId>
     <version>2.1.6</version>
 </dependency>