// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
// response.setContentType("application/vnd.ms-excel;charset=UTF-8");
// response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
// String fileName= URLEncoder.encode("测试","UTF-8").replaceAll("\+","%20");
// response.setHeader("Content-disposition","attachment;filename*=utf-8''"+fileName+".xlsx");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("测试.xlsx", "UTF-8"));
ExcelWriter writer = EasyExcel.write(response.getOutputStream()).excelType(ExcelTypeEnum.XLSX)
.build();
writer.write(generateUsers(1,1000), EasyExcel.writerSheet(0, "模板").head(User.class).build());
writer.write(generateUsers(1001,2000), EasyExcel.writerSheet(0, "模板").head(User.class).build());
writer.finish();
public class Excel {
public static String getCurrentTime() {
return getCurrentTime(DateUtils.DATE_FORMAT_14);
}
public static String getCurrentTime(String dateFormat) {
return DateUtils.format(new Date(), dateFormat);
}
public static ExcelWriter write(HttpServletResponse response, String fileName) throws BizException {
if (StringUtils.isBlank(fileName)) {
fileName = getCurrentTime();
}
boolean flag = fileName.endsWith(ExcelTypeEnum.XLS.getValue());
if (!flag && !fileName.endsWith(ExcelTypeEnum.XLSX.getValue())) {
fileName = fileName + ExcelTypeEnum.XLSX.getValue();
}
try {
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
return EasyExcel.write(response.getOutputStream()).excelType(flag ? ExcelTypeEnum.XLS : ExcelTypeEnum.XLSX)
.build();
} catch (IOException e) {
log.error("Excel下载异常", e);
throw new BizException(ResultCode.EXCEL_DOWNLOAD_FAIL.getCode(), "Excel写入异常");
}
}
public static ExcelWriter withTemplate(HttpServletResponse response, String fileName, Class head,
InputStream templateStream) throws BizException {
if (StringUtils.isBlank(fileName)) {
fileName = getCurrentTime();
}
boolean flag = fileName.endsWith(ExcelTypeEnum.XLS.getValue());
if (!flag && !fileName.endsWith(ExcelTypeEnum.XLSX.getValue())) {
fileName = fileName + ExcelTypeEnum.XLSX.getValue();
}
try {
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
return EasyExcel.write(response.getOutputStream(), head)
.excelType(flag ? ExcelTypeEnum.XLS : ExcelTypeEnum.XLSX).withTemplate(templateStream).build();
} catch (IOException e) {
log.error("Excel下载异常", e);
throw new BizException(ResultCode.EXCEL_DOWNLOAD_FAIL.getCode(), "Excel写入异常");
}
}
public static ExcelWriter withTemplate(HttpServletResponse response, String fileName, Class head,
String templatePath) throws BizException {
if (StringUtils.isBlank(fileName)) {
fileName = getCurrentTime();
}
boolean flag = fileName.endsWith(ExcelTypeEnum.XLS.getValue());
if (!flag && !fileName.endsWith(ExcelTypeEnum.XLSX.getValue())) {
fileName = fileName + ExcelTypeEnum.XLSX.getValue();
}
try {
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
return EasyExcel.write(response.getOutputStream(), head)
.excelType(flag ? ExcelTypeEnum.XLS : ExcelTypeEnum.XLSX).withTemplate(templatePath).build();
} catch (IOException e) {
log.error("Excel下载异常", e);
throw new BizException(ResultCode.EXCEL_DOWNLOAD_FAIL.getCode(), "Excel写入异常");
}
}
public static ExcelWriter withTemplate(HttpServletResponse response, String fileName,
InputStream templateInputStream) throws BizException {
return withTemplate(response, fileName, null, templateInputStream);
}
public static ExcelWriter withTemplate(HttpServletResponse response, String fileName, String templatePath)
throws BizException {
return withTemplate(response, fileName, null, templatePath);
}
public static WriteSheet writerSheet(Integer sheetNo, String sheetName, Class clazz) {
return EasyExcel.writerSheet(sheetNo, sheetName).head(clazz)
.registerWriteHandler(new ColumnWidthStyleStrategy()).registerWriteHandler(new CustomCellWriteHandler())
.build();
}
public static WriteSheet writerSheet(String sheetName, Class clazz) {
return writerSheet(0, sheetName, clazz);
}
public static WriteSheet writerTemplateSheet(String sheetName) {
return EasyExcel.writerSheet(sheetName).build();
}
public static <T> List<T> getListByExcel(InputStream input, Class<T> clazz) {
return getListByExcel(input, clazz, 1);
}
public static <T> List<T> getListByExcel(InputStream input, Class<T> clazz, int headRowNumber) {
return getListByExcel(input, clazz, headRowNumber, 0);
}
public static <T> List<T> getListByExcel(InputStream input, Class<T> clazz, int headRowNumber, int sheetNo) {
final List<T> rows = new ArrayList<>();
EasyExcel.read(input, clazz, new AnalysisEventListener<T>() {
@Override
public void invoke(T data, AnalysisContext context) {
rows.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {}
}).sheet().headRowNumber(headRowNumber).sheetNo(sheetNo).doRead();
return rows;
}
public static Map<String, List<Map<String, String>>> getListMapByExcel(InputStream input, Integer headerIndex,
int dataBeginIndex, Boolean isXlsx) {
try {
Object book;
if (isXlsx) {
book = new XSSFWorkbook(input);
} else {
book = new HSSFWorkbook(input);
}
Iterator<Sheet> sheetIterator = ((Workbook) book).sheetIterator();
Map<String, List<Map<String, String>>> result = new HashMap<>(((Workbook) book).getNumberOfSheets());
while (sheetIterator.hasNext()) {
Sheet sheet = sheetIterator.next();
Map<Integer, String> headerNameMap = new HashMap<>(1000);
List<Map<String, String>> list = new ArrayList<>();
Iterator<Row> rowIterator = sheet.rowIterator();
boolean initHeaderNameMap = Boolean.FALSE;
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (!initHeaderNameMap && Objects.nonNull(headerIndex) && headerIndex.equals(row.getRowNum())) {
headerNameMap = getExcelHeader(row);
initHeaderNameMap = Boolean.TRUE;
} else if (Objects.isNull(headerIndex)) {
initHeaderNameMap = Boolean.TRUE;
}
if (row.getRowNum() >= dataBeginIndex) {
Map<String, String> rowResult = analysisRow(row, headerNameMap);
if (CommonUtil.isNotEmpty(rowResult)) {
list.add(rowResult);
}
}
}
result.put(sheet.getSheetName(), list);
}
return result;
} catch (Exception var22) {
log.error("getListByExcel", var22);
} finally {
try {
input.close();
} catch (IOException var21) {
log.error("input.close", var21);
}
}
return Collections.emptyMap();
}
private static Map<Integer, String> getExcelHeader(Row row) {
Map<Integer, String> headerNameMap = new HashMap<>(row.getPhysicalNumberOfCells());
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
Cell cell = (Cell) cells.next();
String cellValue = cell.getStringCellValue();
if (!StringUtils.isBlank(cellValue)) {
headerNameMap.put(cell.getColumnIndex(), cellValue.trim());
}
}
return headerNameMap;
}
private static Map<String, String> analysisRow(Row row, Map<Integer, String> headerNameMap) {
try {
Iterator<Cell> cells = row.cellIterator();
Map<String, String> result = new HashMap<>(headerNameMap.size());
while (cells.hasNext()) {
Cell cell = cells.next();
cell.setCellType(CellType.STRING);
String cellValue = cell.getStringCellValue();
String key = null;
if (headerNameMap.containsKey(cell.getColumnIndex())) {
key = headerNameMap.get(cell.getColumnIndex());
}
if (Objects.nonNull(key)) {
result.put(key, cellValue);
}
}
return result;
} catch (Exception var7) {
log.error("analysisRow", var7);
return null;
}
}
}
public class LocalDateConverter implements Converter<LocalDate> {
private static final DateTimeFormatter YMD = DateTimeFormatter.ofPattern(DateUtils.DATE_FORMAT_10);
@Override
public Class supportJavaTypeKey() {
return LocalDate.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDate convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
String source;
if (StringUtils.isBlank(source = cellData.getStringValue())) {
return null;
}
source = source.substring(0, 10);
return LocalDate.parse(source, YMD);
}
@Override
public CellData convertToExcelData(LocalDate value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
if (Objects.isNull(value)) {
return new CellData(CommonUtil.blankStr());
}
return new CellData(value.format(YMD));
}
}
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
public static final String DATE_FORMAT_16 = "yyyy-MM-dd HH:mm";
public static final String DATE_FORMAT_16_FORWARD_SLASH = "yyyy/MM/dd HH:mm";
private static final String TIME_ZONE_TAG = "T";
private static final String NOT_TIMESTAMP_TAG = "-";
private static final String SPACE_TAG = " ";
private static final int DATE_LENGTH_10 = 10;
private static final DateTimeFormatter YMD = DateTimeFormatter.ofPattern(DateUtils.DATE_FORMAT_10);
private static final DateTimeFormatter YMDHMS = DateTimeFormatter.ofPattern(DateUtils.DATE_FORMAT_19);
private static Map<String, DateTimeFormatter> FORMAT_MAP = new ConcurrentHashMap();
private static String switchDateFormat(String dateString) {
switch (dateString.length()) {
case 19:
if (dateString.contains(NOT_TIMESTAMP_TAG)) {
return DateUtils.DATE_FORMAT_19;
} else {
return DateUtils.DATE_FORMAT_19_FORWARD_SLASH;
}
case 17:
return DateUtils.DATE_FORMAT_17;
case 16:
if (dateString.contains(NOT_TIMESTAMP_TAG)) {
return DATE_FORMAT_16;
} else {
return DATE_FORMAT_16_FORWARD_SLASH;
}
case 14:
return DateUtils.DATE_FORMAT_14;
case 10:
return DateUtils.DATE_FORMAT_10;
default:
return null;
}
}
@Override
public Class supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
String source;
if (StringUtils.isBlank(source = cellData.getStringValue())) {
return null;
}
if (source.contains(TIME_ZONE_TAG)) {
return LocalDateTime.parse(source);
}
if (!source.contains(NOT_TIMESTAMP_TAG) && source.contains(SPACE_TAG)) {
long timestamp = Long.valueOf(source);
return LocalDateTime.ofEpochSecond(timestamp / 1000, (int) (timestamp % 1000), ZoneOffset.ofHours(8));
}
int length = source.length();
if (length == DATE_LENGTH_10) {
return LocalDateTime.of(LocalDate.parse(source, YMD), LocalTime.MIN);
} else {
return LocalDateTime.parse(source, DateTimeFormatter.ofPattern(switchDateFormat(source)));
}
}
@SuppressWarnings("deprecation")
@Override
public CellData convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
if (Objects.isNull(value)) {
return new CellData(CommonUtil.blankStr());
}
ExcelProperty annotation = contentProperty.getField().getAnnotation(ExcelProperty.class);
String format;
if (annotation != null && StringUtils.isNotBlank(format = annotation.format())) {
if (Objects.equals(DateUtils.DATE_FORMAT_10, format)) {
return new CellData(YMD.format(value));
} else if (Objects.equals(DateUtils.DATE_FORMAT_19, format)) {
return new CellData(YMDHMS.format(value));
} else if (Objects.nonNull(FORMAT_MAP.get(format))) {
return new CellData(FORMAT_MAP.get(format).format(value));
} else {
DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(format);
FORMAT_MAP.put(format, dateTimeFormatter);
return new CellData(dateTimeFormatter.format(value));
}
}
return new CellData(YMDHMS.format(value));
}
}
public class BooleanChConverter implements Converter<Boolean> {
@Override
public Class supportJavaTypeKey() {
return Boolean.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public Boolean convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
String value = cellData.getStringValue();
if (StringUtils.isBlank(value)) {
return null;
} else {
return Constants.TRUE_CH.equals(StringUtils.trim(value)) ? Boolean.TRUE : Boolean.FALSE;
}
}
@Override
public CellData convertToExcelData(Boolean value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
if (Objects.isNull(value)) {
return null;
}
return new CellData(value ? Constants.TRUE_CH : Constants.FALSE_CH);
}
}
public class BaseEnumConverter implements Converter<AbstractEnum> {
@Override
public Class supportJavaTypeKey() {
return AbstractEnum.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public AbstractEnum convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
String value = cellData.getStringValue();
Class<?> type = contentProperty.getField().getType();
if (StringUtils.isNotBlank(value)) {
AbstractEnum[] es = (AbstractEnum[]) type.getEnumConstants();
for (AbstractEnum e : es) {
if (e.getDesc().equals(value)) {
return e;
}
}
}
return null;
}
@Override
public CellData convertToExcelData(AbstractEnum value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
if (Objects.isNull(value)) {
return new CellData(CommonUtil.blankStr());
}
return new CellData(value.getDesc());
}
}
