DateConverter
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import java.text.SimpleDateFormat;
import java.util.Date;
public class DateConverter implements Converter<Date> {
static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");
static final SimpleDateFormat DATE_TIME_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
@Override
public Class<Date> supportJavaTypeKey() {
return Date.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public Date convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
if (cellData.getType() == CellDataTypeEnum.NUMBER) {
return HSSFDateUtil.getJavaDate(Double.parseDouble(StrUtil.toString(cellData.getNumberValue())));
} else if (cellData.getType() == CellDataTypeEnum.STRING) {
String value = cellData.getStringValue().replace("/", "-").replace(".", "-");
Date date;
try {
if (value.length() <= 10) {
date = DATE_FORMAT.parse(value);
} else {
date = DATE_TIME_FORMAT.parse(value);
}
} catch (Exception e) {
throw new RuntimeException("日期时间格式错误 请修改为 'yyyy-MM-dd'或'yyyy-MM-dd HH:mm:ss' 格式");
}
return date;
} else {
return null;
}
}
@Override
public CellData<String> convertToExcelData(Date value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new CellData<>(DATE_TIME_FORMAT.format(value));
}
}
LocalDateConverter
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import java.time.LocalDate;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Date;
public class LocalDateConverter implements Converter<LocalDate> {
static final DateTimeFormatter FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");
@Override
public Class<LocalDate> supportJavaTypeKey() {
return LocalDate.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDate convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
if (cellData.getType() == CellDataTypeEnum.NUMBER) {
Date date = HSSFDateUtil.getJavaDate(Double.parseDouble(StrUtil.toString(cellData.getNumberValue())));
return date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
} else if (cellData.getType() == CellDataTypeEnum.STRING) {
String value = cellData.getStringValue().replace("/", "-").replace(".", "-");
LocalDate localDate;
try {
String[] strings = value.split("-");
value = strings[0] + "-" + StrUtil.fillBefore(strings[1], '0', 2) + "-" + StrUtil.fillBefore(strings[2], '0', 2);
localDate = LocalDate.parse(value, FORMATTER);
} catch (Exception e) {
throw new RuntimeException("日期时间格式错误 请修改为 'yyyy-MM-dd'格式");
}
return localDate;
} else {
return null;
}
}
@Override
public CellData<String> convertToExcelData(LocalDate value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new CellData<>(value.format(FORMATTER));
}
}
LocalDateTimeConverter
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Date;
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
static final DateTimeFormatter FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
@Override
public Class<LocalDateTime> supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
if (CellDataTypeEnum.NUMBER.equals(cellData.getType())) {
Date date = HSSFDateUtil.getJavaDate(cellData.getNumberValue().doubleValue());
return date.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
} else if (CellDataTypeEnum.STRING.equals(cellData.getType())) {
String value = cellData.getStringValue().replace("/", "-");
if (value.length() <= 10) {
value = value + " 00:00:00";
}
LocalDateTime dateTime;
try {
dateTime = LocalDateTime.parse(value, FORMATTER);
} catch (Exception e) {
throw new RuntimeException("日期时间格式错误 请修改为 'yyyy-MM-dd HH:mm:ss'格式");
}
return dateTime;
} else {
return null;
}
}
@Override
public CellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new CellData<>(value.format(FORMATTER));
}
}