08---Poi&EasyPoi&导入导出

Poi

Poi操作Excel

• Java操作办公软件的两个框架
  jxl：只能对Excel进行操作，属于比较老的框架。
  POI：是apache的项目，可对ms的word,Excel,PPT进行操作，包括office2003和2007。

Poi初体验

• 导包

<!-- poi支持的jar包 -->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.11</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.11</version>
</dependency>

• 创建一个乘法表–Excel
  1.创建一个工作簿
  2.创建表
  3.根据表创建行
  3.根据行创建列
  4.根据列填数据

//创建一个九九乘法表
    @Test
    public void test() throws IOException {//导出
        //创建一个工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        //创建一张表
        Sheet sheet = workbook.createSheet("99乘法表");
        //创建行
        for (int i = 1; i <=9 ; i++) {
            Row row = sheet.createRow(i - 1);
            //创建列
            for (int j = 1; j <=9 ; j++) {
                Cell cell = row.createCell(j - 1);
                //往格子中放值
                cell.setCellValue(i+"*"+j+"="+(i+j));
            }
        }
        //从内存中写出
        FileOutputStream fos = null;
        try {
           fos = new FileOutputStream("乘法表.xslx");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        workbook.write(fos);
        fos.close();
    }

• 读取Excel
  准备一个excel文件emp-poi.xlsx

 @Test
    public void test(){
        //将文件读出来放在内存
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(new FileInputStream("emp-poi.xlsx "));
        } catch (Exception e) {
            e.printStackTrace();
        }
        //读取选中的表  索引从0开始  或者名字sheet1
        Sheet sheet = workbook.getSheetAt(0);
        //获取总行数
        int lastRowNum = sheet.getLastRowNum();
        //循环拿行  i从1开始 因为不需要表头
        for (int i = 1; i <lastRowNum ; i++) {
            //获取行
            Row row = sheet.getRow(i);
            //获取总列数
            short lastCellNum = row.getLastCellNum();
            //循环拿列
            for (int j = 0; j <lastCellNum ; j++) {
                //获取列
                Cell cell = row.getCell(j);
                //获取列中格子中的数据
                if (cell.getCellType()==0){
                    double value = cell.getNumericCellValue();
                    System.out.println(value);
                }else {
                    String value = cell.getStringCellValue();
                    System.out.println(value);
                }
            }
            System.out.println();
        }
    }

EasyPoi

EasyPoi是对Poi的封装可以用来做更多Excel操作，更简单
官方文档:http://easypoi.mydoc.io/

• 导包
  需要把之前导入的Poi包删除,因为可能会产生冲突

 <!-- easypoi的支持 -->
<dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-base</artifactId>
  <version>3.2.0</version>
</dependency>
<dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-web</artifactId>
  <version>3.2.0</version>
</dependency>
<dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-annotation</artifactId>
  <version>3.2.0</version>
</dependency>

• 案例
  1.创建实体类POIEmployee

@ExcelTarget("emp")//可以根据不同情况单独设置关联对象的字段名字
public class POIEmployee {
    //@Excel 表示可以导出的字段
    @Excel(name = "编号")
    private Integer id;
    @Excel(name = "姓名")
    private String name;
    //width 设置宽度
    @Excel(name = "邮件",width = 20)
    private String email;
    //replace 设置显示 true会显示男
    @Excel(name = "性别",replace = {"男_true","女_false"},width = 20)
    private Boolean sex = true;
    //format 将日期格式化
    @Excel(name = "出生日期",format = "yyyy-MM-dd",width = 20)
    private Date date = new Date();
    //type = 2 表示是图片
    @Excel(name ="头像",type = 2)
    private String headImage;

    @ExcelEntity//关联对象
    private POIDepartment poiDepartment;
    .....
   }

2.创建实体类POIDepartment

@ExcelTarget("dept")
public class POIDepartment {
    private Integer id;
    //表示在员工表中显示部门  在部门表中显示名称
    @Excel(name = "部门_emp,名称_dept")
    private String name;
    @Excel(name = "地址")
    private String address;

• 读出数据

 @Test
    public void testExport() throws Exception{
        //部门数据
        POIDepartment d1 = new POIDepartment();
        d1.setId(1);
        d1.setName("采购部");

        //准备数据
        POIEmployee pe1 = new POIEmployee();
        pe1.setId(1);
        pe1.setName("小王");
        pe1.setEmail("wang@qq.com");
        pe1.setSex(false);
        pe1.setHeadImage("images/head/1.jpg");
        pe1.setPoiDepartment(d1);

        POIEmployee pe2 = new POIEmployee();
        pe2.setId(2);
        pe2.setName("老王");
        pe2.setEmail("laowang@qq.com");
        pe2.setSex(true);
        pe2.setHeadImage("images/head/1.jpg");
        pe2.setPoiDepartment(d1);
        //放进集合中
        List<POIEmployee> list =new ArrayList<>();
        list.add(pe1);
        list.add(pe2);

        /*
        * ExcelExportUtil导出的工具类
        *new ExportParams(); 导出的参数配置
        *   title 表头  sheetName 表名 ExcelType:表类型
        * */
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("人口统计","王室"),
                POIEmployee .class, list);

