用Java实现excle导入

excle导入

 public Map<String,Object> uploadSpaceUserExc(HttpServletRequest request) throws Exception {
        Map<String,Object> map = new HashMap<>();
        Integer state = 1000;
        try {
            MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
            //获取excle文件
            MultipartFile file = multipartRequest.getFile("Filedata");
            if (file.isEmpty()) {
//	            return error("上传文件不能为空");
            }
            
             /**
             *这里做优化，excle不用限制版本
             */
            Workbook workbook = null;
            String name=file.getOriginalFilename();//获取文件名称
            if(name.endsWith(".xlsx")){
                InputStream is = file.getInputStream();
                workbook = new XSSFWorkbook(is);//2007以上版本,扩展名是.xlsx
            }else if(name.endsWith(".xls")){
                InputStream is = file.getInputStream();
                workbook = new HSSFWorkbook(is);//2003以下版本,扩展名是.xls
            }
            Sheet sheet = workbook.getSheetAt(0);

            //获取最后一行行标
            int lastRow = sheet.getLastRowNum();
//	        if (lastRow < 1)
//	            return error("数据不能为空");
            List<String> datas = new ArrayList<String>();
            //从第一行遍历，如果不想遍历第一行就从第二行遍历，
            //改为for (int i = 1; i < lastRow + 1; i++) {
            for (int i = 0; i < lastRow + 1; i++) {
                Row row = sheet.getRow(i);
                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                Long userId = Long.parseLong(row.getCell(0).getStringCellValue());

                spaceUserExcMapper.insertIntoSpaceUserExc(userId);


            }
        }catch(Exception e){
            map.put("msg", "sql错误");
            state=2000;
        }
        map.put("state", state);
        return map;

    }

获取行和列

            Workbook workbook = null;
            String name=file.getOriginalFilename();//获取文件名称
            if(name.endsWith(".xlsx")){
                InputStream is = file.getInputStream();
                workbook = new XSSFWorkbook(is);//2007以上版本,扩展名是.xlsx
            }else if(name.endsWith(".xls")){
                InputStream is = file.getInputStream();
                workbook = new HSSFWorkbook(is);//2003以下版本,扩展名是.xls
            }

            //获得工作表
            Sheet sheet = workbook.getSheetAt(0);
            
            //获取最后一行行标(编号是从0开始)，比行数小1
            int lastRow = sheet.getLastRowNum(); 
            //如果遍历行的时候 
            for (int i = 0; i <= lastRow; i++)
            
            
            //获取列数，比最后一列列标大1
            int lastCell = sheet.getRow(k).getLastCellNum(); 
            //如果遍历列的时候  
            for (int i = 0; i < lastCell; i++)
        
            

这个在导入的时候遍历那块会用到，所以注意遍历时的变量长度

excle导出

    @RequestMapping(value = {"exportOutSpaceUserExc"})
    @ResponseBody
    public void exportOutSpaceUserExc(HttpServletResponse response, SpaceUserExc spaceUserExc) throws IOException {
    Map<String,Object> map = new HashMap<>();
        List<Map<String,Object>> list = spaceUserExcMapper.findByPage();
       


        System.out.println("list是"+list.toString());
        String fileName = DateUtils.format(DateUtils.yyyyMMddHHmmssSSS) + ".csv";
        response.setContentType("application/octet-stream;charset=utf-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        response.setCharacterEncoding(DEFAULT_CHARSET);
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, DEFAULT_CHARSET));

        String[] headerNames = {"用户VV号","用户昵称","活跃等级","歌手等级","财富等级","注册时间","认证类型","认证时间","是否实名认证","家族Id","家族名称","加入家族时间","家族负责人","自建歌房Id","自建歌房名称","家族歌房Id","家族歌房名称"};
        String[] columnNames = {"UserId","Nickname","level","levelSinger","levelWealth","registerTime","AuthType","AuthTime","liveAuthState","familyId","name","createtime","principal","userRoomId","userRoomName","familyRoomId","familyRoomName"};
        System.out.println("list是"+list);

        System.out.println("list是"+list.toString());
        logger.info(list.toString());
        System.out.println(columnNames.toString());
        ExcelUtils.writeEXCEL(list, headerNames, columnNames, response.getOutputStream());
        spaceUserExcMapper.delete();

    }

ExcelUtils.writeEXCEL(list, headerNames, columnNames, response.getOutputStream()); 这个底层代码

 public static void writeEXCEL(List<Map<String, Object>> datas, String[] headerNames, String[] columnNames, OutputStream os) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = sheet.createRow(0);

        int count;
        for(count = 0; count < headerNames.length; ++count) {
            HSSFCell cell = row.createCell(count);
            cell.setCellValue(headerNames[count]);
        }

        count = 0;
        if (datas != null && datas.size() > 0) {
            Iterator i = datas.iterator();

            while(i.hasNext()) {
                Map<String, Object> data = (Map)i.next();
                ++count;
                row = sheet.createRow(count);

                for(int i = 0; i < columnNames.length; ++i) {
                    HSSFCell cell = row.createCell(i);
                    String value = StringUtils.ifnull(data.get(columnNames[i]), "");
                    cell.setCellValue(value);
                }
            }
        }

        workbook.write(os);
        os.flush();
    }

导出数据如图：

导出数据逻辑这里如果要导出的顺序不能变，这里就要保证每一条数据都要查出来，如果查出来的为null，则会出现如下错误：

导出接口直接报错，为了防止以上bug,在查询的时候指定一个默认值，类似

select (case when su.UserId is null then '-1' else su.UserId end) as UserId from user

如果查出来为null，则赋值一个默认值’-1’,就ok了！

扫描关注下面二维码获得更多有用的资源！