支持数组公式(Ctrl+Shift+Enter)吗

Aspose.Cells Product Family
1

可以支持 数组公式 (Ctrl+Shift+Enter) 吗，在公式首尾加 {} 后，发生报错com.aspose.cells.CellsException: "Absent operand for ‘=’("Based on cell "Sheet1!D2)
有其他公式函数可以替代吗？或者有其他解决方案吗？

public static void main(String[] args) throws Exception {
    String path = "C:\\test\\test.xlsx";
    FileInputStream fis = new FileInputStream(path);
    Workbook wb = new Workbook(fis);
    Worksheet sheet = wb.getWorksheets().get(0);
    Cells cells = sheet.getCells();
    Cell cell = cells.get("D2");
    String formula = "{=TEXTJOIN(\",\",TRUE,IF(B:B=60,A:A,\"\"))}";
    cell.setValue("");
    cell.setFormula(formula);
    wb.calculateFormula(true);
    System.out.println("formula：" + formula);
    System.out.println("value：" + cell.getStringValue());
    fis.close();
    wb.dispose();
}

发现 FILTER 函数可以替代 数组公式 (Ctrl+Shift+Enter),能支持吗

public static void main(String[] args) throws Exception {
    String path = "C:\\test\\test.xlsx";
    FileInputStream fis = new FileInputStream(path);
    Workbook wb = new Workbook(fis);
    Worksheet sheet = wb.getWorksheets().get(0);
    Cells cells = sheet.getCells();
    Cell cell = cells.get("D2");
    // String formula = "{=TEXTJOIN(\",\",TRUE,IF(B:B=60,A:A,\"\"))}";
    // 发现FILTER函数可以替代，数组公式(Ctrl+Shift+Enter)
    String formula = "=TEXTJOIN(\",\",TRUE,FILTER(A:A,B:B=60))";
    cell.setValue("");
    cell.setFormula(formula);
    wb.calculateFormula(true);
    System.out.println("formula：" + formula);
    System.out.println("value：" + cell.getStringValue());
    fis.close();
    wb.dispose();
}
2

@baicai,

您可以尝试使用 setArrayFormula() 方法输入您的公式（不带“{}”）吗？ 如果您仍然发现问题，请分享您输入的 Excel XLSX 文件，我们会尽快检查。

7

发现 FILTER 函数可以替代 数组公式 (Ctrl+Shift+Enter), 当前不支持FILTER 函数，能开发 FILTER 函数吗

public static void main(String[] args) throws Exception {
    String path = "C:\\test\\test.xlsx";
    FileInputStream fis = new FileInputStream(path);
    Workbook wb = new Workbook(fis);
    Worksheet sheet = wb.getWorksheets().get(0);
    Cells cells = sheet.getCells();
    Cell cell = cells.get("D2");
    String formula = "{=TEXTJOIN(\",\",TRUE,IF(B:B=60,A:A,\"\"))}";
    formula = "=TEXTJOIN(\",\",TRUE,FILTER(A:A,B:B=60))";
    cell.setValue("");
    cell.setFormula(formula);
    wb.calculateFormula(true);
    System.out.println("formula：" + formula);
    System.out.println("value：" + cell.getStringValue());
    fis.close();
    wb.dispose();
}

test.zip (7.0 KB)

8

@baicai,

感谢您提供示例文件和代码片段。

请尝试我们的最新版本/修复：Aspose.Cells for Java v23.5。
Aspose.Cells for Java (Releases | Maven)

我已经使用您的模板 XLSX 文件和示例代码进行了测试，效果很好。

最新版本支持FILTER公式/函数，参考Aspose.Cells公式计算引擎的[支持的公式列表]。

9

@baicai,

如果使用原来的公式:

String formula = "{=TEXTJOIN(\",\",TRUE,IF(B:B=60,A:A,\"\"))}";

请删除两头的"{}"。如果您希望像在excel里用Ctrl+Shift+Enter那样让公式成为数组公式，请使用 setArrayFormula 代替setFormula().

10

感谢。
发现 FILTER 函数计算结果与 Microsoft Excel 计算结果不一致。

	public static void main(String[] args) throws Exception {
		String path = "C:\\test\\test.xlsx";
		FileInputStream fis = new FileInputStream(path);
		Workbook wb = new Workbook(fis);
		Worksheet sheet = wb.getWorksheets().get(0);
		Cells cells = sheet.getCells();
		Cell cell = cells.get("E2");
		String formula = "=TEXTJOIN(\",\",TRUE,FILTER(A:A,B:B=\"A\"))";
		cell.setValue("");
		cell.setFormula(formula);
		wb.calculateFormula(true);
		System.out.println("formula:" + formula);
		System.out.println("value:" + cell.getStringValue());
		fis.close();
		wb.dispose();
	}

test.zip (6.5 KB)

aspose.cells 计算结果为：name,Mike,Amy,Sarah
Microsoft Excel 计算结果：Mike,Sarah

11

@baicai
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-45422

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

12

@baicai,

这是为了通知您，您的问题现已解决。 此修复将包含在计划于 2023 年 6 月上旬发布的下一个版本 (Aspose.Cells v23.6) 中。下一个版本发布时，您将收到通知。

13

The issues you have found earlier (filed as CELLSJAVA-45422) have been fixed in Aspose.Cells for Java 23.6.