POI
•
Java
POI
1. Basic object
2. POI write
1. Import dependency
<?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.wang</groupId>
<artifactId>POI</artifactId>
<version>1.0-SNAPSHOT</version>
<!--导入依赖-->
<dependencies>
<!--xls 03-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!--xlsx 07-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.5</version>
</dependency>
<!--Junit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
</dependency>
</dependencies>
</project>
2. Write test code
package com.wang;
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.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;
import java.io.FileOutputStream;
public class ExcelWriteTest {
public static String PATH = "D:\\Java_Web\\POI\\";
@Test
public void testWrite03() throws Exception {
//创建一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个工作表
HSSFSheet sheet = workbook.createSheet("我的Excel03");
//创建一行
Row row1 = sheet.createRow(0);
//创建一个单元格 ==> (1,1)
Cell cell11 = row1.createCell(0);
//填写数据
cell11.setCellValue("今日新增bug");
//(1,2)单元格
Cell cell12 = row1.createCell(1);
cell12.setCellValue(666);
//第二行
Row row2 = sheet.createRow(1);
//(2,1)
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间");
//(2,2)
Cell cell22 = row2.createCell(1);
//利用 joda-time 工具,toString中可以直接传递时间格式
String date = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(date);
//生成一张表
// 03 版本就是使用 xls 结尾!
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "今日产生bug统计表03.xls");
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
System.out.println("今日产生bug统计表03.xls 生成完毕!");
}
@Test
public void testWrite07() throws Exception {
//创建一个工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//创建一个工作表
XSSFSheet sheet = workbook.createSheet("我的Excel07");
//创建一行
Row row1 = sheet.createRow(0);
//创建一个单元格 ==> (1,toString中可以直接传递时间格式
String date = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(date);
//生成一张表
// 07 版本就是使用 xlsx 结尾!
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "今日产生bug统计表03.xlsx");
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
System.out.println("今日产生bug统计表07.xlsx 生成完毕!");
}
}
3. Difference between 03 and 07
4. Write a large amount of data
1. Write large files to HSSF
2. Write xssf for large files
3. Large file write sxssf
3. POI read
1. Simple reading
As above, for versions 03 and 07, different objects and suffixes are used for reading!
package com.wang;
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.junit.Test;
import java.io.FileInputStream;
import java.io.IOException;
public class ExcelReadTest {
public static String PATH = "D:\\Java_Web\\POI\\";
@Test
public void testRead03() throws IOException {
//获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "今日产生bug统计表03.xls");
//根据文件流创建一个工作簿
Workbook workbook = new HSSFWorkbook(fileInputStream);
//得到表
Sheet sheet = workbook.getSheetAt(0);
//得到行
Row row = sheet.getRow(1);
//得到列
Cell cell = row.getCell(1);
//读取值的时候,一定要注意读取值的类型
//getStringCellValue 字符串类型
System.out.println(cell.getStringCellValue());
fileInputStream.close();
}
@Test
public void testRead07() throws IOException {
//获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "今日产生bug统计表03.xlsx");
//根据文件流创建一个工作簿
Workbook workbook = new XSSFWorkbook(fileInputStream);
//得到表
Sheet sheet = workbook.getSheetAt(0);
//得到行
Row row = sheet.getRow(1);
//得到列
Cell cell = row.getCell(1);
//读取值的时候,一定要注意读取值的类型
//getStringCellValue 字符串类型
System.out.println(cell.getStringCellValue());
fileInputStream.close();
}
}
Note the type of value obtained!
2. Read different data types
@Test
public void testCellType() throws Exception {
FileInputStream inputStream = new FileInputStream(PATH + "明细表.xls");
Workbook workbook = new HSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
//获取标题内容
Row rowTitle = sheet.getRow(0);
if (rowTitle != null) {
//获取列数
int cellCount = rowTitle.getPhysicalNumberOfCells();
//遍历
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if (cell != null) {
CellType cellType = cell.getCellTypeEnum();
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 = rowTitle.getPhysicalNumberOfCells();
//读取列
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");
//获得单元格
Cell cellData = rowData.getCell(cellNum);
//对单元格的数据进行非空判断
if (cellData != null) {
CellType cellType = cellData.getCellTypeEnum();
String cellValue = "";
//按照类型输出为字符串
switch (cellType) {
//字符串
case STRING:
System.out.print("[String]");
cellValue = cellData.getStringCellValue();
break;
//数字 (日期,普通数字)
case NUMERIC:
System.out.print("[Number]");
//如果是一个日期类型的数字
if (HSSFDateUtil.isCellDateFormatted(cellData)) {
System.out.print("[日期]");
Date dateCellValue = cellData.getDateCellValue();
//利用 joda 转化时间格式,输出为字符串
cellValue = new DateTime(dateCellValue).toString("yyyy-MM-dd");
} else {
//如果是一个普通的数字类型
System.out.print("[普通的数字类型]");
//转换为字符串
HSSFDataFormatter hssfDataFormat = new HSSFDataFormatter();
cellValue = hssfDataFormat.formatCellValue(cellData);
}
break;
//布尔
case BOOLEAN:
System.out.print("[Boolean]");
cellValue = String.valueOf(cellData.getBooleanCellValue());
break;
//数据类型错误
case ERROR:
System.out.print("[数据类型错误]");
break;
}
System.out.println(cellValue);
} else {
System.out.println("[Blank]");
}
}
}
}
inputStream.close();
}
Attention
You can extract tool classes
package com.wang.Util;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.joda.time.DateTime;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Date;
public class HSSFReadUtil {
public static void ReadWithType(String PATH,int SheetNum) throws IOException {
FileInputStream inputStream = new FileInputStream(PATH);
Workbook workbook = new HSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
//获取标题内容
Row rowTitle = sheet.getRow(0);
if (rowTitle != null) {
//获取列数
int cellCount = rowTitle.getPhysicalNumberOfCells();
//遍历
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if (cell != null) {
CellType cellType = cell.getCellTypeEnum();
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 = rowTitle.getPhysicalNumberOfCells();
//读取列
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");
//获得单元格
Cell cellData = rowData.getCell(cellNum);
//对单元格的数据进行非空判断
if (cellData != null) {
CellType cellType = cellData.getCellTypeEnum();
String cellValue = "";
//按照类型输出为字符串
switch (cellType) {
//字符串
case STRING:
System.out.print("[String]");
cellValue = cellData.getStringCellValue();
break;
//数字 (日期,普通数字)
case NUMERIC:
System.out.print("[Number]");
//如果是一个日期类型的数字
if (HSSFDateUtil.isCellDateFormatted(cellData)) {
System.out.print("[日期]");
Date dateCellValue = cellData.getDateCellValue();
//利用 joda 转化时间格式,输出为字符串
cellValue = new DateTime(dateCellValue).toString("yyyy-MM-dd");
} else {
//如果是一个普通的数字类型
System.out.print("[普通的数字类型]");
//转换为字符串
HSSFDataFormatter hssfDataFormat = new HSSFDataFormatter();
cellValue = hssfDataFormat.formatCellValue(cellData);
}
break;
//布尔
case BOOLEAN:
System.out.print("[Boolean]");
cellValue = String.valueOf(cellData.getBooleanCellValue());
break;
//数据类型错误
case ERROR:
System.out.print("[数据类型错误]");
break;
}
System.out.println(cellValue);
} else {
System.out.println("[Blank]");
}
}
}
}
inputStream.close();
}
}
Test tool class
@Test
public void testCellTypeUtil() throws IOException {
HSSFReadUtil.ReadWithType(PATH + "明细表.xls",0);
}
3. Calculation formula
@Test
public void testFormula() throws Exception {
FileInputStream inputStream = new FileInputStream(PATH + "公式.xls");
Workbook workbook = new HSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(4);
Cell cell = row.getCell(0);
// 拿到计算公式
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
//输出单元格的内容
CellType cellType = cell.getCellTypeEnum();
switch (cellType) {
//公式
case FORMULA:
String formula = cell.getCellFormula();
System.out.println(formula);
//计算
CellValue evaluate = formulaEvaluator.evaluate(cell);
String cellValue = evaluate.formatAsString();
System.out.println(cellValue);
break;
}
}
be careful
The content of this article comes from the network collection of netizens. It is used as a learning reference. The copyright belongs to the original author.
THE END
二维码