        //保存数据
        FileOutputStream fos = new FileOutputStream("emp.xls");
        workbook.write(fos);
        fos.close();
    }

• 结果
  
• 部门读取数据

@Test
    public void testExport01() throws Exception{
        //部门数据
        POIDepartment d1 = new POIDepartment();
        d1.setId(1);
        d1.setName("采购部");
        POIDepartment d2 = new POIDepartment();
        d2.setId(2);
        d2.setName("人事部");


        //放进集合中
        List<POIDepartment> list =new ArrayList<>();
        list.add(d1);
        list.add(d2);

        /*
        * ExcelExportUtil导出的工具类
        *new ExportParams(); 导出的参数配置
        *   title 表头  sheetName 表名 ExcelType:表类型
        * */
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("人口统计","王室"),
                POIDepartment .class, list);

        //保存数据
        FileOutputStream fos = new FileOutputStream("dept.xls");
        workbook.write(fos);
        fos.close();
    }

• 结果
  

导出

• Employee实体类中加注解

@Entity
@Table(name = "employee")
public class Employee extends BaseDomain {//员工
    //加上@Excel 表示需要导出的字段数据
    @Excel(name = "用户名")
    private String username;//用户名
    private String password;//密码
    @Excel(name = "邮件",width = 20)
    private String email;//邮件
    @Excel(name = "年龄")
    private Integer age;//年龄
    @Excel(name = "头像",type = 2)
    private String headImage;//头像
    
    @ManyToOne(fetch = FetchType.LAZY)
    //@JsonIgnoreProperties(value={"hibernateLazyInitializer","handler","fieldHandler"})
    @JoinColumn(name = "department_id")
    @ExcelEntity//关联对象
    private Department department;
    ....
  }

• 部门

@Entity
@Table(name = "department")
public class Department extends BaseDomain {//部门

    @Excel(name = "部门名称")
    private String name;

• EmployeeController层执行导出功能

@RequestMapping("/export")
    public String export(ModelMap map, EmployeeQuery query, HttpServletRequest req){
        //根据条件查询
        List<Employee> list = employeeService.queryAll(query);
        //获取真实路径  不然找不到路径 不会显示图片
        String realPath = req.getServletContext().getRealPath("");
        list.forEach(e->{
            //  /images/head/3.jpg 数据库根据绝对路径去找图片，但是我们这是相对路径
            //设置图片路径
            e.setHeadImage(realPath+e.getHeadImage());
        });

        //设置基本参数 title表头  sheetName表名
        ExportParams params = new ExportParams("员工数据", "员工表", ExcelType.XSSF);
        //冻结  表示前面的两列都不能移动
        //params.setFreezeCol(2);
        map.put(NormalExcelConstants.DATA_LIST, list); // 数据集合
        map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体
        map.put(NormalExcelConstants.PARAMS, params);//参数
        map.put(NormalExcelConstants.FILE_NAME, "员工");//文件名称
        //返回的是路径 easypoiExcelView
        return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称
    }

• employee.jsp页面添加导出按钮
  按下导出按钮 根据查询条件提交表单

 <form id="searchForm" method="post" action="/employee/export" >
        用户名: <input name="username" class="easyui-textbox" style="width:80px">
        邮件: <input name="email" class="easyui-textbox" style="width:80px">
        部门:<input class="easyui-combobox" name="departmentId"
                  data-options="valueField:'id',textField:'name',panelHeight:'auto',url:'/department/list'" />
        <a href="#"  data-method="search"  class="easyui-linkbutton" iconCls="icon-search">查询</a>
        <button type="submit" class="easyui-linkbutton" iconCls="icon-Undo">导出</button>
    </form>

• 配置applicationContext-mvc.xml
  写了上面代码发现会报错，是因为Springmvc中没有配置导出的视图解析器

 <!--bean的视图解析器  p:order:伪类属性 order:顺序 设置优先级 会先通过这个视图解析器 没有才会去找mvc的视图解析器-->
    <bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="1"/>
   <!-- 方式二-->
   <!-- <bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver">
            <property name="order" value="1"></property>
    </bean>-->
    <!--扫描easypoi的view-->
    <context:component-scan base-package="cn.afterturn.easypoi.view"/>

导入

• 需要配置applicationContext-mvc的文件上传解析器上传

 <!--文件上传解析器  id必须为multipartResolver-->
    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <!-- 设置上传文件的最大尺寸为1MB -->
        <property name="maxUploadSize">
            <value>1048576</value>
        </property>
    </bean>
	

• 准备一个导入页面
• importController层

@Controller
@RequestMapping("/import")
public class ImportController {//导入

    @Autowired
    private IEmployeeService employeeService;

    @Autowired
    private IDepartmentService departmentService;

    @RequestMapping("/index")
    public String index(){
        return "import/index";
    }

    @RequestMapping("/file")
    public String importFile(MultipartFile importFile) throws Exception {
		//importFile 和导入页面的控件name名字要对应
       /* System.out.println(importFile.getName());//导入控件的名称
        System.out.println(importFile.getContentType());//文件的类型
        System.out.println(importFile.getOriginalFilename());//导入文件名
        System.out.println(importFile.getSize());//导入文件大小*/
        //设置导入参数
        ImportParams params = new ImportParams();
        //是否需要导入表名和表头
        //params.setTitleRows(1);
        params.setHeadRows(1);
        List<Employee> list = ExcelImportUtil.importExcel(
                importFile.getInputStream(),
                Employee.class, params);
        list.forEach(e->{
            //导入时没有密码 会报空指针异常，所以设置初始密码
            e.setPassword("123456");
            //导入的部门是部门名称  而数据库保存的是部门id 所以需要根据名称获取部门对象
            if (e.getDepartment()!=null){//如果部门对象存在才进行保存
                //通过传过来的部门名称获取部门对象并设置
                Department department = departmentService.findByName(e.getDepartment().getName());
                e.setDepartment(department);
            }
            //保存数据到数据库
            employeeService.save(e);
        });
        return "import/index";
    }
}

导入数据验证功能

• 加入一些验证功能让不规范的数据保存失败并且生成一个失败数据错误文档
• 导入JSR303规范包

<!-- JSR 303 规范验证包 -->
<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-validator</artifactId>
  <version>5.2.4.Final</version>
</dependency>

• Employee实体类
  加入需要验证的字段注解

@Entity
@Table(name = "employee")
public class Employee extends BaseDomain {//员工
    //加上@Excel 表示需要导出的字段数据
    @Excel(name = "用户名")
    @NotNull(message = "用户名不能为null")//验证用户名
    private String username;//用户名
    private String password;//密码
    @Excel(name = "邮箱",width = 20)
    private String email;//邮件
    @Excel(name = "年龄")
    @Max(value = 65,message = "年龄最大不能超过65")//最大年龄不能超过65
    @Min(value = 18,message = "年龄最小不能小于18")//最小年龄不能低于18
    private Integer age;//年龄
    ....

• 自定义类实现 IExcelVerifyHandler 接口 自定义验证用户名重复规则
  1.将这个类交给Spring管理；
  @Component
  2.扫描这个类

 <!--扫描common中的 验证用户名类-->
    <context:component-scan base-package="cn.itsource.aisell.common"/>

3.实现接口定义规则

//实现IExcelVerifyHandler 接口 自定义验证用户名重复规则
@Component//将这个类交给Spring管理 因为注入了其他对象
public class CheckName implements IExcelVerifyHandler<Employee> {

    @Autowired
    private IEmployeeService employeeService;
    @Override
    public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
        //根据用户名获取一个员工对象
        Employee emp = employeeService.findByUsername(employee.getUsername());
        //如果对象存在表示用户名重复
        if(emp!=null){
            return new ExcelVerifyHandlerResult(false,"用户名重复");
        }
        //没有重复就返回true
        return new ExcelVerifyHandlerResult(true);
    }
}

4.importController层
注意—>加入自定义规则
params.setVerifyHandler(checkName);

@RequestMapping("/file")
    public String importFile(MultipartFile importFile, HttpServletResponse response) throws Exception {
        //importFile 和导入页面的控件name名字要对应
        //设置导入参数
        ImportParams params = new ImportParams();
        //是否需要导入表名和表头
        //params.setTitleRows(1);
        params.setHeadRows(1);
        params.setNeedVerfiy(true);//设置验证规则为true
        params.setVerifyHandler(checkName);//设置自定义验证
        //返回一个ExcelImportResult 对象 多了一些元素
        ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
                importFile.getInputStream(),
                Employee.class, params);
        //数据正确就保存
        result.getList().forEach(e-> {
            //导入时没有密码 会报空指针异常，所以设置初始密码
            e.setPassword("123456");
            //导入的部门是部门名称  而数据库保存的是部门id 所以需要根据名称获取部门对象
            if (e.getDepartment()!=null){//如果部门对象存在才进行保存
                //通过传过来的部门名称获取部门对象并设置给员工
                Department department = departmentService.findByName(e.getDepartment().getName());
                e.setDepartment(department);
            }
            //保存数据到数据库
            employeeService.save(e);
        });
        //数据错误就把错误的数据返回一个工作簿
        if (result.isVerfiyFail()){
            //错误数据工作簿
            Workbook workbook = result.getFailWorkbook();
            //设置响应头和错误文件名字
            response.setHeader("content-disposition", "attachment;filename=error.xlsx");
            //获取一个输出流
            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
        }
        return "import/index";
    }