Excel导出模板加数据时，下拉框丢失解决方案

@vespera

已于 2023-02-08 15:17:15 修改

阅读量2.4k

点赞数 4

分类专栏： Excel 文章标签： java

于 2022-12-16 16:37:03 首次发布

by-@vespera

本文链接：https://blog.youkuaiyun.com/alwBelInThePro/article/details/128343529

版权

Excel 专栏收录该内容

1 篇文章

订阅专栏

简介：

在工作中、我们大多数会碰到导出、导入excel功能。此篇文档便是对导出时，模板中自带的下拉框丢失的问题记录。

摘要：

首先简要描述如何实现excel导出模板加数据功能。

1、读取sheet模板行

2、复制sheet模板行（excel单元格格式、函数、下拉框等）

// 创建行、第一行模板行不用复制，i为要导出excel的行数
if (i != 0) {
   sheet.copyRows(2, 2, i + 2, new CellCopyPolicy());
}

Row row = sheet.getRow(i + 2);

// 创建单元格，i为当前sheet的总列数
Cell cell = row.getCell(i);
cell.setCellValue("smaple");
// cellStyle为单元格的格式
cell.setCellStyle(cellStyle);

// 单元格格式示例
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);    //下边框
cellStyle.setBorderLeft(BorderStyle.THIN);      //左边框
cellStyle.setBorderTop(BorderStyle.THIN);       //上边框
cellStyle.setBorderRight(BorderStyle.THIN);     //右边框
// 设置单元格格式为文本、如果需要设置其他格式、详情参照:https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/BuiltinFormats.html
cellStyle.setDataFormat(workbook.createDataFormat().getFormat("@"));

场景：

代码获取DataValidations为空，实际模板里是有下拉框

导出模板数据、除了第一行保留格式、其他行下拉框丢失

解决方案

一、修改excel模板（Excel）

1、选择下拉框当前列

2、按住ctrl键，鼠标焦点放在标题上，点击鼠标左键，取消选中标题

3、设置下拉框，（如何设置下拉框具体请查看excel相关文档）

4、设置后当前整列都有下拉框，（缺点：没用到的行也会有下拉格式）

二、通过自定义方法【getDataValidations】获取到模板里的下拉框（Java）

先看结果：

代码中getDataValidations这里可以获取到下拉框，表格中第一行以外的情况也保留下拉框

代码：


static List<XmlObject> getX14DataValidations(XSSFSheet sheet) {
    List<XmlObject> x14DataValidations = new ArrayList<>();
    XmlCursor cursor = sheet.getCTWorksheet().newCursor();
    cursor.selectPath(
            "declare namespace x14='http://schemas.microsoft.com/office/spreadsheetml/2009/9/main' .//x14:dataValidation");
    while(cursor.hasNextSelection()) {
        cursor.toNextSelection();
        XmlObject obj = cursor.getObject();
        x14DataValidations.add(obj);
    }
    return x14DataValidations;
}

static void addXSSFX14DataValidations(XSSFSheet sheet, List<DataValidation> dataValidations) {
    List<XmlObject> x14DataValidations = getX14DataValidations(sheet);
    for (XmlObject x14DataValidation : x14DataValidations) {
        XSSFX14DataValidation xssfX14DataValidation = new XSSFX14DataValidation(x14DataValidation);
        dataValidations.add(xssfX14DataValidation);
    }
}

public static List<DataValidation> getDataValidations(Sheet sheet) {
    @SuppressWarnings("unchecked")
    List<DataValidation> dataValidations = (List<DataValidation>)sheet.getDataValidations();
    if (sheet instanceof XSSFSheet) {
        addXSSFX14DataValidations((XSSFSheet)sheet, dataValidations);
    }
    return dataValidations;
}

/**
 * 根据sheet既有的下拉框复制下拉框
 *
 * @param sheet     工作簿
 * @param rowNumEnd 结束行
 */
