We're pleased to announce the release of Spire.XLS 15.3. This version supports creating slicer using table data, modifying slicer, retrieving slicer information and removing slicer. Besides, it also fixes an issue that the slicers were lost when converting Excel to PDF. More details are shown below.
Here is a list of changes made in this release
| Category | ID | Description |
| New feature | - | Added support for creating slicer using table data.
Workbook wb = new Workbook();
Worksheet worksheet = wb.Worksheets[0];
worksheet.Range["A1"].Value = "fruit";
worksheet.Range["A2"].Value = "grape";
worksheet.Range["A3"].Value = "blueberry";
worksheet.Range["A4"].Value = "kiwi";
worksheet.Range["A5"].Value = "cherry";
worksheet.Range["A6"].Value = "grape";
worksheet.Range["A7"].Value = "blueberry";
worksheet.Range["A8"].Value = "kiwi";
worksheet.Range["A9"].Value = "cherry";
worksheet.Range["B1"].Value = "year";
worksheet.Range["B2"].Value2 = 2020;
worksheet.Range["B3"].Value2 = 2020;
worksheet.Range["B4"].Value2 = 2020;
worksheet.Range["B5"].Value2 = 2020;
worksheet.Range["B6"].Value2 = 2021;
worksheet.Range["B7"].Value2 = 2021;
worksheet.Range["B8"].Value2 = 2021;
worksheet.Range["B9"].Value2 = 2021;
worksheet.Range["C1"].Value = "amount";
worksheet.Range["C2"].Value2 = 50;
worksheet.Range["C3"].Value2 = 60;
worksheet.Range["C4"].Value2 = 70;
worksheet.Range["C5"].Value2 = 80;
worksheet.Range["C6"].Value2 = 90;
worksheet.Range["C7"].Value2 = 100;
worksheet.Range["C8"].Value2 = 110;
worksheet.Range["C9"].Value2 = 120;
// Get slicer collection
XlsSlicerCollection slicers = worksheet.Slicers;
//Create a super table with the data from the specific cell range.
IListObject table = worksheet.ListObjects.Create("Super Table", worksheet.Range["A1:C9"]);
int count = 3;
int index = 0;
foreach (SlicerStyleType type in Enum.GetValues(typeof(SlicerStyleType)))
{
count += 5;
//Add a Slicer through 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[index];
xlsSlicer.Name = "slicers_" + count;
xlsSlicer.StyleType = type;
}
//Save to file
wb.SaveToFile("output.xlsx", ExcelVersion.Version2013);
|
| New feature | - | Added support for creating slicer using pivot table data.
Workbook wb = new Workbook();
Worksheet worksheet = wb.Worksheets[0];
worksheet.Range["A1"].Value = "fruit";
worksheet.Range["A2"].Value = "grape";
worksheet.Range["A3"].Value = "blueberry";
worksheet.Range["A4"].Value = "kiwi";
worksheet.Range["A5"].Value = "cherry";
worksheet.Range["A6"].Value = "grape";
worksheet.Range["A7"].Value = "blueberry";
worksheet.Range["A8"].Value = "kiwi";
worksheet.Range["A9"].Value = "cherry";
worksheet.Range["B1"].Value = "year";
worksheet.Range["B2"].Value2 = 2020;
worksheet.Range["B3"].Value2 = 2020;
worksheet.Range["B4"].Value2 = 2020;
worksheet.Range["B5"].Value2 = 2020;
worksheet.Range["B6"].Value2 = 2021;
worksheet.Range["B7"].Value2 = 2021;
worksheet.Range["B8"].Value2 = 2021;
worksheet.Range["B9"].Value2 = 2021;
worksheet.Range["C1"].Value = "amount";
worksheet.Range["C2"].Value2 = 50;
worksheet.Range["C3"].Value2 = 60;
worksheet.Range["C4"].Value2 = 70;
worksheet.Range["C5"].Value2 = 80;
worksheet.Range["C6"].Value2 = 90;
worksheet.Range["C7"].Value2 = 100;
worksheet.Range["C8"].Value2 = 110;
worksheet.Range["C9"].Value2 = 120;
// Get pivot table collection
Spire.Xls.Collections.PivotTablesCollection pivotTables = worksheet.PivotTables;
//Add a PivotTable to the worksheet
CellRange dataRange = worksheet.Range["A1:C9"];
PivotCache cache = wb.PivotCaches.Add(dataRange);
//Cell to put the pivot table
Spire.Xls.PivotTable pt = worksheet.PivotTables.Add("TestPivotTable", worksheet.Range["A12"], cache);
//Drag the fields to the row area.
PivotField pf = pt.PivotFields["fruit"] as PivotField;
pf.Axis = AxisTypes.Row;
PivotField pf2 = pt.PivotFields["year"] as PivotField;
pf2.Axis = AxisTypes.Column;
//Drag the field to the data area.
pt.DataFields.Add(pt.PivotFields["amount"], "SUM of Count", SubtotalTypes.Sum);
//Set PivotTable style
pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium10;
pt.CalculateData();
//Get slicer collection
XlsSlicerCollection slicers = worksheet.Slicers;
//Add a Slicer through pivot table data: here invoke Add(IPivotTable, string, int) api.
int index = slicers.Add(pt, "E12", 0);
XlsSlicer xlsSlicer = slicers[index];
xlsSlicer.Name = "test_xlsSlicer";
xlsSlicer.Width = 100;
xlsSlicer.Height = 120;
xlsSlicer.StyleType = SlicerStyleType.SlicerStyleLight2;
xlsSlicer.PositionLocked = true;
//Get SlicerCache object of current slicer
XlsSlicerCache slicerCache = xlsSlicer.SlicerCache;
slicerCache.CrossFilterType = SlicerCacheCrossFilterType.ShowItemsWithNoData;
//Style setting
XlsSlicerCacheItemCollection slicerCacheItems = xlsSlicer.SlicerCache.SlicerCacheItems;
XlsSlicerCacheItem xlsSlicerCacheItem = slicerCacheItems[0];
xlsSlicerCacheItem.Selected = false;
XlsSlicerCollection slicers_2 = worksheet.Slicers;
IPivotField r1 = pt.PivotFields["year"];
int index_2 = slicers_2.Add(pt, "I12", r1);
XlsSlicer xlsSlicer_2 = slicers[index_2];
xlsSlicer_2.RowHeight = 40;
xlsSlicer_2.StyleType = SlicerStyleType.SlicerStyleLight3;
xlsSlicer_2.PositionLocked = false;
//Get SlicerCache object of current slicer
XlsSlicerCache slicerCache_2 = xlsSlicer_2.SlicerCache;
slicerCache_2.CrossFilterType = SlicerCacheCrossFilterType.ShowItemsWithDataAtTop;
//Style setting
XlsSlicerCacheItemCollection slicerCacheItems_2 = xlsSlicer_2.SlicerCache.SlicerCacheItems;
XlsSlicerCacheItem xlsSlicerCacheItem_2 = slicerCacheItems_2[1];
xlsSlicerCacheItem_2.Selected = false;
pt.CalculateData();
//Save to file
wb.SaveToFile("out.xlsx", ExcelVersion.Version2013);
|
| New feature | - | Added support for removing slicer.
Workbook wb = new Workbook(); wb.LoadFromFile(inputFile); //Get slicer collection of first worksheet Worksheet worksheet = wb.Worksheets[0]; XlsSlicerCollection slicers = worksheet.Slicers; //Remove the first slicer by index slicers.RemoveAt(0); Worksheet worksheet_2 = wb.Worksheets[1]; //Remove all slicers worksheet_2.Slicers.Clear(); wb.SaveToFile(outputFile, ExcelVersion.Version2013); |
| New feature | - | Added support for modifying slicer.
Workbook wb = new Workbook();
wb.LoadFromFile("in.xlsx");
//Get the first worksheet of workbook
Worksheet worksheet = wb.Worksheets[0];
//Get slicer collection
XlsSlicerCollection slicers = worksheet.Slicers;
//Style setting
XlsSlicer xlsSlicer = slicers[0];
xlsSlicer.StyleType = SlicerStyleType.SlicerStyleDark4;
xlsSlicer.Caption = "Slicer";
xlsSlicer.PositionLocked = true;
XlsSlicerCacheItemCollection slicerCacheItems = xlsSlicer.SlicerCache.SlicerCacheItems;
XlsSlicerCacheItem xlsSlicerCacheItem = slicerCacheItems[0];
xlsSlicerCacheItem.Selected = false;
string displayValue = xlsSlicerCacheItem.DisplayValue;
//Get SlicerCache object of current slicer
XlsSlicerCache slicerCache = xlsSlicer.SlicerCache;
slicerCache.CrossFilterType = SlicerCacheCrossFilterType.ShowItemsWithNoData;
//Save to file
wb.SaveToFile("out.xlsx", ExcelVersion.Version2013);
|
| New feature | - | Added support for retrieving slicer information.
Workbook wb = new Workbook();
wb.LoadFromFile("in.xlsx");
//Get slicer collection of first worksheet
Worksheet worksheet = wb.Worksheets[0];
XlsSlicerCollection slicers = worksheet.Slicers;
StringBuilder builder = new StringBuilder();
builder.AppendLine("slicers.Count：" + slicers.Count);
XlsSlicer xlsSlicer = slicers[1];
builder.AppendLine("xlsSlicer.Name：" + xlsSlicer.Name);
builder.AppendLine("xlsSlicer.Caption：" + xlsSlicer.Caption);
builder.AppendLine("xlsSlicer.NumberOfColumns：" + xlsSlicer.NumberOfColumns);
builder.AppendLine("xlsSlicer.ColumnWidth：" + xlsSlicer.ColumnWidth);
builder.AppendLine("xlsSlicer.RowHeight：" + xlsSlicer.RowHeight);
builder.AppendLine("xlsSlicer.ShowCaption：" + xlsSlicer.ShowCaption);
builder.AppendLine("xlsSlicer.PositionLocked：" + xlsSlicer.PositionLocked);
builder.AppendLine("xlsSlicer.Width：" + xlsSlicer.Width);
builder.AppendLine("xlsSlicer.Height：" + xlsSlicer.Height);
//Get SlicerCache object of current slicer
XlsSlicerCache slicerCache = xlsSlicer.SlicerCache;
builder.AppendLine("slicerCache.SourceName：" + slicerCache.SourceName);
builder.AppendLine("slicerCache.IsTabular：" + slicerCache.IsTabular);
builder.AppendLine("slicerCache.Name：" + slicerCache.Name);
XlsSlicerCacheItemCollection slicerCacheItems = slicerCache.SlicerCacheItems;
XlsSlicerCacheItem xlsSlicerCacheItem = slicerCacheItems[1];
builder.AppendLine("xlsSlicerCacheItem.Selected：" + xlsSlicerCacheItem.Selected);
File.WriteAllText("out.txt", builder.ToString());
wb.Dispose();
|
| New feature | SPIREXLS-5302 | Added support for modifying the names of slicer.
Workbook wb = new Workbook(); wb.LoadFromFile(inputFile); Worksheet worksheet = wb.Worksheets[0]; XlsSlicerCollection slicers = worksheet.Slicers; XlsSlicer xlsSlicer = slicers[0]; xlsSlicer.Caption = "Name1"; wb.SaveToFile(outputFile, ExcelVersion.Version2013); |
| Bug | SPIREXLS-1922 SPIREXLS-5641 |
Fixed the issue of missing slicers when converting Excel to PDF. |
Click the link to download Spire.XLS 15.3:
More information of Spire.XLS new release or hotfix:
