poi导入导出比较常用
这里举例说明
HSSF
XSSF
SXSSF
HSSFWorkbook
最多只能处理65536行文件
优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快
XSSFWorkbook
写数据时非常耗费内存,百万条数据内存溢出,例如几十万较快
SXSSFWorkbook
需占用大量内存,生成临时文件 要清除
注意excel 03 07后缀不一样,xls,xlsx
HSSFWorkbook如下示例:
package com.kkk;
import java.io.FileOutputStream;
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.joda.time.DateTime;
import org.junit.Test;
public class Write11 {
String PATH ="F:\\11111111111111\\today\\";
@Test
public void write2003() throws Exception {
// 1.创建工作簿
Workbook workbook = new HSSFWorkbook();
// 2.创建一个表
Sheet sheet = workbook.createSheet("创建一个表");
// 3.创建一行
Row row = sheet.createRow(0); //第一个默认0
// 4.创建一个单元格
Cell cell1 = row.createCell(0);
cell1.setCellValue("第一列");
Cell cell2 = row.createCell(1);
cell2.setCellValue("第二列");
// 第二行
Row row1 = sheet.createRow(1);
Cell cell21 = row1.createCell(0);
cell21.setCellValue("第二行第一列");
Cell cell22 = row1.createCell(1);
String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(s);
// 生成表(IO流)
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "张三李四dw.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
System.out.println("生成文件");
}
}
XSSFWorkbook如下示例
package com.kkk;
import java.io.FileOutputStream;
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.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;
public class Write07 {
String PATH ="F:\\11111111111111\\today\\";
@Test
public void write2007() throws Exception {
// 1.创建工作簿
Workbook workbook = new XSSFWorkbook();
// 2.创建一个表
Sheet sheet = workbook.createSheet("创建一个表");
// 3.创建一行
Row row = sheet.createRow(0);
// 4.创建一个单元格
Cell cell1 = row.createCell(0);
cell1.setCellValue("第一列");
Cell cell2 = row.createCell(1);
cell2.setCellValue("第二列");
// 第二行
Row row1 = sheet.createRow(1);
Cell cell21 = row1.createCell(0);
cell21.setCellValue("第二行第一列");
Cell cell22 = row1.createCell(1);
String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(s);
// 生成表(IO流) 03xls 07xlsx
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "张三李四dw.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
System.out.println("生成文件");
}
}
SXSSFWorkbook如下示例
package com.kkk;
import java.io.FileOutputStream;
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.junit.Test;
public class WriteSxss {
String PATH ="F:\\11111111111111\\today\\";
@Test
public void testWriteData() throws Exception {
// 时间
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);
}
}
System.out.println("========");
FileOutputStream stream = new FileOutputStream(PATH+"张三测试S.xlsx");
workbook.write(stream);
stream.close();
// 清除临时文件
((SXSSFWorkbook)workbook).dispose();
long end = System.currentTimeMillis();
System.out.println((double)(end-begin)/1000);
}
}
生成excel文件如下:
读excel表中的内容示例
其中有很多方法,需自己去查看体验 并实际应用,在此不详细介绍,掌握方法即可,相通的。
package com.aaa;
import java.io.FileInputStream;
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.junit.Test;
public class exRead {
String PATH ="F:\\11111111111111\\today\\";
@Test
public void read03() throws Exception {
// 获取文件 读取
FileInputStream inputStream = new FileInputStream(PATH+"张三李四dw.xls");
// 工作簿
Workbook workbook = new HSSFWorkbook(inputStream);
// 通过下标获取sheet,第1个
Sheet sheet = workbook.getSheetAt(0);
//Sheet sheet = workbook.getSheet("创建一个表");
// 获取第一行
Row row = sheet.getRow(0);
// 第一行第一格
Cell cell = row.getCell(0);
// 获取该cell中的值
System.out.println(cell.getStringCellValue());
//关闭流
inputStream.close();
}
}
另外,可学习使用Alibaba的EasyEscelhttps://www.yuque.com/easyexcel/doc/easyexcel,