/// <summary>
/// Edit Slicer element
/// </summary>
/// <param name="stream">Editing target stream</param>
/// <param name="log">Logger</param>
public void EditElements(Stream stream, VerifiableLog log)
{
using (SpreadsheetDocument package = SpreadsheetDocument.Open(stream, true))
{
//Getting of SlicerCacheParts.
SlicerCachePart slicerCachePart1 = GetSlicerCachePart(package.WorkbookPart, Slicer1);
SlicerCachePart slicerCachePart2 = GetSlicerCachePart(package.WorkbookPart, Slicer2);
SlicerCachePart slicerCachePart3 = GetSlicerCachePart(package.WorkbookPart, Slicer3);
X15.TableSlicerCache tableSlicerCache1 = slicerCachePart1.RootElement.Descendants <X15.TableSlicerCache>().Single();
X15.TableSlicerCache tableSlicerCache2 = slicerCachePart2.RootElement.Descendants <X15.TableSlicerCache>().Single();
X15.TableSlicerCache tableSlicerCache3 = slicerCachePart3.RootElement.Descendants <X15.TableSlicerCache>().Single();
//Change TableId attribute value of TableSlicerCache.
tableSlicerCache1.TableId.Value = 2;
log.Pass("Edited TableSlicerCache attribute of TableId.");
//Change Column attribute value.
//Change Column attribute value of TableSlicerCache.
tableSlicerCache1.Column.Value = 2;
log.Pass("Edited TableSlicerCache attribute of Column.");
//Change SortOrder attribute value.
//Change SortOrder attribute value of TableSlicerCache. Set value is Descending.
tableSlicerCache1.SortOrder = X14.TabularSlicerCacheSortOrderValues.Descending;
log.Pass("Edited TableSlicerCache attribute of SortOrder. Set value is Descending.");
//Change SortOrder attribute value of TableSlicerCache. Set value is Ascending.
tableSlicerCache2.SortOrder = X14.TabularSlicerCacheSortOrderValues.Ascending;
log.Pass("Edited TableSlicerCache attribute of SortOrder. Set value is Ascending.");
//Change CustomListSort attribute value.
//Change CustomListSort attribute value of TableSlicerCache. Set value is false.
tableSlicerCache1.CustomListSort = false;
log.Pass("Edited TableSlicerCache attribute of CustomListSort. Set value is false.");
//Change CustomListSort attribute value of TableSlicerCache. Set value is true.
tableSlicerCache2.CustomListSort = true;
log.Pass("Edited TableSlicerCache attribute of CustomListSort. Set value is true.");
//Change CrossFilter attribute value.
//Change CrossFilter attribute value of TableSlicerCache. Set value is None.
tableSlicerCache1.CrossFilter = X14.SlicerCacheCrossFilterValues.None;
log.Pass("Edited TableSlicerCache attribute of CrossFilter. Set value is None.");
//Change CrossFilter attribute value of TableSlicerCache. Set value is ShowItemsWithDataAtTop.
tableSlicerCache2.CrossFilter = X14.SlicerCacheCrossFilterValues.ShowItemsWithDataAtTop;
log.Pass("Edited TableSlicerCache attribute of CrossFilter. Set value is ShowItemsWithDataAtTop.");
//Change CrossFilter attribute value of TableSlicerCache. Set value is ShowItemsWithNoData.
tableSlicerCache3.CrossFilter = X14.SlicerCacheCrossFilterValues.ShowItemsWithNoData;
log.Pass("Edited TableSlicerCache attribute of CrossFilter. Set value is ShowItemsWithNoData.");
X15.TableSlicerCache newTableSlicerCache1 = tableSlicerCache1.Clone() as X15.TableSlicerCache;
X15.TableSlicerCache newTableSlicerCache2 = tableSlicerCache2.Clone() as X15.TableSlicerCache;
X15.TableSlicerCache newTableSlicerCache3 = tableSlicerCache3.Clone() as X15.TableSlicerCache;
//Remove the existing Extension, to set the new Extension.
slicerCachePart1.RootElement.Descendants <SlicerCacheDefinitionExtension>().Single().Remove();
slicerCachePart2.RootElement.Descendants <SlicerCacheDefinitionExtension>().Single().Remove();
slicerCachePart3.RootElement.Descendants <SlicerCacheDefinitionExtension>().Single().Remove();
SlicerCacheDefinitionExtension extension1 = new SlicerCacheDefinitionExtension()
{
Uri = "{2F2917AC-EB37-4324-AD4E-5DD8C200BD13}"
};
extension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
extension1.Append(newTableSlicerCache1);
slicerCachePart1.SlicerCacheDefinition.SlicerCacheDefinitionExtensionList.AppendChild(extension1);
SlicerCacheDefinitionExtension extension2 = new SlicerCacheDefinitionExtension()
{
Uri = "{2F2917AC-EB37-4324-AD4E-5DD8C200BD13}"
};
extension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
extension2.Append(newTableSlicerCache2);
slicerCachePart2.SlicerCacheDefinition.SlicerCacheDefinitionExtensionList.AppendChild(extension2);
SlicerCacheDefinitionExtension extension3 = new SlicerCacheDefinitionExtension()
{
Uri = "{2F2917AC-EB37-4324-AD4E-5DD8C200BD13}"
};
extension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
extension3.Append(newTableSlicerCache3);
slicerCachePart3.SlicerCacheDefinition.SlicerCacheDefinitionExtensionList.AppendChild(extension3);
//Append SlicerCacheHideItemsWithNoData element.
Extension extension = new Extension()
{
Uri = "{470722E0-AACD-4C17-9CDC-17EF765DBC7E}"
};
extension.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
extension.AppendChild <X15.SlicerCacheHideItemsWithNoData>(new X15.SlicerCacheHideItemsWithNoData());
slicerCachePart2.SlicerCacheDefinition.SlicerCacheDefinitionExtensionList.AppendChild <Extension>(extension);
log.Pass("Added SlicerCacheHideItemsWithNoData element.");
}
}
/// <summary>
/// Edit Slicer element
/// </summary>
/// <param name="filePath">Editing target file path</param>
/// <param name="log">Logger</param>
public void EditElements(string filePath, VerifiableLog log)
{
using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, true))
{
//Getting of SlicerCacheParts.
SlicerCachePart slicerCachePart1 = GetSlicerCachePart(package.WorkbookPart, this.Slicer1);
SlicerCachePart slicerCachePart2 = GetSlicerCachePart(package.WorkbookPart, this.Slicer2);
SlicerCachePart slicerCachePart3 = GetSlicerCachePart(package.WorkbookPart, this.Slicer3);
X15.TableSlicerCache tableSlicerCache1 = slicerCachePart1.RootElement.Descendants<X15.TableSlicerCache>().Single();
X15.TableSlicerCache tableSlicerCache2 = slicerCachePart2.RootElement.Descendants<X15.TableSlicerCache>().Single();
X15.TableSlicerCache tableSlicerCache3 = slicerCachePart3.RootElement.Descendants<X15.TableSlicerCache>().Single();
//Change TableId attribute value.
try
{
//Change TableId attribute value of TableSlicerCache.
tableSlicerCache1.TableId.Value = 2;
log.Pass("Edited TableSlicerCache attribute of TableId.");
}
catch(Exception e)
{
log.Fail(e.Message);
}
//Change Column attribute value.
try
{
//Change Column attribute value of TableSlicerCache.
tableSlicerCache1.Column.Value = 2;
log.Pass("Edited TableSlicerCache attribute of Column.");
}
catch (Exception e)
{
log.Fail(e.Message);
}
//Change SortOrder attribute value.
try
{
//Change SortOrder attribute value of TableSlicerCache. Set value is Descending.
tableSlicerCache1.SortOrder = X14.TabularSlicerCacheSortOrderValues.Descending;
log.Pass("Edited TableSlicerCache attribute of SortOrder. Set value is Descending.");
//Change SortOrder attribute value of TableSlicerCache. Set value is Ascending.
tableSlicerCache2.SortOrder = X14.TabularSlicerCacheSortOrderValues.Ascending;
log.Pass("Edited TableSlicerCache attribute of SortOrder. Set value is Ascending.");
}
catch (Exception e)
{
log.Fail(e.Message);
}
//Change CustomListSort attribute value.
try
{
//Change CustomListSort attribute value of TableSlicerCache. Set value is false.
tableSlicerCache1.CustomListSort = false;
log.Pass("Edited TableSlicerCache attribute of CustomListSort. Set value is false.");
//Change CustomListSort attribute value of TableSlicerCache. Set value is true.
tableSlicerCache2.CustomListSort = true;
log.Pass("Edited TableSlicerCache attribute of CustomListSort. Set value is true.");
}
catch (Exception e)
{
log.Fail(e.Message);
}
//Change CrossFilter attribute value.
try
{
//Change CrossFilter attribute value of TableSlicerCache. Set value is None.
tableSlicerCache1.CrossFilter = X14.SlicerCacheCrossFilterValues.None;
log.Pass("Edited TableSlicerCache attribute of CrossFilter. Set value is None.");
//Change CrossFilter attribute value of TableSlicerCache. Set value is ShowItemsWithDataAtTop.
tableSlicerCache2.CrossFilter = X14.SlicerCacheCrossFilterValues.ShowItemsWithDataAtTop;
log.Pass("Edited TableSlicerCache attribute of CrossFilter. Set value is ShowItemsWithDataAtTop.");
//Change CrossFilter attribute value of TableSlicerCache. Set value is ShowItemsWithNoData.
tableSlicerCache3.CrossFilter = X14.SlicerCacheCrossFilterValues.ShowItemsWithNoData;
log.Pass("Edited TableSlicerCache attribute of CrossFilter. Set value is ShowItemsWithNoData.");
}
catch (Exception e)
{
log.Fail(e.Message);
}
try
{
X15.TableSlicerCache newTableSlicerCache1 = tableSlicerCache1.Clone() as X15.TableSlicerCache;
X15.TableSlicerCache newTableSlicerCache2 = tableSlicerCache2.Clone() as X15.TableSlicerCache;
X15.TableSlicerCache newTableSlicerCache3 = tableSlicerCache3.Clone() as X15.TableSlicerCache;
//Remove the existing Extension, to set the new Extension.
slicerCachePart1.RootElement.Descendants<SlicerCacheDefinitionExtension>().Single().Remove();
slicerCachePart2.RootElement.Descendants<SlicerCacheDefinitionExtension>().Single().Remove();
slicerCachePart3.RootElement.Descendants<SlicerCacheDefinitionExtension>().Single().Remove();
SlicerCacheDefinitionExtension extension1 = new SlicerCacheDefinitionExtension() { Uri = "{2F2917AC-EB37-4324-AD4E-5DD8C200BD13}" };
extension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
extension1.Append(newTableSlicerCache1);
slicerCachePart1.SlicerCacheDefinition.SlicerCacheDefinitionExtensionList.AppendChild(extension1);
SlicerCacheDefinitionExtension extension2 = new SlicerCacheDefinitionExtension() { Uri = "{2F2917AC-EB37-4324-AD4E-5DD8C200BD13}" };
extension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
extension2.Append(newTableSlicerCache2);
slicerCachePart2.SlicerCacheDefinition.SlicerCacheDefinitionExtensionList.AppendChild(extension2);
SlicerCacheDefinitionExtension extension3 = new SlicerCacheDefinitionExtension() { Uri = "{2F2917AC-EB37-4324-AD4E-5DD8C200BD13}" };
extension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
extension3.Append(newTableSlicerCache3);
slicerCachePart3.SlicerCacheDefinition.SlicerCacheDefinitionExtensionList.AppendChild(extension3);
}
catch (Exception e)
{
log.Fail(e.Message);
}
try
{
//Append SlicerCacheHideItemsWithNoData element.
Extension extension = new Extension() { Uri = "{470722E0-AACD-4C17-9CDC-17EF765DBC7E}" };
extension.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
extension.AppendChild<X15.SlicerCacheHideItemsWithNoData>(new X15.SlicerCacheHideItemsWithNoData());
slicerCachePart2.SlicerCacheDefinition.SlicerCacheDefinitionExtensionList.AppendChild<Extension>(extension);
log.Pass("Added SlicerCacheHideItemsWithNoData element.");
}
catch (Exception e)
{
log.Fail(e.Message);
}
}
}
// Generates content of slicerCachePart2.
private void GenerateSlicerCachePart2Content(SlicerCachePart slicerCachePart2)
{
X14.SlicerCacheDefinition slicerCacheDefinition2 = new X14.SlicerCacheDefinition(){ Name = "Slicer_Column1", SourceName = "Column1", MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x" } };
slicerCacheDefinition2.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
slicerCacheDefinition2.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
X14.SlicerCacheDefinitionExtensionList slicerCacheDefinitionExtensionList2 = new X14.SlicerCacheDefinitionExtensionList();
SlicerCacheDefinitionExtension slicerCacheDefinitionExtension2 = new SlicerCacheDefinitionExtension(){ Uri = "{2F2917AC-EB37-4324-AD4E-5DD8C200BD13}" };
slicerCacheDefinitionExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TableSlicerCache tableSlicerCache2 = new X15.TableSlicerCache(){ TableId = (UInt32Value)1U, Column = (UInt32Value)1U };
slicerCacheDefinitionExtension2.Append(tableSlicerCache2);
slicerCacheDefinitionExtensionList2.Append(slicerCacheDefinitionExtension2);
slicerCacheDefinition2.Append(slicerCacheDefinitionExtensionList2);
slicerCachePart2.SlicerCacheDefinition = slicerCacheDefinition2;
}
