wisdomsell-day8(智能商贸系统第八天)

于 2019-12-20 11:19:01 发布
阅读量146 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45624614/article/details/103629581
版权

wisdomsell-day8(智能商贸系统第八天)

用java来操作文本文件，这里选用的是POI的封装easyPOI

1.easyPOI导入导出案列

1.1准备员工

@ExcelTarget("emp")
public class POIEmployee {

    /**
     *  Excel:会导出的数据
     *      name:列名
     */
    @Excel(name = "编号")
    private Long id;
    @Excel(name = "用户名")
    private String username;
    @Excel(name = "邮箱",width = 20)
    private String email;
    /**
     * replace:替换
     */
    @Excel(name = "性别",replace = {"男_true", "女_false" })
    private Boolean sex = true;

    @Excel(name = "出生日期",format="yyyy-MM-dd",width = 18)
    private Date bornDate = new Date();

    @Excel(name = "头像", type = 2,width = 40 , height = 30)
    private String headImage;

    /**
     * 关连对象
     */
    @ExcelEntity
    private POIDepartment department;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Boolean getSex() {
        return sex;
    }

    public void setSex(Boolean sex) {
        this.sex = sex;
    }

    public Date getBornDate() {
        return bornDate;
    }

    public void setBornDate(Date bornDate) {
        this.bornDate = bornDate;
    }

    public String getHeadImage() {
        return headImage;
    }

    public void setHeadImage(String headImage) {
        this.headImage = headImage;
    }

    public POIDepartment getDepartment() {
        return department;
    }

    public void setDepartment(POIDepartment department) {
        this.department = department;
    }

    @Override
    public String toString() {
        return "POIEmployee{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", email='" + email + '\'' +
                ", sex=" + sex +
                ", bornDate=" + bornDate +
                ", headImage='" + headImage + '\'' +
                ", department=" + department +
                '}';
    }
}

1.2准备部门

@ExcelTarget("dept")
public class POIDepartment {

    private Long id;
    @Excel(name = "部门_emp,名称_dept")
    private String name;
    @Excel(name = "地址_dept")
    private String address;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

1.3测试头像、日期、性别

public class EasyPOITest {

    @Test
    public void testCreate() throws Exception{
        //准备部门
        POIDepartment department = new POIDepartment();
        department.setId(1L);
        department.setName("IT部");
        department.setAddress("小小街坊");
        //准备数据
        POIEmployee e1 = new POIEmployee();
        e1.setId(1L);
        e1.setUsername("二狗子");
        e1.setEmail("gougou@qq.com");
        e1.setHeadImage("images/1.jpg");
        e1.setDepartment(department);
        POIEmployee e2 = new POIEmployee();
        e2.setId(2L);
        e2.setUsername("二脚子");
        e2.setEmail("jiao@qq.com");
        e2.setSex(false);
        e2.setHeadImage("images/2.jpg");
        e2.setDepartment(department);

        //数据放到List中
        List<POIEmployee> list = new ArrayList<>();
        list.add(e1);
        list.add(e2);
         /**
         * ExcelExportUtil:excel导出的工具类
         *      new ExportParams():导出的参数配置
         *          title:表头   sheetName:表名  ExcelType:表类型
         *      POIEmployee.class:导出的对象类型
         *      list:导出的数据
         */
        Workbook wb = ExcelExportUtil.exportExcel(new ExportParams("123","456", ExcelType.XSSF), POIEmployee.class, list);


        //文件输出流把excel写出去
        FileOutputStream out = new FileOutputStream("emp.xlsx");
        wb.write(out);
        out.close();
    }

1.4测试有关联表的时候

 @Test
    public void testCreate02() throws Exception{
        //准备部门
        POIDepartment d1 = new POIDepartment();
        d1.setId(1L);
        d1.setName("IT部");
        d1.setAddress("天府神小长假");
        POIDepartment d2 = new POIDepartment();
        d2.setId(2L);
        d2.setName("销售部");
        d2.setAddress("蓬莱");


        //数据放到List中
        List<POIDepartment> list = new ArrayList<>();
        list.add(d1);
        list.add(d2);

        /**
         * ExcelExportUtil:excel导出的工具类
         *      new ExportParams():导出的参数配置
         *          title:表头   sheetName:表名  ExcelType:表类型
         *      POIEmployee.class:导出的对象类型
         *      list:导出的数据
         */
        Workbook wb = ExcelExportUtil.exportExcel(new ExportParams("123","456", ExcelType.XSSF), POIDepartment.class, list);

        //文件输出流把excel写出去
        FileOutputStream out = new FileOutputStream("dept.xlsx");
        wb.write(out);
        out.close();
    }

1.5导出测试

 @Test
    public void testImport() throws Exception{
        //导入的参数
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);

        List<POIEmployee> list = ExcelImportUtil.importExcel(
                new File("emp.xlsx"),
                POIEmployee.class, params);

        list.forEach(e-> System.out.println(e));
    }
}

2.应用到项目中

2.1准备

在domain里面的employee里面的字段上面打上注解@Excle，以及有关联的部门字段

然后在employee的controller里面加入

@RequestMapping("/export")
    public String export(EmployeeQuery query,ModelMap map, HttpServletRequest request){
         //获取相应的数据
        List<Employee> list = employeeService.findAll();
    }

别忘了在employee的jsp中引入导出按钮

 <%--高级查询搜索框--%>
    <form id="searchForm" method="post" action="/employee/export">
        用户名: <input name="username" class="easyui-textbox" style="width:80px">
        邮件: <input name="email" class="easyui-textbox" style="width:80px">
        部门: <input class="easyui-combobox" name="departmentId"
                   data-options="valueField:'id',textField:'name',panelHeight:'auto',url:'/department/list'" />
        <a href="#"  data-method="search"  class="easyui-linkbutton" iconCls="icon-search">查询</a>
        <button type="submit" class="easyui-linkbutton" iconCls="icon-search">导出</button>
    </form>

