Exemplo de Script - Importação Personalizada do Excel¶

#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);



    }

}