一.文件导入导出的认识
操作办公软件(word,excel,ppt)
有两款操作框架:

jxl:韩国一小伙子弄的,只能操作excel,功能强大,但局限
poi:apache名下,功能强大,能操作多种办公软件,支持广泛
本文主要介绍poi的操作

(1)要想使用poi,需要导入支持的jar包

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

(2)创建一个新的excel文件,以99乘法表为列

//创建一个新的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();
}

(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();
    }
}

上面是基础的poi使用,接下来,使用POI的集成框架EasyPOI
二.EasyPOI的使用

要想使用EasyPOI,同样得先导包,但是有一点得注意,之前导入的jar包会和接下来导入的jar包产生冲突,所以得把之前导入jar包的坐标去掉或者注释掉

(1)导包

<!-- 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)准备domain:通过注解的方式配置

@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
}

(3)domain里面管理的其他类:以dept为列

@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
}

(4)EasyPOI创建excel文件

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

(5)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());
});

三.EasyPOI集成SpringMVC完成导入导出
(1)导出功能

需要配置扫描:在applicationContext-mvc.xml文件中配置
<context:component-scan base-package=“cn.afterturn.easypoi.view” />

(2)配置视图解析器优先级

applicationContext-mvc.xml文件中配置

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

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

(5)后台接收参数并导出

	//上传员工excel的功能
    @RequestMapping("/employeeXlsx")
    public String employeeXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception{
        //1.设置导入的参数
        ImportParams importParams = new ImportParams();
        //2.params.setTitleRows(1);
        importParams.setNeedVerfiy(true); //代表这里是需要验证的
        importParams.setHeadRows(1);
        //加入自定义验证
        importParams.setVerifyHandler(employeeExcelVerifyHandler);

        //使用工具类导入excel
        ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
                empFile.getInputStream(),
                Employee.class, importParams);
        //拿到正确的结果集  result.getFailList()这是错误的结果集
        List<Employee> list = result.getList();
        list.forEach(e ->{

            //从数据库中查询部门
            String deptName = e.getDepartment().getName();
            Department dept = departmentService.findByName(deptName);
            e.setDepartment(dept);
            //设置初始密码
            e.setPassword("123");
            employeeService.save(e);
        });

        Workbook failWorkbook = result.getFailWorkbook();
        //结合以前我们学习的下载功能把文件写出去
        //下面设置好直接使用即可导出
        //设置响应的文件类型 mime类型
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //attachment:不要用浏览器打开(下载)
        response.setHeader("Content-disposition", "attachment;filename=empFail.xlsx");
        response.setHeader("Pragma", "No-cache");//设置不要缓存
        OutputStream ouputStream = response.getOutputStream();
        failWorkbook.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
        return "import";
    }

导入功能
(1)页面访问

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

(2)导入页面

	我是导入界面!请选择你要导入的excel文件
   <!-- 上传必需是:post，enctype="multipart/form-data"-->
   <form action="/import/employeeXlsx" method="post" enctype="multipart/form-data">
       <input class="easyui-filebox" name="empFile" style="width:50%"
              data-options="prompt:'选择一个文件...',buttonText: '选择文件'" />
       <button class="easyui-linkbutton">导入</button>
   </form>

(3)导入验证

@Component
public class EmployeeExcelVerifyHandler implements IExcelVerifyHandler<Employee> {
    @Autowired
    private IEmployeeService employeeService;
    @Override
    public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
        //写逻辑验证用户名是否重复
        boolean success = employeeService.checkName(employee.getUsername());
        if(success){ //true代表可以用
            return new  ExcelVerifyHandlerResult(true);
        }
        return new ExcelVerifyHandlerResult(false,"用户重复,请修改后再添加");
    }
}

(4)导入实现

	//上传员工excel的功能
    @RequestMapping("/employeeXlsx")
    public String employeeXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception{
        //1.设置导入的参数
        ImportParams importParams = new ImportParams();
        //2.params.setTitleRows(1);
        importParams.setNeedVerfiy(true); //代表这里是需要验证的
        importParams.setHeadRows(1);
        //加入自定义验证
        importParams.setVerifyHandler(employeeExcelVerifyHandler);

        //使用工具类导入excel
        ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
                empFile.getInputStream(),
                Employee.class, importParams);
        //拿到正确的结果集  result.getFailList()这是错误的结果集
        List<Employee> list = result.getList();
        list.forEach(e ->{
            //从数据库中查询部门
            String deptName = e.getDepartment().getName();
            Department dept = departmentService.findByName(deptName);
            e.setDepartment(dept);
            //设置初始密码
            e.setPassword("123");
            employeeService.save(e);
        });

        Workbook failWorkbook = result.getFailWorkbook();
        //结合以前我们学习的下载功能把文件写出去
        //下面设置好直接使用即可导出
        //设置响应的文件类型 mime类型
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //attachment:不要用浏览器打开(下载)
        response.setHeader("Content-disposition", "attachment;filename=empFail.xlsx");
        response.setHeader("Pragma", "No-cache");//设置不要缓存
        OutputStream ouputStream = response.getOutputStream();
        failWorkbook.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
        return "import";
    }

(5)自定义导入验证功能

使用JSR303规范
导入jar包:

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

加自定义注解,这个注解类需要扫描
context:component-scan base-package=“xx.xx.xx.xx(扫描的全限定名)”
根据bean的名称去返回 p:order顺序在前面
bean id=“beanNameViewResolver” class=“org.springframework.web.servlet.view.BeanNameViewResolver”
p:order=“0”

@Component
public class EmployeeExcelVerifyHandler implements IExcelVerifyHandler<Employee> {
    @Autowired
    private IEmployeeService employeeService;
    @Override
    public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
        //写逻辑验证用户名是否重复
        boolean success = employeeService.checkName(employee.getUsername());
        if(success){ //true代表可以用
            return new  ExcelVerifyHandlerResult(true);
        }
        return new ExcelVerifyHandlerResult(false,"用户重复,请修改后再添加");
    }
}

代码实现

//上传员工excel的功能
    @RequestMapping("/employeeXlsx")
    public String employeeXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception{
        //1.设置导入的参数
        ImportParams importParams = new ImportParams();
        //2.params.setTitleRows(1);
        importParams.setNeedVerfiy(true); //代表这里是需要验证的
        importParams.setHeadRows(1);
        //加入自定义验证
        importParams.setVerifyHandler(employeeExcelVerifyHandler);

        //使用工具类导入excel
        ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
                empFile.getInputStream(),
                Employee.class, importParams);
        //拿到正确的结果集  result.getFailList()这是错误的结果集
        List<Employee> list = result.getList();
        list.forEach(e ->{

            //从数据库中查询部门
            String deptName = e.getDepartment().getName();
            Department dept = departmentService.findByName(deptName);
            e.setDepartment(dept);
            //设置初始密码
            e.setPassword("123");
            employeeService.save(e);
        });

        Workbook failWorkbook = result.getFailWorkbook();
        //结合以前我们学习的下载功能把文件写出去
        //下面设置好直接使用即可导出
        //设置响应的文件类型 mime类型
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //attachment:不要用浏览器打开(下载)
        response.setHeader("Content-disposition", "attachment;filename=empFail.xlsx");
        response.setHeader("Pragma", "No-cache");//设置不要缓存
        OutputStream ouputStream = response.getOutputStream();
        failWorkbook.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
        return "import";
    }