springboot整合poi注解方式
依赖
<!-- 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>
创建注解@ExcelAttribute，标记它导出Excel’时的列位置
/**
* 定义到 对象中的属性的 标记它导出Excel'时的列位置
*/
//运行时注解
@Retention(RetentionPolicy.RUNTIME)
//属性注解
@Target(ElementType.FIELD)
public @interface ExcelAttribute {
/** 对应的列名称 */
String name() default "";
/** 列序号
* 属性 上short=0 在excel导出时他就是第一列
* */
int sort();
/** 字段类型对应的格式 */
String format() default "";
}
创建注解@ExcelImportAnnotation ,标记导入Excel时的列位置
/**
* 定义到 对象中的属性的 标致它导入Excel'时的列位置
*/
//运行时注解
@Retention(RetentionPolicy.RUNTIME)
//属性注解
@Target(ElementType.FIELD)
public @interface ExcelImportAnnotation {
/** 对应的列名称 */
String name() default "";
/** 列序号
* 属性 上short=0 在excel导入时他就是第一列
* */
int sort();
/** 字段类型对应的格式 */
String format() default "";
}
创建列表实体类
public class User {
@ExcelAttribute(sort = 0)//导出顺序
@ExcelImportAnnotation(sort = 0)//导入顺序
private int id;
@ExcelAttribute(sort = 1)
@ExcelImportAnnotation(sort = 1)
private String name;
@ExcelAttribute(sort = 2)
@ExcelImportAnnotation(sort = 2)
private int age;
@ExcelAttribute(sort = 3)
@ExcelImportAnnotation(sort = 3)
private String sex;
@ExcelAttribute(sort = 4)
@ExcelImportAnnotation(sort = 4)
private String telephone;
@ExcelAttribute(sort = 5)
@ExcelImportAnnotation(sort = 5)
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getTelephone() {
return telephone;
}
public void setTelephone(String telephone) {
this.telephone = telephone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", age=" + age +
", telephone='" + telephone + '\'' +
", address='" + address + '\'' +
'}';
}
}
EXCEL数据导出
制作导出模板
EXCEL数据导出工具类
public class ExcelExportUtil<T> {
private int rowIndex; //写入数据的起始行
private int styleIndex; //需要提取的样式所在的行号
private Class clazz; //对象的字节码
private Field fields[]; //对象中的所有属性
public int getRowIndex() {
return rowIndex;
}
public void setRowIndex(int rowIndex) {
this.rowIndex = rowIndex;
}
public int getStyleIndex() {
return styleIndex;
}
public void setStyleIndex(int styleIndex) {
this.styleIndex = styleIndex;
}
public Class getClazz() {
return clazz;
}
public void setClazz(Class clazz) {
this.clazz = clazz;
}
public Field[] getFields() {
return fields;
}
public void setFields(Field[] fields) {
this.fields = fields;
}
public ExcelExportUtil(Class clazz, int rowIndex, int styleIndex) {
this.clazz = clazz;
this.rowIndex = rowIndex;
this.styleIndex = styleIndex;
fields = clazz.getDeclaredFields();
}
/**
* 基于注解导出
参数：
response：
InputStream:模板的输入流
objs：数据
fileName：生成的文件名
*
*/
public void export(HttpServletResponse response, InputStream is, List<T> objs, String fileName) throws Exception {
//1.根据模板创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook(is);
//2.读取工作表
Sheet sheet = workbook.getSheetAt(0);
//3.提取公共的样式
CellStyle[] styles = getTemplateStyles(sheet.getRow(styleIndex));
//4.根据数据创建每一行和每一个单元格的数据2
AtomicInteger datasAi = new AtomicInteger(rowIndex); //数字
for (T t : objs) {
//datasAi.getAndIncrement() ：获取数字，并++ i++
Row row = sheet.createRow(datasAi.getAndIncrement());
for(int i=0;i<styles.length;i++) {
Cell cell = row.createCell(i);
cell.setCellStyle(styles[i]);
for (Field field : fields) {
if(field.isAnnotationPresent(ExcelAttribute.class)){
field.setAccessible(true);
ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
//如果当前i的值和 属性上注解的 sort值一致就反射调用它的get方法,也就时 当 i=0时就会找属性上注解为0的属性值写入其值
if(i == ea.sort()) {
if(field.get(t) != null) {
cell.setCellValue(field.get(t).toString());
}
}
}
}
}
}
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes("ISO8859-1")));
response.setHeader("filename", fileName);
workbook.write(response.getOutputStream());
}
public CellStyle[] getTemplateStyles(Row row) {
CellStyle [] styles = new CellStyle[row.getLastCellNum()];
for(int i=0;i<row.getLastCellNum();i++) {
styles[i] = row.getCell(i).getCellStyle();
}
return styles;
}
}
EXCEL数据导出接口
/**
* 使用Excel的工具类进行导出 基于模板
*/
@GetMapping("ExcelUtilExport")
public void ExcelUtilExport(HttpServletResponse response) throws Exception {
//1.获取数据
//人为构造的数据，实际是要从数据库中查的
List<Object> users=new ArrayList<>();
for(int i=0;i<20;i++){
User user=new User();
int rs = (int) ((Math.random() * 9 + 1) * Math.pow(10, 8 - 1));
user.setId(i+1);
user.setName("zhangsan"+i);
user.setAge(23);
user.setSex("man");
user.setTelephone("188"+rs);
user.setAddress("随便编个地址");
users.add(user);
}
//2.加载模板
Resource resource = new ClassPathResource("excel/user_info.xlsx");
FileInputStream fis = new FileInputStream(resource.getFile());
//3.通过 工具类下载文件
//因为内容样式和要写的内容都在下标1也就时excel中的第二行
String fileName = "用户信息-"+new Date().getTime()+ ".xlsx";
new ExcelExportUtil<>(User.class,1,1)
/**
* 参数 1：HttpServletResponse
* 2：文件输入流
* 3：封装好的对象
* 4：文件名
*/
.export(response,fis,users,fileName);
}
访问http://localhost:8080/ExcelUtilExport，导出后的数据
EXCEL数据导入
EXCEL数据导入工具类
/**
* Excel 的导入工具类
* ExcelImportAnnotation 导入注解
* @param <T>
*/
public class ExcelImportUtil<T> {
private Class clazz;
private Field fields[];
/** 传入你希望构建的队象*/
public ExcelImportUtil(Class clazz) {
this.clazz = clazz;
fields = clazz.getDeclaredFields();
}
/**
* 基于注解读取excel
* @param is 文件上传的流信息
* @param rowIndex 读取数据的起始行
* @param cellIndex 读取数据的起始单元格位置
* @return
*/
public List<T> readExcel(InputStream is, int rowIndex,int cellIndex) {
List<T> list = new ArrayList<T>();
T entity = null;
try {
XSSFWorkbook workbook = new XSSFWorkbook(is);
Sheet sheet = workbook.getSheetAt(0);
// 不准确
int rowLength = sheet.getLastRowNum();
System.out.println("一共 "+(sheet.getLastRowNum()+1)+" 行");
for (int rowNum = rowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
entity = (T) clazz.newInstance();
System.out.println("一共 "+row.getLastCellNum()+" 列");
for (int j = cellIndex; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
for (Field field : fields) {
if(field.isAnnotationPresent(ExcelImportAnnotation.class)){
field.setAccessible(true);
ExcelImportAnnotation ea = field.getAnnotation(ExcelImportAnnotation.class);
if(j == ea.sort()) {
field.set(entity, covertAttrType(field, cell));
}
}
}
}
list.add(entity);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* 类型转换 将cell 单元格格式转为 字段类型
*/
private Object covertAttrType(Field field, Cell cell) throws Exception {
// System.out.println(field.getType());
String fieldType = field.getType().getSimpleName();
if ("String".equals(fieldType)) {
return getValue(cell);
}else if ("Date".equals(fieldType)) {
return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(getValue(cell)) ;
}else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {
return Integer.parseInt(getValue(cell));
}else if ("long".equals(fieldType) || "Long".equals(fieldType)) {
return Long.parseLong(getValue(cell));
}else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
return Double.parseDouble(getValue(cell));
}else {
return null;
}
}
/**
* 格式转为String
* @param cell
* @return
*/
public String getValue(Cell cell) {
if (cell == null) {
return "";
}
switch (cell.getCellType()) {
case STRING:
return cell.getRichStringCellValue().getString().trim();
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date dt = DateUtil.getJavaDate(cell.getNumericCellValue());
return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(dt);
} else {
// 防止数值变成科学计数法
String strCell = "";
Double num = cell.getNumericCellValue();
BigDecimal bd = new BigDecimal(num.toString());
if (bd != null) {
strCell = bd.toPlainString();
}
// 去除 浮点型 自动加的 .0
if (strCell.endsWith(".0")) {
strCell = strCell.substring(0, strCell.indexOf("."));
}
return strCell;
}
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
default:
return "";
}
}
}
EXCEL数据导入接口
/**
* 使用抽取的Excel的工具类进行导入 基于模板
*/
@PostMapping("ExcelUtilImport")
@ResponseBody
public String ExcelUtilImport(@RequestParam(name = "file") MultipartFile file) throws Exception {
List<Object> list = new ExcelImportUtil<>(User.class).readExcel(file.getInputStream(), 1, 0);
for (Object object:list) {
User user=(User) object;
System.out.println(user);
}
return "SUCCESS";
}
EXCEL数据导入页面
这里使用的是thymeleaf页面
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>
</head>
<body>
<div style="text-align: center">
导入EXCEL文件
<form th:action="@{/ExcelUtilImport}" method="post" enctype="multipart/form-data">
<input type="file" name="file"/>
<input type="submit" value="导入数据"/>
</form>
</div>
</body>
</html>
得到数据
一共 21 行
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
一共 6 列
User{id=1, name='zhangsan0', sex='man', age=23, telephone='18867691002', address='随便编个地址'}
User{id=2, name='zhangsan1', sex='man', age=23, telephone='18871858182', address='随便编个地址'}
User{id=3, name='zhangsan2', sex='man', age=23, telephone='18860515690', address='随便编个地址'}
User{id=4, name='zhangsan3', sex='man', age=23, telephone='18822934150', address='随便编个地址'}
User{id=5, name='zhangsan4', sex='man', age=23, telephone='18842939002', address='随便编个地址'}
User{id=6, name='zhangsan5', sex='man', age=23, telephone='18817180044', address='随便编个地址'}
User{id=7, name='zhangsan6', sex='man', age=23, telephone='18872839379', address='随便编个地址'}
User{id=8, name='zhangsan7', sex='man', age=23, telephone='18896521264', address='随便编个地址'}
User{id=9, name='zhangsan8', sex='man', age=23, telephone='18814273716', address='随便编个地址'}
User{id=10, name='zhangsan9', sex='man', age=23, telephone='18832458697', address='随便编个地址'}
User{id=11, name='zhangsan10', sex='man', age=23, telephone='18855081239', address='随便编个地址'}
User{id=12, name='zhangsan11', sex='man', age=23, telephone='18834442132', address='随便编个地址'}
User{id=13, name='zhangsan12', sex='man', age=23, telephone='18826540639', address='随便编个地址'}
User{id=14, name='zhangsan13', sex='man', age=23, telephone='18879828722', address='随便编个地址'}
User{id=15, name='zhangsan14', sex='man', age=23, telephone='18834370748', address='随便编个地址'}
User{id=16, name='zhangsan15', sex='man', age=23, telephone='18859144330', address='随便编个地址'}
User{id=17, name='zhangsan16', sex='man', age=23, telephone='18854104948', address='随便编个地址'}
User{id=18, name='zhangsan17', sex='man', age=23, telephone='18826971920', address='随便编个地址'}
User{id=19, name='zhangsan18', sex='man', age=23, telephone='18839741207', address='随便编个地址'}
User{id=20, name='zhangsan19', sex='man', age=23, telephone='18837065603', address='随便编个地址'}
扫一扫
22万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
