关于使用JXLS使用方法和避开一些隐藏的坑及应用实例介绍

一、加入依赖

<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-reader</artifactId>
    <version>2.0.6</version>
</dependency> 

二、

配置映射文件(store_mapping.xml)

<?xml version="1.0" encoding="ISO-8859-1"?>
    <workbook>
    <!--  这里的name为导入文件的页名，而且必须保持一致--> 
        <worksheet name="Sheet1">

    <!--  这里的startRow为标题起始行，endRow为结束行--> 
            <section startRow="0" endRow="2">
           
            </section>

  <!--  1、这里的startRow为数据的起始行，endRow和startRow保持一致就行，如果你的excel有标题，那startRow就是你标题所在的行
        2、items对应的是实现类中的bean中对应的key
        3、var为配置文件中的对象类的别名，varType为映射的对象类
        4、mapping中的row对应的标题列，col对应具体的列,也就是0对应的就是excel中的A,1对应为B
--> 
            <loop startRow="3" endRow="3" items="list" var="employee" varType="org.jxls.reader.sample.Employee">
                <section startRow="2" endRow="2">
                    <mapping row="2" col="0">employee.name</mapping>
                    <mapping row="2" col="1">employee.age</mapping>
                    <mapping row="2" col="3">employee.payment</mapping>
                    <mapping row="2" col="4">employee.bonus</mapping>
                </section>
 <!--  这里的结束读取数据的条件配置--> 
                <loopbreakcondition>
                    <rowcheck offset="0">
                        <!--  下面的offset="0"表示当读取某列时，当第一列为空就结束读取--> 
                        <cellcheck offset="0"></cellcheck>
                    </rowcheck>
                </loopbreakcondition>
            </loop>
        </worksheet>
    </workbook>

三、配置导入controller类接口

 @PostMapping("/excel/import.do")
 @ResponseBody 
public JsonResult importExcel(@RequestParam("file") MultipartFile file) throws Exception {


// 判断文件是否为空
 if (file.isEmpty()) {
            return JsonResult.fail();
        }
        List<对象> list = null;

 try {


<!-- 这里的映射配置文件需放在根目录 一直按着Ctrl键，点击映射配置文件可以进入到配置文件，来验证是否是正确的路径-->
InputStream inputXML = new BufferedInputStream(类名.getClass().getClassLoader().getResourceAsStream("store_mapping.xml"));
    XLSReader mainReader = ReaderBuilder.buildFromXML( inputXML );
    InputStream inputXLS = new BufferedInputStream(getClass().getResourceAsStream(dataXLS));
    Department department = new Department();
    Department hrDepartment = new Department();
    list = new ArrayList();
    Map<String, List<对象>> beans = new HashMap<>();
    beans.put("list", list);

    // 解析excel，并重新赋值封装到list,调试会发现在这一步之后可发现list的值发生改变
/**
* 这里有一个隐藏的坑，就是当你的excel文件有某些列的单元格格式为日期格式时，在解析的时候如果这列有空值，是会解析报错的停止对该条数据列后面的字段进行解析，从而导致数据丢失的问题。
关于日期数据类型会产生两个问题：
1、当单元格格式是日期类型，接收的对象类型也是日期类型，必须保证日期格式的单元格不能为空，否者会出现上述所说的问题
2、当单元格格式是日期类型，接收的对象非日期类型（如：String） 虽不会报错，但是会出现日期格式的单元格列数据接收不到

解决的办法， 在导入时，日期列统一为非日期格式，如文本类型，
*/
    XLSReadStatus readStatus = mainReader.read( inputXLS, beans);



 // 是否忽略错误  true:不忽略
            ReaderConfig.getInstance().setUseDefaultValuesForPrimitiveTypes( true );
            List<XLSReadMessage>  errors = readStatus.getReadMessages();
            if(!errors.isEmpty()) {
                StringBuilder sb = new StringBuilder();
                for(XLSReadMessage msg:errors) {
                    sb.append(parseXLSReadMessage(msg));
                    sb.append(",");
                }
                sb.setLength(sb.length()-1);
                return JsonResult.failMessage("解析excel出错:"+sb.toString());
            }


   } catch (Exception e) {
          e.printStackTrace();
   }

    // 执行具体的批量导入方法
    jrAgentInventoryService.batchInsert(list);
      return  JsonResult.success();
}



 private String parseXLSReadMessage(XLSReadMessage msg) {
//        String message = "Can't read cell " + getCellName(mapping, rowShift) + " on " + cursor.getSheetName() + " spreadsheet";
        String str = msg.getMessage();
        int start = "Can't read cell ".length();
        int end = str.indexOf("on");
        return str.substring(start,end);
    }

以上为导入时的一些配置和具体的方法及一些坑，下面介绍下导出数据到excel，

@PostMapping("/excel/export.json")
    @ResponseBody
    public JsonResult<String> export(HttpServletResponse response,JrAgentInventoryQuery condtion) {

        // 导出的模板
        String excelTemplate ="excelTemplates/jrAgentInventory_collection_template.xls";
        PageQuery<JrAgentInventory> page = condtion.getPageQuery();
        page.setPageSize(Integer.MAX_VALUE);
        page.setPageNumber(1);
        page.setTotalRow(Integer.MAX_VALUE);
        // 获取数据
        List<JrAgentInventory> jrAgentInventorys =jrAgentInventoryService.queryByCondition(page).getList();
        // 判断模板是否存在
        try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
            if(is==null) {
                throw new PlatformException("模板资源不存在："+excelTemplate);
            }
            FileItem item = fileService.createFileTemp("文件名_"+DateUtil.now("yyyyMMddHH")+".xls");
            OutputStream os = item.openOutpuStream();
            Context context = new Context();
            // 这里的key值即为模板excel的each中的items的值，要保持一致
            context.putVar("jrAgentInventorys", jrAgentInventorys);
            JxlsHelper.getInstance().processTemplate(is, os, context);
            os.close();
            // 将数据生成excel文件成功后返回文件的存放路径, 下载的话请参考网上别的文档
            return  JsonResult.success(item.getPath());
        } catch (IOException e) {
            throw new PlatformException(e.getMessage());
        }

    }

 

以下是我配置文件的存放路径： 其中 1 为JXLS的映射配置文件，2 为导出的excel模板，

上图是导出时的excel模板文件