首页 > 基础资料博客日记

JAVA下载EXCEL模板文件设置下拉框以及级联下拉框（easyExcel）

2024-09-20 11:00:06基础资料围观98次

Java资料网推荐JAVA下载EXCEL模板文件设置下拉框以及级联下拉框（easyExcel）这篇文章给大家，欢迎收藏Java资料网享受知识的乐趣

一、概述

当有需求下载EXCEL模板文件时，一些列的数据是想让用户选择而不是输入，此时就需要实现下拉框，当下拉框逐渐多起来之后，有层级关系的时候就涉及到了级联下拉框。

导入导出基于easyExcel实现的，学习跳转--关于Easyexcel | Easy Excel 官网

二、代码实现

（一）、相关依赖

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.3.7</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

（二）、代码实现

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.*;


@Slf4j
public class CascadeSelectTool {

    private final XSSFWorkbook workbook;

    private XSSFSheet mainSheet;

    /**
     * 数据
     */
    private Map<String, List<String>> areaList = new LinkedHashMap<>();

    /**
     * 隐藏页名称
     */
    private String hiddenSheetName = "hidden";

    /**
     * 第一行
     */
    private int firstRow = 1;

    /**
     * 一级名称
     */
    private String topName;

    /**
     * 级联集合
     */
    private List<Integer> selectColList;


    public CascadeSelectTool(XSSFWorkbook book) {
        this.workbook = book;
    }

    public CascadeSelectTool createSheet(String sheetName) {
        Sheet sheet = workbook.getSheet(sheetName);
        if (Objects.nonNull(sheet)) {
            this.mainSheet = (XSSFSheet) sheet;
        } else {
            this.mainSheet = (XSSFSheet) workbook.createSheet(sheetName);
        }
        return this;
    }

    public CascadeSelectTool createSelectDateList(Map<String, List<String>> areaList) {
        this.areaList = areaList;
        return this;
    }

    public CascadeSelectTool createTopName(String topName) {
        this.topName = topName;
        return this;
    }

    public CascadeSelectTool createSelectColList(List<Integer> selectColList) {
        this.selectColList = selectColList;
        return this;
    }

    public CascadeSelectTool createHiddenName(String hiddenSheetName) {
        this.hiddenSheetName = hiddenSheetName;
        return this;
    }

    public CascadeSelectTool createFirstRow(int firstRow) {
        this.firstRow = firstRow;
        return this;
    }

public CascadeSelectTool createHead(List<String> heads) {
    XSSFRow startRow = this.mainSheet.createRow(0);
    startRow.setHeightInPoints(30);

    // 创建单元格风格样式
    XSSFCellStyle cellStyle = this.workbook.createCellStyle();

    // 设置样式-单元格边框
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    XSSFFont font = this.workbook.createFont();
    font.setBold(true);
    font.setFontHeight(14);
    cellStyle.setFont(font);
    for (int i = 0; i < heads.size(); i++) {
        String value = heads.get(i);
        XSSFCell startCell1 = startRow.createCell(i);
        startCell1.setCellValue(value);
        startCell1.setCellStyle(cellStyle);
        this.mainSheet.autoSizeColumn(i);
        this.mainSheet.setColumnWidth(i, this.mainSheet.getColumnWidth(i) * 3);
    }
    return this;
}

public CascadeSelectTool createEmptyList(int row ,int col) {
    XSSFCellStyle cellStyle = this.workbook.createCellStyle();
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    row = row + 1;
    for (int i = 1; i < row; i++) {
        XSSFRow startRow = this.mainSheet.createRow(i);
        startRow.setHeightInPoints(18);
        for (int j = 0; j < col; j++) {
            XSSFCell startCell1 = startRow.createCell(j);
            startCell1.setCellStyle(cellStyle);
        }
    }
    return this;
}

