Angebotskalkulation in Excel

Inhalt

Kurzbeschreibung und Zweck der Funktion

Für die Kalkulation der Preise von Stahlrohren oder Metallerzeugnissen sollen bestehende Excel-Dateien verwendet und mit den Auftragspositionen verknüpft werden. Dafür müssen Daten an Excel übergeben werden und Daten wieder aus Excel zurück in die Auftragspositionen übernommen werden. Außerdem sollen zusätzlich zu den Tabellen Angebote und Angebotspositionen die Ergebnisse von beliebigen SQL-Statements nach Excel geschrieben werden können. Als Basis dient die Entwicklung der Aigner-Kisten-Angebotskalkulation in Excel, die entsprechend erweitert wurde.

Die Entwicklung wurde in Abstimmung mit den neuen Anforderungen vorgenommen und als Ticket 135605 eingecheckt.

Technische Beschreibung der Funktionalität

Wenn für eine Auftragsposition die Funktion „Externe Positionskalkulation“ aufgerufen wird, so gibt es folgende Vorgangsweise:

Der User wird aufgefordert die akt. Änderungen der Auftragsposition zu speichern, falls das noch nicht gemacht wurde.
Es wird geprüft, ob für den gewählten Artikel im Artikelstamm eine .XLS oder .XLSX angehängt ist. Das ist das Basisfile für die Kalkulation.
Es wird geprüft, ob es im DMS für diesen Auftrag bereits eine best. Excel-Datei für die Kalkulation für diesen Artikel und diese Position gibt:
Dateiname = POS< APOSINX>_<DATEINAMEAUSSTAMMDATEN>.xls(x)
1. Falls ja, so wird diese Kalkulationsdatei aus dem DMS heruntergeladen und in Excel geöffnet.
2. Falls nein, so wird die Basisdatei aus dem Stammartikel kopiert und somit eine neue Kalkulationsdatei erstellt.
Die Excel-Kalkulationsdatei wird geöffnet und nach den Registerblättern Mapping (notwendig) und SQLMapping (optional) durchsucht. Die Registerblätter können an einer beliebigen Stelle stehen.
Wenn das Registerblatt SQLMapping gefunden wird, so werden die angegebenen SQL-Statements über den Webservice ausgeführt und die Ergebnisse als Tabelle dem Dataset hinzugefügt. Über das Mapping kann man sich folgend auf diese Tabellen beziehen und somit beliebige SQL-Ergebnisse von Pollex nach Excel schreiben lassen.
Nun werden die Werte aus Pollex laut Mapping nach Excel geschrieben. Das Mapping ist jeweils in der Excel-Datei definiert.
Das Pollex-TC wartet nun auf das Schließen der Excel-Kalkulation.
Nach dem Schließen prüft die Funktion, welche Werte sich in Excel geändert haben und übernimmt geänderte Werte von Excel in das DBGrid. Anschließend wird für die erste geänderte Spalte mit dem Suffix VK_ das CellChange-Event ausgelöst.
Die geänderte Excel-Kalkulationsdatei wird im DMS für diesen Auftrag und diese Position gespeichert (s. (c)).
Die Änderungen der Auftragspositionen werden sofort gespeichert.

Aufruf der Funktion

Die Funktion wird über Auswahl einer Auftragsposition, Rechtsklick und Menübefehl „Externe Positionskalkulation“ aufgerufen.

Technische Voraussetzungen

Excel-Version Microsoft Excel 2016 (oder höher) oder Microsoft Office 365

Betriebssystem Microsoft Windows 8.1 oder höher

SQL-Mapping definieren

Anforderungen

Damit ein gültiges SQL-Mapping definiert wird, muss die Excel-Datei ein Registerblatt mit dem Namen SQLMapping enthalten. Als Excel-Datei können sowohl .XLS als auch .XLSX verwendet werden.

Als Muster- bzw. Beispielmapping ist die Datei Bsp-Mapping.xlsx angehängt.

Bsp-Mapping.xlsx

Verwendete Spalten im Mapping

Spalte A [SQL] Der SQL-Befehl, der ausgeführt werden soll. Wenn ein Referenz-Feld (Verknüpfungsfeld) in Spalte B verwendet werden soll, so muss das SQL-Statement %reference% enthalten. Dieser Wert wird dann durch den Wert des Referenz-Feldes ersetzt.

Spalte B [Referenz-Feld] Fixer Wert oder Verweis auf ein Feld in einer Tabelle, das als Referenz/Verknüpfungs-Feld dient. Zu Testzwecken kann hier ein fixer Wert (Ganzzahl oder Text) eingetragen werden. Andernfalls verweist man über den Syntax Tabellenname.Feldname auf das gewünschte Feld einer Tabelle. Als Tabellenname können Angebote oder Angebotspositionen verwendet werden.

Spalte C [Tabellenname] Unter diesem Namen werden die SQL-Ergebnisse dem Dataset hinzugefügt. Über das Mapping kann man sich später auf Ergebnisse dieser Tabelle beziehen.

