//使用
@PostMapping(value = "/exportCustomExcel")
public JsonResult<Boolean> exportCustomExcel(@RequestBody PcmStudentReq req, HttpServletResponse response,) throws ServiceException {
List<StudentModel> resultList = iStudentService.getContractList(req, user);
if (CollectionUtils.isEmpty(resultList)){
throw new ServiceException("列表为空");
}
CustomExcelUtils<StudentModel> util = new CustomExcelUtils<>(StudentModel.class);
util.exportExcel(resultList,req.getFieldsName(),"proceedsContract",response);
return JsonResult.ok(true);
}
//工具类
package com.property.util;
import com.property.proceeds.contract.annotation.CustomExcelColumn;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.http.MediaType;
import javax.servlet.http.HttpServletResponse;
import java.lang.reflect.Field;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
/**
* 自定义导出
* @author jiagx
*/
@Slf4j
public class CustomExcelUtils<T> {
/**
* 工作薄
*/
private Workbook wb;
/**
* 工作表
*/
private Sheet sheet;
/**
* 需要导出的数据
*/
private List<T> exportList;
/**
* 对象的class对象
*/
private Class<T> clazz;
/**
* 被选中需要导出的字段名称
*/
private List<String> checkedFieldsName;
/**
* 被选中需要导出的字段对象
*/
private List<Field> checkedFields;
/**
* 包含需要字典转换的字段对象
*/
private List<Field> fieldsContainDict;
/**
* 对象中的字典值
*/
private Map<String, Map<String, String>> dicts;
private CustomExcelUtils(){
}
public CustomExcelUtils(Class<T> clazz){
this.clazz = clazz;
}
/**
* @param list 导出的集合数据
* @param fieldsName 需要导出的字段集合
* @param sheetName sheet页/文件名称
* @param response response
*/
public void exportExcel(List<T> list, List<String> fieldsName, String sheetName,HttpServletResponse response){
// 初始化数据
init(list, sheetName, fieldsName);
// 转换字典值
try {
convertDict();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
// sheet第一行加入名称数据
createTopRow();
// sheet其他行，添加目标数据
try {
createOtherRow();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
// 导出wb-浏览器下周excel
buildExcelDocument(generateFileName(sheetName),wb,response);
}
/**
* 浏览器下载excel
* @param fileName 文件名称
* @param wb wb
* @param response HttpServletResponse
*/
public void buildExcelDocument(String fileName, Workbook wb,HttpServletResponse response){
try {
response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8"));
response.flushBuffer();
wb.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 添加导出数据
*/
private void createOtherRow() throws IllegalAccessException {
for (int rowNum = 1; rowNum <= exportList.size(); rowNum++) {
Row row = sheet.createRow(rowNum);
T t = exportList.get(rowNum - 1);
for (int colNum = 0; colNum < checkedFields.size(); colNum++) {
Cell cell = row.createCell(colNum);
Field field = checkedFields.get(colNum);
field.setAccessible(true);
// 单元格设置值
addCell(cell, field, t);
}
}
}
/**
* 单元格中添加数据
*
* @param cell 单元格
* @param field 字段
* @param t list中的一条数据
*/
private void addCell(Cell cell, Field field, T t) throws IllegalAccessException {
Class<?> fieldType = field.getType();
if (String.class == fieldType) {
cell.setCellValue((String) field.get(t));
} else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
cell.setCellValue((Integer) field.get(t));
} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
cell.setCellValue((Long) field.get(t));
} else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
cell.setCellValue((Double) field.get(t));
} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
cell.setCellValue((Float) field.get(t));
}else if ((BigDecimal.class == fieldType)) {
if(null!=field.get(t)){
if (field.get(t).toString().equals("0.00000000")||field.get(t).toString().equals("0E-8")){
cell.setCellValue("0");
}else{
cell.setCellValue(getRateStr(field.get(t).toString()));
}
}
}else if (LocalDateTime.class == fieldType) {
String dateFormat = field.getAnnotation(CustomExcelColumn.class).dateFormat();
cell.setCellValue(dateFormat((LocalDateTime) field.get(t), dateFormat));
}
}
/**
* 截取小数点后两位
* @param rateStr
* @return
*/
public String getRateStr(String rateStr) {
if(rateStr.indexOf(".") != -1){
//获取小数点的位置
int num = 0;
num = rateStr.indexOf(".");
//获取小数点后面的数字 是否有两位 不足两位补足两位
String dianAfter = rateStr.substring(0,num+1);
String afterData = rateStr.replace(dianAfter, "");
if(afterData.length() < 2){
afterData = afterData + "0" ;
}
return rateStr.substring(0,num) + "." + afterData.substring(0,2);
}else{
return rateStr;
}
}
/**
* 时间格式转换
* @param date 日期
* @param dateFormat 日期格式
* @return
*/
private String dateFormat(LocalDateTime date, String dateFormat) {
if (dateFormat == null || "".equals(dateFormat)) {
dateFormat = "yyyy-MM-dd HH:mm:ss";
}
if (null==date){
return "";
}
DateTimeFormatter df = DateTimeFormatter.ofPattern(dateFormat);
return df.format(date);
}
/**
* sheet第一行加入名称数据
*/
private void createTopRow() {
Row row = sheet.createRow(0);
Map<String, CellStyle> styles = createStyles(wb);
AtomicInteger aj = new AtomicInteger();
for (int index = 0; index < checkedFields.size(); index++) {
Cell cell = row.createCell(aj.getAndIncrement());
cell.setCellStyle(styles.get("header"));
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
//设置边框
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
cell.setCellValue(checkedFields.get(index).getAnnotation(CustomExcelColumn.class).name());
}
}
/**
* 转换字典值
* 将数据中字典值转化为对应的值(注:字典值应为String格式)
*/
private void convertDict() throws IllegalAccessException {
for (Field field : fieldsContainDict) {
CustomExcelColumn annotation = field.getAnnotation(CustomExcelColumn.class);
String dictKey = annotation.dictKey();
field.setAccessible(true);
for (T t : exportList) {
// 获取字段值
String o = (String) field.get(t);
field.set(t, dicts.get(dictKey).get(o));
}
}
}
/**
* 将数据导出Excel
* @param list 需要导出的数据
* @param sheetName 工作表名称
*/
public void exportExcel(List<T> list, String sheetName,HttpServletResponse response){
exportExcel(list, null, sheetName,response);
}
/**
* 将数据导出Excel
* @param list 需要导出的数据
*/
public void exportExcel(List<T> list,HttpServletResponse response) {
exportExcel(list, null, "sheet",response);
}
/**
* 初始化
*/
public void init(List<T> list ,String sheetName, List<String> fieldsName){
this.checkedFieldsName = fieldsName;
this.exportList = list;
// 初始化导出数据
initExportList();
// 初始化工作薄
initWorkbook();
// 初始化工作表
initSheet(sheetName);
// 初始化checkedFields, fieldsContainDict
initFields();
// 根据注解生成生成字典
generateObjDict();
}
/**
* 初始化导出数据
*/
private void initExportList(){
// 防止导出过程中出现空指针
if(Objects.isNull(this.exportList)) {
this.exportList = new ArrayList<>();
}
}
/**
* 初始化工作簿
*/
private void initWorkbook(){
this.wb = new SXSSFWorkbook();
}
/**
* 初始化工作表
*/
private void initSheet(String sheetName){
this.sheet = wb.createSheet(sheetName);
}
/**
* 初始化checkedFields, fieldsContainDict
* fieldsContainDict含有字典表达式的字段
* checkedFields用户选中的字段
* 1.如果checkedFieldsName没有定义(未自定义导出字段),所有字段全部导出
* 2.如果checkedFieldsName进行了定义,根据定义字段进行导出
*/
private void initFields(){
// 获取对象所有字段对象
Field[] fields = clazz.getDeclaredFields();
// 过滤出checkedFields
this.checkedFields = Arrays.
asList(fields).
stream().
filter(field -> {
if(!Objects.isNull(this.checkedFieldsName)) {
if (field.isAnnotationPresent(CustomExcelColumn.class)) {
return checkedFieldsName.contains(field.getName());
}
} else {
return field.isAnnotationPresent(CustomExcelColumn.class);
}
return false;
}).sorted(Comparator.comparing(field -> {
return checkedFieldsName.indexOf(field.getName());
}))
.collect(Collectors.toList());
// 过滤出fieldsContainDict
for (Field declaredField : clazz.getDeclaredFields()) {
if(declaredField.getAnnotation(CustomExcelColumn.class) != null) {
System.out.println(declaredField.getAnnotation(CustomExcelColumn.class).dictExp());
}
}
this.fieldsContainDict = Arrays
.asList(clazz.getDeclaredFields())
.stream()
.filter(item -> !"".equals(item.getAnnotation(CustomExcelColumn.class) != null? item.getAnnotation(CustomExcelColumn.class).dictExp() : ""))
.collect(Collectors.toList());
}
/**
* 通过扫描字段注解生成字典数据
*/
private void generateObjDict(){
if(fieldsContainDict.size() == 0) {
return;
}
if(dicts == null) {
dicts = new HashMap<>(); // Map<String, List<Map<String, String>>>
}
for (Field field : fieldsContainDict) {
String dictKey = field.getAnnotation(CustomExcelColumn.class).dictKey();
String exps = field.getAnnotation(CustomExcelColumn.class).dictExp();
String[] exp = exps.split(",");
Map<String, String> keyV = new HashMap<>();
dicts.put(dictKey, keyV);
for (String s : exp) {
String[] out = s.split("=");
keyV.put(out[0], out[1]);
}
System.out.println("字典值:"+ dicts);
}
}
/**
* 创建表格样式
*
* @param wb 工作薄对象
* @return 样式列表
*/
private Map<String, CellStyle> createStyles(Workbook wb)
{
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
// 数据格式
CellStyle style = wb.createCellStyle();
style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.ALIGN_CENTER);
Font dataFont = wb.createFont();
dataFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
style.setFont(dataFont);
styles.put("data", style);
// 表头格式
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
cellStyle.setFont(font);
styles.put("header", cellStyle);
return styles;
}
/**
* 生成随机名称,防止文件复写
* @return
*/
private String generateFileName(String sheetName){
return DateUtil.LocalDateTimeStr()+sheetName+".xlsx";
}
}