day-8导入与导出

最新推荐文章于 2021-04-13 17:24:02 发布

shuaiXu233

最新推荐文章于 2021-04-13 17:24:02 发布

阅读量286

点赞数

分类专栏： Jva学习

本文链接：https://blog.csdn.net/shuaiXu233/article/details/96014460

版权

Jva学习专栏收录该内容

17 篇文章 0 订阅

订阅专栏

1.1 导包

<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 创建 e

xcel文件//创建一个新的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文件//读取e

xcel:导入

@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

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

lTarget(“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）
*/

Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(“部门名称”,“bbb”),
PoiEmployee.class, list);
FileOutputStream fos = new FileOutputStream(“poidept.xlsx”);
workbook.write(fos);
fos.close();

3.4

Ea

syPOI读取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支持SpringMVC4.1.1 扫描view<context:component-scan base-package=“cn.afterturn.easypoi.view” />4.1.2 配置视图解析器p:order=“0” 先找这个bean的解析，再找其它的 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 访问页面I

mportController@RequestMapping("/import")
public class ImportController {

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

import

.jsp

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

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 30

3 规范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 {

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