导入导出

1.1. Java操作Excel
1.1.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>

创建Excel(完成99乘法表)

public class ExcelTest {

    //创建一个Excel文件
    //在文件里加一个99乘法法
    //把这文件保存在项目中
    @Test
    public void testCreateExcel() throws Exception{
        //1.创建一个Excel文件(内存中)
        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.1.3. 读取Excel
可以使用我们已经准备好的excel文件做测试

/**

• 读取我们使用相应的方案
• @throws Exception
  */
  @Test
  public void readExcel() throws Exception{
  File file = new File(“employee-3.xlsx”);
  FileInputStream fis = new FileInputStream(file);
  //1.读取一个Excel文件(内存中)
  Workbook wb = new XSSFWorkbook(fis);
  //2.拿到第个sheet表
  Sheet sheet = wb.getSheetAt(0);
  //3.拿到wb中的行(不要拿头部)
  int lastRowNum = sheet.getLastRowNum();
  for (int i = 1; i <= lastRowNum; i++) {
  Row row = sheet.getRow(i);
  //4.拿到每一列(格子)
  short lastCellNum = row.getLastCellNum();
  for (int j = 0; j < lastCellNum; j++) {
  Cell cell = row.getCell(j);
  System.out.print(cell.getStringCellValue()+" ");
  }
  System.out.println();
  }
  }
  1.2. SpringMVC导出功能

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
 response.setHeader("Content-disposition", "attachment;filename="+filename);
 response.setHeader("Pragma", "No-cache");//设置不要缓存
 OutputStream ouputStream = response.getOutputStream();
 wb.write(ouputStream);
 ouputStream.flush();
 ouputStream.close();

1.2.1.	直接导出Excel文件
employee.jsp添加导出按钮
<a href="/employee/download "  class="easyui-linkbutton" iconCls="icon-redo" plain="true">导出</a>
EmployeeController完成导出功能
@RequestMapping("/download")
public void download(HttpServletResponse response) throws Exception{
    //准备下载的文件名
    String filename = "employee.xlsx";
    response.setHeader("Content-disposition", filename);
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
    response.setHeader("Content-disposition", "attachment;filename="+filename);
    response.setHeader("Pragma", "No-cache");

    //1.创建一个Excel文件(内存中)
    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));
        }
    }
    //从内存中写出来
    OutputStream out = response.getOutputStream();
    wb.write(out);
    out.close();
}

```java
1.2.2.	导出真实数据
导出数据时提交表单

完成导出功能的工具方法
package cn.itsource.pss.common;
…
//Excel的公共类
public class ExcelUtils {
    /**
     * 导出一个Excel文件
     * @param filename  文件名称
     * @param heads 文件头
     * @param datas 文件内容
     * @param response 响应
     * @throws Exception
     */
    public static void exportExcel(String filename, String[] heads, List<String[]> datas, HttpServletResponse response) throws Exception{
        response.setHeader("Content-disposition", filename);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
        response.setHeader("Content-disposition", "attachment;filename="+filename);
        response.setHeader("Pragma", "No-cache");
        //1.创建一个Excel文件(内存中)
        SXSSFWorkbook wb = new SXSSFWorkbook();
        //2.创建一张表
        Sheet sheet = wb.createSheet();
        //3.创建表头
        Row headRow = sheet.createRow(0);
        for (int i = 0; i < heads.length; i++) {
            Cell cell = headRow.createCell(i);
            cell.setCellValue(heads[i]);
        }
        //4.创建表中数据
        for (int i = 1; i <= datas.size(); i++) {
            //4.1创建数据行
            Row row = sheet.createRow(i);
            //4.2创建数据列
            String[] rowData = datas.get(i-1);
            for (int j = 0; j < rowData.length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(rowData[j]); //添加数据(注意:必需是字符串)
            }
        }
        //从内存中写出来
        OutputStream out = response.getOutputStream();
        wb.write(out);
        out.close();
    }
}
EmployeeController完成导出方法
@RequestMapping("/download")
public void download(EmployeeQuery baseQuery,HttpServletResponse response) throws Exception{
    //准备相应的表头
    String[] heads = {"编号","用户名","密码","邮件","年龄","部门"};
    //拿到相应的员工数据
    List<Employee> employees = employeeService.findByQuery(baseQuery);
    //把List<Employee>格式转换成List<String[]>格式
    //1.准备相应的容器
    List<String[]> datas = new ArrayList<>();
    //2.遍历List<Employee>
    for (int i = 0; i < employees.size(); i++) {
        Employee employee = employees.get(i);//拿到某一个员工
        //每一行数据与头像长度一致
        String[] rowData = new String[heads.length];
        //设置数据
        rowData[0] = employee.getId().toString();
        rowData[1] = employee.getUsername();
        rowData[2] = employee.getPassword();
        rowData[3] = employee.getEmail();
        rowData[4] = employee.getAge()==null?"":employee.getAge().toString();
        rowData[5] = employee.getDepartment()==null?"":employee.getDepartment().getName();
        datas.add(rowData);
    }
String filename = new String("员工.xlsx".getBytes("UTF-8"),"ISO-8859-1");
    ExcelUtils.exportExcel(filename,heads,datas,response);
}

employee.jsp 加上导出按钮
<a href="#"  data-method="export" class="easyui-linkbutton" iconCls="icon-redo" plain="true">

employee.js加上导出方法
var itsource={
    …
    //导出数据
    export:function(){
        $('#searchForm').submit();
    }
}

1.3.	SpringMVC导入功能(了解认识)
1.3.1.	添加相应配置(已经完成)
applicationContext-mvc.xml
<!-- 上传配置 -->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
    <property name="maxUploadSize">
        <value>1048576</value>
    </property>
</bean>

1.3.2.	准备上传的页面
我们在系统中专门准备上传的页面
/WEB-INF/views/import.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <%@include file="/WEB-INF/views/head.jsp" %>
</head>
<body>

<span style="color: red">${count}</span>
<!-- 上传请配置enctype -->
<form action="/import/xlsx" method="post" enctype="multipart/form-data">
    <%--<input type="file" name="xlsxFile" /> --%>
    <input class="easyui-filebox" name="xlsxFile" data-options="prompt:'选择一个文件...'" style="width:80%">
        <button class="easyui-linkbutton" type="submit">确定</button>
</form>
</body>
</html>

1.3.3.	ImportController实现
package cn.itsource.pss.web.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
@Controller
@RequestMapping("/import")
public class ImportController extends BaseController {

    //跳转到导入页面
    @RequestMapping("/index")
    public String index(){
        return "import";
    }

    //跳转到导入页面
    @RequestMapping("/xlsx")
    public String importXlsx(MultipartFile xlsxFile) throws Exception{
        System.out.println(xlsxFile.getContentType()); //文件的类型(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
        System.out.println(xlsxFile.getName()); //文件的名称(xlsxFile)
        System.out.println(xlsxFile.getOriginalFilename());//文件的名称(employee-3.xlsx)
        System.out.println(xlsxFile.getSize()); //文件的大小
        return "import";
    }
}

1.3.4.	准备上传的工具方法
ExcelUtils:

/**
 * 导入功能:传一个文件的输入流，讲取完成后返回一个相应的数据集合
 * @throws Exception
 */
public static List<String[]>  importExcel(InputStream is) throws Exception{
    //一.准备装数据的容器
    List<String[]> list = new ArrayList<>();
    //1.读取一个Excel文件(内存中)
    Workbook wb = new XSSFWorkbook(is);
    //2.拿到第个sheet表
    Sheet sheet = wb.getSheetAt(0);
    //3.拿到wb中的行(不要拿头部)
    int lastRowNum = sheet.getLastRowNum();
    for (int i = 1; i <= lastRowNum; i++) {
        Row row = sheet.getRow(i);
        //4.拿到每一列(格子)
        short lastCellNum = row.getLastCellNum();
        //二.准备一个String[]装一行数据(有几列就有几条数据)
        String[] data = new String[lastCellNum];
        for (int j = 0; j < lastCellNum; j++) {
            Cell cell = row.getCell(j);
            data[j] = cell.getStringCellValue();
        }
        //三.把一行数据写到集合中去
       list.add(data);
    }
    return list;
}

1.3.5.	完成ImportController
package cn.itsource.pss.web.controller;
…
@Controller
@RequestMapping("/import")
public class ImportController extends BaseController {

    @Autowired
    private IDepartmentService departmentService;
    @Autowired
    private IEmployeeService employeeService;
    //跳转到导入页面
    @RequestMapping("/index")
    public String index(){
        return "import";
    }
    //跳转到导入页面
    @RequestMapping("/xlsx")
    public String importXlsx(MultipartFile xlsxFile, Model model) throws Exception{
        //拿到上传的数据
        List<String[]> list = ExcelUtils.importExcel(xlsxFile.getInputStream());
        int count = 0;
        for (int i = 0; i < list.size(); i++) {
            //拿到每一行数据
            String[] data = list.get(i);
            //准备Employee对象进行数据放置
            Employee employee = new Employee();
            employee.setUsername(data[0]+ UUID.randomUUID().toString().substring(0,5));//随机一个名称
            employee.setPassword(data[1]);
            employee.setEmail(data[2]);
            String age = data[3];
            if(StringUtils.isNotBlank(age)){
                employee.setAge(Integer.parseInt(age));
            }
            //拿到与设置部门
            String deptName = data[4];
            if(StringUtils.isNotBlank(deptName)){
                Department department = departmentService.findByName(deptName);
                employee.setDepartment(department); 
            }
            employeeService.save(employee);
            count ++;
        }
        //给出成功提示
        model.addAttribute("count","成功导入"+count+"条数据！");
        return "import";
    }
}

EasyPOI导出
1.3.6.	基本使用
引入咱们EASYPOI的JAR包
特别注意:把之前咱们的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.3.7.	Employee对象加注解
注意:一定要加上getter,setter

@Entity
@Table(name="employee")
public class Employee extends BaseDomain {
    @Excel(name="用户名")
    private String username;
    private String password;
    @Excel(name="邮件",width = 25)
    private String email;
    @Excel(name="年纪")
    private Integer age;

    @Excel(name = "头像",type = 2,savePath = "/images/head",height = 23)
    private String headImage; //头像

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="department_id")
    //Json Ignore(忽略) Properties(属性)
    //@JsonIgnoreProperties(value={"hibernateLazyInitializer","handler","fieldHandler"})
    @ExcelEntity
    private Department department;
...

部门有相应的关连
@Entity
@Table(name="department")
public class Department extends BaseDomain {

    @Excel(name = "部门名称")
    private String name;
...
1.3.8.	员工的页面 Employee.jsp
<form id="searchForm" 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" name="dept"
              panelHeight="auto"
              data-options="valueField:'id',textField:'name',url:'/util/dept'" />

    <a href="#" class="easyui-linkbutton" data-method="search" iconCls="icon-search">查询</a>
    <!--  button不加type属性就是提交 -->
    <button class="easyui-linkbutton" iconCls="icon-search">导出</button>
</form>

1.3.9.	控制层进行导出 EmployeeController
/**
 * 导出功能:下载
 * @param map
 * @return
 */
@RequestMapping("/download")
public String download(ModelMap map,EmployeeQuery query, HttpServletRequest request) {
    //拿到所有数据
    List<Employee> list = employeeService.findByQuery(query);
    //获取到真实路径
//解决了下载的图片的路径问题
    String realPath = request.getServletContext().getRealPath("");
    list.forEach(e -> {
        e.setHeadImage(realPath+e.getHeadImage());
        System.out.println(e.getHeadImage());
    });

    //设置一些属性
    ExportParams params = new ExportParams("员工管理", "明细", ExcelType.XSSF);
    //params.setFreezeCol(3);
    map.put(NormalExcelConstants.DATA_LIST, list); // 数据集合
    map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体
    map.put(NormalExcelConstants.PARAMS, params);//参数
    map.put(NormalExcelConstants.FILE_NAME, "employee");//文件名称
    //返回的名称 :easypoiExcelView -> 并没有找我的bean，而且当做一个路径去进行访问
    //  现在默认去找的视图解析器，而没有找我的那一个bean
    return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称
}

特别注意:要使用  return NormalExcelConstants.EASYPOI_EXCEL_VIEW
spring的.xml 需要加上：

<!-- 扫描easypoi的一些view:视图 -->
<context:component-scan base-package="cn.afterturn.easypoi.view" />
<!-- bean的视图解析器  p:order="0":顺序在最前面 -->
<bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver"
      p:order="0" />

1.4.	EasyPOI导入
1.4.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>

1.4.2.	准备controller
@RequestMapping("/employeeXlsx")
public String employeeXlsx(MultipartFile empFile) throws Exception {

    //准备导入的参数
    ImportParams params = new ImportParams();
    params.setTitleRows(1);

    List<Employee> list = ExcelImportUtil.importExcel(
            empFile.getInputStream(),
            Employee.class,
            params);

    list.forEach(e ->{
       // System.out.println(e+","+e.getDepartment());
        //根据部门名称拿到它的部门，再放到对应的员工中
        Department dept = departmentService.findByName(e.getDepartment().getName());
        e.setDepartment(dept);
        //给一个默认密码
        e.setPassword("123");
        employeeService.save(e);
    });

    return "import";
}

1.4.3.	准备相应的验证功能
JSR 303 规范的导包
<!-- JSR 303 规范验证包 -->
<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-validator</artifactId>
  <version>5.2.4.Final</version>
</dependency>

Employee加上相应的注解
@Excel(name="用户名")
@NotNull(message = "用户名不为空")
private String username;
private String password;
@Excel(name="邮件",width = 25)
private String email;
@Excel(name="年纪")
@Max(value = 80,message = "max 最大值不能超过80")
private Integer age;

完成验证的功能
@RequestMapping("/employeeXlsx")
   public String employeeXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception {
       //准备导入的参数
       ImportParams params = new ImportParams();
       params.setTitleRows(1);
       // need:需要  verfiy:核实,验证
       params.setNeedVerfiy(true); //需要验证

       //excel导入的一个结果
       ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
               empFile.getInputStream(),
               Employee.class, params);
       //引入正确的list（正常保存）
       result.getList().forEach(e ->{
           // System.out.println(e+","+e.getDepartment());
           //根据部门名称拿到它的部门，再放到对应的员工中
           Department dept = departmentService.findByName(e.getDepartment().getName());
           e.setDepartment(dept);
           //给一个默认密码
           e.setPassword("123");
           employeeService.save(e);
       });
//        //引入错误的list
//        result.getFailList().forEach(e ->{
//            System.out.println("错误的："+e);
//        });
       //如果有错误，就直接导出错误文件到前台
       // Verfiy:检验 Fail：失败
       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";
   }
1.4.4.	自定义验证
注意:一定要实现IExcelVerifyHandler接口
注意:扫描它，把它交给Spring管理

	<context:component-scan base-package="cn.itsource.aisell.common" />
1
准备验证规则
/**
 * 准备一个自定义验证
 */
@Component
public class EmployeeExcelVerifyHandler implements IExcelVerifyHandler<Employee> {

    @Autowired
    private IEmployeeService employeeService;

    @Override
    public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
        ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(true);
        //如果存在，返回false
        if(!employeeService.checkUsername(employee.getUsername())){
            //代表用户名重复
            result.setMsg("用户名重复");
            result.setSuccess(false);
        }
        return result;
    }
}
验证规则用起来
@Autowired
private EmployeeExcelVerifyHandler employeeExcelVerifyHandler;
 
@RequestMapping("/employeeXlsx")
    public String employeeXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception {
        //准备导入的参数
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        // need:需要  verfiy:核实,验证
        params.setNeedVerfiy(true); //需要验证
        //加入自定义验证
        params.setVerifyHandler(employeeExcelVerifyHandler);
       ....
        return "import";
    }