Spire.XLS for Java 16.7.3 现已正式发布。该版本新增单元格区域转 HTML 功能，支持在单元格中嵌入复选框控件，以及支持转换 Excel 到 JSON 数据。同时，一些在加载 Excel 文档、拷贝单元格范围或工作表、获取打印区域时出现的问题也得以成功被修复。更多详情如下。

新功能:

• 新增属性支持在转换 Excel 到 HTML 时控制是否保留冻结的行列，默认为 false。

Workbook wb = new Workbook();
wb.loadFromFile(inputFile);
HTMLOptions.Default.isSaveFreezedPanes(true);
wb.saveToFile(outputFile, FileFormat.HTML);
wb.dispose();

• 新增 isSavePicInRelativePath 属性，支持在转换 Excel 到 Markdown 时控制图片是否以相对路径形式保存。

Workbook wb = new Workbook();
        wb.loadFromFile(inputFile);
        MarkdownOptions markdownOptions = new MarkdownOptions();
        markdownOptions.isSavePicInRelativePath(true);
        wb.saveToMarkdown(outputFile, markdownOptions);
        wb.dispose();

• 支持 BYROW 和 BYCOL 函数。

sheet.getRange().get("G7").setFormula("=BYROW(B7:F7, LAMBDA(row, AVERAGE(row)))");
sheet.getRange().get("B8").setFormula("=BYCOL(B2:B7, LAMBDA(col, AVERAGE(col)))");

• 新增 isSaveHyperlinkAsRef 属性，支持在转换 Excel 到 Markdown 时控制是否将超链接保存为 Markdown 引用样式。

Workbook wb = new Workbook();
wb.loadFromFile(inputFile);
MarkdownOptions markdownOptions = new MarkdownOptions();
markdownOptions.isSaveHyperlinkAsRef(true);
wb.saveToMarkdown(outputFile, markdownOptions);
wb.dispose();

• 支持单元格区域转 HTML 功能。

StringBuilder sb = new StringBuilder();
Workbook workbook = new Workbook();
workbook.loadFromFile(inputFile);

Worksheet sheet = workbook.getWorksheets().get(0);
CellRange cell = sheet.getRange().get("A1:B3");
sb.append(cell.getHtmlString());

• 支持 XMATCH 函数。

sheet.getRange().get("C4").setFormula("=XMATCH(\"王五\", A2:A5)");

• ● 支持行列置换功能。

Workbook workbook = new Workbook();
        workbook.loadFromFile(inputFile);
        Worksheet sheet = workbook.getWorksheets().get(0);

        EnumSet<CopyRangeOptions> options = EnumSet.of(CopyRangeOptions.Transpose, CopyRangeOptions.All);
        sheet.getRange().get("D1").setText("A1:C4置换到D2:G3：");
        sheet.getRange().get("A1:C4").copy(sheet.getRange().get("D2:G3"), options);
        sheet.getRange().get("D4").setText("A1:B5置换到D5：");
        sheet.getRange().get("A1:B5").copy(sheet.getRange().get("D5"), options);

        options = EnumSet.of(CopyRangeOptions.Transpose, CopyRangeOptions.CopyStyles);
        sheet.getRange().get("D9").setText("A10置换到D10：");
        sheet.getRange().get("A10").copy(sheet.getRange().get("D10"), options);
        sheet.getRange().get("D11").setText("A11:B11置换到D12：");
        sheet.getRange().get("A11:B11").copy(sheet.getRange().get("D12"), options);

        options = EnumSet.of(CopyRangeOptions.Transpose, CopyRangeOptions.CopyConditionalFormats);
        sheet.getRange().get("D16").setText("B17:B20置换到D17：");
        sheet.getRange().get("B17:B20").copy(sheet.getRange().get("D17"), options);

        options = EnumSet.of(CopyRangeOptions.Transpose, CopyRangeOptions.CopyStyles);
        sheet.getRange().get("D19").setText("B17:B20置换到D20：");
        sheet.getRange().get("B17:B20").copy(sheet.getRange().get("D20"), options);

        options = EnumSet.of(CopyRangeOptions.Transpose);
        sheet.getRange().get("D22").setText("B17:B20置换到D23：");
        sheet.getRange().get("B17:B20").copy(sheet.getRange().get("D23"), options);

        workbook.saveToFile(outputFile);

        sheet.dispose();
        workbook.dispose();

