easyExcel实现动态导出需要的字段列

easyExcel实现动态导出需要的字段列

1. 实体概况

package excel;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import java.math.BigDecimal;
import java.util.Date;

@Data
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(18)
public class InstitutionInfoExcel {

	@ExcelProperty("机构名称")
	private String institutionName;

	@ExcelProperty("统一社会信用代码")
	private String creditCode;

	@ExcelProperty("机构状态")
	private String institutionStatus;

	@ExcelProperty("服务内容")
	private String serviceContent;

	@ExcelProperty("机构地址")
	private String institutionAddress;

	@ExcelProperty("机构等级")
	private String institutionLevel;

	@ExcelProperty("高德地图纬度")
	private Double lon;

	@ExcelProperty("高德地图纬度")
	private Double lat;

	@ExcelIgnore
	@DateTimeFormat(pattern = "yyyy-MM-dd")
	@JsonFormat(pattern = "yyyy-MM-dd")
	private Date establishDate;

	@ExcelProperty("成立日期")
	private String establishDateStr;

	@ExcelProperty("法定代表人")
	private String legalPersonName;

	@ExcelProperty("法定代表人身份证号")
	private String legalPersonIdCard;

	@ExcelProperty("法定代表人手机号码")
	private String legalMobile;

	@ExcelProperty("区间价位(元)")
	private String rangePrice;

	@ExcelProperty("月最低收费标准(元)")
	private String monthLowAmt;

	@ExcelProperty("月最高收费标准(元)")
	private String monthHighAmt;

	@ExcelProperty("月最低床位费(元)")
	private String monthBedLowAmt;

	@ExcelProperty("月最高床位费(元)")
	private String monthBedHighAmt;

	@ExcelProperty("月最低护理费(元)")
	private String monthAttendantLowAmt;

	@ExcelProperty("月最高护理费(元)")
	private String monthAttendantHighAmt;

	@ExcelProperty("月最低伙食费(元)")
	private String monthMealLowAmt;

	@ExcelProperty("月最高伙食费(元)")
	private String monthMealHighAmt;

	@ExcelProperty("收费方式")
	private String chargingMethod;

	@ExcelProperty("服务人员总数")
	private String serverNum;

	@ExcelProperty("持证人员数")
	private String premitNum;

	@ExcelProperty("持证养老护理员数")
	private String premitAttendantNum;

	@ExcelProperty("入住率")
	private BigDecimal checkInRate;

	@ExcelProperty("机构床位总数")
	private String bedNum;

	@ExcelProperty("护理床位数")
	private String attendantBedNum;

	@ExcelProperty("普通床位数")
	private String generalBedNum;

	@ExcelProperty("医疗床位数")
	private String medBedNum;

	@ExcelProperty("旅居床位数")
	private String accommodationBedNum;

	@ExcelProperty("建筑面积(m²)")
	private Double buildArea;

	@ExcelProperty("占地面积(m²)")
	private Double coverArea;

	@ExcelProperty("服务设施面积(m²)")
	private Double serviceArea;

	@ExcelProperty("联系人姓名")
	private String contactName;

	@ExcelProperty("联系人电话")
	private String contactPhone;

	@ExcelProperty("医疗机构名称")
	private String medName;

	@ExcelProperty("医疗机构社会信用代码")
	private String medCreditCode;

	@ExcelProperty("入住须知")
	private String checkInNotice;

	@ExcelProperty("联系我们")
	private String linkMethod;

	@ExcelProperty("服务设施")
	private String serviceFacilities;

	@ExcelProperty("位置")
	private String place;

	@ExcelProperty("收住老人类型说明")
	private String liveElderIntroduce;

	@ExcelProperty("政府扶持政策内容")
	private String governmentSupportPolicies;

	@ExcelProperty("最低押金(元)")
	private BigDecimal depositMin;

	@ExcelProperty("最高押金(元)")
	private BigDecimal depositMax;

	@ExcelProperty("其他收费项目")
	private String otherChargingProjectName;

	@ExcelProperty("其他收费项目金额(元)")
	private BigDecimal otherChargingMoney;

	@ExcelProperty("行政区划名称")
	private String areaName;
}

2. 封装的帮助类

package com.sdcc.gsp.util;

import com.alibaba.excel.EasyExcel;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.codec.Charsets;
import org.apache.commons.lang3.StringUtils;
import org.springblade.core.tool.utils.Func;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * describe: 导出Excel 表格
 *
 * @author cgj
 * @date 2023/7/7
 */
@Slf4j
public class OutputExcelUtils {

	/**
	 * 忽略部分导出字段方法
	 * @param response
	 * @param fileName 文件名称
	 * @param sheetName sheet名称
	 * @param dataList 需要导出的数据
	 * @param clazz 类
	 * @param ignoreParam 忽略的字段,多个通过逗号分割拼接(如：String ignoreParam = “institutionName,creditCode,institutionStatus”)
	 * @param <T>
	 * @throws IOException
	 */
	public static <T> void export(HttpServletResponse response, String fileName, String sheetName, List<T> dataList, Class<T> clazz, String ignoreParam) throws IOException {
		response.setContentType("application/vnd.ms-excel");
		response.setCharacterEncoding(Charsets.UTF_8.name());
		fileName = URLEncoder.encode(fileName, Charsets.UTF_8.name());
		response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
		Set<Integer> selectedIndexes = new HashSet<>();
		if (StringUtils.isNotBlank(ignoreParam)) {
			List<String> strList = Func.toStrList(ignoreParam);
			// 反射获取字段属性
			Field[] declaredFields = clazz.getDeclaredFields();
			for (int i = 0; i < declaredFields.length; i++) {
				for (String s : strList) {
					// 与需要忽略的字段匹配
					if (declaredFields[i].getName().equals(s)) {
						// 获取忽略字段的下标
						selectedIndexes.add(i);
					}
				}
			}
		}
		if (selectedIndexes.size() > 0) {
			EasyExcel.write(response.getOutputStream(), clazz).excludeColumnIndexes(selectedIndexes).sheet(sheetName).doWrite(dataList);
		} else {
			EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(dataList);
		}
	}

}

3. 逻辑调用

String ignoreParam = "institutionName,creditCode,institutionStatus";
OutputExcelUtils.export(response, "机构养老组织数据导出" + DateUtil.time(), "机构养老组织数据导出", excelList, InstitutionInfoExcel.class, null);
		

4. 实现效果
   未忽略前导出的样式
   
   忽略后的样式
   
   完成收官