Apache / poi 操作excel导入导出【Springboot Web】

04-23 1767阅读 0评论

1、准备

1、简单介绍

2、Excel操作核心依赖

2、poi excel导出

1、前端：一个小页面

2、后端处理

1、实体类

2、controller

3、其他数据

3、测试效果

3、poi excel导入

1、前端：一个小页面

2、后端处理

1、controller

2、其他数据

3、测试效果

1、准备

1、简单介绍

Excel文件版本：

2003版本

扩展名为.xls使用HSSFWorkbook类操作(只能65536行，超过报错)

2007版本

扩展名为.xlsx使用XSSFWorkbook类操作

XSSFWorkbook 优化使用(缓存) 使用SXSSFWorkbook类操作

2、Excel操作核心依赖

        
        
		
			org.apache.poi
			poi
			3.17
		
        
		
			org.apache.poi
			poi-ooxml
			3.17

2、poi excel导出

1、前端：一个小页面



    
    excel导入导出
    



    下载列表数据poi


    /**
     * poi导出
     */
    //给"批量导出"按钮添加单击事件
    $("#exportActivityAllBtn").click(function () {
        //发送同步请求
        window.location.href="poiExcel/write";
    });

2、后端处理

1、实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class SysUser {
   private String  userName;// varchar(30) NOT NULL COMMENT '用户账号',
   private String  nickName;// varchar(30) NOT NULL COMMENT '用户昵称',
   private String  email;// varchar(50) DEFAULT '' COMMENT '用户邮箱',
   private String  phonenumber;// varchar(11) DEFAULT '' COMMENT '手机号码',
   private Integer  sex;// char(1) DEFAULT '0' COMMENT '用户性别（0男 1女 2未知）',
   private Integer  status;// char(1) DEFAULT '0' COMMENT '帐号状态（0正常 1停用）',
   private Integer  delFlag;// char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
}

2、controller

为了方便，我就把操作处理全放在了controller层

@Controller
public class PoiExcelController {
    @Autowired
    private ExcelService excelService;
    //映射index页面
    @RequestMapping("/")
    public String index() {
        return "index";
    }
    @GetMapping("/poiExcel/write")
    public void poiExcelWrite(HttpServletResponse response) {
        //从数据库获取用户数据
        List users = excelService.poiExcelWrite();
        //查看是否获取到数据
//        for (SysUser s: users) {
//            System.out.println(s);
//        }
        //1、创建一个03版本的工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        //2、创建一个工作表
        HSSFSheet sheet = workbook.createSheet("用户信息");
        //3、创建第一行标题
        //3、创建一个行
        HSSFRow rowTitle = sheet.createRow(0);
        //4、创建一个小单元格(1,1)
        HSSFCell cellTitle = rowTitle.createCell(0);
        cellTitle.setCellValue("用户名");
        cellTitle = rowTitle.createCell(1);
        cellTitle.setCellValue("昵称");
        cellTitle = rowTitle.createCell(2);
        cellTitle.setCellValue("邮箱");
        cellTitle = rowTitle.createCell(3);
        cellTitle.setCellValue("手机号");
        cellTitle = rowTitle.createCell(4);
        cellTitle.setCellValue("用户性别（0男 1女 2未知）");
        cellTitle = rowTitle.createCell(5);
        cellTitle.setCellValue("帐号状态（0正常 1停用）");
        cellTitle = rowTitle.createCell(6);
        cellTitle.setCellValue("删除标志（0代表存在 2代表删除）");
        if (users != null && users.size() > 0) {
            //写入用户数据
            SysUser sysUser = null;
            for (int rowNum = 0; rowNum  
3、其他数据 
 
 完成上面的步骤，导出就已经就已经差不多了，这里提供一下我的简单的service和数据表信息 
 
service 
@Service
public class ExcelServiceImpl implements ExcelService {
    @Autowired
    private ExceDao excelDao;
    @Override
    public List poiExcelWrite() {
        return excelDao.poiExcelWrite();
    }
}
 
SQL.XML 
    
        SELECT user_name username,nick_name nickname,email,phonenumber,sex,status,del_flag delflag from sys_user;;
     
数据库表： 
CREATE TABLE `sys_user` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `dept_id` bigint(20) DEFAULT NULL COMMENT '部门ID',
  `user_name` varchar(30) NOT NULL COMMENT '用户账号',
  `nick_name` varchar(30) NOT NULL COMMENT '用户昵称',
  `user_type` varchar(2) DEFAULT '00' COMMENT '用户类型（00系统用户）',
  `email` varchar(50) DEFAULT '' COMMENT '用户邮箱',
  `phonenumber` varchar(11) DEFAULT '' COMMENT '手机号码',
  `sex` char(1) DEFAULT '0' COMMENT '用户性别（0男 1女 2未知）',
  `avatar` varchar(100) DEFAULT '' COMMENT '头像地址',
  `password` varchar(100) DEFAULT '' COMMENT '密码',
  `status` char(1) DEFAULT '0' COMMENT '帐号状态（0正常 1停用）',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  `login_ip` varchar(128) DEFAULT '' COMMENT '最后登录IP',
  `login_date` datetime DEFAULT NULL COMMENT '最后登录时间',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=158 DEFAULT CHARSET=utf8 COMMENT='用户信息表' 
 
 3、测试效果 
点击下载 
 
 
 
3、poi excel导入 
1、前端：一个小页面 
 
  这里导入导出都放在了一个页面 
 


    
    excel导入导出
    



    下载列表数据poi

poi导入
            
            
                
                    请选择要上传的文件：[仅支持.xls]
                
                
                    
                
                
                    导入
                
            
        

    /**
     * poi导出
     */
    //给"批量导出"按钮添加单击事件
    $("#exportActivityAllBtn").click(function () {
        //发送同步请求
        window.location.href="poiExcel/write";
    });
  
    /**
     * poi导入
     */
    //给"导入"按钮添加单击事件
    $("#importActivityBtn").click(function () {
        //收集参数
        //——xls文件获取的是文件名
        var activityFileName=$("#activityFile").val();
        var suffix=activityFileName.substr(activityFileName.lastIndexOf(".")+1).toLocaleLowerCase();//xls,XLS,Xls,xLs,....截取字符串后缀，toLocaleLowerCase()：转小写
        if(suffix!="xls"){
            alert("只支持xls文件");
            return;
        }
        //——获取文件内容
        var activityFile=$("#activityFile")[0].files[0];
        //FormData是ajax提供的接口,可以模拟键值对向后台提交参数;
        //FormData最大的优势是不但能提交文本数据，还能提交二进制数据
        var formData=new FormData();
        formData.append("activityFile",activityFile);
        //发送请求
        $.ajax({
            url:'/poiExcel/read',
            data:formData,
            processData:false,//设置ajax向后台提交参数之前，是否把参数统一转换成字符串：true--是,false--不是,默认是true
            contentType:false,//设置ajax向后台提交参数之前，是否把所有的参数统一按urlencoded编码：true--是,false--不是，默认是true
            type:'post',
            dataType:'json',
            success:function (data) {
                if(data.code=="1"){
                    //提示成功导入记录条数
                    alert("成功导入"+data.retData+"条记录");
                }else{
                    //提示信息
                    alert(data.message);
                }
            }
        });
    });
  


 
2、后端处理 
1、controller 
 @PostMapping("/poiExcel/read")
    @ResponseBody
    public Object poiExcelWrite(MultipartFile activityFile) {
//        System.out.println(activityFile.getOriginalFilename());
        ReturnObject returnObject = new ReturnObject();//响应数据
        InputStream in = null;
        try {
            //获取文件流
            in = activityFile.getInputStream();
            //1、创建一个03版本的工作薄
            Workbook workbook = new HSSFWorkbook(in);
            //2、获取第0张表
            Sheet sheet = workbook.getSheetAt(0);
            //3、获取数据
            Row row = null;
            Cell cell = null;
            SysUser sysUser = null;
            List sysUserList = new ArrayList();
            for (int i = 1; i