导入的工具类ImportExcelUtil
package org.springjmis.flyingTeaching.util;
import com.google.common.collect.Lists;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.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 org.springjmis.flyingTeaching.annotation.ExcelField;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
/**
* 导入Excel文件（支持“XLS”和“XLSX”格式）
*/
public class ImportExcelUtil {
private static Logger log = LoggerFactory.getLogger(ImportExcelUtil.class);
/**
* 工作薄对象
*/
private Workbook wb;
/**
* 工作表对象
*/
private Sheet sheet;
/**
* 标题行号
*/
private int headerNum;
/**
* 构造函数
*
* @param multipartFile 导入文件对象
* @param headerNum 标题行号，数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcelUtil(MultipartFile multipartFile, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
}
/**
* 构造函数
*
* @param is 导入文件对象
* @param headerNum 标题行号，数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcelUtil(String fileName, InputStream is, int headerNum, int sheetIndex)
throws 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;
}
/**
* 获取单元格值
*
* @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) {
// 当excel 中的数据为数值或日期是需要特殊处理
if (HSSFDateUtil.isCellDateFormatted(cell)) {
double d = cell.getNumericCellValue();
Date date = HSSFDateUtil.getJavaDate(d);
SimpleDateFormat dformat = new SimpleDateFormat(
"yyyy-MM-dd");
val = dformat.format(date);
} else {
NumberFormat nf = NumberFormat.getInstance();
nf.setGroupingUsed(false);// true时的格式：1,234,567,890
val = nf.format(cell.getNumericCellValue());// 数值类型的数据为double，所以需要转换一下
}
} 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()));
}
});
// 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];
// 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 {
//如果导入的java对象，需要在这里自己进行变换。
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) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
val = sdf.parse(val.toString());
} 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) {
ReflectionsUtil.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");
}
ReflectionsUtil.invokeMethod(e, mthodName, new Class[]{valType}, new Object[]{val});
}
}
sb.append(val + ", ");
}
dataList.add(e);
log.debug("Read success: [" + i + "] " + sb.toString());
}
return dataList;
}
}导入封装的实体类（实际情况看自己的实体）
package org.springjmis.flyingTeaching.dto;
import io.swagger.annotations.ApiModel;
import lombok.Data;
import org.springjmis.flyingTeaching.annotation.ExcelField;
/**
* @author Dell
*/
@Data
@ApiModel(value = "资源导入DTO对象", description = "资源导入DTO对象")
public class DemoDTO {
private static final long serialVersionUID = 1L;
@ExcelField(title = "资源类别序号", align = 2, sort = 1)
private String id;
@ExcelField(title = "所属父级序号", align = 2, sort = 2)
private String parentId;
@ExcelField(title = "资源类别名称", align = 2, sort = 3)
private String name;
@ExcelField(title = "资源类别编号", align = 2, sort = 4)
private String nameCode;
}
自定义注解ExcelField
package org.springjmis.flyingTeaching.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Excel注解定义
* @author Dell
*/
@Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {
/**
* 导出字段名（默认调用当前字段的“get”方法，如指定导出字段为对象，请填写“对象名.对象属性”，例：“area.name”、“office.name”）
*/
String value() default "";
/**
* 导出字段标题（需要添加批注请用“**”分隔，标题**批注，仅对导出模板有效）
*/
String title();
/**
* 字段类型（0：导出导入；1：仅导出；2：仅导入）
*/
int type() default 0;
/**
* 导出字段对齐方式（0：自动；1：靠左；2：居中；3：靠右）
*/
int align() default 0;
/**
* 导出字段字段排序（升序）
*/
int sort() default 0;
/**
* 如果是字典类型，请设置字典的type值
*/
String dictType() default "";
/**
* 反射类型
*/
Class<?> fieldType() default Class.class;
/**
* 字段归属组（根据分组导出导入）
*/
int[] groups() default {};
}是现代吗
@Override
public boolean importExcel(MultipartFile file) {
List<ExpertManageDTO> list = null;
try {
ImportExcelUtil importExcelUtil=new ImportExcelUtil(file,0,0);
list= importExcelUtil.getDataList(ExpertManageDTO.class);
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
List<ExpertManage> listInfo=new ArrayList<>();
for (ExpertManageDTO expertManageDTO : list) {
ExpertManage expertManage=new ExpertManage();
BeanUtil.copyProperties(expertManageDTO,expertManage);
listInfo.add(expertManage);
}
return this.saveBatch(listInfo);
}
扫一扫
5576
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