2.2导出

接着在上面代码下面写

 //设置基本参数
        ExportParams params = new ExportParams("员工数据", "员工表", ExcelType.XSSF);
        //冻结
//        params.setFreezeCol(2);
        map.put(NormalExcelConstants.DATA_LIST, list); // 数据集合
        map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体
        map.put(NormalExcelConstants.PARAMS, params);//参数
        map.put(NormalExcelConstants.FILE_NAME, "员工");//文件名称
        //easypoiExcelView
        return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称
    }

在 spring-mvc中配置扫描的view

  <!--扫描easypoi的view，配置了这个还需要让它优先-->
    <context:component-scan base-package="cn.afterturn.easypoi.view"></context:component-scan>
    <!-- bean的视图解析器  p:order="1":顺序在最前面 让它比上面的视图解析器优先-->
    <!--p:order="1"这个是伪顺序-->
    <bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="1" />

如何显示头像的图片

 //拿到当前项目的真实路径
        String realPath = request.getServletContext().getRealPath("");
        list.forEach(e->{
            //  /images/head/1.jpg
            //设置图片路径
            e.setHeadImage(realPath+e.getHeadImage());
        });

完整版的导出

 @RequestMapping("/export")
    public String export(EmployeeQuery query,ModelMap map, HttpServletRequest request){
        //获取相应的数据
        List<Employee> list = employeeService.queryAll(query);

        //拿到当前项目的真实路径
        String realPath = request.getServletContext().getRealPath("");
        list.forEach(e->{
            //  /images/head/1.jpg
            //设置图片路径
            e.setHeadImage(realPath+e.getHeadImage());
        });

        //设置基本参数
        ExportParams params = new ExportParams("员工数据", "员工表", ExcelType.XSSF);
        //冻结
//        params.setFreezeCol(2);
        map.put(NormalExcelConstants.DATA_LIST, list); // 数据集合
        map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体
        map.put(NormalExcelConstants.PARAMS, params);//参数
        map.put(NormalExcelConstants.FILE_NAME, "员工");//文件名称
        //easypoiExcelView
        return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称
    }

3.导入

3.1写导入的controller

因为专门有一个页面来做导入功能实现

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

    @Autowired
    private IEmployeeService employeeService;
    @Autowired
    private IDepartmentService departmentService;
    @Autowired
    private EmployeeVerifyHandler employeeVerifyHandler;

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

    @RequestMapping("/employeeXlsx")
    public String employeeXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception {
        //设置导入参数
        ImportParams params = new ImportParams();
        //设置需要验证为true
        params.setNeedVerfiy(true);
        //设计验证规则
        params.setVerifyHandler(employeeVerifyHandler);
        //设置标题和头的行数
        //params.setTitleRows(1);
        params.setHeadRows(1);
        //把上传的文件变成数据 =》 导入时拿到更多的信息
        ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
                empFile.getInputStream(),
                Employee.class, params);
        //正确的数据直接保存
        result.getList().forEach(e->{
            //设置一个默认密码
            e.setPassword("123456");
            //获取部门对象
            if(e.getDepartment()!=null) {
                Department department = departmentService.findByName(e.getDepartment().getName());
                e.setDepartment(department);
            }
            employeeService.save(e);
        });
        //有错误的数据返回工作薄给前台
//        result.getFailList().forEach(e->{
//            System.out.println(e);
//        });
        if(result.isVerfiyFail()){
            Workbook workbook = result.getFailWorkbook();
            //设置响应头
            response.setHeader("content-disposition", "attachment;filename=error.xlsx");
            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
        }

        return "import";
    }

3.2写导入的jsp页面

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

3.3引入验证

导包

 <!-- JSR 303 规范验证包 -->
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-validator</artifactId>
      <version>5.2.4.Final</version>
    </dependency>

在domain里面打注解

这些都是引入规范包后可以使用 的

 @NotNull
    private String username;
    
    @Max(60)
    @Min(value = 18,message = "年龄必需大于18")
    private Integer age;

3.4验证用户名的唯一性

写一个验证类

/**
 * 用户的名称唯一性验证
 */
@Component
public class EmployeeVerifyHandler implements IExcelVerifyHandler<Employee> {

    @Autowired
    private IEmployeeService employeeService;

    @Override
    public ExcelVerifyHandlerResult verifyHandler(Employee employee) {

        Employee emp = employeeService.findByUserName(employee.getUsername());
        //如果员工存在，代表重复了,就应该返回false
        if(emp!=null){
            return new ExcelVerifyHandlerResult(false,"用户名已经存在");
        }

        return new ExcelVerifyHandlerResult(true);
    }
}

将这个类让spring能够扫描到

<!--扫描common-->
    <context:component-scan base-package="cn.itsource.aisell.common" />

3.5完成部门的导入

在部门的dao加入下面代码，并将他注入到service层中

  //根据部门名称获取部门
    Department findByName(String deptName);

4.代码中出现的错误

HTTP Status 500 - Servlet.init() for servlet dispatcherServlet threw exception

type Exception report

message Servlet.init() for servlet dispatcherServlet threw exception

description The server encountered an internal error that prevented it from fulfilling this request.

exception

javax.servlet.ServletException: Servlet.init() for servlet dispatcherServlet threw exception
	org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)

原因:在打开tomcat的时候页面直接弹出这个，经检查发现是有两个contrler的跳转页面的路径一模一样导致

你这秃驴又和老衲抢师太
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值