Apache POI Excel读取和写入

Apache POI Excel读取和写入

1.依赖

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <!--07excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>

2.测试代码

**
 * 写出文件
 */
public class ExcelWriteTest {

    // 简单测试03
    @Test
    public void test1() throws Exception {
        // 创建工作簿
        Workbook workbook = new HSSFWorkbook();
        // 创建工作表,默认名称为sheet1
        Sheet sheet = workbook.createSheet();
        // 创建行
        Row row = sheet.createRow(0);
        // 创建列
        Cell cell = row.createCell(0);
        // 设置值
        cell.setCellValue("你好啊");
        // 创建输出流
        FileOutputStream fos = new FileOutputStream("h:\\1.xls");
        // 向流中写入数据
        workbook.write(fos);
        // 关闭资源
        fos.close();
    }

    // 写出很多数据03,03版excel最多只能写出65536行.
    @Test
    public void test2() throws IOException {
        // 创建工作簿
        Workbook workbook = new HSSFWorkbook();
        // 创建工作表
        Sheet sheet = workbook.createSheet("feishifu");
        // 循环创建行和列(单元格)并赋值
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        // 创建输出流对象
        FileOutputStream fos = new FileOutputStream("h:\\2.xls");
        // 向流中写入数据
        workbook.write(fos);
        // 关闭流释放资源
        fos.close();
    }

    // 写出很多数据07,但是速度比较慢
    @Test
    public void test4() throws IOException {
        // 创建工作簿对象
        Workbook workbook = new XSSFWorkbook();
        // 创建工作表对象
        Sheet sheet = workbook.createSheet("费师傅");
        // 循环创建行和列对象,并赋值
        for (int rowNum = 0; rowNum < 100000; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        // 创建输出流对象
        FileOutputStream fos = new FileOutputStream("h:\\3.xlsx");
        // 向流中写入数据
        workbook.write(fos);
        // 关闭流,释放资源
        fos.close();
    }

    // 写出很多数据07,但是速度快,并测试需要的时间,并需要清除临时文件
    @Test
    public void test5() throws IOException {
        long begin = System.currentTimeMillis();
        // 创建工作簿对象
        Workbook workbook = new SXSSFWorkbook();
        // 创建工作表对象
        Sheet sheet = workbook.createSheet("费师傅");
        // 循环创建行和列对象并赋值
        for (int rowNum = 0; rowNum < 100000; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        // 创建输出流对象
        FileOutputStream fos = new FileOutputStream("h:\\4.xlsx");
        // 向流中写入数据
        workbook.write(fos);
        // 关闭流,释放资源
        fos.close();
        // 清除临时文件
        ((SXSSFWorkbook) workbook).dispose();
        long end = System.currentTimeMillis();
        // 打印所用的时间
        System.out.println((double) (end - begin) / 1000 + "秒");
    }
}

/**
 * 读文件
 */
public class ExcelReadTest {

    // 简单的读取测试
    @Test
    public void test1() throws IOException {
        // 创建输入流对象
        FileInputStream fis = new FileInputStream("h:\\1.xls");
        // 新建工作簿对象
        Workbook workbook = new HSSFWorkbook(fis);
        // 获取工作表对象
        Sheet sheet = workbook.getSheetAt(0);
        // 获取行对象
        Row row = sheet.getRow(0);
        // 获取列对象
        Cell cell = row.getCell(0);
        // 获取值并打印输出
        String s = cell.getStringCellValue();
        System.out.println(s);
    }

    // 读取一行的所有内容
    @Test
    public void test2() throws IOException {
        // 创建输入流对象
        FileInputStream fis = new FileInputStream("h:\\2.xls");
        // 创建工作簿对象
        Workbook workbook = new HSSFWorkbook(fis);
        // 获取工作表对象
        Sheet sheet = workbook.getSheetAt(0);
        // 获取标题行对象
        Row row = sheet.getRow(0);
        int cells = row.getPhysicalNumberOfCells();
        for (int cellNum = 0; cellNum < cells; cellNum++) {
            Cell cell = row.getCell(cellNum);
            String s = cell.getStringCellValue();
            System.out.println(s);
        }
        // 获取内容
        int rows = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rows; rowNum++) {
            Row row1 = sheet.getRow(rowNum);
            if (row1 != null) {
                int cells1 = row1.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cells1; cellNum++) {
                    Cell cell = row1.getCell(cellNum);
                    if (cell != null) {
                        int cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING:
                                System.out.println("类型是字符串");
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:// 分为日期和普通数字
                                System.out.println("类型是数字");
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    cellValue = date.toString();
                                } else {
                                    // 转为字符串防止数字过长
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    String s = cell.toString();
                                    System.out.println(s);
                                }
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                System.out.println("类型是布尔");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                System.out.println("类型是空");
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:
                                System.out.println("类型错误");
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        fis.close();
    }
}