    /**
     * 设置二级级联下拉框数据
     */
    public CascadeSelectTool setCascadeDropDownBox() {
        //获取所有sheet页个数
        int sheetTotal = workbook.getNumberOfSheets();
        //处理下拉数据
        if (areaList != null && areaList.size() != 0) {
            //新建一个sheet页
            XSSFSheet hiddenSheet = workbook.getSheet(hiddenSheetName);
            if (hiddenSheet == null) {
                hiddenSheet = workbook.createSheet(hiddenSheetName);
                sheetTotal++;
            }
            int mainStart = 2;
            int mainEnd = mainStart;
            // 获取数据起始行
            int startRowNum = hiddenSheet.getLastRowNum() + 1;
            Set<String> keySet = areaList.keySet();
            for (String key : keySet) {
                XSSFRow fRow = hiddenSheet.createRow(startRowNum++);
                fRow.createCell(0).setCellValue(key);
                List<String> sons = areaList.get(key);
                for (int i = 1; i <= sons.size(); i++) {
                    fRow.createCell(i).setCellValue(sons.get(i - 1));
                }
                if (Objects.equals(topName, key)) {
                    mainEnd = sons.size();
                }
                // 添加名称管理器
                String range = getRange(1, startRowNum, sons.size());
                Name name = workbook.getName(key);
                if (Objects.isNull(name)) {
                    name = workbook.createName();
                    //key不可重复
                    name.setNameName(key);
                    String formula = hiddenSheetName + "!" + range;
                    name.setRefersToFormula(formula);
                }
            }
            //将数据字典sheet页隐藏掉
            workbook.setSheetHidden(sheetTotal - 1, true);

            // 设置父级下拉
            //获取新sheet页内容
            String mainFormula = hiddenSheetName + "!$A$" + mainStart + ":$A$" + (mainEnd + 1);

            for (int i = 0; i < selectColList.size(); i++) {
                Integer col = selectColList.get(i);
                if (i == 0) {
                    // 设置下拉列表值绑定到主sheet页具体哪个单元格起作用
                    mainSheet.addValidationData(setDataValidation(mainFormula, firstRow, col, col));
                } else {
                    Integer fatherCol = selectColList.get(i - 1);
                    // 设置子级下拉
                    // 当前列为子级下拉框的内容受父级哪一列的影响
                    String indirectFormula = "INDIRECT($" + decimalToTwentyHex(fatherCol + 1) + "" + (firstRow + 1) + ")";
                    mainSheet.addValidationData(setDataValidation(indirectFormula, firstRow, col, col));
                }
            }
        }
        return this;
    }

