使用easyExcel 导出文件并合并相同内容的单元格-导出下拉模板-数据有效性校验

原创

已于 2024-08-09 11:52:27 修改 · 1k 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#java #spring boot

于 2024-07-23 17:07:58 首次发布

首先看下效果：

在这里插入图片描述

一、导入依赖

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.3</version>
        </dependency>

二、准备工具类

1.操作的工具类

package com.yuncheng.utils.excel;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.yuncheng.utils.excel.handler.AutoWidthHandler;
import com.yuncheng.utils.excel.handler.SelectSheetWriteHandler;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;
/**
 * @Description: 动态excel工具类
 * @Author: ChenH
 * @Date: 2024/7/23 10:50
 */

@Component
@Slf4j
public class  DynamicExcelUtils {
   
   

    /**
     * 动态excel模板下载
     * @param response  响应
     * @param title     大标题说明，多行使用转义符\n换行
     * @param names     列集合
     * @param fieldEn   列英文名称
     * @param selectMap 下拉框
     * @param fileName  自定义文件名称
     * @param sheetName 自定义sheet页名称
     */
    public void excelDownloadLink(HttpServletResponse response, String title,List<String> names, List<String> fieldEn ,List list ,Map<Integer,
            List<String>> selectMap,String sheetName, String fileName) {
   
   
        try {
   
   
            //设置表格示例数据的值
            List<List<String>> datas = setData(list,fieldEn);
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
            response.setCharacterEncoding("UTF-8");
            EasyExcel.write(response.getOutputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .head(head(CollectionUtil.isNotEmpty(names) ? names.toArray(new String[0]) : new String[0], title))
                    //开启内存模式才能使用动态设置标题样式
                    .inMemory(true)
                    .registerWriteHandler(new TitleStyleUtils(names))
                    .registerWriteHandler(new SelectSheetWriteHandler(selectMap))
                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25))
                    .sheet(sheetName)
                    .doWrite(CollectionUtil.isNotEmpty(datas) ? datas:new ArrayList());
        } catch (IOException e) {
   
   
            e.printStackTrace();
            response.reset();
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/json");
            try {
   
   
                response.getWriter().println("打印失败");
            } catch (IOException ex) {
   
   
                ex.printStackTrace();
            }
        }
    }


    /**
     * 动态excel数据导出
     * @param response 响应
     * @param fileName  自定义文件名称
     * @param sheetName 自定义sheet页名称
     * @param list     数据集合
     */
    public void excelExportData(HttpServletResponse response,List<String> names,List<String> fieldEn,List list ,String sheetName, String fileName){
   
   
        try {
   
   
            //设置返回数据的值跟动态列一一对应
            List<List<String>> datas = setData(list,fieldEn);
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
            response.setCharacterEncoding("UTF-8");
            EasyExcel.write(response.getOutputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .head(headData(CollectionUtil.isNotEmpty(names) ? names.toArray(new String[0]) : new String[0]))
                    .registerWriteHandler(new AutoWidthHandler())
                    .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 25, (short) 25))
                    .sheet(sheetName)
                    .doWrite(CollectionUtil.isNotEmpty(datas) ? datas:new ArrayList());

        } catch (IOException e) {
   
   
            e.printStackTrace();
            response.reset();
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/json");
            try {
   
   
                response.getWriter().println("打印失败");
            } catch (IOException ex) {
   
   
                ex.printStackTrace();
            }
        }
    }
    /**
     * 动态excel数据导出
     * @param response 响应
     * @param fileName  自定义文件名称
     * @param sheetName 自定义sheet页名称
     * @param list     数据集合
     */
    public void excelExportData(HttpServletResponse response, List<String> names, List list, String sheetName, String fileName){
   
   
        try {
   
   
            //设置返回数据的值跟动态列一一对应
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
            response.setCharacterEncoding("UTF-8");
            EasyExcel.write(response.getOutputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    //.head(head(CollectionUtil.isNotEmpty(names) ? names.toArray(new String[0]) : new String[0], title))
                    .head(headData(CollectionUtil.isNotEmpty(names) ? names.toArray(new String[0]) : new String[0]))
                    .registerWriteHandler(new AutoWidthHandler())
                    .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 25, (short) 25))
                    .sheet(sheetName)
                    .doWrite(CollectionUtil.isNotEmpty(list) ? list:new ArrayList());
        } catch (IOException e) {
   
   
            e.printStackTrace();
            response.reset();
            response.setCharacterEncoding(