spring boot 开发篇四、Excel文件上传、下载

一、Excel文件上传

1、添加apache.poi 依赖

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.10-FINAL</version>
        </dependency>

2、接收文件实现方法

Controller层

 @PostMapping("/importDevice")
    public MessageResult importDevice(@RequestParam("ticket")String ticket,@RequestParam("file") MultipartFile file) {
        Map<String,Object> _map=new HashMap<>();
        _map.put("ticket",ticket);
        return _IService.importDevice(file,_map);
    }

service层

 	@Transactional
    @Override
    public MessageResult importDevice(MultipartFile file,Map<String, Object> _map){
        try{
            MessageResult _MessageResult= _GlobalUser.getUserInfo(_map.get("ticket").toString());
            UserInfo _userInfo=(UserInfo)_MessageResult.getKeyValue();
            _map.put("tenementguid",_userInfo.getTenementGUID());
            if(!_MessageResult.getResult()) {
                return MessageResult.error(_MessageResult.getMessage());
            }
            InputStream inputStream = file.getInputStream();
            BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
            POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
            HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
            HSSFSheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            StringBuilder sql=new StringBuilder();
            sql.append(" delete from tb_b_ExcelTemp ; ");
            for (int i = 2; i <= lastRowNum; i++) {
                HSSFRow row =null;
                sql.append(" insert into tb_b_ExcelTemp values( ");
                for (int j=0; j<25;j++){
                    row = sheet.getRow(i);
                    sql.append("'"+row.getCell(j).getStringCellValue()+"',");
                }
                sql.append("'"+_userInfo.getTenementGUID()+"'");
                sql.append(" ); ");
            }
            _map.put("sql",sql.toString());
            int result=_Mapper.insertDeviceTemp(_map);
//            boolean insertTempResult=result>0;
            log.info(" insert结果："+result);
//            log.info(" 写入临时表结果："+insertTempResult);
//            if(!insertTempResult){
//                //回滚操作
//                //TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//                return  MessageResult.error("临时表写入失败");
//            }
            //写入设备表
            _map.remove("sql");
            boolean ImportDeviceResult=_Mapper.importDevice(_map)>0;
            if(!ImportDeviceResult){
                //回滚操作
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return  MessageResult.error("设备表写入失败");
            }
            log.info(" 导入设备结果："+ImportDeviceResult);
            return  MessageResult.Ok(ImportDeviceResult);
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return  MessageResult.error(e.getMessage());
        }
    }

一、Excel文件下载

        Map<String,Object> _map=new HashMap<>();
        _map.put("ticket",ticket);
        List<Map<String,Object>> list=_IService.exportDevice(_map);
        if(list==null||list.size()==0){
            return  MessageResult.error("导出数据为空");
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("信息表");
        String fileName = " exportDevice.xls";

        HSSFRow row = sheet.createRow(0);
        Map<String,Object> titleKey=list.get(0);
        int titleIndex=0;
        for(String key :titleKey.keySet()){
            HSSFCell cell = row.createCell(titleIndex);
            cell.setCellValue(new HSSFRichTextString(key));
            titleIndex++;
        }
        int rowNum = 1;
        int ColumnIndex=0;
        for (Map<String,Object> item:list){
            row = sheet.createRow(rowNum);
            ColumnIndex=0;
            for (String key :item.keySet()){
                row.createCell(ColumnIndex).setCellValue(item.get(key).toString());
                ColumnIndex++;
            }
            rowNum++;
        }
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());
        return  MessageResult.Ok(true);

…未完待续