excel 导出：

导出：

1.模板导出

（1）.所需jar

 <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.3</version>
        </dependency>

（2）工具类

@Slf4j
public class ExcelExpotUtil {

    /**
     * 注:此方法属于一次性写入,比较占用内存 可以listDetails分隔,循环调用excelWriter.fill(listDetails, fillConfig, writeSheet);
     *
     * @param response    响应
     * @param path        相对地址模板地址
     * @param listDetails 参数
     * @throws IOException
     */
    public static void excelExport(HttpServletResponse response, String path, List<Map<String, Object>> listDetails) {
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + new DateTime().toString("yyyyMMddHHmm") + ".xlsx");
        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(ExcelExpotUtil.class.getResourceAsStream(path)).registerConverter(new CustomerLongNumberConverter()).build();
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            List<List<Map<String, Object>>> partition = Lists.partition(listDetails, 500);
            partition.forEach(page->{
                excelWriter.fill(page, fillConfig, writeSheet);
            });
            excelWriter.finish();
            outputStream.flush();
            partition = null;
        } catch (IOException e) {
            log.error("文件导出异常",e);
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (Exception ioException) {
                log.error("流关闭异常", ioException);
            }
            listDetails = null;
        }
    }
}


public class CustomerLongNumberConverter extends LongNumberConverter {

    @Override
    public CellData convertToExcelData(Long s, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) {
        return new CellData(s == null ? "" : s.toString());
    }
}

（3）模板  （student_read.xls）

 

 

（4）代码示例

 

  /**
      * @Description: 导出
      * @create: 2020/7/15 10:51
      * @update logs
      * @throws Exception
      */
    @RequestMapping(value = "/export")
    public void export(HttpServletRequest request, HttpServletResponse response) {
        Map<String, Object> parameterMap = WebUtils.getParametersStartingWith(request, null);
        StudentReadListParam studentReadListParam = new StudentReadListParam();
        if (parameterMap.get("loginName") != null && StringUtils.isNotBlank((String.valueOf(parameterMap.get("loginName"))))) {

            String loginName = String.valueOf(parameterMap.get("loginName"));
            //根据学生（系统或自定义）账号 查询学生id
            StudentAccount studentAccount = this.studentAccountService.getByLoginName(loginName);
            if (null != studentAccount) {
                Long studentId = studentAccount.getStudentId();
                studentReadListParam.setStudentId(studentId);
            }
        }

        Object currentUser = SecurityUtils.getSubject().getSession().getAttribute(Constants.SESSION_ATTRIBUTE_USER);

        //修改根据code改为in  agentId
        //处理回传的班级和年级Id
        dealGradeIdAndEclassId(parameterMap);
        Agent sysAgent = (Agent) currentUser;
        List<Long> agentIdList = this.agentService.getAgentIdListByMasterId(sysAgent);

        String agentIds = request.getParameter("agentIds");
        if (Boolean.valueOf((String) request.getParameter("executeQuery")) != false) {
            if (agentIds != null && !"".equals(agentIds)) {
                List<Long> masterIdList = StringUtil.stringToLongList(agentIds);
                studentReadListParam.setAgentIdList(masterIdList);
            } else {
                studentReadListParam.setAgentIdList(agentIdList);
            }
        } else {
            Long agentId = sysAgent.getAgentId();
            List<Long> list = new ArrayList<Long>();
            list.add(agentId);
            studentReadListParam.setAgentIdList(list);
        }

        studentReadListParam.setParentIdList(agentIdList);
        studentReadListParam.setPageNo(1);
        studentReadListParam.setPageSize(1000);
        Page<Map<String, Object>> page = studentReadService.listStudentRead(studentReadListParam);
        List<Map<String, Object>> studentReadMap = new ArrayList<>();
        List<Map<String, Object>> records = page.getRecords();
        if (page != null && records != null && records.size() > 0){
            for (Map<String, Object> map : records){
                if (null != map.get("readSecond") && !"".equals(map.get("readSecond"))) {
                    Long readSecond = Long.parseLong(String.valueOf(map.get("readSecond")));
                    if (60 <= readSecond) {
                        readSecond = (readSecond - readSecond % 60) / 60;
                    } else {
                        readSecond = 0L;
                    }
                    map.put("readSecond",readSecond);
            }
             studentReadMap.add(map);
            }
        }

       ExcelExpotUtil.excelExport(response, STUDENT_READ_PATH, studentReadMap);
    }

 

---

 

2.

