数据库数据导出Excel文件&Excel文件数据导入

    导出：

    后台我用的是springmvc框架。

   @RequestMapping("/downUser")

   public String downuser(HttpServletRequest request,HttpServletResponse response) throws IOException{
       String fileName="用户数据Excel文件";

        //往数据库插入一条下载记录数据

       Down d=new Down();

        d.setFileName("用户数据Excel文件");
       try {
          d.setDownUser(((User) sessionProvider.getAttribute(request, CommonUtils.USER_SESSION)).getUserId());
    } catch (Exception e) {
        // TODO: handle exception
    }
       
        equipmentService.addDown(d);
       //填充projects数据
       List<QueryUser> projects=userService.findallUser();
       List<Map<String,Object>> list=ExcelUtil.createUserExcelRecord(projects);
       String columnNames[]={"用户名","密码","真实姓名","运营商名","性别","手机号码","邮箱","创建/导入时间","创建人名或导入人名"};//列名
       String keys[] ={"username","password","reaName","opeName","sex","mobile","email","createTime","createName"};//map中的key
       ByteArrayOutputStream os = new ByteArrayOutputStream();
       try {
           ExcelUtil.createWorkBook(list,keys,columnNames).write(os);
       } catch (IOException e) {
           e.printStackTrace();
       }
       byte[] content = os.toByteArray();
       InputStream is = new ByteArrayInputStream(content);
       // 设置response参数，可以打开下载页面
       response.reset();
       response.setContentType("application/vnd.ms-excel;charset=utf-8");
       response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "iso-8859-1"));
       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();
       }
       return null;
   }

  

下面是ExcelUtil工具类的代码：

    public class ExcelUtil {
    
    /**
     * 创建excel文档，
     * [@param](http://my.oschina.net/u/2303379) list 数据
     * @param keys list中map的key数组集合
     * @param columnNames excel的列名
     * */
    public static Workbook createWorkBook(List<Map<String, Object>> list,String []keys,String columnNames[]) {
        // 创建excel工作簿
        Workbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for(int i=0;i<keys.length;i++){
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }

        // 创建第一行
        Row row = sheet.createRow((short) 0);

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

//        Font f3=wb.createFont();
//        f3.setFontHeightInPoints((short) 10);
//        f3.setColor(IndexedColors.RED.getIndex());

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(CellStyle.BORDER_THIN);
        cs2.setBorderRight(CellStyle.BORDER_THIN);
        cs2.setBorderTop(CellStyle.BORDER_THIN);
        cs2.setBorderBottom(CellStyle.BORDER_THIN);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        //设置列名
        for(int i=0;i<columnNames.length;i++){
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        //设置每行每列的值
        for (short i = 1; i < list.size(); i++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow((short) i);
            // 在row行上创建一个方格
            for(short j=0;j<keys.length;j++){
                Cell cell = row1.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }

public static List<Map<String, Object>> createUserExcelRecord(List<QueryUser> projects) {
        List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("sheetName", "sheet1");
        listmap.add(map);
        QueryUser project=null;
        for (int j = 0; j < projects.size(); j++) {
            project=projects.get(j);
            Map<String, Object> mapValue = new HashMap<String, Object>();
            mapValue.put("username", project.getUsername());
            mapValue.put("password", project.getPassword());
            mapValue.put("reaName", project.getReaName());
            mapValue.put("opeName", project.getOpeName());
            mapValue.put("sex", project.getSex());
            mapValue.put("mobile", project.getMobile());
            mapValue.put("email", project.getEmail());
            mapValue.put("createTime",DateUtil.getDateTimeString(project.getCreateTime()));
            mapValue.put("createName",project.getCreateName());
            listmap.add(mapValue);
        }
        return listmap;
    }

}

导入：

       @RequestMapping(value = "/save")
    public void saveExcel(  HttpServletRequest request, ModelMap model,HttpServletResponse response) throws Exception{
        //String fileName = file.getOriginalFilename();
        //System.out.println(fileName);
        
        int count=0;
        // = file.getInputStream();
          MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;  
          InputStream inputStream=multipartRequest.getFile("file").getInputStream();
                  List<String> readList = new ArrayList<String>();
                  XSSFWorkbook wssf=new XSSFWorkbook(inputStream);
                  //循环每一页
                  for (XSSFSheet xssfSheet : wssf) {
                       if(xssfSheet==null){
                           continue;
                       }
                       for(int cellNum = 2; cellNum <= xssfSheet.getLastRowNum(); cellNum++){  
                        String result="";
                           XSSFRow  xssfrow=xssfSheet.getRow(cellNum);
                          int min=xssfrow.getFirstCellNum();
                          int  max= xssfrow.getLastCellNum();
                         
                        for(int cow=min;cow<max;cow++){
                        XSSFCell cell=xssfrow.getCell(cow)    ;
                        if(cell==null){
                            break;
                        }
                        result+=(cell.toString().trim()+",");
                        if(cell.toString().trim().length()<=0){
                            break;
                         }
                        
                        }
                        System.out.println(result);
                        readList.add(result);  
                       }
                }
                  String notSuccess="";
                   try{
                    Connection connection = JdbcUtil.getConnection();
                    PreparedStatement prepareStatement = connection.prepareStatement("insert into  tb_user (username,password,reaName,operatorId,sex,mobile,email,createUserId) values(?,?,?,?,?,?,?,?)");
                 int id=((User)(sessionProvider.getAttribute(request,CommonUtils.USER_SESSION))).getUserId();
                    for (String xssfSheet : readList) {
                        String[] split = xssfSheet.split(",");
                      BigDecimal bd = new BigDecimal(split[4]);
                    prepareStatement.setString(1,split[0]);
             if(split[6].contains(".")){
                 BigDecimal bd1 = new BigDecimal(split[6]);
                 prepareStatement.setString(2,bd1.toPlainString());        
             }else{
                   prepareStatement.setString(2,split[6]);
             }
                        
                     prepareStatement.setString(3,split[1]);
                     List<Operator> operators = MemoryData.operators;
                      long opId=0;
                      for (Operator operator : operators) {
                        if(operator.getOpeName().equals(split[2])){
                            opId=operator.getOpeId();
                        }
                    }
                      
                     prepareStatement.setInt(4,(int)opId);
                     prepareStatement.setString(5,split[3]);
                     prepareStatement.setString(6,bd.toPlainString());
                     prepareStatement.setString(7,split[5]);            
                     prepareStatement.setInt(8,id);
                 
                     prepareStatement.addBatch();   
                     try {
                           prepareStatement.execute();
                          System.out.println("chenggong fou ");
                     } catch (Exception e) {
                         count++;
                         notSuccess+=split[0]+";";
                         e.printStackTrace();
                     }
                    }  
                    
                 prepareStatement.close();
                 connection.close();
             } catch (Exception e) {
             
                 e.printStackTrace();
                 
             }
                   int all=readList.size();
                 int not=all-count;
                 System.out.println("xialai de ");
                response.getWriter().write(not+","+count+","+notSuccess);;
                  //response.getOutputStream().write(JSON.toJSONString(count).getBytes());    
            
    }