前言
使用注解方式可以在使用上更加的简便，可以直接在属性对应的注解中设置导出到Excel表中的列名；同时，不需要移动实体类中属性的位置，就可以设置属性的在导出excel表中列的顺序，对于不需要导出的属性，直接不加注解就可以了。
快速使用
1.在pom.xml中导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
2.创建新注解
@Target(value = ElementType.FIELD)
@Retention(value = RetentionPolicy.RUNTIME)
public @interface ExcelAnnotation {
public String value();
public int index();
}
3.在实体类中使用注解
@Data
public class Person {
@ExcelAnnotation(value = "第一列：序号",index = 0)
private int id;
@ExcelAnnotation(value = "第二列：姓名",index = 1)
private String name;
private int age;
@ExcelAnnotation(value = "第四列：地址",index = 3)
private String address;
@ExcelAnnotation(value = "第三列：工资",index = 2)
private double salary;
public Person(int id, String name, int age, String address, double salary) {
this.id = id;
this.name = name;
this.age = age;
this.address = address;
this.salary = salary;
}
}
4.新建一个ExcelUtil工具类，复制以下的代码
package com.rui.hellospringboot.utils;
import com.rui.hellospringboot.annotation.ExcelAnnotation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.List;
public class ExportExcelUtil3 {
/**
* 导出excel表
* @param response
* @param name
* @throws IOException
*/
public static <T>void exportExel(HttpServletResponse response,List<T> list,Class<T> clazz, String name) {
try {
HSSFWorkbook wb = generateExelFile(list, clazz,name);
//到这里，excel就已经生成了，然后就需要通过流来写出去
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
//将excel写入流
wb.write(byteArrayOutputStream);
//设置文件标题
String outFile = name + ".xls";
//设置返回的文件类型
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//对文件编码
outFile = response.encodeURL(new String(outFile.getBytes("gb2312"), "iso8859-1"));
//使用Servlet实现文件下载的时候，避免浏览器自动打开文件
response.addHeader("Content-Disposition", "attachment;filename=" + outFile);
//设置文件大小
response.setContentLength(byteArrayOutputStream.size());
//创建Cookie并添加到response中
Cookie cookie = new Cookie("fileDownload", "true");
cookie.setPath("/");
response.addCookie(cookie);
//将流写进response输出流中
ServletOutputStream outputstream = response.getOutputStream();
byteArrayOutputStream.writeTo(outputstream);
byteArrayOutputStream.close();
outputstream.flush();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 生成Excel文件
* @param list
* @return
*/
public static <T> HSSFWorkbook generateExelFile(List<T> list,Class<T> clazz,String sheetName) throws IllegalAccessException {
//解析注解
Field[] fields = clazz.getDeclaredFields();
HashMap<Integer, Field> fieldMap = new HashMap<>();
for (Field field : fields) {
boolean annotationPresent = field.isAnnotationPresent(ExcelAnnotation.class);
if (annotationPresent){
ExcelAnnotation excelAnnotation = field.getAnnotation(ExcelAnnotation.class);
fieldMap.put(excelAnnotation.index(),field);
}
}
//生成exel文件
//创建工作薄
HSSFWorkbook wb = new HSSFWorkbook();
//创建工作表
HSSFSheet sheet = wb.createSheet();
sheet.createFreezePane(0, 1, 0, 1);
wb.setSheetName(0, sheetName);
/*//默认宽高
sheet.setDefaultColumnWidth((short) 20);
sheet.setDefaultRowHeight((short) 2000);*/
//创建样式和字体
HSSFCellStyle curStyle = wb.createCellStyle();
HSSFFont curFont = wb.createFont();
Font font = wb.getFontAt((short) 0);
CellStyle style = wb.createCellStyle();
font.setCharSet(HSSFFont.DEFAULT_CHARSET);
//更改默认字体大小
font.setFontHeightInPoints((short) 12);
font.setFontName("宋体");
style.setFont(font);
//创建行列
HSSFRow nRow = sheet.createRow(0);
HSSFCell nCell = nRow.createCell(0);
//设置列的样式（具体实现在后面......）
nCell.setCellStyle(mainTitleStyle(curStyle, curFont));
//控制行号列号
int rowNo = 0;
//设置标题到第一行的列中
nRow = sheet.createRow(rowNo++);
for (int i = 0; i < fieldMap.size(); i++) {
nCell = nRow.createCell(i);
nCell.setCellValue(fieldMap.get(i).getAnnotation(ExcelAnnotation.class).value());
nCell.setCellStyle(textStyle(curStyle, curFont));
}
//创建样式和字体
curStyle = wb.createCellStyle();
curFont = wb.createFont();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
//封装exel 表体
for (int i = 0; i < list.size(); i++) {
T dto = list.get(i);
nRow = sheet.createRow(rowNo++);
//HSSFCell nCell;
int colNo = 0;//控制列号
//第一列
for (int j=0;j<fieldMap.size();j++){
Field field=fieldMap.get(j);
field.setAccessible(true);
nCell = nRow.createCell(colNo++);
nCell.setCellValue(field.get(dto).toString());
nCell.setCellStyle(textStyle(curStyle, curFont));
}
}
return wb;
}
/**
* 表格内容样式
*
* @param curStyle
* @param curFont
* @return
*/
public static HSSFCellStyle textStyle(HSSFCellStyle curStyle, HSSFFont curFont) {
curStyle.setAlignment(CellStyle.ALIGN_CENTER); //水平居中
curStyle.setWrapText(true); // 自动换行
curFont.setFontName("宋体");//字体
curFont.setFontHeightInPoints((short) 10);//字体大小
curStyle.setFont(curFont); // 绑定关系
return curStyle;
}
/**
* 表格标题样式
*
* @param curStyle
* @param curFont
* @return
*/
public static HSSFCellStyle mainTitleStyle(HSSFCellStyle curStyle, HSSFFont curFont) {
curStyle.setAlignment(CellStyle.ALIGN_GENERAL); //水平居中
curFont.setFontHeightInPoints((short) 16);//字体大小
curStyle.setFont(curFont); // 绑定关系
return curStyle;
}
}
5.工具类的使用
访问 /exportExcel 路径就能下载关于Person表的Excel了。
@GetMapping(value = "/exportExcel")
public void exportExcel(HttpServletResponse response) throws IOException {
Person p1 = new Person(01, "zhangsan", 20,"廣州", 5000.3);
Person p2 = new Person(02, "lisi", 20,"杭州", 8000.4);
Person p3 = new Person(03, "wangwu", 20,"上海", 15000.4);
List<Person> personList = Arrays.asList(p1, p2, p3);
ExportExcelUtil3.exportExel(response,personList,Person.class,"注解方式导出Excel");
}
6.效果
