Apache / poi 操作excel导入导出 【Springboot Web】
小白要努力变黑 2024-08-05 17:33:05 阅读 60
目录
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操作核心依赖
<code> <!--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">code>
<head>
<meta charset="UTF-8">code>
<title>excel导入导出</title>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>code>
</head>
<body>
<button id="exportActivityAllBtn" type="button" >code>
下载列表数据poi
</button>
<script>
/**
* poi导出
*/
//给"批量导出"按钮添加单击事件
$("#exportActivityAllBtn").click(function () {
//发送同步请求
window.location.href="poiExcel/write";code>
});
</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 {
@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="com.zoubin.easyexcel.web.entity.SysUser">code>
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='用户信息表'code>
3、测试效果
点击下载
3、poi excel导入
1、前端：一个小页面
这里导入导出都放在了一个页面
<code><!DOCTYPE html>
<html lang="en">code>
<head>
<meta charset="UTF-8">code>
<title>excel导入导出</title>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>code>
</head>
<body>
<button id="exportActivityAllBtn" type="button" >code>
下载列表数据poi
</button>
<hr/>
<!-- 导入 -->
<div >
<div >
<h4 id="myModalLabel">poi导入</h4>code>
</div>
<div style="height: 350px;">code>
<div style="position: relative;top: 20px; left: 50px;">code>
请选择要上传的文件：<small style="color: gray;">[仅支持.xls]</small>code>
</div>
<div style="position: relative;top: 40px; left: 50px;">code>
<input type="file" id="activityFile">code>
</div>
<div style="position: relative;top: 60px; left: 50px;">code>
<button id="importActivityBtn" type="button" >导入</button>code>
</div>
</div>
</div>
<script>
/**
* poi导出
*/
//给"批量导出"按钮添加单击事件
$("#exportActivityAllBtn").click(function () {
//发送同步请求
window.location.href="poiExcel/write";code>
});
/**
* 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);
}
}
});
});
</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.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="";code>
// 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="";code>
// }
//
// return ret;
// }
}
dao层xml
<insert id="saveUsers">code>
insert into sys_user(user_name,nick_name,email,phonenumber,sex,status,del_flag)
values
<foreach collection="list" item="obj" separator=",">code>
(#{obj.userName},#{obj.nickName},#{obj.email},#{obj.phonenumber},#{obj.sex},#{obj.status},#{obj.delFlag})
</foreach>
</insert>
3、 测试效果
用之前导出的文件准备数据
选择文件后，点击导入
数据库查看导入效果
本文还是有许多不足的，欢迎指正。
可以试着去进行优化，这里也还没有做数据校验。
声明
本文内容仅代表作者观点，或转载于其他网站，本站不以此文作为商业用途
如有涉及侵权，请联系本站进行删除
转载本站原创文章，请注明来源及作者。