（1）工具类

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.TableStyle;
import com.alibaba.excel.util.TypeUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.tope365.common.core.exception.BasicException;
import com.tope365.common.core.exception.PubException.ErrCode;
import com.tope365.common.core.pojo.excel.ExcelData;
import com.tope365.common.core.pojo.excel.HeadData;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.function.Consumer;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import net.sf.cglib.beans.BeanMap;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelUtils {
    private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);
    private static final int BATCH_NUM = 100;
    private static final String FORMAT_PUNCTUATION = ".";
    private static final String EXCEL_FORMAT_XLS = ".xls";
    private static final String EXCEL_FORMAT_XLSX = ".xlsx";

    public ExcelUtils() {
    }

    public void singleSheetExport07(HttpServletResponse response, List<List<Object>> data, List<List<String>> head, TableStyle tableStyle, String excelName, String sheetName) {
        if (data != null && !data.isEmpty()) {
            if (excelName == null) {
                excelName = "export";
            }

            if (sheetName == null) {
                sheetName = "Sheet1";
            }

            if (data.size() > 100) {
                int expCount;
                if (data.size() % 100 == 0) {
                    expCount = data.size() / 100;
                } else {
                    expCount = data.size() / 100 + 1;
                }

                for(int i = 0; i < expCount; ++i) {
                    if (100 * (i + 1) >= data.size()) {
                        data.subList(100 * i, data.size());
                    } else {
                        data.subList(100 * i, 100 * (i + 1));
                    }

                    this.singleSheetWrite07(response, data, head, tableStyle, excelName, sheetName);
                }
            } else {
                this.singleSheetWrite07(response, data, head, tableStyle, excelName, sheetName);
            }

        }
    }

    public void singleSheetWrite07(HttpServletResponse response, List<List<Object>> data, List<List<String>> head, TableStyle tableStyle, String excelName, String sheetName) {
        try {
            setResponseHeader(response, excelName);
            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = EasyExcelFactory.getWriter(out);

            try {
                Sheet sheet = new Sheet(1);
                sheet.setSheetName(sheetName);
                if (head != null) {
                    sheet.setHead(head);
                    sheet.setStartRow(((List)head.get(0)).size() - 1);
                } else {
                    sheet.setStartRow(-1);
                }

                if (tableStyle != null) {
                    sheet.setTableStyle(tableStyle);
                }

                writer.write1(data, sheet);
            } finally {
                writer.finish();
                out.flush();
                out.close();
            }

        } catch (FileNotFoundException var15) {
            throw new BasicException(ErrCode.FILE_NOT_EXIST_ERROR.getCode(), ErrCode.FILE_NOT_EXIST_ERROR.getMessage());
        } catch (IOException var16) {
            throw new BasicException(ErrCode.ERROR.getCode(), ErrCode.ERROR.getMessage());
        }
    }

    public void singleSheetWriteFile(File file, List<List<Object>> data, List<List<String>> head, TableStyle tableStyle, String sheetName) {
        FileOutputStream fileOutputStream = null;

        try {
            fileOutputStream = new FileOutputStream(file);
            ExcelWriter writer = EasyExcelFactory.getWriter(fileOutputStream);
            Sheet sheet = new Sheet(1);
            sheet.setSheetName(sheetName);
            if (head != null) {
                sheet.setHead(head);
                sheet.setStartRow(((List)head.get(0)).size() - 1);
            } else {
                sheet.setStartRow(-1);
            }

            if (tableStyle != null) {
                sheet.setTableStyle(tableStyle);
            }

            writer.write1(data, sheet);
            writer.finish();
        } catch (FileNotFoundException var17) {
            var17.printStackTrace();
        } finally {
            try {
                fileOutputStream.close();
            } catch (IOException var16) {
                var16.printStackTrace();
            }

        }

    }

    public ExcelData convertData(List<Map<String, Object>> mapData, List<HeadData> headDataList) {
        ExcelData excelData = new ExcelData();
        List<List<String>> head = new ArrayList();
        List<List<Object>> data = new ArrayList();
        this.sortData(head, data, mapData, headDataList);
        excelData.setHead(head);
        excelData.setData(data);
        return excelData;
    }

    private void sortData(List<List<String>> head, List<List<Object>> data, List<Map<String, Object>> mapData, List<HeadData> headDataList) {
        if (head != null && data != null && mapData != null && headDataList != null && !mapData.isEmpty() && !headDataList.isEmpty()) {
            Iterator var6 = headDataList.iterator();

            while(var6.hasNext()) {
                HeadData headData = (HeadData)var6.next();
                if (headData != null) {
                    List<String> rowHead = new ArrayList();
                    rowHead.add(headData.getTitle());
                    head.add(rowHead);
                }
            }

            Iterator var14 = mapData.iterator();

            label65:
            while(var14.hasNext()) {
                Map<String, Object> m = (Map)var14.next();
                if (m == null || m.isEmpty()) {
                    break;
                }

                List<Object> singleData = new ArrayList();
                Iterator var9 = headDataList.iterator();

                while(true) {
                    while(true) {
                        while(true) {
                            HeadData headData;
                            do {
                                if (!var9.hasNext()) {
                                    data.add(singleData);
                                    continue label65;
                                }

                                headData = (HeadData)var9.next();
                            } while(headData == null);

                            if (m.keySet().contains(headData.getField())) {
                                Iterator var11 = m.entrySet().iterator();

                                while(var11.hasNext()) {
                                    Entry entry = (Entry)var11.next();
                                    if (headData.getField().equals(entry.getKey())) {
                                        singleData.add(entry.getValue());
                                        break;
                                    }
                                }
                            } else {
                                singleData.add("");
                            }
                        }
                    }
                }
            }

        }
    }

    public static <T extends BaseRowModel> void exportExcelSimpleClass(HttpServletResponse response, List<T> dataList, Class<T> clazz, String excelName) {
        ExcelWriter writer = null;
        ServletOutputStream out = null;

        try {
            setResponseHeader(response, excelName);
            out = response.getOutputStream();
            writer = EasyExcelFactory.getWriter(out);
            Sheet sheet = new Sheet(1, 0, clazz);
            sheet.setSheetName(excelName);
            writer.write(dataList, sheet);
            out.flush();
        } catch (Exception var15) {
            var15.printStackTrace();
        } finally {
            if (writer != null) {
                writer.finish();
            }

            if (out != null) {
                try {
                    out.close();
                    out.flush();
                } catch (IOException var14) {
                    var14.printStackTrace();
                }
            }

        }

    }

    public static <T extends BaseRowModel> void exportExcelNestedClass(HttpServletResponse response, List<T> dataList, Class<T> clazz, String excelName) {
        List<HeadData> headDataList = Lists.newArrayList();
        getHeadList(clazz, headDataList);
        List<Map<String, Object>> retMap = Lists.newArrayList();
        dataList.forEach((oneRowData) -> {
            Map<String, Object> rowDateResult = Maps.newLinkedHashMap();
            getValueList(oneRowData, rowDateResult, clazz);
            retMap.add(rowDateResult);
        });
        ExcelUtils excelUtils = new ExcelUtils();
        ExcelData excelData = excelUtils.convertData(retMap, headDataList);
        excelUtils.singleSheetExport07(response, excelData.getData(), excelData.getHead(), (TableStyle)null, excelName, (String)null);
    }

    private static <T extends BaseRowModel> void getHeadList(Class<T> clazz, List<HeadData> headDataList) {
        Field[] var2 = ReflectionUtils.getDeclaredFields(clazz, true);
        int var3 = var2.length;

        for(int var4 = 0; var4 < var3; ++var4) {
            Field field = var2[var4];
            if (field.isAnnotationPresent(ExcelProperty.class)) {
                if (field.getType().getSuperclass().equals(BaseRowModel.class)) {
                    getHeadList(field.getType(), headDataList);
                } else {
                    headDataList.add(new HeadData(field.getName(), ((ExcelProperty)field.getAnnotation(ExcelProperty.class)).value()[0]));
                }
            }
        }

    }

    private static <T extends BaseRowModel> void getValueList(T oneRowData, Map<String, Object> rowDateResult, Class<T> clazz) {
        BeanMap beanMap = BeanMap.create(oneRowData);
        Field[] var4 = ReflectionUtils.getDeclaredFields(clazz, true);
        int var5 = var4.length;

        for(int var6 = 0; var6 < var5; ++var6) {
            Field field = var4[var6];
            if (field.isAnnotationPresent(ExcelProperty.class)) {
                ExcelProperty excelHeadProperty = (ExcelProperty)field.getAnnotation(ExcelProperty.class);
                if (BaseRowModel.class.isAssignableFrom(field.getType())) {
                    Object value = beanMap.get(field.getName());
                    getValueList((BaseRowModel)value, rowDateResult, value.getClass());
                } else {
                    String cellValue = TypeUtil.getFieldStringValue(beanMap, field.getName(), excelHeadProperty.format());
                    rowDateResult.put(field.getName(), cellValue);
                }
            }
        }

    }

    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Type", "application/vnd.ms-excel");

        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        } catch (UnsupportedEncodingException var3) {
            var3.printStackTrace();
        }

    }