    /**
     * 计算formula
     *
     * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    private String getRange(int offset, int rowId, int colCount) {
        char start = (char) ('A' + offset);
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            // 26-51之间，包括边界（仅两次字母表计算）
            if ((colCount - 25) / 26 == 0 || colCount == 51) {
                // 边界值
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                }
            } else {// 51以上
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }

    /**
     * 返回类型 DataValidation
     *
     * @param strFormula formula
     * @param firstRow   起始行
     * @param firstCol   起始列
     * @param endCol     终止列
     * @return 返回类型 DataValidation
     */
    private DataValidation setDataValidation(String strFormula, int firstRow, int firstCol, int endCol) {
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, 65535, firstCol, endCol);
        DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) workbook.getSheet(hiddenSheetName));
        DataValidationConstraint formulaListConstraint = dvHelper.createFormulaListConstraint(strFormula);
        return dvHelper.createValidation(formulaListConstraint, regions);
    }

    /**
     * 返回类型 DataValidation
     * @param strFormula formula
     * @param firstRow   起始行
     * @param endRow     终止行
     * @param firstCol   起始列
     * @param endCol     终止列
     * @return 返回类型 DataValidation
     */
    public DataValidation setTypeListDataValidation(String strFormula, int firstRow, int endRow, int firstCol, int endCol) {
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) workbook.getSheet("typelist"));
        DataValidationConstraint formulaListConstraint = dvHelper.createFormulaListConstraint(strFormula);
        return dvHelper.createValidation(formulaListConstraint, regions);
    }


    /**
     * 设置下拉框数据
     * @param typeName 要渲染的sheet名称
     * @param values   下拉框的值
     * @param col      下拉列的下标
     * @author Hower Wong
     * @date 2022年5月27日
     */
    public void setDropDownBox(String typeName, String[] values, Integer col) {
        //获取所有sheet页个数
        int sheetTotal = workbook.getNumberOfSheets();
        //处理下拉数据
        if (values != null && values.length != 0) {
            //新建一个sheet页
            XSSFSheet hiddenSheet = workbook.getSheet(hiddenSheetName);
            if (hiddenSheet == null) {
                hiddenSheet = workbook.createSheet(hiddenSheetName);
                sheetTotal++;
            }
            // 获取数据起始行
            int startRowNum = hiddenSheet.getLastRowNum() + 1;
            int endRowNum = startRowNum;
            //写入下拉数据到新的sheet页中
            for (int i = 0; i < values.length; i++){
                hiddenSheet.createRow(endRowNum++).createCell(0).setCellValue(values[i]);
            }
            //将新建的sheet页隐藏掉
            workbook.setSheetHidden(sheetTotal - 1, true);
            //获取新sheet页内容
            String strFormula = hiddenSheetName + "!$A$" + ++startRowNum + ":$A$" + endRowNum;
            // 设置下拉
            XSSFSheet mainSheet = workbook.getSheet(typeName);
            mainSheet.addValidationData(setTypeListDataValidation(strFormula, 1, 65535, col, col));
        }
    }


    /**
     * 十进制转二十六进制
     */
    private String decimalToTwentyHex(int decimalNum) {
        StringBuilder result = new StringBuilder();
        while (decimalNum > 0) {
            int remainder = decimalNum % 26;
            //大写A的ASCII码值为65
            result.append((char) (remainder + 64));
            decimalNum = decimalNum / 26;
        }
        return result.reverse().toString();
    }

    public void writeFile() {
        writeFile(workbook);
    }


    public static void writeFile(Workbook book) {
        try {
            String storeName = System.currentTimeMillis() + ".xlsx";
            String folder = "project//cct/" + cn.hutool.core.date.DateUtil.format(DateUtil.date(), "yyMMdd") + "/";
            String attachmentFolder = "E://" + File.separator;
            String address = folder + storeName;
            FileUtil.mkdir(attachmentFolder + folder);
            FileOutputStream fileOut = new FileOutputStream(attachmentFolder + address);
            book.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }


    public XSSFWorkbook getWorkbook() {
        return workbook;
    }

    public void backFlow(HttpServletResponse response) {
        try {
            // 将工作簿写入输出流
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);

            // 设置响应头，告诉浏览器返回的是一个Excel文件
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment; filename=.xls");

            // 将Excel文件写入响应的输出流
            OutputStream outStream = response.getOutputStream();
            outputStream.writeTo(outStream);
            outStream.flush();
            outStream.close();
        } catch (Exception e) {
            log.error("导出异常-->", e);
        }

    }
}

三、导出EXCEL设置下拉框

设置下拉框导出示例

/**
 * 商品导入模板下载
 * @param response
 */
@PostMapping("/template")
@SneakyThrows
public void excelOtherTemplate(HttpServletResponse response){
    String nameStr = "商品导入模板";
    List<String> heads = Arrays.asList("单号", "姓名", "供应商", "商品", "价格");
    XSSFWorkbook book = new XSSFWorkbook();
    CascadeSelectTool cascadeSelectTool = new CascadeSelectTool(book)
            .createSheet(nameStr)
            .createHead(heads)
            .createEmptyList(20, heads.size());
    List<String> storeList = CollectionUtil.newArrayList("哇哈哈", "可口可乐");
    String[] storeStr = storeList.toArray(new String[storeList.size()]);
    cascadeSelectTool.setDropDownBox(nameStr, storeStr, 2);
    List<String> merList = CollectionUtil.newArrayList("营养快线", "爽歪歪", "AD钙");
    String[] merStr = merList.toArray(new String[merList.size()]);
    cascadeSelectTool.setDropDownBox(nameStr, merStr, 3);

    String fileName = nameStr+".xlsx";
    // 将工作簿写入输出流
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    book.write(outputStream);
    // 设置响应头，告诉浏览器返回的是一个Excel文件
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
    // 将Excel文件写入响应的输出流
    OutputStream outStream = response.getOutputStream();
    outputStream.writeTo(outStream);
    outStream.flush();
    outStream.close();
}

