文件导入导出08

1、导入导出的认识

操作办公软件(word,excel,ppt) 03,07
JXL(只支持excel,内存优化) POI(功能强大,支持广泛)
POI的基本使用

1.1 导包

<!-- 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>

1.2 创建 excel文件

//创建一个新的excel,并且写99乘法表:导出

@Test
public void testCreateExcel() throws Exception{
    //1.创建一个工作薄(在内存中)
    SXSSFWorkbook wb = new SXSSFWorkbook();
    //2.创建一张表格
    Sheet sheet = wb.createSheet("99乘法表");
    //3.在表格创建行
    for(int i=1;i<=9;i++){
        Row row = sheet.createRow(i-1);
        //4.在表格中创建列(格子)
        for(int j=1;j<=i;j++){
            Cell cell = row.createCell(j-1);
            //5.格子中写东西
            cell.setCellValue(i+"*"+j+"="+(i*j));
        }
    }
    FileOutputStream out = new FileOutputStream("99.xlsx");
    wb.write(out);
    out.close();
}

1.3 读取excel文件

//读取excel:导入

@Test
public void testReadExcel() throws Exception{
    //读取了emp-poi.xlsx ,创建了Workbook(内存)
    InputStream inp = new FileInputStream("emp-poi.xlsx");
    Workbook wb = WorkbookFactory.create(inp);
    //拿到对应的表
    Sheet sheet = wb.getSheetAt(0);
    //拿到这表的总行数
    int lastRowNum = sheet.getLastRowNum();
    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);
            System.out.print(cell.getStringCellValue()+" ");
        }
        System.out.println();
    }
}

2、EasyPOI的使用

文档:http://easypoi.mydoc.io/

2.1导包

注意:今天使用EasyPOI需要把之前的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>

2.2 准备domain

POIEmployee

@ExcelTarget("emp")
public class PoiEmployee {
    @Excel(name = "名称")
    private String name;
    @Excel(name="邮件",width = 25)
    private String email;
    @Excel(name="年龄")
    private Integer age;
    @Excel(name="性别",replace = {"男_true","女_false"})
    private Boolean sex;
    @Excel(name="出生日期",format = "yyyy-MM-dd")
    private Date bornDate = new Date();
    //type=2:代表这是一张图片
    @Excel(name = "头像",type = 2,height = 25)
    private String headImage;
    @ExcelEntity
    private PoiDepartment department;

    // 不要忘了加上getter,setter
}

POIDepartment

@ExcelTarget("dept")
public class PoiDepartment {
    @Excel(name="部门名称_emp,名称_dept")
    private String name;
    @Excel(name="部门地址_emp,地址_dept")
    private String address;
    @Excel(name="邮件_dept")
    private String email;
    // 不要忘了加上getter,setter
}

2.3 EasyPOI创建excel文件

/**
list的值自己准备一下
 * new ExportParams(title,sheetName):导出的属性设置
    title:表头名称 
    sheetName：sheet表的名称 
 * PoiEmployee .class：导出的实体类型
 * list:导出的数据（List<PoiEmployee>）
 */
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("部门名称","bbb"),
        PoiEmployee.class, list);
FileOutputStream fos = new FileOutputStream("poidept.xlsx");
workbook.write(fos);
fos.close();

2.4 EasyPOI读取excel文件

ImportParams params = new ImportParams();
    //params.setTitleRows(1);
    params.setHeadRows(1);

    List<PoiEmployee> list = ExcelImportUtil.importExcel(
            new File("poiemp.xlsx"),
            PoiEmployee.class, params);

    list.forEach(e -> {
        System.out.println(e+","+e.getDepartment());
    });

3、EasyPOI集成SpringMVC完成导入导出

3.1 导出功能

EasyPOI支持SpringMVC

3.1.1 扫描view

<context:component-scan base-package="cn.afterturn.easypoi.view" />

3.1.2 配置视图解析器

p:order=“0” 先找这个bean的解析，再找其它的 <bean class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="0" />

3.1.3 前台传入相应的查询数据

 <form id="searchForm" method="post" action="/employee/download">
    用户名: <input name="username" class="easyui-textbox" style="width:80px">
    邮件: <input name="email" class="easyui-textbox" style="width:80px">
    部门:
    <input name="departmentId"  class="easyui-combobox"
           data-options="panelHeight:'auto',valueField:'id',textField:'name',url:'/util/deptlist'" />
    <a href="javascript:;" data-method="search" class="easyui-linkbutton" iconCls="icon-search">查询</a>
    <!-- button不写type,它就是提交 -->
    <button class="easyui-linkbutton" iconCls="icon-redo">导出</button></form>

3.1.4 后台接收参数进行导出

注：头像路径必需是真实路径

@RequestMapping("/download")
public String download(ModelMap map, HttpServletRequest request,EmployeeQuery query){
    //拿到所有员工
    List<Employee> employeeList = employeeService.findByQuery(query);
    //拿到当前项目的路径
    String realPath = request.getServletContext().getRealPath("");
    //修改(拼接)员工头像的路径
    employeeList.forEach(e -> {
        e.setHeadImage(realPath+e.getHeadImage());
    });

    ExportParams params = new ExportParams("员工信息", "列表", ExcelType.XSSF);
    params.setFreezeCol(5);

    map.put(NormalExcelConstants.DATA_LIST, employeeList); // 数据集合
    map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体
    map.put(NormalExcelConstants.PARAMS, params);//参数
    map.put(NormalExcelConstants.FILE_NAME, "employee");//文件名称
    //easypoiExcelView
    return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称
}

