写在前边: 工作中领导要求我们导出成绩表,日志表什么的?但是不会使用Java来处理Excel,那么这篇文章教你使用Apache下的POI来处理Excel,后边我还会出一个阿里巴巴的EasyExcel来更简单的处理!
公众号:小白编码
Excel操作
场景:将用户信息出位excel表格
将excel的信息录入到网站数据库
Apache POI
Apache POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。目前POI已经有了Ruby版本。
结构:
- HSSF - 提供读写Microsoft Excel XLS格式档案的功能。(03)
- XSSF - 提供读写Microsoft Excel OOXML XLSX格式档案的功能。(07)
- HWPF - 提供读写Microsoft Word DOC97格式档案的功能。
- XWPF - 提供读写Microsoft Word DOC2003格式档案的功能。
- HSLF - 提供读写Microsoft PowerPoint格式档案的功能。
- HDGF - 提供读Microsoft Visio格式档案的功能。
- HPBF - 提供读Microsoft Publisher格式档案的功能。
- HSMF - 提供读Microsoft Outlook格式档案的功能。
Excel界面
POI环境配置:
<!--xls03-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!--xlsx07-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.5</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
POI-Excel 写
03与07版本区别:
其中03版本以.xsl
为后缀,07以.xlsx
为后缀
对象区别:
03: new HSSFWorkbook();
07:new XSSFWorkbook();
03版本:
@Test
public void testWrite03() {
//1.创建工作簿 03
Workbook workbook = new HSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("日志表");
//3.创建一个行 (1,1) 从0开始
Row row1 = sheet.createRow(0);
//4.创建一个单元格 (1,1)
Cell cell11 = row1.createCell(0);
cell11.setCellValue("登陆日志:");
//(1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue("admin");
//第二行(2,1)
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("登陆时间:");
//(2,2)
Cell cell22 = row2.createCell(1);
cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
//生成一张表:(IO) 03版本:xls
String PATH = "F:\\workspace_idea4\\springboot\\excel\\";
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(PATH + "登陆日志表03.xls");
workbook.write(fileOutputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fileOutputStream != null){
try {
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
System.out.println("生成完毕!");
}
导出结果:
07版本:
@Test
public void testWrite07(){
//1.创建工作簿 07
Workbook workbook = new XSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("日志表");
//3.创建一个行 (1,1) 从0开始
Row row1 = sheet.createRow(0);
//4.创建一个单元格 (1,1)
Cell cell11 = row1.createCell(0);
cell11.setCellValue("登陆日志:");
//(1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue("admin");
//第二行(2,1)
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("登陆时间:");
//(2,2)
Cell cell22 = row2.createCell(1);
cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
//生成一张表:(IO) 03版本:xls
String PATH = "F:\\workspace_idea4\\springboot\\excel\\";
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(PATH + "登陆日志表07.xlsx");
workbook.write(fileOutputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fileOutputStream != null){
try {
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
System.out.println("生成完毕!");
}
导出结果:
批量写入
大文件03:
new HSSFWorkbook();
缺点:最多只能处理65536行,否则抛出异常。
java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
优点:过程中写入缓存,不操作磁盘,最后一次性写入。速度快。
@Test
public void testWrite03Big() {
//计算使用的时间
long start = System.currentTimeMillis();
//1.创建工作簿。 03
Workbook workbook = new HSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("测试批量");
//3.批量插入。
for (int rowNum = 0; rowNum < 65536; rowNum++) {
//3.1 创建65536行
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
//3.2 创建10列
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("操作完成!");
//创建流。
FileOutputStream fileOutputStream = null;
String PATH = "F:\\workspace_idea4\\springboot\\excel\\";
try {
fileOutputStream = new FileOutputStream(PATH + "testWrite03Big.xls");
workbook.write(fileOutputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fileOutputStream != null) {
try {
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
System.out.println("导出成功!");
long end = System.currentTimeMillis();
double time = end - start;
System.out.println("总共花费时间为:" + time); //2232.0
}
大文件07:
new XSSFWorkbook();
缺点:写数据非常慢,耗内存,会发生内存溢出,如100万条数据。
优点:可以写较大的数据量,如20万条。
@Test
public void testWrite07Big() {
//计算使用的时间
long start = System.currentTimeMillis();
//1.创建工作簿。 07
Workbook workbook = new XSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("测试批量");
//3.批量插入。
for (int rowNum = 0; rowNum < 65536; rowNum++) {
//3.1 创建65536行
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
//3.2 创建10列
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("操作完成!");
//创建流。
FileOutputStream fileOutputStream = null;
String PATH = "F:\\workspace_idea4\\springboot\\excel\\";
try {
fileOutputStream = new FileOutputStream(PATH + "testWrite07Big.xlsx");
workbook.write(fileOutputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fileOutputStream != null) {
try {
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
System.out.println("导出成功!");
long end = System.currentTimeMillis();
double time = end - start;
System.out.println("总共花费时间为:" + time);//6102.0
}
更大的数据输入:SXSSF
优点:可以写非常大的数据量,如100万条,甚至更多。写数据速度快,占用更少的内存。
注意点:
过程中会产生临时文件,需要清除临时文件。
默认有100条记录保存在内存中,如果超出这量,则前面的数据写入临时文件中。
如果想自定义内存中数据的数量,可以使用new SXSSFWorkbook(数量)。
@Test
public void testWriteSXSSFWorkbook() {
//计算使用的时间
long start = System.currentTimeMillis();
//1.创建工作簿。 07
Workbook workbook = new SXSSFWorkbook(1000);
//2.创建一个工作表
Sheet sheet = workbook.createSheet("测试批量");
//3.批量插入。
for (int rowNum = 0; rowNum < 65536; rowNum++) {
//3.1 创建65536行
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
//3.2 创建10列
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("操作完成!");
//创建流。
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(PATH + "SXSSFWorkbook.xlsx");
workbook.write(fileOutputStream);
//清除临时文件
((SXSSFWorkbook) workbook).dispose();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fileOutputStream != null) {
try {
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
System.out.println("导出成功!");
long end = System.currentTimeMillis();
double time = end - start;
System.out.println("总共花费时间为:" + time); //1798.0
}
POI-Excel 读
03版本
准备excel:
@Test
public void testRead03() {
//1.创建文件输入流
FileInputStream fileInputStream = null;
String PATH = "F:\\workspace_idea4\\springboot\\excel\\";
try {
fileInputStream = new FileInputStream(PATH + "登陆日志表03.xls");
//2.创建工作簿 03
Workbook workbook = new HSSFWorkbook(fileInputStream);
//3.获取工作表
Sheet sheet = workbook.getSheetAt(0);
//4.获取第1行
Row row = sheet.getRow(0);
//5.获取第1列
Cell cell = row.getCell(0);
// getStringCellValue() 获取字符串类型
System.out.println(cell.getStringCellValue());
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fileInputStream != null) {
try {
fileInputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
打印结果:登陆日志:
如果获取的类型与Excel不一致,会报以下错误:
07版本相差无几。将HSSF对象换成XSSF
读取不同类型:
表:
@Test
public void testReadCellType() {
FileInputStream inputStream = null;
try {
//1.获取文件流
inputStream = new FileInputStream(PATH + "明细表.xlsx");
//2.创建工作簿
Workbook workbook = new XSSFWorkbook(inputStream);
//3.获取第一张表
Sheet sheet = workbook.getSheetAt(0);
//4.获取标题内容
Row rowTitle = sheet.getRow(0);
if (rowTitle != null) {
// 4.1 获取总列数
int cellCount = rowTitle.getPhysicalNumberOfCells();
// 4.2 遍历列值
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if (cell != null) {
CellType cellType = cell.getCellType();
//System.out.print(cellType + " | ");
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + " | ");
}
}
}
System.out.println();
//5.获取表中内容
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 0; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData != null) {
//5.1 读取列数
int cellCount = rowTitle.getPhysicalNumberOfCells();
//5.2 匹配列的数据类型
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowData.getCell(cellNum);
if (cell != null) {
String cellValue = "";
CellType cellType = cell.getCellType();
// 判断类型
switch (cellType) {
case BLANK:
//空白
System.out.println("【BLANK】");
break;
case STRING:
//字符串
System.out.println("【STRING】");
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
//布尔类型
System.out.println("【BOOLEAN】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case NUMERIC:
//数字:(日期,普通数字)
System.out.println("【NUMERIC】");
if (DateUtil.isCellDateFormatted(cell)) {
//如果是日期格式
System.out.println("【日期】");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
// 如果不是日期格式
System.out.println("【字符串输出】");
cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
}
break;
case _NONE:
//空值
System.out.println("【NONE】");
break;
case ERROR:
//数据类型错误
System.out.println("【数据类型错误】");
break;
default:
break;
}
System.out.println(cellValue);
}
}
}
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (inputStream != null) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
公式求和:
@Test
public void testFormula() throws Exception {
FileInputStream is = new FileInputStream(PATH + "求和表.xlsx");
Workbook workbook = new XSSFWorkbook(is);
Sheet sheet = workbook.getSheetAt(0);
//第5行拿来求和
Row row = sheet.getRow(4);
//(5,1)
Cell cell = row.getCell(0);
//拿到计算公公式
FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
// 输出单元格的内容
CellType cellType = cell.getCellType();
// 公式
if (cellType == CellType.FORMULA) {
//需要先获取公式
String formula = cell.getCellFormula();
System.out.println(formula);
// 计算
CellValue evaluate = formulaEvaluator.evaluate(cell);
String cellValue = evaluate.formatAsString();
System.out.println(cellValue);
}
if (is!=null){
is.close();
}
}