We're pleased to announce the release of Spire.XLS for Java 16.7.3. This version supports converting a specified cell range to HTML, embedding checkbox controls in cells, and converting Excel to JSON data. Meanwhile, some issues that occurred when loading Excel documents, copying cell ranges or worksheets, and retrieving the print area have also been successfully fixed. More details are listed below.
Here is a list of changes made in this release
| Category | ID | Description |
| New Feature | SPIREXLS-5934 | Adds a new property to control whether to retain frozen rows and columns when converting Excel to HTML, with the default value set to false.
Workbook wb = new Workbook(); wb.loadFromFile(inputFile); HTMLOptions.Default.isSaveFreezedPanes(true); wb.saveToFile(outputFile, FileFormat.HTML); wb.dispose(); |
| New Feature | SPIREXLS-5938 | Adds the isSavePicInRelativePath property to control whether images are saved using relative paths when converting Excel to Markdown.
Workbook wb = new Workbook();
wb.loadFromFile(inputFile);
MarkdownOptions markdownOptions = new MarkdownOptions();
markdownOptions.isSavePicInRelativePath(true);
wb.saveToMarkdown(outputFile, markdownOptions);
wb.dispose();
|
| New Feature | SPIREXLS-5948 | Adds support for the BYROW and BYCOL functions.
sheet.getRange().get("G7").setFormula("=BYROW(B7:F7, LAMBDA(row, AVERAGE(row)))");
sheet.getRange().get("B8").setFormula("=BYCOL(B2:B7, LAMBDA(col, AVERAGE(col)))");
|
| New Feature | SPIREXLS-5975 | Adds the isSaveHyperlinkAsRef property to control whether to save hyperlinks as Markdown reference-style links when converting Excel to Markdown.
Workbook wb = new Workbook(); wb.loadFromFile(inputFile); MarkdownOptions markdownOptions = new MarkdownOptions(); markdownOptions.isSaveHyperlinkAsRef(true); wb.saveToMarkdown(outputFile, markdownOptions); wb.dispose(); |
| New Feature | SPIREXLS-5980 | Supports converting a specified cell range to 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());
|
| New Feature | SPIREXLS-5982 | Adds support for the XMATCH function.
sheet.getRange().get("C4").setFormula("=XMATCH(\"Lucy\", A2:A5)");
|
| New Feature | SPIREXLS-5983 | Adds support for swapping rows and columns.
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("Transpose A1:C4 to D2:G3:");
sheet.getRange().get("A1:C4").copy(sheet.getRange().get("D2:G3"), options);
sheet.getRange().get("D4").setText("Transpose A1:B5 to D5:");
sheet.getRange().get("A1:B5").copy(sheet.getRange().get("D5"), options);
options = EnumSet.of(CopyRangeOptions.Transpose, CopyRangeOptions.CopyStyles);
sheet.getRange().get("D9").setText("Transpose A10 to D10:");
sheet.getRange().get("A10").copy(sheet.getRange().get("D10"), options);
sheet.getRange().get("D11").setText("Transpose A11:B11 to D12:");
sheet.getRange().get("A11:B11").copy(sheet.getRange().get("D12"), options);
options = EnumSet.of(CopyRangeOptions.Transpose, CopyRangeOptions.CopyConditionalFormats);
sheet.getRange().get("D16").setText("Transpose B17:B20 to D17:");
sheet.getRange().get("B17:B20").copy(sheet.getRange().get("D17"), options);
options = EnumSet.of(CopyRangeOptions.Transpose, CopyRangeOptions.CopyStyles);
sheet.getRange().get("D19").setText("Transpose B17:B20 to D20:");
sheet.getRange().get("B17:B20").copy(sheet.getRange().get("D20"), options);
options = EnumSet.of(CopyRangeOptions.Transpose);
sheet.getRange().get("D22").setText("Transpose B17:B20 to D23:");
sheet.getRange().get("B17:B20").copy(sheet.getRange().get("D23"), options);
workbook.saveToFile(outputFile);
sheet.dispose();
workbook.dispose();
|
| New Feature | SPIREXLS-5990 | Adds support for embedding checkbox controls in cells and setting their checked states.
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();
|
| New Feature | SPIREXLS-6020 | Adds support for the Data Table (What-If Analysis) feature, covering creation, editing, deletion, summary generation, and consolidation.
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 currentChangePercentage_Values = new ArrayList() {{ add(0.23); add(0.8); add(1.1); add(0.5); add(0.35); add(0.2); }};
List increasedChangePercentage_Values = new ArrayList() {{ add(0.45); add(0.56); add(0.9); add(0.5); add(0.58); add(0.43); }};
List decreasedChangePercentage_Values = new ArrayList() {{ add(0.3); add(0.2); add(0.5); add(0.3); add(0.5); add(0.23); }};
List currentQuantity_Values = new ArrayList() {{ add(1500); add(3000); add(5000); add(4000); add(500); add(4000); }};
List increasedQuantity_Values = new ArrayList() {{ add(1000); add(5000); add(4500); add(3900); add(10000); add(8900); }};
List decreasedQuantity_Values = new ArrayList() {{ 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);// Delete by index
scenarios.removeScenarioByName("two");// Delete by name
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 currentChangePercentage_Values = new ArrayList() {{ add(0.23); add(0.8); add(1.1); add(0.5); add(0.35); add(0.2); }};
List increasedChangePercentage_Values = new ArrayList() {{ add(0.45); add(0.56); add(0.9); add(0.5); add(0.58); add(0.43); }};
List decreasedChangePercentage_Values = new ArrayList() {{ add(0.3); add(0.2); add(0.5); add(0.3); add(0.5); add(0.23); }};
List currentQuantity_Values = new ArrayList() {{ add(1500); add(3000); add(5000); add(4000); add(500); add(4000); }};
List increasedQuantity_Values = new ArrayList() {{ add(1000); add(5000); add(4500); add(3900); add(10000); add(8900); }};
List decreasedQuantity_Values = new ArrayList() {{ 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);// This sheet contains Scenarios //Merge the scenario worksheet1.getScenarios().merge(worksheet2); //Saving the workbook wb.saveToFile(outputFile, ExcelVersion.Version2013); wb.dispose(); |
| New Feature | SPIREXLS-6162 | Adds support for converting Excel to JSON data.
Workbook workbook = new Workbook();
workbook.loadFromFile(inputFile);
workbook.saveToFile(outputFile, FileFormat.Json);
workbook.dispose();
|
| Bug Fix | SPIREXLS-6160 | Fixes the issue where an "Invalid cell name" exception was thrown when loading an Excel document. |
| Bug Fix | SPIREXLS-6163 | Fixes the issue where extra vertical lines appeared on chart axes when copying a cell range. |
| Bug Fix | SPIREXLS-6166 | Fixes the issue where retrieving the print area of an Excel document failed. |
| Bug Fix | SPIREXLS-5940 | Fixes the issue where a "System.ArgumentOutOfRangeException" exception was thrown when copying a worksheet containing a Slicer. |
Click the link below to download Spire.XLS for Java 16.7.3:
