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

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

目录

1、准备

1、简单介绍

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操作核心依赖

java">        <!--		poi依赖-->
        <!--		03-->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>
        <!--		07-->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.17</version>
		</dependency>

2、poi excel导出

1、前端:一个小页面

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>excel导入导出</title>
    <script src="https://code.jquery.***/jquery-3.6.0.min.js"></script>

</head>
<body>
<button id="exportActivityAllBtn" type="button" >
    下载列表数据poi
</button>

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

</script>

</body>
</html>

2、后端处理

1、实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class SysUser {

   private String  userName;// varchar(30) NOT NULL ***MENT '用户账号',
   private String  nickName;// varchar(30) NOT NULL ***MENT '用户昵称',
   private String  email;// varchar(50) DEFAULT '' ***MENT '用户邮箱',
   private String  phonenumber;// varchar(11) DEFAULT '' ***MENT '手机号码',
   private Integer  sex;// char(1) DEFAULT '0' ***MENT '用户性别(0男 1女 2未知)',
   private Integer  status;// char(1) DEFAULT '0' ***MENT '帐号状态(0正常 1停用)',
   private Integer  delFlag;// char(1) DEFAULT '0' ***MENT '删除标志(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<SysUser> 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<SysUser> poiExcelWrite() {
        return excelDao.poiExcelWrite();
    }

}

SQL.XML

    <select id="poiExcelWrite" resultType="***.zoubin.easyexcel.web.entity.SysUser">
        SELECT user_name username,nick_name nickname,email,phonenumber,sex,status,del_flag delflag from sys_user;;
    </select>

数据库表:

CREATE TABLE `sys_user` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT ***MENT '用户ID',
  `dept_id` bigint(20) DEFAULT NULL ***MENT '部门ID',
  `user_name` varchar(30) NOT NULL ***MENT '用户账号',
  `nick_name` varchar(30) NOT NULL ***MENT '用户昵称',
  `user_type` varchar(2) DEFAULT '00' ***MENT '用户类型(00系统用户)',
  `email` varchar(50) DEFAULT '' ***MENT '用户邮箱',
  `phonenumber` varchar(11) DEFAULT '' ***MENT '手机号码',
  `sex` char(1) DEFAULT '0' ***MENT '用户性别(0男 1女 2未知)',
  `avatar` varchar(100) DEFAULT '' ***MENT '头像地址',
  `password` varchar(100) DEFAULT '' ***MENT '密码',
  `status` char(1) DEFAULT '0' ***MENT '帐号状态(0正常 1停用)',
  `del_flag` char(1) DEFAULT '0' ***MENT '删除标志(0代表存在 2代表删除)',
  `login_ip` varchar(128) DEFAULT '' ***MENT '最后登录IP',
  `login_date` datetime DEFAULT NULL ***MENT '最后登录时间',
  `create_by` varchar(64) DEFAULT '' ***MENT '创建者',
  `create_time` datetime DEFAULT NULL ***MENT '创建时间',
  `update_by` varchar(64) DEFAULT '' ***MENT '更新者',
  `update_time` datetime DEFAULT NULL ***MENT '更新时间',
  `remark` varchar(500) DEFAULT NULL ***MENT '备注',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=158 DEFAULT CHARSET=utf8 ***MENT='用户信息表'

 3、测试效果

点击下载

3、poi excel导入

1、前端:一个小页面

 这里导入导出都放在了一个页面

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>excel导入导出</title>
    <script src="https://code.jquery.***/jquery-3.6.0.min.js"></script>

</head>
<body>
<button id="exportActivityAllBtn" type="button" >
    下载列表数据poi
</button>

<hr/>
<!-- 导入 -->
        <div >
            <div >
                <h4  id="myModalLabel">poi导入</h4>
            </div>
            <div  style="height: 350px;">
                <div style="position: relative;top: 20px; left: 50px;">
                    请选择要上传的文件:<small style="color: gray;">[仅支持.xls]</small>
                </div>
                <div style="position: relative;top: 40px; left: 50px;">
                    <input type="file" id="activityFile">
                </div>
                <div style="position: relative;top: 60px; left: 50px;">
                    <button id="importActivityBtn" type="button" >导入</button>
                </div>
            </div>

        </div>


<script>
    /**
     * 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',
            su***ess:function (data) {
                if(data.code=="1"){
                    //提示成功导入记录条数
                    alert("成功导入"+data.retData+"条记录");
                }else{
                    //提示信息
                    alert(data.message);
                }
            }
        });
    });

  
</script>

</body>
</html>

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<SysUser> 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.getNumeri***ellValue();
                        //防止数字太长,数字类型装不下,可以转为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.getNumeri***ellValue()+"";
//        }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 id="saveUsers">
        insert into sys_user(user_name,nick_name,email,phonenumber,sex,status,del_flag)
        values
        <foreach collection="list" item="obj" separator=",">
            (#{obj.userName},#{obj.nickName},#{obj.email},#{obj.phonenumber},#{obj.sex},#{obj.status},#{obj.delFlag})
        </foreach>
    </insert>

3、 测试效果

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

 选择文件后,点击导入

 

数据库查看导入效果

 

 

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

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

转载请说明出处内容投诉
CSS教程_站长资源网 » Apache / poi 操作excel导入导出 【Springboot Web】

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买