Problem

Es wird eine Lösung für das Ein- und Auslesen von Werten in Excelsheets benötigt.
A solution shall enable the user to read and write data to an excel spreadsheet.

Ansatz – Approach

Die Nutzung der OLE Schnittstelle von Excel erlaubt die Fernsteuerung.
The usage of the OLE Interface allows Excel to control excel remotely.

Lösung – Solution

Der folgende DXL Schnipsel erlaubt die Fernsteuerung von Excel mit OLE Automation.
The following DXL Snipplet allows the user to control the excel interface remotely.

/****************************************************************************
 * Beispiele für das Einlesen mit OLE Excel Automation in DXL 
 * erstellt von Björn Karpenstein an einem Sonntag (was ich alles mache!!) 
 ****************************************************************************/

// String Functions
#include "\\\\bbmag2k\\exchange\\doors\\dxl\\strings.inc"; 

// Global settings

//pragma encoding,"UTF-8";
pragma runLim, 0;

int findLastRow (OleAutoObj objExcelSheet)
{
  OleAutoObj objUsedRange, objRows;
  int iRow = 0;
  oleGet(objExcelSheet,"UsedRange",objUsedRange);
  oleGet(objUsedRange,"Rows",objRows);
  oleGet(objRows,"Count",iRow);

  return iRow+1;
}

int findLastColumn (OleAutoObj objExcelSheet)
{
  OleAutoObj objUsedRange, objColumns;
  int iColumn = 0;
  oleGet(objExcelSheet,"UsedRange",objUsedRange);
  oleGet(objUsedRange,"Columns",objColumns);
  oleGet(objColumns,"Count",iColumn );

  return iColumn +1;
}

// Excel: Table1.Cells(1,2).Value;
string getCellContent(OleAutoObj objSheet, int x, int y)
{
  // Das hier brauchen wir, um Parameter an OLE-Objekte
  // zu übergeben - immer leer machen!
  OleAutoArgs objArgBlock = create;

  OleAutoObj objCell;	// Das Zellen-Objekt nach Übermittlung der Koordinaten (1,1)
  string zellenInhalt;	// Der Inhalt aus der Zelle als String
  clear( objArgBlock );   // Parameter leeren
  put( objArgBlock, y );  // Parameter 2
  put( objArgBlock, x );  // Parameter 1
  oleGet(objSheet, "Cells", objArgBlock,objCell); // Das Zellenobjekt nimmt Koordinaten an

  if (!null objCell)
  {
    // Get the value
    // oleGet(objCell,"Value",zellenInhalt);
    oleGet(objCell,"FormulaR1C1",zellenInhalt);
  }

  return zellenInhalt "";
}

