/**
* 报警消息列表查询导出
*
*/
@GetMapping("/AlarmMessageListExport")
public void getAlarmMessageListExport(@ModelAttribute AlarmMessageListParam alarmMessageListParam, HttpServletResponse response) throws IOException, NoSuchFieldException, IllegalAccessException {
//查询出需要导出的list
List<Object> alarmMessageListDerive = queryAndStatisticsService.getAlarmMessageListDerive(alarmMessageListParam);
//当前时间字符串 年月日时分秒
LocalDateTime now = LocalDateTime.now();
String formattedDateTime = now.format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"));
//数据异常查询---拼接文件名---也是表格第一行标题
String alarmMessageTitleValue= TitleEnum.ALARM_MESSAGE_TITLE.getValue();
//表格文件名
String title = formattedDateTime + "_" + alarmMessageTitleValue;
// 获取DataExceptionDerive类中所有属性
Field[] fields = AlarmMessageListDerive.class.getDeclaredFields();
DeriveUtil.exportExcel(response, alarmMessageListDerive, fields, title, alarmMessageTitleValue);
}
自定义导出模板所用的注解 :
package com.jl.coldstorecloud.common;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelColumn {
String fieldName();
String header()default "";
public class ExcelColumnHeader {
private String header;
private String fieldName;
public ExcelColumnHeader(String header, String fieldName) {
this.header = header;
this.fieldName = fieldName;
}
public String getHeader() {
return header;
}
public String getFieldName() {
return fieldName;
}
}
}
导出模板:
package com.jl.coldstorecloud.enums;
import com.jl.coldstorecloud.common.ExcelColumn;
import lombok.Data;
@Data
public class DataExceptionDerive {
@ExcelColumn(header = "发生时间", fieldName = "ftime")
private String ftime;
@ExcelColumn(header = "DTU编号", fieldName = "dtuserial")
private String dtuserial;
@ExcelColumn(header = "IMEI", fieldName = "dtuimei")
private String dtuimei;
@ExcelColumn(header = "DTUID", fieldName = "dtuid")
private String dtuid;
@ExcelColumn(header = "模板ID", fieldName = "templateid")
private String templateid;
@ExcelColumn(header = "类型", fieldName = "datatype")
private String datatype;
@ExcelColumn(header = "地址", fieldName = "dataaddress")
private String dataaddress;
@ExcelColumn(header = "名称", fieldName = "beizhu")
private String beizhu;
@ExcelColumn(header = "数量", fieldName = "datacount")
private String datacount;
@ExcelColumn(header = "unitcounter", fieldName = "unitcounter")
private String unitcounter;
@ExcelColumn(header = "unitlist", fieldName = "unitlist")
private String unitlist;
@ExcelColumn(header = "数据", fieldName = "downdata")
private String downdata;
}
导出功能util
package com.jl.coldstorecloud.utils;
import com.jl.coldstorecloud.common.ExcelColumn;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
/**
* @Auther:
* @Date: create in 2023/4/18 11:40
* @Description: 导出util
* @modified By:
* @version: v1.0
*/
public class DeriveUtil {
/**
* 将数据导出为Excel文件并写入HttpServletResponse输出流中
*
* @param response HttpServletResponse对象
* @param dataList 需要导出的数据列表
* @param fields Excel表格的列名列表
* @param title Excel文件的标题
* @param cellValue 单元格的样式
* @throws IOException
*/
public static void exportExcel(HttpServletResponse response, List<Object> dataList, Field[] fields, String title, String cellValue) throws IOException {
// 创建工作簿
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
// 创建字体对象并设置为Arial
Font font = workbook.createFont();
font.setFontName("Arial");//字体
// 创建单元格样式并设置字体为Arial
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
// 设置标题样式,字体大小为20
Font titleFont = workbook.createFont();
titleFont.setFontName(font.getFontName());
titleFont.setFontHeightInPoints((short) 20);
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
//存储Excel表格的列信息
List<ExcelColumn.ExcelColumnHeader> excelColumns = new ArrayList<>();
for (Field field : fields) { // 遍历每一个属性
if (field.isAnnotationPresent(ExcelColumn.class)) { // 如果属性被@ExcelColumn注解标记
ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class); // 获取ExcelColumn注解
String header = excelColumn.header(); // 获取注解中的表头名称
String fieldName = excelColumn.fieldName();// 获取属性名
ExcelColumn.ExcelColumnHeader columnHeader = new ExcelColumn.ExcelColumnHeader(header, fieldName); // 创建ExcelColumnHeader对象
excelColumns.add(columnHeader); // 将ExcelColumnHeader对象添加到集合中
}
}
// 写入标题并合并单元格
Row titleRow = sheet.createRow(0);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue(cellValue);
titleCell.setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, excelColumns.size() - 1));
// 写入表头
Row headerRow = sheet.createRow(1);
for (int i = 0; i < excelColumns.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(excelColumns.get(i).getHeader());
}
// 写入数据
int rowIndex = 2; // 定义行索引从第二行开始写入数据
for (Object data : dataList) { // 遍历数据列表
Row row = sheet.createRow(rowIndex++); // 创建行对象
for (int i = 0; i < excelColumns.size(); i++) { // 遍历Excel列头列表
ExcelColumn.ExcelColumnHeader columnHeader = excelColumns.get(i); // 获取Excel列头对象
String fieldName = columnHeader.getFieldName(); // 获取列头对应的属性名
try {
Field field = data.getClass().getDeclaredField(fieldName); // 获取对象对应的属性
field.setAccessible(true); // 设置属性可访问
Object fieldValue = field.get(data); // 获取属性值
// 在写入数据时,为了避免空指针异常,需要先判断属性值是否为null
Cell cell = row.createCell(i);
if (fieldValue != null) {
cell.setCellValue(fieldValue.toString()); // 将属性值转换为字符串写入单元格
}
cell.setCellStyle(cellStyle); // 设置单元格样式
// 在写入数据时,为了保证数据格式的一致性,可以根据属性类型转换为字符串后再写入单元格
if (fieldValue != null) {
if (field.getType() == int.class || field.getType() == Integer.class) {
row.createCell(i).setCellValue((int) fieldValue); // 将int类型的属性值直接写入单元格
} else if (field.getType() == long.class || field.getType() == Long.class) {
row.createCell(i).setCellValue((long) fieldValue); // 将long类型的属性值直接写入单元格
} else if (field.getType() == double.class || field.getType() == Double.class) {
row.createCell(i).setCellValue((double) fieldValue); // 将double类型的属性值直接写入单元格
} else if (field.getType() == float.class || field.getType() == Float.class) {
row.createCell(i).setCellValue((float) fieldValue); // 将float类型的属性值直接写入单元格
} else {
row.createCell(i).setCellValue(String.valueOf(fieldValue)); // 将其他类型的属性值转换为字符串后再写入单元格
}
}
} catch (NoSuchFieldException | IllegalAccessException e) {
e.printStackTrace(); // 捕获异常并打印堆栈信息
}
}
}
// 设置列宽度为自适应
for (int i = 0; i < excelColumns.size(); i++) {
sheet.autoSizeColumn(i);
}
// 输出Excel文件到输出流
//指定文件名
String fileName = URLEncoder.encode(title, "UTF-8") + ".xlsx";
//设置响应类型为Excel文件
response.setContentType("application/octet-stream");
//设置响应头
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
// 设置响应类型为Excel文件
// 文本文件:text/plain
// HTML文件:text/html
// CSS文件:text/css
// JavaScript文件:application/javascript
// JSON文件:application/json
// XML文件:application/xml
// 图片文件:image/jpeg, image/png, image/gif, 等等
// PDF文件:application/pdf
// Excel文件:application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
// Word文件:application/msword, application/vnd.openxmlformats-officedocument.wordprocessingml.document
// PowerPoint文件:application/vnd.ms-powerpoint, application/vnd.openxmlformats-officedocument.presentationml.presentation
// response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// 输出Excel文件到客户端浏览器
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
}