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;
    
    [PXButton]
    [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))
            {
                reader.Reset();
                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));
                    
 
 
                    newGr.Clear();
                    var newSoOrder = new SOOrder();
                    newSoOrder.OrderType = "IN";
                    int?
                        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);
 
                    newGr.Actions.PressSave();
                }
            }
 
        }
 
        return adapter.Get();
    }

 Excel spreadsheet looks like this:

Summary

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

 

 

Add comment

Loading