com.eworkcloud.excel.util.ImportUtils Maven / Gradle / Ivy
package com.eworkcloud.excel.util;
import com.eworkcloud.excel.exception.ExcelException;
import com.eworkcloud.excel.model.ExcelColumn;
import com.eworkcloud.excel.model.ImportParams;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import static com.eworkcloud.excel.enmus.ValueType.PICTURE;
import static com.eworkcloud.excel.util.PoiUtils.DATE_FORMAT;
import static com.eworkcloud.excel.util.PoiUtils.DATE_TIME_FORMAT;
import static com.eworkcloud.excel.util.PoiUtils.TIME_FORMAT;
public abstract class ImportUtils {
// 解析为数值、时间、文本
private static Object numberValue(Cell cell, ExcelColumn column) {
Class clazz = column.getField().getType();
if (clazz == Byte.class || clazz == Byte.TYPE) {
return (byte) cell.getNumericCellValue();
} else if (clazz == Short.class || clazz == Short.TYPE) {
return (short) cell.getNumericCellValue();
} else if (clazz == Integer.class || clazz == Integer.TYPE) {
return (int) cell.getNumericCellValue();
} else if (clazz == Long.class || clazz == Long.TYPE) {
return (long) cell.getNumericCellValue();
} else if (clazz == Float.class || clazz == Float.TYPE) {
return (float) cell.getNumericCellValue();
} else if (clazz == Double.class || clazz == Double.TYPE) {
return cell.getNumericCellValue();
} else if (clazz == BigInteger.class) {
long value = (long) cell.getNumericCellValue();
return BigInteger.valueOf(value);
} else if (clazz == BigDecimal.class) {
double value = cell.getNumericCellValue();
return BigDecimal.valueOf(value);
} else if (clazz == Date.class) {
return cell.getDateCellValue();
} else if (clazz == java.sql.Date.class) {
Date value = cell.getDateCellValue();
return new java.sql.Date(value.getTime());
} else if (clazz == java.sql.Time.class) {
Date value = cell.getDateCellValue();
return new java.sql.Time(value.getTime());
} else if (clazz == java.sql.Timestamp.class) {
Date value = cell.getDateCellValue();
return new java.sql.Timestamp(value.getTime());
} else if (clazz == LocalDate.class) {
Date value = cell.getDateCellValue();
return LocalDateTime.ofInstant(value.toInstant(), ZoneId.systemDefault()).toLocalDate();
} else if (clazz == LocalTime.class) {
Date value = cell.getDateCellValue();
return LocalDateTime.ofInstant(value.toInstant(), ZoneId.systemDefault()).toLocalTime();
} else if (clazz == LocalDateTime.class) {
Date value = cell.getDateCellValue();
return LocalDateTime.ofInstant(value.toInstant(), ZoneId.systemDefault());
} else {
double value = cell.getNumericCellValue();
return String.valueOf(value);
}
}
private static String replaceValue(String value, String[] replace) {
if (StringUtils.hasText(value) && !ObjectUtils.isEmpty(replace)) {
for (String item : replace) {
String[] text = item.split("_");
if (value.equals(text[1])) {
value = text[0];
break;
}
}
}
return value;
}
private static String removeSuffix(String value, String suffix) {
if (StringUtils.hasText(value) && StringUtils.hasText(suffix) && value.endsWith(suffix)) {
return value.substring(0, value.length() - suffix.length());
}
return value;
}
private static Object getCellValue(Cell cell, ExcelColumn column) {
try {
String cellValue = null;
Class clazz = column.getField().getType();
if (cell.getCellType() == CellType.BOOLEAN) {
if (clazz == Boolean.class || clazz == Boolean.TYPE) {
return cell.getBooleanCellValue();
} else {
boolean value = cell.getBooleanCellValue();
cellValue = String.valueOf(value);
}
} else if (cell.getCellType() == CellType.NUMERIC) {
Object value = numberValue(cell, column);
if (value instanceof String) {
cellValue = (String) value;
} else {
return value;
}
} else if (cell.getCellType() == CellType.FORMULA) {
cellValue = cell.getStringCellValue();
} else if (cell.getCellType() == CellType.STRING) {
cellValue = cell.getStringCellValue();
} else if (cell.getCellType() == CellType.BLANK) {
cellValue = "";
}
cellValue = removeSuffix(cellValue, column.getSuffix());
cellValue = replaceValue(cellValue, column.getReplace());
if (clazz == String.class) {
return cellValue;
} else if (!StringUtils.hasText(cellValue)) {
return null;
} else if (clazz == Boolean.class || clazz == Boolean.TYPE) {
return Boolean.valueOf(cellValue);
} else if (clazz == Character.class || clazz == Character.TYPE) {
return cellValue.charAt(0);
} else if (clazz == Byte.class || clazz == Byte.TYPE) {
return Byte.valueOf(cellValue);
} else if (clazz == Short.class || clazz == Short.TYPE) {
return Short.valueOf(cellValue);
} else if (clazz == Integer.class || clazz == Integer.TYPE) {
return Integer.valueOf(cellValue);
} else if (clazz == Long.class || clazz == Long.TYPE) {
return Long.valueOf(cellValue);
} else if (clazz == Float.class || clazz == Float.TYPE) {
return Float.valueOf(cellValue);
} else if (clazz == Double.class || clazz == Double.TYPE) {
return Double.valueOf(cellValue);
} else if (clazz == BigInteger.class) {
long value = Long.parseLong(cellValue);
return BigInteger.valueOf(value);
} else if (clazz == BigDecimal.class) {
double value = Double.parseDouble(cellValue);
return BigDecimal.valueOf(value);
} else if (clazz == Date.class) {
SimpleDateFormat formatter;
if (StringUtils.hasText(column.getFormat())) {
formatter = new SimpleDateFormat(column.getFormat());
} else {
formatter = new SimpleDateFormat(DATE_TIME_FORMAT);
}
return formatter.parse(cellValue);
} else if (clazz == java.sql.Date.class) {
SimpleDateFormat formatter;
if (StringUtils.hasText(column.getFormat())) {
formatter = new SimpleDateFormat(column.getFormat());
} else {
formatter = new SimpleDateFormat(DATE_FORMAT);
}
Date date = formatter.parse(cellValue);
return new java.sql.Date(date.getTime());
} else if (clazz == java.sql.Time.class) {
SimpleDateFormat formatter;
if (StringUtils.hasText(column.getFormat())) {
formatter = new SimpleDateFormat(column.getFormat());
} else {
formatter = new SimpleDateFormat(TIME_FORMAT);
}
Date date = formatter.parse(cellValue);
return new java.sql.Time(date.getTime());
} else if (clazz == java.sql.Timestamp.class) {
SimpleDateFormat formatter;
if (StringUtils.hasText(column.getFormat())) {
formatter = new SimpleDateFormat(column.getFormat());
} else {
formatter = new SimpleDateFormat(DATE_TIME_FORMAT);
}
Date date = formatter.parse(cellValue);
return new java.sql.Timestamp(date.getTime());
} else if (clazz == LocalDate.class) {
DateTimeFormatter formatter;
if (StringUtils.hasText(column.getFormat())) {
formatter = DateTimeFormatter.ofPattern(column.getFormat());
} else {
formatter = DateTimeFormatter.ofPattern(DATE_FORMAT);
}
return LocalDate.parse(cellValue, formatter);
} else if (clazz == LocalTime.class) {
DateTimeFormatter formatter;
if (StringUtils.hasText(column.getFormat())) {
formatter = DateTimeFormatter.ofPattern(column.getFormat());
} else {
formatter = DateTimeFormatter.ofPattern(TIME_FORMAT);
}
return LocalTime.parse(cellValue, formatter);
} else if (clazz == LocalDateTime.class) {
DateTimeFormatter formatter;
if (StringUtils.hasText(column.getFormat())) {
formatter = DateTimeFormatter.ofPattern(column.getFormat());
} else {
formatter = DateTimeFormatter.ofPattern(DATE_TIME_FORMAT);
}
return LocalDateTime.parse(cellValue, formatter);
}
return cellValue;
} catch (Exception ex) {
throw new ExcelException(cell.getRowIndex(), cell.getColumnIndex(), column.getName(), ex);
}
}
private static List getColumns(Class clazz, Row row) {
// 获取列配置
List columns = PoiUtils.getColumns(clazz);
for (ExcelColumn column : columns) {
column.setIndex(Integer.MAX_VALUE);
}
// 获取导入列
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
Cell cell = cells.next();
int columnIndex = cell.getColumnIndex();
String columnValue = cell.getStringCellValue();
if (StringUtils.hasText(columnValue)) {
for (ExcelColumn column : columns) {
if (column.getName().equals(columnValue)) {
column.setIndex(columnIndex);
}
}
}
}
// 检测验证字段
for (ExcelColumn column : columns) {
if (column.isRequired() && column.getIndex() == Integer.MAX_VALUE) {
throw new RuntimeException("Excel document not recognized");
}
}
columns.removeIf(m -> m.getIndex() == Integer.MAX_VALUE);
Collections.sort(columns);
return columns;
}
private static List importSheetRow(Sheet sheet, ImportParams params, Class clazz) {
// 跳过标题
Iterator rows = sheet.rowIterator();
for (int i = 0; i < params.getTitleRows(); i++) {
rows.next();
}
// 获取表头
List columns = getColumns(clazz, rows.next());
// 获取图片
Map> pictures = PoiUtils.getPictures(sheet);
List entities = new ArrayList<>();
while (rows.hasNext()) {
Row row = rows.next();
T entity = ClassUtils.newInstance(clazz);
for (ExcelColumn column : columns) {
if (column.getType() == PICTURE) {
String key = row.getRowNum() + "_" + column.getIndex();
List images = pictures.getOrDefault(key, new ArrayList<>());
List values = new ArrayList<>();
for (PictureData image : images) {
values.add(image.getData());
}
if (!CollectionUtils.isEmpty(values)) {
if (ClassUtils.isCollection(column.getField())) {
ClassUtils.setValue(column.getField(), entity, values);
} else {
ClassUtils.setValue(column.getField(), entity, values.get(0));
}
}
} else {
Cell cell = row.getCell(column.getIndex());
if (null != cell) {
Object value = getCellValue(cell, column);
if (null != value) {
ClassUtils.setValue(column.getField(), entity, value);
}
}
}
}
entities.add(entity);
}
return entities;
}
/**
* 导入Excel
*
* @param workbook Workbook
* @param clazz 类
* @param params 参数
* @param 类型
* @return 指定类型列表
*/
public static List importExcel(Workbook workbook, Class clazz, ImportParams params) {
Sheet sheet = null;
if (StringUtils.hasText(params.getSheetName())) {
sheet = workbook.getSheet(params.getSheetName());
}
if (null == sheet) {
sheet = workbook.getSheetAt(params.getSheetIndex());
}
return importSheetRow(sheet, params, clazz);
}
}
| © 2015 - 2024 Weber Informatics LLC | Privacy Policy