void setCellContent(OleAutoObj objSheet, int x, int y, string zellenInhalt)
{
  // Das hier brauchen wir, um Parameter an OLE-Objekte
  // zu übergeben - immer leer machen!
  OleAutoArgs objArgBlock = create;

  OleAutoObj objCell;	// Das Zellen-Objekt nach Übermittlung der Koordinaten (1,1)
  clear( objArgBlock );   // Parameter leeren
  put( objArgBlock, y );  // Parameter 2
  put( objArgBlock, x );  // Parameter 1
  oleGet(objSheet, "Cells", objArgBlock,objCell); // Das Zellenobjekt nimmt Koordinaten an

  if (!null objCell)
  {   
    olePut(objCell,"FormulaR1C1",zellenInhalt);
  }
}

 
void main(void)
{
  // Wenn Excel lokal installiert ist gibt es dieses OLE Objekt im System.. suchs Hasso!!!
  OleAutoObj objExcel = oleCreateAutoObject( "Excel.Application" );

  // Das hier brauchen wir, um Parameter an OLE-Objekte zu übergeben - immer leer machen!
  OleAutoArgs objArgBlock = create;

  // Was steht in Application.isVisible?

  bool isVisible;					// Auslesen 1
  oleGet(objExcel, "Visible", isVisible);	// Auslesen 2


  print "Die Anwendung ist momentan " (isVisible?"sichtbar":"unsichtbar") "\n";

  // Mache die Anwendung sichtbar Application.isVisible=true;
  olePut(objExcel, "Visible", true);		// Reinschreiben

  // Ein Woorkbook ist eine Excel-Datei. Excel kann mehrere Dateien öffnen daher die 
  // Collection. Die Collection selbst hat die Open Methode zum einlesen von XLS-Files
  OleAutoObj objWorkbooks;
  oleGet(objExcel, "Workbooks", objWorkbooks);			// Hole Workbooks Collection

  // Öffne Excel application
  string xlsWorkbook="C:\\dev\\alarms.xls";	// Öffne Workbook
  clear( objArgBlock );
  put( objArgBlock, xlsWorkbook);
  OleAutoObj objWorkbook = null;
  oleGet(objWorkbooks, "Open",objArgBlock, objWorkbook ); // Reinschreiben Datei zu öffnen und Auslesen 
  oleGet(objWorkbooks, "Add", objWorkbook ); // Falls man die obere Zeile nicht ausfuehrt eins adden

  

  // Hole das erste Sheet
  clear( objArgBlock )
  put( objArgBlock, 1 )
  oleGet( objWorkbook, "Sheets", objArgBlock, objSheet);

  // Gib den Namen des ersten Sheets aus
  string sheetName;
  oleGet(objSheet, "Name", sheetName);
  print sheetName "\n";

  // Hole das Sheet mit dem Namen adt_warnings_uni.v1.0
  clear( objArgBlock );
  put( objArgBlock, "adt_alarms_uni.v1.0" ); 
  oleGet( objWorkbook, "Sheets", objArgBlock, objSheet);	

  // Gib den Index des "adt_warnings_uni.v1.0" Sheets aus
  int sheetIndex;
  oleGet(objSheet, "Index", sheetIndex);
  print sheetIndex"\n";

  // Hole das Cells(1,1) Objekt um auf die Excel Zellen zuzugreifen
  print "Zelleninhalt  : " getCellContent(objSheet,17,1) "\n";
  print "Zelleninhalt  : " getCellContent(objSheet,18,1) "\n";
  print "Letzte Zeile  : " findLastRow(objSheet) "\n";
  print "Letzte Spalte :"  findLastColumn(objSheet) "\n";

  // Iteration durch das Excel Sheet 
  int spaltenCount = findLastColumn(objSheet);
  int zeilenCount  = findLastRow(objSheet);
  int currentZeile, currentSpalte;
  string zeilenString="";
  Skip texte = create;

  for (currentZeile=1;currentZeile<zeilenCount;currentZeile++)
  {
	
    DxlObject eqtext = new();
    eqtext->"BK_EQtext_english"=getCellContent(objSheet,17,currentZeile) "";
    eqtext->"BK_EQtext_german"=getCellContent(objSheet,18,currentZeile) "";
    string theKey = getCellContent(objSheet,3,currentZeile) "";

    // Delete Brackets and numbers within the key
    int anfangBrackets=indexOf(theKey, "(");
    int endeBrackets=lastIndexOf(theKey, ")");
    if (anfangBrackets<endeBrackets)
    {
      theKey = theKey[0:anfangBrackets-1];
    }

    put(texte,trim(theKey),eqtext); 
  }

  // Iteration through the SkipList
  for myIterator in texte do 
  {
    string keyValue = (string key(texte));
    DxlObject currentObject = null;

    if(find(texte, keyValue, currentObject))
    {
      // Just put the column names here.. it will work
      //print keyValue "\t";
      //print (string currentObject->"BK_EQtext_english") "\t";
      //print (string currentObject->"BK_EQtext_german") "\n";
    }
  }

  Module m = edit("/Project/filder/module", true);
  Object o;

  for o in m do
  {
    string doorsKey = trim(o."Object Text" "");

    if (doorsKey != "" && (o."BK_AlarmType" "" == "Alarm") ) 
    {
      DxlObject currentObject = null;	
      if (find (texte, doorsKey, currentObject)) 
      {
        string BK_EQtext_english = (string currentObject->"BK_EQtext_english");
        string BK_EQtext_german = (string currentObject->"BK_EQtext_german");		
 		
        // Zuweisen
        if(trim(BK_EQtext_english) != "")
        {
          o."BK_EQtext_english" = replace(BK_EQtext_english, charOf(30) "", "\n") "";
          o."BK_EQtext_german" = replace(BK_EQtext_german, charOf(30) "", "\n") "";
          print doorsKey "<<< Da wird was zugwiesen\n";
        }
      }
      else print "Not found: " doorsKey "\n";
    }
  }

  // Speicher aufräumen
  oleCloseAutoObject( objExcel );
}

main();

Problem

