导出excel作为很多页面的常用功能，但是不同页面导出的数据内容和字段不一，不方便操作，本文通过注解和工具类来解决excel通用导出问题
1.注解
通过注解来定义excel字段的名称，排序和日期格式
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelField {
/**
* excel列名
*/
String value() default "";
/**
* 排序
*/
int sort() default 0;
/**
* 日期格式
*/
String dateFormat() default "";
}
2.实体
定义要导出的数据实体，要导出的字段上加上注解
@Data
public class DataVO {
/**
* 设备编号
*/
@ExcelField(value = "设备编号", sort = 1)
private String deviceNo;
/**
* 创建时间
*/
@ExcelField(value = "日期", sort = 2, dateFormat = "yyyy-MM-dd")
private Date createTime;
}
3.导出入口函数
public static void getExcelDate(String title, List list, HttpServletRequest request, HttpServletResponse response) {
//获取导出数据
List> list2 = DataUtil.getModelExcelFields(list);
// 列名
Map map = DataUtil.getModelExcelFieldsToArray(list.get(0).getClass());
String[] column = map.get("array");
String[] columnName = map.get("arrayName");
// 把数据绑定到对应的字段下
List dataList = new ArrayList();
Object[] objs;
for (int i = 0; i < list2.size(); i++) {
objs = new Object[columnName.length];
for (int j = 0; j < columnName.length; j++) {
objs[j] = list2.get(i).get(column[j]);
}
dataList.add(objs);
}
exportExcel(title, columnName, dataList, request, response);
}
4.获取导出数据
实体类中标记了注解的字段才会导出，根据反射获取字段类型和值，其中Date类型的数据转换成String类型时，通过注解的日期格式来格式化日期
public static List> getModelExcelFields(List models) {
List> list = new ArrayList<>();
if (!models.isEmpty()) {
for (T model : models) {
Map map = new HashMap<>(16);
// 获取实体类的所有属性，返回Field数组
Field[] fields = model.getClass().getDeclaredFields();
try {
// 遍历所有属性
for (Field field : fields) {
ExcelField anno = field.getAnnotation(ExcelField.class);
if (anno == null) {
continue;
}
// 获取属性的名字
String fieldName = field.getName();
// 将属性的首字符大写，方便构造get，set方法
String name = fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
// 获取属性的类型
String type = field.getGenericType().toString();
// 如果type是类类型，则前面包含"class "，后面跟类名
if ("class java.lang.String".equals(type)) {
Method m = model.getClass().getMethod("get" + name);
// 调用getter方法获取属性值
String value = (String) m.invoke(model);
if (value != null && (!"".equals(value))) {
map.put(name, value);
}
continue;
}
if ("int".equals(type)) {
Method m = model.getClass().getMethod("get" + name);
int value = (int) m.invoke(model);
map.put(name, value);
continue;
}
if ("double".equals(type)) {
Method m = model.getClass().getMethod("get" + name);
double value = (double) m.invoke(model);
map.put(name, value);
continue;
}
if ("class java.lang.Double".equals(type)) {
Method m = model.getClass().getMethod("get" + name);
Double value = (Double) m.invoke(model);
if (value != null) {
map.put(name, value);
}
continue;
}
if ("class java.math.BigDecimal".equals(type)) {
Method m = model.getClass().getMethod("get" + name);
BigDecimal value = (BigDecimal) m.invoke(model);
if (value != null) {
map.put(name, value);
}
continue;
}
if ("class java.lang.Boolean".equals(type)) {
Method m = model.getClass().getMethod("get" + name);
Boolean value = (Boolean) m.invoke(model);
if (value != null) {
map.put(name, value);
}
continue;
}
if ("long".equals(type) && !("SerialVersionUID".equals(name))) {
Method m = model.getClass().getMethod("get" + name);
long value = (long) m.invoke(model);
if (value != 0) {
map.put(name, value);
}
continue;
}
if ("class java.lang.Long".equals(type)) {
Method m = model.getClass().getMethod("get" + name);
Long value = (Long) m.invoke(model);
if (value != null) {
map.put(name, value);
}
continue;
}
if ("class java.util.Date".equals(type)) {
Method m = model.getClass().getMethod("get" + name);
Date value = (Date) m.invoke(model);
String date = null;
if (value != null) {
try {
if (StringUtil.isNotEmpty(anno.dateFormat())) {
date = DateTools.formatDate(value, anno.dateFormat());
} else {
date = DateTools.formatDate(value, DateTools.DATE_PATTERN_DEFAULT);
}
} catch (ParseException e) {
e.printStackTrace();
}
map.put(name, date);
}
continue;
}
if ("class java.lang.Integer".equals(type)) {
Method m = model.getClass().getMethod("get" + name);
Integer value = (Integer) m.invoke(model);
if (value != null) {
map.put(name, value);
}
continue;
}
if ("class java.lang.Short".equals(type)) {
Method m = model.getClass().getMethod("get" + name);
Short value = (Short) m.invoke(model);
if (value != null) {
map.put(name, value);
}
}
}
} catch (NoSuchMethodException | SecurityException | IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
e.printStackTrace();
}
list.add(map);
}
}
return list;
}
View Code
5.获取列名
实体类中标记了注解的字段才会导出，根据注解的sort进行列的排序
public static Map getModelExcelFieldsToArray(Class clazz) {
Map map = new HashMap<>(16);
// 获取实体类的所有属性，返回Field数组
Field[] fields = clazz.getDeclaredFields();
List list = new ArrayList<>();
List listName = new ArrayList<>();
// 遍历所有属性
Arrays.stream(fields).filter(field -> {
ExcelField anno = field.getAnnotation(ExcelField.class);
return anno != null;
}).sorted(Comparator.comparing(field -> {
ExcelField anno = field.getAnnotation(ExcelField.class);
return anno.sort();
})).forEach(field -> {
ExcelField anno = field.getAnnotation(ExcelField.class);
// 获取属性的名字
String fieldName = field.getName();
// 将属性的首字符大写，方便构造get，set方法
String name = fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
list.add(name);
listName.add(anno.value());
});
String[] array = new String[list.size()];
String[] arrayName = new String[list.size()];
for (int i = 0; i < list.size(); i++) {
array[i] = list.get(i);
arrayName[i] = listName.get(i);
}
map.put("array", array);
map.put("arrayName", arrayName);
return map;
}
6.生成excel文件导出
public static void exportExcel(String title, String[] columnName, List dataList, HttpServletRequest request,
HttpServletResponse response) {
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
// 实例化工具类
ExportExcel ex = new ExportExcel(title, columnName, dataList, request, response);
try {
// 导出excel
ex.export(null);
} catch (Exception e) {
e.printStackTrace();
}
}
public void export(String path) throws Exception {
try {
HSSFWorkbook workbook = getWorkbook();
String fileName = title + "_" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
String headStr = "attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"";
if (StringUtil.isNull(path)) {
try {
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", headStr);
OutputStream out1 = response.getOutputStream();
workbook.write(out1);
} catch (IOException e) {
e.printStackTrace();
}
} else {
//判断是否存在目录. 不存在则创建
if (!FileUtil.exists(path)) {
FileUtil.createFolder(path);
}
//输出Excel文件
FileOutputStream out1 = new FileOutputStream(path + File.separator+ fileName);
workbook.write(out1);
out1.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
private HSSFWorkbook getWorkbook() {
// 创建工作簿对象
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建工作表
HSSFSheet sheet = workbook.createSheet(title);
// 产生表格标题行
HSSFRow rowm = sheet.createRow(0);
HSSFCell cellTiltle = rowm.createCell(0);
//设置标题和单元格样式
//获取列头样式对象
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
//单元格样式对象
HSSFCellStyle style = this.getStyle(workbook);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (columnName.length)));
cellTiltle.setCellStyle(columnTopStyle);
cellTiltle.setCellValue(title);
// 定义所需列数
int columnNum = columnName.length + 1;
// 在索引2的位置创建行(最顶端的行开始的第二行)
HSSFRow rowRowName = sheet.createRow(2);
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
if (n == 0) {
//创建列头对应个数的单元格
HSSFCell cellRowName = rowRowName.createCell(n);
//设置列头单元格的数据类型
cellRowName.setCellType(CellType.STRING);
//设置列头单元格的值
cellRowName.setCellValue("序号");
//设置列头单元格样式
cellRowName.setCellStyle(columnTopStyle);
} else {
//创建列头对应个数的单元格
HSSFCell cellRowName = rowRowName.createCell(n);
//设置列头单元格的数据类型
cellRowName.setCellType(CellType.STRING);
HSSFRichTextString text = new HSSFRichTextString(columnName[n - 1]);
//设置列头单元格的值
cellRowName.setCellValue(text);
//设置列头单元格样式
cellRowName.setCellStyle(columnTopStyle);
}
}
//将查询出的数据设置到sheet对应的单元格中
for (int i = 0; i < dataList.size(); i++) {
//遍历每个对象
Object[] obj = dataList.get(i);
//创建所需的行数
HSSFRow row = sheet.createRow(i + 3);
for (int j = 0; j < obj.length + 1; j++) {
//设置单元格的数据类型
HSSFCell cell;
if (j == 0) {
cell = row.createCell(j, CellType.NUMERIC);
cell.setCellValue(i + 1);
} else {
//其余列都为字符串类型并设置单元格的值
cell = row.createCell(j, CellType.STRING);
if (!"".equals(obj[j - 1]) && obj[j - 1] != null) {
cell.setCellValue(obj[j - 1].toString());
} else {
cell.setCellValue(" ");
}
}
//设置单元格样式
cell.setCellStyle(style);
}
}
//让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
//取得当前的单元格
HSSFCell currentCell = currentRow.getCell(colNum);
//如果当前单元格类型为字符串
if (currentCell.getCellType() == CellType.STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
//将单元格里面值大小作为列宽度
columnWidth = length;
}
}
}
}
//再根据不同列单独做下处理
if (colNum == 0) {
sheet.setColumnWidth(colNum, (columnWidth - 2) * 200);
} else {
if (columnWidth < 252) {
sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
} else {
sheet.setColumnWidth(colNum, 6000);
}
}
}
return workbook;
}
View Code
至此，导出完成。
Original: https://www.cnblogs.com/cott/p/14331650.html
Author: Cott
Title: poi导出excel工具类+注解
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/714023/
转载文章受原作者版权保护。转载请注明原作者出处！