说明：本人第一篇文章，希望各位多指教批评，同时希望该文章能够给各位读者带来帮助
运行环境
SpringBoot + maven + jdk1.8
1、配置maven依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
2、自定义注解用于自动标识需要导出的列
/**
* @author goujj
*/
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelResources {
/**
* 标题
*
* @return
*/
String title();
/**
* 排序
*
* @return
*/
int order();
}
3、poi 导出的格式xls、xlsx后缀，本文主要介绍使用HSSFWorkbook导出xls格式excel
import com.scty.pps.pps.aop.ExcelResources;
import com.scty.pps.pps.dto.ExportDataDTO;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import static java.util.stream.Collectors.toList;
/**
* @author goujj
* 导出excel文件
*/
public class ExportExcelUtil {
/**
* 导出数据处理
*
* @param exportDataDTO
* @param out
*/
public static void exportDeal(ExportDataDTO exportDataDTO, OutputStream out, Class<?> cls) throws IOException {
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
try {
if (exportDataDTO.getSheetName() == null) {
exportDataDTO.setSheetName("sheet1");
}
HSSFSheet hssfSheet = hssfWorkbook.createSheet(exportDataDTO.getSheetName());
writeWorkBook(hssfWorkbook, hssfSheet, exportDataDTO, cls);
hssfWorkbook.write(out);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
hssfWorkbook.close();
}
}
/**
* 数据写入到workbook
*/
private static void writeWorkBook(HSSFWorkbook hssfWorkbook, HSSFSheet hssfSheet, ExportDataDTO exportDataDTO, Class<?> cls) {
int rowIndex = 0;
//------统计有多少列需要导出
int count = 0;
Field[] field = cls.getDeclaredFields();
Map<Integer, Object[]> res = new ConcurrentHashMap<>();
List<Map<Integer, Object[]>> collection = new ArrayList<>();
for (Field fie : field) {
if (fie.isAnnotationPresent(ExcelResources.class)) {
ExcelResources resources = fie.getAnnotation(ExcelResources.class);
if (!res.containsKey(resources.order())) {
res.put(resources.order(), new Object[]{resources, fie});
count++;
}
}
}
if (res.size() > 0) {
//设置数据第一行标题并且居中
createTitle(hssfWorkbook, hssfSheet, count, exportDataDTO);
rowIndex++;
//设置导出的每列标题
collection.add(res);
Map<Integer, Object[]> dealSort = new ConcurrentHashMap<>();
//对res根据key的大小排序，dataList为Filed排序之后的结果
res.entrySet().stream().sorted(Map.Entry.<Integer, Object[]>comparingByKey().reversed()).forEach(e -> dealSort.put(e.getKey(), e.getValue()));
List<Object[]> dataList = dealSort.entrySet().stream().map(e -> e.getValue()).collect(toList());
//导出属性列名对应的字段
List<String> heads = new ArrayList<>();
//------获取每列的列名
HSSFRow row = hssfSheet.createRow(rowIndex);
rowIndex++;
int colIndex = 0;
for (Object[] obj : dataList) {
ExcelResources resources = (ExcelResources) obj[0];
HSSFCell cell = row.createCell(colIndex);
cell.setCellValue(resources.title());
HSSFCellStyle hssfCellStyle = hssfWorkbook.createCellStyle();
cell.setCellStyle(hssfCellStyle);
Field file = (Field) obj[1];
heads.add(file.getName());
colIndex++;
}
//-----设置每行的值并根据注解排序
Map<String, Map<Integer, String>> result = new ConcurrentHashMap<>(heads.size());
orderBy(heads, exportDataDTO, result);
for (int i = 0; i < exportDataDTO.getDataList().size(); i++) {
int zdCell = 0;
HSSFRow ro = hssfSheet.createRow(rowIndex);
rowIndex++;
for (String head : heads) {
//写进excel对象
ro.createCell((short) zdCell).setCellValue(result.get(head).get(i));
zdCell++;
}
}
}
}
/**
* 创建第一行数据标题
*
* @param hssfWorkbook
* @param hssfSheet
* @param mergeCount 统计合并列的结束位置
* @param exportDataDTO 获取第一行数据的标题
*/
public static void createTitle(HSSFWorkbook hssfWorkbook, HSSFSheet hssfSheet, int mergeCount, ExportDataDTO exportDataDTO) {
Font font = hssfWorkbook.createFont();
font.setFontName("Arial");
font.setColor(IndexedColors.BLACK.index);
font.setBold(true);
HSSFCellStyle titleStyle = hssfWorkbook.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
setStyle(titleStyle, font);
HSSFRow row = hssfSheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue(exportDataDTO.getTitle());
CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, 0, mergeCount - 1);
hssfSheet.addMergedRegion(rangeAddress);
cell.setCellStyle(titleStyle);
cell.setCellValue(exportDataDTO.getTitle());
}
/**
* 排序
*
* @param
* @param heads 列属性
* @param exportDataDTO
* @param result 排序结果
*/
private static void orderBy(List<String> heads, ExportDataDTO exportDataDTO, Map<String, Map<Integer, String>> result) {
for (String str : heads) {
result.put(str, new HashMap<>());
}
Integer num = 0;
for (Object t : exportDataDTO.getDataList()) {
Field[] fields = t.getClass().getDeclaredFields();
for (Field field : fields) {
String propertyName = field.getName();
if (heads.contains(propertyName)) {
//获取value值
String methodName = "get" + propertyName.substring(0, 1).toUpperCase() + propertyName.substring(1);
Class cl = t.getClass();
try {
Method getMethod = cl.getMethod(methodName,
new Class[]{});
//操控该对象属性的get方法，从而拿到属性值
Object val = getMethod.invoke(t, new Object[]{});
if (val != null) {
//转化成String
result.get(propertyName).put(num, String.valueOf(val));
} else {
result.get(propertyName).put(num, null);
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
num++;
}
}
/**
* 指定单元格的样式
*/
private static void setStyle(HSSFCellStyle titleStyle, Font font) {
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setBorderLeft(BorderStyle.DOTTED);
titleStyle.setBorderTop(BorderStyle.DOTTED);
titleStyle.setBorderRight(BorderStyle.DOTTED);
titleStyle.setBorderBottom(BorderStyle.DOTTED);
titleStyle.setWrapText(true);
titleStyle.setFont(font);
}
}
4、测试方法
public static void main(String[] args) {
try {
ExportDataDTO exportDataDTO = new ExportDataDTO();
exportDataDTO.setSheetName("测试1");
exportDataDTO.setFileName("导出1");
exportDataDTO.setTitle("测试");
List<JiaofeiDTO> jiaofeiDTOS = new ArrayList<>();
for (int i = 0; i < 100; i++) {
JiaofeiDTO jiaofeiDTO = new JiaofeiDTO();
jiaofeiDTO.setStudentId(i + 1L);
jiaofeiDTO.setCostAll(i + 0.1);
jiaofeiDTO.setCostType(0);
jiaofeiDTOS.add(jiaofeiDTO);
}
exportDataDTO.setDataList(jiaofeiDTOS);
FileOutputStream exportXls = new FileOutputStream(new StringBuffer("D://" + exportDataDTO.getFileName() + ".xls").toString());
exportDeal(exportDataDTO, exportXls, JiaofeiDTO.class);
} catch (Exception ex) {
ex.printStackTrace();
}
}
网友评论