There are lots of event to react on listView events, but they are not clearly defined in the language reference or the DXL Manual.

Approach

This will show the most common events working with listView DBE elements.

Solution

void doSelect(DBE dbe, int idx)
{
// Auswahl mit Einfachklick
} 

void doDeselect(DBE dbe, int idx)
{
// Element verliert selektion (evtl. durch anderes)
} 

void doActivate(DBE dbe, int idx)
{
// Doppelklick / double click
} 

void checkBoxReact(DBE lv, int i)
{
    string s = get(lv, i)
    bool b = getCheck(lv, i)
    if ( b ) 
    {
	   int j=0;
	   for(j=0;j<noElems(lv);j++)
	   {
		   if(i!=j)
		   {
			   setCheck(lv,j,false);
		   }
	   }
       //infoBox(s " was selected")
    }
    else
    {
       //infoBox(s " was deselected")
    }
}

And this is how the ListView has been declared:

DB mainDialog=create("Select Baseline to compare");
	
DBE listViewBaselines = listView(mainDialog, listViewOptionCheckboxes,360,10,baselineEntries);

// The first callback fires when an option is selected (a single click); 
// the second fires when an option is deselected (a side effect of a single click on another item); 
// the third fires when an item is activated (a double click).
set(listViewBaselines,doSelect,doDeselect,doActivate);
set(listViewBaselines, checkBoxReact);
	
DBE publishButton=apply(mainDialog, "Compare", compareButtonCallBack);
	
realize mainDialog;
	
insertColumn(listViewBaselines, 0, "Baseline", 80, iconNone);
insertColumn(listViewBaselines, 1, "Description", 140, iconNone);
insertColumn(listViewBaselines, 2, "Increment", 140, iconNone);

show mainDialog;

Problem

Approach – Ansatz

Solution – Lösung

Lösung 1

Braucht 7 Minuten auf Testmodul

void collectBaselineInfo(Buffer buf, Skip skp)
{ 
  Module m
  Baseline b
  ModName_ mn
  for mn in skp do
  {
    m = read(fullName mn, false)
    if ( !null m ) 
    {
      buf += name(mn)
      b = getMostRecentBaseline(m);
      if ( !null b )
      { 
        buf += ( ", Baseline " major(b) "." minor(b) "\n")
      }
      else 
      { 
        buf += ", Baseline 0.0\n"
      }
    }
    close m
  }  
}


string getTraceability(Module m)
{
  Object o
  Link l
  ModName_ mn
  Skip targetSkp = create
  Skip sourceSkp = create
  Buffer buf = create
  
  // Collect targetmodules and sourcemodules
  for o in m do
  {
    for l in o -> "*" do
    {
      mn = target l
      put(targetSkp, mn, mn)
    }
    for mn in o <- "*" do
    {
      put(sourceSkp, mn, mn)
    } 
  }
  
  buf += "Out-Links\n"
  collectBaselineInfo(buf, targetSkp)
   
  buf += "\nIn-Links\n"
  collectBaselineInfo(buf, sourceSkp)
  
  string s = stringOf buf
  delete buf
  return s
}

Module m = current
print getTraceability(m)

Lösung 2

