笔记原视频链接：点这里
POI-Excel写
Excel基本写操作
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.kuang</groupId>
<artifactId>kuang-poi</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>21</maven.compiler.source>
<maven.compiler.target>21</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<!-- xls(03)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.62</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>
</project>案例：
package com.kuang;
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.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
public class ExcelWriteTest {
//设置生成表的路径
String path = "D:\\idea_project\\poi\\kuang-poi";
@Test
public void testWrite03() throws Exception {
//1、先创建工作簿对象，03版本的excel文件使用HSSFWorkbook类创建
Workbook workbook = new HSSFWorkbook();
//2、通过工作簿对象 创建 工作表对象，传入工作表的名称
Sheet sheet = workbook.createSheet("狂神观众统计表");
//3、创建工作表的某行（传入第几行，从0开始）
Row row1 = sheet.createRow(0);
//4、创建某行的某个单元格（传入第几列，从0开始） (1,1)
Cell cell = row1.createCell(0);
//向这个单元格中填入数据
cell.setCellValue("今日新增观众");
//下面再向另外的单元格中填入数据
//(2,1)
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间");
//(2,2)
Cell cell22 = row2.createCell(1);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
//通过输出流将内存中的Excel文件写入到磁盘
//生成表（excel的03版本使用xls后缀）
//创建 FileOutputStream 对象，指定了要写入的文件。
FileOutputStream fileOutputStream = new FileOutputStream(path + "狂神观众统计表03.xls");
//使用 workbook.write 方法，将工作簿的数据写入到文件中。
workbook.write(fileOutputStream);
//关闭 FileOutputStream 流，释放资源，确保数据正确写入文件。
fileOutputStream.close();
System.out.println("狂神观众统计表03.xls生成完毕");
}
@Test
public void testWrite07() throws Exception {
//1、先创建工作簿对象，07版本的excel文件使用XSSFWorkbook类创建
Workbook workbook = new XSSFWorkbook();
//2、通过工作簿对象 创建 工作表对象，传入工作表的名称
Sheet sheet = workbook.createSheet("狂神观众统计表");
//3、通过工作表 创建 某行（传入第几行，从0开始）
Row row1 = sheet.createRow(0);
//4、通过行 创建 某个单元格（传入第几列，从0开始） (1,1)
Cell cell = row1.createCell(0);
//向这个单元格中填入数据
cell.setCellValue("今日新增观众");
//下面再向另外的单元格中填入数据
//(2,1)
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间");
//(2,2)
Cell cell22 = row2.createCell(1);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
//通过输出流将内存中的Excel文件写入到磁盘
//生成表（excel的07版本使用xlsx后缀）
//创建 FileOutputStream 对象，指定了要写入的文件。
FileOutputStream fileOutputStream = new FileOutputStream(path + "狂神观众统计表07.xlsx");
//使用 workbook.write 方法，将工作簿的数据写入到文件中。
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
System.out.println("狂神观众统计表07.xlsx生成完毕");
}
}
大数据量写入excel
对于excel03版的(使用HSSF类创建工作簿对象):
案例：
package com.kuang;
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.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
public class ExcelWriteTest {
//设置生成表的路径
String path = "D:\\idea_project\\poi\\kuang-poi";
@Test
public void testWrite03BigData() throws Exception {
long begin = System.currentTimeMillis();
//创建一个工作簿
Workbook workbook = new HSSFWorkbook();
//创建表
Sheet sheet = workbook.createSheet();
//写入数据
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);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(path + "testWrite03BigData.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin)/1000);
}
}
对于excel07版的(使用XSSF类创建工作簿对象):
案例：
package com.kuang;
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.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
public class ExcelWriteTest {
//设置生成表的路径
String path = "D:\\idea_project\\poi\\kuang-poi";
@Test
public void testWrite07BigData() throws Exception {
long begin = System.currentTimeMillis();
//创建一个工作簿
Workbook workbook = new XSSFWorkbook();
//创建表
Sheet sheet = workbook.createSheet();
//写入数据
for (int rowNum = 0;rowNum < 65537;rowNum++){
Row row = sheet.createRow(rowNum);
for (int cellNum = 0;cellNum < 10;cellNum++){
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(path + "testWrite07BigData.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin)/1000);
}
}
对于excel07版的(使用SXSSF类创建工作簿对象加速):
案例：
package com.kuang;
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.FileNotFoundException;
import java.io.FileOutputStream;
public class ExcelWriteTest {
//设置生成表的路径
String path = "D:\\idea_project\\poi\\kuang-poi";
@Test
public void testWrite07BigDataS() 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("over");
FileOutputStream fileOutputStream = new FileOutputStream(path + "testWrite07BigDataS.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
//因为使用SXSSFWorkbook这个类来写入数据到excel中会产生临时文件，所以这里需要清除临时文件!
((SXSSFWorkbook) workbook).dispose();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin)/1000);
}
}
POI-Excel读
Excel基本读操作
读取表中的一个单元格，这里的单元格数据类型我们已经知道，是字符串类型的。
案例：
package com.kuang;
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.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.FileOutputStream;
public class ExcelReadTest {
//设置生成表的路径
String path = "D:\\idea_project\\poi\\kuang-poi\\";
@Test
public void testRead03() throws Exception {
//获取文件流
FileInputStream fileInputStream = new FileInputStream(path + "kuang-poi狂神观众统计表03.xls");
//1、先创建工作簿对象（操作03版本的excel文件需要使用HSSFWorkbook类创建）
Workbook workbook = new HSSFWorkbook(fileInputStream);
//2、通过工作簿对象 获取 工作表对象，通过下标获取
Sheet sheet = workbook.getSheetAt(0);
//3、获取表的某行（传入第几行，从0开始）
Row row = sheet.getRow(0);
//4、获取某行的某个单元格（传入第几列，从0开始） (1,1)
Cell cell = row.getCell(0);
//5、获取单元格的值
System.out.println(cell.getStringCellValue());
fileInputStream.close();
}
@Test
public void testRead07() throws Exception {
//获取文件流
FileInputStream fileInputStream = new FileInputStream(path + "kuang-poi狂神观众统计表07.xlsx");
//1、先创建工作簿对象（操作07版本的excel文件需使用XSSFWorkbook类创建）
Workbook workbook = new XSSFWorkbook(fileInputStream);
//2、通过工作簿对象 获取 工作表对象，通过下标获取
Sheet sheet = workbook.getSheetAt(0);
//3、获取表的某行（传入第几行，从0开始）
Row row = sheet.getRow(0);
//4、获取某行的某个单元格（传入第几列，从0开始） (1,1)
Cell cell = row.getCell(0);
//5、获取单元格的值
System.out.println(cell.getStringCellValue());
fileInputStream.close();
}
}
两个单元测试的方法的运行结果都为：
如何读取不同类型的数据
批量读取表中的大量行，此过程中会遇到不同数据类型的单元格。
package com.kuang;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Date;
public class ExcelReadTest {
//设置生成表的路径
String path = "D:\\idea_project\\poi\\kuang-poi\\";
@Test
public void testCellType() throws Exception{
//获取文件流
FileInputStream fileInputStream = new FileInputStream(path + "会员消费商品明细表.xls");
//1、先创建工作簿（操作03版本的excel文件需使用HSSFWorkbook类创建）
Workbook workbook = new HSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
//获取标题内容
Row rowTittle = sheet.getRow(0);
if (rowTittle != null){ //判断改行存不存在
int cellCount = rowTittle.getPhysicalNumberOfCells(); //获取该行单元格的列数
for(int cellNum = 0; cellNum < cellCount; cellNum++){
Cell cell = rowTittle.createCell(cellNum);
if (cell != null){ //判断该单元格是否为空
int cellType = cell.getCellType(); //获取该单元格的数据类型
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + "|");
}
}
System.out.println();
}
//获取表中的内容
int rowCount = sheet.getPhysicalNumberOfRows(); //获取表中的行数
for (int rowNum = 1; rowNum < rowCount; rowNum++){ //遍历表中除表头的行
Row rowData = sheet.getRow(rowNum);
if (rowData != null){
int cellCount = rowTittle.getPhysicalNumberOfCells();
for (int cellNum = 0;cellNum < cellCount; cellNum++){ //遍历一行中的所有单元格
System.out.print("[" + (rowNum+1) + "-" + (cellNum+1)+ "]");
Cell cell = rowData.getCell(cellNum);
//匹配单元格的数据类型
if (cell != null){
int cellType = cell.getCellType();
String cellValue = "";
switch (cellType){
case Cell.CELL_TYPE_STRING:
System.out.print("[String]");
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print("[BOOLEAN]");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK:
System.out.print("[BLANK]");
break;
case Cell.CELL_TYPE_NUMERIC: //数字（日期、普通数字）
System.out.print("[NUMERIC]");
if (HSSFDateUtil.isCellDateFormatted(cell)){ //如果是日期,就转成对应的日期格式
System.out.println("[日期]");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
}else {
//不是日期格式，防止数字过长
System.out.print("转换为字符串输出");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case Cell.CELL_TYPE_ERROR:
System.out.print("[数据类型错误]");
break;
}
System.out.println(cellValue);
}
}
}
}
fileInputStream.close();
}
//将批量读取封装成工具类，只需要传入一个FileInputStream进来就可以，其他的都不用动
@Test
public void testCellType(FileInputStream fileInputStream) throws Exception{
//1、先创建工作簿（操作03版本的excel文件需使用HSSFWorkbook类创建）
Workbook workbook = new HSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
//获取标题内容
Row rowTittle = sheet.getRow(0);
if (rowTittle != null){ //判断改行存不存在
int cellCount = rowTittle.getPhysicalNumberOfCells(); //获取该行单元格的列数
for(int cellNum = 0; cellNum < cellCount; cellNum++){
Cell cell = rowTittle.createCell(cellNum);
if (cell != null){ //判断该单元格是否为空
int cellType = cell.getCellType(); //获取该单元格的数据类型
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + "|");
}
}
System.out.println();
}
//获取表中的内容
int rowCount = sheet.getPhysicalNumberOfRows(); //获取表中的行数
for (int rowNum = 1; rowNum < rowCount; rowNum++){ //遍历表中除表头的行
Row rowData = sheet.getRow(rowNum);
if (rowData != null){
int cellCount = rowTittle.getPhysicalNumberOfCells();
for (int cellNum = 0;cellNum < cellCount; cellNum++){ //遍历一行中的所有单元格
System.out.print("[" + (rowNum+1) + "-" + (cellNum+1)+ "]");
Cell cell = rowData.getCell(cellNum);
//匹配单元格的数据类型
if (cell != null){
int cellType = cell.getCellType();
String cellValue = "";
switch (cellType){
case Cell.CELL_TYPE_STRING:
System.out.print("[String]");
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print("[BOOLEAN]");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK:
System.out.print("[BLANK]");
break;
case Cell.CELL_TYPE_NUMERIC: //数字（日期、普通数字）
System.out.print("[NUMERIC]");
if (HSSFDateUtil.isCellDateFormatted(cell)){ //如果是日期,就转成对应的日期格式
System.out.println("[日期]");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
}else {
//不是日期格式，防止数字过长
System.out.print("转换为字符串输出");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case Cell.CELL_TYPE_ERROR:
System.out.print("[数据类型错误]");
break;
}
System.out.println(cellValue);
}
}
}
}
fileInputStream.close();
}
}
获取计算公式和获取计算单元格的值
@Test
public void testFormula() throws Exception{
//先获取到要计算的单元格
FileInputStream fileInputStream = new FileInputStream(path + "计算公式.xls");
Workbook workbook = new HSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(4);
Cell cell = row.getCell(0);
//拿到计算公式对象
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
//输出单元格的内容
int cellType = cell.getCellType();
switch (cellType){
case Cell.CELL_TYPE_FORMULA:
//获取到单元格的计算公式
String cellFormula = cell.getCellFormula();
System.out.println(cellFormula);
//计算单元格的值
CellValue evaluate = formulaEvaluator.evaluate(cell);
String cellValue = evaluate.formatAsString();
System.out.println(cellValue);
break;
}
}
1354
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
