本文介绍了一种基于Java的Excel数据导入处理方法，该方法能够解析Excel文件并将其内容转换为对象集合，支持多种数据类型及复杂的对象结构。此外，还详细展示了如何验证数据的有效性，并提供了异常处理方案。
/**
* INTEGER,DOUBLE,STRING,BOOLEAN,DATE,TIMESTAMP
* 子对象目前只支持2层处理
* 第一个参数表示模版的列名称
* 第二个参数表示获取的数据类型
* 第三个参数表示对应的对象取值属性
* 第四个参数表示是否需要校验为空
* 第五个参数表示时间类型的格式化规则
* 注意：阿里接口的属性是取得set方法，所以属性第一个字母为大写，set方法会变为小写。
*/
Map<Integer, String[]> hydBotImpo = new HashMap() {{
put(0, new String[]{"氢瓶编号", "STRING", "hydBottleNo",StatusType.CAN_NOT_NULL});
put(1, new String[]{"使用车辆", "STRING", "busInfo.plateNo",StatusType.CAN_NULL});
put(2, new String[]{"品牌", "STRING", "vehHydrogenBottleModel.hydBotModBrands",StatusType.CAN_NOT_NULL});
put(3, new String[]{"型号", "STRING", "vehHydrogenBottleModel.hydBottleModel",StatusType.CAN_NOT_NULL});
put(4, new String[]{"出厂日期", "DATE", "outTime",StatusType.CAN_NOT_NULL, "yyyy-MM-dd"});
put(5, new String[]{"使用截止日期", "DATE", "expiryTime",StatusType.CAN_NOT_NULL, "yyyy-MM-dd"});
put(6, new String[]{"首次检测日期", "DATE", "firstTestTime",StatusType.CAN_NULL, "yyyy-MM-dd"});
}};private List<VehHydrogenBottle> initExcelData(MultipartFile file, Map<Integer, List<String>> map, Map<String, Integer> message) {
List<VehHydrogenBottle> data = new ArrayList<>();
//解析excel
ExcelInfo excelInfo = ExcelUtil.getRowInfosByFile(map, file, ColumnName.hydBotImpo);
if (excelInfo != null) {
if (ExcelUtil.isEmpty(excelInfo.getRowsMap())) {
ExcelUtil.importFailInfo(map, -6, "导入数据为空");
}
//所有的校验
message.put("total", excelInfo.getRowsMap().size());
if (ExcelUtil.isEmpty(map)) {
//读取所有的行信息为一个对象(根据掺入的class文件名称)。
List<Object> infos = ExcelUtil.addObjectToData(VehHydrogenBottle.class, rowInfos);
infos.forEach(info -> {
//添加数据
data.add(vehHydrogenBottle);
});
}
}
return data;
} /**
* 通过ExcelFile获取列信息
* 一次导入数据量不用超过500条
*
* @return
*/
public static ExcelInfo getRowInfosByFile(Map<Integer, List<String>> map, MultipartFile file, Map<Integer, String[]> cells) {
if (file.isEmpty()) {
ExcelUtil.importFailInfo(map, -3, "文件不存在");
return null;
}
ImportExcel ei;
try {
ei = new ImportExcel(file, 0, 0);
} catch (InvalidFormatException | IOException e) {
ExcelUtil.importFailInfo(map, -1, "excel导入失败" + e);
return null;
}
Row r = ei.getRow(0);
if (r == null) {
ExcelUtil.importFailInfo(map, -5, "excel为空");
return null;
}
//校验列名是否都正确，判断是否使用模版
AtomicInteger atomicInteger = new AtomicInteger(-1);
Row finalR = r;
try {
cells.forEach((index, columnName) -> {
if (!columnName[0].equals(finalR.getCell(index).getStringCellValue())) {
ExcelUtil.importFailInfo(map, -2, "第" + (index + 1) + "列的列名名异常，模版为" + columnName[0] + "，模板使用错误，请下载模板重新导入");
atomicInteger.set(index);
}
});
} catch (Exception e) {
}
if (atomicInteger.get() != -1) {
return null;
}
List<Integer> columns = new ArrayList(cells.keySet());
ExcelInfo excelInfo = new ExcelInfo(ei.getLastDataRowNum(), columns);
for (int i = ei.getDataRowNum(), len = ei.getLastDataRowNum(); i <= len; i++) {
r = ei.getRow(i);
Row finalR1 = r;
int finalI = i;
cells.forEach((index, columnType) -> {
Cell cell = finalR1.getCell(columns.get(index));
Object obj = null;
if (cell != null && !"".equals(cell.toString().trim())) {
if (columnType[1].equals("INTEGER")) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
try {
obj = Double.valueOf(cell.getNumericCellValue()).intValue();
} catch (Exception e) {
ExcelUtil.importFailInfo(map, (finalI - 1), columnType[0] + "数据格式异常");
}
} else if (columnType[1].equals("DOUBLE")) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
try {
obj = Double.valueOf(cell.getNumericCellValue());
} catch (Exception e) {
ExcelUtil.importFailInfo(map, (finalI - 1), columnType[0] + "数据格式异常");
}
} else if (columnType[1].equals("DATE")) {
try {
if (DateUtil.isCellDateFormatted(cell)) {
String pattern = columnType.length > 4 ? columnType[4] : DatetimeUtil.TIME_FORMAT_Y_M_D;
obj = DatetimeUtil.changeToDate(DatetimeUtil.formatDate(cell.getDateCellValue(), pattern), pattern);
} else {
ExcelUtil.importFailInfo(map, (finalI - 1), columnType[0] + "时间格式异常");
}
} catch (Exception e) {
ExcelUtil.importFailInfo(map, (finalI - 1), columnType[0] + "时间格式异常");
}
} else if (columnType[1].equals("BOOLEAN")) {
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
try {
obj = cell.getBooleanCellValue();
} catch (Exception e) {
ExcelUtil.importFailInfo(map, (finalI - 1), columnType[0] + "数据格式异常");
}
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
try {
obj = cell.getStringCellValue().trim();
if ("".equals(obj)) {
obj = null;
}
} catch (Exception e) {
ExcelUtil.importFailInfo(map, (finalI - 1), columnType[0] + "数据异常");
}
}
} else {
if (StatusType.CAN_NOT_NULL.equals(columnType[3])) {
ExcelUtil.importFailInfo(map, (finalI - 1), columnType[0] + "不能为空");
}
}
if (excelInfo.getRowsMap().get(finalI) == null) {
excelInfo.getRowsMap().put(finalI, new HashMap<>());
}
if (columnType[2].indexOf(".") > -1) {
String[] columnsName = columnType[2].split("\\.");
if (excelInfo.getRowsMap().get(finalI).get(columnsName[0]) == null) {
excelInfo.getRowsMap().get(finalI).put(columnsName[0], new HashMap<>());
}
((Map) (excelInfo.getRowsMap().get(finalI).get(columnsName[0]))).put(columnsName[1], obj);
} else {
excelInfo.getRowsMap().get(finalI).put(columnType[2], obj);
}
excelInfo.getColumnsMap().get(index).add(obj);
});
}
return excelInfo;
}/**
* Copyright © 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
*/
package com.thinkgem.jeesite.common.utils.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import com.google.common.collect.Lists;
import com.thinkgem.jeesite.common.utils.Reflections;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
import com.thinkgem.jeesite.modules.sys.utils.DictUtils;
/**
* 导入Excel文件（支持“XLS”和“XLSX”格式）
*
* @author ThinkGem
* @version 2013-03-10
*/
public class ImportExcel {
private static Logger log = LoggerFactory.getLogger(ImportExcel.class);
/**
* 工作薄对象
*/
private Workbook wb;
/**
* 工作表对象
*/
private Sheet sheet;
/**
* 标题行号
*/
private int headerNum;
/**
* 构造函数
*
* @param path 导入文件，读取第一个工作表
* @param headerNum 标题行号，数据行号=标题行号+1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum);
}
/**
* 构造函数
*
* @param path 导入文件对象，读取第一个工作表
* @param headerNum 标题行号，数据行号=标题行号+1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum)
throws InvalidFormatException, IOException {
this(file, headerNum, 0);
}
/**
* 构造函数
*
* @param path 导入文件
* @param headerNum 标题行号，数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum, sheetIndex);
}
/**
* 构造函数
*
* @param path 导入文件对象
* @param headerNum 标题行号，数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
}
/**
* 构造函数
*
* @param file 导入文件对象
* @param headerNum 标题行号，数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
}
/**
* 构造函数
*
* @param path 导入文件对象
* @param headerNum 标题行号，数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
if (StringUtils.isBlank(fileName)) {
throw new RuntimeException("导入文档为空!");
} else if (fileName.toLowerCase().endsWith("xls")) {
this.wb = new HSSFWorkbook(is);
} else if (fileName.toLowerCase().endsWith("xlsx")) {
this.wb = new XSSFWorkbook(is);
} else {
throw new RuntimeException("文档格式不正确!");
}
if (this.wb.getNumberOfSheets() < sheetIndex) {
throw new RuntimeException("文档中没有工作表!");
}
this.sheet = this.wb.getSheetAt(sheetIndex);
this.headerNum = headerNum;
log.debug("Initialize success.");
}
/**
* 获取行对象
*
* @param rownum
* @return
*/
public Row getRow(int rownum) {
return this.sheet.getRow(rownum);
}
/**
* 获取数据行号
*
* @return
*/
public int getDataRowNum() {
return headerNum + 1;
}
/**
* 获取最后一个数据行号
*
* @return
*/
public int getLastDataRowNum() {
return this.sheet.getLastRowNum() + headerNum;
}
/**
* 获取最后一个列号
*
* @return
*/
public int getLastCellNum() {
return this.getRow(headerNum).getLastCellNum();
}
/**
* 获取单元格值
*
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
public Object getCellValue(Row row, int column) {
Object val = "";
try {
Cell cell = row.getCell(column);
if (cell != null) {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
val = cell.getNumericCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
val = cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
val = cell.getCellFormula();
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
val = cell.getBooleanCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
val = cell.getErrorCellValue();
}
}
} catch (Exception e) {
return val;
}
return val;
}
/**
* 获取导入数据列表
*
* @param cls 导入对象类型
* @param groups 导入分组
*/
public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException {
List<Object[]> annotationList = Lists.newArrayList();
// Get annotation field
Field[] fs = cls.getDeclaredFields();
for (Field f : fs) {
ExcelField ef = f.getAnnotation(ExcelField.class);
if (ef != null && (ef.type() == 0 || ef.type() == 2)) {
if (groups != null && groups.length > 0) {
boolean inGroup = false;
for (int g : groups) {
if (inGroup) {
break;
}
for (int efg : ef.groups()) {
if (g == efg) {
inGroup = true;
annotationList.add(new Object[]{ef, f});
break;
}
}
}
} else {
annotationList.add(new Object[]{ef, f});
}
}
}
// Get annotation method
Method[] ms = cls.getDeclaredMethods();
for (Method m : ms) {
ExcelField ef = m.getAnnotation(ExcelField.class);
if (ef != null && (ef.type() == 0 || ef.type() == 2)) {
if (groups != null && groups.length > 0) {
boolean inGroup = false;
for (int g : groups) {
if (inGroup) {
break;
}
for (int efg : ef.groups()) {
if (g == efg) {
inGroup = true;
annotationList.add(new Object[]{ef, m});
break;
}
}
}
} else {
annotationList.add(new Object[]{ef, m});
}
}
}
// Field sorting
Collections.sort(annotationList, new Comparator<Object[]>() {
@Override
public int compare(Object[] o1, Object[] o2) {
return new Integer(((ExcelField) o1[0]).sort()).compareTo(
new Integer(((ExcelField) o2[0]).sort()));
}
;
});
//log.debug("Import column count:"+annotationList.size());
// Get excel data
List<E> dataList = Lists.newArrayList();
for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {
E e = (E) cls.newInstance();
int column = 0;
Row row = this.getRow(i);
StringBuilder sb = new StringBuilder();
for (Object[] os : annotationList) {
Object val = this.getCellValue(row, column++);
if (val != null) {
ExcelField ef = (ExcelField) os[0];
// If is dict type, get dict value
if (StringUtils.isNotBlank(ef.dictType())) {
val = DictUtils.getDictValue(val.toString(), ef.dictType(), "");
//log.debug("Dictionary type value: ["+i+","+colunm+"] " + val);
}
// Get param type and type cast
Class<?> valType = Class.class;
if (os[1] instanceof Field) {
valType = ((Field) os[1]).getType();
} else if (os[1] instanceof Method) {
Method method = ((Method) os[1]);
if ("get".equals(method.getName().substring(0, 3))) {
valType = method.getReturnType();
} else if ("set".equals(method.getName().substring(0, 3))) {
valType = ((Method) os[1]).getParameterTypes()[0];
}
}
//log.debug("Import value type: ["+i+","+column+"] " + valType);
try {
if (valType == String.class) {
String s = String.valueOf(val.toString());
if (StringUtils.endsWith(s, ".0")) {
val = StringUtils.substringBefore(s, ".0");
} else {
val = String.valueOf(val.toString());
}
} else if (valType == Integer.class) {
val = Double.valueOf(val.toString()).intValue();
} else if (valType == Long.class) {
val = Double.valueOf(val.toString()).longValue();
} else if (valType == Double.class) {
val = Double.valueOf(val.toString());
} else if (valType == Float.class) {
val = Float.valueOf(val.toString());
} else if (valType == Date.class) {
val = DateUtil.getJavaDate((Double) val);
} else {
if (ef.fieldType() != Class.class) {
val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
} else {
val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
"fieldtype." + valType.getSimpleName() + "Type")).getMethod("getValue", String.class).invoke(null, val.toString());
}
}
} catch (Exception ex) {
log.info("Get cell value [" + i + "," + column + "] error: " + ex.toString());
val = null;
}
// set entity value
if (os[1] instanceof Field) {
Reflections.invokeSetter(e, ((Field) os[1]).getName(), val);
} else if (os[1] instanceof Method) {
String mthodName = ((Method) os[1]).getName();
if ("get".equals(mthodName.substring(0, 3))) {
mthodName = "set" + StringUtils.substringAfter(mthodName, "get");
}
Reflections.invokeMethod(e, mthodName, new Class[]{valType}, new Object[]{val});
}
}
sb.append(val + ", ");
}
dataList.add(e);
log.debug("Read success: [" + i + "] " + sb.toString());
}
return dataList;
}
// /**
// * 导入测试
// */
// public static void main(String[] args) throws Throwable {
//
// ImportExcel ei = new ImportExcel("target/export.xlsx", 1);
//
// for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) {
// Row row = ei.getRow(i);
// for (int j = 0; j < ei.getLastCellNum(); j++) {
// Object val = ei.getCellValue(row, j);
// System.out.print(val+", ");
// }
// System.out.print("\n");
// }
//
// }
}
/**
* 把行信息转换为对象信息。
*
* @param clazz 需要转换的对象
* @param rowsMap 行信息
* @return
*/
public static List<Object> addObjectToData(Class clazz, Map<Integer, Map<String, Object>> rowsMap) {
List<Object> data = new ArrayList<>();
//获取所有的行数
rowsMap.forEach((index, value) -> {
data.add(JSONMapToBean.caseToJavaBean(JSONObject.parseObject(new SimpleJsonFormat(value).serialize()), clazz));
});
return data;
}newSimpleJsonFormat的路径对象转换为json字符串_beanutils.isbasictype-优快云博客
package com.suyun.utils.json;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.parser.ParserConfig;
import com.alibaba.fastjson.parser.deserializer.FieldDeserializer;
import com.alibaba.fastjson.util.TypeUtils;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
public class JSONMapToBean<T> {
public static <T> T caseToJavaBean(JSONObject jsonObject, Class<T> clazz) {
try {
Map map = (Map) jsonObject;
ParserConfig mapping = ParserConfig.getGlobalInstance();
Map<String, FieldDeserializer> setters = mapping.getFieldDeserializers(clazz);
Constructor<T> constructor = clazz.getDeclaredConstructor();
if (!constructor.isAccessible()) {
constructor.setAccessible(true);
}
T object = constructor.newInstance();
for (Map.Entry<String, FieldDeserializer> entry : setters.entrySet()) {
String key = entry.getKey();
FieldDeserializer fieldDeser = entry.getValue();
Method method = fieldDeser.getMethod();
if (map.containsKey(key)) {
Object value = map.get(key);
if (value instanceof Map) {
method.invoke(object, JSONObject.toJavaObject(JSONObject.parseObject(new SimpleJsonFormat(map.get(key)).serialize()), fieldDeser.getFieldClass()));
}
if (method != null) {
Type paramType = method.getGenericParameterTypes()[0];
value = TypeUtils.cast(value, paramType, mapping);
method.invoke(object, new Object[]{value});
} else {
Field field = fieldDeser.getField();
Type paramType = field.getGenericType();
value = TypeUtils.cast(value, paramType, mapping);
field.set(object, value);
}
}
}
return object;
} catch (Exception e) {
throw new JSONException(e.getMessage(), e);
}
}
}
扫一扫
1119
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