string getTraceabilityForModule(Module m)
{
  Object obj;
  Skip inLinkSkip=createString;
  Skip outLinkSkip=createString;
	
  filtering off;
	
  for obj in m do
  {
    Link lnk;
    for lnk in obj->"*" do
    {
      string tmn=fullName target(lnk);
      Module openMod=read(tmn,false, true);
      Baseline b = getMostRecentBaseline(openMod);
      int majorBL=-1;
      int minorBL=-1;
			
      if(null b)
      {
        majorBL=0;
        minorBL=0;
      }
      else
      {
        majorBL=major b;
        minorBL=minor b;		
      }
				
      outLinks=name(openMod) ", Baseline " majorBL "." minorBL "";
			
      string dummy;
      if(!find(outLinkSkip, outLinks, dummy))
      {
        put(outLinkSkip, outLinks, outLinks);
      }
    }
		
    LinkRef lnkRef;
    ModName_ otherMod = null;
			
    for lnkRef in all(obj<-"*") do 
    {
      otherMod = module (sourceVersion lnkRef);
      if (!null otherMod) 
      {
        if ((!isDeleted otherMod) && (null data(sourceVersion lnkRef))) 
	{
          load((sourceVersion lnkRef),false);
        }
      }
    }
		
    for lnk in obj <- "*" do 
    { 
      Object src = source lnk;
			
      Baseline b = getMostRecentBaseline(module src);
      int majorBL=-1;
      int minorBL=-1;
			
      if(null b)
      {
        majorBL=0;
        minorBL=0;
      }
      else
      {
        majorBL=major b;
        minorBL=minor b;		
      }
			
      inLinks=name(module src) ", Baseline " majorBL "." minorBL "";
			
      string dummy2;
      if(!find(inLinkSkip, inLinks, dummy2))
      {
        put(inLinkSkip, inLinks, inLinks);
      }
    }
  }
	
  string gesamtAusgabe="";
  bool isFirst=true;
  bool hasOutLinks=false;
  for myIterator in outLinkSkip do
  {
    string myKey = (string key(outLinkSkip));
    string myValue = "";
	
    if(find(outLinkSkip , myKey, myValue))
    {
      if(isFirst)
      {
        gesamtAusgabe=gesamtAusgabe "{\\b Out-Links }" ;
	isFirst=false;
      }
		   
      gesamtAusgabe=gesamtAusgabe "\n" myKey;
      hasOutLinks=true;
    }
  }
	
  // Ne Zeile zwischen machen
  if(hasOutLinks)
  {
    gesamtAusgabe=gesamtAusgabe "\n\n";
  }
	
  isFirst=true;
	
  for myIterator in inLinkSkip do
  {
    string myKey = (string key(inLinkSkip));
    string myValue = "";
	
    if(find(inLinkSkip , myKey, myValue))
    {
      if(isFirst)
      {
        gesamtAusgabe=gesamtAusgabe "{\\b In-Links }" ;
	isFirst=false;
      }
		   
      gesamtAusgabe=gesamtAusgabe "\n" myKey	   
    }
  }
	
  delete outLinkSkip;
  delete inLinkSkip;
	
  return gesamtAusgabe;
}

Problem

A DXL generated HTML string should be shown in a browser

Approach

Using the DBE Element htmlView it is possible to use an instance from the installation of the Clients Internet Explorer and show generated HTML Files.

Solution

// Global UI Elements
DB mainWindow;
DBE browser;
DBE test;

// Global variables
bool isFirstLoad=true;

void setBrowserHTML(string html){ 
  Buffer b = create;
  string s = html;
  print s;
  b = s;
  set(browser, b);
  delete b;
} 

string myHTML="Add your html code here";

bool onHTMLBeforeNavigate(DBE dbe, string URL, string frame, string body)
{ 
  string buttons[] = {"OK"};
  string message = "Before navigate - URL: " URL "\r\nFrame: " frame "\r\nPostData: " body "\r\n";
  print message "";
  return true;
} 
void onHTMLDocComplete(DBE dbe, string URL){ 
  string buttons[] = {"OK"};
  string message = "Document complete - URL: " URL "\r\n";
  print message "";
  string s = get(dbe);
  print "url: " s "\r\n";
	
  if(isFirstLoad)
  {
    setBrowserHTML(myHTML);
    isFirstLoad=false;
  }
} 
bool onHTMLError(DBE dbe, string URL, string frame, int error){ 
  string buttons[] = {"OK"};
  string message = "Navigate error - URL: " URL "; Frame: " frame "; Error: " error "\r\n"; 
  print message "" ;
  return true;
} 
void onHTMLProgress(DBE dbe, int percentage){ 
  string buttons[] = {"OK"};
  string message = "Percentage complete: " percentage "%\r\n";
  print message;
  return true;
} 

void showHTMLCallBack(DB x)
{
  setBrowserHTML(myHTML);	
}
	
void main(void)
{
  mainWindow=create("Traceability Master Professional");
  browser = htmlView(mainWindow, 1024, 768, "", 
  onHTMLBeforeNavigate, onHTMLDocComplete, onHTMLError, onHTMLProgress),
  test=apply(mainWindow, "Show HTML", showHTMLCallBack);		
  realize mainWindow;
  show mainWindow;
}

main();

Loop all views in module

Module m=current;
string myView;

for myView in views m do 
{
   print myView "\n";
}

Example: Get all Filter Strings from all views in current Module

Module m=current;
string myView;

