POI批量导入导出 JXL

最新推荐文章于 2021-02-16 05:38:59 发布

fxfanglin

最新推荐文章于 2021-02-16 05:38:59 发布

阅读量185

点赞数

文章标签：导入导出

本文链接：https://blog.csdn.net/fxfanglin/article/details/80401859

版权

麻烦的简单的导入导出

导出

@RequestMapping("exportxz/{ids}")

   public String exportxz(@PathVariable("ids")String ids) throws IOException, RowsExceededException, WriteException{
       System.out.println("接到的几个id"+ids);
       List<User> list=userService.getuserlistbyid(ids);
       System.out.println(list+"查出来几条");
       WritableWorkbook bWorkbook = Workbook.createWorkbook(new File("D:/11111大实训/练习一些项目/导入导出/test2.xls"));
       WritableSheet sheet = bWorkbook.createSheet("sheet1", 0);
       for(int i =0;i<list.size();i++){
           User user=list.get(i);
           Label label=new Label(0,i,String.valueOf(user.getId()));
           Label label1=new Label(1, i,user.getUsername());
           Label label2=new Label(2, i,user.getNickname());
           Label label3=new Label(3, i,String.valueOf(user.getSex()));
           Label label4=new Label(4, i,user.getTelnum());
           Label label5=new Label(5, i,user.getEmail());
           Label label6=new Label(6, i,String.valueOf(user.getStatus()));
           Label label7=new Label(7, i,user.getCreatetime());
           Label label8=new Label(8, i,user.getPassword());
           Label label9=new Label(9, i,user.getImg());
           sheet.addCell(label);
           sheet.addCell(label1);
           sheet.addCell(label2);
           sheet.addCell(label3);
           sheet.addCell(label4);
           sheet.addCell(label5);
           sheet.addCell(label6);
           sheet.addCell(label7);
           sheet.addCell(label8);
           sheet.addCell(label9);

       }
       bWorkbook.write();
       bWorkbook.close();
       return "success";

}

导入

@RequestMapping("importxz")
   public String importxz() throws BiffException, IOException{
       ArrayList<User> list = new ArrayList<User>();
       Workbook workbook=null;
       workbook=Workbook.getWorkbook(new File("D:/11111大实训/练习一些项目/导入导出/test3.xls"));
       Sheet sheet = workbook.getSheet(0);
       for(int i=0;i<sheet.getRows();i++){
           User user=new User();
           Cell cell =sheet.getCell(0,i);
           user.setUsername(sheet.getCell(1,i).getContents());
           user.setNickname(sheet.getCell(2,i ).getContents());
           user.setSex(Integer.valueOf(sheet.getCell(3,i ).getContents()));
           user.setTelnum(sheet.getCell(4,i ).getContents());
           user.setEmail(sheet.getCell(5,i ).getContents());
           user.setStatus(Integer.valueOf(sheet.getCell(6,i ).getContents()));
           user.setCreatetime(sheet.getCell(7,i ).getContents());
           user.setPassword(sheet.getCell(8,i ).getContents());
           user.setImg(sheet.getCell(9,i ).getContents());
           list.add(user);
       }
       workbook.close();
       userService.insertpl(list);
       return "redirect:list";

}

导入导出demo

    @RequestMapping("exportData.do")
   public void export() throws IOException{
       List<Map<String,Object>> moneyList = moneyService.getMoneyListAll();
       //创建一个Excel文本对象
       HSSFWorkbook workbook = new HSSFWorkbook();

       HSSFSheet sheet = workbook.createSheet("基金信息");
       HSSFRow row0 = sheet.createRow(0);
       PoiUtils.buildCell(workbook,row0,0,"基金编号");
       PoiUtils.buildCell(workbook,row0,1,"基金名称");
       PoiUtils.buildCell(workbook,row0,2,"基金发行金额");
       PoiUtils.buildCell(workbook,row0,3,"基金发行日期");
       PoiUtils.buildCell(workbook,row0,4,"基金剩余金额");
       for (int i = 1; i <=moneyList.size(); i++) {
           HSSFRow row1 = sheet.createRow(i);
           PoiUtils.buildCell(workbook,row1,0,moneyList.get(i-1).get("mid")+"");
           PoiUtils.buildCell(workbook,row1,1,moneyList.get(i-1).get("mname")+"");
           PoiUtils.buildCell(workbook,row1,2,moneyList.get(i-1).get("msum")+"");
           PoiUtils.buildCell(workbook,row1,3,moneyList.get(i-1).get("mdate")+"");
           PoiUtils.buildCell(workbook,row1,4,moneyList.get(i-1).get("sheng")+"");
       }

       //输出流
       OutputStream outputStream = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\test.xls");
       workbook.write(outputStream);
       outputStream.close();

   }


   @RequestMapping("importData.do")
   public void importData(MultipartFile file) throws IOException{
       InputStream inputStream = file.getInputStream();
       //poi文件系统
       POIFSFileSystem fileSystem = new POIFSFileSystem(inputStream);
       //获取工作簿对象
       HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);

       HSSFSheet sheet = workbook.getSheetAt(0);
       List<Money> moList = new ArrayList<Money>();
       //是sheet有效的行数
       int lastRowNum = sheet.getLastRowNum();
       for (int i = 1; i <= lastRowNum; i++) {
           HSSFRow row = sheet.getRow(i);
           Money money = new Money();
           money.setMname(PoiUtils.getCellValue(row.getCell(0)));
           money.setMsum(Double.parseDouble(PoiUtils.getCellValue(row.getCell(1))));
           money.setMdate(PoiUtils.getCellValue(row.getCell(2)));
           moList.add(money);
       }
       int i = moneyService.addMoneyList(moList);
       inputStream.close();
   }