第一种:基于easy-poi实现Excel导入导出
1、导出Excel表格
-
第一步:在pom文件中导入依赖
<!--基于easy-poi实现Excel导入导出-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>6.1.3.Final</version>
</dependency>
-
第二步:在数据库表对应的实体类中给每个属性添加注解@Excel
// 这里@Excel注解中的name属性制定了之后导出Excel表的第一行表头
@Excel(name = "员工编号")
private Integer id;
@Excel(name = "员工姓名")
private String name;
@Excel(name = "性别")
private String gender;
@Excel(name = "出生日期")
private Date birthday;
@Excel(name = "身份证号")
private String idCard;
@Excel(name = "婚姻状况")
private String wedlock;
@Excel(name = "民族")
private Integer nationId;
@Excel(name = "籍贯")
private String nativePlace;
@Excel(name = "政治面貌")
private Integer politicId;
@Excel(name = "邮箱")
private String email;
-
第三步:表格样式类编写
package com.zjy.utils;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
/**
* @author zjy
* @since 2023-02-21
*/
public class ExcelStyleUtil implements IExcelExportStyler {
private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
private static final short FONT_SIZE_TEN = 15;
private static final short FONT_SIZE_ELEVEN = 15;
private static final short FONT_SIZE_TWELVE = 18;
// 标题头样式
private CellStyle headerStyle;
// 数据列标题样式
private CellStyle titleStyle;
// 数据行样式
private CellStyle styles;
public ExcelStyleUtil(Workbook workbook) {
this.init(workbook);
}
/**
* 初始化样式
*
* @param workbook
*/
private void init(Workbook workbook) {
this.headerStyle = initHeaderStyle(workbook);
this.titleStyle = initTitleStyle(workbook);
this.styles = initStyles(workbook);
}
/**
* 标题头样式
*
* @param color
* @return
*/
@Override
public CellStyle getHeaderStyle(short color) {
return headerStyle;
}
/**
* 数据列标题样式
*
* @param color
* @return
*/
@Override
public CellStyle getTitleStyle(short color) {
return titleStyle;
}
/**
* 数据行样式
*
* @param parity 表示奇偶行
* @param excelExportEntity 数据内容
* @return
*/
@Override
public CellStyle getStyles(boolean parity, ExcelExportEntity excelExportEntity) {
return styles;
}
/**
* 数据行样式
*
* @param cell 单元格
* @param dataRow 数据行
* @param excelExportEntity 数据内容
* @param obj 对象
* @param data 数据
* @return
*/
@Override
public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity excelExportEntity, Object obj, Object data) {
return getStyles(true, excelExportEntity);
}
/**
* 模板使用的样式设置
*
* @param b
* @param excelForEachParams
* @return
*/
@Override
public CellStyle getTemplateStyles(boolean b, ExcelForEachParams excelForEachParams) {
return null;
}
/**
* 初始化--标题头样式
*
* @param workbook
* @return
*/
private CellStyle initHeaderStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
return style;
}
/**
* 初始化--数据列标题样式
*
* @param workbook
* @return
*/
private CellStyle initTitleStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
// 背景色
style.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
/**
* 初始化--数据行样式
*
* @param workbook
* @return
*/
private CellStyle initStyles(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
style.setDataFormat(STRING_FORMAT);
return style;
}
/**
* 基础样式
*
* @return
*/
private CellStyle getBaseCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
// 下边框
style.setBorderBottom(BorderStyle.THIN);
// 左边框
style.setBorderLeft(BorderStyle.THIN);
// 上边框
style.setBorderTop(BorderStyle.THIN);
// 右边框
style.setBorderRight(BorderStyle.THIN);
// 水平居中
style.setAlignment(HorizontalAlignment.CENTER);
// 上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置自动换行
style.setWrapText(true);
return style;
}
/**
* 字体样式
*
* @param size 字体大小
* @param isBold 是否加粗
* @return
*/
private Font getFont(Workbook workbook, short size, boolean isBold) {
Font font = workbook.createFont();
// 字体样式
font.setFontName("楷体");
// 是否加粗
font.setBold(isBold);
// 字体大小
font.setFontHeightInPoints(size);
return font;
}
}
-
第四步:编写service层代码,service实现类实现接口
//文件下载
@Override
public Workbook download() {
// 查询出后台实体类集合
List<User> userList = userMapper.downloadList(sid);
ExportParams exportParams = new ExportParams();
exportParams.setType(ExcelType.XSSF); //对应的xlsx
exportParams.setStyle(ExcelStyleUtil.class); //设置导出样式
exportParams.setHeight((short) 8); //设置行高
return ExcelExportUtil.exportExcel(exportParams, User.class, userList);
}
-
第五步:编写controller层代码
//导出下载表格,注意返回值必须是void
@RequestMapping("/download")
public void download(HttpServletResponse response) {
Workbook workbook = employeeService.download();
//获取Sheet
Sheet sheet = workbook.getSheetAt(0);
setSizeColumn(sheet);
//命名表格
String fileName = "emp.xlsx";
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(fileName,"UTF-8"));
//输出流
workbook.write(response.getOutputStream());
// 清除response
response.flushBuffer();
} catch (Exception e) {
log.error(e.getMessage());
}
}
//设置表格自适应宽度
private void setSizeColumn(Sheet sheet) {
int maxColumn = sheet.getRow(0).getPhysicalNumberOfCells();
for(int i = 0; i < maxColumn; i++){
sheet.autoSizeColumn(i);
}
for (int i = 0; i < maxColumn; i++) {
int orgWidth = sheet.getColumnWidth(i);
sheet.autoSizeColumn(i, true);
int newWidth = (int) (sheet.getColumnWidth(i) + 100);
int maxWith = 256*255;
//限制下最大宽度
if(newWidth > maxWith) {
sheet.setColumnWidth(i, maxWith);
}else if (newWidth > orgWidth) {
sheet.setColumnWidth(i, newWidth);
} else {
sheet.setColumnWidth(i, orgWidth);
}
}
}
-
第六步:访问controller方法的路径进行文件下载
如果在项目中实现Excel表导出,只需写一个按钮,点击之后跳转controller方法路径,实现导出表格
2、导入Excel表格
-
第一步:编写service层代码,service实现类实现接口
//上传表格
@Override
public List<Employee> uploadFile(MultipartFile multipartFile) {
// 新建导入对象
ImportParams importParams = new ImportParams();
List<Employee> employeeList = null;
try {
employeeList = ExcelImportUtil.importExcel(multipartFile.getInputStream(),Employee.class,importParams);
} catch (Exception e) {
e.printStackTrace();
}
// 添加实体类集合中的实体类数据到数据库表中
if (employeeList != null) {
for (Employee emp : employeeList) {
employeeMapper.insert(emp);
log.info(emp + "");
}
}
return employeeList;
}
-
第二步:编写controller层代码
//实现上传,返回值 R 是我自定义的一个工具类,只是将数据返回到页面
@PostMapping("/upload")
public R uploadFile(@RequestParam("file")MultipartFile multipartFile, Model model) {
List<Employee> employeeList = employeeService.uploadFile(multipartFile);
return R.ok().data("employeeList",employeeList);
}
-
第三步:在前端页面中写一个表单进行文件导入
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<form action="upload" method="post" enctype="multipart/form-data">
<input type="file" name="file">
<input type="submit" value="上传">
</form>
</body>
</html>
至此就完成Excel表格的导出与导入啦。
第二种:使用EasyExcel导入导出功能
1、导出Excel表格
-
第一步:添加依赖
<!--使用EasyExcel导入导出功能-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
-
第二步:在实体类中属性上添加注解@ExcelProperty(value = "编号",index = 0)
@ExcelProperty(value = "编号",index = 0)
private Integer id;
@ExcelProperty(value = "姓名",index = 1)
private String name;
@ExcelProperty(value = "手机号码",index = 2)
private String phone;
@ExcelProperty(value = "住宅电话",index = 3)
private String telephone;
@ExcelProperty(value = "联系地址",index = 4)
private String address;
@ExcelProperty(value = "是否启用",index = 5)
private Boolean enabled;
@ExcelProperty(value = "用户名",index = 6)
private String username;
@ExcelProperty(value = "密码",index = 7)
private String password;
@ExcelProperty(value = "用户头像",index = 8)
private String userFace;
@ExcelProperty(value = "备注",index = 9)
private String remark;
@ExcelProperty(value = "盐值",index = 10)
private String slot;
-
第三步:编写controller层代码
//导出文件
@GetMapping("/write")
public R writeExcel(HttpServletResponse response) {
response.setCharacterEncoding("UTF-8");
//content-disposition 指示如何处理响应内容,一般有两种方式:
// inline :直接在页面显示, attchment :以附件形式下载
response.setHeader("Content-disposition","attachment;filename=demo.xlsx");
//获取所有数据
List<Admin> list = adminService.list();
try {
EasyExcel.write(response.getOutputStream(),Admin.class)
.sheet("账号基本信息").doWrite(list);
} catch (IOException e) {
e.printStackTrace();
}
return R.ok().message("数据导出成功");
}
-
第四步:访问controller方法的路径进行文件下载
如果在项目中实现Excel表导出,只需写一个按钮,点击之后跳转controller方法路径,实现导出表格
2、导入Excel表格
-
第一步:导入功能需要自己去重写一个监听器类
package com.zjy.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellData;
import com.zjy.entity.Admin;
import com.zjy.service.AdminService;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* @Author ZJY
* @Date: 2022/11/17 22:25
*/
public class ExcelListener extends AnalysisEventListener<Admin> {
// 用于存储导入进来的数据
private List<Admin> adminList = new ArrayList<>();
/**
* 间隔多少条数据进行保存
* @param headMap
* @param context
*/
private static final int BATCH_COUNT=5;
/**
* @param headMap
* @param context
*/
private AdminService adminService;
public ExcelListener(AdminService adminService){
this.adminService=adminService;
}
@Override
public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
System.out.println("读取表头位置:"+headMap);
super.invokeHead(headMap, context);
}
// 一行一行读取数据,每一次都需要调用该方法
@Override
public void invoke(Admin admin, AnalysisContext analysisContext) {
System.out.println("*******555555555**"+ admin+"====>");
adminList.add(admin);
// 数字大小
int size = adminList.size();
if(size>=BATCH_COUNT){
//存储数据到数据库
adminService.saveBatch(adminList);
adminList.clear();
}
}
// 读取完毕操作
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
adminService.saveBatch(adminList);
System.out.println("读取完毕");
}
}
这里面注意一个问题,如果获取到的数据全部是null的情况下, 请注意检查下自己的实体类中:是否有这个注解:@Accessors(chain = true) ,有的话删除掉即可。
-
第二步:编写controller层代码
//上传文件
@PostMapping("/read")
public R readExcel(@RequestParam("file") MultipartFile file) throws IOException {
// EasyExcel.read(multipartFile.getInputStream(),User.class)
//String filePath="D:\\bdqn_log\\filepath\\aa.xlsx";
// EasyExcel.read(filePath,User.class,newExcelListener(adminService)).sheet().doRead();
EasyExcel.read(file.getInputStream(),Admin.class,new
ExcelListener(adminService)).sheet().doRead();
return R.ok().message("数据导入成功!");
}
-
第三步:在前端页面中写代码进行文件导入,我使用的是vue
<el-upload
action="/user/read"
:show-file-list="false"
:on-success="fileSucc"
style="display: inline-block"
>
<el-button type="primary" style="margin-left: 5px;">导入<i class="el-iconbottom"></i></el-button>
</el-upload>
至此第二种方式实现Excel表导入导出就完成啦。