for myView in views m do 
{
   print myView "\t";
   View v=view(myView);
   load(m,v);
   Filter f=current;

   if(null f)
   {
	print "no filter\n"
   }
   else
   {
      string filterString=stringOf(m,f);
      print filterString "\n";
   }
}

Loop all attributes on module

Module m=current;
string attribute;
for attribute in m do {
	print attribute "\n";
}

Loop through all In-Links (with Baselines) of an Object

Object obj=current;

Link lnk;
LinkRef lnkRef;
ModName_ otherMod = null;
string linkModuleString;

// I.e. for different Projects with different
// Link-Module Locations
string projName = name(current Project);	
if(projName=="VarCo")
{
  // Links zu Change Request
  linkModuleString = "/VarCo/20 Sample Project/22 Links/Changes"; 
}
else
{
  // Links zu Change Request
  linkModuleString = "/Project/90 Administration/Changes"; 
}
	
int lnkCount=0;
	
Item linkModItem = itemFromID(uniqueID(item(linkModuleString)));
linkModName = fullName(linkModItem);
	
for lnkRef in all(obj<-linkModName) do 
{
  otherMod = module (sourceVersion lnkRef);
  if (!null otherMod) 
  {
    if ((!isDeleted otherMod) && (null data(sourceVersion lnkRef))) 
    {
      load((sourceVersion lnkRef),false);
    }
  }
}
	
for lnk in all(obj<-linkModName) do 
{
  // Get In-Link Object
  Object src = source lnk;
  if ( isDeleted(src) || null(src) ) continue;
	
  identifierCC =  identifier(src) "";
  lnkCount++;
}

Loop through out-links of an object

Object obj=current;

Link lnk;

for lnk in obj->"*" do
{
  string tmn=fullName target(lnk);

  if(!open module tmn)
  {
    read(tmn,false);
  }

  Object tgt = target(lnk);
  print identifier(tgt) "\n";
}

Loop through all (with baselines) out-links of an object

First Approach

Module baselineModul=current;                                         

string satisfiesModuleString = "/NDS/90 Administration/Satisfies";
int object_count=0;
int link_count=0;

Object blObject;    
for blObject in baselineModul do 
{
	// Laufe durch die gebaselinten Outlinks
	Link lnk;
	for lnk in all(blObject->satisfiesModuleString) do
	{
		ModuleVersion mvTarget=targetVersion(lnk);
		if(null data(mvTarget)) 
		{
			load(mvTarget,false);			
		}
		
		Object tgt=target(lnk);
		link_count++;
    }
	 
	 object_count++;
}

print "Link Count: " link_count "\nObject Count: "  object_count "";

Second Approach

Module baselineModul=current;

ModName_ otherMod=null;                                                

string satisfiesModuleString = "/NDS/90 Administration/Satisfies";
int object_count=0;
int link_count=0;

Object blObject;    
for blObject in baselineModul do 
{
    	
	// Laufe durch die gebaselinten Outlinks
	Link lnk;
	for lnk in all(blObject->satisfiesModuleString) do
	{
	  	string tmn=fullName target(lnk);
	  
	  	otherMod = module (targetVersion lnk);
	  	if (!null otherMod) 
	  	{
	   		if ((!isDeleted otherMod) && (null data(targetVersion lnk))) 
			{
			
				load((targetVersion lnk),false);
			}
	  	}			  
	
	  	Object tgt = target(lnk);
	  	
	  	if(null tgt)
	  	{
		  	print "Is null das Teil!\n";
	  	}
	  	else
	  	{
		  	Baseline blI= baselineInfo(module tgt);
		  	print major(blI) " " minor (blI) "\n";
	  	}
	  	
  		link_count++;
	 }
	 
	 object_count++;
}

print "Link Count: " link_count "\nObject Count: "  object_count "";

Loop through all (with baselines) out-links without opening the target module

Object obj=current;

Link lnk;
for lnk in all(obj->"*") do
{
	ModuleVersion mvTarget=targetVersion(lnk);
	Baseline b = baseline(mvTarget);
	if(!null b)
      {
		// Wenn der Link nicht auf Current geht
		print major(b) "." minor(b) "\n";
      }
	print fullName(mvTarget) " " targetAbsNo(lnk) "\n";
}

Problem

Es soll geprüft werden, ob ein User Mitglied eienr Gruppe ist.
It shall be checked whether a user is a member of a group or not..

Lösung – Solution

User u= find();
Group g=find("ChangeControlBoard");
bool isCCB=member(g,u);