Java POI解析Excel的跨Sheet读取数据验证下拉值

﹏ヽ暗。殇╰゛Y 2022-11-22 13:00 814阅读 0赞

存在一个Excel文件，其中有列数据是下拉选择，且下拉的来源是在另外一个Sheet中

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l5ajEwODMxNw_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l5ajEwODMxNw_size_16_color_FFFFFF_t_70 1][]

这个时候，我们使用POI对其进行解析，想获得数据验证的个数：`sheet.getDataValidations()`

public static void main(String[] args) throws Exception {
            String filePath = "/Users/fujiexiang/ExcelWorkbook.xlsx";
    
            Workbook workbook = WorkbookFactory.create(new FileInputStream(filePath));
            Sheet sheet = workbook.getSheetAt(0);
            System.out.println("" + dataValidations + " " + dataValidations.size());
        }

这个时候，你会抓狂，明明Excel里面有下拉，代码得到的确实空，是不是很头大。。。

网上各种查找，终于。。。。。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l5ajEwODMxNw_size_16_color_FFFFFF_t_70 2][]

找重点：简单来说，就是Apache POI是基于Excel2007的xml进行解析的，而Excel2007自身是不支持数据验证跨Sheet的。

这个时候你就会惊讶的得到一个结论，POI不支持跨Sheet设置validation的读取，仿佛世界末日到了。。。。

办法总比问题多，还是有大牛的。

import java.io.FileInputStream;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddressList;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.xmlbeans.XmlObject;
    import org.apache.xmlbeans.XmlCursor;
    import javax.xml.namespace.QName;
    
    class ReadExcelDataValidaton {
    
     static java.util.List<XmlObject> getX14DataValidations(XSSFSheet sheet) {
      java.util.List<XmlObject> x14DataValidations = new java.util.ArrayList<XmlObject>();
      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, java.util.List<DataValidation> dataValidations) {
      java.util.List<XmlObject> x14DataValidations = getX14DataValidations(sheet);
      for (XmlObject x14DataValidation : x14DataValidations) {
       XSSFX14DataValidation xssfX14DataValidation = new XSSFX14DataValidation(x14DataValidation);
       dataValidations.add(xssfX14DataValidation);
      }
     }
    
     static java.util.List<DataValidation> getDataValidations(Sheet sheet) {
      @SuppressWarnings("unchecked")
      java.util.List<DataValidation> dataValidations = (java.util.List<DataValidation>)sheet.getDataValidations();
      if (sheet instanceof XSSFSheet) {
       addXSSFX14DataValidations((XSSFSheet)sheet, dataValidations);
      } 
      return dataValidations;
     }
    
     public static void main(String[] args) throws Exception {
      Workbook workbook = WorkbookFactory.create(new FileInputStream("./Excel.xlsx"));
      Sheet sheet = workbook.getSheetAt(0);
      java.util.List<DataValidation> dataValidations = getDataValidations(sheet);
      for (DataValidation dataValidation : dataValidations) {
       System.out.println(dataValidation);
       System.out.println(dataValidation.getValidationConstraint().getFormula1());
      }
      workbook.close();
     }
    }
    
    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));
    
      //TODO: complete
    
     }
    
     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;
     }
    }

在你的工程里新建上面的工具类。然后调用这个工具类的getDataValidations方法。你会发现，太阳出来了。。。

注意：你会发现上面工具类对2007模板的处理了，单对更早的2003的没处理，是不是很想说：大牛，你干嘛不送佛送到西，把它处理完善了，反正我当时是这么想的。。。

于是乎，想着照猫画虎把2003的，就是excel文件后缀是.xls的也给整出来。搞白了头，没画出来，很多接口是没有的，怎么办？

突然想，2003的连底层的接口都没有，是不是本身就不能这么设置下拉呢，于是乎，找度娘深夜聊天。。。告诉我，2003Excel本身就不能直接设置跨Sheet,知道当时的心情吗，想哭~~想笑~~~

好了，事后总结：

使用POI进行validation读取的时候，将上面代码作为工具类，调用getDataValidations方法，不要用POI本身的getDataValidations方法。

再有普及下：对于低版本的Excel设置跨Sheet下拉时，需要用到自定义名称，然后再根据自定义名称进行设置下拉即可，不在这里细说。

--------------------

参考：[https://stackoverflow.com/questions/62695000/sheet-getdatavalidations-returns-an-empty-list-when-a-cell-is-validated-by-a-s][https_stackoverflow.com_questions_62695000_sheet-getdatavalidations-returns-an-empty-list-when-a-cell-is-validated-by-a-s]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l5ajEwODMxNw_size_16_color_FFFFFF_t_70]: /images/20221120/adb732b822b045dfad518b3db2d15d1d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l5ajEwODMxNw_size_16_color_FFFFFF_t_70 1]: /images/20221120/3c0371ba929f4facb4b1bd22478716cf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l5ajEwODMxNw_size_16_color_FFFFFF_t_70 2]: /images/20221120/5d9c3d4b069e4d54974b7563f0c5c4ef.png
[https_stackoverflow.com_questions_62695000_sheet-getdatavalidations-returns-an-empty-list-when-a-cell-is-validated-by-a-s]: https://stackoverflow.com/questions/62695000/sheet-getdatavalidations-returns-an-empty-list-when-a-cell-is-validated-by-a-s