当前位置 博文首页 > 范栩的博客：Poi处理Excel学习
? 先导入依赖
<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 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
