java实现excel导入导出笔记

首先先理解下概念，导出是创建个excel文件(workbook)，里面填充数据，再以流的形式输出即可。
导出简单的说分为两种：
1、直接导出(适用于header不复杂的情况，例如就一行标题，且一一对应)。
2、根据模板进行导出。(如果标题头比较复杂，例如好几行，且还有合并等，用excel实现效果太麻烦，模板最方便)

导入是读取excel文件，拿到数据进行处理。

导出

导出其实比导入逻辑上简单些，所以先说导出。

几种poi工具对比

主流的几种对比下。
poi(apache poi)，easypoi，easyexcel

至于用哪种比较好，见仁见智了。
入门建议poi，更容易理解原理。熟悉后从easypoi和easyexcel种选一种即可，写代码还快些。

最原始的方法

如果确实不知道咋弄，就用最原始的办法，硬解码，由workbook到sheet，再到row，到cell。
一样可以实现功能。
缺点就是变通性不强，比如加减字段还需要调整。

直接创建文件流

根据模板创建文件流

String templateName="template.xls";
//                File file = ResourceUtils.getFile("classpath:export/"+templateName);
//                FileInputStream fileInputStream = new FileInputStream(file);

org.springframework.core.io.Resource resource = new ClassPathResource("export/" + templateName);
//        读取模板文件产生workbook对象,这个workbook是一个有内容的工作薄
String fileName = resource.getURI().toString().substring(resource.getURI().toString().lastIndexOf("/") + 1);
Workbook workbook = getWorkbook(resource.getInputStream(), templateName);
//                Workbook workbook  = new XSSFWorkbook(file);
//        读取工作薄的第一个工作表，向工作表中放数据
Sheet sheet = workbook.getSheetAt(0);

输出文件流

最简单的，其实用HttpServletResponse就行。

String filename="导出文件名.xls";
//            设置文件的打开方式和mime类型
ServletOutputStream outputStream = response.getOutputStream();
response.setHeader( "Content-Disposition", "attachment;filename="  + new String(filename.getBytes(),"ISO8859-1"));
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
workbook.write(outputStream);

进阶玩法

能自动吗? 当然可以，事实上人家poi等产品也是这么设计的，例如注解等，都是为了自动化导入导出。

步骤

无论再复杂的excel导出，都有基本的步骤。
1、查询到数据。
2、根据字典转换数据(因为不像页面可以根据字典值转，所以导出前要转好)。
3、转换list中的实体数据为entityMap数据，方便之后一一对应。(模拟代码见后面)

写标题(根据复杂度选择是用模板、还是用excel代码生成)。
写数据。
因为数据已经转换为map，所以这里for循环columns，从map中根据key找对应值即可。

列名、列值

heads 设置为一个数组，columns也是一个数组，他们一一对应。

private String[] heads; 
private String[] columns;

至于采用什么赋值方式看自己了，可以固定写到配置文件中，也可以动态的。

当然，标题行和属性行也可以用map，两者都是可以的。 map的话直接写在代码里也比较方便，毕竟导出需求不经常变，缺点是不可配置。 如果一定要配置，也可以配置成json格式，再解析为map。

实体类转换为map代码

网络上到处都是的通用代码：

