POI的使用（Excel的导入和导出）

遇到了需要从Excel导入数据或导出数据的需求，写入数据到Excel和写入到文件没有本质区别，都在IO的范畴，但是使用原生的IO来处理比较麻烦，所以我们要学习使用第三方库来完成；

1. POI:

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

POI的结构：

**HSSF － 提供读写Microsoft Excel XLS格式档案的功能。 03版本
XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。 07版本 **
HWPF － 提供读写Microsoft Word DOC格式档案的功能。
HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
HDGF － 提供读Microsoft Visio格式档案的功能。
HPBF － 提供读Microsoft Publisher格式档案的功能。
HSMF － 提供读Microsoft Outlook格式档案的功能。

依赖：

<!--            xls03-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
<!--            xls07-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

POI-Excel写：

操作非常的简单，就是按照工作簿->表->行->单元格来逐个操作的；03版本和07版本除了对象不一样，其他方法都是相同的，最后注意
文件后缀，03版本为xls，07版本是xlsx。

简单写入：

static String PATH = "/home/nzy/IdeaProjects/";
    @Test
    public void EexclWrite03() throws IOException {
//        创建工作簿
        Workbook workbook = new HSSFWorkbook();
//         创建工作表
        Sheet sheet = workbook.createSheet("Excel测试表");
//          创建行
        Row row = sheet.createRow(0);
//        创建单元格
        Cell cell00 = row.createCell(0);
        cell00.setCellValue("name");

        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "Excel测试表.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        System.out.println("end");
    }

    @Test
    public void EexclWrite07() throws IOException {
//        创建工作簿
        Workbook workbook = new XSSFWorkbook();
//         创建工作表
        Sheet sheet = workbook.createSheet("Excel测试表");
//          创建行
        Row row0 = sheet.createRow(0);
        Row row1 = sheet.createRow(1);
//        创建单元格
        Cell cell00 = row0.createCell(0);
        cell00.setCellValue("name");
        Cell cell01 = row0.createCell(1);
        cell01.setCellValue("超人");
        Cell cell10 = row1.createCell(0);
        cell10.setCellValue("时间");
        Cell cell11 = row1.createCell(1);
        cell11.setCellValue(new DateTime().toString("yyyy-MM-dd"));

        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "Excel07测试表.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        System.out.println("end");
    }

大文件写：

@Test
    public void ExcelWrite03BigDate() throws IOException {
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet1 = workbook.createSheet("sheet1");
        for (int i = 0; i <= 65535; i++){
            Row row = sheet1.createRow(i);
            for (int j = 0; j <= 10; j++){
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "bigDate03.xls");
        workbook.write(fileOutputStream);

        fileOutputStream.close();
        System.out.println("end");

    }

    @Test
    public void ExcelWrite07BigDate() throws IOException {
        //带缓存的写入
        long begin = System.currentTimeMillis();
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        Sheet sheet1 = workbook.createSheet("sheet1");
        for (int i = 0; i <= 100000; i++){
            Row row = sheet1.createRow(i);
            for (int j = 0; j <= 10; j++){
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "bigDate07.xls");
        workbook.write(fileOutputStream);
        //清除临时文件
        workbook.dispose();
        fileOutputStream.close();
        long end = System.currentTimeMillis();

        System.out.println((double) (end-begin)/1000);
        System.out.println("end");
    }

POI-Excel读：

package com.hd.test;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Date;

public class ExcelReadTest {
    static String PATH = "/home/nzy/IdeaProjects/";

    @Test
    public void ExcelRead07() throws IOException {
        FileInputStream inputStream = new FileInputStream(PATH + "t_order.xlsx");
        //从流中获取工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        //获取表
        XSSFSheet sheet = workbook.getSheetAt(0);
        //获取title 即第一行
        XSSFRow title = sheet.getRow(0);
        //获取第一行的单元格数
        int cellCount = title.getPhysicalNumberOfCells();
        if (title != null){
            for (int cellNum = 0; cellNum < cellCount; cellNum++){
                XSSFCell cell = title.getCell(cellNum);
                if (cell != null){
                    String stringCellValue = cell.getStringCellValue();
                    int cellType = cell.getCellType();
                    System.out.print(stringCellValue + " | ");
                }
            }
            System.out.println();
        }
        //获取表的行数
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < physicalNumberOfRows; rowNum++) {
            XSSFRow row = sheet.getRow(rowNum);
            if (row != null){
                for (int collNum = 0; collNum < cellCount; collNum++) {
                    XSSFCell cell = row.getCell(collNum);
                    String cellValue = null;
                    //根据cell的类型来对进行
                    if (cell != null) {
                        switch (cell.getCellType()){
                            case HSSFCell.CELL_TYPE_STRING:
                                System.out.print("[String]");
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                System.out.print("[Blank]");
                                cellValue = null;
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                System.out.print("[Boolean]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                System.out.print("[numeric]");
                                if (HSSFDateUtil.isCellDateFormatted(cell)){
                                    System.out.print("[日期]");
                                    Date dateCellValue = cell.getDateCellValue();
                                    cellValue = new DateTime(dateCellValue).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("[错误]");
                                break;
                            default:
                                System.out.print("[默认]");
                        }
                    }
                    System.out.print(cellValue);
                }
                System.out.println();
            }
        }
    }
}