0 程式碼量雜談

icarusliu81發表於2018-06-21

在使用POI匯出Excel檔案時，如果某個單元格需要設定成下拉選擇的方式，並且下拉選單數目大於255時，直接寫序列將會報錯：

java.lang.IllegalArgumentException: String literals in formulas can't be bigger than 255 characters ASCII

此時可以增加一個隱藏的Sheet，然後在需要設定下拉的單元格中進行引用。也就是說單元格的來源直接這樣指定：

=$A$1:$A$5

但這樣做沒有辦法進行模糊匹配，因此再通過Excel的OFFSET等函式可以達到要求。
Java程式碼實現如下：

CellRangeAddressList regions = new CellRangeAddressList(1, 10000, 0, 0);
DVConstraint constraint = DVConstraint.createFormulaListConstraint("OFFSET(deviceTypeSheet!$A$1,MATCH(A6&\"*\"," +
                    "deviceTypeSheet!$A:$A,0)-1,,COUNTIF(deviceTypeSheet!$A:$A,A6&\"*\"),)");
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
// 設定輸入無效資料時不顯示出錯警告
dataValidation.setShowErrorBox(false);
sheet.addValidationData(dataValidation);

其中最關鍵的就是通過createFormulaListConstraint設定的語句：

OFFSET(deviceTypeSheet!$A$1,MATCH(A6&"*",deviceTypeSheet!$A:$A,0)-1,,COUNTIF(deviceTypeSheet!$A:$A,A6&"*"),)

這幾個函式的說明如下：
OFFSET(reference,rows,cols,height,width)： 表示對某個區域的引用，refrerence指明的是區域，rows指明需要引用的開始行，cols表示的是需要引用該區域的開始列；height表示的是引用的區域行數；width表示的是引用的區域的列數；
如offset(A

A
1, 2, 2, 4, 4)表示的是引用從A
A
1這個點第3行第3列開始，跨度為3行4列的區域。
MATCH（lookup_value,lookuparray,match-type）: 表示從lookuparray中查詢滿足lookup_value表示式的值的物件，並返回其索引值。
countif（range，criteria）: 與match類似，不過返回的是命中的數量

因此上面的語句就很容易理解了: 當在單元格中輸入一個值後，會去指定的區域裡面查詢以輸入值開頭的值，查詢命中的起點並且數目，然後再通過OFFSET函式來獲取命中的序列作為下拉的來源。

相關文章