智能供销系统_7导入导出功能

最新推荐文章于 2022-04-11 14:44:51 发布

chude4301

最新推荐文章于 2022-04-11 14:44:51 发布

阅读量209

点赞数

原文链接：https://my.oschina.net/u/4108086/blog/3074435

版权

用到的技术有EasyPOI

# 一.导入导出的认识

> 操作办公软件(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.2 读取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();
    }

}

# 三.EasyPOI的使用
> 文档:http://easypoi.mydoc.io/

## 3.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>

```

## 3.2 准备domain

@Excel(name="对应Excel表中的头")

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

```

## 3.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();
```

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

```

# 四 EasyPOI集成SpringMVC完成导入导出

## 4.1 导出功能
> EasyPOI支持SpringMVC

### 4.1.1 扫描view

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

### 4.1.2 配置视图解析器
> p:order="0" 先找这个bean的解析，再找其它的
`

<bean class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="0" />

### 4.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>

```

### 4.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名称
}

```

## 4.2 导入功能

### 4.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>

```

### 4.2.1 导入实现

> 上传解析器一定要有名字：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";
}
```

## 4.3 导入验证
> 使用的是JSR 303 规范----缺少这个包会导致验证功能失败/报错

### 4.3.1 导入验证包
```

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

```

### 4.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")
@ExcelEntity  //缺少这个注解无法再表格中读取到这个属性
private Department department;

```

### 4.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);
    }
}

```

### 4.3.3 导入功能修改

> 要验证，必需设置 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";
}

```

转载于:https://my.oschina.net/u/4108086/blog/3074435