POI-Excel
pom依赖导入
<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>
<!-- <dependency>-->
<!-- <groupId>com.alibaba</groupId>-->
<!-- <artifactId>fastjson</artifactId>-->
<!-- <version>1.2.62</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>
</dependencies>
版本差别
03版本最多可写65536行 以xls结尾
07版本理论为无限制 以xlsx结尾
写数据
03版
package POI;
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;
import java.io.IOException;
public class ExcelWrite {
static String path="D:\\IDEAproject\\java-Excel\\";
//03版本
@Test
public void textWrite03() throws IOException {
// 1、创建工作簿
Workbook workbook=new HSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = workbook.createSheet("测试表sheet的名字");
// 3、创建行
Row row = sheet.createRow(0);//代表第一行
// 4、创建一个单元格
Cell cell00 = row.createCell(0);//代表(1,1)格
cell00.setCellValue("年龄");
Cell cell01 = row.createCell(1);//代表(1,2)格
cell01.setCellValue(18);
Row row1 = sheet.createRow(1);//代表第二行
Cell cell10 = row1.createCell(0);
cell10.setCellValue("地址");
Cell cell11= row1.createCell(1);
cell11.setCellValue("河南驻马店");
Row row2 = sheet.createRow(2);//代表第三行
Cell cell20 = row2.createCell(0);
cell20.setCellValue("时间");
Cell cell21= row2.createCell(1);
String s = new DateTime().toString("yyyy-mm-dd hh:mm:ss");
cell21.setCellValue(s);
//5、生成io流 03版本 以xls结尾
String name=path + "测试使用表03.xls";
FileOutputStream fileOutputStream = new FileOutputStream(name);
workbook.write(fileOutputStream);
//6、关闭流
fileOutputStream.close();
System.out.println(name+" 创建完毕");
}
@Test
public void textWrite03BigData() throws IOException {
long begin = System.currentTimeMillis();
//创建一个工作簿
Workbook workbook=new HSSFWorkbook();
//创建一个表
Sheet sheet = workbook.createSheet("大数据表格一号");
for (int i = 0; i < 65535; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < 30; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(j);
}
}
System.out.println("创建完毕");
FileOutputStream fileOutputStream = new FileOutputStream(path + "测试大数据表03.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println("消耗时间为："+(end-begin)/1000);
}
}
07版
static String path="D:\\IDEAproject\\java-Excel\\";
//07版本
@Test
public void textWrite07() throws IOException {
// 1、创建工作簿
Workbook workbook=new XSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = workbook.createSheet("测试表sheet的名字");
// 3、创建行
Row row = sheet.createRow(0);//代表第一行
// 4、创建一个单元格
Cell cell00 = row.createCell(0);//代表(1,1)格
cell00.setCellValue("年龄");
Cell cell01 = row.createCell(1);//代表(1,2)格
cell01.setCellValue(18);
Row row1 = sheet.createRow(1);//代表第二行
Cell cell10 = row1.createCell(0);
cell10.setCellValue("地址");
Cell cell11= row1.createCell(1);
cell11.setCellValue("河南驻马店");
Row row2 = sheet.createRow(2);//代表第三行
Cell cell20 = row2.createCell(0);
cell20.setCellValue("时间");
Cell cell21= row2.createCell(1);
String s = new DateTime().toString("yyyy-mm-dd hh:mm:ss");
cell21.setCellValue(s);
//5、生成io流 07版本 以xlsx结尾
String name=path + "测试使用表07.xlsx";
FileOutputStream fileOutputStream = new FileOutputStream(name);
workbook.write(fileOutputStream);
//6、关闭流
fileOutputStream.close();
System.out.println(name+" 创建完毕");
}
//耗时长
@Test
public void textWrite07BigData() throws IOException {
long begin = System.currentTimeMillis();
//创建一个工作簿
Workbook workbook=new XSSFWorkbook();
//创建一个表
Sheet sheet = workbook.createSheet("大数据表格一号");
for (int i = 0; i < 65537; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < 10; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(j);
}
}
System.out.println("创建完毕");
FileOutputStream fileOutputStream = new FileOutputStream(path + "测试大数据表07.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println("消耗时间为："+(end-begin)/1000);
}
//速度快，性能较好，但会产生临时缓存文件，要清除
@Test
public void textWrite07BigDataSuper() throws IOException {
long begin = System.currentTimeMillis();
//创建一个工作簿
Workbook workbook=new SXSSFWorkbook();
//创建一个表
Sheet sheet = workbook.createSheet("大数据表格一号");
for (int i = 0; i < 65537; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < 10; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(j);
}
}
System.out.println("创建完毕");
FileOutputStream fileOutputStream = new FileOutputStream(path + "测试大数据表07升级版.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
//清除临时文件
((SXSSFWorkbook) workbook).dispose();
long end = System.currentTimeMillis();
System.out.println("消耗时间为："+(end-begin)/1000);
}
读数据
03版
static String path = "D:\\IDEAproject\\java-Excel\\";
@Test
public void Read03() throws IOException {
FileInputStream fileInputStream = new FileInputStream(path + "测试使用表03.xls");
//得到工作簿
Workbook workbook = new HSSFWorkbook(fileInputStream);
//得到表
Sheet sheetAt = workbook.getSheetAt(0);
//得到行
Row row = sheetAt.getRow(0);
//得到单元格
Cell cell00 = row.getCell(0);
//读取值时要主义数据类型
//getStringCellValue获取字符串类型
System.out.println(cell00.getStringCellValue());
Cell cell01 = row.getCell(1);
//getNumericCellValue获取数字类型
System.out.println(cell01.getNumericCellValue());
//关闭流
fileInputStream.close();
}
07
@Test
public void Read07() throws IOException {
FileInputStream fileInputStream = new FileInputStream(path + "测试使用表07.xlsx");
//得到工作簿
Workbook workbook = new XSSFWorkbook(fileInputStream);
//得到表
Sheet sheetAt = workbook.getSheetAt(0);
//得到行
Row row = sheetAt.getRow(0);
//得到单元格
Cell cell00 = row.getCell(0);
//读取值时要主义数据类型
//getStringCellValue获取字符串类型
System.out.println(cell00.getStringCellValue());
Cell cell01 = row.getCell(1);
//getNumericCellValue获取数字类型
System.out.println(cell01.getNumericCellValue());
//关闭流
fileInputStream.close();
}
不同数据类型
@Test
public void Read03CellType() throws IOException {
//获取文件流
FileInputStream fileInputStream = new FileInputStream(path + "会员消费商品明细表.xls");
//创建工作簿
Workbook workbook=new HSSFWorkbook(fileInputStream);
Sheet sheetAt = workbook.getSheetAt(0);
//获取标题内容
Row rowTitle = sheetAt.getRow(0);
if(rowTitle!=null){//必须掌握
//数目
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
//获取单元格内容
Cell cell = rowTitle.getCell(cellNum);
if(cell!=null){
//单元格数据类型
String stringCellValue = cell.getStringCellValue();
System.out.print(stringCellValue+" | ");
}
}
System.out.println();
}
//获取表格内容
//共用多少行
int rowCount = sheetAt.getPhysicalNumberOfRows();
//从 1 开始即为除标题之外开始
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheetAt.getRow(rowNum);
if(rowData!=null){
//获取该行对应的单元格数
int cellCount = rowData.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {//遍历单元格
//获取单元格
Cell cell = rowData.getCell(cellNum);
if(cell!=null){
//获取单元格类型
int cellType = cell.getCellType();
String cellValue="";
switch (cellType){
case HSSFCell.CELL_TYPE_STRING://字符串
System.out.print("String ");
cellValue=cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC://数字(日期 | 普通数字)
System.out.print("number ");
//判断是否为日期
if(HSSFDateUtil.isCellDateFormatted(cell)){
System.out.print("日期 ");
Date dateCellValue = cell.getDateCellValue();
cellValue=new DateTime(dateCellValue).toString("yyyy-mm-ddd");
}else {
System.out.print("转化为字符串输出 ");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case HSSFCell.CELL_TYPE_BOOLEAN://boolean
System.out.print("boolean ");
cellValue=String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK://空
System.out.print("空 ");
break;
case HSSFCell.CELL_TYPE_ERROR://空
System.out.print("数据类型错误 ");
break;
// default:
// return;
}
System.out.println(cellValue);
}
}
}
}
//关闭io流
fileInputStream.close();
}
带有公式
@Test
public void testFormula() throws Exception{
InputStream is = new FileInputStream(path + "计算公式.xls");
Workbook workbook = new HSSFWorkbook(is);
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://2
//得到公式
String formula = cell.getCellFormula();
System.out.println(formula);
CellValue evaluate = formulaEvaluator.evaluate(cell);
//String cellValue = String.valueOf(evaluate.getNumberValue());
String cellValue = evaluate.formatAsString();
System.out.println(cellValue);
break;
}
}
EasyExcel
导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.62</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency>
使用方法： https://www.yuque.com/easyexcel/doc/easyexcel