easypoi的入门---excel表格导入数据库

easypoi的入门—excel表格导入数据库

easypoi是一款简化版的poi工具，让你不需要太多的poi知识就能够使用poi完成Excel和word的各种操作

1.相关jar包

2.数据库中表的准备

id	username	psssword	nickName
1	root	123456	新垣结衣

3.JSP页面的准备（基于Bootstrap框架）

由于是上传，应遵循文件上传三要素：

​ a.表单提交方式:post (get方式提交有大小限制,post没有)

​ b.表单的enctype属性:必须设置为multipart/form-data.

​ c.表单必须有文件上传项:file

<!-- 导入时的模态框 -->
<div class="modal fade bs-example-modal-lg" id="importModal">
    <div class="modal-dialog">
        <div class="modal-content message_align">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                    <span aria-hidden="true">×</span>
                </button>
            </div>
            <div class="modal-body">
                <form action="/system/employee/import" method="post" enctype="multipart/form-data"  class="form-horizontal" id="importForm">
                    <div class="form-group row">
                        <label for="empFile" class="control-label col-md-2">导入的文件</label>
                        <div class="col-md-10">
                            <input class="form-control" type="file" name="empFile">
                        </div>
                    </div>
                </form>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">取消</button>
                <a href='javascript:void(0);' id="importFileButton" class="btn btn-success">确定</a>
            </div>
        </div>
    </div>
</div>

4.相关javascript准备

//导入模态框的确定按钮绑定事件
$("#importFileButton").click(function(){
    //点击确定，直接向后台发送请求，提交表单
    $("#importForm").ajaxSubmit({
        success:function(msg){
            if(msg.success){
                //成功了，就关闭模态框
                $("#importModal").modal("hide");
                //刷新页面
                GridManager.refreshGrid('demo-ajaxPageCode');
            }else{
                alert(msg.error);
            }
        }
    });
});

5.Springmvc中配置文件上传解析器

<!-- 文件上传解析器 -->
<bean id="multipartResolver"
      class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
    <!-- 设置上传文件的最大尺寸为1MB -->
    <property name="maxUploadSize">
        <!-- spring el写法：1MB -->
        <value>#{1024*1024*50}</value>
    </property>
</bean>

6.Domain的准备

public class Employee {
	@Excel(name = "编号")
	private Long id;
	@Excel(name = "员工姓名")
	private String username;
	@Excel(name = "密码")
	private String password;
    @Excel(name = "昵称")
	private String nickName;
    ...
}

7.Controller层接收数据并返回结果

@Controller
@RequestMapping("/system")
public class EmployeeController {
    
    @Autowired
	private IEmployeeService employeeService;
    
	@RequestMapping("/employee/import")
	@ResponseBody
	public AjaxResult employeeXlsx(MultipartFile empFile) {
		try {
			// 准备导入的参数
			ImportParams params = new ImportParams();
			params.setTitleRows(1);//标题占的行数
			params.setTitleRows(1);//表头占的行数
			
			List<Employee> list = ExcelImportUtil.importExcel(empFile.getInputStream(), Employee.class, params);
			list.forEach(e -> {
				employeeService.save(e);
			});
			return new AjaxResult();
		} catch (Exception e) {
			e.printStackTrace();
			return new AjaxResult(false, "操作有误");
		}
	}
}

8.Service层的具体实现

@Service
public class EmployeeServiceImpl implements IEmployeeService {
    
    @Autowired
	private EmployeeMapper employeeMapper;
    
	@Override
	public void save(Employee e) {
		//先判断username是否存在
		Employee originalEmployee = employeeMapper.findByUsername(e.getUsername());
		//如果username不存在，就添加到数据库
		if(originalEmployee==null){
			employeeMapper.insert(e);
		}
	}

9.Mapper.xml中写入对应的SQL语句

<!-- void insert(Employee e); -->
<insert id="insert">
    insert into t_employee (username, password, nickName) values (#{username}, #{password}, #{nickName})
</insert>

10.准备要导入的表格

导入效果

​ 以上就是easypoi对excel导入数据库的操作，当然easypoi的功能远不止这么多，easypoi的功能是非常强大的，有兴趣的朋友可以去官方学习。