使用poi实现excel导入导出

1.maven部分：

 <!-- poi -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>4.1.0</version>
    </dependency>

    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.0</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>1.1.2-beat1</version>
    </dependency>

2.工具类：
ExcelRead 读取

public class ExcelRead {
    private final static String xls = "xls";
    private final static String xlsx = "xlsx";

    public static List<List<String>> readExcel(MultipartFile multipartFile){
        List<List<String>> listList=new ArrayList<>();
        //获得文件名
        String fileName = multipartFile.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = multipartFile.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            //endsWith()判断后缀
            if(fileName.endsWith(xls)){
                //2003 xls
               // workbook = new HSSFWorkbook(is);
                workbook = new XSSFWorkbook(is);
            }else if(fileName.endsWith(xlsx)){
                //2007 及2007以上 xlsx
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {

        }
        //获取workbook工作簿对象的第一个sheet工作表
        Sheet sheet=workbook.getSheetAt(0);
        //循环sheet表中所有行 sheet.getFirstRowNum()获取sheet表中第一行下标 sheet.getLastRowNum()获取最后一行下标
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            //存储每一行的数据
            List<String> list=new ArrayList<>();
            //获取当前行
            Row row=sheet.getRow(i);
            //循环当前行所有单元格  row.getFirstCellNum()获取第一格下标 row.getLastCellNum()获取当前行格数
            for (int j = row.getFirstCellNum(); j <row.getLastCellNum() ; j++) {
                //读取当前单元格
                Cell cell=row.getCell(j);
                //设置数据格式
                cell.setCellType(CellType.STRING);
                //获取单元格的值并添加到list集合
                list.add(cell.getStringCellValue());
            }
            //将当前行添加到外层list集合
            listList.add(list);
        }
        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return listList;
    }
}

ToExcel 导出

public class ToExcel<T> {

    public void excel(String filename, HttpServletResponse response, List<T> list, String[] headres){
        Workbook workbook=new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("one");
        Row row=sheet.createRow(0);
        for (int i = 0; i < headres.length; i++) {
            row.createCell(i).setCellValue(headres[i]);
        }
        for (int i = 0; i < list.size(); i++) {
            Row row1=sheet.createRow(i+1);
            T t = list.get(i);
            Field[] fields = t.getClass().getDeclaredFields();
            int y=0;
            for (Field field : fields) {
                if(field.getName().equals("id")){
                    continue;
                }
                String name="get"+field.getName().substring(0,1).toUpperCase()+field.getName().substring(1);
                try {
                    Method method = t.getClass().getMethod(name, new Class[]{});
                    Object invoke = method.invoke(t, new Class[]{});
                    if(invoke!=null){
                        if(invoke instanceof Date){
                            SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            row1.createCell(y).setCellValue(simpleDateFormat.format(invoke));
                        }else {
                            row1.createCell(y).setCellValue(invoke.toString());
                        }
                    }else{
                        row1.createCell(y).setCellValue("");
                    }
                } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                    } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                }
                y++;
            }
        }
        try {
            response.setHeader("content-disposition","attachment;filename="+ URLEncoder.encode(filename,"UTF-8")+".xls");
                workbook.write(response.getOutputStream());
                workbook.close();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }catch (IOException e) {
            e.printStackTrace();
        }

    }
}

3.controller层：
ToExcel：

 @RequestMapping("/ToExcel")
    @ResponseBody
    public String ToExcel(HttpServletResponse response,@RequestParam(value = "idList",required = false,defaultValue = "")Integer[] idList,
                          XXGLVO xxglvo){
       List<XXGL> all=null;
       if (idList.length==0){
           all=sampleInfoService.selectXXGL(xxglvo);
       }else {
           all=sampleInfoService.findAllByIdList(idList);
       }
        String[] headers={"样品编号","省","市","县","农产品加工类型","取样时间","录入时间","真菌污染率","主要毒素"};
        new ToExcel<XXGL>().excel("test",response,all,headers);
        return JSON.toJSONString("dd");
    }