• 支持在单元格中嵌入复选框控件，并支持设置复选框的选中状态。

Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);

XlsRange range = (XlsRange)sheet.getRange().get("A1");
range.insertEmbedCheckBox();
range.setEmbedCheckBoxCheckState(true);

workbook.saveToFile(outputFile);
workbook.dispose();

• 支持数据模拟分析功能，涵盖创建、编辑、删除到摘要生成及合并。

Workbook wb = new Workbook();
        wb.loadFromFile(inputFile);
        Worksheet worksheet = wb.getWorksheets().get(0);

        // Access the collection of scenarios in the worksheet
        XlsScenarioCollection scenarios = worksheet.getScenarios();

        //Initialize list objects with different values for scenarios
        List<Object> currentChangePercentage_Values = new ArrayList<Object>() {{ add(0.23); add(0.8); add(1.1); add(0.5); add(0.35); add(0.2); }};
        List<Object> increasedChangePercentage_Values = new ArrayList<Object>() {{ add(0.45); add(0.56); add(0.9); add(0.5); add(0.58); add(0.43); }};
        List<Object> decreasedChangePercentage_Values = new ArrayList<Object>() {{ add(0.3); add(0.2); add(0.5); add(0.3); add(0.5); add(0.23); }};
        List<Object> currentQuantity_Values = new ArrayList<Object>() {{ add(1500); add(3000); add(5000); add(4000); add(500); add(4000); }};
        List<Object> increasedQuantity_Values = new ArrayList<Object>() {{ add(1000); add(5000); add(4500); add(3900); add(10000); add(8900); }};
        List<Object> decreasedQuantity_Values = new ArrayList<Object>() {{ add(1000); add(2000); add(3000); add(3000); add(300); add(4000); }};

        //Add scenarios in the worksheet with different values for the same cells
        scenarios.add("Current % of Change", worksheet.getRange().get("F5:F10"), currentChangePercentage_Values);
        scenarios.add("Increased % of Change", worksheet.getRange().get("F5:F10"), increasedChangePercentage_Values);
        scenarios.add("Decreased % of Change", worksheet.getRange().get("F5:F10"), decreasedChangePercentage_Values);
        scenarios.add("Current Quantity", worksheet.getRange().get("D5:D10"), currentQuantity_Values);
        scenarios.add("Increased Quantity", worksheet.getRange().get("D5:D10"), increasedQuantity_Values);
        scenarios.add("Decreased Quantity", worksheet.getRange().get("D5:D10"), decreasedQuantity_Values);

        //Saving the workbook
        wb.saveToFile(outputFile, ExcelVersion.Version2013);
        wb.dispose();

Workbook wb = new Workbook();
        wb.loadFromFile(inputFile);
        Worksheet worksheet = wb.getWorksheets().get(0);

        // Access the collection of scenarios in the worksheet
        XlsScenarioCollection scenarios = worksheet.getScenarios();
        XlsScenario scenario1 = scenarios.get(0);
        XlsScenario scenario2 = scenarios.get(1);

        //Modify the scenario
        scenario1.setVariableCells(worksheet.getRange().get("A1:A5"), scenario2.getValues());

        CellRange sourceCell = worksheet.getRange().get("B1:B5");
        scenario2.setVariableCells(sourceCell, scenario2.getValues());

        scenario1.show();
        scenario2.show();

        //Saving the workbook
        wb.saveToFile(outputFile, ExcelVersion.Version2013);
        wb.dispose();