public static Map<String, Object> entityToMap(Object obj) {
    Map<String, Object> params = new HashMap<String, Object>(0);
    try {
        PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
        PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(obj);
        for (int i = 0; i < descriptors.length; i++) {
            String name = descriptors[i].getName();
            if (!"class".equals(name)) {
                params.put(name, propertyUtilsBean.getNestedProperty(obj, name));
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return params;
}

导入

导入稍微复杂些，因为导出只需要标题行和属性行。
导入光有这2个还不够。
首先，先读到excel的header，放到map中，headerMap<标题列字段，标题列index>。
数据行没有属性，只有index，所以需要根据index先匹配到列字段。
但是列字段一般是汉字，java操作时主要基于对象，用的是field，这里还要匹配下，也就是说，比导出多了一层。

其他步骤和导入类似，也是根据excel获取到java数据，后面就好操作了。

数据库导出数据的导入 和常规导入其实差别不大，就是列名适配下，或者定制套即可。

导入导出一定要先有文件，根据文件进行定制，文件不同，代码可能就不适配了。

导入结果反馈

成功了无需反馈，如果失败了，反馈很必要。
用json返回还是excel反馈。
当然是excel好，因为无论多少条有问题，excel都能处理，而且很清晰。
json的话，处理的数据量很少，而且不可复用。

导入结果是一条还是多条?
分情况的。
全局错误是一条。# new 一个空excelVo对象，message填错误信息即可。
明细错误是多条。# 错误的信息附在vo后即可。

导入导出记录查询

条数较多时，处理可能比较慢，比较优雅的方式是异步，回头再拿结果。

注：无论导入成功还是失败，记录都一定要覆盖到。

其他

excel空行的判断

完全空行还好说。
但是空格，或者空字符串的空行如何判断呢?

导入的数据校验与兼容

场景：
日期字段，值会根据该单元格的格式而变化。
例如：
日期格式 返回 2025/01-02
数值格式 返回 46700 这样的数值

那么如何处理呢?
推荐在收到值时做校验，规定一种格式，如果值格式不正确，直接报错。

如果是改别人的代码，那么可以考虑兼容格式。
1、数值类型用java代码解析。
2、年月日、左斜杠、横岗等都替换为横线，这样便于统一解析。

通用创建workbook的代码

这里需要考虑下excel版本(xls，xlsx)，因为这两种格式创建workbook的代码不一样，弄混了打不开或读取不了。
一般根据后缀名判断就行。

代码：

private final static String excel2003minus =".xls";    //2003- 版本的excel
private final static String excel2007plus =".xlsx";   //2007+ 版本的excel

public static  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
    Workbook wb = null;
    String fileType = fileName.substring(fileName.lastIndexOf("."));
    if(excel2003minus.equals(fileType)){
        wb = new HSSFWorkbook(inStr);  //2003-
    }else if(excel2007plus.equals(fileType)){
        wb = new XSSFWorkbook(inStr);  //2007+
    }else{
        throw new Exception("文件格式解析有误！");
    }
    return wb;
}

很显然，不同版本创建workbook用的代码不一样

new HSSFWorkbook(inStr);  //2003- 后缀名是 xls
new XSSFWorkbook(inStr);  //2007+ 后缀名是 xlsx

大胆的想法 有没有可能行级别的根据规则进行匹配

自定义注解

为什么要说自定义注解呢?
标准的excel类库都有注解，看起来云里雾里。 自己写一遍就清楚了。

自定义注解

注解定义，代码：

@Target({ElementType.FIELD,ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface CustomExcelColumn {
    String name();
    int index();
    String dictGroup() default "";
}

注解的应用：

public class Entity{
    @CustomExcelColumn(name = "用户名",index=1)
    private String username;

    @CustomExcelColumn(name = "用户类型",index=2,dictGroup="userTypeMark")
    private String userType;
}

实现合并单元格

报错 The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF)

完整报错：
The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF)

这就是前面说到的版本问题，看扩展名和文件和创建类是否一致即可。

导出时报错 class path resource [export/template.xls] cannot be resolved to absolute file path because it does not reside in the file system: jar:file:/data/custom-1/custom.jar!/BOOT-INF/classes!/export/template.xls

完整报错：
class path resource [export/template.xls] cannot be resolved to absolute file path because it does not reside in the file system: jar:file:/data/custom-1/custom.jar!/BOOT-INF/classes!/export/template.xls

但是奇怪的是本地测试的时候不报错，不然也不可能发布。
后来找到了原因，和jar包部署的方式有关。
如果是tomcat不会报错，因为文件会展开有BOOT-INF/classes目录。
如果是jar包直接启动会报错，因为文件未展开，所以找不到。

解决方案 调整加载文件的代码即可。

jar包方式找不到。
File file = ResourceUtils.getFile("classpath:export/"+templateName);

这种jar包方式也能找到。
org.springframework.core.io.Resource resource = new ClassPathResource("export/" + templateName);

excel导入的时候报错 java.io.IOException: maxSwallowSize exceeded 参数设置

这个实际不应该算是excel的问题，而是servlet设置的问题。
默认是1M，肯定是不够用的，所以设置大点即可。

servlet:
  multipart:
    max-file-size: 7MB
    max-request-size: 7MB