upLoad：

    @RequestMapping("/upLoad")
    @ResponseBody
    public String upLoad(@RequestParam("file")MultipartFile multipartFile) throws ParseException {
        //调用导入工具类读取文件内容封装到双层list集合
        List<List<String>> lists = ExcelRead.readExcel(multipartFile);
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        int success=0;
        int success1=0;
        for (int i = 1; i < lists.size(); i++) {
            List<String> list = lists.get(i);
            SampleInfo sampleInfo = new SampleInfo();
            //将样品编号添加到sampleinfo对象
            sampleInfo.setSampleId(list.get(0));
            List<AddressProvince> addressProvinces = sampleInfoService.selectProvince();
            String province="";
            for (int j = 0; j <addressProvinces.size(); j++) {
                if (addressProvinces.get(j).getName().equals(list.get(1))){
                    province=addressProvinces.get(j).getCode();
                    sampleInfo.setProvince(province);
                }
            }
            List<AddressCity> addressCities = sampleInfoService.selectCity(province);
            String city="";
            for (int j = 0; j < addressCities.size(); j++) {
                if (addressCities.get(j).getName().equals(list.get(2))){
                    city=addressCities.get(j).getCode();
                    sampleInfo.setCity(city);
                }
            }
            List<AddressTown> addressTowns = sampleInfoService.selectCounty(city);
            for (int j = 0; j < addressTowns.size(); j++) {
                if (addressTowns.get(j).getName().equals(list.get(3))){
                    sampleInfo.setCounty(addressCities.get(j).getCode());
                }
            }

            CropCategory cropCategories = sampleInfoService.findCropCategory(list.get(4));
            sampleInfo.setCropCategoryId(cropCategories.getId());
            sampleInfo.setInputTime(simpleDateFormat.parse(list.get(6)));
            sampleInfo.setSamplingTime(simpleDateFormat.parse(list.get(5)));
            sampleInfo.setPollutionRate(Float.parseFloat(list.get(7)));
            int i1 = sampleInfoService.addSampleInfo(sampleInfo);
            if (i1>0){
                success++;
            }
            //分割毒素信息
            String[] split = list.get(8).split(",");
            SampleToxin[] sampleToxins=new SampleToxin[split.length];
            int s=0;
            //查询所有毒素信息
            List<SampleToxinInfo> allToxinInfo = sampleInfoService.selectToxinById();
            //循环当前毒素名称数组
            for (int c=0;c<split.length;c++){
                //循环所有毒素信息
                for (int b=0;b<allToxinInfo.size();b++){
                    SampleToxinInfo sampleToxinInfo = allToxinInfo.get(b);
                    //判断当前循环毒素信息名称是否与split数组中毒素名称相同
                    if(sampleToxinInfo.getToxinType().equals(split[c])){
                        SampleToxin sampleToxin=new SampleToxin();
                        sampleToxin.setSampleInfoId(sampleInfo.getId());
                        sampleToxin.setToxinCount(Float.parseFloat(list.get(7)));
                        sampleToxin.setToxinId(sampleToxinInfo.getId());
                        sampleToxins[s]=sampleToxin;
                        s++;
                        break;
                    }
                }
            }
            success1+= sampleInfoService.addSampleToxins(sampleToxins);
        }
        return JSON.toJSONString(String.format("主表数据添加成功%d条,从表数据添加成功%d条",success,success1));
        }

---

前端部分
1.html部分：