Spalte D [Beschreibung] Eine beliebige/freie Beschreibung des Wertes

Limits beim Einlesen

Die Zeilen im SQL-Mapping werden von oben nach unten eingelesen, solange ein Wert in Spalte 1 gefunden wird.

Wenn als Referenzfeld eine Tabelle oder ein Feld angegeben wird, das nicht existiert, so wird die Verarbeitung der Kalkulation mit einer Fehlermeldung abgebrochen. Wenn das SQL-Statement einen Fehler verursacht, so wird die Verarbeitung ebenfalls mit einem Fehler abgebrochen.

Mapping definieren

Anforderungen

Damit ein gültiges Mapping definiert wird, muss die Excel-Datei ein Registerblatt mit dem Namen Mapping enthalten. Als Excel-Datei können sowohl .XLS als auch .XLSX verwendet werden.

Verwendete Spalten im Mapping

Spalte A [Tabelle/Zeilen] Hier gibt es 3 Möglichkeiten: Angabe einer Tabelle, Angabe einer spezifischen Zeile einer Tabelle oder Angabe aller Datenzeilen für die komplette Ausgabe der SQL-Ergebnisse (s. Punkt 6.3).

Spalte B [Crm Field/Fields] Name der DB-Spalte in der DB-Tabelle oder <Alle Spalten> (s. Punkt 6.3).

Spalte C [Excel Cellname] Hier kann man entweder auf einen Excel-Zellennamen verweisen (die betroffene Zelle muss dann in Excel mit dem angegebenen Namen versehen werden) oder man verweist auf Registerblatt.Zelle (Bsp: Tabelle1.A2). Wenn Tabelle.Rows in Spalte A angegeben wurde, so muss ein Excel-Zellenbereich angegeben werden.

Spalte D [Direction] PollexToExcel (Werte werden nur nach Excel übernommen), ExcelToPollex (Werte werden nur von Excel nach Pollex übernommen) oder All (Werte werden in beide Richtungen übernommen). Achtung: ExcelToPollex oder All dürfen nur für die Tabelle Angebotspositionen verwendet werden.

Spalte E [Beschreibung] Eine beliebige/freie Beschreibung des Wertes

Spalte F [Crm Value / Conversion] Wird für spezielle Umwandlungen und Sonderfälle verwendet (s. Punkt 6.7 und 6.8)

Angabe von Tabellen- und Zeilen-Verweisen in Spalte A

Verweis auf eine Tabelle => Bsp: Angebote
Damit wird aut. die erste Zeile der angegebenen Tabelle eingelesen.
Verweis auf eine Zeile der Tabelle => Bsp: chem_elemente.Row[x]
Damit wird die angegebene Zeile der Tabelle eingelesen.
X=1 ist die 1. Zeile, X=2 ist die 2. Zeile.
Verweis auf die gesamte Tabelle => Bsp: chem_elemente.Rows. In diesem Fall wird der Inhalt der gesamten Tabelle in Excel ausgegeben. Es ist wichtig, dass in der Spalte C ein Excel-Zellenbereich angegeben wird, der sich mit der Spaltenanzahl und der maximalen Zeilenanzahl der DB-Tabelle deckt.
Der angegebene Bereich wird vor dem Befüllen geleert. Der Wert der Spalte B (CRM Field/Fields) wird ignoriert, der Wert <Alle Spalten> hat nur Info-Charakter.

Limits beim Einlesen

Die Zeilen im Mapping werden von oben nach unten eingelesen, solange Werte für Spalte 1 und Spalte 2 gefunden werden. Sobald beide Werte leer sind, wird der Einlesevorgang beendet.

Falls DB-Tabellen oder -Spalten angegeben werden, die nicht gefunden werden, so wird diese Zeile übersprungen und rot markiert. Wenn über das SQL-Mapping zuvor weitere Tabellen ergänzt wurden, so darf bei deren Verwendung im Mapping nur die Richtung PollexToExcel verwendet werden, andernfalls wird der Einlesevorgang mit einem Fehler abgebrochen.

Einfaches Lesen und Schreiben von Excel-Werten

Um Werte nach Excel zu schreiben bzw. von Excel zu lesen, kann man man die gewünschte Excel-Zelle mit dem entsprechenden Namen versehen. Dieser Name ist über das Mapping in der Spalte C (Excel Cellname) angegeben. Die Funktion spricht die Excel-Zelle über diesen Namen an. Dabei ist es nicht wichtig, in welchem Registerblatt sich die Zelle befindet. Somit ist es auch möglich, eine Eingabe/Ausgabe auf mehreren Registerblättern zu erreichen.

Alternativ kann man in der Spalte C (Excel Cellname) auch direkt auf eine Zelle in einem Registerblatt verweisen, Beispiel: Tabelle1.A2

