// ---------------------------------------------------------------------------- // Layout // ---------------------------------------------------------------------------- /* Struktur: Zeilensortierung Anpassen der Spalteneigeschaften Gruppierung Gruppenanordnung */ //--------------------------- // Zeilensortierung //--------------------------- Mastertabelle << Sort( By( Food_Number ), Order( Ascending ), Replace Table ); //--------------------------- // Spalten //--------------------------- DT_ColumProperties = Open( OrdnerFuerSkripte || "_Layout_Metadaten.xlsx", Worksheets( "Übersicht_Spalten" ), Use for all sheets( 1 ), Concatenate Worksheets( 0 ), Create Concatenation Column( 0 ), Worksheet Settings( 1, Has Column Headers( 1 ), Number of Rows in Headers( 1 ), Headers Start on Row( 1 ), Data Starts on Row( 2 ), Data Starts on Column( 1 ), Data Ends on Row( 0 ), Data Ends on Column( 0 ), Replicated Spanned Rows( 0 ), Suppress Hidden Rows( 0 ), Suppress Hidden Columns( 0 ), Treat as Hierarchy( 0 ) ), invisible ); DT_ColumProperties << sort( By( Column( DT_ColumProperties, "AbsolutePosition" ) ), Order( Ascending ), Replace Table ); DT_ColumProperties << select where( Is Missing( :ColumnName ) | :Name( "ColumnName" ) == "." ) << Delete Rows; DT_ColumProperties_ColumnNames = Column( DT_ColumProperties, "ColumnName" ) << Get Values; DT_ColumProperties_GroupNames = Column( DT_ColumProperties, "GroupName" ) << Get Values; //------------- // Anpassen der Spalteneigeschaften //------------- Mastertabelle << Ungroup Columns; Mastertabelle_ALLColumnNames = Mastertabelle << get column names( string ); ScrollLockedColumns = {{}}; // i= 332; // Setzen der Spalteneigeschaften For( i = 1, i <= N Rows( DT_ColumProperties ), i++, // Abbrechen des aktuellen Schleifendurchlauf und Eintrag in das Log-File für fehlerhafte Metadaten-Zeile If( Is Missing( Column( DT_ColumProperties, "ColumnName" )[i] ), log = log || Char( Format( Today(), "d/m/y h:m:s" ) ) || "\!t\!t\!t Zeile '" || Char( i ) || " in 'GT_Mastertabelle_Metadaten.xlsx, Tabellenblatt 'Übersicht_Spalten'' FEHLERHAFT!\!n"; Save Text File( Ord_Log || log_Datei, log ); Continue(); ); // Hinzufügen fehlender Spalten If( Contains( Mastertabelle_ALLColumnNames, Column( DT_ColumProperties, "ColumnName" )[i] ) == 0, Mastertabelle << New Column( Column( DT_ColumProperties, "ColumnName" )[i] ) ); // Anordnung für spätere Gruppierung Column( Mastertabelle, Column( DT_ColumProperties, "ColumnName" )[i] ) << Set Selected( 1 ); Mastertabelle << Move Selected Columns( to last ); Mastertabelle << Clear Column Selection; // Units If( !Is Missing( Column( DT_ColumProperties, "Unit" )[i] ), string = "Column( Mastertabelle, \!"" || Column( DT_ColumProperties, "ColumnName" )[i] || "\!" ) << Set Property( \!"Units\!", \!" " || Column( DT_ColumProperties, "Unit" )[i] || " \!");"; Eval( Parse( string ) ); ); // Data Type If( !Is Missing( Column( DT_ColumProperties, "Type" )[i] ), string = "Column( Mastertabelle, \!"" || Column( DT_ColumProperties, "ColumnName" )[i] || "\!" ) << Set Property( \!"Data Type\!", " || Column( DT_ColumProperties, "Type" )[i] || " );"; Eval( Parse( string ) ); ); // Modeling Type If( !Is Missing( Column( DT_ColumProperties, "ModelingType" )[i] ), string = "Column( Mastertabelle, \!"" || Column( DT_ColumProperties, "ColumnName" )[i] || "\!" ) << Set Modeling Type( \!"" || Column( DT_ColumProperties, "ModelingType" )[i] || "\!" );"; Eval( Parse( string ) ); ); // Hide If( Column( DT_ColumProperties, "Hide" )[i] == "x", Column( Mastertabelle, Column( DT_ColumProperties, "ColumnName" )[i] ) << Hide ); // Exclude If( Column( DT_ColumProperties, "Exclude" )[i] == "x", Column( Mastertabelle, Column( DT_ColumProperties, "ColumnName" )[i] ) << Exclude ); // Label If( Column( DT_ColumProperties, "Label" )[i] == "x", Column( Mastertabelle, Column( DT_ColumProperties, "ColumnName" )[i] ) << Label ); // Lock If( Column( DT_ColumProperties, "Lock" )[i] == "x", Column( Mastertabelle, Column( DT_ColumProperties, "ColumnName" )[i] ) << Lock ); // Scroll Lock Column If( Column( DT_ColumProperties, "Scroll Lock" )[i] == "x", Column( Mastertabelle, Column( DT_ColumProperties, "ColumnName" )[i] ) << Scroll Lock; // Delete group-association in ColumnProperties-data table Column( DT_ColumProperties, "GroupName" )[i] = Empty(); Insert Into( ScrollLockedColumns[1], Column( DT_ColumProperties, "ColumnName" )[i] ); ); // CellColor If( !Is Missing( Column( DT_ColumProperties, "CellColor" )[i] ), color = RGB Color( Num( Words( Column( DT_ColumProperties, "CellColor" )[i], "," )[1] ), Num( Words( Column( DT_ColumProperties, "CellColor" )[i], "," )[2] ), Num( Words( Column( DT_ColumProperties, "CellColor" )[i], "," )[3] ) ); Column( Mastertabelle, Column( DT_ColumProperties, "ColumnName" )[i] ) << Color Cells( color ); ); ); //------------- // Grouping and Sorting //------------- DT_GroupProperties = Open( OrdnerFuerSkripte || "_Layout_Metadaten.xlsx", Worksheets( "Übersicht_Gruppen" ), Use for all sheets( 1 ), Concatenate Worksheets( 0 ), Create Concatenation Column( 0 ), Worksheet Settings( 1, Has Column Headers( 1 ), Number of Rows in Headers( 1 ), Headers Start on Row( 1 ), Data Starts on Row( 2 ), Data Starts on Column( 1 ), Data Ends on Row( 0 ), Data Ends on Column( 0 ), Replicated Spanned Rows( 1 ), Suppress Hidden Rows( 0 ), Suppress Hidden Columns( 0 ), Treat as Hierarchy( 0 ) ), invisible ); DT_GroupProperties << select where( Is Missing( GroupName ) ) << Delete Rows; DT_GroupProperties << Sort( By( AbsolutePosition ), Order( Ascending ), Replace Table ); For( i = 1, i <= N Rows( DT_GroupProperties ), i++, NameOfGroup = Column( DT_GroupProperties, "GroupName" )[i]; DT_ColumnNames = DT_ColumProperties << select where( :Name( "GroupName" ) == Column( DT_GroupProperties, "GroupName" )[i] ) << Subset( Selected Rows( 1 ), columns( :ColumnName ) ); If( !Is Empty( DT_ColumnNames ), Groupcolumns = Column( DT_ColumnNames, "ColumnName" ) << Get Values; Close( DT_ColumnNames, nosave ); Try( Mastertabelle << Group Columns( NameOfGroup, Groupcolumns ); Mastertabelle << Move Column Group( to last, NameOfGroup ); Mastertabelle << Clear Column Selection; ); ); DT_ColumProperties << Clear Column Selection; ); //------------- // Hinzufügen der Formeln für berechnete Spalten //------------- log = log || Char( Format( Today(), "d/m/y h:m:s" ) ) || "\!t Hinzufügen von Spaltenformeln ... \!n"; Include( OrdnerFuerSkripte || "04a_Spaltenformeln.jsl" ); log = log || Char( Format( Today(), "d/m/y h:m:s" ) ) || "\!t Hinzufügen von Spaltenformeln abgeschlossen \!n"; Save Text File( Ord_Log || log_Datei, log ); // Formeln anwenden Mastertabelle << run formulas; //------------- // Delete single or grouped Columns //------------- // Löschen aller Spaltenformeln DeleteColumnFormulas( Mastertabelle ); // Übersicht der gelöschten Spalten DeletedColumns = {{}}; For( i = 1, i <= N Rows( DT_ColumProperties ), i++, If( Column( DT_ColumProperties, "Delete" )[i] == "x", // Delete Column Try( Mastertabelle << Delete Columns( Column( DT_ColumProperties, "ColumnName" )[i] ); // Add ColumnName in list of deleted Columns Insert Into( DeletedColumns[1], Column( DT_ColumProperties, "ColumnName" )[i] ); ); ) ); // Delete Column Groups according to Metadaten "Übersicht_Gruppen" For( i = 1, i <= N Rows( DT_GroupProperties ), i++, If( Is Missing( Column( DT_GroupProperties, "Mastertabelle" )[i] ), Try( Mastertabelle << Select Column Group( Column( DT_GroupProperties, "GroupName" )[i] ); Markierte_Spalten = Mastertabelle << Get Selected Columns(); Mastertabelle << Delete Columns( Markierte_Spalten ); // Add ColumnName in list of deleted Columns Insert Into( DeletedColumns[1], Markierte_Spalten ); ); ); Mastertabelle << Clear Column Selection; ); //------------- // Spezielle Spalteneigenschaften //------------- /* Column( Mastertabelle, "KnowHowTranfer_GewichtetetMitFood" ) << Add Column Properties( Set property( "Value Colors", {"Coarse" = 38, "Medium" = 5} ), Color Cell by Value ); */ Close( DT_ColumProperties, nosave ); Close( DT_GroupProperties, nosave );