Spire.XLS 15.3 现已发布。该版本支持使用表格数据创建 Slicer 切片器、修改 Slicer 切片器、获取 Slicer 切片器信息以及删除Slicer切片器。此外，它还修复了将 Excel 转换为 PDF 时切片器丢失的那个问题。新功能及问题修复详情如下。
新功能:
- 支持使用表格数据添加 Slicer 切片器。
- 支持使用透视表数据添加 Slicer 切片器。
- 支持移除 Slicer 切片器。
- 支持修改 Slicer 切片器。
- 支持获取 Slicer 切片器信息。
- 支持修改 Slicer 切片器的名称。
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);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);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);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);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();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);问题修复:
- 修复了 Excel 转 PDF，切片器丢失的问题。
下载 Spire.XLS 15.3，请点击：
