智销系统day08-Excel导入导出

一.POI:操作Excel

1.1 操作excel的认识

• Java操作excel有两个框架:POI,JXL
• 办公软件分两个版本: 03及以前，07及以后
  • 03版文档的后缀 如：doc,xls
  • 07版文档的后缀 如：docx，xlsx

1.2 导出Excel文件

创建工作簿(workbook) -> 创建表（sheet） -> 创建行(row) -> 创建格子(写数据)(cell)

//导出一个99乘法表
//创建工作簿(里面啥也没有)
SXSSFWorkbook wb = new SXSSFWorkbook();
//创建工作表
Sheet sheet = wb.createSheet();
//创建行
for (int i = 1;i<=9;i++){
    //创建行
    Row row = sheet.createRow(i-1);
    for (int j = 1;j<=i;j++){
        //创建列(单元格)
        Cell cell = row.createCell(j-1);
        //为单元格添加内容
        cell.setCellValue(j+"*"+i+"="+i*j);
    }
}
//导出Excel
FileOutputStream fos = new FileOutputStream("99.xlsx");
wb.write(fos);
fos.close();

1.3 导入Excel文件

读取文件(workbook) -> 获取表（sheet） -> 获取行(row) -> 获取格子(拿到数据)(cell)

//通过工厂获取工作簿
Workbook wb = WorkbookFactory.create(new FileInputStream("emp.xlsx"));
//获取第一个工作表
Sheet sheet = wb.getSheetAt(0);
//获取总行数
int lastRowNum = sheet.getLastRowNum();
//不要第1.2行，表头
for (int i = 2; i <= lastRowNum; i++) {
    //先获取行
    Row row = sheet.getRow(i);
    //获取总列数
    short lastCellNum = row.getLastCellNum();
    for (int j = 0; j < lastCellNum; j++) {
        //获取列(单元格)
        Cell cell = row.getCell(j);
        //获取单元格的值
        System.out.print(cell.getStringCellValue()+" ");
    }
    System.out.println();
}

二.EasyPOI:用它做更多Excel操作

百度搜索easypoi官方文档使用

2.1 导入easypoi的包

若之前有poi的包，需要将之前poi的包删掉

<!-- easypoi支持包 -->
<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>

2.2 EasyPOI的测试

2.2.1 实体类

• @Excel(name = “用户名”)导出的Excel的列名
• type=2表示图片类型
• @ExcelEntity关连的实体，关连的实体类设置列名
• replace = {“男_true”,“女_false”})男代替true，女代替false
• name = “部门_emp,名称_dept”,emp表现是部门，dept表显示名称

/*
 * 导出Excel的时候，为这个类取个名称
 * */
@ExcelTarget("emp")
public class PoiEmployee {
    /*
    * name表示导出Excel表格的列名
    * */
    @Excel(name = "编号")
    private Long id;
    @Excel(name = "用户名")
    private String name;
    @Excel(name = "邮箱",width = 20)
    private String email;
    @Excel(name = "性别",replace = {"男_true","女_false"})
    private boolean sex;
    @Excel(name = "出生日期",format = "yyyy-MM-dd",width = 20)
    private Date bornDate = new Date();
    @Excel(name = "头像",type = 2)//type = 2 表示图片
    private String headImg;
    /*
    * 关连对象用ExcelEntity
    * */
    @ExcelEntity
    private PoiDepartment department;
   
   	//getter，setter，toString...
}

/*
* 导出Excel的时候，为这个类取个名称
* */
@ExcelTarget("dept")
public class PoiDepartment {
    private Long id;
    @Excel(name = "部门_emp,名称_dept")
    private String name;
    @Excel(name = "地址_dept")
    private String address;
   
   	//getter，setter，toString...
}

2.2.2 测试代码

员工导出

@Test
public void testEasypoi() throws Exception{
    PoiDepartment poiDepartment = new PoiDepartment();
    poiDepartment.setId(1L);
    poiDepartment.setName("IT部");
    //准备数据
    PoiEmployee p1 = new PoiEmployee();
    p1.setId(1L);
    p1.setName("狗蛋");
    p1.setEmail("goudan@qq.com");
    p1.setSex(true);
    p1.setHeadImg("images/1.jpg");
    p1.setDepartment(poiDepartment);

    PoiEmployee p2 = new PoiEmployee();
    p2.setId(2L);
    p2.setName("古大师");
    p2.setEmail("gudashi@qq.com");
    p2.setSex(false);
    p2.setHeadImg("images/2.jpg");
    p2.setDepartment(poiDepartment);
    //准备list
    List<PoiEmployee> list = new ArrayList<>();
    //添加数据到list中
    list.add(p1);
    list.add(p2);
    //导出Excel文档
    /*ExcelExportUtil:Excel导出工具类
    * ExportParams:导出的参数、
    *
    * */
    Workbook wb = ExcelExportUtil.exportExcel(new ExportParams("员工数据", "员工表", ExcelType.XSSF),
            PoiEmployee.class, list);

    //文件输出流把excel写出去
    FileOutputStream fos = new FileOutputStream("emp.xlsx");
    wb.write(fos);
    fos.close();
}

部门导出

@Test
public void testEasypoi2() throws Exception{
    PoiDepartment p1 = new PoiDepartment();
    p1.setId(1L);
    p1.setName("IT部");
    p1.setAddress("天府新谷");

    PoiDepartment p2 = new PoiDepartment();
    p2.setId(2L);
    p2.setName("销售部");
    p2.setAddress("国防家苑");

    //准备list
    List<PoiDepartment> list = new ArrayList<>();
    //添加数据到list中
    list.add(p1);
    list.add(p2);

    //导出Excel文档
    /*ExcelExportUtil:Excel导出工具类
    * ExportParams:导出的参数、
    *
    * */
    Workbook wb = ExcelExportUtil.exportExcel(new ExportParams("部门数据", "部门表", ExcelType.XSSF),
            PoiDepartment.class, list);

    //文件输出流把excel写出去
    FileOutputStream fos = new FileOutputStream("dept.xlsx");
    wb.write(fos);
    fos.close();

}

Excel导入

@Test
public void testImport() throws Exception{
    ImportParams params = new ImportParams();
    //去掉标题和表头
    params.setTitleRows(1);
    params.setHeadRows(1);
    //将empxlsx的信息导入
    List<PoiEmployee> list = ExcelImportUtil.importExcel(
            new File("emp.xlsx"),
            PoiEmployee.class, params);
    list.forEach(e-> System.out.println(e));
}

三.easypoi集成SpringMVC

3.1easypoi导出

3.1.1添加导出按钮

员工首页添加导出按钮(按钮设为button，可以提交条件)

<%--查询表单--%>
<form id="searchForm" method="post" action="/employee/export">
    ...
    <button type="submit" class="easyui-linkbutton" iconCls="icon-redo">导出</button>
</form>

3.1.2实体类

Employee类

@Entity
@Table(name = "employee")
public class Employee extends BaseDomain{
    @Excel(name = "用户名")
    private String username;
    private String password;
    @Excel(name = "邮箱")
    private String email;
    @Excel(name = "年龄")
    private Integer age;
    @Excel(name = "头像",type = 2)
    private String headImage;
    //多个员工对应一个部门
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "department_id")
    @ExcelEntity
    private Department department;

	//getter,setter,toString...
}

3.1.3导出代码

EmployeeController

/*
 * 导出功能
 * */
@RequestMapping("/export")
public String export(EmployeeQuery query, ModelMap map, HttpServletRequest req){
    List<Employee> list = employeeService.queryAll(query);

    //获取项目真实路径
    String realPath = req.getServletContext().getRealPath("");
    list.forEach(e->{
        //设置图片路径
        e.setHeadImage(realPath+e.getHeadImage());
    });

    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名称
}

3.1.4xml配置

配置applicationContext-mvc.xml

• 导出数据时，需要配置一个试图解析器
• 为视图解析器设置优先级p:order=“1”
• 扫描cn.afterturn.easypoi.view

<!--
    easypoi 的视图解析器
    伪类属性 p:order="1" 顺序  设置优先级
    等同于 <property name="order" value="1"/>
-->
<bean class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="1"/>
<!-- 注解目录扫描的时候加上 -->
<context:component-scan base-package="cn.afterturn.easypoi.view"/>

3.2easypoi导出

3.2.1准备导入页面

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <%@include file="/WEB-INF/views/head.jsp" %>
</head>
<body>
<!-- 上传必需是:post，enctype="multipart/form-data"-->
<form action="/import/employeeXlsx" method="post" enctype="multipart/form-data">
    <input class="easyui-filebox" name="empFile" style="width:80%"
           data-options="prompt:'选择一个文件...',buttonText: '选择文件'" />
    <button class="easyui-linkbutton">导入</button>
</form>
</body>
</html>

3.2.2准备ImportController

• 设置初始密码,上传文件没有传密码，空指针异常
• 上传的表中只有部门名称,没有部门id，不能进行部门设置，
  通过部门名称查询部门对象，获取部门进行设置

