Daten aus Tabellen in Text parsen
Das Modul hat versteckte Attribute. Damit diese aktiv übernommen werden reicht es nicht aus, den Code in ein neues Modul zu kopieren. Man muss das Modul aus der Datei nach VBA importieren.
Bild zum Import
Es war schon immer mein Traum, Daten aus Einer Tabelle oder Abfrage relativ einfach in ein Text zu parsen. Mit der Klasse JSF habe ich mir diesen Traum umgesetzt.
Im Prinzip ist es einfach. In einem Text können mit dem Pattern #{[inhalt]} Daten aus einer Tabelle oder eines Dictionary ausgelesen werden. Zudem kann darin jeder VBA-Code verwendet werden, der durch eval() ausgewertet werden kann.
pattern = "Willkommen #{user}. Es ist jetzt #{TimeValue(now())} und du bist seit #{DateDiff('d', start_date, now)} Tagen dabei." ?JSF("T_JSF_EXAMPLE").parse(pattern) Willkommen Stefan. Es ist jetzt 12:51:41 und du bist seit 39 Tagen dabei.
Es gibt 2 Bedingte Komplilierungen. Je nachdem, ob die Module geladen sind oder nicht. Im Zweifelsfall beide auf False stellen. Man hat dann einfach ein bischen weniger möglichkeiten
' /** ' * Wenn sprintf existiert, dann kann mit $format das Format analog zu sprintf() mitgegeben werden. Ansonsten wird der $-Teil ignoriert ' * @url http://wiki.yaslaw.info/doku.php/vba/functions/printf/index ' * @example ?JSF().parse("#{now $To}") -> 20181109 ' * @example ?JSF().parse("#{1.3 $.3f}") -> 1.300 ' * @example ?JSF("DUAL").parse("#{now $TO}_#{id $6d}_DATA.CSV") -> 20181109_092806_000001_DATA.CSV ' */ #Const sPrintF_exists = True
'/** ' * Wenn mein json-Modul vorhanden ist, dann kann man auch json-Strings als Paramter übergeben. ' * Achtung! Nur eindimensionale Dictionary! ' * @url http://wiki.yaslaw.info/doku.php/vba/cast/json ' * @example ?JSF("{nr:1,name:Yaslaw}").parse("#{name}, du hast die Nummer #{nr} ") -> Yaslaw, du hast die Nummer 1 ' */ #Const json_exists = True
'/** ' * Erstellt ein neues Objekt und initialisiert es ' * @param Dictionary/Recordset/TableDef/QueryDef/Iterator/Sql/TableName/QueryName/ ' * @param String Einen Filter umd auf den richtigen Datensatz zu springen. Nur gültig für Recordset, TableDef und ähnliches ' * @param jsfParams Paramter um die Klasse zu steuern ' * @return JSF ' */ Public Function instance( _ Optional ByRef iSource As Variant = Nothing, _ Optional ByVal iCriteria As String, _ Optional ByVal iParams As jsfParams = jsfParams.[_Default] _ ) As JSF
'/** ' * Initialisiert ein JSF-Objekt ' * @param Dictionary/Recordset/TableDef/QueryDef/Iterator/Sql/TableName/QueryName/ ' * @param String Einen Filter umd auf den richtigen Datensatz zu springen. Nur gültig für Recordset, TableDef und ähnliches ' * @param jsfParams Paramter um die Klasse zu steuern ' * @return JSF ' */ Public Function construct( _ Optional ByRef iSource As Variant = Nothing, _ Optional ByVal iCriteria As String, _ Optional ByVal iParams As jsfParams = jsfParams.[_Default] _ ) As JSF
'/** ' * Parst einen Text mit den vorhandenen Daten ' * @param String ' * @return String ' */ Public Function parse(ByVal iString As String) As String
'/** ' * Fügt ein Wert der Map zu oder überschreibt ihn ' * @param String Key ' * @param Variant Value ' * @return JSF ' */ Public Function add(ByVal iKey As String, ByVal iValue As Variant) As JSF
Die folgenden Quellen sind zulässig
Ab Version 1.3 gibt es Modifiers, mit denen man die Werte belästigen kann.
Die grundsätzliche Syntax ist value|modifier:param1:param2:…:paramX
. Der Modifier wird mit einem |
angehäng, gefolgt von den Paramtern, die mit :
getrennt werden
Es können mehrere Modifier auf ein Element angewendet werden. Sie werden in der angegeben Reihenfolge abgearbeitet. value|modifier1:param11|modifier2|modifier2:param31
.
Modifier | Atribute | Beschreibung / VBA-Befehl | Beispiel |
---|---|---|---|
format | $format | VBA.format(wert, $format) | |format:'0.00 ' |
trim | VBA.Trim(wert) | |trim |
|
rev | VBA.StrReverse(wert) | |rev |
|
lcase | VBA.LCase(wert) | |lcase |
|
lower | Alais zu lcase | |lower |
|
ucase | VBA.UCase(wert) | |ucase |
|
upper | Alais zu ucase | |upper |
|
proper | VBA.strConv(wert, vbProperCase) | |proper |
|
strconv | $convType als Integer | VBA.strConv(wert, $convType) | |strconv:3 |
tech | Falls cast_techName im Projekt vorhanden ist, wird dieses ausgeführt. techName(wert) | |tech |
|
nz | $default=Leerstring | VBA.nz(wert, $default) nz funktioniert auch, wenn das Element nicht in der Liste existiert | |nz ' |
round | $size=0 | VBA.round(wert, $size) | |round:2 |
sql | wandelt den Wert in ein SQL-Wert | |sql |
|
code | Alias zu sql | |code |
|
reg_replace | $pattern, $replace | Führt ein RegEx-Replace durch | |reg_replace:'\d(\w+)':'$1 ' |
rxr | $pattern, $replace | Alias zu reg_replace | |rxr:'\d(\w+)':'$1 ' |
lpad | $len, $padString = ' ' | Links mit Zeichen auffüllen | |lpad:3 |lpad:3:'_ ' |
rpad | $len, $padString = ' ' | Rechts mit Zeichen auffüllen | |rpad:3 |rpad:3:'_ ' |
item | $key/$index, $defaultKey=null | Wählt aus einer Unterliste den entsprechenden Wert | |
tbl | $range = Leerstring | Erstellt eine ADODB-Excel-tabelle [tblname$range] | |Sheet1 |Sheet1:'A1:A3 ' |
fld | $table | Erstellt ein SQL-String für ein Feld oder ein Tabelle. | |fld |fld:table |
|item:ort
|item:ort:unbekannt
|
? jsf("{name:Yaslaw, ort:null, sex:null}") _ .parse("#{sex|nz:m='m' ? 'Herr' : 'Frau'} #{name|upper} aus #{ort|nz:unbekannt|proper}") Herr YASLAW aus Unbekannt 'sex ist in der Tabelle gar nicht enthalten, wird jedoch mit nz gesetzt ? jsf("{name:Yaslaw, ort:null}").parse("#{sex|nz:'männlich'}") männlich 'b ist wieder ein Dictionary d Jsf("{a:A,b:{name:Hans,ort:Winterthur}}").parse(" #{b|item:name} aus #{b|item:1}") <String> 'Hans aus Winterthur' Set j = Jsf("{flag:ort,data:{name:Hans,ort:Winterthur,flag:test,country:Schweiz}}") 'Das Attribut flag ist nit in ' gefasst. Also wird versucht auch dieses zu paren. flag hat somit den Wert ort d j.parse("#{data|item:flag}") <String> 'Winterthur' 'Setzen wir flag hingegen in '', dann wird der Wert flag genommen um in der Unterliste zu suchen d j.parse("#{data|item:'flag'}") <String> 'test' 'Land ist in der Liste nicht vorhanden d j.parse("#{data|item:'land'}") <String> '<Err: item land not found in List data>' d j.parse("#{data|item:'land':'country'}") <String> 'Schweiz' 'fld und tpl Set j = jsf("{t1:table_1,t2:[table_2],f1:'[ort]', f2:PLZ}") d j.map <Dictionary> ( [t1] => <String> 'table_1' [t2] => <String> 'table_2' [f1] => <String> '[ort]' [f2] => <String> 'PLZ' ) ?j.parse("select #{f1|fld:a}, #{f2|fld|upper} from #{t1|tbl:'A1:F4'} a, #{t2|tbl} b") select [a].[ort], [PLZ] from [table_1$A1:F4] a, [table_2$] b
Meine Tabelle mit den Testdaten
Tabelle T_JSF_EXAMPLE | |||||
---|---|---|---|---|---|
ID | USER | VULGO | LOCATION | START_DATE | IS_IMPORTANT |
1 | Stefan | Yaslaw | Büro 1A | 01.10.2018 | True |
2 | Sandra | Büro 3 | 01.11.2018 | False | |
3 | Manuela | Manu | Home | 29.10.2018 | False |
4 | Thomas | 31.10.2018 | True |
'Einfaches auslesen des ersten Datensatzes der Tabelle und parsen der Daten ? JSF("T_JSF_EXAMPLE").parse("Welcome #{user}") Welcome Stefan 'Mit einer VBA-Funktion. In diesem Beispiel nz() ? JSF("T_JSF_EXAMPLE").parse("Welcome #{nz(vulgo, user)}") Welcome Yaslaw 'dasselbe mit einem Datenkriterium ? JSF("T_JSF_EXAMPLE", "id=2").parse("Welcome #{nz(vulgo, user)}") Welcome Sandra 'mit einem if - Schreibweise (criteria ? true) ? JSF("T_JSF_EXAMPLE", "id=1").parse("Welcome #{user}#{is_important ? '!'}") Welcome Stefan! ? JSF("T_JSF_EXAMPLE", "id=2").parse("Welcome #{user}#{is_important ? '!'}") Welcome Sandra 'und einem else - Schreibweise (criteria ? true : false) ? JSF("T_JSF_EXAMPLE", "id=2").parse("Welcome #{user}#{is_important ? '!' : '.'}") Welcome Sandra. 'Ein Not in Form eines ! ?JSF("T_JSF_EXAMPLE").parse("#{user} hat #{!IsNull(LOCATION) ? 'ein' : 'kein'} Arbeitsplatz") Stefan hat ein Arbeitsplatz ?JSF("T_JSF_EXAMPLE").parse("#{user} ist #{user != 'Sandra' ? 'nicht '}Sandra") Stefan ist nicht Sandra
Auch ein Array kann eine Quelle sein. Dann wird der Key als [index]
gehandhabt.
Es beginnt also mit [0]
? JSF(array("Yaslaw","Winterthur","Herr")).parse("Hallo #{[2]} #{[0]} aus #{[1]}") Hallo Herr Yaslaw aus Winterthur
Public Function test(ByVal iUserId As Long, ByVal iHutgroesse As Long) As String Dim rs As DAO.Recordset Dim parser As New JSF Set rs = CurrentDb.openRecordset("SELECT * FROM T_JSF_EXAMPLE WHERE ID=" & iUserId) parser.construct rs 'Noch einen eigenen Wert mitgeben parser.add "HutGr", iHutgroesse test = parser.parse("#{user} hat die Hutgrösse #{hutgr}") End Function
'Und der Test:
?test(3, 57)
Manuela hat die Hutgrösse 57
Und als Vergleich als Einzeiler
?JSF("T_JSF_EXAMPLE", "ID=3").add("HutGr", 57).parse("#{user} hat die Hutgrösse #{hutgr}") Manuela hat die Hutgrösse 57
Public Function test() As String Dim dict As New Dictionary Dim parseer As JSF 'Das Dictionary kähme natürlich woanders her. dict.add "Ort", "Winterthur" dict.add "PLZ", "8404" Set parser = JSF(dict) test = parser.parse("Willkommen in #{plz} #{ort}") End Function
?test
Willkommen in 8404 Winterthur
Man kann über einen kleinen Kniff die Klasse auch in einem SQL verwenden. Aber achtung, ist nicht sehr perfomant!
Als Erstes muss man noch eine Funktion schreiben, da man nicht direkt auf das Objekt zugreifen kann. Zum Beispiel
Public Function DJsf(ByVal iSource As String, Optional ByVal iCriteria As String, Optional ByVal iPattern As String) As String DJsf = JSF(iSource, iCriteria).parse(iPattern) End Function
Und hier mein Test Die Daten
code]SELECT * FROM T_VALUES; ID | VORNAME | NACHNAME ---|---------|--------- 1 | Hans | Muster 2 | Heidi | Knobel 3 | Sandra | Test SELECT * FROM T_PATTERNS; ID | PATTERN ---|---------------------------------- 1 | Person #{VORNAME} #{NACHNAME} 2 | #{NACHNAME}, #{VORNAME} ist Toll!
Und das SQL
SELECT v.*, djsf('T_VALUES', 'ID=' & v.id, p.pattern) AS txt FROM T_VALUES v, T_PATTERNS p; ID | VORNAME | NACHNAME | txt ---|---------|----------|------------------------ 1 | Hans | Muster | Person Hans Muster 1 | Hans | Muster | Muster, Hans ist Toll! 2 | Heidi | Knobel | Person Heidi Knobel 2 | Heidi | Knobel | Knobel, Heidi ist Toll! 3 | Sandra | Test | Person Sandra Test 3 | Sandra | Test | Test, Sandra ist Toll!
Für die Formatierungen, schaue direkt bei sprintf() mit Formatierungen
?JSF("T_JSF_EXAMPLE").parse("Hallo #{user} es ist #{now() $TT}") Hallo Stefan es ist 12:12:55
'Ein User-file ohne Formatierung ?JSF("select * from T_JSF_EXAMPLE where id=4").parse("#{now()}_UID#{id}_USER_REPORT.CSV") 09.11.2018 12:16:15_UID4_USER_REPORT.CSV 'und mit Formatierung über sprintf ?JSF("select * from T_JSF_EXAMPLE where id=4").parse("#{now() $TO}_UID#{id $3d}_USER_REPORT.CSV") 20181109_121538_UID004_USER_REPORT.CSV 'und dasselbe ohne sprintf mit Standard VBA Befehlen ?JSF("select * from T_JSF_EXAMPLE where id=4").parse("#{format(now(), 'YYYYMMDD_HHNNSS')}_UID#{String('0', 3-len(id)) & id}_USER_REPORT.CSV") 20181109_121830_UID4_USER_REPORT.CSV
Das Beispiel macht so nicht direkt Sinn. Erst in Komplexen Applikationen kann es vereinzelt Sinn ergeben.
?JSF("{ort:Winterthur,PLZ:8404}").parse("Willkommen in #{plz} #{ort}") Willkommen in 8404 Winterthur