Wenn im Mapping in der Spalte A (Tabelle/Zeilen) Tabelle.Rows angegeben wurde, so muss hier auf einen Namen verwiesen werden, der einem Zellenbereich zugewiesen wurde. In diesem Fall werden vor der Synchronisierung alle Inhalte in diesem Bereich gelöscht und anschließend alle Inhalte der Tabelle nach Excel geschrieben. Der angegebene Bereich soll sich von der Spaltenanzahl und der maximalen Anzahl an Zeilen der DB-Tabelle (vom SQL-Statement) decken.

Als Muster- bzw. Beispielmapping ist die Datei Bsp-Mapping.xlsx angehängt.

Bsp-Mapping.xlsx

Anforderungen an Excel-Zellformatierungen

Um Werte korrekt nach Excel zu schreiben bzw. diese korrekt nach Pollex zu übernehmen, ist es notwendig, dass die angegebenen Zielzellen mit der passenden Formatierung versehen sind, die mit der Datenbank-Spalte übereinstimmen. Somit sollte eine Texteingabe/-ausgabe als Text formatiert sein, Datum als Datum und Zahlen als Zahlen (inkl. notwendiger Dezimalzahlen). Andernfalls kann es bei der Übernahme bzw. beim Schreiben der Werte zu unerwünschten Umwandlungen oder gar Fehlern kommen, falls die Werte nicht in das DBGrid zurückgeschrieben werden können.

Sonderfall „Kombinierte Werte in Excel ausgeben“

Wenn man in Excel einen kombinierten Wert ausgeben möchte – Beispiel Adresse in einer Zeile – so lässt man die betroffenen DB-Spalten in Excel in Detailzellen schreiben (mit der Richtung PollexToExcel) und kombiniert diese in der Ausgabe mit einer Excel-Formel in der Ausgabezelle.

Beispiel

Lieferadresse soll in einer Zeile ausgegeben werden. Die Adresse verteilt sich aber auf die Felder (L_N1, L_STR, L_LAND, L_PLZ usw.)
In diesem Fall verwendet man im Registerblatt Mapping die Spalte F (Crm Value) um die DB-Werte einzeln zu schreiben. Dazu kann man bspw. als „Excel Cellname“ jeweils den Titel vom DB-Feld verwenden (L_N1, L_STR usw.) und die Zelle in der Spalte F mit dem angegebenen Namen versehen. Die Werte werden somit direkt im Registerblatt Mapping in die Spalte F geschrieben.
In der Hauptausgabe (Lieferanschrift) setzt man die internen Werte nun mit einer simplen Excel-Formel zusammen. Beispiel: =WENN(L_N1<>"";L_N1 & ", " &L_STR&", "&L_PLZ & " " &L_ORT&", "&L_LAND;STRASSE&", "&PLZ&" "&ORT&", "&LAND)
Im Beispielmapping lässt sich das für die Zellen/Zeilen Tabelle1: A2 und Mapping: Zeilen 4 – 12 nachvollziehen.

Sonderfall „Konvertierung von Werten“

Es kann vorkommen, dass in Excel andere Werte gespeichert sind als in der Datenbank. Beispiel: Im Statistik2-Feld ist entweder 1 oder 0 gespeichert, in Excel allerdings „x“ oder „“. In diesem Fall muss eine Aufteilung (Lesen/Schreiben) und Konvertierung vorgenommen werden:

Beispiel:

Im Feld „alu“ wird in Excel x oder gar nichts eingetragen. In der Datenbank ist aber 1 oder 0 gespeichert.
Übernahme nach Excel: Als Direction muss PollextoExcel angegeben werden. Als Zielfeld in Excel wird die echte Zelle angegeben (alu). Die Funktion schreibt dann zuerst 1 oder 0 nach Excel. Zusätzlich kann man nun in der Spalte F (Conversion) eine Excel-Formel angeben, die im Anschluss den Wert von 1/0 nach „x“/leer umwandelt
Bsp: =WENN(ODER(alu=1;alu="1");"x";"")
Übernahme nach Pollex: Als Direction muss ExcelToPollex angegeben werden. Als Basisfeld in Excel wird eine interne Zelle (alu2) angegeben. Diese kann z. B. direkt im Mapping in Spalte F zu finden sein. In dieser Zelle ist jedenfalls eine Konvertierungsformel angegeben, die den Excel-Wert wieder in 1/0 umwandelt:
Bsp: =WENN(KLEIN(GLÄTTEN(alu))="x";1;0)
Im Beispielmapping lässt sich das für die Mapping-Zeilen 25 bis 34 (Statistik2 – Statistik6) nachvollziehen.

Rückübernahme von Werten nach Pollex

Einschränkungen

Für die Rückübernahme von Werten (Richtung PollextoExcel oder All) kann nur die Tabelle Angebotspositionen angegeben werden.

Aktualisierung der Werte

Änderungen von Excel nach Pollex werden direkt in das DBGrid übertragen. Sofern ein Feld mit dem Prefix „VK_“ geändert wurde, wird für das erste Feld (das geändert wurde) ein ChangeEvent im DBGrid ausgeführt.