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

分类:热门推荐日期:2024-03-09浏览:1评论: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.poipoi3.17 org.apache.poipoi-ooxml3.17

2、poi excel导出

1、前端：一个小页面

  excel导入导出

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 < users.size(); rowNum++) {
                sysUser = users.get(rowNum);
                //生成一行
                HSSFRow row = sheet.createRow(rowNum + 1);
                //添加每一列数据
                HSSFCell cell = row.createCell(0);
                cell.setCellValue(sysUser.getUserName());
                cell = row.createCell(1);
                cell.setCellValue(sysUser.getNickName());
                cell = row.createCell(2);
                cell.setCellValue(sysUser.getEmail());
                cell = row.createCell(3);
                cell.setCellValue(sysUser.getPhonenumber());
                cell = row.createCell(4);
                cell.setCellValue(sysUser.getSex());
                cell = row.createCell(5);
                cell.setCellValue(sysUser.getStatus());
                cell = row.createCell(6);
                cell.setCellValue(sysUser.getDelFlag());
            }
        }
        OutputStream out = null;
        try {
            //把生成的excel文件下载到客户端
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=user.xls");
            out = response.getOutputStream();
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.flush();//刷新流：通道中数据全部输出
                    out.close();//关闭流
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

3、其他数据

完成上面的步骤，导出就已经就已经差不多了，这里提供一下我的简单的service和数据表信息

service

@Service
public class ExcelServiceImpl implements ExcelService {
    @Autowired
    private ExceDao excelDao;
    @Override
    public List poiExcelWrite() {
        return excelDao.poiExcelWrite();
    }
}

SQL.XML

数据库表：

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导入
 请选择要上传的文件：[仅支持.xls]

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 <= sheet.getLastRowNum(); i++) {// sheet.getLastRowNum()：最后一行的下标
                row = sheet.getRow(i);// 行的下标，下标从0开始，依次增加
                sysUser = new SysUser();
                for (int j = 0; j < row.getLastCellNum(); j++) { // row.getLastCellNum():最后一列的下标+1
                    //根据row获取HSSFCell对象，封装了一列的所有信息
                    cell = row.getCell(j);// 列的下标，下标从0开始，依次增加
                    //获取列中的数据
                    Object cellValue = HSSFutils.getCellValueForStr((HSSFCell) cell);
                    //对应的列=对应的数据（固定的）
                    if (j == 0) {
                        sysUser.setUserName(cellValue.toString());
                    } else if (j == 1) {
                        sysUser.setNickName(cellValue.toString());
                    } else if (j == 2) {
                        sysUser.setEmail(cellValue.toString());
                    } else if (j == 3) {
                        sysUser.setPhonenumber(cellValue.toString());
                    } else if (j == 4) {
                        sysUser.setSex(Integer.parseInt(cellValue.toString()));
                    } else if (j == 5) {
                        sysUser.setStatus(Integer.parseInt(cellValue.toString()));
                    } else if (j == 6) {
                        sysUser.setDelFlag(Integer.parseInt(cellValue.toString()));
                    }
                }
                //每一行中所有列都封装完成之后，把activity保存到list中
                sysUserList.add(sysUser);
            }
            //调用service层方法，保存市场活动
            int ret = excelService.saveUsers(sysUserList);
            returnObject.setCode("1");//成功
            returnObject.setRetData(ret);
            System.out.println(ret);
        } catch (IOException e) {
            e.printStackTrace();
            returnObject.setCode("500");//失败
            returnObject.setMessage("系统忙，请稍后重试....");
        } finally {
            if (in != null) {
                try {
                    in.close();//关闭流
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return returnObject;
    }

2、其他数据

提供使用到的工具类和xml

工具类 

返回前端的响应数据

@Data
public class ReturnObject {
    private String code;//返回的编码
    private String message;//返回的提示信息
    private Integer retData;//返回条数
}

根据数据类型，获取表中数据

/**
 * 关于excel文件操作的工具类
 */
public class HSSFutils {
    public static Object getCellValueForStr(HSSFCell cell){
            int cellType = cell.getCellType();//获取当前数据类型
            Object cellValue = null;
            switch (cellType){
//                case Cell.CELL_TYPE_FORMULA://公式：excel函数类型
//                    //获取公式，可以理解为已String类型获取cell的值输出
//                    String cellFormula = cell.getCellFormula();
//                                    System.out.println(cellFormula);
//                    //执行公式，此处cell的值就是公式
//                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);//让cell类型变成数字进行计算
//                    CellValue evaluate = formulaEvaluator.evaluate(cell);//计算
//                    cellValue  = evaluate.formatAsString();//值转成String
//                                    String cellFormula = cell.getCellFormula();//获取计算公式内容
//                                    System.out.println(cellFormula);
//                                    //计算
//                                    CellValue evaluate = formulaEvaluator.evaluate(cell);
//                                    cellValue = evaluate.formatAsString();
//                    break;
                case HSSFCell.CELL_TYPE_NUMERIC://数字类型(日期、普通数据)
                    if (HSSFDateUtil.isCellDateFormatted(cell)){//是否为日期类型
                        Date dateCellValue = cell.getDateCellValue();
                        cellValue = new DateTime(dateCellValue).toString("yyyy-MM-dd");
                    }else {//普通数字
                        //cellValue = cell.getNumericCellValue();
                        //防止数字太长,数字类型装不下，可以转为String
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cellValue = cell.toString();
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING://String
                    cellValue = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                    cellValue = cell.getBooleanCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BLANK://空null
                    cellValue = null;
                    break;
                case HSSFCell.CELL_TYPE_ERROR://错误类型
                    System.out.println("数据类型错误");
                    break;
                default:
                    System.out.println("未知类型");
            }
        return cellValue;
    }
    /**
     * 返回String类型   
     * 从指定的HSSFCell对象中获取列的值 
     * @return
     */
//    public static String getCellValueForStr(HSSFCell cell){
//        String ret="";
//        if(cell.getCellType()==HSSFCell.CELL_TYPE_STRING){
//            ret=cell.getStringCellValue();
//        }else if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
//            ret=cell.getNumericCellValue()+"";
//        }else if(cell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
//            ret=cell.getBooleanCellValue()+"";
//        }else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA){
//            ret=cell.getCellFormula();
//        }else{
//            ret="";
//        }
//
//        return ret;
//    }
}

dao层xml

  insert into sys_user(user_name,nick_name,email,phonenumber,sex,status,del_flag)
        values  (#{obj.userName},#{obj.nickName},#{obj.email},#{obj.phonenumber},#{obj.sex},#{obj.status},#{obj.delFlag})

3、 测试效果

用之前导出的文件准备数据

 选择文件后，点击导入

 

数据库查看导入效果

 

 

 本文还是有许多不足的，欢迎指正。

可以试着去进行优化，这里也还没有做数据校验。

相关推荐

关于我们常见问题广告服务免责声明联系我们
Powered By Z-BlogPHP 1.7.3 Theme By 优美主题
Copyright Your WebSite.Some Rights Reserved. 浙ICP备2023018783号