Example #1
0
        /// <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);
                }
            }
        }
Example #2
0
        /// <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.");
            }
        }