对于新建list的时候用asList方法还是newArrayList，如果新建的list不涉及到新增、删除操作时用asList()，涉及的话用newArrayList()，也可统一用后者。

效果图：

设置级联下拉框导出示例

/**
 * 商品模板下载
 * @param response
 */
@PostMapping("/template")
@SneakyThrows
public void excelTemplate(HttpServletResponse response){
    String fileName = "供货单模板.xlsx";
    // 将工作簿写入输出流
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    excelTemplate().write(outputStream);
    // 设置响应头，告诉浏览器返回的是一个Excel文件
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
    // 将Excel文件写入响应的输出流
    OutputStream outStream = response.getOutputStream();
    outputStream.writeTo(outStream);
    outStream.flush();
    outStream.close();
}

public XSSFWorkbook excelTemplate() {
    //业务数据模拟 请根据真实场景替换
    Map<Long, String> supNameMap = new HashMap<>();
    supNameMap.put(1L, "哇哈哈");
    supNameMap.put(2L, "可口可乐");
    Map<Long, List<String>> merMap = new HashMap<>();
    merMap.put(1L, Arrays.asList("营养快线", "爽歪歪", "AD钙"));
    merMap.put(2L, Arrays.asList("美汁源","芬达","水动乐","健怡","零度"));
    //示例--供应商与商品的级联下拉框
    List<String> supNameList = supNameMap.values().stream().collect(Collectors.toList());
    Map<String, List<String>> areaList = new LinkedHashMap<>();
    areaList.put("供应商", supNameList);
    List<Map<String, List<String>>> twoList = new ArrayList<>();
    for (Map.Entry<Long, String> entry : supNameMap.entrySet()) {
        String supName = entry.getValue();
        Long supId = entry.getKey();
        List<String> commodityList = merMap.get(supId);
        List<String> merNameList = new ArrayList<>();
        Map<String, List<String>> twoMap = new HashMap<>();
        for (String merName : commodityList) {
            merNameList.add(merName);
        }
        if (merNameList.isEmpty()) {
            merNameList.add("");
        }
        twoMap.put(supName, merNameList);
        twoList.add(twoMap);
    }
    twoList.forEach(t -> areaList.putAll(t));

    List<Integer> selectColList = CollectionUtil.newArrayList(2,3);

    XSSFWorkbook book = new XSSFWorkbook();
    List<String> heads = Arrays.asList("单号", "姓名", "供应商", "商品", "价格");
    CascadeSelectTool cascadeSelectTool = new CascadeSelectTool(book)
            .createSheet("供货单")
            .createHead(heads)
            .createEmptyList(20, heads.size())
            .createFirstRow(1)
            .createSelectDateList(areaList)
            .createTopName("供应商")
            .createSelectColList(selectColList)
            .setCascadeDropDownBox();
    //示例--姓名下拉框
    List<String> nameList = Arrays.asList("张三", "李四", "王五");
    cascadeSelectTool.setDropDownBox(   "供货单", nameList.toArray(new String[nameList.size()]), 1);
    return cascadeSelectTool.getWorkbook();
}

参考地址：Java 导出Excel下拉框(多级级联)-CSDN博客

效果图：

正片结束~~

附：关于导入导出的杂记

附上easyExcel导入导出一些小tips：

导入导出遇到时间类型报错处理

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

/**
 * 自定义LocalDateStringConverter
 * 用于解决使用easyexcel导出表格时候，默认不支持LocalDateTime日期格式
 *
 * 在需要的属性上添加注解 @ExcelProperty(value = "创建日期", converter = LocalDateStringConverter.class)
 */