@RequestMapping("/employeeXlsx")
    public String employeeXlsx(MultipartFile empFile) throws Exception {
        //System.out.println(empFile.getName());//empFile控件名称(没啥用)
        //System.out.println(empFile.getOriginalFilename());//文件名称
        //System.out.println(empFile.getContentType());//文件的mime类型 ：application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
        //System.out.println(empFile.getSize());//文件大小

        //设置导入参数
        ImportParams params = new ImportParams();
        //设置标题和行
        //params.setTitleRows(1);
        params.setHeadRows(1);
        //把上传的文件变成数据
        List<Employee> list = ExcelImportUtil.importExcel(
                empFile.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";
    }

3.2.3通过部门名查找部门

DepartmentRepository

public interface DepartmentRepository extends BaseRepository<Department,Long>{
    //根据部门名称查询部门对象
    Department findByName(String deptName);
}

IDepartmentService

public interface IDepartmentService extends IBaseService<Department,Long>{
    Department findByName(String deptName);
}

DepartmentServiceImpl

@Service
public class DepartmentServiceImpl extends BaseServiceImpl<Department,Long> implements IDepartmentService{
    @Autowired
    private DepartmentRepository departmentRepository;
    @Override
    public Department findByName(String deptName) {
        return departmentRepository.findByName(deptName);
    }
}

四.导入验证

需求：
1.正确的信息正常上传
2.错误的信息，生成错误信息提示文档

4.1导包

导入6.3.1.JSR 303 规范验证包

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

4.2实体类设置验证规则

• @NotNull//用户名不能为空
• @Max(value = 80,message = “年龄不能大于80”)//年龄最大80
• @Min(value = 18,message = “年龄不能小于18”)//年龄最小18

@Entity
@Table(name = "employee")
public class Employee extends BaseDomain{
    @Excel(name = "用户名")
    @NotNull//用户名不能为空
    private String username;
    private String password;
    @Excel(name = "邮箱")
    private String email;
    @Excel(name = "年龄")
    @Max(value = 80,message = "年龄不能大于80")//年龄最大80
    @Min(value = 18,message = "年龄不能小于18")//年龄最小18
    private Integer age;
    @Excel(name = "头像",type = 2)
    private String headImage;
    //多个员工对应一个部门
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "department_id")
    @ExcelEntity
    private Department department;

4.3自定义验证处理器

自定义验证处理器实现IExcelVerifyHandler< T >接口

/*
* 自定义验证处理器
* */
@Component
public class EmployeeVerifyHandler 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,"该用户已存在");
        }

        return new ExcelVerifyHandlerResult(true);
    }
}

4.3.1扫描common包

将验证处理器交给Spring管理

<!--扫描common包-->
<context:component-scan base-package="com.luo.aisell.common"/>

4.4导入与验证代码

• 设置需要验证为true
• 与之前相比，方法为importExcelMore，返回结果的类型为ExcelImportResult

ImportController

/*
* 导入加验证
* */
@RequestMapping("/employeeXlsx")
public String employeeXlsx(MultipartFile empFile,HttpServletResponse resp) throws Exception {
    //设置导入参数
    ImportParams params = new ImportParams();
    //设置需要验证为true
    params.setNeedVerfiy(true);
    //为验证处理器设置验证规则
    params.setVerifyHandler(excelVerifyHandler);
    //设置标题和行
    //params.setTitleRows(1);
    params.setHeadRows(1);
    //把上传的文件变成数据
    //与之前相比，把方法名改了，返回结果的类型不一样了
    ExcelImportResult<Employee> list = ExcelImportUtil.importExcelMore(
            empFile.getInputStream(),
            Employee.class, params);

    //验证通过的集合，直接保存到数据库
    list.getList().forEach(e-> {
        //设置初始密码,上传文件没有传密码，空指针异常
        e.setPassword("123456");
        /*
        * 上传的表中只有部门名称,没有部门id，不能进行部门设置，
        * 通过部门名称查询部门对象，获取到
        * */
        if(e.getDepartment() != null) {
            //获取部门对象
            Department department = departmentService.findByName(e.getDepartment().getName());
            //为员工设置部门
            e.setDepartment(department);
        }
        employeeService.save(e);
    });
    System.out.println("--------------------------------------------");
    //判断是否有错误的数据，有就导出一个错误Execl,并提示错误信息
    if(list.isVerfiyFail()){
        //获取错误数据的工作簿
        Workbook workbook = list.getFailWorkbook();
        //设置响应头
        resp.setHeader("content-disposition", "attachment;filename=error.xlsx");
        ServletOutputStream out = resp.getOutputStream();
        workbook.write(out);
        out.flush();
    }
    return "import";
}