Preface:
This tool class provides the excel import function. Through the reflection mechanism, the data in excel is mapped to the entity class to obtain Excel data. The tool class depends on the org.apache.poi package. It supports RESTful API and Spring MVC.
I. supporting functions of this tool class:
- Support File type import
- Support MultipartFile type import
- Excel2003 and above supported
II. Tools
This tool class can be used directly in the project.
package com.excel.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
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.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
/**
* Excel Import tool class
*/
public class ImportExcelUtil {
// Regular expression, used to determine whether get or set method
private static final String REGEX = "[a-zA-Z]";
private static final Logger log = LoggerFactory.getLogger(ImportExcelUtil.class);
/**
* @param multipartFile
* @param startRow Start line
* @param endRow End line (0 to end of last line)
* @param clazz
* @return
* @throws Exception
*/
public static List<List<Object>> importExcelMultipartFile(MultipartFile multipartFile, int startRow, int endRow,
Class<?> clazz) throws Exception {
// Judge whether the file exists
if (multipartFile == null || multipartFile.isEmpty()) {
throw new IOException("Excel The file content is empty or does not exist!");
}
String name = "Excel" + System.currentTimeMillis();
File file = File.createTempFile(name, null);
multipartFile.transferTo(file);
if (!file.exists()) {
throw new IOException("File name is" + file.getName() + "Excel File does not exist!");
}
return importExcelFile(file, startRow, endRow, clazz);
}
/**
* Import Excel according to file, only import data, remove verification
*
* @param file file
* @param startRow Start line, starting from 0
* @param endRow End lines, 0 for all lines; positive for end of lines; negative for end of last lines
* @param clazz sheet Object type to map
* @return List<List < Object>>
* @throws Exception
*/
public static List<List<Object>> importExcelFile(File file, int startRow, int endRow, Class<?> clazz)
throws Exception {
List<List<Object>> sheetsData = new ArrayList<>();
// Judge whether the file exists
if (!file.exists()) {
throw new IOException("File name is" + file.getName() + "Excel File does not exist!");
}
Workbook wb = null;
FileInputStream inputStream = null;
try {
inputStream = new FileInputStream(file);
// Factory mode, according to Excel Version (before 2003 version, or other versions), create corresponding workbook processing class
wb = WorkbookFactory.create(inputStream);
for (int sheetNumber = 0; sheetNumber < wb.getNumberOfSheets(); sheetNumber++) {
List<Row> rowList = new ArrayList<Row>();
Sheet sheet = wb.getSheetAt(sheetNumber);
// Get last line number
int lastRowNum = sheet.getLastRowNum();
Row row = null;
Pattern p = Pattern.compile("\\s*|\t|\r|\n");
// Cyclic reading
for (int i = startRow; i <= lastRowNum + endRow; i++) {
// Are all cells empty
boolean isEmptyRow = true;
row = sheet.getRow(i);
if (row != null) {
// Get the value of each cell
for (int j = 0; j < row.getLastCellNum(); j++) {
String value = p.matcher(getCellValue(row.getCell(j))).replaceAll("");
if (!value.trim().equals("")) {
isEmptyRow = false;
}
}
// Validation rule: if it is a blank line with no data, only characters such as space character and tab character (characters entered by the user unintentionally), it should be filtered out.
if (!isEmptyRow) {
// If there are non empty cells in the row data, the row will be returned
rowList.add(row);
}
}
}
sheetsData.add(returnObjectList(rowList, clazz));
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (wb != null) {
wb.close();
}
}
return sheetsData;
}
/**
* Function: returns the specified object collection
*/
private static List<Object> returnObjectList(List<Row> rowList, Class<?> clazz) {
List<Object> objectList = null;
Object obj = null;
String attribute = null;
String value = null;
int j = 0;
try {
objectList = new ArrayList<Object>();
Field[] declaredFields = clazz.getDeclaredFields();
for (Row row : rowList) {
j = 0;
obj = clazz.newInstance();
for (Field field : declaredFields) {
try {
attribute = field.getName().toString();
value = getCellValue(row.getCell(j));
setAttrributeValue(obj, attribute, value.trim());
j++;
} catch (Exception e) {
log.info("Property mapping error, property name:" + attribute + "Attribute values:" + value);
e.printStackTrace();
}
}
// Extract only objects without non empty fields
objectList.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
}
return objectList;
}
/**
* Function: get cell value
*/
@SuppressWarnings("deprecation")
private static String getCellValue(Cell cell) {
Object result = "";
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
result = cell.getNumericCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
result = cell.getCellFormula();
break;
case Cell.CELL_TYPE_ERROR:
result = cell.getErrorCellValue();
break;
case Cell.CELL_TYPE_BLANK:
break;
default:
break;
}
}
return result.toString();
}
/**
* Function: assign a value to the specified property of the specified object
*/
private static void setAttrributeValue(Object obj, String attribute, String value) {
if (value == null || value.trim().equals("")) {
return;
}
// Get the set method name of the property
String method_name = convertToMethodName(attribute, obj.getClass(), true);
Method[] methods = obj.getClass().getMethods();
for (Method method : methods) {
/**
* Because only the set method of the property in the bean is called here, and the property name cannot be repeated, so the set method will not be repeated, so you can directly lock a method with the method name (Note: in java, the condition for locking a method is the method name and parameters)
*/
if (method.getName().equals(method_name)) {
Class<?>[] parameterC = method.getParameterTypes();
try {
/**
* If yes (integer, floating-point, Boolean, byte, time type), convert the value value to its own type according to their own rules, otherwise, cast by type (such as String type)
*/
if (parameterC[0] == int.class || parameterC[0] == java.lang.Integer.class) {
int index = value.lastIndexOf(".");
if (index != -1) {
value = value.substring(0, index);
}
try {
method.invoke(obj, Integer.valueOf(value));
} catch (Exception e) {
System.out.println(value);
e.printStackTrace();
}
break;
} else if (parameterC[0] == float.class || parameterC[0] == java.lang.Float.class) {
method.invoke(obj, Float.valueOf(value));
break;
} else if (parameterC[0] == double.class || parameterC[0] == java.lang.Double.class) {
method.invoke(obj, Double.valueOf(value));
break;
} else if (parameterC[0] == byte.class || parameterC[0] == java.lang.Byte.class) {
method.invoke(obj, Byte.valueOf(value));
break;
} else if (parameterC[0] == boolean.class || parameterC[0] == java.lang.Boolean.class) {
method.invoke(obj, Boolean.valueOf(value));
break;
} else if (parameterC[0] == java.util.Date.class) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
Date date = null;
try {
date = sdf.parse(value);
} catch (Exception e) {
e.printStackTrace();
}
method.invoke(obj, date);
break;
} else {
method.invoke(obj, parameterC[0].cast(value));
break;
}
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
}
}
}
}
/**
* Function: generate the corresponding set/get method according to the property
*/
private static String convertToMethodName(String attribute, Class<?> objClass, boolean isSet) {
/** Match the first character with a regular expression**/
Pattern p = Pattern.compile(REGEX);
Matcher m = p.matcher(attribute);
StringBuilder sb = new StringBuilder();
/** If set method name**/
if (isSet) {
sb.append("set");
} else {
/** get Method name**/
try {
Field attributeField = objClass.getDeclaredField(attribute);
/** If the type is boolean**/
if (attributeField.getType() == boolean.class || attributeField.getType() == Boolean.class) {
sb.append("is");
} else {
sb.append("get");
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
}
/** For properties beginning with an underscore**/
if (attribute.charAt(0) != '_' && m.find()) {
sb.append(m.replaceFirst(m.group().toUpperCase()));
} else {
sb.append(attribute);
}
return sb.toString();
}
}
How to use:
1. Import MultipartFile type (recommended)
Application scenario: upload the file through MultipartFile in the Web page, and it can be directly converted to list < list < Object > >.
Use example:
@PostMapping(value = "/project/import")
public ResultObject importAlarmEvents(@RequestParam MultipartFile file) {
try {
// From the first line of Excel to the end of the last line,
List<List<Object>> excelData = ImportExcelUtil.importExcelMultipartFile(file, 1, 0, EventDTO.class);
if (excelData == null || excelData.isEmpty()) {
result.setMessage("Import failed,Excel Content is empty");
return result;
}
//Convert data in Excel to your entity class
List<List<EventDTO>> alarmList = new ArrayList<>();
for (List<?> list : excelData) {
alarmList.add((List<EventDTO>) list);
}
Map<String, Object> res = eventService.importEvent(alarmList);
} catch (Exception e) {
result.setMessage(e.getMessage());
}
return result;
}
2. Import File format is File
Application scenario: upload the File through File in the Web page, which can be directly converted to list < list < Object > >.
Use example:
@PostMapping(value = "/project/import")
public ResultObject importAlarmEvents(@RequestParam File file) {
try {
// From the first line of Excel to the end of the last line,
List<List<Object>> excelData = ImportExcelUtil.importExcelFile(file, 1, 0, EventDTO.class);
if (excelData == null || excelData.isEmpty()) {
result.setMessage("Import failed,Excel Content is empty");
return result;
}
//Convert data in Excel to your entity class
List<List<EventDTO>> alarmList = new ArrayList<>();
for (List<?> list : excelData) {
alarmList.add((List<EventDTO>) list);
}
Map<String, Object> res = eventService.importEvent(alarmList);
} catch (Exception e) {
result.setMessage(e.getMessage());
}
return result;
}
Example entity class:
Note: the attribute declaration order of entity class must be consistent with the Excel column header order, otherwise there will be no correspondence between column data and attribute name
package com.entity.dto;
import java.io.Serializable;
/**
* Data transferred to Excel object note: the order of property names and Excel columns must be the same
*/
@SuppressWarnings("serial")
public class EventDTO implements Serializable {
// Column field 1
private String name;
// Column field 2
private String code;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
}