用Java实现excle导入

本文介绍了如何使用Java进行Excel导入操作,重点讲解了在遍历行和列时的注意事项。同时,对于导出Excel的过程,通过ExcelUtils.writeEXCEL方法进行了说明,并指出在保证数据顺序不变的情况下,查询数据时应避免出现null值,建议设置默认值以防止接口报错。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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();
    }
导出数据如图:

[外链图片转存失败(img-K2wzwh2S-1563883371263)(52A86276B1A245BD9385295E900D75FF)]

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

[外链图片转存失败(img-RLc4MpMe-1563883371264)(F7356F6D7C0C467280AF8F4B8CD7C696)]

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

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

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

扫描关注下面二维码获得更多有用的资源!
在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值