public class LocalDateStringConverter implements Converter<LocalDate> {
    @Override
    public Class supportJavaTypeKey() {
        return LocalDateTime.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public WriteCellData<?> convertToExcelData(LocalDate localDate, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        WriteCellData cellData = new WriteCellData();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        String cellValue;
        cellValue=formatter.format(localDate);
        cellData.setType(CellDataTypeEnum.STRING);
        cellData.setStringValue(cellValue);
        cellData.setData(cellValue);
        return cellData;
    }
}

表头的自动行高

设置表头的自动调整行高策略

import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;

import java.util.Iterator;

/**
 * 设置表头的自动调整行高策略
 */
public class CellRowHeightStyleStrategy extends AbstractRowHeightStyleStrategy {

    /**
     * 默认高度
     */
    private static final Integer DEFAULT_HEIGHT = 300;

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
        //设置主标题行高为10
        if(relativeRowIndex == 0){
            row.setHeight((short) 500);
        }
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        Iterator<Cell> cellIterator = row.cellIterator();
        while (!cellIterator.hasNext()) {
            return;
        }
        // 默认为 1行高度
        int maxHeight = 1;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getCellTypeEnum() == CellType.STRING) {
                String value = cell.getStringCellValue();
                int len = value.length();
                int num = 0;
                if (len > 50) {
                    num = len % 50 > 0 ? len / 50 : len / 2 - 1;
                }
                if (num > 0) {
                    for (int i = 0; i < num; i++) {
                        value = value.substring(0, (i + 1) * 50 + i) + "\n" + value.substring((i + 1) * 50 + i, len + i);
                    }
                }
                if (value.contains("\n")) {
                    int length = value.split("\n").length;
                    maxHeight = Math.max(maxHeight, length) + 1;
                }
            }
        }
        row.setHeight((short) ((maxHeight) * DEFAULT_HEIGHT));
    }

}

设置表头和填充内容的样式

import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

/**
 * 设置表头和填充内容的样式
 */
public class CellStyleStrategy extends HorizontalCellStyleStrategy {
   

    private final WriteCellStyle headWriteCellStyle;
    private final WriteCellStyle contentWriteCellStyle;


    public CellStyleStrategy(WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {

        this.headWriteCellStyle = headWriteCellStyle;
        this.contentWriteCellStyle = contentWriteCellStyle;
    }

    //设置头样式
    @Override
    protected void setHeadCellStyle( CellWriteHandlerContext context) {
        // 字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("宋体");
        headWriteFont.setFontHeightInPoints((short)14);
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);

        // 样式
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        headWriteCellStyle.setBottomBorderColor((short) 0);
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setLeftBorderColor((short) 0);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setRightBorderColor((short) 0);
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        headWriteCellStyle.setTopBorderColor((short) 0);

        headWriteCellStyle.setWrapped(true);

        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headWriteCellStyle.setShrinkToFit(true);

        if (stopProcessing(context)) {
            return;
        }
        WriteCellData<?> cellData = context.getFirstCellData();
        WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle());
    }

    //设置填充数据样式
    @Override
    protected void setContentCellStyle(CellWriteHandlerContext context) {
   
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 11);
        //设置数据填充后的实线边框
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setWrapped(true);
        WriteCellData<?> cellData = context.getFirstCellData();
        WriteCellStyle.merge(contentWriteCellStyle, cellData.getOrCreateStyle());
    }
}

自动设置列宽

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.springblade.core.tool.utils.Func;
import org.springframework.util.CollectionUtils;

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


@Slf4j
public class CellWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {

    private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            columnWidth = (int) (Func.toInt(columnWidth) * 1.5);
            // 单元格文本长度大于60换行
            if (columnWidth >= 0) {
                if (columnWidth > 60) {
                    columnWidth = 60;
                }
                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    Sheet sheet = writeSheetHolder.getSheet();
                    sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }
            }
        }
    }
    /**
     * 计算长度
     * @param cellDataList
     * @param cell
     * @param isHead
     * @return
     */
    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData<?> cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        // 换行符（数据需要提前解析好）
                        int index = cellData.getStringValue().indexOf("\n");
                        return index != -1 ?
                                cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