import java.util.List;

public class ExcelData {
    private List<List<String>> head;
    private List<List<Object>> data;

    public ExcelData() {
    }

    public List<List<String>> getHead() {
        return this.head;
    }

    public List<List<Object>> getData() {
        return this.data;
    }

    public void setHead(List<List<String>> head) {
        this.head = head;
    }

    public void setData(List<List<Object>> data) {
        this.data = data;
    }

    public boolean equals(Object o) {
        if (o == this) {
            return true;
        } else if (!(o instanceof ExcelData)) {
            return false;
        } else {
            ExcelData other = (ExcelData)o;
            if (!other.canEqual(this)) {
                return false;
            } else {
                Object this$head = this.getHead();
                Object other$head = other.getHead();
                if (this$head == null) {
                    if (other$head != null) {
                        return false;
                    }
                } else if (!this$head.equals(other$head)) {
                    return false;
                }

                Object this$data = this.getData();
                Object other$data = other.getData();
                if (this$data == null) {
                    if (other$data != null) {
                        return false;
                    }
                } else if (!this$data.equals(other$data)) {
                    return false;
                }

                return true;
            }
        }
    }

    protected boolean canEqual(Object other) {
        return other instanceof ExcelData;
    }

    public int hashCode() {
        int PRIME = true;
        int result = 1;
        Object $head = this.getHead();
        int result = result * 59 + ($head == null ? 43 : $head.hashCode());
        Object $data = this.getData();
        result = result * 59 + ($data == null ? 43 : $data.hashCode());
        return result;
    }

