Poi处理Excel学习

POI学习

​ 先导入依赖

<dependencies>
        <!--xls(03)-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>

    <!--xlsx(07)-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>
    <!--日期格式化工具-->
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.10.1</version>
    </dependency>
    <!--test-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>

Excel2003版本的最多只能放65536行数据(xls)

Excel2007版本没有这个限制(xlsx)

Excel组成

• 工作簿
  • 工作表
    • 行
    • 列
      • 单元格

大文件写HSSF

缺点：最多只能处理65536行，否则会抛出异常

优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快

大文件写XSSF

缺点：写数据的时候非常慢，非常耗内存，也会发生内存溢出，如100万条

优点：可以写较大的数据量，如20万条

大文件写SXSSF

优点：可以写较大的数据量，如20万条

操作Excel

创建一个Excel 07版本的xlsx文件

package com.muyu.main;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.joda.time.DateTime;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriteTest {
	private static String path = "E:\\code\\IDEA code\\ControlExcel\\Excel-poi";
	public static void main(String[] args) throws IOException {
		//1. 创建工作簿
		Workbook workbook = new SXSSFWorkbook();
		//2. 创建一个工作表
		Sheet sheet = workbook.createSheet("范栩实践表");
		//3. 创建一个行
		Row row1 = sheet.createRow(0);
		//4. 创建一个单元格
		Cell cell11 = row1.createCell(0);
		cell11.setCellValue("今日新学：");
		Cell cell12 = row1.createCell(1);
		cell12.setCellValue("poi");
		//第二行
		Row row2 = sheet.createRow(1);
		Cell cell21 = row2.createCell(0);
		Cell cell22 = row2.createCell(1);

		String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
		cell21.setCellValue("统计时间：");
		cell22.setCellValue(time);

		FileOutputStream fileOutputStream = new FileOutputStream(path + "\\FanXu.xlsx");
		workbook.write(fileOutputStream);
		fileOutputStream.close();
		
	}
}

像xls中写入大量数据

@Test
public void xls65536Test() throws IOException {
    //执行速度快
    long begain = System.currentTimeMillis();
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("66636");
    for (int i = 0; i <65536; i++) {
        Row row = sheet.createRow(i);
        for (int j = 0; j <10 ; j++) {
            Cell cell = row.createCell(j);
            cell.setCellValue(j);
        }
    }
    FileOutputStream fileOutputStream = new FileOutputStream(path + "\\xls65536Test.xls");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    System.out.println("over");
    long end = System.currentTimeMillis();
    double time = (double)(end-begain)/1000 ;
    System.out.println(time);

}
//over     输出
//1.776

向xlsx中写入大量数据

@Test
public void xlsx65536Test() throws IOException {
    //执行速度慢  1万条执行了2.682秒
    long begain = System.currentTimeMillis();
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("66636");
    for (int i = 0; i <10000; i++) {
        Row row = sheet.createRow(i);
        for (int j = 0; j <10 ; j++) {
            Cell cell = row.createCell(j);
            cell.setCellValue(j);
        }
    }
    FileOutputStream fileOutputStream = new FileOutputStream(path + "\\xlsx65536Test.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    System.out.println("over");
    long end = System.currentTimeMillis();
    double time = (double)(end-begain)/1000 ;
    System.out.println(time);

}
//over  输出
//2.682

优化！第二种方法向xlsx中写入大量数据

@Test
public void sxlsx65536Test() throws IOException {
    //20万条执行了4.205秒
    long begain = System.currentTimeMillis();
    Workbook workbook = new SXSSFWorkbook();
    Sheet sheet = workbook.createSheet("66636");
    for (int i = 0; i <200000; i++) {
        Row row = sheet.createRow(i);
        for (int j = 0; j <10 ; j++) {
            Cell cell = row.createCell(j);
            cell.setCellValue(j);
        }
    }
    FileOutputStream fileOutputStream = new FileOutputStream(path + "\\xlsx65536Test.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    ((SXSSFWorkbook)workbook).dispose();
    System.out.println("over");
    long end = System.currentTimeMillis();
    double time = (double)(end-begain)/1000 ;
    System.out.println(time);

}
//over   输出
//4.205

读Excel的操作

package com.muyu.main;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelReadTest {
	private static String path = "E:\\code\\IDEA code\\ControlExcel\\Excel-poi";

	@Test
	public void getExcelValue() throws IOException {
		FileInputStream fileInputStream = new FileInputStream("FanXu.xlsx");
		Workbook workbook = new XSSFWorkbook(fileInputStream);
		Sheet sheet = workbook.getSheet("范栩实践表");
		Row row = sheet.getRow(0);
		Cell cell = row.getCell(1);
		System.out.println(cell.getStringCellValue());
		fileInputStream.close();
	}
}