POI操作excel文件（导入和导出）

POI操作

一、配置准备

导入apache下的poi配置

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>

二、excel导出操作

（先准备一下导出的集合 List users = (List)userService.listByIds(idsList);）

1. 创建一个工作簿对象（HSSFWorkbook workbook=new HSSFWorkbook();）
2. 创建一个Sheet表 HSSFSheet sheet =workbook.createSheet(“用户统计表”);
3. 设置一个控制行数的变量(int count;)用来操作行数
4. 创建一个大标题（需要合并单元格）

sheet.addMergedRegion(new CellRangeAddress(0,0,0,7));
HSSFRow bigTitleRow=sheet.createRow(count++);//大标题的一行（count++）为0，所以是第一行
HSSFCell bigTitleRowCell = bigTitleRow.createCell(0);//合并后的单元格
//这里可以设置大标题的样式，和给大标题加值
bigTitleRowCell.setCellValue("用户信息表");
bigTitleRowCell.setCellStyle(this.bigTitleStyle(workbook));

5. 创建第一行的数据（）

HSSFRow titleRow=sheet.createRow(count++);
      String [] titles={"编号","姓名","性别","手机","邮箱","地区","备注","状态"};
      for (int i = 0; i <titles.length ; i++) {
       HSSFCell cell=titleRow.createCell(i);
       cell.setCellValue(titles[i]);//单元格设置
}

6. 遍历用户集合，得到user对象，然后将user的属性值传到sheet对应的单元格cell中

for (int i = 0; i <users.size() ;i++) {
            User user = users.get(i);
            HSSFRow row=sheet.createRow(count++);
            HSSFCell unoCell = row.createCell(0);
            unoCell.setCellStyle(smallCellStyle(workbook));//单元格的样式
            unoCell.setCellValue(user.getUno());

            HSSFCell uNameCell = row.createCell(1);
            uNameCell.setCellStyle(smallCellStyle(workbook));
            uNameCell.setCellValue(user.getUname());

            HSSFCell uSexCell = row.createCell(2);
            uSexCell.setCellStyle(smallCellStyle(workbook));
            uSexCell.setCellValue(user.getUsex()==1?"男":"女");

            HSSFCell uPhoneCell = row.createCell(3);
            uPhoneCell.setCellStyle(smallCellStyle(workbook));
            uPhoneCell.setCellValue(user.getUphone());

            HSSFCell uEmailCell = row.createCell(4);
            uEmailCell.setCellStyle(smallCellStyle(workbook));
            uEmailCell.setCellValue(user.getUemail());

            HSSFCell uPlaceCell = row.createCell(5);
            uPlaceCell.setCellStyle(smallCellStyle(workbook));
            uPlaceCell.setCellValue(user.getUplace());

            HSSFCell uIntrogCell = row.createCell(6);
            uIntrogCell.setCellStyle(smallCellStyle(workbook));
            uIntrogCell.setCellValue(user.getUintrog());

            HSSFCell uStateCell = row.createCell(7);
            uStateCell.setCellStyle(smallCellStyle(workbook));
            uStateCell.setCellValue(user.getUstate()==1?"启用":"禁用");
        }

7. 设置编码格式和生成文件

 		String fileName="用户信息统计表.xls";
        ServletOutputStream out = response.getOutputStream();
        // 强制下载不打开
        response.setContentType("application/force-download");
        // 中文需要编码
        response.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "utf-8"));
        // 生成文件
        workbook.write(out);
        System.out.println("成功");
        out.close();

下面是一些简单的样式设计

// Excel表格大标题样式
    private CellStyle bigTitleStyle(Workbook wb) {
        //单元格样式
        CellStyle curStyle = wb.createCellStyle();
        //字体设置
        Font curFont = wb.createFont();
        curFont.setFontName("宋体");
        curFont.setFontHeightInPoints((short) 18);
        //字体加粗
        curFont.setBold(true);
        curStyle.setFont(curFont); // 绑定字体

        curStyle.setAlignment(HorizontalAlignment.CENTER); // 横向居中
        curStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 纵向居中
        return curStyle;
    }
