pom依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- 阿里的json解析依赖 -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.datatype</groupId>
<artifactId>jackson-datatype-joda</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.module</groupId>
<artifactId>jackson-module-parameter-names</artifactId>
</dependency>
<!-- excel依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.0-beta2</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.2</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-simple</artifactId>
<version>1.7.25</version>
</dependency>
<dependency>
<groupId>org.eclipse.ecf</groupId>
<artifactId>org.objectweb.asm</artifactId>
<version>5.0.1.v201404251740</version>
</dependency>工具类（可根据自己的需求调整类型转换）
package cn.com.wy.test;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
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.ss.usermodel.WorkbookFactory;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.util.StringUtils;
public class ExcelJxUtils {
/**
* 异常数据styler
*/
private static CellStyle errorCellStyle;
static class FieldExcelProperty{
private int index;
private String[] value;
public int getIndex() {
return index;
}
public void setIndex(int index) {
this.index = index;
}
public String[] getValue() {
return value;
}
public void setValue(String[] value) {
this.value = value;
}
}
/**
* 验证模板与实体是否匹配
* @param headMap
* @param context
* @param headList
*/
public static void invokeHeadMap(List<String> titleNameList, List<String> entityFieldExcelNameList) {
for (int i = 0; i < entityFieldExcelNameList.size(); i++) {
try {
if (null != titleNameList &&
null != entityFieldExcelNameList &&
!titleNameList.get(i).equals(entityFieldExcelNameList.get(i))
) {
throw new ExcelAnalysisException("上传模板与系统模板不匹配，请使用平台模板上传数据");
}
} catch (Exception e) {
throw new ExcelAnalysisException("上传模板与系统模板不匹配，请使用平台模板上传数据");
}
}
}
/**
* 获取实体类的中文名称（@ExcelProperty注解中配置的名称，并根据index进行正序排序）
*/
public static List<String> getEntityFieldExcelName(Class clazz){
//获取类的所有属性
Field[] declaredFields = clazz.getDeclaredFields();
List<FieldExcelProperty> fieldExcelPropertyList = new ArrayList<>();
for (Field field: declaredFields) {
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if (annotation != null) {
FieldExcelProperty fieldExcelProperty = new FieldExcelProperty();
fieldExcelProperty.setIndex(annotation.index());
fieldExcelProperty.setValue(annotation.value());
fieldExcelPropertyList.add(fieldExcelProperty);
}
}
//获取排序后的字段顺序
List<String> headList =new ArrayList<>();
if (!fieldExcelPropertyList.isEmpty()) {
//利用拉姆达表达式，进行排序，将排序后的结果输出为list
fieldExcelPropertyList=fieldExcelPropertyList.stream().sorted((p1,p2) -> String.valueOf(p1.getIndex()).compareTo(String.valueOf(p2.getIndex()))).collect(Collectors.toList());
List<String> collect = fieldExcelPropertyList.stream().map(u->u.getValue()[0]).collect(Collectors.toList());
headList.addAll(collect);
}
return headList;
}
/**
* 创建错误数据的样式
* @param workbook
*/
private static void createErrorCellStyle(Workbook workbook) {
errorCellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setColor(Font.COLOR_RED);
errorCellStyle.setFont(font);
}
/**
* 获取标题行数据
* @param titleRow 标题行
* @param sheet sheet页
* @param filterColumnNum 忽略列的数量（从1开始）
* @return
*/
public static List<String> getTitleNameList(int titleRow,Sheet sheet,int filterColumnNum){
List<String> titleNameList = new ArrayList<String>();
Row row = sheet.getRow(titleRow);
Iterator<Cell> cellTitle = row.cellIterator();
//遍历标题行的列
while(cellTitle.hasNext()){
//跳过忽略的列
for(int i=0;i<filterColumnNum;i++){
cellTitle.next();
}
Cell cell =cellTitle.next();
//设置单元格为字符串类型
cell.setCellType(Cell.CELL_TYPE_STRING);
//获取单元格内容
String cellValue = cell.getStringCellValue();
//不为空，去除前后空格。为空设置空
if(!StringUtils.isEmpty(cellValue)){
titleNameList.add(cellValue.trim());
}else{
titleNameList.add("");
}
}
return titleNameList;
}
public static List getEntityRowsList(Iterator<Row> rows,Row row,Sheet sheet,int filterColumnNum){
List resultDataList = new ArrayList<>();
while (rows.hasNext()&& (row != null && sheet.getLastRowNum() - row.getRowNum() > 0)) {
List<String> entityRowsList = new ArrayList<String>();
row = rows.next();
Iterator<Cell> cellTitle = row.cellIterator();
//遍历标题行的列
while(cellTitle.hasNext()){
//跳过忽略的列
for(int j=0;j<filterColumnNum;j++){
cellTitle.next();
}
Cell cell =cellTitle.next();
//设置单元格为字符串类型
cell.setCellType(Cell.CELL_TYPE_STRING);
//获取单元格内容
String cellValue = cell.getStringCellValue();
//不为空，去除前后空格。为空设置空
if(!StringUtils.isEmpty(cellValue)){
entityRowsList.add(cellValue.trim());
}else{
entityRowsList.add("");
}
}
resultDataList.add(entityRowsList);
}
return resultDataList;
}
/**
* 解析后拼接结果对象
* @param entityRowsList
* @param clazz
* @return
*/
public static List<Map<String,Object>> handleEntityDataList(List<List<String>> entityRowsList,Class clazz){
//获取类的所有属性
Field[] declaredFields = clazz.getDeclaredFields();
List<FieldExcelProperty> fieldExcelPropertyList = new ArrayList<>();
for (Field field: declaredFields) {
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if (annotation != null) {
FieldExcelProperty fieldExcelProperty = new FieldExcelProperty();
fieldExcelProperty.setIndex(annotation.index());
fieldExcelProperty.setValue(new String[]{field.getName()});
fieldExcelPropertyList.add(fieldExcelProperty);
}
}
//获取排序后的字段顺序
List<Map<String,Object>> resultList =new ArrayList<>();
if (!fieldExcelPropertyList.isEmpty()) {
//利用拉姆达表达式，进行排序，将排序后的结果输出为list
fieldExcelPropertyList=fieldExcelPropertyList.stream().sorted((p1,p2) -> String.valueOf(p1.getIndex()).compareTo(String.valueOf(p2.getIndex()))).collect(Collectors.toList());
List<String> collect = fieldExcelPropertyList.stream().map(u->u.getValue()[0]).collect(Collectors.toList());
for(int i=0;i<entityRowsList.size();i++){
List<String> list = entityRowsList.get(i);
Map<String,Object> map = new HashMap<String, Object>();
for(int j=0;j<list.size();j++){
map.put(collect.get(j), list.get(j));
}
resultList.add(map);
}
}
return resultList;
}
/**
* excel解析返回相应的对象集合
* @param resultDataList 返回的对象结果集
* @param clazz 实体类型
* @param inputStream 文件流
* @param headRows 头部行数（从1开始，头部行数headRows大于等于标题行数titleRow）
* @param titleRow 标题行（从0开始）
* @param filterColumnNum 忽略列的数量（从1开始）
* @throws Exception
*/
public static <T> List<T> getExcelEntityList(Class clazz,InputStream inputStream,int headRows,int titleRow,int sheetNum,int filterColumnNum) throws Exception{
List resultDataList = new ArrayList<>();
//创建可回退流
if (!(inputStream.markSupported())) {
inputStream = new PushbackInputStream(inputStream, 8);
}
//获取实体类中文名称
List<String> entityFieldExcelNameList = getEntityFieldExcelName(clazz);
Workbook book = null;
/*该方法中包含对版本信息判定*/
book = WorkbookFactory.create(inputStream);
//创建错误数据样式
createErrorCellStyle(book);
List collection = new ArrayList();
for(int i=0;i<sheetNum;i++){
Sheet sheet = book.getSheetAt(i);
//行数
Iterator<Row> rows = sheet.rowIterator();
//获取标题行
List<String> titleNameList = getTitleNameList(titleRow, sheet,filterColumnNum);
//验证 模板是否正确
invokeHeadMap(titleNameList,entityFieldExcelNameList);
Row row = null;
//跳过头部行数
for (int j = 0; j < headRows; j++) {
row = rows.next();
}
//遍历行数据,获取数据集合
List entityRowsList = getEntityRowsList(rows, row, sheet, filterColumnNum);
//拼接结果对象
List<Map<String, Object>> handleEntityDataList = handleEntityDataList(entityRowsList,clazz);
//反射设置值到实体
for(Map<String, Object> map:handleEntityDataList){
T clacc = (T) clazz.newInstance();
for(String key:map.keySet()){
Field field = clacc.getClass().getDeclaredField(key);
field.setAccessible(true);
if(!StringUtils.isEmpty(map.get(key))){
System.out.println(field.getType().getName());
String type = field.getType().getName();
String obj = String.valueOf(map.get(key));
if(type.contains("String")){
field.set(clacc, obj);
}else if(type.contains("int")||type.contains("Integer")){
field.set(clacc, Integer.valueOf(obj));
}else if(type.contains("Float")||type.contains("float")){
field.set(clacc, Float.valueOf(obj));
}else if(type.contains("Double")||type.contains("double")){
field.set(clacc, Double.valueOf(obj));
}else if(type.contains("Boolean")||type.contains("boolean")){
field.set(clacc, Boolean.valueOf(obj));
}else if(type.contains("Date")||type.contains("date")){
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
field.set(clacc, simpleDateFormat.parse(obj));
}
}
}
resultDataList.add(clacc);
}
}
return resultDataList;
}
/**
* excel解析返回相应的对象集合
* @param clazz 实体类型
* @param inputStream 文件流
* @param filterColumnNum 忽略列的数量（从1开始,没有传0或者null）（例如序号列）
* @throws Exception
*/
public static <T> List<T> getExcelEntityList(Class clazz,InputStream inputStream,Integer filterColumnNum) throws Exception{
int headRows=1;
int titleRow=0;
int sheetNum=1;
if(filterColumnNum==null){
filterColumnNum=0;
}
return getExcelEntityList( clazz, inputStream, headRows, titleRow, sheetNum, filterColumnNum);
}
}
测试类
package cn.com.wy.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* Hello world!
*
*/
@SpringBootApplication
public class App
{
@Autowired
private static ExcelJxUtils excelUtils;
public static void main( String[] args ) throws Exception
{
File file = new File("C:\\Users\\Administrator\\Desktop\\123.xls");
InputStream inputStream = new FileInputStream(file);
Class clazz = ExcelPropertyV1.class;
//
List<ExcelPropertyV1> resultDataList = new ArrayList<ExcelPropertyV1>();
resultDataList = excelUtils.getExcelEntityList(clazz,inputStream,0);
System.out.println(123);
}
}
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
