ExcelUtil

效果图:

1. 页面 

<input type="button" value="导出" class="s_button" onclick="doExportExcel()"/>&nbsp;

          //导出用户列表
          function doExportExcel(){
              window.open("${basePath}nsfw/user_exportExcel.action");
          }

2.跳到指定的action中

    //导出用户列表
    public void exportExcel(){//弹出下载框,无需跳转到jsp页面
        try {
            //1.查找用户列表 --数据
            userList = userService.findObjects();
            //2.导出
            HttpServletResponse response = ServletActionContext.getResponse();
            response.setContentType("application/x-excel");//以excel输出
            //设置以附件的形式下载 并兼容多个浏览器
            response.setHeader("Content-Disposition", "attachment;filename="+ new String("用户列表.xls".getBytes(), "ISO-8859-1"));
            ServletOutputStream outputStream = response.getOutputStream();
            userService.exportExcel(userList,outputStream);
            if(outputStream !=null){
                outputStream.close();
            }                
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

3.service层

    @Override
    public void exportExcel(List<User> userList,ServletOutputStream outputStream){
        ExcelUtil.exportExcel(userList, outputStream);
    }

4.导出列表

    public static void exportExcel(List<User> userList, ServletOutputStream outputStream) {
        try {
            //1、创建工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //1.1、创建合并单元格对象
            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4);//起始行号，结束行号，起始列号，结束列号
            
            //1.2、头标题样式
            HSSFCellStyle style1 = createCellStyle(workbook, (short)16);
            
            //1.3、列标题样式
            HSSFCellStyle style2 = createCellStyle(workbook, (short)13);
            
            //2、创建工作表
            HSSFSheet sheet = workbook.createSheet("用户列表");
            //2.1、加载合并单元格对象
            sheet.addMergedRegion(cellRangeAddress);
            //设置默认列宽
            sheet.setDefaultColumnWidth(20);
            
            //3、创建行
            //3.1、创建头标题行；并且设置头标题
            HSSFRow row1 = sheet.createRow(0);
            HSSFCell cell1 = row1.createCell(0);
            //加载单元格样式
            cell1.setCellStyle(style1);
            cell1.setCellValue("用户列表");
            
            //3.2、创建列标题行；并且设置列标题
            HSSFRow row2 = sheet.createRow(1);
            String[] titles = {"用户名","帐号", "所属部门", "性别", "电子邮箱"};
            for(int i = 0; i < titles.length; i++){
                HSSFCell cell2 = row2.createCell(i);
                //加载单元格样式
                cell2.setCellStyle(style2);
                cell2.setCellValue(titles[i]);
            }
            
            //4、操作单元格；将用户列表写入excel
            if(userList != null){
                for(int j = 0; j < userList.size(); j++){
                    HSSFRow row = sheet.createRow(j+2);
                    
                    HSSFCell cell11 = row.createCell(0);
                    cell11.setCellValue(userList.get(j).getName());
                    
                    HSSFCell cell12 = row.createCell(1);
                    cell12.setCellValue(userList.get(j).getAccount());
                    
                    HSSFCell cell13 = row.createCell(2);
                    cell13.setCellValue(userList.get(j).getDept());
                    
                    HSSFCell cell14 = row.createCell(3);
                    cell14.setCellValue(userList.get(j).isGender()?"男":"女");
                    
                    HSSFCell cell15 = row.createCell(4);
                    cell15.setCellValue(userList.get(j).getEmail());
                }
            }
            //5、输出
            workbook.write(outputStream);
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建单元格样式
     * @param workbook 工作簿
     * @param fontSize 字体大小
     * @return 单元格样式
     */
    private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontSize) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        //创建字体
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗字体
        font.setFontHeightInPoints(fontSize);
        //加载字体
        style.setFont(font);
        return style;
    }

二,导入用户列表

1.

          //导入用户列表 同上传头像
          function doImportExcel(){
              document.forms[0].action = "${basePath}nsfw/user_importExcel.action";
              document.forms[0].submit();
          }

2.

    //导入用户列表
    public String importExcel(){
        if(userExcel !=null){//如果获取到文件
            if(userExcelFileName.matches("^.+\.(?i)((xls)|(xlsx))$")){
                //第一个点代表任意字符 ,\. 代表点因为.和是特殊字符需要转意,?i不区分大小写
                userService.importExcel(userExcel,userExcelFileName);
            }
        }
        return "list";//重定向到listUI界面
    }

3.

    @Override//所谓的导入用户列表 其实就是把excel 每一行中的用户信息保存的数据库中user表中
    public void importExcel(File userExcel, String userExcelFileName) {
        try {
            FileInputStream fileInputStream = new FileInputStream(userExcel);
            boolean is03Excel = userExcelFileName.matches("^.+\.(?i)(xls)$");
            //1、读取工作簿
            Workbook workbook = is03Excel ? new HSSFWorkbook(fileInputStream):new XSSFWorkbook(fileInputStream);
            //2、读取工作表
            Sheet sheet = workbook.getSheetAt(0);
            //3、读取行
            if(sheet.getPhysicalNumberOfRows() > 2){
                User user = null;
                for(int k = 2; k < sheet.getPhysicalNumberOfRows(); k++){
                    //4、读取单元格
                    Row row = sheet.getRow(k);
                    user = new User();
                    //用户名
                    Cell cell0 = row.getCell(0);
                    user.setName(cell0.getStringCellValue());
                    //帐号
                    Cell cell1 = row.getCell(1);
                    user.setAccount(cell1.getStringCellValue());
                    //所属部门
                    Cell cell2 = row.getCell(2);
                    user.setDept(cell2.getStringCellValue());
                    //性别
                    Cell cell3 = row.getCell(3);
                    user.setGender(cell3.getStringCellValue().equals("男"));
                    //手机号  excel输入有可能是数字或字符串
                    String mobile = "";
                    Cell cell4 = row.getCell(4);
                    try {
                        mobile = cell4.getStringCellValue();
                    } catch (Exception e) {
                        double dMobile = cell4.getNumericCellValue();//拿到的是double 科学计数法
                        mobile = BigDecimal.valueOf(dMobile).toString();
                    }
                    user.setMobile(mobile);
                    
                    //电子邮箱
                    Cell cell5 = row.getCell(5);
                    user.setEmail(cell5.getStringCellValue());
                    //生日 
                    Cell cell6 = row.getCell(6);
                    if(cell6.getDateCellValue() != null){ //如果用户输入了生日才设置到user表中
                        user.setBirthday(cell6.getDateCellValue());
                    }
                    //默认用户密码为 123456
                    user.setPassword("123456");
                    //默认用户状态为 有效
                    user.setState(User.USER_STATE_VALID);
                    
                    //5、保存用户
                    save(user);
                }
            }
            workbook.close();
            fileInputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }