操作excel导入导出 【Springboot Web】"/>
Apache / poi 操作excel导入导出 【Springboot Web】
目录
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操作核心依赖
<!-- 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=".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 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 {@Autowiredprivate 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 {@Autowiredprivate ExceDao excelDao;@Overridepublic List<SysUser> poiExcelWrite() {return excelDao.poiExcelWrite();}}
SQL.XML
<select id="poiExcelWrite" resultType="com.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 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、前端：一个小页面
这里导入导出都放在了一个页面
<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>excel导入导出</title><script src=".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--不是,默认是truecontentType:false,//设置ajax向后台提交参数之前，是否把所有的参数统一按urlencoded编码：true--是,false--不是，默认是truetype:'post',dataType:'json',success:function (data) {if(data.code=="1"){//提示成功导入记录条数alert("成功导入"+data.retData+"条记录");}else{//提示信息alert(data.message);}}});});</script></body>
</html>2、后端处理
1、controller
@PostMapping("/poiExcel/read")@ResponseBodypublic 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.getNumericCellValue();//防止数字太长,数字类型装不下，可以转为Stringcell.setCellType(HSSFCell.CELL_TYPE_STRING);cellValue = cell.toString();}break;case HSSFCell.CELL_TYPE_STRING://StringcellValue = cell.getStringCellValue();break;case HSSFCell.CELL_TYPE_BOOLEAN://布尔cellValue = cell.getBooleanCellValue();break;case HSSFCell.CELL_TYPE_BLANK://空nullcellValue = 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 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、 测试效果
用之前导出的文件准备数据
选择文件后，点击导入
数据库查看导入效果
本文还是有许多不足的，欢迎指正。
可以试着去进行优化，这里也还没有做数据校验。
更多推荐
Apache / poi 操作excel导入导出 【Springboot Web】
发布评论