销售宝系统_Day08(导入导出)

最新推荐文章于 2022-10-18 16:41:13 发布

　鹏程

最新推荐文章于 2022-10-18 16:41:13 发布

阅读量349

点赞数

分类专栏：销售宝(第二个项目)

本文链接：https://blog.csdn.net/Wei_mo/article/details/95925077

版权

销售宝(第二个项目) 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

Java操作Excel
easypoi
EasyPOI基于SpringMVC导出数据
EasyPOI基于SpringMVC导入数据

Java操作Excel

jxl：只能对Excel进行操作，属于比较老的框架。
POI：是apache的项目，可对ms的word,Excel,PPT进行操作，包括office2003和2007。对两种版本的处理都比较好。具体操作请看官方的文档。

导包

<!-- 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乘法表)

    //用原生的poi创建一个99乘法表
    @Test
    public void  test() throws IOException {
        //1.创建一个Excel文件(内存中)
        SXSSFWorkbook wb = new SXSSFWorkbook();
        //2.创建一张表
        Sheet sheet = wb.createSheet("九九乘法表");
        //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(j+"*"+i+"="+(i*j));
            }
        }
        //输出的标准格式
        FileOutputStream outputStream = new FileOutputStream("99.xlsx");
        wb.write(outputStream);
        outputStream.close();
    }

最后会在项目的根目录下生成一个这样的Excel

九九乘法表
3. 读取Excel

    //读取xlsx表格中的数据
    @Test
    public void  test02() throws IOException, InvalidFormatException {
        // 1.获取到相应的Excel文件(封装成对象，放到内存中)
        Workbook wb = WorkbookFactory.create(new FileInputStream("emp-poi.xlsx"));
        //2.拿到第一个sheet表
        Sheet sheet = wb.getSheetAt(0);
        //3.拿到wb中的行(不要头部行)
        int rowNum = sheet.getLastRowNum();//总行数
        for (int i = 1; i <=rowNum ; i++) {
            Row row = sheet.getRow(i);
            short cellNum = row.getLastCellNum();//每行总列数
            for (int j = 0; j <cellNum ; j++) {
                Cell cell = row.getCell(j);
                //如果表中有数字请转成字符(数字前加')
                System.out.print(cell.getStringCellValue()+" ");
            }
            System.out.println();
        }
    }

EasyPOI的使用

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

导包
注意:使用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>

PoiEmployee(模拟数据库对应的一个实体类)

package com.xpc.poi.easyPoi;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;

import java.util.Date;

//模拟与数据库对应的domain实体类(员工)
@ExcelTarget("emp")
public class PoiEmployee {

    @Excel(name = "名字")
    private String username;
    
    @Excel(name = "密码")
    private String password;
    
    @Excel(name="年纪")
    private Integer age;
    
    //replace:值的替换，导出是男，导入是true
    @Excel(name="性别",replace = {"男_true","女_false"})
    private Boolean sex = true;
    
    //width:设置列宽(默认为10)
    @Excel(name = "邮箱",width = 20)
    private String email;

    //type:导出类型(2为图片)   savePath文件导出保存路径   height：列高
    @Excel(name = "头像",type = 2,savePath = "images/head",height = 23)
    private String headImage;
    @Excel(name = "入职时间",format = "yyyy-MM-dd HH-mm-ss")
    private Date date = new Date();

    @ExcelEntity
    private PoiDepartment department;
    
    get/set方法省略
}

PoiDepartment(模拟数据库对应的一个实体类)

package com.xpc.poi.easyPoi;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;

//模拟与数据库对应的domain实体类(部门)
@ExcelTarget("dept")
public class PoiDepartment {
    //emp这个实体导出的时候表中列叫部门名称   dept这个实体导出的时候叫名称
    @Excel(name = "部门名称_emp,名称_dept")
    private String name;

    //这个字段只有dept这个实体才能导出
    @Excel(name = "部门地址_dept")
    private String address;

    get/set方法省略
}

EasyPOI创建excel文件

	    /*
         * ExportParams:当前导出的excel的属性
         *      title:一级标题
         *      secondTitle:二级标题
         *      sheetName:表名称
         * PoiEmployee.class：导出的数据类型
         * list:导出的值
         */
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(),
                PoiEmployee .class, list);

        //输出的标准格式
        FileOutputStream outputStream = new FileOutputStream("PoiEmployee.xlsx");
        workbook.write(outputStream);
        outputStream.close();

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导出数据

扫描view
applicationContext-mvc.xml中配置

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

配置bean视图解析器
在applicationContext-mvc.xml的视图解析器后面配置

    <!--value="0"  加载优先级-->
    <bean class="org.springframework.web.servlet.view.BeanNameViewResolver" >
        <property name="order" value="0"/>
    </bean>

前台传入相应的查询数据
在employee.jsp的查询条的最后加个button，form标签中加个action属性

<%--查询条--%>
    <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"   panelHeight="auto"
                data-options="valueField:'id',textField:'name',url:'/util/depts'" />
        <a href="#" data-method="search" class="easyui-linkbutton" iconCls="icon-search">查询</a>
        <button  type="submit" class="easyui-linkbutton" iconCls="icon-search">导出</button>
    </form>

