We're pleased to announce the release of Spire.XLS for Java 15.4.0. The latest version supports creating a slicer using table data. Moreover, some known bugs are fixed successfully in this update, such as the issue that OLE objects failed to open correctly after converting Excel to XLSB format. More details are listed below.
Here is a list of changes made in this release
| Category | ID | Description |
| New feature | - | Supports creating a slicer using table data.
Workbook wb = new Workbook();
//Get the first worksheet of workbook
Worksheet worksheet = wb.getWorksheets().get(0);
worksheet.getRange().get("A1").setValue("fruit");
worksheet.getRange().get("A2").setValue("grape");
worksheet.getRange().get("A3").setValue("blueberry");
worksheet.getRange().get("A4").setValue("kiwi");
worksheet.getRange().get("A5").setValue("cherry");
worksheet.getRange().get("A6").setValue("grape");
worksheet.getRange().get("A7").setValue("blueberry");
worksheet.getRange().get("A8").setValue("kiwi");
worksheet.getRange().get("A9").setValue("cherry");
worksheet.getRange().get("B1").setValue("year");
worksheet.getRange().get("B2").setValue2(2020);
worksheet.getRange().get("B3").setValue2(2020);
worksheet.getRange().get("B4").setValue2(2020);
worksheet.getRange().get("B5").setValue2(2020);
worksheet.getRange().get("B6").setValue2(2021);
worksheet.getRange().get("B7").setValue2(2021);
worksheet.getRange().get("B8").setValue2(2021);
worksheet.getRange().get("B9").setValue2(2021);
worksheet.getRange().get("C1").setValue("amount");
worksheet.getRange().get("C2").setValue2(50);
worksheet.getRange().get("C3").setValue2(60);
worksheet.getRange().get("C4").setValue2(70);
worksheet.getRange().get("C5").setValue2(80);
worksheet.getRange().get("C6").setValue2(90);
worksheet.getRange().get("C7").setValue2(100);
worksheet.getRange().get("C8").setValue2(110);
worksheet.getRange().get("C9").setValue2(120);
//Get slicer collection
XlsSlicerCollection slicers = worksheet.getSlicers();
//Create a super table with the data from the specific cell range.
IListObject table = worksheet.getListObjects().create("Super Table", worksheet.getRange().get("A1:C9"));
int count = 3;
int index = 0;
for (Object styletype : SlicerStyleType.values())
{
SlicerStyleType type = (SlicerStyleType)styletype;
count += 5;
//Add a Slicer through pivot table data : here invoke Add(IListObject, string, int) api.
String range = "E" + count;
index = slicers.add(table, range.toString(), 0);
//Style setting
XlsSlicer xlsSlicer = slicers.get(index);
xlsSlicer.setName("slicers_" + count);
xlsSlicer.setStyleType(type);
}
//Save to file
wb.saveToFile(outputFile_xlsx, ExcelVersion.Version2013);
|
| New feature | - | Supports creating a slicer using pivot table data.
Workbook wb = new Workbook();
//Get the first worksheet of workbook
Worksheet worksheet = wb.getWorksheets().get(0);
worksheet.getRange().get("A1").setValue("fruit");
worksheet.getRange().get("A2").setValue("grape");
worksheet.getRange().get("A3").setValue("blueberry");
worksheet.getRange().get("A4").setValue("kiwi");
worksheet.getRange().get("A5").setValue("cherry");
worksheet.getRange().get("A6").setValue("grape");
worksheet.getRange().get("A7").setValue("blueberry");
worksheet.getRange().get("A8").setValue("kiwi");
worksheet.getRange().get("A9").setValue("cherry");
worksheet.getRange().get("B1").setValue("year");
worksheet.getRange().get("B2").setValue2(2020);
worksheet.getRange().get("B3").setValue2(2020);
worksheet.getRange().get("B4").setValue2(2020);
worksheet.getRange().get("B5").setValue2(2020);
worksheet.getRange().get("B6").setValue2(2021);
worksheet.getRange().get("B7").setValue2(2021);
worksheet.getRange().get("B8").setValue2(2021);
worksheet.getRange().get("B9").setValue2(2021);
worksheet.getRange().get("C1").setValue("amount");
worksheet.getRange().get("C2").setValue2(50);
worksheet.getRange().get("C3").setValue2(60);
worksheet.getRange().get("C4").setValue2(70);
worksheet.getRange().get("C5").setValue2(80);
worksheet.getRange().get("C6").setValue2(90);
worksheet.getRange().get("C7").setValue2(100);
worksheet.getRange().get("C8").setValue2(110);
worksheet.getRange().get("C9").setValue2(120);
// Get pivot table collection
PivotTablesCollection pivotTables = worksheet.getPivotTables();
//Add a PivotTable to the worksheet
CellRange dataRange = worksheet.getRange().get("A1:C9");
PivotCache cache = wb.getPivotCaches().add(dataRange);
//Cell to put the pivot table
PivotTable pt = worksheet.getPivotTables().add("TestPivotTable", worksheet.getRange().get("A12"), cache);
//Drag the fields to the row area.
IPivotField pf = pt.getPivotFields().get("fruit");
pf.setAxis(AxisTypes.Row);
IPivotField pf2 = pt.getPivotFields().get("year");
pf2.setAxis(AxisTypes.Column);
//Drag the field to the data area.
pt.getDataFields().add(pt.getPivotFields().get("amount"), "SUM of Count", SubtotalTypes.Sum);
//Set PivotTable style
pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium10);
//Get slicer collection
XlsSlicerCollection slicers = worksheet.getSlicers();
//Add a Slicer through pivot table data: here invoke Add(IPivotTable, string, int) api.
int index = slicers.add(pt, "E12", 0);
XlsSlicer xlsSlicer = slicers.get(index);
xlsSlicer.setName("test_xlsSlicer");
xlsSlicer.setWidth(100);
xlsSlicer.setHeight(120);
xlsSlicer.setStyleType(SlicerStyleType.SlicerStyleLight2);
xlsSlicer.isPositionLocked(true);
//Get SlicerCache object of current slicer
XlsSlicerCache slicerCache = xlsSlicer.getSlicerCache();
slicerCache.setCrossFilterType(SlicerCacheCrossFilterType.ShowItemsWithNoData);
//Style setting
XlsSlicerCacheItemCollection slicerCacheItems = xlsSlicer.getSlicerCache().getSlicerCacheItems();
XlsSlicerCacheItem xlsSlicerCacheItem = slicerCacheItems.get(0);
xlsSlicerCacheItem.isSelected(false);
XlsSlicerCollection slicers_2 = worksheet.getSlicers();
IPivotField r1 = pt.getPivotFields().get("year");
int index_2 = slicers_2.add(pt, "I12", r1);
XlsSlicer xlsSlicer_2 = slicers.get(index_2);
xlsSlicer_2.setRowHeight(40);
xlsSlicer_2.setStyleType(SlicerStyleType.SlicerStyleLight3);
xlsSlicer_2.isPositionLocked(false);
//Get SlicerCache object of current slicer
XlsSlicerCache slicerCache_2 = xlsSlicer_2.getSlicerCache();
slicerCache_2.setCrossFilterType(SlicerCacheCrossFilterType.ShowItemsWithDataAtTop);
//Style setting
XlsSlicerCacheItemCollection slicerCacheItems_2 = xlsSlicer_2.getSlicerCache().getSlicerCacheItems();
XlsSlicerCacheItem xlsSlicerCacheItem_2 = slicerCacheItems_2.get(1);
xlsSlicerCacheItem_2.isSelected(false);
pt.calculateData();
//Save to file
wb.saveToFile("out.xlsx", ExcelVersion.Version2013);
|
| New feature | - | Supports removing slicers.
Workbook wb = new Workbook(); wb.loadFromFile(inputFile); //Get slicer collection of first worksheet Worksheet worksheet_1 = wb.getWorksheets().get(0); XlsSlicerCollection slicers = worksheet_1.getSlicers(); // Remove the first slicer by index slicers.removeAt(0); XlsSlicer slicer = worksheet_1.getSlicers().get(1); // Remove second slicer by object worksheet_1.getSlicers().remove(slicer); Worksheet worksheet_2 = wb.getWorksheets().get(2); // Remove all slicers worksheet_2.getSlicers().clear(); //Save to file wb.saveToFile(outputFile_xlsx, ExcelVersion.Version2013); |
| New feature | - | Supports modifying slicers.
Workbook wb = new Workbook();
wb.loadFromFile(inputFile);
//Get the first worksheet of workbook
Worksheet worksheet = wb.getWorksheets().get(0);
// Get slicer collection
XlsSlicerCollection slicers = worksheet.getSlicers();
//Style setting
XlsSlicer xlsSlicer = slicers.get(0);
xlsSlicer.setStyleType(SlicerStyleType.SlicerStyleDark4);
xlsSlicer.setCaption("Slicer");
xlsSlicer.isPositionLocked(true);
XlsSlicerCacheItemCollection slicerCacheItems = xlsSlicer.getSlicerCache().getSlicerCacheItems();
XlsSlicerCacheItem xlsSlicerCacheItem = slicerCacheItems.get(0);
xlsSlicerCacheItem.isSelected(false);
//Get SlicerCache object of current slicer
XlsSlicerCache slicerCache = xlsSlicer.getSlicerCache();
slicerCache.setCrossFilterType(SlicerCacheCrossFilterType.ShowItemsWithNoData);
//Save to file
wb.saveToFile(outputFile_xlsx, ExcelVersion.Version2013);
|
| New feature | - | Supports retrieving slicer information.
Workbook wb = new Workbook();
wb.loadFromFile(inputFile);
// Get slicer collection of first worksheet
Worksheet worksheet = wb.getWorksheets().get(0);
XlsSlicerCollection slicers = worksheet.getSlicers();
StringBuilder builder = new StringBuilder();
builder.append("slicers.Count：" + slicers.getCount()+"\r\n");
XlsSlicer xlsSlicer = slicers.get(1);
builder.append("xlsSlicer.Name：" + xlsSlicer.getName()+"\r\n");
builder.append("xlsSlicer.Caption：" + xlsSlicer.getCaption()+"\r\n");
builder.append("xlsSlicer.NumberOfColumns：" + xlsSlicer.getNumberOfColumns()+"\r\n");
builder.append("xlsSlicer.ColumnWidth：" + xlsSlicer.getColumnWidth()+"\r\n");
builder.append("xlsSlicer.RowHeight：" + xlsSlicer.getRowHeight()+"\r\n");
builder.append("xlsSlicer.ShowCaption：" + xlsSlicer.isShowCaption()+"\r\n");
builder.append("xlsSlicer.PositionLocked：" + xlsSlicer.isPositionLocked()+"\r\n");
builder.append("xlsSlicer.Width：" + xlsSlicer.getWidth()+"\r\n");
builder.append("xlsSlicer.Height：" + xlsSlicer.getHeight()+"\r\n");
//Get SlicerCache object of current slicer
XlsSlicerCache slicerCache = xlsSlicer.getSlicerCache();
builder.append("slicerCache.SourceName：" + slicerCache.getSourceName()+"\r\n");
builder.append("slicerCache.IsTabular：" + slicerCache.isTabular()+"\r\n");
builder.append("slicerCache.Name：" + slicerCache.getName()+"\r\n");
XlsSlicerCacheItemCollection slicerCacheItems = slicerCache.getSlicerCacheItems();
XlsSlicerCacheItem xlsSlicerCacheItem = slicerCacheItems.get(1);
builder.append("xlsSlicerCacheItem.Selected：" + xlsSlicerCacheItem.isSelected() +"\r\n");
FileWriter fw = new FileWriter(outputFile_T);
fw.write(builder.toString());
fw.flush();
fw.close();
wb.dispose();
|
| Bug | SPIREXLS-5569 | Fixes the issue that OLE objects failed to open correctly after converting Excel to XLSB format. |
| Bug | SPIREXLS-5673 | Fixes the issue that alignment was incorrect when converting Excel to PDF. |
| Bug | SPIREXLS-5752 | Optimizes the text rendering effect of grouped shapes when converting Excel to PDF. |
| Bug | SPIREXLS-5757 | Fixes the issue that some formula values were calculated incorrectly when converting Excel to images. |
Click the link to download Spire.XLS for Java 15.4.0:
