Skriptbeispiel im Jitterbit App Builder - Benutzerdefinierter Excel Import

Dieses Plugin dient zum Importieren eines formatierten Excel-Dokuments.

Es ermöglicht die Einrückung von Daten um eine Spalte, ignoriert Titelformatierungen vor dem Zugriff auf Kopfzeilen, überspringt Header, überspringt Zeilen mit Zwischensummen/Gesamtsummen und ignoriert vorhandene Fußzeilen. Bei laufenden Business-Objekt-Ereignissen:

BusinessObjectID bietet die TableID des Ziels für importierte Daten.

File Gibt die zu importierende Datei an (Binärdatenfeld)

AttachmentID Und AttachmentTypeID: GUIDs zur Identifizierung der Quelle importierter Daten

if (columnName == "Billing") //wobei „Abrechnung“ der Wert in der zweiten Spalte für den Anfang der ersten Header ist.

if (firstColumnName == "Grand Total:")//Wert der ersten Spalte, erste Zeile nach allen Datensätzen der gewünschten importierten Daten

if (SecondColumnName == "Invoice Number:") //Wert der ersten Spalte der letzten Zeile der Titeldaten vor der ersten Header. Unten werden die Zielspalten mit der Anzahl der Excel-Spaltennummern für die Quelldaten abgeglichen. Die folgende Zeile gibt beispielsweise an, dass die 10. Datenspalte abgerufen und in das Feld „Produkt“ im Ziel-Business-Objekt eingefügt werden soll.

Anwendungsfall

Dies kann beispielsweise verwendet werden, wenn ein anderer Dienst oder ein interner Geschäftsprozess über eine formatierte Excel-Datei verwaltet wird, die gepflegt und später als Datenquelle für einen laufenden Geschäftsprozess in den App Builder übernommen werden muss. Dies ist hilfreich bei einer Umstellung, wenn ein Geschäftsprozess im App Builder nur in Teilen und nicht auf einmal ersetzt wird. Dies ist die beste Option, um eine genaue und nahtlose Datenkontinuität zu gewährleisten, ohne dass das Unternehmen zusätzliche Schritte zur Formatierung der Daten unternehmen muss, bevor diese für aktualisierte Prozesse in den App Builder übernommen werden.

Plugin-Referenzen

Spalte	Datentyp	Beschreibung
BusinessObjectID	Unique ID	stellt die TableID des Ziels für importierte Daten bereit
File	binary data field	stellt die zu importierende Datei bereit
AttachmentID	Unique ID	Quelle der importierten Daten identifizieren
AttachmentTypeID	Unique ID	Quelle der importierten Daten identifizieren

Benutzerdefiniertes Excel Script

#r "ExcelDataReader.dll"
using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Vinyl.Sdk.Filtering;
// TARGET
// The MRP table it will populate
string strBusinessObjectID;
strBusinessObjectID = Row["BusinessObjectID"].Value.ToString();
Guid targetMRPTableId = Guid.Parse(strBusinessObjectID);
// SOURCE
// File to be imported
var file = (byte[])Row["File"].Value;
string strAttachmentID;
strAttachmentID = Row["AttachmentID"].Value.ToString();
string strAttachmentTypeID;
strAttachmentTypeID = Row["AttachmentTypeID"].Value.ToString();
//Guid attachmentID = Guid.Parse(strAttachmentID);
// SERVICES
var tableService = Services.GetService<ITableService>();
var eventService = Services.GetService<IEventService>();
// EVENT TABLES
var mrpTable = await GetEventTable(eventService, tableService, targetMRPTableId);
// Read file
using (var stream = new MemoryStream(file))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        // Generates the Primary Key for the SRP as it will also be used as a FK
        // on the MRP
        //var id = Guid.Parse(strAttachmentID);
        await ConsumeMRP(eventService, mrpTable, reader, strAttachmentID, strAttachmentTypeID);
    }
}
/// <sumary>
/// For a given table id, returns its EventTable
/// </sumary>
private async Task<EventTable> GetEventTable(
    IEventService eventService,
    ITableService tableService,
    Guid tableId)
{
    // This workaround is not needed anymore in the latest 2.7 hotfix
    // but anything before then, to get the EventTable from a given
    // tableId, we need to raise a filter event with no rows
    var table = tableService.GetTable(tableId);
    var filter = table.CreateFilter();
    filter.Limit = 0;

    var eventTable = await eventService.InvokeFilterEventAsync(filter);
    return eventTable;
}
/// <sumary>
/// Tries to set a cell value, checking if column is available first
/// </sumary>
private void TrySetValue(EventRow row, string columnName, object value)
{
    if (row.TryGetCell(columnName, out EventCell cell))
    {
        cell.Value = value;
    }
}
private string GetColumnName(object value)
{
    string str = Convert.ToString(value ?? "");
    str = Regex.Replace(str, " *[\r\n]+", " ");
    return str.Trim();
}
/// <sumary>
/// Populate the MRP until the first cell is empty
/// </sumary>
private async Task ConsumeMRP(
    IEventService eventService,
    EventTable importTable,
    IExcelDataReader reader,
    string attachmentID,
    string attachmentTypeID)
    {
    while(reader.Read())
    {
        var columnName = reader
            .GetValue(1)?
            .ToString()
            .Replace(":", "")
            .Trim();

        if (columnName == "Billing") {
            // MRP started
            break;
        }
    }


    var fields = new List<string>();

        for (var index = 1; index < reader.FieldCount; index++)
        {
            fields.Add(GetColumnName(reader.GetValue(index)));
        }
    if (!reader.Read())
    {
        // MRP cut short? Throw exception?
        return;
    }
    // Append sub-title
    for (var index = 0; index < fields.Count; index++)
    {
            if (!reader.IsDBNull(index+1))
            {
                fields[index] += " " + GetColumnName(reader.GetValue(index+1));
            }
    }

    int rowcount= 0;

    while(reader.Read())
    {
        rowcount = rowcount+1;
        string SecondColumnName="";
        string firstColumnName="";

            if(! reader.IsDBNull(1))
            {
                SecondColumnName = reader.GetValue(1).ToString().Trim();
            }
            else
            {
                SecondColumnName ="";
            }
            if(! reader.IsDBNull(0))
            {
                firstColumnName = reader.GetValue(0).ToString();
            }
            else
            {
                firstColumnName="";
            }



        if (firstColumnName == "Grand Total:")
        {
            break;
        }
        if (SecondColumnName == "Invoice Number:")
        {
            continue;
        }
        if (SecondColumnName == "")
        {
            continue;
        }

            // Creates a new row in the staging table
            var dataRow = await eventService.InvokeNewEventAsync(importTable);

            dataRow["stGarnishmentID"].Value = Guid.NewGuid();
            dataRow["AttachmentID"].Value = attachmentID;
            dataRow["AttachmentTypeID"].Value = attachmentTypeID;
            //testRow6["Product"].Value = reader.GetValue(10);
         //   await eventService.InvokeInsertEventAsync(testRow6);

            //row["Invoice Number"].Value=reader.GetValue(1);

            for (var index = 0; index < fields.Count; index++)
            {
                var columnName = fields[index];
                var value = Convert.ToString(reader.GetValue(index+1));
                TrySetValue(dataRow, columnName, value);
            }
         await eventService.InvokeInsertEventAsync(dataRow);



    }

}