JAVA 导入EXCEL数据 图片 工具类
仅用于记录附源码如下
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ExcelUtil {
public static <T> List<T> readExcel(MultipartFile file, Class<T> clazz) {
try {
List<T> resultList = new ArrayList<T>();
Workbook workbook = null;
InputStream is = file.getInputStream();
String name = file.getOriginalFilename().toLowerCase();
if (name.contains(".xlsx") || name.contains(".xls")) {
workbook = WorkbookFactory.create(is);
} else {
return null;
}
Sheet sheet = workbook.getSheetAt(0);
int totalRowNum = sheet.getLastRowNum();
int cellLength = sheet.getRow(0).getPhysicalNumberOfCells();
Row firstRow = sheet.getRow(0);
Field[] fields = clazz.getDeclaredFields();
Field[] newFields = new Field[cellLength];
Method m = clazz.getDeclaredMethod("convert", String.class);
Object ob = clazz.newInstance();
for (int i = 0; i < cellLength; i++) {
for (Field field : fields) {
Cell cell = firstRow.getCell(i);
if (m.invoke(ob, getXCellVal(cell)).equals(field.getName())) {
newFields[i] = field;
}
}
}
for (int x = 1; x <= totalRowNum; x++) {
T object = clazz.newInstance();
Row row = sheet.getRow(x);
int a = 0;
for (int y = 0; y < cellLength; y++) {
if (!(row == null)) {
Cell cell = row.getCell(y);
if (cell == null) {
a++;
} else {
Field field = newFields[y];
String value = getXCellVal(cell).replace(" ", "");
if (value != null && !value.equals("")) {
setValue(field, value, object);
}
}
}
}
if (a != cellLength && row != null) {
resultList.add(object);
}
}
return resultList;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
private static void setValue(Field field, String value, Object object) {
try {
field.setAccessible(true);
DateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
if (field.getGenericType().toString().contains("Integer")) {
field.set(object, Integer.valueOf(value));
} else if (field.getGenericType().toString().contains("String")) {
field.set(object, value);
} else if (field.getGenericType().toString().contains("Date")) {
field.set(object, fmt.parse(value));
}
field.setAccessible(false);
} catch (Exception e) {
e.printStackTrace();
}
}
private static String getXCellVal(Cell cell) {
DateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
DecimalFormat df = new DecimalFormat("0.0000");
String val = "";
switch (cell.getCellType()) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
val = fmt.format(cell.getDateCellValue());
} else {
val = df.format(cell.getNumericCellValue());
val = val.replaceAll("0+?$", "").replaceAll("[.]$", "");
}
break;
case STRING:
val = cell.getStringCellValue();
break;
case BOOLEAN:
val = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK:
val = cell.getStringCellValue();
break;
case ERROR:
val = "";
break;
case FORMULA:
try {
val = String.valueOf(cell.getStringCellValue());
} catch (IllegalStateException e) {
val = String.valueOf(cell.getNumericCellValue());
}
break;
default:
val = cell.getRichStringCellValue() == null ? null : cell.getRichStringCellValue().toString();
}
return val;
}
public static List<Map<Integer, String>> read(MultipartFile file) {
try {
List<Map<Integer, String>> list = new ArrayList<Map<Integer, String>>();
Workbook workbook = null;
InputStream is = file.getInputStream();
String name = file.getOriginalFilename().toLowerCase();
if (name.contains(".xlsx") || name.contains(".xls")) {
workbook = WorkbookFactory.create(is);
}
Sheet sheet = workbook.getSheetAt(0);
int totalRowNum = sheet.getLastRowNum();
int cellLength = sheet.getRow(0).getPhysicalNumberOfCells();
Map<Integer, String> map = null;
for (int x = 0; x <= totalRowNum; x++) {
map = new HashMap<Integer, String>();
Row row = sheet.getRow(x);
int a = 0;
for (int y = 0; y < cellLength; y++) {
if (!(row == null)) {
Cell cell = row.getCell(y);
if (cell == null) {
map.put(y, "");
} else {
map.put(y, getXCellVal(cell));
}
if (map.get(y) == null || "".equals(map.get(y))) {
a++;
}
}
}
if (a != cellLength && row != null) {
list.add(map);
}
}
return list;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}