easypoi实现步骤

easypoi实现步骤

1.建表

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bir` timestamp(0) NULL DEFAULT NULL,
  `no` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (2, '机器人0号', '2020-09-14 00:00:00', '522401199708139634', '贵州遵义');
INSERT INTO `t_user` VALUES (3, '机器人1号', '2020-09-15 00:00:00', '522401199708139635', '贵州毕节');
INSERT INTO `t_user` VALUES (4, '机器人2号', '2020-09-16 00:00:00', '522401199708139636', '北京');
INSERT INTO `t_user` VALUES (5, '机器人3号', '2020-09-17 00:00:00', '522401199708139637', '贵州贵阳');
INSERT INTO `t_user` VALUES (6, '机器人4号', '2020-09-18 00:00:00', '522401199708139638', '厦门');

SET FOREIGN_KEY_CHECKS = 1;

2.创建实体类

@Data
@ExcelTarget("users")
public class User implements Serializable {
    @Excel(name = "编号")
    private String id;
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "生日",format = "yyyy年MM月dd日",width = 30.0)
    private Date bir;
    @Excel(name = "身份证号",width = 30.0)
    private String no;
    @Excel(name = "籍贯")
    private String address;
}

3.创建DAO接口和Mapper

1.UserDao

@Mapper
public interface UserDao {
    List<User> findAll();
    void save(User user);
}

2.Mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.cjh.dao.UserDao">
    <select id="findAll" resultType="User">
        select
          id,name,bir,no,address
        from
          t_user
    </select>
    
    <insert id="save" parameterType="User" useGeneratedKeys="true" keyProperty="id">
        insert into t_user values (#{id},#{name},#{bir},#{no},#{address})
    </insert>
</mapper>

4.创建Service和实现类

public interface UserService {
    List<User> findAll();
    void saveAll(List<User> users);
}

@Service
@Transactional
public class UserServiceImpl implements UserService {

    @Autowired()
    private UserDao userDao;

    @Override
    @Transactional(propagation = Propagation.SUPPORTS)
    public List<User> findAll() {
        return userDao.findAll();
    }

    @Override
    public void saveAll(List<User> users) {
        for (User user : users) {
            user.setId(null);
            userDao.save(user);
        }
    }
}

5.创建Controller实现查询和导入导出

1.查询所有

    /**
     * 查询所有
     *
     * @param model
     * @return
     */
    @RequestMapping("/findAll")
    public String findAll(Model model) {
        List<User> users = userService.findAll();
        model.addAttribute("users", users);
        return "index";
    }

}

2.导入导出

1.Excel导入

    /**
     * excel导入
     *
     * @param excelFile
     * @return
     * @throws Exception
     */
    @RequestMapping("/import")
    public String importExcel(MultipartFile excelFile) throws Exception {
//        log.info("文件名："+excelFile.getOriginalFilename());
        //excel导入
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);

        List<User> users = ExcelImportUtil.importExcel(excelFile.getInputStream(), User.class, params);
//        for (User user : users) {
//            System.out.println(user);
//        }
        userService.saveAll(users);
        return "redirect:/user/findAll";
    }

2.Excel导出

    /**
     * 导出excel
     *
     * @param response
     * @throws IOException
     */
    @RequestMapping("export")
    public void ExportExcel(HttpServletResponse response) throws IOException {
        List<User> users = userService.findAll();
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户列表信息", "用户信息"), User.class, users);
        response.setHeader("content-disposition", "attachment;fileName=" + URLEncoder.encode("用户列表.xls", "UTF-8"));
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.close();
        workbook.close();
    }

6.修改index.html页面，实现所有功能

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <link rel="stylesheet" href="http://cdn.bootcss.com/bootstrap/3.3.0/css/bootstrap.min.css">
</head>
<body>
<div class="container-fluid">
    <div class="row">
        <div class="col-md-12">
            <h1>选择Excel文件导入到数据中</h1>
            <form th:action="@{/user/import}" method="post" enctype="multipart/form-data" class="form-inline">
                <div class="form-group">
                    <input class="form-control" type="file" name="excelFile">
                    <input type="submit" class="btn btn-danger" value="导入数据">
                </div>
            </form>
        </div>
        <div class="col-md-12">
            <h1>显示导入数据列表</h1>
            <table class="table table-bordered">
                <tr>
                    <th>编号</th>
                    <th>姓名</th>
                    <th>生日</th>
                    <th>身份证号码</th>
                    <th>籍贯</th>
                </tr>
                <tr th:each="user:${users}">
                    <td th:text="${user.id}">1</td>
                    <td th:text="${user.name}"> 小陈</td>
                    <td th:text="${#dates.format(user.bir,'yyyy-MM-dd')}">23</td>
                    <td th:text="${user.no}"></td>
                    <td th:text="${user.address}"></td>
                </tr>

            </table>
            <hr>
            <a th:href="@{/user/export}" class="btn btn-info" >导出excel</a>
        </div>
    </div>
</div>
</body>
</html>

7.实现页面

源码链接：
https://gitee.com/cao_jianhua/easy-poi_-spring-boot