DAO层

mapper.xml

<select id="queryQuarterInfo" resultType="com.digiwin.simple.basic.model.pojo.UserInfoDO">
        select <include refid="baseColumn" />
        from user_info
   			<choose>
            <when test="dto.orderByColumn != null">
                ORDER BY ${dto.orderByColumn} ${dto.orderType}
            </when>
            <otherwise>
                ORDER BY cr.question_time DESC
            </otherwise>
        </choose>
    </select>

备注： 在使用动态拼接字段的时候，mybatis里面要用$进行处理，如果是#的话，是字符串，里面包含要查询的字段，就不生效了。但是注意SQL注入风险。

Export

需要根据前端传入的label和key确认需要的字段和顺序，最终用反射设置value值

columnList: [ { label: ‘员工姓名’， key: ‘usercode’} , … ]类似这样的格式

但是自动列宽设置这时候对Data类不起作用

这是需要自定义CustomVerticalCellStyleStrategy类并继承AbstractColumnWidthStyleStrategy

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CustomVerticalCellStyleStrategy extends AbstractColumnWidthStyleStrategy {


    private static final int MAX_COLUMN_WIDTH = 255;

    private final Map<Integer, Map<Integer, Integer>> cache = MapUtils.newHashMapWithExpectedSize(8);

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell,
                                  Head head,
                                  Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (!needSetWidth) {
            return;
        }
        Map<Integer, Integer> maxColumnWidthMap = cache.computeIfAbsent(writeSheetHolder.getSheetNo(), key -> new HashMap<>(16));
        Integer columnWidth = dataLength(cellDataList, cell, isHead);
        if (columnWidth < 0) {
            return;
        }
        if (columnWidth > MAX_COLUMN_WIDTH) {
            columnWidth = MAX_COLUMN_WIDTH;
        }
        Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
        if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
            maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
        }
    }

    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        }
        WriteCellData<?> cellData = cellDataList.get(0);
        CellDataTypeEnum type = cellData.getType();
        if (type == null) {
            return -1;
        }
        switch (type) {
            case STRING:
                return cellData.getStringValue().getBytes().length;
            case BOOLEAN:
                return cellData.getBooleanValue().toString().getBytes().length;
            case NUMBER:
                return cellData.getNumberValue().toString().getBytes().length;
            case DATE:
                return (cellData.getDateValue().toString().getBytes().length)*2;
            default:
                return -1;
        }
    }
}

在这里设置DATE类型的长度，具体看需求要求。

  					//控制 导出字段 未控制顺序
            EasyExcel.write(byteArrayOutputStream, ChatRecordDTO.class)
                    .head(EasyExcelUtil.head(labelList))
                    .includeColumnFieldNames(columnList)
                    .registerWriteHandler(new CustomVerticalCellStyleStrategy())
                    .sheet("模板")
                    .doWrite(EasyExcelUtil.dataList(objectList, columnList));

最后附上EasyExcelUtil的代码

import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

public class EasyExcelUtil {
    /**
     * 设置Excel头
     * @param headList  Excel头信息
     * @return
     */
    public static List<List<String>> head(List<String> headList) {
        List<List<String>> list = new ArrayList<>();
        for (String value : headList) {
            List<String> head = new ArrayList<>();
            head.add(value);
            list.add(head);
        }
        return list;
    }
    /**
     * 设置表格信息
     * @param dataList  查询出的数据
     * @param fileList  需要显示的字段
     * @return
     */
    public static List<List<Object>> dataList(List<Object> dataList, List<String> fileList) {
        List<List<Object>> list = new ArrayList<>();
        for (Object person : dataList) {
            List<Object> data = new ArrayList<>();
            for (String fieldName : fileList) {
                /**通过反射根据需要显示的字段，获取对应的属性值*/
                data.add(getFieldValue(fieldName, person));

            }
            list.add(data);
        }
        return list;
    }

    /**
     * 根据传入的字段获取对应的get方法，如name,对应的getName方法
     * @param fieldName  字段名
     * @param person    对象
     * @return
     */
    private static Object getFieldValue(String fieldName, Object person) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method method = person.getClass().getMethod(getter);
            return method.invoke(person);
        } catch (Exception e) {
            return null;
        }
    }

}