总结
- 首先创建实体类,然后新建excel文件,第一行的值为Project对应的属性值。
- 写数据访问对象ProjectDao ,继承自JpaRepository。
- ExcelUtils 通用工具类读取excel文件,根据project对象的属性对应赋值(可以实现数据格式的转换,如日期,时间戳,枚举、基本类型),生成project对象。
- FileController将数据保存到数据库。
1、Project 实体类
public class Project {
private String id;
private String projectName;
private String leaderId;
}
2、ProjectDao 数据访问对象
@Repository
public interface ProjectDao extends JpaRepository<Project, String> {}
3、FileController控制器
@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
@ApiOperation(value = "导入项目")
public Object importExcel(@RequestParam MultipartFile file) throws Exception {
String name = file.getOriginalFilename();
System.out.println("上传:" + name);
List<Project> list = ExcelUtils.readExcelToEntity(Project.class, file.getInputStream(), name);
return = fileDao.saveProject(list);
}
4、ExcelUtils 通用工具类
package ccsah.avatar.web.component.utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class ExcelUtils {
private static final String FULL_DATA_FORMAT = "yyyy/MM/dd HH:mm:ss";
private static final String SHORT_DATA_FORMAT = "yyyy/MM/dd";
public static <T> List<T> readExcelToEntity(Class<T> classzz, InputStream in, String fileName, List<ExcelHead> excelHeads) throws Exception {
checkFile(fileName);
Workbook workbook = getWorkBoot(in, fileName);
List<T> excelForBeans = readExcel(classzz, workbook, excelHeads);
return excelForBeans;
}
public static <T> List<T> readExcelToEntity(Class<T> classzz, InputStream in, String fileName) throws Exception {
return readExcelToEntity(classzz, in, fileName,null);
}
public static void checkFile(String fileName) throws Exception {
if (!StringUtils.isEmpty(fileName) && !(fileName.endsWith(".xlsx") || fileName.endsWith(".xls"))) {
throw new Exception("不是Excel文件!");
}
}
private static Workbook getWorkBoot(InputStream in, String fileName) throws IOException {
if (fileName.endsWith(".xlsx")) {
return new XSSFWorkbook(in);
} else {
return new HSSFWorkbook(in);
}
}
private static <T> List<T> readExcel(Class<T> classzz, Workbook workbook, List<ExcelHead> excelHeads) throws Exception {
List<T> beans = new ArrayList<T>();
int sheetNum = workbook.getNumberOfSheets();
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
String sheetName=sheet.getSheetName();
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
Row head = sheet.getRow(firstRowNum);
if (head == null)
continue;
short firstCellNum = head.getFirstCellNum();
short lastCellNum = head.getLastCellNum();
Field[] fields = classzz.getDeclaredFields();
for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) {
Row dataRow = sheet.getRow(rowIndex);
if (dataRow == null)
continue;
T instance = classzz.newInstance();
if(CollectionUtils.isEmpty(excelHeads)){
firstCellNum=dataRow.getFirstCellNum();
lastCellNum=dataRow.getLastCellNum();
}
for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) {
Cell headCell = head.getCell(cellIndex);
if (headCell == null)
continue;
Cell cell = dataRow.getCell(cellIndex);
headCell.setCellType(Cell.CELL_TYPE_STRING);
String headName = headCell.getStringCellValue().trim();
if (StringUtils.isEmpty(headName)) {
continue;
}
ExcelHead eHead = null;
if (!CollectionUtils.isEmpty(excelHeads)) {
for (ExcelHead excelHead : excelHeads) {
if (headName.equals(excelHead.getExcelName())) {
eHead = excelHead;
headName = eHead.getEntityName();
break;
}
}
}
for (Field field : fields) {
if (headName.equalsIgnoreCase(field.getName())) {
String methodName = MethodUtils.setMethodName(field.getName());
Method method = classzz.getMethod(methodName, field.getType());
if (isDateFied(field)) {
Date date=null;
if(cell!=null){
date=cell.getDateCellValue();
}
if (date == null) {
volidateValueRequired(eHead,sheetName,rowIndex);
break;
}
method.invoke(instance, cell.getDateCellValue());
} else if (isTimeStamp(field)) {
Date date=null;
if(cell!=null){
date = cell.getDateCellValue();
}
if (date == null) {
volidateValueRequired(eHead,sheetName,rowIndex);
break;
}
method.invoke(instance, new Timestamp(cell.getDateCellValue().getTime()));
}else if (isEnum(field)) {
String value = null;
if(cell!=null){
value=cell.getStringCellValue();
}
if (StringUtils.isEmpty(value)) {
volidateValueRequired(eHead,sheetName,rowIndex);
break;
}
method.invoke(instance, Enum.valueOf((Class)field.getType(), value));
}
else {
String value = null;
if(cell!=null){
cell.setCellType(Cell.CELL_TYPE_STRING);
value=cell.getStringCellValue();
}
if (StringUtils.isEmpty(value)) {
volidateValueRequired(eHead,sheetName,rowIndex);
break;
}
method.invoke(instance, convertType(field.getType(), value.trim()));
}
break;
}
}
}
beans.add(instance);
}
}
return beans;
}
private static boolean isDateFied(Field field) {
return (Date.class == field.getType());
}
private static boolean isTimeStamp(Field field) {
return (Timestamp.class == field.getType());
}
private static boolean isEnum(Field field) {
return field.getType().isEnum();
}
private static void volidateValueRequired(ExcelHead excelHead,String sheetName,int rowIndex) throws Exception {
if (excelHead != null && excelHead.isRequired()) {
throw new Exception("《"+sheetName+"》第"+(rowIndex+1)+"行:\""+excelHead.getExcelName() + "\"不能为空!");
}
}
private static Object convertType(Class classzz, String value) {
if (Integer.class == classzz || int.class == classzz) {
return Integer.valueOf(value);
}
if (Short.class == classzz || short.class == classzz) {
return Short.valueOf(value);
}
if (Byte.class == classzz || byte.class == classzz) {
return Byte.valueOf(value);
}
if (Character.class == classzz || char.class == classzz) {
return value.charAt(0);
}
if (Long.class == classzz || long.class == classzz) {
return Long.valueOf(value);
}
if (Float.class == classzz || float.class == classzz) {
return Float.valueOf(value);
}
if (Double.class == classzz || double.class == classzz) {
return Double.valueOf(value);
}
if (Boolean.class == classzz || boolean.class == classzz) {
return Boolean.valueOf(value.toLowerCase());
}
if (BigDecimal.class == classzz) {
return new BigDecimal(value);
}
return value;
}
static class MethodUtils {
private static final String SET_PREFIX = "set";
private static final String GET_PREFIX = "get";
private static String capitalize(String name) {
if (name == null || name.length() == 0) {
return name;
}
return name.substring(0, 1).toUpperCase() + name.substring(1);
}
public static String setMethodName(String propertyName) {
return SET_PREFIX + capitalize(propertyName);
}
public static String getMethodName(String propertyName) {
return GET_PREFIX + capitalize(propertyName);
}
}
}
5 、ExcelHead类
public class ExcelHead {
private String excelName;
private String entityName;
private boolean required=false;
public String getExcelName() {
return excelName;
}
public void setExcelName(String excelName) {
this.excelName = excelName;
}
public String getEntityName() {
return entityName;
}
public void setEntityName(String entityName) {
this.entityName = entityName;
}
public boolean isRequired() {
return required;
}
public void setRequired(boolean required) {
this.required = required;
}
public ExcelHead(String excelName, String entityName, boolean required) {
this.excelName = excelName;
this.entityName = entityName;
this.required = required;
}
public ExcelHead(String excelName, String entityName) {
this.excelName = excelName;
this.entityName = entityName;
}
public ExcelHead(){};
}