/**
* @author Damon.liu
* @description excel工具类
* @date 2021/12/6 13:46
*/
@Slf4j
@SuppressWarnings("DuplicatedCode")
public class ExcelUtils {
private ExcelUtils() {
}
/**
* 日期格式
*/
public static final String DATE_PATTERN = "yyyyMMddHHmmss";
public static final String SPLIT_DATE_PATTERN = "yyyy-MM-dd HH:mm:ss";
public static final String EXPORT_SPLIT_DATE_PATTERN = "yyyy-MM-dd HH-mm-ss";
/**
* 文件后缀
*/
public static final String SUFFIX = ".xls";
public static final String SUFFIX_XLSX = ".xlsx";
/**
* contentType
*/
private static final String CONTENT_TYPE = "application/vnd.ms-excel";
/**
* 导出Excel
*
* @param excelName 要导出的excel名称
* @param list 要导出的数据集合
* @param fieldMap 中英文字段对应Map,即要导出的excel表头
* @param response 使用response可以导出到浏览器
* @param <T> -
*/
public static <T> void exportExcel(String excelName, List<T> list, Map<String, String> fieldMap, HttpServletResponse response) {
excelName += new SimpleDateFormat(EXPORT_SPLIT_DATE_PATTERN).format(new Date());
response.setContentType(CONTENT_TYPE);
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
try {
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename="
+ URLEncoder.encode(excelName, StandardCharsets.UTF_8.name())
.replaceAll("\\+", "%20") + SUFFIX);
response.setHeader(HttpHeaders.ACCESS_CONTROL_EXPOSE_HEADERS, HttpHeaders.CONTENT_DISPOSITION);
} catch (UnsupportedEncodingException e1) {
log.error("导出Excel编码转换异常:{}", e1.getMessage());
}
try (OutputStream outputStream = response.getOutputStream(); HSSFWorkbook wb = new HSSFWorkbook()) {
HSSFSheet sheet = wb.createSheet(excelName);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
HSSFFont font = wb.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 12);
style.setFont(font);
fillSheet(sheet, list, fieldMap, style);
wb.write(outputStream);
outputStream.flush();
} catch (Exception e) {
log.error("导出Excel失败!:{}", e.getMessage());
}
}
/**
* 导出Excel
*
* @param filePath 导出文件路径
* @param excelName 要导出的excel名称
* @param list 要导出的数据集合
* @param fieldMap 中英文字段对应Map,即要导出的excel表头
* @param <T> -
*/
public static <T> void exportExcel(String filePath, String excelName, List<T> list, Map<String, String> fieldMap) {
excelName += new SimpleDateFormat(DATE_PATTERN).format(new Date());
File file = null;
try {
file = new File(filePath + "/" + URLEncoder.encode(excelName, StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20")
+ SUFFIX);
} catch (UnsupportedEncodingException e) {
log.error("导出Excel失败!:{}", e.getMessage());
}
if (file == null) {
return;
}
if (!file.exists()) {
file.getParentFile().mkdirs();
}
try (OutputStream outputStream = new FileOutputStream(file); HSSFWorkbook wb = new HSSFWorkbook()) {
HSSFSheet sheet = wb.createSheet(excelName);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
HSSFFont font = wb.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 12);
style.setFont(font);
fillSheet(sheet, list, fieldMap, style);
wb.write(outputStream);
outputStream.flush();
} catch (Exception e) {
log.error("导出Excel失败!:{}", e.getMessage());
}
}
/**
* 导出Excel
*
* @param excelName 要导出的excel名称
* @param formatPattern 日期格式化字符串
* @param list 要导出的数据集合
* @param fieldMap 中英文字段对应Map,即要导出的excel表头
* @param response 使用response可以导出到浏览器
* @param <T> -
*/
public static <T> void exportExcel(String excelName, String formatPattern, List<T> list, Map<String, String> fieldMap,
HttpServletResponse response, String suffix) {
excelName += new SimpleDateFormat(formatPattern).format(new Date());
response.setContentType(CONTENT_TYPE);
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
try {
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename="
+ URLEncoder.encode(excelName, StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20") + suffix);
response.setHeader(HttpHeaders.ACCESS_CONTROL_EXPOSE_HEADERS, HttpHeaders.CONTENT_DISPOSITION);
} catch (UnsupportedEncodingException e1) {
log.error("导出Excel编码转换异常:{}", e1.getMessage());
}
try (OutputStream outputStream = response.getOutputStream(); HSSFWorkbook wb = new HSSFWorkbook()) {
HSSFSheet sheet = wb.createSheet(excelName);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
HSSFFont font = wb.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 12);
style.setFont(font);
fillSheet(sheet, list, fieldMap, style);
wb.write(outputStream);
outputStream.flush();
} catch (Exception e) {
log.error("导出Excel失败!:{}", e.getMessage());
}
}
/**
* 根据字段名获取字段对象
*
* @param fieldName 字段名
* @param cla 包含该字段的类
* @return 字段
*/
public static Field getFieldByName(String fieldName, Class<?> cla) {
Field[] selfFields = cla.getDeclaredFields();
// 如果本类中存在该字段,则返回
for (Field field : selfFields) {
//如果本类中存在该字段,则返回
if (field.getName().equals(fieldName)) {
return field;
}
}
// 查看父类中是否存在此字段,如果有则返回
Class<?> superClazz = cla.getSuperclass();
if (superClazz != null && superClazz != Object.class) {
//递归
return getFieldByName(fieldName, superClazz);
}
// 如果本类和父类都没有,则返回空
return null;
}
/**
* 根据字段名获取字段值
*
* @param fieldName 字段名
* @param obj 对象
* @return 字段值
* @throws Exception 异常
*/
public static Object getFieldValueByName(String fieldName, Object obj)
throws Exception {
Object value;
//根据字段名得到字段对象
Field field = getFieldByName(fieldName, obj.getClass());
if (field != null) {
//类中的成员变量为private,在类外边使用属性值,故必须进行此操作
field.setAccessible(true);
//获取当前对象中当前Field的value
value = field.get(obj);
if (Date.class.isAssignableFrom(field.getType())) {
Date time = (Date) value;
value = DateUtil.format(time, DatePattern.NORM_DATETIME_FORMAT);
}
} else {
throw new Exception(obj.getClass().getSimpleName() + "类不存在字段名 "
+ fieldName);
}
return value;
}
/**
* 根据带路径或不带路径的属性名获取属性值,即接受简单属性名,
* 如userName等,又接受带路径的属性名,如student.department.name等
*
* @param fieldNameSequence 带路径的属性名或简单属性名
* @param obj 对象
* @return 属性值
* @throws Exception 异常
*/
public static Object getFieldValueByNameSequence(String fieldNameSequence,
Object obj) throws Exception {
Object value;
// 将fieldNameSequence进行拆分
String[] attributes = fieldNameSequence.split("\\.");
if (attributes.length == 1) {
value = getFieldValueByName(fieldNameSequence, obj);
} else {
// 根据数组中第一个连接属性名获取连接属性对象,如student.department.name
Object fieldObj = getFieldValueByName(attributes[0], obj);
//截取除第一个属性名之后的路径
String subFieldNameSequence = fieldNameSequence
.substring(fieldNameSequence.indexOf(".") + 1);
//递归得到最终的属性对象的值
value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
}
return value;
}
/**
* 向工作表中填充数据
*
* @param sheet excel的工作表名称
* @param list 数据源
* @param fieldMap 中英文字段对应关系的Map
* @param style 表格中的格式
* @throws Exception 异常
*/
public static <T> void fillSheet(HSSFSheet sheet, List<T> list,
Map<String, String> fieldMap, HSSFCellStyle style) throws Exception {
// 定义存放英文字段名和中文字段名的数组
String[] enFields = new String[fieldMap.size()];
String[] cnFields = new String[fieldMap.size()];
// 填充数组
int count = 0;
for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
enFields[count] = entry.getKey();
cnFields[count] = entry.getValue();
count++;
}
HSSFRow row = sheet.createRow(0);
row.setHeightInPoints((short) 20);
// 填充表头
for (int i = 0; i < cnFields.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(cnFields[i]);
cell.setCellStyle(style);
sheet.autoSizeColumn(i);
}
// 填充内容
for (int index = 0; index < list.size(); index++) {
row = sheet.createRow(index + 1);
row.setHeightInPoints((short) 18);
// 获取单个对象
T item = list.get(index);
for (int i = 0; i < enFields.length; i++) {
Object objValue = getFieldValueByNameSequence(enFields[i], item);
String fieldValue = objValue == null ? "" : objValue.toString();
row.createCell(i).setCellValue(fieldValue);
}
}
}
/**
* 导出报表
*
* @param <T> -
* @param excelName 要导出的excel名称
* @param list 要导出的数据集合
* @param fieldList 属性名称集合
* @param firstRow 第一行表头
* @param secondRow 第二行表头
* @param response 使用response可以导出到浏览器
*/
public static <T> void exportReportFormExcel(String excelName, List<T> list, LinkedList<String> fieldList, String[] firstRow,
String[] secondRow, HttpServletResponse response) {
excelName += new SimpleDateFormat(EXPORT_SPLIT_DATE_PATTERN).format(new Date());
response.setContentType(CONTENT_TYPE);
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
try {
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename="
+ URLEncoder.encode(excelName, StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20") + SUFFIX);
response.setHeader(HttpHeaders.ACCESS_CONTROL_EXPOSE_HEADERS, HttpHeaders.CONTENT_DISPOSITION);
} catch (UnsupportedEncodingException e1) {
log.error("导出Excel编码转换异常:{}", e1.getMessage());
}
try (OutputStream outputStream = response.getOutputStream(); HSSFWorkbook wb = new HSSFWorkbook()) {
HSSFSheet sheet = wb.createSheet(excelName);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont font = wb.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 12);
style.setFont(font);
fillSheet(sheet, list, firstRow, secondRow, fieldList, style);
wb.write(outputStream);
outputStream.flush();
} catch (Exception e) {
log.error("导出Excel失败!:{}", e.getMessage());
}
}
/**
* 向工作表中填充数据
*
* @param sheet excel的工作表名称
* @param list 数据源
* @param firstRow 第一行表头
* @param secondRow 第二行表头
* @param fieldList 属性名称集合
* @param style 表格中的格式
* @throws Exception 异常
*/
public static <T> void fillSheet(HSSFSheet sheet, List<T> list, String[] firstRow, String[] secondRow,
LinkedList<String> fieldList, HSSFCellStyle style) throws Exception {
// 定义存放英文字段名和中文字段名的数组
String[] enFields = new String[fieldList.size()];
for (int i = 0; i < fieldList.size(); i++) {
enFields[i] = fieldList.get(i);
}
// 设置前两行表头
HSSFRow row = sheet.createRow(0);
row.setHeightInPoints((short) 20);
for (int i = 0; i < firstRow.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(firstRow[i]);
cell.setCellStyle(style);
sheet.autoSizeColumn(i);
}
row = sheet.createRow(1);
row.setHeightInPoints((short) 20);
for (int i = 0; i < secondRow.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(secondRow[i]);
cell.setCellStyle(style);
sheet.autoSizeColumn(i);
}
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 5, 6));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 8));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 9, 10));
// 填充内容
for (int index = 0; index < list.size(); index++) {
row = sheet.createRow(index + 2);
row.setHeightInPoints((short) 18);
// 获取单个对象
T item = list.get(index);
for (int i = 0; i < enFields.length; i++) {
Object objValue = getFieldValueByNameSequence(enFields[i], item);
String fieldValue = objValue == null ? "" : objValue.toString();
row.createCell(i).setCellValue(fieldValue);
}
}
}
}
poi导出excel
最新推荐文章于 2022-11-16 17:46:35 发布