同列相同数据合并

import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.concurrent.atomic.AtomicInteger;

/**
 *
 * easyExcel 列合并
 * 同列相同数据合并
 *
 */
public class CustomMergeStrategy extends AbstractMergeStrategy {

   /**
    * 分组，每几行合并一次
    */
   private List<List<Integer>> mergeColDataGroupCountList;

   /**
    * 目标合并列index
    */
   private List<Integer> targetColumnIndex;
   /**
    *     需要开始合并单元格的首行index
    */
   private Integer rowIndex;

   /**
    *     mergeColDataList为待合并目标列的值
    */
   public CustomMergeStrategy(List<List<String>> mergeColDataList, List<Integer> targetColumnIndex) {
      this.mergeColDataGroupCountList = getGroupCountList(mergeColDataList);
      this.targetColumnIndex = targetColumnIndex;
   }


   @Override
   protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {

      if (null == rowIndex) {
         rowIndex = cell.getRowIndex();
      }
      // 仅从首行以及目标列的单元格开始合并，忽略其他
      if (cell.getRowIndex() == rowIndex && targetColumnIndex.contains(cell.getColumnIndex())) {
         //找到对应的需要合并的列
         AtomicInteger i = new AtomicInteger(0);
         Optional<Integer> first = targetColumnIndex.stream().filter(col -> {
            i.getAndIncrement();
            return col == cell.getColumnIndex();
         }).findFirst();
         mergeGroupColumn(sheet, first.get());
      }
   }

   private void mergeGroupColumn(Sheet sheet, Integer index) {
      int rowCount = rowIndex;
      for (Integer count : mergeColDataGroupCountList.get(index)) {
         if (count == 1) {
            rowCount += count;
            continue;
         }
         // 合并单元格
         CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1,
               targetColumnIndex.get(index), targetColumnIndex.get(index));
         sheet.addMergedRegionUnsafe(cellRangeAddress);
         rowCount += count;
      }
   }

   /**
    *     该方法将目标列根据值是否相同连续可合并，存储可合并的行数
    */
   private List<List<Integer>> getGroupCountList(List<List<String>> exportDataList) {
      if (CollUtil.isEmpty(exportDataList)) {
         return new ArrayList<>();
      }
      List<List<Integer>> groupCountListList = new ArrayList<>();
      exportDataList.forEach(dataList->{
         List<Integer> groupCountList = new ArrayList<>();
         int count = 1;
         for (int i = 1; i < dataList.size(); i++) {
            if (dataList.get(i).equals(dataList.get(i - 1))) {
               count++;
            } else {
               groupCountList.add(count);
               count = 1;
            }
         }
         // 处理完最后一条后
         groupCountList.add(count);
         groupCountListList.add(groupCountList);
      });
      return groupCountListList;
   }
}

合并参考：EasyExcel合并单元格（同列相同数据合并）_easyexcel合并列单元格-CSDN博客

根据指定列合并参考：JAVA导出EXCEL根据某列自定义单元格合并（easyExcel）-CSDN博客

策略使用示例

根据实际情况使用registerWriteHandler，可以一个都不注册，也可以注册一个或多个。

// 需要合并的列 mergeColDataList 
//  List<List<String>> mergeColDataList 需要合并的数据
List<List<String>> mergeColDataList = Stream.of(Arrays.asList("张三","李四","王五").stream().collect(Collectors.toList()),
        Arrays.asList("哇哈哈","可口可乐").stream().collect(Collectors.toList()),
        Arrays.asList("芬达","零度").stream().collect(Collectors.toList())).collect(Collectors.toList());
