使用场景
1,将用户信息导出为excel 表格(导出数据)
2,将Excel 表中的幸喜录入到网站数据库
基本功能
HSSF – 提供读写excel 格式档案的功能
XSSF–提供读写EXCEL 格式档案的功能
HWPF --提供读写WORD 格式档案的功能
HSLF–提供读写ppt 档案的功能
HDGF–提供读写VISIO 格式的功能
简单的03,07 版本的写入
导入依赖
<!-- pom中导入包-->
<!-- DateTimeFormat注解-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.9.4</version>
</dependency>
<!--文件上传组件-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>
<!--读取excel文件-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
import org.apache.poi.hssf.usermodel.HSSFSheet;
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.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
/**
* @author fjj
* @date 2021/1/22 15:47
*/
public class Demo1 {
@Test
public void poi03() throws Exception {
//创建一个工作
Workbook workbook = new HSSFWorkbook();
//创建工作表
Sheet sheet01 = workbook.createSheet("fjj 的表");
//创建一行
Row row = sheet01.createRow(0);
//创建单元格
Cell cell = row.createCell(0);
cell.setCellValue("娇娇的第一行数据");
//创建一行
Row row1 = sheet01.createRow(1);
//创建单元格
Cell cell1 = row1.createCell(0);
String dateTime = new DateTime().toString("yyyy-MM-dd");
cell1.setCellValue(dateTime);
//写入
FileOutputStream fileOutputStream = new FileOutputStream("E:\\实习的代码\\Sprint-poi\\" + "娇娇.xls");
workbook.write(fileOutputStream);
System.out.println("ok");
fileOutputStream.close();
}
@Test
public void poi07() throws IOException {
//创建工作簿
XSSFWorkbook sheets = new XSSFWorkbook();
//创建表
XSSFSheet sheet = sheets.createSheet("娇娇的07版");
//创建一行
Row row = sheet.createRow(0);
//创建单元格
Cell cell = row.createCell(0);
cell.setCellValue("娇娇的第一行数据");
//创建一行
Row row1 = sheet.createRow(1);
//创建单元格
Cell cell1 = row1.createCell(0);
String dateTime = new DateTime().toString("yyyy-MM-dd");
cell1.setCellValue(dateTime);
//写入
FileOutputStream fileOutputStream = new FileOutputStream("E:\\实习的代码\\Sprint-poi\\" + "娇娇07.xlsx");
sheets.write(fileOutputStream);
System.out.println("ok");
fileOutputStream.close();
}
}
批量插入数据
大文件写入HSSF 03 版本
优点:过程写入缓存,不操作磁盘,最后一次性写入磁盘,速度快
缺点:最多只能处理65536行,否则会抛出异常
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.junit.Test;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
/**
* @author fjj
* @date 2021/1/22 16:26
*/
public class Demo2 {
@Test
public void poi03() throws IOException {
long start=System.currentTimeMillis();
//创建工作簿
HSSFWorkbook sheets = new HSSFWorkbook();
//创建表
HSSFSheet sheet = sheets.createSheet("批量添加03的");
for (int i = 0; i <65536 ; i++) {
HSSFRow row = sheet.createRow(i);
for (int cell = 0; cell < 10; cell++) {
HSSFCell cell1 = row.createCell(cell);
cell1.setCellValue(cell);
}
}
//写入
FileOutputStream fileOutputStream = new FileOutputStream("E:\\实习的代码\\Sprint-poi\\" + "批量添加03的.xls");
sheets.write(fileOutputStream);
long end=System.currentTimeMillis();
System.out.println("ok");
fileOutputStream.close();
System.out.println((double )(end-start)/1000+"s");
}
}
结果
我们可以考虑用07版本的XSSF
优点:可以写较大的数据量,如20万条
缺点:写数据时速度非常慢,非常耗内存,也会发生内存溢出,如100万条
@Test
public void poi07() throws IOException {
long start=System.currentTimeMillis();
//创建工作簿
XSSFWorkbook sheets = new XSSFWorkbook();
//创建表
XSSFSheet sheet = sheets.createSheet("批量添加07的");
for (int i = 0; i <65536 ; i++) {
XSSFRow row = sheet.createRow(i);
for (int cell = 0; cell < 10; cell++) {
XSSFCell cell1 = row.createCell(cell);
cell1.setCellValue(cell);
}
}
//写入
FileOutputStream fileOutputStream = new FileOutputStream("E:\\实习的代码\\Sprint-poi\\" + "批量添加07的.xlsx");
sheets.write(fileOutputStream);
long end=System.currentTimeMillis();
System.out.println("ok");
fileOutputStream.close();
System.out.println((double )(end-start)/1000+"s");
}
}
如何改变这种情况呢,我们可以优化,加入缓存
大文件写SXSSF
优点可以写非常大的数据量,100万,写的速度非常块,占少的内存,但那时会有临时文件,需要清理临时文件
SXSSFWorkbook-来至官方的解释:实现"BigGridDemo"策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内
存,因为任何时候只有可配置的行部分被保存在内存中。
请注意,仍然可能会消耗大量内存,这些内存基于您正在使用的功能,例如合并区域,注释…仍然只存储在内存中,因此如果广
泛使用,可能需要大量内存。
@Test
public void poiS07() throws IOException {
long start=System.currentTimeMillis();
//创建工作簿
SXSSFWorkbook sheets = new SXSSFWorkbook();
//创建表
SXSSFSheet sheet = sheets.createSheet("批量添加07的");
for (int i = 0; i <65536 ; i++) {
SXSSFRow row = sheet.createRow(i);
for (int cell = 0; cell < 10; cell++) {
SXSSFCell cell1 = row.createCell(cell);
cell1.setCellValue(cell);
}
}
//写入
FileOutputStream fileOutputStream = new FileOutputStream("E:\\实习的代码\\Sprint-poi\\" + "批量添加S07的.xlsx");
sheets.write(fileOutputStream);
long end=System.currentTimeMillis();
System.out.println("ok");
fileOutputStream.close();
boolean dispose = sheets.dispose();
System.out.println("dispose = " + dispose);
System.out.println((double )(end-start)/1000+"s");
}
}
读取值这个获取得是03版本得
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
/**
* @author fjj
* @date 2021/1/22 18:01
*/
public class Demo3 {
@Test
public void getpoi03() throws Exception {
//获取打算写入得流
FileInputStream in = new FileInputStream("E:\\实习的代码\\Sprint-poi\\" + "批量添加03的.xls");
//创建工作
HSSFWorkbook sheets = new HSSFWorkbook(in);
HSSFSheet sheetAt = sheets.getSheetAt(0);
HSSFRow row = sheetAt.getRow(0);
HSSFCell cell = row.getCell(1);
System.out.println(cell.getNumericCellValue());
in.close();
}
}
获取表中复杂数据
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.FileNotFoundException;
import java.util.Date;
/**
* @author fjj
* @date 2021/1/22 18:34
*/
public class Demo4 {
@Test
public void getpoi07() throws Exception {
//获取输入流
FileInputStream in = new FileInputStream("E:\\实习的代码\\Sprint-poi\\" + "高等学校学生信息核准登记表(1).xlsx");
//创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook(in);
//获取工作表
XSSFSheet sheet = workbook.getSheetAt(0);
//获取标题
XSSFRow row = sheet.getRow(0);
if (row!=null){
//这个方法是获取所有一行得总数
int cells = row.getPhysicalNumberOfCells();
for (int cellnum = 0; cellnum <cells ; cellnum++) {
//获取本列得所有数据
XSSFCell cell = row.getCell(cellnum);
if (cell!=null){
//获取属性
int type = cell.getCellType();
//输出此函得
System.out.print(cell.getStringCellValue()+"\t");
}
}
System.out.println();
}
//获取表中得数据
int numberOfRows = sheet.getPhysicalNumberOfRows();
for (int rownum = 0; rownum < numberOfRows; rownum++) {
XSSFRow row1 = sheet.getRow(rownum);
if (row1!=null){
//读取列
int cells = row.getPhysicalNumberOfCells();
for (int cellnum = 0; cellnum <cells ; cellnum++) {
XSSFCell cell = row1.getCell(cellnum);
//匹配得数据类型
if (cell!=null){
int cellType = cell.getCellType();
String cellValue="";
switch (cellType){
case XSSFCell.CELL_TYPE_STRING ://字符串
System.out.print("字符串");
cellValue=cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_BOOLEAN://布尔
System.out.print("布尔");
cellValue=String.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK://空
System.out.print("空");
// cellValue=String.valueOf(cell.getBooleanCellValue())
break;
case XSSFCell.CELL_TYPE_NUMERIC://数字日期
System.out.print("数字or日期");
if (HSSFDateUtil.isCellDateFormatted(cell)){
//日期
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
}else{
//不是日期,转换为字符串
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
// cellValue=String.valueOf(cell.getBooleanCellValue())
break;
case XSSFCell.CELL_TYPE_ERROR:
System.out.print("类型错误");
break;
}
System.out.print("cellValue = " + cellValue);
}
}
}
}
}
}
抽取出来当工具类
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.FileNotFoundException;
import java.util.Date;
/**
* @author fjj
* @date 2021/1/22 18:34
*/
public class Demo4 {
@Test
public void getpoi07(FileInputStream inputStream) throws Exception {
//获取输入流
// FileInputStream in = new FileInputStream("E:\\实习的代码\\Sprint-poi\\" + "高等学校学生信息核准登记表(1).xlsx");
//创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
//获取工作表
XSSFSheet sheet = workbook.getSheetAt(0);
//获取标题
XSSFRow row = sheet.getRow(0);
if (row!=null){
//这个方法是获取所有一行得总数
int cells = row.getPhysicalNumberOfCells();
for (int cellnum = 0; cellnum <cells ; cellnum++) {
//获取本列得所有数据
XSSFCell cell = row.getCell(cellnum);
if (cell!=null){
//获取属性
int type = cell.getCellType();
//输出此函得
System.out.print(cell.getStringCellValue()+"\t");
}
}
System.out.println();
}
//获取表中得数据
int numberOfRows = sheet.getPhysicalNumberOfRows();
for (int rownum = 0; rownum < numberOfRows; rownum++) {
XSSFRow row1 = sheet.getRow(rownum);
if (row1!=null){
//读取列
int cells = row.getPhysicalNumberOfCells();
for (int cellnum = 0; cellnum <cells ; cellnum++) {
XSSFCell cell = row1.getCell(cellnum);
//匹配得数据类型
if (cell!=null){
int cellType = cell.getCellType();
String cellValue="";
switch (cellType){
case XSSFCell.CELL_TYPE_STRING ://字符串
System.out.print("字符串");
cellValue=cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_BOOLEAN://布尔
System.out.print("布尔");
cellValue=String.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK://空
System.out.print("空");
// cellValue=String.valueOf(cell.getBooleanCellValue())
break;
case XSSFCell.CELL_TYPE_NUMERIC://数字日期
System.out.print("数字or日期");
if (HSSFDateUtil.isCellDateFormatted(cell)){
//日期
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
}else{
//不是日期,转换为字符串
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
// cellValue=String.valueOf(cell.getBooleanCellValue())
break;
case XSSFCell.CELL_TYPE_ERROR:
System.out.print("类型错误");
break;
}
System.out.print("cellValue = " + cellValue);
}
}
}
}
}
}
带有计算得
@Test
public void demo() throws IOException {
FileInputStream in = new FileInputStream("E:\\实习的代码\\Sprint-poi\\" + "计算表.xlsx");
//创建工作
XSSFWorkbook sheets = new XSSFWorkbook(in);
//获取表
XSSFSheet sheet = sheets.getSheetAt(0);
//获取带有计算得行数
XSSFRow row = sheet.getRow(4);
//获取具体得单元格
XSSFCell cell = row.getCell(0);
//拿到计算得eval
XSSFFormulaEvaluator xssfFormulaEvaluator = new XSSFFormulaEvaluator(sheets);
//输出单元格得内容
int cellType = cell.getCellType();
switch (cellType){
case XSSFCell.CELL_TYPE_FORMULA:
//公式
String cellFormula = cell.getCellFormula();
System.out.println("cellFormula = " + cellFormula);
//计算
CellValue evaluate = xssfFormulaEvaluator.evaluate(cell);
String s = evaluate.formatAsString();
System.out.println("s = " + s);
break;
}
}
EayExcel
EayExcel得官方文档