excel导出注解实现
1.所涉及到的包
<dependency> <groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
2.自定义注解
@Retention(RetentionPolicy.RUNTIME)//注解保留到运行时@Target(ElementType.FIELD)//注解应该出现的位置，即标注在字段上
public @interface ExcelField {
/**
* 列名
*/
String name();
/**
* 列名
*/
boolean isMark() default false;
/**
* 日期格式
*/
String dateFormat() default "yyyy-MM-dd HH:mm:ss";
/**
* 导出列的顺序，从0开始自增单位1，不指定默认按照字段顺序导出
*/
String sort() default "";
/**
* 是否为超链接
*/
boolean isLink() default false;
}
3.封装excel导出工具类
public class ExcelUtils implements Serializable { private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
private static final String tail = ".xlsx";
/**
* 默认每个sheet最大记录数
*/
private static int sheetSize = 3000;
/**
* 导出对应的实体类对象
*/
private Class<T> classObj;
public ExcelUtils() {}
public ExcelUtils(Class<T> classObj) {
this.classObj = classObj;
}
public ExcelUtils(Class<T> classObj, int sheetSize) {
this(classObj);
this.sheetSize = sheetSize;
}
/**
* 导出
*
* @param
* @return
*/
public void export(List<T> list, String sheetName, HttpServletResponse response) {
if (CollectionUtils.isEmpty(list)) {
list = Lists.newArrayList();
}
String excelName = sheetName + tail;
FileOutputStream fos = null;
FileInputStream fis = null;
//创建一个workbook
SXSSFWorkbook workbook = new SXSSFWorkbook();
try {
//设置响应头
response.setHeader("Content-Disposition", "attachment; filename="" + new String(excelName.getBytes("gbk"), "iso-8859-1") + """);
OutputStream outputStream = response.getOutputStream();
//找出需要导出的字段
Field[] fields = classObj.getDeclaredFields();
List<Field> excelFieldList = Lists.newArrayList();
for (Field field : fields) {
//包含了导出注解的放入需导出字段列表
if (field.isAnnotationPresent(ExcelField.class)) {
excelFieldList.add(field);
}
}
//计算sheetCount
int sheetCount = list.size() % sheetSize !=0 ? list.size() / sheetSize + 1 : list.size() / sheetSize;
//创建字体，样式
Font font = workbook.createFont();
CellStyle style = workbooke.createCellStyle();
//分sheet页导出
for (int i = 0; i < sheetCount; i++) {
//创建工作表
SXSSFSheet sheet = workbook.createSheet(sheetName + i);
//创建表头，始终在第一行
SXSSFRow row = sheet.createRow(0);
//列
SXSSFCell cell;
//创建填充表头
for (int j = 0; j < excelFieldList.size(); j++) {
ExcelField excelField = excelFieldList.get(j).getAnnotation(ExcelField.class);
//根据值定的顺序在响应的下标创建列
if (StringUtils.isNotBlank(excelField.sort())) {
cell = row.createCell(Integer.valueOf(excelField.sort()));
} else {
cell = row.createCell(j);
}
cell.setCellType(CellType.STRING);
cell.setCellValue(excelField.name());
if (excelField.isMark()) {
font.setColor(Font.COLOR_RED);
style.setFont(font);
cell.setStyle(style);
}
}
//确认每页sheet开始和结束的记录下标
int startNo = i * sheetSize;
int endNo = Math.min(startNo + sheetSize, list.size());
//开始填充记录内容
for (int j = startNo; j < endNo; j++) {
//保证
row = sheet.createRow(j + 1 - startNo);
//取出一条数据
T t = list.get(j);
for (int k = 0; k < excelFieldList; k++) {
Field field = excelFieldList.get(k);
field.setAccessible(true);
ExcelField excelField = field.getAnnotation(ExcelField.class);
if (StringUtils.isNotBlank(excelField.sort())) {
cell = row.createCell(Integer.valueOf(excelField.sort()));
} else {
cell = row.createCell(j);
}
//转换日期类型
if (field.get(t) !=null && field.getType().isAssignableForm(Date.class)) {
cell.setCellValue(DateFormatUtils.format((Date)field.get(t), excelField.dateFormat()));
} else {
cell.setCellValue(String.valueOf(field.get(t)));
}
//标红
if (StringUtils.isNotBlank(excelField.sort())) {
cell = row.createCell(Integer.valueOf(excelField.sort()));
} else {
cell = row.createCell(j);
}
//超链接处理
if (excelField.isLink()) {
cell.setCellFormula("HYPERLINK("" + field.get(t) + "")");
}
}
}
}
//文件备份
String fileName = sheetName + "_" + DateFormatUtils.format(new Date(), "yyyyMMddHHmmss") + tail;
String filePath = EnvUtils.excelPath;//获取配置文件中制定的目录
File fileDir = new File(filePath);
if (!fileDir.exists()) {
fileDir.mkdirs();
}
File file = new File(filePath + fileName);
if (!file.exists()) {
file.createNewFile();
}
//写出文件流到指定目录
fos = new FileOutputStream(file);
workbook.write(fos);
//从指定目录读入文件流写出到响应输出流（此时浏览器自动下载）
fis = new FileIntputStream(file);
StreamUtils.copy(fis, outputStream);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
if (fos !=null ) {
fos.close();
}
workbook.close();
if (fis != null ) {
fis.close();
}
} catch (IOException e) {
logger.error("关闭流异常" + e);
}
}
}
}
4.使用
4.1.导出实体类
@Datapublic class TestExcelEntity {
@ExcelField(name = "id", sort = "0")
private Long id;
@ExcelField(name = "创建时间", sort = "3")
private Date createdAt;
@ExcelField(name = "产品名称", sort = "1", isMark = true)
private String appName;
@ExcelField(name = "反馈截图", sort = "2", isLink = true)
private String imgUrl;
}
4.2.导出
/** * 导出
*
* @param params 查询参数
* @return
*/
@GetMapping("/export")
public void exportList(ExportParams params, HttpServletResponse response) {
List<TestExcelEntity> list = selectList(params);
//每个sheet页1000条记录
ExcelUtils excelUtils = new ExcelUtils(TestExcelEntity.class, 1000);
excelUtils.export(list, "测试导出", response);
}
4.3.结果示例
以上是 excel导出注解实现 的全部内容， 来源链接： utcz.com/z/515086.html