public static void validationCopy(XSSFSheet sheet, int rowNumEnd) {

    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    List<DataValidation> dataValidations = getDataValidations(sheet);
    for (DataValidation dataValidation : dataValidations) {
        CellRangeAddress range = dataValidation.getRegions().getCellRangeAddresses()[0];

        // 创建下拉框
        CellRangeAddressList addressList = new CellRangeAddressList(range.getFirstRow(), rowNumEnd < range.getFirstRow() ? range.getLastRow() : rowNumEnd, range.getFirstColumn(), range.getLastColumn());
        XSSFDataValidationConstraint constraint =
                (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(dataValidation.getValidationConstraint().getFormula1());
        XSSFDataValidation validation =
                (XSSFDataValidation) dvHelper.createValidation(constraint, addressList);
        sheet.addValidationData(validation);
    }

}
}


class XSSFX14DataValidation implements DataValidation {

private DataValidationConstraint validationConstraint;
private int errorStyle;
private boolean emptyCellAllowed;
private boolean suppressDropDownArrow;
private boolean showPromptBox;
private boolean showErrorBox;
private String promptBoxTitle;
private String promptBoxText;
private String errorBoxTitle;
private String errorBoxText;
private CellRangeAddressList regions;

public XSSFX14DataValidation(XmlObject x14DataValidation) {
    String type = "";
    XmlObject typeAttribute = x14DataValidation.selectAttribute(new QName("type"));
    if (typeAttribute != null) type = typeAttribute.newCursor().getTextValue();
    Integer validationType = DataValidationConstraint.ValidationType.ANY;
    if ("CUSTOM".equalsIgnoreCase(type)) {
        validationType = DataValidationConstraint.ValidationType.FORMULA;
    } else if ("DATE".equalsIgnoreCase(type)) {
        validationType = DataValidationConstraint.ValidationType.DATE;
    } else if ("DECIMAL".equalsIgnoreCase(type)) {
        validationType = DataValidationConstraint.ValidationType.DECIMAL;
    } else if ("LIST".equalsIgnoreCase(type)) {
        validationType = DataValidationConstraint.ValidationType.LIST;
    } else if ("NONE".equalsIgnoreCase(type)) {
        validationType = DataValidationConstraint.ValidationType.ANY;
    } else if ("TEXT_LENGTH".equalsIgnoreCase(type)) {
        validationType = DataValidationConstraint.ValidationType.TEXT_LENGTH;
    } else if ("TIME".equalsIgnoreCase(type)) {
        validationType = DataValidationConstraint.ValidationType.TIME;
    } else if ("WHOLE".equalsIgnoreCase(type)) {
        validationType = DataValidationConstraint.ValidationType.INTEGER;
    }

    String operator = "";
    XmlObject operatorAttribute = x14DataValidation.selectAttribute(new QName("operator"));
    if (operatorAttribute != null) operator = operatorAttribute.newCursor().getTextValue();
    Integer operatorType = DataValidationConstraint.OperatorType.IGNORED;
    if ("BETWEEN".equalsIgnoreCase(operator)) {
        operatorType = DataValidationConstraint.OperatorType.BETWEEN;
    } else if ("NOT_BETWEEN".equalsIgnoreCase(operator)) {
        operatorType = DataValidationConstraint.OperatorType.NOT_BETWEEN;
    } else if ("EQUAL".equalsIgnoreCase(operator)) {
        operatorType = DataValidationConstraint.OperatorType.EQUAL;
    } else if ("NOT_EQUAL".equalsIgnoreCase(operator)) {
        operatorType = DataValidationConstraint.OperatorType.NOT_EQUAL;
    } else if ("GREATER_THAN".equalsIgnoreCase(operator)) {
        operatorType = DataValidationConstraint.OperatorType.GREATER_THAN;
    } else if ("GREATER_OR_EQUAL".equalsIgnoreCase(operator)) {
        operatorType = DataValidationConstraint.OperatorType.GREATER_OR_EQUAL;
    } else if ("LESS_THAN".equalsIgnoreCase(operator)) {
        operatorType = DataValidationConstraint.OperatorType.LESS_THAN;
    } else if ("LESS_OR_EQUAL".equalsIgnoreCase(operator)) {
        operatorType = DataValidationConstraint.OperatorType.LESS_OR_EQUAL;
    }

    String formula1 = null;
    XmlObject[] xmlObjects = x14DataValidation.selectChildren(
            new QName("http://schemas.microsoft.com/office/spreadsheetml/2009/9/main", "formula1"));
    if (xmlObjects.length > 0) {
        XmlObject formula1Element = xmlObjects[0];
        formula1 = formula1Element.newCursor().getTextValue();
    }

    String formula2 = null;
    xmlObjects = x14DataValidation.selectChildren(
            new QName("http://schemas.microsoft.com/office/spreadsheetml/2009/9/main", "formula2"));
    if (xmlObjects.length > 0) {
        XmlObject formula2Element = xmlObjects[0];
        formula2 = formula2Element.newCursor().getTextValue();
    }

    this.validationConstraint = new XSSFDataValidationConstraint(validationType, operatorType, formula1, formula2);

    this.regions = new CellRangeAddressList();
    String sqref = "";
    xmlObjects = x14DataValidation.selectChildren(
            new QName("http://schemas.microsoft.com/office/excel/2006/main", "sqref"));
    if (xmlObjects.length > 0) {
        XmlObject sqrefElement = xmlObjects[0];
        sqref = sqrefElement.newCursor().getTextValue();
    }
    String [] refs = sqref.split(" ");
    for (String ref : refs) {
        CellRangeAddress address = CellRangeAddress.valueOf(ref);
        this.regions.addCellRangeAddress(address);
    }

    String allowBlank = "";
    XmlObject allowBlankAttribute = x14DataValidation.selectAttribute(new QName("allowBlank"));
    if (allowBlankAttribute != null) allowBlank = allowBlankAttribute.newCursor().getTextValue();
    this.emptyCellAllowed = ("1".equals(allowBlank) || "TRUE".equalsIgnoreCase(allowBlank));

    String showInputMessage = "";
    XmlObject showInputMessageAttribute = x14DataValidation.selectAttribute(new QName("showInputMessage"));
    if (showInputMessageAttribute != null) showInputMessage = showInputMessageAttribute.newCursor().getTextValue();
    this.showPromptBox = ("1".equals(showInputMessage) || "TRUE".equalsIgnoreCase(showInputMessage));

    String showErrorMessage = "";
    XmlObject showErrorMessageAttribute = x14DataValidation.selectAttribute(new QName("showErrorMessage"));
    if (showErrorMessageAttribute != null) showErrorMessage = showErrorMessageAttribute.newCursor().getTextValue();
    this.showErrorBox = ("1".equals(showErrorMessage) || "TRUE".equalsIgnoreCase(showErrorMessage));


}

public DataValidationConstraint getValidationConstraint() {
    return this.validationConstraint;
}

public void setErrorStyle(int errorStyle) {
    this.errorStyle = errorStyle;
}

public int getErrorStyle() {
    return this.errorStyle;
}

public void setEmptyCellAllowed(boolean allowed) {
    this.emptyCellAllowed = allowed;
}

public boolean getEmptyCellAllowed() {
    return this.emptyCellAllowed;
}

public void setSuppressDropDownArrow(boolean suppress) {
    this.suppressDropDownArrow = suppress;
}

public boolean getSuppressDropDownArrow() {
    return this.suppressDropDownArrow;
}

public void setShowPromptBox(boolean show) {
    this.showPromptBox = show;
}

public boolean getShowPromptBox() {
    return this.showPromptBox;
}

public void setShowErrorBox(boolean show) {
    this.showErrorBox = show;
}

public boolean getShowErrorBox() {
    return this.showErrorBox;
}

public void createPromptBox(String title, String text) {
    this.promptBoxTitle = title;
    this.promptBoxText = text;
}

public String getPromptBoxTitle() {
    return this.promptBoxTitle;
}

public String getPromptBoxText() {
    return this.promptBoxText;
}

public void createErrorBox(String title, String text) {
    this.errorBoxTitle = title;
    this.errorBoxText = text;
}

public String getErrorBoxTitle() {
    return this.errorBoxTitle;
}

public String getErrorBoxText() {
    return this.errorBoxText;
}

public CellRangeAddressList getRegions() {
    return this.regions;
}

附录

Java POI解析Excel的跨Sheet读取数据验证下拉值_白不懂黑的静的博客-优快云博客_getdatavalidations