// Excel表格单元格样式
    public CellStyle smallCellStyle(Workbook wb) {
        //单元格样式
        CellStyle curStyle = wb.createCellStyle();
        //字体
        Font curFont = wb.createFont();
        curFont.setFontName("宋体");
        curStyle.setFont(curFont); // 绑定字体
        curStyle.setAlignment(HorizontalAlignment.CENTER); // 横向居中
        curStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 纵向居中
        return curStyle;
    }

二、excel导入操作

注意点：要有模板，模板的值要和导入的对象属性要对应上

1、先创建一个模板提供下载（创建一个读取服务器的流，通过流读取服务器上已提供的模板文件）

@RequestMapping("/user_downloadExcelModel")
    public void downloadExcelModel(HttpServletRequest req, HttpServletResponse response) throws Exception {
        response.setContentType("text/html;charset=utf-8");
        String downloadfileName = "用户信息统计表模板.xls";
        //构建一个完成下载地址
        String downloadrealPath = req.getServletContext().getRealPath("/excel") + "\\" + downloadfileName;
        System.out.println(downloadrealPath);
        File file = new File(downloadrealPath);
        if (file.exists() == true) {
            FileInputStream fis = new FileInputStream(downloadrealPath);
            //把文件以附件的形式的打开
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(downloadfileName, "utf-8"));
            byte[] bytes1 = new byte[1024];
            int length1;
            ServletOutputStream outstrem = response.getOutputStream();
            while ((length1 = fis.read(bytes1)) != -1) {
                outstrem.write(bytes1, 0, length1);
            }
            fis.close();
            outstrem.close();
        } else {
            PrintWriter out = response.getWriter();
            out.print("<script>alert('下载的文件不存在!');location.href='/jsp_importExcel';</script>");
        }
    }

2、用户提交完成的表单后将里面的数据提取出来加到数据库

  @RequestMapping("/user_importExcel")
    public void importExcel(MultipartFile uploadExcel, HttpServletResponse response ) throws Exception {
        response.setContentType("text/html;charset=utf-8");
        ServletOutputStream out = response.getOutputStream();
        String filename = uploadExcel.getOriginalFilename();
        if(!filename.equals("")){
            InputStream in = uploadExcel.getInputStream();
            List<User> users=new ArrayList<User>();
            //通过文件流得到工作簿对象
            HSSFWorkbook workbook=new HSSFWorkbook(in);
            //因为只有一张表所以下标设置为0
            HSSFSheet sheet = workbook.getSheetAt(0);
            for (int i = 2; i <=sheet.getLastRowNum() ; i++) {//getLastRowNum()为最后表的最后一行
                HSSFRow row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                User user = new User();
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    HSSFCell cell = row.getCell(j);
                    String cellString = "";
                    if (cell == null) {
                        cellString = "";
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
                        cellString = String.valueOf(cell.getBooleanCellValue());
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        cellString = cell.getNumericCellValue() + "";
                    } else {
                        cellString = cell.getStringCellValue();
                    }
                     // String [] titles={"编号","姓名","性别","手机","邮箱","地区","备注","状态"};
                     //从表中给user属性赋值
                    if (j == 0) {
                        user.setUno(cellString);
                    } else if (j == 1) {
                          user.setUname(cellString);
                    } else if (j == 2) {
                        user.setUsex(cellString.equals("男") ? 1 : 0);
                    } else if (j == 3) {
                        user.setUphone(cellString);
                    } else if (j == 4) {
                        user.setUemail(cellString);
                    } else if (j == 5) {
                        user.setUplace(cellString);
                    } else if (j == 6) {
                        user.setUintrog(cellString);
                    } else if (j == 7) {
                        user.setUstate(cellString.equals("启用") ? 1 : 0);
                    }
                }
                //初始密码
                String miwen = Md5Util.encodeByMd5(salt.getSaltPrefix() + "111" + salt.getSaltSuffix());
                user.setUpass(miwen);
                users.add(user);
            }
                System.out.println("得到要导入对象集合:" + users);
                System.out.println(users.size());
                boolean b = userService.saveBatch(users);
                if(b){
                    out.print("<script>alert('Excel导入成功！');location.href='/jsp_importProduct';</script>");
                }else{
                    out.print("<script>alert('Excel导入失败！');location.href='/jsp_importProduct';</script>");
                }
        }else{
            out.print("<script>alert('请选择导入的文件！');location.href='/jsp_importProduct';</script>");
        }
    }