    public String toString() {
        return "ExcelData(head=" + this.getHead() + ", data=" + this.getData() + ")";
    }

 

（2）代码示例

 

  /**
      * @Description: 导出抽奖数据
      * @create: 2020/6/29 17:30
      * @update logs
      * @throws Exception
      */
     @RequestMapping(value = "/exportActivityLottery")
     public void exportActivityLottery(HttpServletRequest request, HttpServletResponse response) throws Exception {

         Map<String, Object> parameterMap = WebUtils.getParametersStartingWith(request, null);
         DrawResultsParam drawResultsParam = JSONObject.parseObject(JSONObject.toJSONString(parameterMap), DrawResultsParam.class);
         drawResultsParam.setPageSize(Integer.MAX_VALUE);
         drawResultsParam.setPageNo(1);
         StudentSearchDTO studentSearchDTO = new StudentSearchDTO();
         if (StringUtils.isNotEmpty(drawResultsParam.getStudentName())) {
             studentSearchDTO.setStudentName(drawResultsParam.getStudentName());
         }
         if (StringUtils.isNotEmpty(drawResultsParam.getStudentLoginName())) {
             studentSearchDTO.setStudentLoginName(drawResultsParam.getStudentLoginName());
         }
         if (StringUtils.isNotEmpty(drawResultsParam.getSystemLoginName())) {
             studentSearchDTO.setSystemLoginName(drawResultsParam.getSystemLoginName());
         }

         if (StringUtils.isNotEmpty(drawResultsParam.getStudentName()) ||
                 StringUtils.isNotEmpty(drawResultsParam.getStudentLoginName()) ||
                 StringUtils.isNotEmpty(drawResultsParam.getSystemLoginName())) {
             List<Student> studentByStudentSearchDTO = studentService.getStudentByStudentSearchDTO(studentSearchDTO);
             if (CollectionUtils.isNotEmpty(studentByStudentSearchDTO)) {
                 Student student = studentByStudentSearchDTO.get(0);
                 drawResultsParam.setStudentId(student.getStudentId());
             }
         }


         List<Map<String, Object>> readActivityEnrollList = this.readActivityEnrollService.exportLottery(drawResultsParam);

         // 1.查询数据集合
         List<Map<String, Object>> retMap = readActivityEnrollList;
         if (null == retMap) {
             return;
         }
         // 2.处理 定义表头
         ExcelUtils excelUtils = new ExcelUtils();
         List<HeadData> headData = new ArrayList<>();
         headData.add(new HeadData("studentName", "学生姓名"));
         headData.add(new HeadData("systemLoginName", "系统账号"));
         headData.add(new HeadData("studentLoginName", "自定义账号"));
         headData.add(new HeadData("prizeId", "抽奖状态"));
         headData.add(new HeadData("pId", "兑奖状态"));
         headData.add(new HeadData("isGive", "领取状态"));
         headData.add(new HeadData("goodsTaking", "领取方式"));
         headData.add(new HeadData("payPrice", "加价购"));
         headData.add(new HeadData("endTime", "兑换券截止有效期"));
         headData.add(new HeadData("receiverAddress", "收货地址/自提大区"));
         headData.add(new HeadData("receiverPhone", "收货人电话"));
         headData.add(new HeadData("logisticsNumber", "物流单号"));
         headData.add(new HeadData("prizeKind", "获奖类型"));
         headData.add(new HeadData("description", "奖品"));

         ExcelData excelData = excelUtils.convertData(retMap, headData);
         // 3.导出
         excelUtils.singleSheetExport07(response, excelData.getData(), excelData
                 .getHead(), null, "抽奖结果", "结果");

     }