3.2导入功能

3.2.1 访问页面

ImportController

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

import.jsp

<!-- 注意:上传需要加enctype -->
<form method="post" action="/import/empXlsx" enctype="multipart/form-data">
    <input class="easyui-filebox" name="empFile" data-options="prompt:'选择一个excel文件..'" style="width:80%">
    <button class="easyui-linkbutton">导入</button>
</form>

3.2.2 导入实现

上传解析器一定要有名字：multipartResolver 必需加一个默认密码 员工对应的部门必需有id(根据名称拿到对应的部门)

@RequestMapping("/empXlsx")
Public String empXlsx(MultipartFile empFile) throws Exception{
//        System.out.println(empFile);
//        System.out.println(empFile.getName());//empFile:上传控件名称
//        System.out.println(empFile.getOriginalFilename()); //emp.xlsx:上传的文件名称
//        System.out.println(empFile.getSize()); //文件大小
//        System.out.println(empFile.getContentType());//文件的memi类型
//        System.out.println(empFile.getInputStream());
    //准备一些导入的参数
    ImportParams params = new ImportParams();
    params.setTitleRows(1);
    params.setHeadRows(1);
    //把上传的excel文件中的数据变成Employee
    List<Employee> list = ExcelImportUtil.importExcel(
            empFile.getInputStream(),
            Employee.class, params);
    //把员工进行保存
    list.forEach(e -> {
        //设置一个默认密码
        e.setPassword("123456");
        //根据名称到数据库中拿到部门
        Department department = e.getDepartment();
        if(department!=null){
            Department dbDept =  departmentService.findByName(department.getName());
            e.setDepartment(dbDept);
        }
        employeeService.save(e);
    });
    return "import";
}

3.3导入验证

使用的是JSR 303 规范

3.3.1 导入验证包

<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-validator</artifactId>
  <version>5.2.4.Final</version></dependency>

3.3.2 加上验证的注解

@Excel(name = "用户名")
@NotNull(message = "用户名不能为空")private String username;private String password;
@Excel(name = "邮箱",width = 25)private String email;

@Excel(name = "年龄")
@Max(value=80,message = "年龄不能超过80岁")
@Min(value = 20,message = "年龄不能小于20岁")private Integer age;

@Excel(name="头像",type = 2,height = 25)private String headImage; //头像
//jpa在管理的懒加载对象
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="department_id")
@ExcelEntityprivate Department department;

3.3.3 加上自定义注解

不要忘了,这个类型是需要Spring扫描

@Component
public class EmployeeExcelVerifyHandler implements IExcelVerifyHandler<Employee> {
    @Autowired
    private IEmployeeService employeeService;
    @Override
    public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
        //如果用户存在，我们就返回一个false
        //  checkUsername:这个用户名是否可以使用
        if(!employeeService.checkUsername(employee.getUsername())){
            ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(false);
            result.setMsg("用户名已经存在！");
            return result;
        }
        return  new ExcelVerifyHandlerResult(true);
    }
}

3.3.4 导入功能修改

要验证，必需设置 params.setNeedVerfiy(true);
自定义验证:params.setVerifyHandler(employeeExcelVerifyHandler);
result.getList(); 拿到所有通过验证的数据 result.getFailList():拿到所有错误的数据 result.getFailWorkbook();拿到错误的文本

@Autowired
private EmployeeExcelVerifyHandler employeeExcelVerifyHandler;...
@RequestMapping("/empXlsx")
public String empXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception{
    //准备一些导入的参数
    ImportParams params = new ImportParams();
    params.setTitleRows(1);
    params.setHeadRows(1);
    params.setNeedVerfiy(true); //需要做验证
    //设置验证处理器
    params.setVerifyHandler(employeeExcelVerifyHandler);
    //把上传的excel文件中的数据变成Employee
    ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
            empFile.getInputStream(),
            Employee.class, params);
    //拿到相应的值
    List<Employee> list = result.getList();
    //把员工进行保存
    list.forEach(e -> {
        //设置一个默认密码
        e.setPassword("123456");
        //根据名称到数据库中拿到部门
        Department department = e.getDepartment();
        if(department!=null){
            Department dbDept =  departmentService.findByName(department.getName());
            e.setDepartment(dbDept);
        }
        employeeService.save(e);
    });
    //拿到错误的值//        List<Employee> errList = result.getFailList();//        errList.forEach(e -> System.out.println("错误的："+e));
    //有错误的情况进行导出
    if(result.isVerfiyFail()){
        //拿到错误的文件
        Workbook failWorkbook = result.getFailWorkbook();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
        response.setHeader("Content-disposition", "attachment;filename=error.xlsx");
        response.setHeader("Pragma", "No-cache");//设置不要缓存
        OutputStream ouputStream = response.getOutputStream();
        failWorkbook.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
    }
    return "import";
}