使用Java實(shí)現(xiàn)在Excel中添加動(dòng)態(tài)數(shù)組公式
前言
動(dòng)態(tài)數(shù)組公式是 Excel 引入的一項(xiàng)重要功能,它將 Excel 分為兩種風(fēng)格:Excel 365 和傳統(tǒng) Excel(2019 或更早版本)。動(dòng)態(tài)數(shù)組功能允許用戶從單個(gè)單元格中的公式返回多個(gè)結(jié)果值,并將這些值自動(dòng)填充到與公式單元格相鄰的單元格中。由于結(jié)果會(huì)溢出到多個(gè)單元格中,這也被稱為溢出范圍功能。在傳統(tǒng) Excel 中,用戶必須使用 Ctrl + Shift + Enter 來將公式識(shí)別為數(shù)組公式,否則公式結(jié)果將僅返回單個(gè)值。因此,動(dòng)態(tài)數(shù)組為用戶提供了更加便利的使用體驗(yàn)?,F(xiàn)在我們將這樣的返回多個(gè)值的公式稱為動(dòng)態(tài)數(shù)組公式。
今天小編就為大家介紹如何使用葡萄城公司的Java API 組件GrapeCity Documents for Excel(以下簡(jiǎn)稱GcExcel)實(shí)現(xiàn)在Excel中添加動(dòng)態(tài)數(shù)組。
動(dòng)態(tài)數(shù)組公式
下表總結(jié)了GcExcel所有支持的公式及其語法、說明和代碼示例:
1.FILTER **:**FILTER 公式根據(jù)您指定的條件篩選范圍或數(shù)組。 FILTER(array,include,[if_empty])
代碼片段:sheet.getRange(“I4”).setFormula2(“=FILTER(D4:E12,E4:E12>G4,\”\“)”);
2.**RANDARRAY:**RANDARRAY 公式允許您在數(shù)組中生成從 0 到 1 的隨機(jī) numbers 列表。 RANDARRAY ([rows],[columns])
代碼片段:sheet.getRange(“$D$3”).setFormula2(“=RANDARRAY(4,5)”);
3.**SEQUENCE :**SEQUENCE 公式允許您在數(shù)組中生成序列號(hào)列表。 SEQUENCE(rows,[columns],[start],[step])
代碼片段:sheet.getRange(“$D$4”).setFormula2(“=SEQUENCE(10,100,-10)”);
4.**SORTBY :**SORTBY 公式允許您根據(jù)相應(yīng)范圍或數(shù)組中的值對(duì)范圍或數(shù)組進(jìn)行排序。 SORTBY(array, by_array1, [sort_order1], [[by_array2], [sort_order2]],…)
代碼片段:
5.**SORT:**SORT 公式用于按升序或降序?qū)Ψ秶驍?shù)組進(jìn)行排序。 SORT(array, [sort_index], [sort_order], [by_col])
6.**UNIQUE:**UNIQUE 公式允許您從項(xiàng)目范圍或數(shù)組中返回唯一列表。UNIQUE(array, [by_col], occurs_once])
代碼片段:
詳細(xì)代碼請(qǐng)點(diǎn)擊這里。
@ 運(yùn)算符
@ 運(yùn)算符(也稱為隱式**交集運(yùn)算符)**實(shí)現(xiàn)一種稱為隱式交集的公式行為,該行為將一組值減少為單個(gè)值。這適用于返回多個(gè)值的數(shù)組公式,在這種情況下,將根據(jù)單元格位置(即行和列)返回單個(gè)值。在老版本 Excel 中,這是默認(rèn)行為,因此不需要顯式運(yùn)算符。但是,在 Excel 365 中,所有公式都是數(shù)組公式,因此,如果您不希望數(shù)組公式溢出,則可以在公式前面加上 @ 運(yùn)算符,它只會(huì)返回一個(gè)值。
實(shí)現(xiàn)代碼:
public void ImplicitIntersection() { Workbook wb = new Workbook(); //初始化工作表 IWorksheet sheet = wb.getWorksheets().get(0); sheet.setName("IMPLICIT INTERSECTION"); //添加樣例數(shù)據(jù) sheet.getRange("$D$3:$D12").setValue(new Object[] { "Products", "Apple", "Grape", "Pear", "Banana", "Apple", "Grape", "Pear", "Banana", "Banana" }); sheet.getRange("$E$3").setValue("Unique Products"); //添加含有隱式運(yùn)算符的動(dòng)態(tài)數(shù)組公式 sheet.getRange("$E$4").setFormula2("=@UNIQUE(D4:D12)"); wb.save("output/ImplicitIntersection.xlsx"); }
實(shí)現(xiàn)效果:
溢出范圍參考 (#)
動(dòng)態(tài)數(shù)組公式將其多值結(jié)果溢出到的單元格范圍稱為**溢出范圍。**每當(dāng)單擊溢出范圍中的任何單元格時(shí),溢出范圍都會(huì)以藍(lán)色邊框突出顯示,表示該范圍中的所有值都是通過該范圍左上角單元格中的公式計(jì)算得出的。
溢出范圍參考運(yùn)算符用于引用此溢出范圍。要引用溢出范圍,請(qǐng)?jiān)谝绯龇秶凶笊辖菃卧竦牡刂泛蠓胖靡粋€(gè)主題標(biāo)簽或井號(hào) (#)。
例如,若要查找單元格 E4 中應(yīng)用的 UNIQUE 公式提取了多少個(gè)唯一值,請(qǐng)?zhí)峁?duì) COUNTA 公式的溢出范圍引用,如下面的代碼所示:
public void SpillReference() { Workbook wb = new Workbook(); //初始化工作表 IWorksheet sheet = wb.getWorksheets().get(0); sheet.setName("SPILL REFERENCE"); //添加樣例數(shù)據(jù) sheet.getRange("$D$3:$D12").setValue(new Object[] { "Products", "Apple", "Grape", "Pear", "Banana", "Apple", "Grape", "Pear", "Banana", "Banana" }); sheet.getRange("$E$3").setValue("Unique Products"); sheet.getRange("F3").setValue("Unique Products Count"); //設(shè)置動(dòng)態(tài)數(shù)組公式 sheet.getRange("$E$4").setFormula2("=UNIQUE(D4:D12)"); //設(shè)置帶有溢出范圍引用的公式 sheet.getRange("$F$4").setFormula2("=COUNTA(E4#)"); wb.save("output/SpillReference.xlsx"); }
實(shí)現(xiàn)效果:
現(xiàn)在,我們已經(jīng)了解了 GcExcel 如何在 Java 中為動(dòng)態(tài)數(shù)組公式提供支持,下面小編將用一個(gè)簡(jiǎn)單的例子展示動(dòng)態(tài)數(shù)組公式的用途。
用例:使用動(dòng)態(tài)數(shù)組在 Excel 中創(chuàng)建交互式圖表
考慮這樣一個(gè)場(chǎng)景:我們有一些表格數(shù)據(jù),想用柱狀圖來展示。通常情況下,表格數(shù)據(jù)有很多行和列,但在制作圖表時(shí),我們需要按照某些條件來選擇部分?jǐn)?shù)據(jù)。為了提取所需數(shù)據(jù),我們需要根據(jù)一些條件來進(jìn)行篩選,這就是動(dòng)態(tài)數(shù)組公式的應(yīng)用場(chǎng)景。我們使用FILTER函數(shù)根據(jù)定義的條件來篩選表格數(shù)據(jù),一旦獲取到篩選后的數(shù)據(jù),我們就可以用它來創(chuàng)建柱狀圖。
柱狀圖繪制來自預(yù)定義單元格范圍的數(shù)據(jù),該范圍綁定到圖表系列。因此,如果過濾后的行數(shù)發(fā)生變化,例如用戶更改了“Show”列中的值,就會(huì)導(dǎo)致FILTER公式重新計(jì)算,從而篩選后的數(shù)據(jù)單元格范圍也會(huì)發(fā)生變化。但是,圖表系列始終引用相同的單元格范圍,因此任何新的篩選數(shù)據(jù)行如果不在系列單元格范圍內(nèi),則不會(huì)在圖表上繪制。但是,我們希望所有經(jīng)過篩選的數(shù)據(jù)都能在圖表上顯示,這就需要將簡(jiǎn)單靜態(tài)圖表轉(zhuǎn)換為交互式圖表,以便刷新其繪圖區(qū)域來繪制所有篩選數(shù)據(jù)的值。
因此,請(qǐng)繼續(xù)了解實(shí)現(xiàn)上述定義方案的詳細(xì)步驟。
步驟 1:加載數(shù)據(jù)文件
我們將首先在 GcExcel 工作簿中加載源數(shù)據(jù),該數(shù)據(jù)將用于繪制圖表。
源數(shù)據(jù)文件下載(源文件文章系統(tǒng)的附件中)
將 Excel 文件加載到Workbook 中:
//創(chuàng)建一個(gè)工作簿,并打開數(shù)據(jù)文件 Workbook workbook = new GrapeCity.Documents.Excel.Workbook(); workbook.Open("xlsx\\SalesReport_sourcedata.xlsx");
加載效果:
步驟 2:使用動(dòng)態(tài)數(shù)組公式提取圖表數(shù)據(jù)
現(xiàn)在將開始提取相關(guān)數(shù)據(jù),以便在圖表中進(jìn)行繪制。我們將根據(jù)產(chǎn)品的銷售額繪制圖表。因此,圖表數(shù)據(jù)應(yīng)包含來自產(chǎn)品列和金額列的數(shù)值。從這兩列中提取特定數(shù)值的標(biāo)準(zhǔn)是基于展示列。我們將從展示列的值為1的每一行中提取產(chǎn)品和金額數(shù)值,并在圖表上繪制這些篩選后的數(shù)據(jù)。
使用FILTER函數(shù)從產(chǎn)品列中過濾數(shù)據(jù),然后使用SORTBY函數(shù)按照金額列的值進(jìn)行降序排序。因此,我們將通過連接FILTER和SORTBY這兩個(gè)動(dòng)態(tài)數(shù)組函數(shù)來創(chuàng)建一個(gè)動(dòng)態(tài)數(shù)組公式。
將使用VLOOKUP函數(shù)提取金額列中的數(shù)值,并將FILTER函數(shù)的溢出范圍作為參數(shù)傳遞,以便獲取與每個(gè)篩選產(chǎn)品對(duì)應(yīng)的金額值。
以下是相應(yīng)的代碼片段:
IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.getRange("G3").setValue("Product"); worksheet.getRange("H3").setValue("Sales"); worksheet.getRange("G3").getFont().setBold(true); worksheet.getRange("H3").getFont().setBold(true); //使用動(dòng)態(tài)數(shù)組公式來獲取產(chǎn)品列的值 worksheet.getRange("G4").setFormula2("=SORTBY(FILTER(B4:B13,E4:E13=1),FILTER(D4:D13,E4:E13=1),-1)"); //引用溢出區(qū)域來獲取金額列的值 worksheet.getRange("H4").setFormula2("=VLOOKUP(G4#,B4:D13,3,FALSE)");
以下是對(duì)工作表的快速瀏覽,其中包含 G 列和 H 列中經(jīng)過篩選和排序的數(shù)據(jù),并突出顯示了溢出范圍:
步驟 3:使用篩選后的數(shù)據(jù)添加簡(jiǎn)單圖表
上述步驟已生成要在圖表中繪制的數(shù)據(jù)。在這里,我們通過向工作表添加兩個(gè)命名范圍來添加一個(gè)簡(jiǎn)單的圖表來展示銷售額,其中一個(gè)引用篩選出的數(shù)據(jù)中的“產(chǎn)品”列,另一個(gè)引用“銷售”列。然后,這些命名區(qū)域?qū)⒂糜趧?chuàng)建圖表系列。代碼如下所示:
//添加數(shù)據(jù)引用 wb.getNames().add("Product", "=Sheet1!$G$4:$G$8"); wb.getNames().add("Sales", "=Sheet1!$H$4:$H$8"); //添加圖表 IShape chartShape = sheet.getShapes().addChart(ChartType.ColumnClustered, 600, 170, 500, 300); IChart chart = chartShape.getChart(); //添加系列 ISeries series = chart.getSeriesCollection().newSeries(); series.setFormula("=SERIES(\"Sales\", Product, Sales, 1)");
生成的圖表如下所示:
步驟 4:使圖表具有交互性
讓我們首先了解使用此圖表具有交互性的需求。觀察下面的 GIF,會(huì)發(fā)現(xiàn)更改“Show”列中的值會(huì)重新計(jì)算動(dòng)態(tài)數(shù)組公式并更新過濾后的數(shù)據(jù)單元格范圍。但是,僅當(dāng)新篩選的數(shù)據(jù)位于單元格區(qū)域 G3:H8(即系列單元格區(qū)域)時(shí),此更改才會(huì)在圖表中可見。如果過濾后的數(shù)據(jù)不在單元格范圍 G3:H8 中,則它不會(huì)顯示在圖表中,如本示例中過濾后的數(shù)據(jù)范圍擴(kuò)展到 G3:H10,但圖表僅呈現(xiàn)來自 G3:H8 的數(shù)據(jù):
此圖表應(yīng)繪制所有過濾掉的數(shù)據(jù)。為此,我們必須使用溢出范圍參考更新序列單元格范圍,這將確保序列單元格范圍始終包含包含過濾數(shù)據(jù)的完整單元格范圍。下面是示例代碼片段,該代碼片段更新命名區(qū)域以使用溢出范圍引用,從而根據(jù)動(dòng)態(tài)數(shù)組公式的結(jié)果使引用的單元格區(qū)域動(dòng)態(tài)化。然后,這些動(dòng)態(tài)命名區(qū)域用于創(chuàng)建圖表系列,使圖表具有交互性。
代碼如下所示:
//添加命名引用,用于圖表數(shù)據(jù) wb.getNames().add("Product", "=Sheet1!$G$4#"); wb.getNames().add("Sales", "=Sheet1!$H$4#"); //添加圖表 IShape chartShape = sheet.getShapes().addChart(ChartType.ColumnClustered, 600, 170, 500, 300); IChart chart = chartShape.getChart(); //添加系列 ISeries series = chart.getSeriesCollection().newSeries(); series.setFormula("=SERIES(\"Sales\", Product, Sales, 1)");
實(shí)現(xiàn)效果:
到此這篇關(guān)于使用Java實(shí)現(xiàn)在Excel中添加動(dòng)態(tài)數(shù)組公式的文章就介紹到這了,更多相關(guān)Java Excel添加動(dòng)態(tài)數(shù)組公式內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
springboot配合Thymeleaf完美實(shí)現(xiàn)遍歷功能
Thymeleaf顯然是一個(gè)開發(fā)頁面的技術(shù),現(xiàn)在各種前端技術(shù)層出不窮,比如現(xiàn)在主流的Vue、React、AngularJS等。這篇文章主要介紹了springboot配合Thymeleaf完美實(shí)現(xiàn)遍歷,需要的朋友可以參考下2021-09-09Spring Security實(shí)現(xiàn)不同接口安全策略方法詳解
這篇文章主要介紹了Spring Security實(shí)現(xiàn)不同接口安全策略方法詳解,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-09-09ElasticSearch學(xué)習(xí)之多條件組合查詢驗(yàn)證及示例分析
這篇文章主要為大家介紹了ElasticSearch 多條件組合查詢驗(yàn)證及示例分析,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-02-02SpringBoot自定義Starter與自動(dòng)配置實(shí)現(xiàn)方法詳解
在Spring Boot官網(wǎng)為了簡(jiǎn)化我們的開發(fā),已經(jīng)提供了非常多場(chǎng)景的Starter來為我們使用,即便如此,也無法全面的滿足我們實(shí)際工作中的開發(fā)場(chǎng)景,這時(shí)我們就需要自定義實(shí)現(xiàn)定制化的Starter2023-02-02如何將Java對(duì)象轉(zhuǎn)換為JSON實(shí)例詳解
有時(shí)候需要將對(duì)象轉(zhuǎn)換為JSON格式,所以這篇文章主要給大家介紹了關(guān)于如何將Java對(duì)象轉(zhuǎn)換為JSON的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-08-08Java集合之Set、HashSet、LinkedHashSet和TreeSet深度解析
這篇文章主要介紹了Java集合之Set、HashSet、LinkedHashSet和TreeSet深度解析,List是有序集合的根接口,Set是無序集合的根接口,無序也就意味著元素不重復(fù),更嚴(yán)格地說,Set集合不包含一對(duì)元素e1和e2 ,使得e1.equals(e2) ,并且最多一個(gè)空元素,需要的朋友可以參考下2023-09-09