Springboot如何通过jxls导入导出excel

转载自
一、必须的maven依赖(可在jxls的官网上去找最新版本)：

   <dependency>
  
    <groupId>org.jxls</groupId>
    <artifactId>jxls</artifactId>
    <version>2.4.6</version>
</dependency>
 
<dependency>

    <groupId>org.jxls</groupId>
    <artifactId>jxls-poi</artifactId>
    <version>1.0.15</version>
</dependency>   

二、设置Excel模板（新建一个Excel文档，按如下步骤设置），将新建的文档放到项目中（本案例是放到项目的resource目录下，其他目录不知道可以不）



三、代码编写：
@RequestMapping(value = “exportDeviceModelMsg”,method = RequestMethod.GET)
@ResponseBody
public void exportDeviceModelMsg(HttpServletRequest request, HttpServletResponse response) {

   try {
       List<MyTest> myTestList = myTestService.findAll(); //获取列表数据
       InputStream in = this.getClass().getClassLoader().getResourceAsStream("excel/test.xls");   //得到文档的路径
       //列表数据将存储到指定的excel文件路径，这个路径是在项目编译之后的target目录下
       FileOutputStream out = new FileOutputStream("target/classes/excel/aaaa.xls");
       //这里的context是jxls框架上的context内容
       org.jxls.common.Context context = new org.jxls.common.Context();
       //将列表参数放入context中
       context.putVar("myTestList", myTestList);
       //将List<Exam>列表数据按照模板文件中的格式生成到scoreOutput.xls文件中
       JxlsHelper.getInstance().processTemplate(in, out, context);

       //下面步骤为浏览器下载部分
       //指定数据生成后的文件输入流（将上述out的路径作为文件的输入流）
       FileInputStream fileInputStream = new FileInputStream("target/classes/excel/aaaa.xls");
       //导出excel文件，设置文件名
       String filename = URLEncoder.encode("test信息.xls", "UTF-8");
       //设置下载头
       response.setHeader("Content-Disposition", "attachment;filename=" + filename);
       ServletOutputStream outputStream = response.getOutputStream();
       //将文件写入浏览器
       byte[] bys = new byte[fileInputStream.available()];
       fileInputStream.read(bys);
       outputStream.write(bys);
       outputStream.flush();
       outputStream.close();
       fileInputStream.close();
       if (file.exists()) {
          file.delete();
        }
   } catch (Exception e) {
       e.printStackTrace();
   }

}

转载自添加链接描述
导入excel数据功能的话
第一步：先确定好Excel导入的格式以及各表格字段值的含义

第二步：定义好解析的XML–videoConfig.xml;

<?xml version="1.0" encoding="UTF-8"?> 
<workbook> 
   <worksheet name="Sheet1"> 
    <section startRow="0" endRow="0"/> 
    <loop startRow="1" endRow="1" items="videoInfoList" var="videoInfo" varType="com.iflytek.weike.job.bo.VideoInfo"> 
    <section startRow="1" endRow="1"> 
    <mapping row="1" col="0">

    videoInfo.index</mapping> 
        <mapping row="1" col="1">videoInfo.videoName</mapping> 
        <mapping row="1" col="2">videoInfo.resourceId</mapping> 
        <mapping row="1" col="3">videoInfo.upload</mapping> 
        <mapping row="1" col="4">videoInfo.content</mapping> 
        <mapping row="1" col="5">videoInfo.schoolName</mapping>
       </section> 
        <loopbreakcondition> 
         <rowcheck offset="0">
            <cellcheck offset="0"></cellcheck> 
          </rowcheck> 
        </loopbreakcondition> 
        </loop> 
        </worksheet> 
    </workbook>

第三步：生成一下解析的实体类PipSysAccount（这个需要根据excel文件的列去手工写一个）
第四步：Controller 接受一个excel文件，然后返回excel的数据

public List<PipSysAccount> setExcel(MultipartFile file) {
    List<PipSysAccount> videoInfoList = new ArrayList<PipSysAccount>();
    try {
        InputStream inputXML = new BufferedInputStream(getClass().getClassLoader().getResourceAsStream("videoConfig.xml"));
        XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
        InputStream inputXLS = new BufferedInputStream(file.getInputStream());
        PipSysAccount videoInfo = new PipSysAccount();
        Map<String, Object> beans = new HashMap<String, Object>();
        beans.put("videoInfo", videoInfo);
        beans.put("videoInfoList", videoInfoList);
        XLSReadStatus readStatus = mainReader.read(inputXLS, beans);
        if (readStatus.isStatusOK()) {
            System.err.println("jxls读取Excel成功！");
        }
    } catch (Exception e) {
        System.err.println("格式错误");
    }
    return videoInfoList;
}

xml读取excel有可能会涉及的一些问题

<?xml version="1.0" encoding="UTF-8"?>
<workbook>
    <worksheet name="基础基213">
        <section startRow="0" endRow="5"/>    标题等有6行
       
        <loop startRow="1" endRow="2" items="videoInfoList" var="videoInfo" varType="com.lmbx.edu.data.common.domain.PipSysAccount">  两行数据对应一个对象时将endRow改成2`在这里插入代码片`
            <section startRow="1" endRow="2">  
              <mapping row="1" col="0">videoInfo.id</mapping>
                <mapping row="1" col="1">videoInfo.loginName</mapping>
                <mapping row="1" col="2">videoInfo.status</mapping>
                <mapping row="1" col="3">videoInfo.createDatetime</mapping>
                <mapping row="1" col="5">videoInfo.loginName</mapping>
                 <mapping row="2" col="5">videoInfo.pwd</mapping>
            </section>
            <loopbreakcondition>
                <rowcheck offset="0">
                    <cellcheck offset="0"></cellcheck>
                </rowcheck>
            </loopbreakcondition>
        </loop>
    </worksheet>