EasyExcel 解决localDate、localDateTime导出报错问题

---

前言

导出execl文件是所有公司都必不可少的功能，但是之前导出都是poi原生导出，代码量多，不同的模板要定制化代码，增加了代码量，耗内存。EasyExcel 以使用简单、节省内存著称。但是对localDate、localDateTime这种日期格式是不支持的，需要手动处理。

---

一、使用步骤

1.引入依赖

代码如下（示例）：

 <!-- excel实用导入 -->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>3.1.1</version>
            </dependency>

2.导出工具类

代码如下（示例）：

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.converters.ConverterKeyBuild;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.nh.cloud.base.utils.excelextra.EasyExcelLocalDateConverter;
import com.nh.cloud.base.utils.excelextra.EasyExcelLocalDateTimeConverter;
import com.nh.farm.cloud.app.utils.excelExpUtils.CustomCellWriteHandler;
import com.nh.farm.cloud.collect.enums.MenuEnum;
import com.nh.fk.common.base.exception.BusinessException;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;


/**
 * @Author 绝伦
 * @Version 1.0
 */
public class ExcelTemplate {

    /**
     * 
     * @param fileName 导出文件宁次
     * @param filePath 文件模板存放位置
     * @param list 数据集合
     * @param map  单个处理的数据
     * @param response 
     * @throws Exception
     */
    public static void ExcelTemplate(String fileName, String filePath, List list, Map<String, Object> map, HttpServletResponse response) throws Exception {


        ExcelWriter excelWriter = null;
        try {
           
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String filename = URLEncoder.encode(fileName, "utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");
            //使用response.getOutputStream()下载,并使用项目下的模板填充
            excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(filePath).build();

            // LocalDate转化器，导入导出都可以使用
            EasyExcelLocalDateConverter converter = new EasyExcelLocalDateConverter();
            excelWriter.writeContext().currentWriteHolder().converterMap().put(ConverterKeyBuild.buildKey(converter.supportJavaTypeKey()), converter);
            excelWriter.writeContext().currentWriteHolder().converterMap().put(ConverterKeyBuild.buildKey(converter.supportJavaTypeKey(), converter.supportExcelTypeKey()), converter);

// LocalDateTime转化器，导入导出都可以使用
            EasyExcelLocalDateTimeConverter localDateTimeDateConverter = new EasyExcelLocalDateTimeConverter();
            excelWriter.writeContext().currentWriteHolder().converterMap().put(ConverterKeyBuild.buildKey(localDateTimeDateConverter.supportJavaTypeKey()), localDateTimeDateConverter);
            excelWriter.writeContext().currentWriteHolder().converterMap().put(ConverterKeyBuild.buildKey(localDateTimeDateConverter.supportJavaTypeKey(), localDateTimeDateConverter.supportExcelTypeKey()), localDateTimeDateConverter);

            WriteSheet writeSheet =  EasyExcel.writerSheet().build();

            if (map != null) {
                excelWriter.fill(map, writeSheet);//存入map
            }
            if (list != null) {
                excelWriter.fill(list, writeSheet);//存入list
            }

            Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
            workbook.setForceFormulaRecalculation(true);
            excelWriter.finish();


        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            throw new BusinessException(500, "导出失败!");

        }
    }

}

3.localDate转化工具类

代码如下（示例）：

import java.text.ParseException;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.Date;

import org.apache.poi.ss.usermodel.DateUtil;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.nh.cloud.base.utils.LocalUtil;
import com.nh.cloud.base.utils.ServiceException;

/**
 * 导入导出 localDate
 */
public class EasyExcelLocalDateConverter implements Converter<LocalDate> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return LocalDate.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public LocalDate convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
        GlobalConfiguration globalConfiguration) throws ParseException {
    	if(null==cellData) {
    		return null;
    	}
    	LocalDate result=null;
    	if(cellData.getType()==CellDataTypeEnum.NUMBER) {
    		if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
                Date date= DateUtil.getJavaDate(cellData.getNumberValue().doubleValue(),
                    globalConfiguration.getUse1904windowing(), null);
                result =date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
            } else {
            	Date date=  DateUtil.getJavaDate(cellData.getNumberValue().doubleValue(),
                    contentProperty.getDateTimeFormatProperty().getUse1904windowing(), null);
            	result =date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
            }
    	}
    	if(cellData.getType()==CellDataTypeEnum.STRING) {
    		String value=cellData.getStringValue();
    		if(value.contains("-")) {
    			try {
					result= LocalUtil.toLocalDate(cellData.getStringValue());
				} catch (Exception e) {
					e.printStackTrace();
				}
    		}
    		else if(value.contains("/")) {
    			try {
					result= LocalUtil.toLocalDate(value, "yyyy/MM/dd");
				} catch (Exception e) {
					e.printStackTrace();
				}
    		}
    		if(null==result) {
    			throw new ServiceException("日期格式错误，日期格式只支持 yyyy-MM-dd 和 yyyy/MM/dd");
    		}
    	}
    	return result;
    }

    @Override
    public WriteCellData<LocalDate> convertToExcelData(LocalDate value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
    	if(null==value) {
    		return new WriteCellData<>();
    	}
        return new WriteCellData<>(LocalUtil.toStr(value));
    }
}

4.LocalDateTime转化工具类

代码如下（示例）：

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.nh.cloud.base.utils.LocalUtil;
import org.springframework.util.StringUtils;

import java.text.ParseException;
import java.time.LocalDateTime;

public class EasyExcelLocalDateTimeConverter implements Converter<LocalDateTime> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return LocalDateTime.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
       return CellDataTypeEnum.STRING;
    }

    @Override
    public LocalDateTime convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws ParseException {
    	if(null==cellData||!StringUtils.hasLength(cellData.getStringValue())) {
    		return null;
    	}
    	return LocalUtil.toLocalDateTime(cellData.getStringValue());
    }

    @Override
    public WriteCellData<LocalDateTime> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
    	if(null==value) {
    		return new WriteCellData<>();
    	}
        return new WriteCellData<>(LocalUtil.toStr(value));
    }
}

5.模板

总结

以上就是今天要讲的内容，本文仅仅简单介绍了EasyExcel 解决localDate、localDateTime导出报错问题，EasyExcel 为我们提供了简单快速的excel处理数据的工具。