excel导入导出方案对比

1、使用poi原生导入，导出，功能强大，代码很多，输出流到页面进行下载
2、若依框架对poi导出导入做了很好的封装，生成导出文件保存在服务端，返回给 前端文件名，前端再次请求文件进行下载，都是配合注解实现，简单易用
3、easyexcel导入导出，简单高效易用，直接输出文件流到页面下载

踩坑历程

需求时这样的，导入的数据，将不满足条件的数据导出给用户进行修改再导入，使用若依的导入，发现时间格式的字符串到后端成CST的时间格式，最后发现excel的时间列格式为常规，修改为文本就正确了，还有一个问题，就是导入时会读取空行，会给属性赋值为null或空串。

修改为easyexcel实现，就不会存在上述两个问题，综合来看， 推荐使用easyexcel做导入导出更好。个人观点，仅供参考

使用easyexcel做导入导出关键代码如下：

• 1、引入pom

<!-- 阿里导出工具 easyexcel -->
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.4</version>
</dependency>

• 2、工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;public class EasyExcelUtil {/*** 出Excel(07版.xlsx)到指定路径下* @param path          路径* @param excelName     excel名称* @param sheetName     sheet页名称* @param clazz* @param data*/public static void export2File(String path, String excelName, String sheetName, Class clazz, List data) {String fileName = path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue());EasyExcel.write(fileName, clazz).sheet(sheetName).doWrite(data);}/*** 出Excel(07版.xlsx)到web* @param response      响应体* @param excelName     Excel名称* @param sheetName     sheet页名称* @param clazz         Excel要转换的类型* @param data          要导出的数据* @throws IOException*/public static void export2Web(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws IOException {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");excelName = URLEncoder.encode(excelName, "utf-8");response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);}/*** 出Excel(07版.xlsx)到web  添加表头及内容样式* @param response      响应体* @param excelName     Excel名称* @param sheetName     sheet页名称* @param clazz         Excel要转换的类型* @param data          要导出的数据* @throws IOException*/public static void export2WebAddHead(HttpServletResponse response, String headName, String excelName, String sheetName, Class clazz, List data) throws IOException {//头策略WriteCellStyle headCellStyle = new WriteCellStyle();//表头居中对齐headCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 背景色headCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());//表头字体设置WriteFont headWriteFont = new WriteFont();headWriteFont.setFontHeightInPoints((short) 12);headCellStyle.setWriteFont(headWriteFont);//内容策略WriteCellStyle contentCellStyle = new WriteCellStyle();//设置内容靠左对齐contentCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);// 背景绿色/*contentCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);contentCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());*/// 字体大小WriteFont contentWriteFont = new WriteFont();contentWriteFont.setFontHeightInPoints((short) 12);contentCellStyle.setWriteFont(contentWriteFont);//设置 自动换行contentCellStyle.setWrapped(true);contentCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置边框样式contentCellStyle.setBorderLeft(BorderStyle.THIN);contentCellStyle.setBorderTop(BorderStyle.THIN);contentCellStyle.setBorderRight(BorderStyle.THIN);contentCellStyle.setBorderBottom(BorderStyle.THIN);//这个策略是,头是头的样式,内容是内容的样式,其他的策略可以自己实现HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headCellStyle, contentCellStyle);response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");excelName = URLEncoder.encode(excelName, "utf-8");response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());EasyExcel.write(response.getOutputStream(), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).doWrite(data);}/*** 多个sheet导出* @param response* @param excelName* @param clazz* @param data* @throws IOException*/public static void export2WebMoreSheet(HttpServletResponse response, String excelName,  Class clazz, List data) throws IOException {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");excelName = URLEncoder.encode(excelName, "utf-8");response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());//文件输出到流ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();WriteSheet sheet1 = EasyExcel.writerSheet(0, "用户信息").head(clazz).build();WriteSheet sheet2 = EasyExcel.writerSheet(1, "客户信息").head(clazz).build();excelWriter.write(data, sheet1);excelWriter.write(data, sheet2);excelWriter.finish();}
}

• 3、导入

public void importOrderGrabsingleData(MultipartFile file) throws Exception {List<User> orderList = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(User.class).sheet().doReadSync();
}

• 4、导出

response.setCharacterEncoding("utf-8");
EasyExcelUtil.export2Web(response, UUIDUtil.getUUID(), "信息", User.class, list);

response.setCharacterEncoding("utf-8");
response.getWriter().write(JSON.toJSONString(AjaxResult.warn("数据为空")));