后台接收参数进行导出
EmployeeController类中加一个方法

    @RequestMapping("/download")
    //baseQuery：前台传过来的查询条件
    public String download(ModelMap map,EmployeeQuery baseQuery,HttpServletRequest request){
    	//根据条件查询，拿到想要的数据
        List<Employee> list = employeeService.findByQuery(baseQuery);
        //得到当前头像所在的绝对路径
        String realPath = request.getServletContext().getRealPath("");
        //把头像的路径改为觉得路径
        list.forEach(e->{
            e.setHeadImage(realPath+e.getHeadImage());
        });
        ExportParams params = new ExportParams("员工表", "mployee", ExcelType.XSSF);
        //params.setFreezeCol(2);冻结的2列
        map.put(NormalExcelConstants.DATA_LIST, list); // 数据集合
        map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体
        map.put(NormalExcelConstants.PARAMS, params);//参数
        map.put(NormalExcelConstants.FILE_NAME,"mployee");//文件名称
        return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称
    }

给Employee中的字段加上注解

    @Excel(name = "用户名")
    private String username;
    private String password;
    
    @Excel(name = "邮件",width = 20)
    private String email;
    
    @Excel(name = "年龄")
    private Integer age;

    //头像
    @Excel(name = "头像",type = 2,width = 10 , height = 20)
    private String headImage;

    //部门
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="department_id")
    @ExcelEntity
    private Department department;

Department类中的name字段加上注解

    @Excel(name = "部门名称")
    private String name;

EasyPOI基于SpringMVC导入数据

访问页面准备
在webapp/views目录下创建import.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>文件上传</title>
    <%@ include file="/WEB-INF/views/head.jsp"%>
</head>
<body>
<!-- 注意:上传需要加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>
</body>
</html>

在web.controller下面创建一个ImportController类

package com.xpc.web.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

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

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

完善Department的repository和service层
repository

    //根据名称拿到部门对象
    Department findByName(String name);

service.impl

    @Autowired
    private DepartmentRepository departmentRepository;

    @Override
    public Department findByName(String name) {
        return departmentRepository.findByName(name);
    }

导入实现(无验证)
在ImportController类中添加一个方法

 @RequestMapping("/empXlsx")
    public String empXlsx(MultipartFile empFile,HttpServletResponse response) throws Exception {
        //准备一些导入的参数
        ImportParams params = new ImportParams();
        //params.setTitleRows(1);
        //params.setHeadRows(1);
        
        //把上传的excel文件中的数据变成Employee
        /*List<Employee> list = ExcelImportUtil.importExcel(
                empFile.getInputStream(),
                Employee.class, params);*/
        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);
        });
        }
        return "import";
    }

导入验证
导包

<!--easypoi自定义验证包导入-->
<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-validator</artifactId>
  <version>5.2.4.Final</version>
</dependency>

加上验证的注解

...
    @Excel(name = "用户名")
    @NotNull(message = "用户名不能为空")
    private String username;

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

加上自定义注解(自定义名字不能重复)
在applicationContext.xml中加上这句，开启common包的扫描

    <!--扫描common包-->
    <context:component-scan base-package="com.xpc.common" />

然后在common包下面建个EmployeeExcelVerifyHandler类去实现IExcelVerifyHandler(这里泛型要确定)这个接口

package com.xpc.common;

import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import com.xpc.domain.Employee;
import com.xpc.service.IEmployeeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component
public class EmployeeExcelVerifyHandler implements IExcelVerifyHandler<Employee> {

    @Autowired
    private IEmployeeService employeeService;

    @Override
    public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
        ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(true);
        //拿到相应的用户名
        String username = employee.getUsername();
        if(!employeeService.checkUsername(username)){
            result.setSuccess(false);
            result.setMsg("用户名重复了");
        }
        return result;
    }
}

导入功能修改(导入失败的就会新建一个Excel文件，可以去查看然后修改)

要验证，必需设置： params.setNeedVerfiy(true);

自定义验证:params.setVerifyHandler(employeeExcelVerifyHandler);

result.getList()； 拿到所有通过验证的数据
result.getFailList()；拿到所有错误的数据
result.getFailWorkbook();拿到错误的文本

    @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
        /*List<Employee> list = ExcelImportUtil.importExcel(
                empFile.getInputStream(),
                Employee.class, params);*/
        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);
        });
        //有错误的情况进行导出
        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";
    }

上传失败会生成的Excel文件类容如下图所试
在这里插入图片描述

　鹏程

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
销售宝系统_Day08(导入导出)

Java操作ExceleasypoiEasyPOI基于SpringMVC导出数据EasyPOI基于SpringMVC导入数据Java操作Exceljxl：只能对Excel进行操作，属于比较老的框架。POI：是apache的项目，可对ms的word,Excel,PPT进行操作，包括office2003和2007。对两种版本的处理都比较好。具体操作请看官方的文档。导包<!...
复制链接

扫一扫