Spire.XLS for Java 15.4.0 支持使用表格数据添加 Slicer 切片器

Spire.XLS for Java 15.4.0 现已正式发布。最新版本支持使用表格数据添加 Slicer 切片器。此外，本次升级还成功修复了一些已知问题，比如，Excel 转 XLSB 格式后，OLE 对象打开不正确的问题。更多详情请查阅以下内容。

新功能:

支持使用表格数据添加 Slicer 切片器。

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);

支持使用透视表数据添加 Slicer 切片器。

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);

支持移除 Slicer 切片器。

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);

支持修改 Slicer 切片器。

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);

支持获取 Slicer 切片器信息。

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();

问题修复:

修复了 Excel 转 XLSB 格式后，OLE 对象打开不正确的问题。
修复了 Excel 转 PDF 时，对齐方式不正确的问题。
优化了 Excel 转 PDF 时，组合图形文字效果。
修复了 Excel 转图片时，一些公式值计算不正确的问题。

获取 Spire.XLS for Java 15.4.0，请点击：

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

返回顶部

Spire.XLS for Java 15.4.0 支持使用表格数据添加 Slicer 切片器

新闻类别