前言
之前的一篇文章已经介绍了POI导入的工具类，然后有小伙伴私信我说能不能整理一个POI导出的工具类。现在它来了！！！
如果有小伙伴不知道POI导入的工具类，可以看我上篇文章：POI导入工具类
现在我们言归正传，直接开始
工具类
- POI导出工具类
public class DownLoadExcelUtil {
/**
* 导出excel
*
* @param response http响应
* @param list 数据列表 List<E> 实体类列表
* @param nameMap excel表头Map，存放"属性名,中文名"格式字符串，"中文名"为excel标题行， "属性名"为对象字段名
* @param filePath 导出文件全路径
* @param sheetName sheet名称：sheet1
* @param dataPattern 日期格式：yyyy-MM-dd HH:mm:ss
* @throws Exception 另一个程序正在使用此文件，进程无法访问
*/
public static <E> void exportListEntity(HttpServletResponse response, List<E> list, LinkedHashMap<String, String> nameMap,
String filePath, String sheetName, String dataPattern) throws Exception {
//工作簿
Workbook wb;
// 创建模板工作表
String excelEndStr = ".xls";
if (filePath.endsWith(excelEndStr)) {
wb = new HSSFWorkbook();
} else {
//大于1024行时会把之前的行写入硬盘，解决内存溢出
wb = new SXSSFWorkbook(1024);
}
//创建sheet
buildSheet(list, nameMap, sheetName, dataPattern, wb);
//流输出excel
streamOutPutExcel(response, filePath, wb);
}
/**
* 流导出excel
*
* @param response
* @param filePath
* @param wb
* @throws IOException
*/
public static void streamOutPutExcel(HttpServletResponse response, String filePath, Workbook wb) throws IOException {
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
wb.write(byteArrayOutputStream);
byte[] byteArray = byteArrayOutputStream.toByteArray();
ByteArrayInputStream in = new ByteArrayInputStream(byteArray);
DownLoadUtil.downLoad(response, in, filePath);
}
/**
* 构建excel的Sheet页
*
* @param list
* @param nameMap
* @param sheetName
* @param dataPattern
* @param wb
* @param <E>
* @throws IllegalAccessException
*/
public static <E> void buildSheet(List<E> list, LinkedHashMap<String, String> nameMap, String sheetName, String dataPattern, Workbook wb) throws IllegalAccessException {
// 标题数组
String[] headerArr = new String[nameMap.size()];
// 字段名数组
String[] includeArr = new String[nameMap.size()];
// 遍历参数中的map进行赋值
int num = 0;
for (String key: nameMap.keySet()) {
includeArr[num] = nameMap.get(key);
headerArr[num] = key;
num ++;
}
List<String> includeList = Arrays.asList(includeArr);
//字体
Font font = wb.createFont();
font.setBold(true);
//样式
CellStyle styleBold = createBorderedStyle(wb);
styleBold.setFont(font);
styleBold.setVerticalAlignment(VerticalAlignment.CENTER);
//自动换行
styleBold.setWrapText(false);
//样式
CellStyle styleWrap = createBorderedStyle(wb);
styleWrap.setWrapText(false);
styleWrap.setVerticalAlignment(VerticalAlignment.TOP);
//表
Sheet sheet = wb.createSheet(sheetName);
for (int i = 0; i < headerArr.length; i++) {
sheet.autoSizeColumn(i);
}
//行
Row row = sheet.createRow(0);
//单元格
Cell cell;
//写入标题行
for (int i = 0; i < headerArr.length; i++) {
cell = row.createCell(i);
cell.setCellValue(headerArr[i]);
cell.setCellStyle(styleBold);
}
//写入数据
//实体类
E e;
//属性数组
Field[] fields = new Field[]{};
//属性
Field field;
//属性值
Object value;
//yyyy-MM-dd HH:mm:ss
DateFormat dtf = new SimpleDateFormat(dataPattern);
if (CollectionUtils.isEmpty(list)) {
return;
}
if (list.size() > 0 && includeArr.length > 0) {
fields = putFieldsInOrderByExcelHeader(list.get(0),includeArr);
}
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 1);
e = list.get(i);
// 利用反射，获取属性数组
for (int f = 0, c = 0; f < fields.length; f++) {
field = fields[f];
if (field != null && includeList.contains(field.getName())) {
cell = row.createCell(c);
cell.setCellStyle(styleWrap);
//设置些属性是可以访问的
field.setAccessible(true);
//得到此属性的值
value = field.get(e);
//Byte,Short,Int,Long,Float,Double,Boolean,Char, String,Date,BigDecimal,byte[]
//cell.setCellValue: boolean,Calendar,Date,double,RichTextString,String
if (value == null) {
c++;
continue;
} else if (value instanceof Date) {
cell.setCellValue(dtf.format((Date) value));
} else if (value instanceof BigDecimal) {
cell.setCellValue(Double.parseDouble(value.toString()));
} else {
cell.setCellValue(value.toString());
}
c++;
}
}
}
}
/**
* 创建样式
*
* @param wb
* @return
*/
private static CellStyle createBorderedStyle(Workbook wb) {
CellStyle style = wb.createCellStyle();
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
return style;
}
public DownLoadExcelUtil() {
super();
}
@Override
protected void finalize() throws Throwable {
super.finalize();
}
@Override
public String toString() {
return super.toString();
}
@Override
protected Object clone() throws CloneNotSupportedException {
return super.clone();
}
@Override
public int hashCode() {
return super.hashCode();
}
@Override
public boolean equals(Object obj) {
return super.equals(obj);
}
/**
* 通过Excel的表头转化为对象属性数组
*
* @param object 对象
* @param excelHeaderArr 表头数组
* @return 属性数组
*/
private static Field[] putFieldsInOrderByExcelHeader(Object object, String[] excelHeaderArr) {
Field[] fields = new Field[excelHeaderArr.length];
List<Field> fieldsInOrder = new ArrayList<>();
if (object == null) {
return fields;
}
Field[] objectFields = object.getClass().getDeclaredFields();
if (excelHeaderArr.length == 0) {
return objectFields;
}
if (objectFields.length > 0) {
Map<String,Object> fieldMap = new HashMap<>(32);
Arrays.stream(objectFields).forEach(
field -> {
if (field!=null) {
fieldMap.put(field.getName(),field);
}
}
);
if (fieldMap.size() > 0) {
for (String headerPropertyName : excelHeaderArr) {
if (StringUtils.isNotBlank(headerPropertyName) && fieldMap.get(headerPropertyName)!=null) {
fieldsInOrder.add((Field)fieldMap.get(headerPropertyName));
}
}
if (!fieldsInOrder.isEmpty()) {
fieldsInOrder.toArray(fields);
}
}
}
return fields;
}
}
使用
- 首先还是要设置一下实体类
@AllArgsConstructor
@Data
public class UserVo {
@ApiModelProperty(value = "账号", dataType = "String")
private String account;
@ApiModelProperty(value = "密码", dataType = "String")
private String userPassword;
}
- 编写导出接口和导出的逻辑
@GetMapping("/downloadExcelTest")
void downloadExcelTest(HttpServletResponse response, HttpServletRequest request) throws Exception {
loginService.downloadExcelTest(response, request);
}
public void downloadExcelTest(HttpServletResponse response, HttpServletRequest request) throws Exception {
//模拟导出的数据
List<UserVo> list = new ArrayList<>();
list.add(new UserVo("admin","123"));
list.add(new UserVo("xiaoming","456"));
list.add(new UserVo("xiaohong","789"));
String sheetName = "sheet1";
String filePath = "导出Excel测试.xls";
LinkedHashMap<String, String> map = ApiModelPropertyUtils.getApiModelPropertyValueAndFieldName(UserVo.class);
DownLoadExcelUtil.exportListEntity(response, list, map, filePath, sheetName, "yyyy-MM-dd HH:mm:ss");
}
- 查看导出后的效果，如下图：
微信搜索【君耀软件设计】了解更多