Workbook wb = new Workbook();
        wb.loadFromFile(inputFile);
        Worksheet worksheet = wb.getWorksheets().get(0);

        // Access the collection of scenarios in the worksheet
        XlsScenarioCollection scenarios = worksheet.getScenarios();

        //delete the scenario
        scenarios.removeScenarioAt(0);
        scenarios.removeScenarioByName("two");

        String content = "";
        content += "Count:" + scenarios.getCount() + "\n";
        content += "ContainsScenario:" + scenarios.containsScenario("two") + "\n";
        content += "ContainsScenario:" + scenarios.containsScenario("one") + "\n";
        TestUtil.writeAllText(outputFile, content);

        //Saving the workbook
        wb.dispose();

Workbook wb = new Workbook();
        wb.loadFromFile(inputFile);
        Worksheet worksheet = wb.getWorksheets().get(0);

        // Access the collection of scenarios in the worksheet
        XlsScenarioCollection scenarios = worksheet.getScenarios();

        //Initialize list objects with different values for scenarios
        List<Object> currentChangePercentage_Values = new ArrayList<Object>() {{ add(0.23); add(0.8); add(1.1); add(0.5); add(0.35); add(0.2); }};
        List<Object> increasedChangePercentage_Values = new ArrayList<Object>() {{ add(0.45); add(0.56); add(0.9); add(0.5); add(0.58); add(0.43); }};
        List<Object> decreasedChangePercentage_Values = new ArrayList<Object>() {{ add(0.3); add(0.2); add(0.5); add(0.3); add(0.5); add(0.23); }};
        List<Object> currentQuantity_Values = new ArrayList<Object>() {{ add(1500); add(3000); add(5000); add(4000); add(500); add(4000); }};
        List<Object> increasedQuantity_Values = new ArrayList<Object>() {{ add(1000); add(5000); add(4500); add(3900); add(10000); add(8900); }};
        List<Object> decreasedQuantity_Values = new ArrayList<Object>() {{ add(1000); add(2000); add(3000); add(3000); add(300); add(4000); }};

        //Add scenarios in the worksheet with different values for the same cells
        scenarios.add("Current % of Change", worksheet.getRange().get("F5:F10"), currentChangePercentage_Values);
        scenarios.add("Increased % of Change", worksheet.getRange().get("F5:F10"), increasedChangePercentage_Values);
        scenarios.add("Decreased % of Change", worksheet.getRange().get("F5:F10"), decreasedChangePercentage_Values);
        scenarios.add("Current Quantity", worksheet.getRange().get("D5:D10"), currentQuantity_Values);
        scenarios.add("Increased Quantity", worksheet.getRange().get("D5:D10"), increasedQuantity_Values);
        scenarios.add("Decreased Quantity", worksheet.getRange().get("D5:D10"), decreasedQuantity_Values);

        //Create Summary
        worksheet.getScenarios().summary(worksheet.getRange().get("L7"));

        //Saving the workbook
        wb.saveToFile(outputFile, ExcelVersion.Version2013);
        wb.dispose();

Workbook wb = new Workbook();
        wb.loadFromFile(inputFile);
        Worksheet worksheet1 = wb.getWorksheets().get(0);
        Worksheet worksheet2 = wb.getWorksheets().get(1);

        //Merge the scenario
        worksheet1.getScenarios().merge(worksheet2);

        //Saving the workbook
        wb.saveToFile(outputFile, ExcelVersion.Version2013);
        wb.dispose();

• 支持转换 Excel 到 JSON 数据。

Workbook workbook = new Workbook();
        workbook.loadFromFile(inputFile);
        workbook.saveToFile(outputFile, FileFormat.Json);
        workbook.dispose();

问题修复:

• 修复了加载 Excel 文档时程序抛出“Invalid cell name”异常的问题。
• 修复了拷贝单元格范围时图表坐标轴多出竖线的问题。
• 修复了获取 Excel 文档打印区域失败的问题。
• 修复了复制包含 Slicer 的工作表时程序抛出“System.ArgumentOutOfRangeException”异常的问题。

https://www.e-iceblue.cn/Downloads/Spire-XLS-JAVA.html