目录
1.导入依赖
在springboot项目里面导入依赖
<!-- poi依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<!-- poi对于excel 2007的支持依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<!-- poi对于excel 2007的支持依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>2.创建实体类
package com.sprboot_poi.pojo;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.io.Serializable;
/**
* @TableName t_user
*/
@TableName(value = "t_user")
@Data
public class TUser implements Serializable {
//方便数据导入时转换
public TUser(Object[] args) {
/** DecimalFormat 用法
* https://www.jianshu.com/p/b3699d73142e
* Integer.valueOf 返回的时包装类 Integer.parseInt() 返回的是int
*/
//因为传进来的args 的赋值是从1开始的
this.id = new Double(args[0].toString()).intValue();
this.username = args[1].toString();
this.age = new Double(args[2].toString()).intValue();
this.password = args[3].toString();
this.email = args[4].toString();
this.gender=args[5].toString();
}
public TUser() {
}
/**
*
*/
@TableId(type = IdType.AUTO)
private Integer id;
/**
*
*/
private String username;
/**
*
*/
private String password;
/**
*
*/
private Integer age;
/**
*
*/
private String gender;
/**
*
*/
private String email;
@TableField(exist = false)
private static final long serialVersionUID = 1L;
@Override
public boolean equals(Object that) {
if (this == that) {
return true;
}
if (that == null) {
return false;
}
if (getClass() != that.getClass()) {
return false;
}
TUser other = (TUser) that;
return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))
&& (this.getUsername() == null ? other.getUsername() == null : this.getUsername().equals(other.getUsername()))
&& (this.getPassword() == null ? other.getPassword() == null : this.getPassword().equals(other.getPassword()))
&& (this.getAge() == null ? other.getAge() == null : this.getAge().equals(other.getAge()))
&& (this.getGender() == null ? other.getGender() == null : this.getGender().equals(other.getGender()))
&& (this.getEmail() == null ? other.getEmail() == null : this.getEmail().equals(other.getEmail()));
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((getId() == null) ? 0 : getId().hashCode());
result = prime * result + ((getUsername() == null) ? 0 : getUsername().hashCode());
result = prime * result + ((getPassword() == null) ? 0 : getPassword().hashCode());
result = prime * result + ((getAge() == null) ? 0 : getAge().hashCode());
result = prime * result + ((getGender() == null) ? 0 : getGender().hashCode());
result = prime * result + ((getEmail() == null) ? 0 : getEmail().hashCode());
return result;
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append(getClass().getSimpleName());
sb.append(" [");
sb.append("Hash = ").append(hashCode());
sb.append(", id=").append(id);
sb.append(", username=").append(username);
sb.append(", password=").append(password);
sb.append(", age=").append(age);
sb.append(", gender=").append(gender);
sb.append(", email=").append(email);
sb.append(", serialVersionUID=").append(serialVersionUID);
sb.append("]");
return sb.toString();
}
}3.EXCEL数据导出
制作excel数据模板
EXCEL数据导出工具类
package com.sprboot_poi.utils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
/**
* 用于 Excel下载的工具类
*/
public class DownloadUtils {
/**
*
* @param byteArrayOutputStream 输出字节流
* @param response
* @param returnName 输出到客户端的文件名
* @throws IOException
*/
public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {
response.setContentType("application/octet-stream");
returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1")); //保存的文件名,必须和页面编码一致,否则乱码
// response.addHeader("Content-Disposition","attachment;filename=total.xls");
response.addHeader("Content-Disposition","attachment;filename="+returnName);
response.setContentLength(byteArrayOutputStream.size());
response.addHeader("Content-Length", "" + byteArrayOutputStream.size());
ServletOutputStream outputStream = response.getOutputStream(); //取得输出流
byteArrayOutputStream.writeTo(outputStream); //写到输出流
byteArrayOutputStream.close(); //关闭
outputStream.flush(); //刷数据
}
}
EXCEL数据导出接口
/**
* 导出数据 提前制作好的excel模板
*/
@GetMapping("exportTemplate")
public void exportTemplate(HttpServletResponse response) throws IOException {
//1.获取数据
//人为构造的数据，实际是要从数据库中查的
List<TUser> users = tUserService.list();
//2.加载模板
Resource resource = new ClassPathResource("excel/user_info.xlsx");
FileInputStream fis = new FileInputStream(resource.getFile());
//3.根据模板创建工作簿
Workbook wb = new XSSFWorkbook(fis);
//4.读取工作表
Sheet sheet = wb.getSheetAt(0);
//5.抽取第2行的公共样式 , 因为第一行 为标题 第2行是数据 下标为1
Row row = sheet.getRow(0);
CellStyle styles[] = new CellStyle[row.getLastCellNum()];
Cell cell = null;
for (int i = 0; i < row.getLastCellNum(); i++) {
cell = row.getCell(i);
styles[i] = cell.getCellStyle();
}
//6.构造单元格
int rowIndex = 1;
for (TUser user : users) {
//创建每一行，同excel的第二行开始
row = sheet.createRow(rowIndex++);
//第一列
cell = row.createCell(0);
//设置单元格样式
cell.setCellStyle(styles[0]);
//写入数据 序号
cell.setCellValue(user.getId());
//第2列
cell = row.createCell(1);
cell.setCellStyle(styles[1]);
cell.setCellValue(user.getUsername());
cell = row.createCell(2);
cell.setCellStyle(styles[2]);
cell.setCellValue(user.getAge());
cell = row.createCell(3);
cell.setCellStyle(styles[3]);
cell.setCellValue(user.getPassword());
cell = row.createCell(4);
cell.setCellStyle(styles[4]);
cell.setCellValue(user.getEmail());
cell = row.createCell(5);
cell.setCellStyle(styles[5]);
cell.setCellValue(user.getGender() == null ? "男" : user.getGender());
}
//7.下载
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
String fileName = "用户信息-" + new Date().getTime() + ".xlsx";
new DownloadUtils().download(os, response, fileName);
}访问接口以后下载的导出的数据
4.EXCEL数据导入
数据导入接口
/**
* excel 文件上传
* postman 上传文件 操作指南https://jingyan.baidu.com/article/425e69e614f472be14fc166f.html
*/
@PostMapping("/upload")
@ResponseBody
public String upload(@RequestParam(name = "file") MultipartFile file) throws IOException {
//1.解析Excel
//1.1.根据Excel文件创建工作簿
Workbook wb = new XSSFWorkbook(file.getInputStream());
//1.2.获取Sheet
Sheet sheet = wb.getSheetAt(0);//参数：索引
//1.3.获取Sheet中的每一行，和每一个单元格
//2.获取用户数据列表
List<TUser> list = new ArrayList<>();
System.out.println("最后一行的下标 :" + sheet.getLastRowNum());
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);//根据索引获取每一个行
// System.out.println("列数"+row.getLastCellNum());
Object[] values = new Object[row.getLastCellNum()];
for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
Cell cell = row.getCell(cellNum);
Object value = getCellValue(cell);
values[cellNum] = value;
}
TUser user = new TUser(values);
user.setId(null);
list.add(user);
}
//3.批量保存用户
for (TUser userInfo : list) {
tUserService.save(userInfo);
}
return "SUCCESS";
}
public static Object getCellValue(Cell cell) {
//1.获取到单元格的属性类型
CellType cellType = cell.getCellType();
//2.根据单元格数据类型获取数据
Object value = null;
switch (cellType) {
case STRING:
value = cell.getStringCellValue();
break;
case BOOLEAN:
value = cell.getBooleanCellValue();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
//日期格式
value = cell.getDateCellValue();
} else {
//数字
value = cell.getNumericCellValue();
}
break;
case FORMULA: //公式
value = cell.getCellFormula();
break;
default:
break;
}
return value;
}EXCEL数据导入页面
这里使用的是thymeleaf页面
thymeleaf依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>页面
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="echarts.js"></script>
<script src="https://unpkg.com/axios/dist/axios.min.js"></script>
</head>
<body>
<a th:href="@{/exportTemplate}">导出</a>
<form th:action="@{/upload}" method="post" enctype="multipart/form-data">
<input type="file" name="file">
<input type="submit" value="提交">
</form>
</body>
</html>
提交我们刚刚导出的数据。
就发现我们已经将数据新增到数据库了
1928
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
