OfficeFileBatchProcess/App.config at master · burma69/OfficeFileBatchProcess · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="BatchConfig" type="OfficeBatchProcess.BatchConfigSection, OfficeFileBatchProcess" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <appSettings>
    <!--<add key="SourceDirectory" value="C:\source" />
    <add key="DestinationDirectory" value="C:\dest" /> -->
    <add key="MaxThreads" value="6" />
    <add key="ClientSettingsProvider.ServiceUri" value="" />
  </appSettings>
  <BatchConfig>
    <ProcessParams>
      <Param XLSName="" ZipObjectPath="sharedStrings.xml" NodeXPath="/x:sst/x:si/x:t" NodeNamespace="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <ReplaceAttributes>
          <Replace OriginalValue="[ ]+$" ReplaceValue=""/>
          <!--remove trailing spaces-->
          <Replace OriginalValue=" [ ]+" ReplaceValue=" "/>
          <!--remove repeated spaces-->
          <Replace OriginalValue="^Amount$" ReplaceValue="Amount Approved"/>
          <!--rename cell values-->
        </ReplaceAttributes>
      </Param>
      <!--rename table object headers-->
      <Param XLSName="" ZipObjectPath="xl\/(t|T)ables\/([^.\/]+).xml" NodeXPath="//*[@name]" NodeNamespace="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <ReplaceAttributes>
          <Replace AttributeName="name" OriginalValue="[ ]+$" ReplaceValue=""/>
          <Replace AttributeName="name" OriginalValue=" [ ]+" ReplaceValue=" "/>
          <Replace AttributeName="name" OriginalValue="^Amount$" ReplaceValue="Amount Approved"/>
        </ReplaceAttributes>
      </Param>

      <!-- extract some data from internal XML contents (e.g. Word file) and save it as XML-->
      <Param XLSName="" ZipObjectPath="word/document.xml" NodeXPath="/w:document/w:body/w:tbl" NodeNamespace="http://schemas.openxmlformats.org/wordprocessingml/2006/main">
        <ExtractContents>
          <Extract SliceByXPaths="w:tr,w:tc,w:p/w:r/w:t" />
        </ExtractContents>
      </Param>

      <!-- force remove reference to file for data source connections (e.g. SSAS connected Pivot Tables)-->
      <Param XLSName="" ZipObjectPath="connections.xml" NodeXPath="/x:connections/x:connection" NodeNamespace="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <ReplaceAttributes>
          <Replace AttributeName="onlyUseConnectionFile" OriginalValue=".+" ReplaceValue="0"/>
        </ReplaceAttributes>
      </Param>
      <!-- change connection string attributes -->
      <Param XLSName="" ZipObjectPath="connections.xml" NodeXPath="/x:connections/x:connection/x:dbPr" NodeNamespace="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <ReplaceAttributes>
          <Replace AttributeName="connection" OriginalValue="Data Source=([^;]+)" ReplaceValue="Data Source=1.2.3.4"/>
          <Replace AttributeName="connection" OriginalValue="Initial Catalog=([^;]+)" ReplaceValue="Initial Catalog=new_database"/>
        </ReplaceAttributes>
      </Param>


      <!-- major SSAS connected Pivot Tables transformations-->
      <Param XLSName="" ZipObjectPath="(connections.xml|metadata.xml|xl\/pivotTables\/([^.\/]+).xml|xl\/pivotCache\/pivotCacheDef([^.\/]+).xml|xl\/slicerCaches\/([^.\/]+).xml)" NodeXPath="(/x:connections/x:connection/x:dbPr|/x:metadata/x:metadataStrings/x:s|//*[@name]|//*[@uniqueName]|//*[@defaultMemberUniqueName]|//*[@allUniqueName]|//*[@dimensionUniqueName]|//*[@v]|//*[@n])" NodeNamespace="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <ReplaceAttributes>
          <!-- transform members -->

          <Replace AttributeName="name$|^n$" OriginalValue="\[Old Member\]" ReplaceValue="[New Member]"/>

          <!-- transform member properties -->
          <Replace AttributeName="name$|^n$" OriginalValue="\[Hierarchy\]\.\[Level\]\.\[Property\]" ReplaceValue="[New Hierarchy].[Level].[New Property]"/>

          <!-- transform levels, members and hierarchies -->
          <Replace AttributeName="name$|^n$|^v$" OriginalValue="\.\[Level\]" ReplaceValue=".[New Level]"/>

          <!-- transform dimensions -->
          <Replace AttributeName="name$|^n$" OriginalValue="\[(Dim1|Dim2) - Name\]" ReplaceValue="[New Dimension - Name]"/>

          <!-- transform selected members (values) ID codes using the text, cached in Excel cell (stored in "c" attribute of corresponding XML node). E.g. [Dimension].[Hierarchy].123 to [Dimension.[Hierarchy].[Value-004] -->
          <Replace AttributeName="(command|v)" OriginalValue="\[Dimension\].\[Hierarchy\].([^,)}\n]+)" ReplaceValue="[Dimension].[Hierarchy].[#]" AttributeNameForDashReplace="c" DefaultValueForDashReplace="All"/>
        </ReplaceAttributes>
      </Param>
      <Param XLSName="" ZipObjectPath="sharedStrings.xml" NodeXPath="/x:sst/x:si/x:t" NodeNamespace="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <ReplaceAttributes>
          <!-- replace inner text-->
          <Replace OriginalValue="Hierarchy\.Level$" ReplaceValue="New Hierarchy.New Level"/>
        </ReplaceAttributes>
      </Param>
      <Param XLSName="" ZipObjectPath="xl\/(query)*(t|T)ables\/([^.\/]+).xml" NodeXPath="//*[@name]" NodeNamespace="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <ReplaceAttributes>
          <Replace AttributeName="name" OriginalValue="Hierarchy\.Level$" ReplaceValue="New Hierarchy.New Level"/>
        </ReplaceAttributes>
      </Param>

      <!-- in case there were any collisions introduced, below would help fix the Pivot Cache-->
      <Param XLSName="" ZipObjectPath="xl\/pivotTables\/([^.\/]+).xml|xl\/pivotCache\/pivotCacheDef([^.\/]+).xml" NodeXPath="//x:cacheHierarchies|//x:dimensions" NodeNamespace="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <DeduplicateElements>
          <!--<Deduplicate Mode="Rename" XMLElement="cacheHierarchy" XMLKey="uniqueName">
            <CascadeRemoves>
              <CascadeRemove Id="1" CascadeFrom="-1" UpdateLinkedIndexElement="//x:cacheFields/x:cacheField" UpdateLinkedIndexAttribute="hierarchy"/>
              <CascadeRemove Id="2" CascadeFrom="1" UpdateLinkedIndexElement="//x:fieldsUsage/x:fieldUsage" UpdateLinkedIndexAttribute="x"/>
              <CascadeRemove Id="3" CascadeFrom="2" ParentAttributeNameForXPathDash="x" UpdateLinkedIndexElement="//x:cacheFields/x:cacheField[@hierarchy = //x:cacheFields/x:cacheField[#+1]/@hierarchy]" UpdateLinkedIndexAttribute="level"/>
            </CascadeRemoves>
          </Deduplicate>-->
          <Deduplicate Mode="Rename" XMLElement="dimension" XMLKey="uniqueName">
            <CascadeRemoves>
              <CascadeRemove Id="1" CascadeFrom="-1" UpdateLinkedIndexElement="//x:map" UpdateLinkedIndexAttribute="dimension"/>
            </CascadeRemoves>
          </Deduplicate>
        </DeduplicateElements>
      </Param>
    </ProcessParams>
  </BatchConfig>

</configuration>