Excel导入功能

  /**
     * 导入excel内容
     * @return
     */
    @RequestMapping(value = "/doExcelDr",  method = {RequestMethod.POST},produces = "text/html; charset=utf-8")
    @ResponseBody
    public String doExcelDr(MultipartHttpServletRequest request, HttpServletResponse response)throws Exception {
        String qyid = request.getParameter("qyid");
//        Iterator<String> iterator = request.getFileNames();
        //获取文件的名字
        MultipartFile multipartFile = request.getFile("excelfile");
//        String filename =  multipartFile.getOriginalFilename();
//        long size =multipartFile.getSize();
        // 文件转换成输入流
        InputStream fis=multipartFile.getInputStream();
        //读取Excel 中的内容
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        //装换成list
        List<Map<String,String>> list= loadHSSF(wb);
        //关闭流
        if (fis!= null) {
            try {
                fis.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(list.size()>1000){
            return  "{\"flag\":\"error\",\"message\":\"每次导入的数据不要多于1000条\"}";
        }
        HashMap m=new HashMap();
        m.put("QYID",qyid);
        m.put("NAMELIST",list);
        //首先批量插入信息
        boolean f=service.insertXzxk(m);

        //查出信息。
        List<Map<String,String>> listALL = service.queryXzxkList(m, false);
        //对比两个list  excel中的数据在list存在表示成功，不存在表示失败，失败的放到error里面
        List<Map<String,String>> listerror=new ArrayList<Map<String, String>>();
        List<Map<String,String>> listSucsess=new ArrayList<Map<String, String>>();
        listerror.addAll(list);
        listSucsess.addAll(list);
        listerror.removeAll(listALL);
        listSucsess.removeAll(listerror);
        /**通过上面计算 得到listerror 为失败列表，listSucsess 为成功列表
         * 将listerror 放入session 供导出使用
         * **/
        request.getSession().setAttribute("listerror",listerror);


        if(listerror.size()>0){
            return  "{\"flag\":\"successbf\",\"message\":\"导入已完成，成功导入"+listSucsess.size()+"条，失败"+listerror.size()+"条\"}";
        }else {
            return  "{\"flag\":\"success\",\"message\":\"已全部导入成功！共"+listSucsess.size()+"条\"}";
        }

    }
    /**
     * 读取excel内容
     */
    public List<Map<String,String>> loadHSSF(XSSFWorkbook wb) throws Exception {
        XSSFSheet sheet = wb.getSheetAt(0);
        int lastrow = sheet.getLastRowNum();//最后一行的行号，行号从0开始
        //一条数据没有则返回空
        if(lastrow<2) {
            throw new Exception("Excel中没有数据。");
        }
        List<Map<String,String>> list = new ArrayList<Map<String, String>>();
        Map<String, String> map = null;
        XSSFRow row = null;
        XSSFCell cell = null;
        //循环读取Excel数据
        for(int r=1;r<=lastrow;r++) {
            map = new HashMap<String, String>();
            row = sheet.getRow(r);
            int cols = row.getPhysicalNumberOfCells();//获取总列数


            //验证数据
            if(row.getCell(0)!=null){
                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                if(row.getCell(0).getStringCellValue()!=null&&!"".equals(row.getCell(0).getStringCellValue())){
                    map.put("LIC_NO",row.getCell(0).getStringCellValue());
                }
            }
            if(row.getCell(1)!=null){
                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                if(row.getCell(1).getStringCellValue()!=null&&!"".equals(row.getCell(1).getStringCellValue())){
                    map.put("ENTNAME",row.getCell(1).getStringCellValue());
                }
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 导出excel举报问题导出
     * @return
     */
    @RequestMapping(value = "/gotoDcerror" , method = RequestMethod.POST,produces="text/html;charset=UTF-8")
    public void gotoDcJbTZ(HttpServletRequest request, HttpServletResponse response) throws IOException {
        List<Map> list=(List<Map>) request.getSession().getAttribute("listerror");
        String columnNames[]={"未导入的许可证编号","企业名称"};//列名
        String keys[] = {"LIC_NO","ENTNAME"};//map中的key
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            ExcelUtil.createWorkBook(list, keys, columnNames).write(os);
        } catch (Exception e) {
            e.printStackTrace();
        }
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        Date date=new Date();
        SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat df2=new SimpleDateFormat("hhmmss");
        String time=df.format(date);
        String time2=df2.format(date);
        response.reset();
        response.setContentType("application/msexcel");
        response.setHeader("Content-disposition", "attachment; filename="+new String("导入失败的列表".getBytes("GB2312"),"8859_1")+time+time2+".xls");

        ServletOutputStream out = response.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (final IOException e) {
            throw e;
        } finally {
            if (bis != null)
                bis.close();
            if (bos != null)
                bos.close();
        }
    }