一、需求说明
系统中经常有读取各种不同表头excel的需求，如根据用户上传的数据来实现某种功能的批量操作，我们通常会根据读取的内容把数据放入不同的实体中，形成List<rowEntity>。这跟jdbctemplate中查询rowMapper方法相似，将读取的数据库字段映射至实体中，将接口作为调用该方法的参数，使用时必须实现接口中的映射方法，其实这就是模板方法模式的钩子函数。
完成一个excel文件的读取具有固定的流程，1判断文件是否存在->2判断是否为excel->3根据excel版本新建读取对象->4按行读取文件内容->5行数据放入实体中->6返回List<rowEntity>对象，其中行数据放入实体中这一步是读取不同excel而有所差异的，可以把该步定义成抽象方法或接口，交给具体的调用方来实现。
二、Excel文件读取代码实现
2.1 引入工具包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-csv</artifactId>
<version>1.5</version>
</dependency>
2.2 代码实现
1）先创建ExcelRowMapper接口
public interface ExcelRowMapper<T> {
T mapRow(RowSet rs);
}
2）定义Excel中Row实体，方便单元格的设置和读取
import com.mszlu.blog.utils.date.DateTimeUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import java.util.Date;
import java.util.Map;
public class RowSet {
private Row row;
private Map<String, Integer> headMap;
public RowSet() {
super();
}
/**
* 传入字段名和字段名索引的对应关系，对应放置每一行的数据
*
* @param headMap
*/
public RowSet(Map<String, Integer> headMap) {
this.headMap = headMap;
}
public RowSet(Row row, Map<String, Integer> headMap) {
this.row = row;
this.headMap = headMap;
}
protected Row getRow() {
return row;
}
protected void setRow(Row row) {
this.row = row;
}
protected Map<String, Integer> getHeadMap() {
return headMap;
}
protected void setHeadMap(Map<String, Integer> headMap) {
this.headMap = headMap;
}
public Date getDate(String rowName) {
Cell cell = this.getCell(rowName);
if (cell == null) {
return null;
}
Date date = null;
if (cell.getCellStyle().getDataFormatString().indexOf("m/d/yy") > -1) {
date = cell.getDateCellValue();
} else {
cell.setCellType(CellType.STRING);
String dateStr = cell.getRichStringCellValue().getString();
date = DateTimeUtil.getDateTimeStr(dateStr, null);
}
return date;
}
public Integer getInt(String rowName) {
String val = getString(rowName);
return StringUtils.isEmpty(val) ? null : Integer.valueOf(val);
}
public Long getLong(String rowName) {
String val = getString(rowName);
return StringUtils.isEmpty(val) ? null : Long.valueOf(val);
}
public Double getDouble(String rowName) {
Cell cell = this.getCell(rowName);
if (cell == null) {
return null;
}
if (cell.getCellType() == CellType.NUMERIC) {
return cell.getNumericCellValue();
} else {
return Double.valueOf(this.getString(cell));
}
}
/**
* 按文本获取cell的内容
*
* @param rowName
* @return
*/
public String getString(String rowName) {
Cell cell = this.getCell(rowName);
if (cell == null) {
return null;
}
return this.getString(cell);
}
private String getString(Cell cell) {
if (cell.getCellType() == CellType.STRING) {
return cell.getRichStringCellValue().getString();
} else {
cell.setCellType(CellType.STRING);
return cell.getRichStringCellValue().getString();
}
}
private Cell getCell(String rowName) {
Integer idx = headMap.get(rowName);
if (idx == null) {
return null;
} else {
return this.row.getCell(idx);
}
}
public void setString(int i, String val) {
row.createCell(i).setCellValue(val);
}
}
3）定义ExcelTemplateUtil模板工具类，实现具体的读取流程
import com.mszlu.blog.utils.Exception.AppBaseException;
import com.mszlu.blog.utils.response.ResponseStatus;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.FileMagic;
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 java.io.*;
import java.util.*;
public class ExcelTemplateUtil {
private static final String SUFFIX_2003 = ".xls";
private static final String SUFFIX_2007 = ".xlsx";
private static final String SUFFIX_CSV = ".csv";
public static <T> List<T> parseExcel_v2(String fileName, ExcelRowMapper<T> rowMapper) {
List<T> rows = new ArrayList<>();
FileInputStream fis = null;
BufferedInputStream bis = null;
try {
//1、判断文件是否存在
File file = new File(fileName);
if (!file.exists()){
throw new AppBaseException(fileName + "文件不存在！", ResponseStatus.FAIL.getStatus());
}
fis = new FileInputStream(file);
bis = new BufferedInputStream(fis);
//2、判断是否为Excel文件
if (isExcelFile(bis)) {
excelRead_v2(bis, rowMapper, rows, fileName, 0);
} else {
throw new AppBaseException(fileName + "不是excel文件！", ResponseStatus.FAIL.getStatus());
}
} catch (FileNotFoundException e) {
throw new AppBaseException(fileName + "解析Excel报错！" + e.getMessage(), ResponseStatus.FAIL.getStatus());
} finally {
if (bis != null) {
try {
bis.close();
} catch (IOException e) {
throw new AppBaseException("解析Excel关闭BufferedInputStream报错！" + e.getMessage(), ResponseStatus.FAIL.getStatus());
}
}
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
throw new AppBaseException("解析Excel关闭FileInputStream报错！" + e.getMessage(), ResponseStatus.FAIL.getStatus());
}
}
}
return rows;
}
public static <T> void excelRead_v2(BufferedInputStream bis, ExcelRowMapper<T> rowMapper, List<T> dataList, String fileName, int sheetIndex) {
Workbook workbook = null;
try {
//3、根据excel版本新建读取对象
if (fileName.endsWith(SUFFIX_2003)) {
try {
workbook = new HSSFWorkbook(bis);
} catch (IOException e) {
if (e.getMessage().contains("XSSF")) {
workbook = new XSSFWorkbook(bis);
}
}
} else if (fileName.endsWith(SUFFIX_2007)) {
try {
workbook = new XSSFWorkbook(bis);
} catch (IOException e) {
if (e.getMessage().contains("HSSF")) {
workbook = new HSSFWorkbook(bis);
}
}
}
} catch (IOException e) {
e.printStackTrace();
}
//4按行读取文件内容
Sheet sheet = workbook.getSheetAt(sheetIndex);
int totalRows = sheet.getPhysicalNumberOfRows();
// 获取第一行：作为表头（字段），并把表头（字段）和索引存起来
Map<String, Integer> headerMap = new HashMap<String, Integer>();
Row row = sheet.getRow(0);
if (row == null) {
throw new AppBaseException("excel的第一行不能为空！", ResponseStatus.FAIL.getStatus());
}
int minColIdx = row.getFirstCellNum();
int maxColIdx = row.getLastCellNum();
for (int colIx = minColIdx; colIx < maxColIdx; colIx++) {
Cell cell = row.getCell(colIx);
headerMap.put(cell.getStringCellValue(), cell.getColumnIndex());
}
// 封装数据
RowSet rowSet = new RowSet(headerMap);
for (int i = 1; i < totalRows; i++) {
Row dataRow = sheet.getRow(i);
if (dataRow != null) {
//5、行数据放入实体中
rowSet.setRow(dataRow);
T t = rowMapper.mapRow(rowSet);
dataList.add(t);
}
}
}
/**
* 判断是否是EXCEL文件
*
* @param bis
* @return
*/
private static boolean isExcelFile(BufferedInputStream bis) {
Boolean judgeResult = false;
try {
FileMagic fileMagic = FileMagic.valueOf(bis);
if (Objects.equals(fileMagic, FileMagic.OLE2) || Objects.equals(fileMagic, FileMagic.OOXML)) {
judgeResult = true;
}
} catch (IOException e) {
throw new AppBaseException("判断是否excel报错！" + e.getMessage(), ResponseStatus.FAIL.getStatus());
}
return judgeResult;
}
}
4）定义映射实体类
import lombok.Data;
import java.util.Date;
@Data
public class UserEntity {
private String name;
private int age;
private Date date;
}
5）excel文件读取测试
public class FileReadWriteTest {
public static void main(String[] args) {
List<UserEntity> userEntitys = ExcelTemplateUtil.parseExcel_v2("F:\\tmp\\test.xls", rs -> {
UserEntity userEntity = new UserEntity();
userEntity.setName(rs.getString("name"));
userEntity.setAge(rs.getInt("age"));
userEntity.setDate(rs.getDate("date"));
return userEntity;
});
for(UserEntity userEntity : userEntitys) {
System.out.println(userEntity);
}
}
}
6）补充异常类，在项目中可以通用
/**
* 定义项目异常基础类
*/
public class AppBaseException extends RuntimeException {
private String msg;
private String status;
public AppBaseException(){
}
public AppBaseException(Throwable e){
super(e);
}
public AppBaseException(String msg, String status){
super(msg);
this.msg = msg;
this.status = status;
}
}
7）补充统一响应码类，在项目中可以通用
/**
* 定义项目统一响应码
*/
public enum ResponseStatus {
/**
* 成功
*/
SUCCESS("0", "成功"),
/**
* 失败
*/
FAIL("1", "失败"),
/**
* 请登录
*/
LOGIN("2", "请登录"),
/**
* 参数为空
*/
SERVICE_METHOD_PARAM_NULL("3", "参数%s为空"),
/**
* ID为空
*/
SERVICE_METHOD_PARAM_ID_NULL("4", "ID为空"),
/**
* 数据不存在
*/
SERVICE_DATA_NULL("5", "数据不存在"),
/**
* token验证出错
*/
TOKEN_FAIL("6", "token验证出错");
private String status;
private String msg;
ResponseStatus(String status, String msg){
this.status = status;
this.msg = msg;
}
public String getStatus(){
return status;
}
public String getMsg(){
return msg;
}
}
三、Excel文件生成代码实现
与读取excel类似，传入List<Entity>，将数据写入到不同表头的excel文件中，同样将表头和表数据写入表格中的步骤定义成抽象方法，交给具体的调用方来继承实现。
1）定义抽象类，用于写表头和表数据
import java.util.List;
public abstract class ExcelSet{
public abstract void setValues(int i, RowSet rs);
public abstract List<String> statementHeader();
}
2）定义创建表格的建造者类，这里使用到建造者模式，该模式将一个复杂对象的构建过程与它的表示分离，使得同样的构建过程可以创建不同的表示。建造者模式适用于创建对象需要很多步骤，如excel数据写入单元格，1创建工作簿->2创建sheet页->3创建行->4创建单元格->5放入单元格数据->6生成excel文件，在应用中可采用链式编程的方式来构造对象。
import com.mszlu.blog.utils.Exception.AppBaseException;
import com.mszlu.blog.utils.response.ResponseStatus;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
public class ExcelBuilder {
private XSSFWorkbook book;
private XSSFSheet sheet;
/**
* 创建工作簿
*
* @return
*/
public ExcelBuilder buildWorkBook() {
this.book = new XSSFWorkbook();
return this;
}
/**
* 创建sheet
*
* @param sheetName
*/
public ExcelBuilder buildSheet(String sheetName) {
this.sheet = this.book.createSheet(sheetName);
return this;
}
/**
* 创建表头
*
* @param headerList
*/
public ExcelBuilder buildHeader(List<String> headerList) {
XSSFRow row = sheet.createRow(0);
row.setHeightInPoints(100);
XSSFCellStyle style = book.createCellStyle();
//居中
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
//自动换行
style.setWrapText(true);
for (int i = 0; i < headerList.size(); i++) {
craeteCell(row, i, headerList.get(i), style);
}
return this;
}
public <T> ExcelBuilder buildRows(List<T> list, ExcelSet es) throws Exception {
return this.buildRows(list.size(), es);
}
public ExcelBuilder buildRows(int size, ExcelSet es) throws Exception {
RowSet rowSet = new RowSet();
for (int i = 0; i < size; i++) {
// 数据从第二行开始
XSSFRow row = sheet.createRow(i + 1);
rowSet.setRow(row);
//设置行，并创建单元格
es.setRow(i, rowSet);
}
return this;
}
public void build(String fileName) {
FileOutputStream out = null;
try {
out = new FileOutputStream(new File(fileName));
book.write(out);
} catch (Exception e) {
throw new AppBaseException(fileName + "写文件失败，报错信息为：" + e.getMessage(), ResponseStatus.FAIL.getStatus());
} finally {
try {
out.close();
} catch (IOException e) {
throw new AppBaseException(fileName + "写文件时关闭文件流失败，报错信息为：" + e.getMessage(), ResponseStatus.FAIL.getStatus());
}
}
}
private void craeteCell(Row row, int idx, String value, CellStyle style) {
Cell cell = row.createCell(idx);
cell.setCellValue(value);
cell.setCellStyle(style);
}
}
3）定义ExcelTemplateUtil模板工具类，实现具体的写流程
public class ExcelTemplateUtil {
/**
* excel文件写入
* @param fileName
* @param size
* @param es
* @param <T>
*/
public static <T> void write(String fileName, int size, ExcelSet es) {
ExcelBuilder excelBuilder = new ExcelBuilder();
if (size == 0) {
//数据为0，只创建空表头
excelBuilder.buildWorkBook().buildSheet("sheet1").buildHeader(es.setHeader()).build(fileName);
} else {
excelBuilder.buildWorkBook().buildSheet("sheet1").buildHeader(es.setHeader()).buildRows(size, es).build(fileName);
}
}
}
4）excel文件写测试
public class FileReadWriteTest {
public static void main(String[] args) {
List<UserEntity> userEntitys = ExcelTemplateUtil.parseExcel_v2("F:\\tmp\\test.xls", rs -> {
UserEntity userEntity = new UserEntity();
userEntity.setName(rs.getString("name"));
userEntity.setAge(rs.getInt("age"));
userEntity.setDate(rs.getDate("date"));
return userEntity;
});
for (UserEntity userEntity : userEntitys) {
System.out.println(userEntity);
}
// ExcelTemplateUtil.saveExcel("F:\\tmp\\test1.xls", "shee1", userEntitys);
ExcelTemplateUtil.write("F:\\tmp\\test2.xls", userEntitys.size(), new ExcelSet(){
@Override
public void setRow(int i, RowSet rs){
UserEntity userEntity = userEntitys.get(i);
rs.setString(0, userEntity.getName());
rs.setString(1, String.valueOf(userEntity.getAge()));
rs.setString(2, DateTimeUtil.getDateTimeStr(userEntity.getDate(),null));
}
@Override
public List<String> setHeader() {
return Arrays.asList("姓名", "年龄", "日期");
}
});
}
}
四、Excel文件读取代码实现_自动反射生成实体
在前面第二节文件读取时，需要按列去读取每行单元格的内容再放入实体中，这里可以进一步做简化，可以根据字段名反射获取实体的set方法，自动将单元格数据放入实体属性中，这里需要根据实体的字段属性类型，将单元格的数据内容进行相应的转换，而单元格的数据内容统一当字符串读取(不好一一做判断)，对于单元格是日期类型读取会有一点问题。既要判断单元格类型，又要判断实体属性类型，还要反射设置实体数据，个人不太建议这样做，下面演示一个简单版本。
1）在ExcelTemplateUtil中添加数据行转为实体的方法
public class ExcelTemplateUtil {
public static <T> void rowToModel(RowSet rowSet, T t) {
List<String> actualFields = rowSet.getHeadMap().keySet().stream().collect(Collectors.toList());
Field[] fields = t.getClass().getDeclaredFields();
if (actualFields.size() > fields.length) {
throw new AppBaseException("读取文件的list大于entity的字段数量", ResponseStatus.FAIL.getStatus());
}
for (int i = 0, len = fields.length; i < len; i++) {
//根据属性名称获取set方法
String name = fields[i].getName();
String setMethodName = "set" + name.substring(0, 1).toUpperCase() + name.substring(1);
Class<?> clazz = t.getClass();
try {
Method method = clazz.getMethod(setMethodName, fields[i].getType());
String fieldType = fields[i].getType().toString();
if (!actualFields.contains(name)) {
//允许实体字段比excel字段多
continue;
}
fieldTypeTran(rowSet, t, name, method, fieldType);
} catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
throw new AppBaseException("将数据转换成实体报错！" + e.getMessage(), ResponseStatus.FAIL.getStatus());
}
}
}
/**
* 根据实体类中属性的字段类型获取excel不同类型的数据并进行转换
* @param rowSet
* @param t
* @param name
* @param method
* @param fieldType
* @param <T>
*/
private static <T> void fieldTypeTran(RowSet rowSet, T t, String name, Method method, String fieldType) throws InvocationTargetException, IllegalAccessException {
switch (fieldType) {
case "class java.util.Date":
method.invoke(t, DateTimeUtil.getDateTimeStr(rowSet.getString(name), null));
break;
case "class java.lang.Boolean":
method.invoke(t, Boolean.valueOf(rowSet.getString(name)));
break;
case "class java.lang.Integer":
case "int":
method.invoke(t, Integer.valueOf(rowSet.getString(name)));
break;
case "class java.lang.Long":
case "long":
method.invoke(t, Long.valueOf(rowSet.getString(name)));
break;
case "double":
method.invoke(t, Double.valueOf(rowSet.getString(name)));
break;
case "float":
method.invoke(t, Float.valueOf(rowSet.getString(name)));
break;
case "class java.lang.String":
default:
method.invoke(t, rowSet.getString(name));
break;
}
}
}
2）再测试excel文件读取
public class FileReadWriteTest {
public static void main(String[] args) {
List<UserEntity> userEntitys = ExcelTemplateUtil.parseExcel_v2("F:\\tmp\\test.xls", rs -> {
UserEntity userEntity = new UserEntity();
ExcelTemplateUtil.rowToModel(rs, userEntity);
return userEntity;
});
for (UserEntity userEntity : userEntitys) {
System.out.println(userEntity);
}
}
}
五、结束语
以上结合设计模式对excel文件读取和生成进行工具类封装，有部分代码还是比较简陋的，博友们可以继续深化，主要有以下几点：
1、对于大文件读取，不能直接这样读入内存中，需要进行分段按流进行读写。
2、对于CSV文件读取，可以一并封装至ExcelTemplateUtil类中，实现几种常见的表格读取，对于CSV的RowSet方法相对简单，有表头字段和索引关系，字段数据内容统一按照字符串来处理就行。
3、当然其余还有很多代码内容可以优化，这里只是提供一种思路。
