| 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. |