List<Integer> mergeColIndexList = Stream.of(0,1,2).collect(Collectors.toList());
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
String fileName = URLEncoder.encode(name + ".xlsx", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
EasyExcel.write(response.getOutputStream(), ExcelMealStatisticFeeDto.class)
        .registerWriteHandler(new CellStyleStrategy(new WriteCellStyle(), new WriteCellStyle()))
        .registerWriteHandler(new CellWidthStyleStrategy())
        .registerWriteHandler(new CellRowHeightStyleStrategy())
        .registerWriteHandler(new CustomMergeStrategy(mergeColDataList, mergeColIndexList))
        .sheet(name)
        .doWrite(list);

附上模板下载的工具类

exportNoModel方法是下载模板文件，生成文件的表头为传入的headMap。

download方法适用于本地已存在模板文件的场景，fileName为浏览器下载的文件名称（可传中文），filePath为文件所处的位置。

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import lombok.extern.slf4j.Slf4j;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@Slf4j
public class ExcelUtil {

    /**
     * 下载模板文件
     * @param response
     * @param headMap
     * @param fileName
     * @param dataList
     */
    public static void exportNoModel(HttpServletResponse response, String[] headMap, String fileName, List<List<Object>> dataList) {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String name = URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + name + ".xlsx");
            EasyExcel.write(response.getOutputStream()).head(createdHead(headMap))
                    .registerWriteHandler(new CellStyleStrategy(new WriteCellStyle(), new WriteCellStyle()))
                    .registerWriteHandler(new CellWidthStyleStrategy())
                    .registerWriteHandler(new CellRowHeightStyleStrategy())
                    .sheet(fileName).doWrite(dataList);
        } catch (IOException e) {
            log.error("导出异常-->", e);
        }
    }

    /**
     * 下载模板文件
     * @param response
     * @param fileName
     * @param filePath
     */
    public static void download(HttpServletResponse response, String fileName, String filePath){
        try {
            response.setContentType("application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setHeader("filename", URLEncoder.encode(fileName, "UTF-8"));
            response.setHeader("Access-Control-Expose-Headers", "filename,Content-Disposition");

            ClassPathResource resource = new ClassPathResource(filePath);
            InputStream input = resource.getInputStream();
            OutputStream out = response.getOutputStream();
            byte[] b = new byte[2048];
            int len;
            while ((len = input.read(b)) != -1) {
                out.write(b, 0, len);
            }
            input.close();
        } catch (Exception e) {
            log.error("下载模板失败 :", e);
        }
    }


    /**
     * 处理字段的表头值
     * @param headMap
     * @return
     */
    private static List<List<String>> createdHead(String[] headMap) {
        List<List<String>> headList = new ArrayList<List<String>>();
        for (String head : headMap) {
            List<String> list = new ArrayList<String>();
            list.add(head);
            headList.add(list);
        }
        return headList;
    }
}

文章来源:https://blog.csdn.net/qq_43878405/article/details/140384726
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：jacktools123@163.com进行投诉反馈，一经查实，立即删除！

标签：

上一篇：MutationObserver详解+案例——深入理解 JavaScript 中的 MutationObserver：原理与实战案例
下一篇：敏感词 v0.21.0 白名单支持单个编辑，修正白名单包含黑名单时的问题

首页 > 基础资料博客日记

JAVA下载EXCEL模板文件设置下拉框以及级联下拉框（easyExcel）

一、概述

二、代码实现

（一）、相关依赖

（二）、代码实现

三、导出EXCEL设置下拉框

设置下拉框导出示例

设置级联下拉框导出示例

附：关于导入导出的杂记

导入导出遇到时间类型报错处理

表头的自动行高

设置表头和填充内容的样式

自动设置列宽

同列相同数据合并

策略使用示例

附上模板下载的工具类

相关文章

最新发布

点击排行

本站推荐

标签云

首页 > 基础资料 博客日记

JAVA下载EXCEL模板文件设置下拉框以及级联下拉框（easyExcel）

一、概述

二、代码实现

（一）、相关依赖

（二）、代码实现

三、导出EXCEL设置下拉框

设置下拉框导出示例

设置级联下拉框导出示例

附：关于导入导出的杂记

导入导出遇到时间类型报错处理

表头的自动行高

设置表头和填充内容的样式

自动设置列宽

同列相同数据合并

策略使用示例

附上模板下载的工具类

相关文章

最新发布

点击排行

本站推荐

标签云

首页 > 基础资料博客日记