- 一、简介
- 二、maven依赖
- 三、工具类ExcelUtil
- 四、测试
- 4.1、EmployeeVo
- 4.2、写入数据到Excel
- 4.3、从Excel读取数据
- 结语
HSSFWorkbook 、 XSSFWorkbook 、SXSSFWorkbook三者的区别
- HSSFWorkbook：是操作Excel 2003以前（包括2003）的版本，扩展名是.xls，数据限制是65535，超过则内存溢出
- XSSFWorkbook：是操作Excel 2007后的版本，扩展名是.xlsx，数据限制是1048576行，16384列，超过则内存溢出
- SXSSFWorkbook：是操作Excel 2007后的版本，扩展名是.xlsx，从POI 3.8版本开始可以持久化到磁盘，减少内存溢出问题
我们写个工具不仅能直接把简单Java对象数据直接导出到Excel，还能把数据读取成我们需要的简单的Java对象，并且可以根据你的需要选择不同的 Workbook，不用纠结HSSFWorkbook 、 XSSFWorkbook 、SXSSFWorkbook分别怎么用，本文中 org.apache.poi 使用的版本是 5.0.0。
二、maven依赖pom.xml
4.0.0 org.springframework.boot spring-boot-starter-parent 2.5.2 com.alian excel 0.0.1-SNAPSHOT excel Java实现Excel文件读写 1.8 org.springframework.boot spring-boot-starter org.apache.poi poi 5.0.0 org.apache.poi poi-ooxml 5.0.0 org.projectlombok lombok 1.16.14 junit junit 4.12 compile org.springframework.boot spring-boot-maven-plugin
最重要的依赖就是下面两个
三、工具类ExcelUtilorg.apache.poi poi 5.0.0 org.apache.poi poi-ooxml 5.0.0
ExcelUtil.java
package com.alian.excel.utils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
@Slf4j
public class ExcelUtil {
public static List readDataFromExcel(String filePath, Class t, int filterRowNumbers) {
FileInputStream fis = null;
ArrayList list = new ArrayList<>();
try {
T obj = t.newInstance();
fis = new FileInputStream(filePath);
Workbook sheets = WorkbookFactory.create(fis);
//获取sheet第一页（根据自己需要）
Sheet sheet = sheets.getSheetAt(0);
//获取表格的行数
int totalRowNumber = sheet.getPhysicalNumberOfRows();
//获取对象的字段列表
Field[] fields = t.getDeclaredFields();
for (int i = filterRowNumbers; i < totalRowNumber; i++) {
//获取一行数据
Row row = sheet.getRow(i);
// //变量一行数据的每个单元格，row.getPhysicalNumberOfCells()是单元格的数量
for (int j = 0, jLen = row.getPhysicalNumberOfCells(); j < jLen; j++) {
Cell cell = row.getCell(j);
//获取字段
Field dataField = fields[j];
String startWord = dataField.getName().substring(0, 1);
String methodName = "set" + dataField.getName().replace(startWord, startWord.toUpperCase());
//获取字段的set方法，dataField.getType()是参数的类型
Method method = t.getMethod(methodName, dataField.getType());
//反射调用set方法，getValueFromCell是把表格的值转成对应的类型
method.invoke(obj, getValueFromCell(dataField, cell));
}
list.add(obj);
}
return list;
} catch (Exception e) {
log.error("从Excel读取数据异常", e);
return Collections.emptyList();
} finally {
try {
if (fis != null) {
fis.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static void exportDataToExcel(Workbook workbook, String sheetName, String[] headerArray, List list, String filePath) {
//生成一个表格,并命名
Sheet sheet = workbook.createSheet(sheetName);
//设置表格默认列宽15个字节
sheet.setDefaultColumnWidth(15);
//生成一个头部样式
CellStyle headerStyle = getCellStyle(workbook, true);
//生成表格标题
Row headerRow = sheet.createRow(0);
headerRow.setHeight((short) 300);
for (int i = 0, len = headerArray.length; i < len; i++) {
//创建头部行的一个小单元格
Cell headerRowCell = headerRow.createCell(i);
//设置头部单元格的样式
headerRowCell.setCellStyle(headerStyle);
//设置头部单元格的值
headerRowCell.setCellValue(headerArray[i]);
}
//获取数据域样式
CellStyle bodyStyle = getCellStyle(workbook, false);
FileOutputStream os = null;
try {
//将数据放入sheet中
for (int i = 0, iLen = list.size(); i < iLen; i++) {
//创建一行，因为头部已经占用一行故需要加1
Row dataRow = sheet.createRow(i + 1);
T t = list.get(i);
//利用反射，根据JavaBean属性的先后顺序，动态调用get方法得到属性的值
Field[] fields = t.getClass().getDeclaredFields();
try {
for (int j = 0, jLen = fields.length; j < jLen; j++) {
//获取单元格第值
Cell dataRowCell = dataRow.createCell(j);
//获取字段
Field dataField = fields[j];
String startWord = dataField.getName().substring(0, 1);
String methodName = "get" + dataField.getName().replace(startWord, startWord.toUpperCase());
//获取对象的get方法
Method getMethod = t.getClass().getMethod(methodName);
//反射调用get方法
Object value = getMethod.invoke(t);
//单元格值为String
dataRowCell.setCellValue(null == value ? "" : value.toString());
dataRowCell.setCellStyle(bodyStyle);
}
} catch (Exception e) {
log.error("第【{}】行数据生成异常(下标0开始)", i, e);
}
}
os = new FileOutputStream(filePath);
workbook.write(os);
os.flush();
} catch (Exception e) {
log.error("生成数据异常", e);
} finally {
try {
if (os != null) {
os.close();
}
} catch (IOException e) {
log.error("关闭文件异常", e);
}
}
}
public static CellStyle getCellStyle(Workbook workbook, boolean isHeader) {
CellStyle style = workbook.createCellStyle();
//设置边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
//设置边框颜色
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
//水平对齐方式
style.setAlignment(HorizontalAlignment.CENTER);
//垂直对齐方式
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置字体样式
Font font = workbook.createFont();
font.setColor(IndexedColors.BLACK.getIndex());
font.setFontHeightInPoints((short) 12);
if (isHeader) {
//设置背景色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
font.setFontHeightInPoints((short) 14);
font.setBold(true);
}
//把字体应用到当前样式
style.setFont(font);
return style;
}
public static Object getValueFromCell(Field dataField, Cell cell) {
String fieldTypeStr = dataField.getType().toString();
if (fieldTypeStr.contains("String")) {
return cell.getStringCellValue();
} else if (fieldTypeStr.contains("Integer") || fieldTypeStr.contains("int")) {
return Integer.parseInt(cell.getStringCellValue());
} else if (fieldTypeStr.contains("Boolean") || fieldTypeStr.contains("boolean")) {
return Boolean.getBoolean(cell.getStringCellValue());
} else if (fieldTypeStr.contains("Double") || fieldTypeStr.contains("double")) {
return Double.parseDouble(cell.getStringCellValue());
} else if (fieldTypeStr.contains("float")) {
return Float.parseFloat(cell.getStringCellValue());
} else if (fieldTypeStr.contains("Long") || fieldTypeStr.contains("long")) {
return Long.parseLong(cell.getStringCellValue());
} else if (fieldTypeStr.contains("char")) {
return cell.getStringCellValue().charAt(0);
} else if (fieldTypeStr.contains("LocalTime")) {
return LocalTime.parse(cell.getStringCellValue());
} else if (fieldTypeStr.contains("LocalDate")) {
return LocalDate.parse(cell.getStringCellValue());
} else if (fieldTypeStr.contains("LocalDateTime")) {
return LocalDateTime.parse(cell.getStringCellValue());
} else if (fieldTypeStr.contains("Date")) {
try {
return new SimpleDateFormat("yyyy-MM-dd").parse(cell.getStringCellValue());
} catch (ParseException e) {
log.error("时间转化异常", e);
}
}
return null;
}
}
工具类中使用到了反射的知识，来完成get和set方法的操作。
四、测试 4.1、EmployeeVoEmployeeVo.java
package com.alian.excel.vo;
import lombok.Data;
import java.io.Serializable;
import java.time.LocalDate;
@Data
public class EmployeeVo {
private String id;
private String name;
private int age;
private double salary;
private String department;
private LocalDate hireDate;
public EmployeeVo(){
}
public EmployeeVo(String id, String name, int age, double salary, String department, LocalDate hireDate) {
this.id = id;
this.name = name;
this.age = age;
this.salary = salary;
this.department = department;
this.hireDate = hireDate;
}
}
4.2、写入数据到Excel
我们写个测试类
@Test
public void write() {
List dataList = new ArrayList<>();
//仅仅是模拟数据
for (int i = 0; i < 30; i++) {
EmployeeVo employeeVo = new EmployeeVo("BAT10012","梁南生",18,20000.0,"研发部",LocalDate.of(2020,2,13));
dataList.add(employeeVo);
}
String sheetName = "员工信息";
String[] headerArray = new String[]{"员工编号", "员工姓名", "员工年龄", "工资", "部门", "入职时间"};
String filePath = "C:\myFile\CSDN\Excel\Excel文件写入测试.xls";
ExcelUtil.exportDataToExcel(new HSSFWorkbook(), sheetName, headerArray, dataList, filePath);
//ExcelUtil.exportDataToExcel(new XSSFWorkbook(), sheetName, headerArray, dataList, filePath);
//ExcelUtil.exportDataToExcel(new SXSSFWorkbook(),sheetName, headerArray, dataList, filePath);
}
运行结果：
如果你用HSSFWorkbook 写入的数据大于65535则会报错
17:02:37.541 [main] ERROR com.alian.excel.utils.ExcelUtil - 生成数据异常 java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)4.3、从Excel读取数据
@Test
public void read() {
String filePath = "C:\myFile\CSDN\Excel\Excel文件写入测试.xls";
List employeeVos = ExcelUtil.readDataFromExcel(filePath, EmployeeVo.class, 1);
log.info("读取的数据行数：{}",employeeVos.size());
for (EmployeeVo employeeVo:employeeVos){
log.info("{}",employeeVo);
}
}
运行结果：
17:00:40.303 [main] INFO - 读取的数据行数：30 17:00:40.306 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.306 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.306 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13) 17:00:40.307 [main] INFO - EmployeeVo(id=BAT10012, name=梁南生, age=18, salary=20000.0, department=研发部, hireDate=2020-02-13)结语
本工具类是针对简单Java对象实现读写的，尽量不要使用继承之类的，假设你的对象实现了序列化接口，可能就要对字段 serialVersionUID进行排除。还有就是各种类型的转换并没有写全（只包括常见类型），包括数据空值处理等，大家可以根据需要进行调整。
本文是先写入再读取，因为写入时字段的类型是字符串，所以读取的时候会转为对象相应的类型，Excel里的格式本身就是很复杂的，所以使用本工具类时，最好是使用本工具写入的，或者先把数据的格式先转为文本格式再使用。
如果你对账文件处理，建议还是使用CSV文件，参考：Java实现CSV文件的读写（包含追加内容）