<div class="oper">
    <ul>
      <li class="btn-top"> <a href="editIM-add.html" class="dt">新增单条</a>
        <form class="form-b" action="rest/excel/updateExcel" method="post" enctype="multipart/form-data" id="excelForm">
          <input type="file" name="file" id="fileName" class="file-info-b  file-info-b-1" accept=".xls">
          <a class="xz-btn-b xz-btn-b-1">选择文件</a>
          <input type="text" name="" id="" value="" class="vall vall-1" readonly>
          <input class="submit-b" type="button" value="导入样品信息" onclick="excelRead()">
        </form>
        <a class="mb">下载模板</a> <br>
        <a class="pl">批量删除</a>
        <!-- 产毒菌株信息的导入 -->
        <form class="form-b" action="rest/excel/updateBacterialExcel" method="post" enctype="multipart/form-data" id="excelForm1">
          <input type="file" name="file"  class="file-info-b junzhu-a" accept=".xls">
          <a class="xz-btn-b junzhu-b">选择文件</a>
          <input type="text" name="" id="flag" value="" class="vall vall-c" readonly>
          <input class="submit-b submit-c" type="button" value="导入菌株信息">
        </form>
        <a href="javascript:void(0)" class="dc" onclick="toexcel()">信息导出</a> </li>
        <a href="javascript:void(0)" class="dc" onclick="toexcelById()">勾选导出</a> </li>
        <a href="javascript:void(0)" class="dc" onclick="toexcelBySample()">条件导出</a> </li>
    </ul>
  </div>

function excelRead(){
    var formdata=new FormData();
    $.each($("#fileName")[0].files,function (index,item){
      formdata.append("file",item);
    })
    console.log(formdata);
    $.ajax({
      url:"/upLoad",
      data:formdata,
      type:"post",
      async:false,
      contentType: false,
      processData: false,
      success:function (data) {
        alert(data);
      }
    })
  }
  function toexcel() {
    location.href="/ToExcel";
  }
  function toexcelBySample() {
    var formdata=$("#form1").serialize();
    location.href="/ToExcel?"+formdata;
  }
  function toexcelById() {
    var IdList=new Array();
    $.each($(".testyangpin"),function (index,item) {
      if($(item).prop('checked')==true){
        IdList.push($(item).val());
      }
    })
    //alert(IdList)
    if(IdList.length==0){
      alert("请选择导出数据");
      return false;
    }
    location.href="/ToExcel?idList="+IdList;
  }

---

双重list集合遍历导出：
工具类：

public class ToExcelList<T> {

    public  void excelL(String filename, HttpServletResponse response,List<List<String>> list, String[] headres){
        Workbook workbook=new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("two");
        Row row=sheet.createRow(0);
        for (int i = 0; i < headres.length; i++) {
            row.createCell(i).setCellValue(headres[i]);
        }
        for (int i = 0; i < list.size(); i++) {
            Row row1=sheet.createRow(i+1);
            List<String> ts = list.get(i);
            for (int j = 0; j < ts.size(); j++) {
                row1.createCell(j).setCellValue(ts.get(j));
                }
            }
        try {
            response.setHeader("content-disposition","attachment;filename="+ URLEncoder.encode(filename,"UTF-8")+".xls");
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }catch (IOException e) {
            e.printStackTrace();
        }

        }
    }

controller层：

@RequestMapping("/ToExcel")
   @ResponseBody
    public String ToExcel(HttpServletResponse response,@RequestParam(value = "idList",required = false,defaultValue = "")Integer[] idList,
                          XXGLVO xxglvo){
       List<XXGL> all=null;
       if (idList.length==0){
           all=sampleInfoService.selectXXGL(xxglvo);
       }else {
           all=sampleInfoService.findAllByIdList(idList);
       }
       List<List<String>> list=new ArrayList<>();
       for (int i = 0; i < all.size(); i++) {
           XXGL xxgl = all.get(i);
           List<String> list1=new ArrayList<>();
           list1.add(xxgl.getSampleId());
           list1.add(xxgl.getProvince());
           list1.add(xxgl.getCity());
           list1.add(xxgl.getCounty());
           list1.add(xxgl.getCropCategory());
           list1.add(xxgl.getSamplingTime());
           list1.add(xxgl.getInputTime());
           list1.add(xxgl.getPollutionRate().toString());
           list1.add(xxgl.getToxinType());
           list.add(list1);
       }
       String[] headers={"样品编号","省","市","县","农产品加工类型","取样时间","录入时间","真菌污染率","主要毒素"};
       new ToExcelList<XXGL>().excelL("test",response,list,headers);
       return JSON.toJSONString("dd");
   }