jxsl实现数据导入xlsx文档中

pom.xml

java

package com.example.demo;

import com.alibaba.fastjson.JSONObject;
import com.example.demo.pojo.People;
import org.jxls.reader.ReaderBuilder;
import org.jxls.reader.XLSReadStatus;
import org.jxls.reader.XLSReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

@RestController
public class TestImport {
    private final Logger log = LoggerFactory.getLogger(this.getClass());

//解析Excel日期格式
    public static String ExcelDoubleToDate(String strDate) {
        if(strDate.length() == 5){
            try {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                Date tDate = DoubleToDate(Double.parseDouble(strDate));
                return sdf.format(tDate);
            }catch (Exception e){
                e.printStackTrace();
                return strDate;
            }
        }
        return strDate;
    }

    //解析Excel日期格式
    public static Date DoubleToDate(Double dVal) {
        Date tDate = new Date();
        long localOffset = tDate.getTimezoneOffset() * 60000; //系统时区偏移 1900/1/1 到 1970/1/1 的 25569 天
        tDate.setTime((long) ((dVal - 25569) * 24 * 3600 * 1000 + localOffset));

        return tDate;
    }



    @PostMapping("import")
    public void one(@RequestParam("file") MultipartFile file) {
        String pathOnTemplateExcel = "templates/import/test.xml";
        List<People> peopleList = new ArrayList<>();

        try {
            // 解析excel，并重新赋值封装到list,调试会发现在这一步之后可发现list的值发生改变
            InputStream inputXML = new ClassPathResource(pathOnTemplateExcel).getInputStream();
            XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
            InputStream inputXLS = new BufferedInputStream(file.getInputStream());
            Map<String, Object> beans = new HashMap<>();
            beans.put("peopleList", peopleList);
            XLSReadStatus readStatus = mainReader.read(inputXLS, beans);
            if (readStatus.isStatusOK()) {
                log.info("jxls读取Excel成功！");
            }
        } catch (Exception e) {
            log.error("文件模版不符合要求，具体错误为:{}", e.getMessage());
            e.printStackTrace();
        }
            log.info("导入的数据为: {}", JSONObject.toJSONString(peopleList));



        List<String> errorMSGList = new ArrayList<>();
        //错误检查
        int i = 1;

        for (People people : peopleList) {
            i++;
            if (StringUtils.isEmpty(people.getSex())){
                errorMSGList.add("第"+i+"行的数据"+"name列"+"数据不能为空");
            }
           people.setSex(ExcelDoubleToDate(people.getSex()));
            System.out.println(ExcelDoubleToDate(people.getSex()));
            
            

        }
        System.out.println(JSONObject.toJSONString(errorMSGList));

        log.info("导入的数据Excel格式化日期: {}", JSONObject.toJSONString(peopleList));



        //一般导入都是要定位第几行什么字段导入错误
        //2是excel 起始行
       // int i = 2;
        //错误信息列表
        //public static final String MSG = "第%s行%s";//第X行，某字段格式错误
       // List<String> errorMSGList = new ArrayList<>();
//        for (People people : peopleList) {
//            if (StringUtils.isEmpty(stationCode)) {
//                errorMSGList.add(String.format(LeadingIn.MSG, lineNumber, "充电站编码为空"));
//            } else if (stationCodeMap.get(stationCode) == null) {
//                errorMSGList.add(String.format(LeadingIn.MSG, lineNumber, "充电站编码不存在"));
//
//
//            }
//        }
    }

}

xml模板

<?xml version="1.0" encoding="UTF-8"?>
<workbook>
    <!--  这里的startRow为标题起始行，endRow为结束行-->
    <worksheet name="Sheet1">
        <section startRow="0" endRow="0"/>

        <!--  1、这里的startRow为数据的起始行，endRow和startRow保持一致就行，如果你的excel有标题，那startRow就是你标题所在的行
              2、items对应的是实现类中的bean中对应的key
              3、var为配置文件中的对象类的别名，varType为映射的对象类
              4、mapping中的row对应的标题列，col对应具体的列,也就是0对应的就是excel中的A,1对应为B
      -->

        <loop startRow="1" endRow="1" items="peopleList" var="people" varType="com.example.demo.pojo.People">
            <section startRow="1" endRow="1">
                <mapping row="1" col="0" nullAllowed="true">people.name</mapping>
                <mapping row="1" col="1" nullAllowed="true" >people.sex</mapping>
                <mapping row="1" col="2" nullAllowed="true" >people.high</mapping>
            </section>
            <loopbreakcondition>
                <!--  这里的结束读取数据的条件配置-->
                <rowcheck offset="0">
                    <!--  下面的offset="0"表示当读取某列时，当第一列为空就结束读取,
                    不把这个注释掉,会导致出现问题,读到首列为空的时候,会结束读取,不会读取下面的其他行的数据
                    <cellcheck offset="0"></cellcheck>
                    -->
                </rowcheck>
            </loopbreakcondition>
        </loop>
    </worksheet>
</workbook>

代码

jxls导入demo案例-互联网文档类资源-CSDN下载

jxls导入表格案例.zip-互联网文档类资源-CSDN下载