How to read from excel in Acumatica

Hello everybody,

today I want to share with you how to read from attached excel document to Acumatica. The code goes below:

public class SOOrderEntryExt : PXGraphExtension<SOOrderEntry>
    public PXAction<SOOrder> LoadDataFromAttachment;
    [PXUIField(DisplayName = "Import from Attachment")]
    protected virtual IEnumerable loadDataFromAttachment(PXAdapter adapter)
        var notes = PXNoteAttribute.GetFileNotes(Base.Document.Cache, Base.Document.Current);
        foreach (var guid in notes)
            var fm = new PX.SM.UploadFileMaintenance();
            PX.SM.FileInfo fi = fm.GetFile(guid); //fm.GetFileWithNoData(guid);
            var bytes = fi.BinData;
            var newGr = PXGraph.CreateInstance<SOOrderEntry>();
            using (PX.Data.XLSXReader reader = new XLSXReader(bytes))
                Dictionary<String, Int32> indexes = reader.IndexKeyPairs.ToDictionary(p => p.Value.ToUpper(), p => p.Key);
                reader.MoveNext(); // skip the first row
                while (reader.MoveNext())
                    string inventory = reader.GetValue(1);
                    DateTime dt = DateTime.Parse(reader.GetValue(2));
                    string customer = reader.GetValue(3);
                    string item = reader.GetValue(4);
                    string warehouse = reader.GetValue(5);
                    decimal qty = decimal.Parse(reader.GetValue(6));
                    var newSoOrder = new SOOrder();
                    newSoOrder.OrderType = "IN";
                        customerID = SelectFrom<Customer>.Where<Customer.acctCD.IsEqual<@P.AsString>>.View
                        .Select(newGr, customer).FirstOrDefault().GetItem<Customer>().BAccountID;
                    newSoOrder.CustomerID = customerID;
                    var insertedOrder = newGr.Document.Insert(newSoOrder);
                    if (insertedOrder == null)
                        return adapter.Get();
                    var soline = newGr.Transactions.Insert();
                    newGr.Transactions.Cache.SetValueExt<SOLine.inventoryID>(soline, item);
                    newGr.Transactions.Cache.SetValueExt<SOLine.siteID>(soline, warehouse);
                    newGr.Transactions.Cache.SetValueExt<SOLine.orderQty>(soline, qty);
        return adapter.Get();

 Excel spreadsheet looks like this:


If you need to read from excel spreadsheet you may use standard Acumatica library which lives in PX.Data namespace.



Add comment