Hello everybody,

Today I want to share with you two approaches for working with Excel files:

1. How we might create Excel file in Acumatica using standard dll library of Acumatica website.
2. How we might import data from excel file to Acumatica using XLSXReader from PX.Data.dll library.

Example will be developed on Stock Item screen.

Export data to Excel file:

1. We will use PX.Export.dll library, so first you need add it to References:

2. Create new Package() object from the PX.Export.Excel.Core namespace and take first sheet from package.

3. Use method Add() on sheet object

Here is full example of source code logic that attach the excel file to Stock Item screen.

using PX.Data;
using PX.Objects.IN;
using PX.SM;
using System;
using System.Collections;
using System.IO;
 
namespace ExportExcelLib
{
    public class InventoryItemMaintExt : PXGraphExtension<InventoryItemMaint>
    {
        public static bool IsActive() => true;
 
        public PXAction<InventoryItem> ExportExcel;
        [PXUIField(DisplayName = "Export Excel", MapEnableRights = PXCacheRights.Select, MapViewRights = PXCacheRights.Select)]
        [PXButton]
        public virtual IEnumerable exportExcel(PXAdapter adapter)
        {
            if (this.Base.Item.Current == null) return adapter.Get();
 
            ExportToExcel(this.Base.Item.Cache, this.Base.Item.Current);
 
            return adapter.Get();
        }
 
        public virtual void ExportToExcel(PXCache cache, InventoryItem inventoryItem)
        {
            var excel = new PX.Export.Excel.Core.Package();
            var sheet = excel.Workbook.Sheets[1];
 
            sheet.Add(1, 1, "Inventory ID");
            sheet.Add(1, 2, "Item Description");
            sheet.Add(1, 3, "Item Status");
            sheet.Add(1, 4, "UOM");
            sheet.Add(1, 5, "Default Price");
 
            sheet.Add(2, 1, inventoryItem.InventoryCD);
            sheet.Add(2, 2, inventoryItem.Descr);
            sheet.Add(2, 3, inventoryItem.ItemStatus);
            sheet.Add(2, 4, inventoryItem.BaseUnit);
            sheet.Add(2, 5, (double)inventoryItem.BasePrice);
 
            // this logic conver Excel file to Memory Stream and bin format, after you could attache or save file to disk
            using (MemoryStream ms = new MemoryStream())
            {
                UploadFileMaintenance fileUpload = PXGraph.CreateInstance<UploadFileMaintenance>();
 
                excel.Write(ms);
 
                var oneFileInfo = new PX.SM.FileInfo(Guid.NewGuid(), "NewExcelFile.xlsx", null, ms.ToArray());
                fileUpload.SaveFile(oneFileInfo, FileExistsAction.CreateVersion);
                PXNoteAttribute.AttachFile(cache, inventoryItem, oneFileInfo);
            }
        }
    }
}

 4. Screen shot of the excel file:

PS.: According to response from Acumatica Support the PX.Export.dll library doesn’t allow export images to excel file, method AddPng() will be removed in future:

Import data from Excel file

1. We need setup and invoke Acumatica’s pop-up screen to load the excel file.

Add PXUploadDialog to the aspx file (IN202500.aspx) to the content that has Form object with DataMember =”Item”, because in source code we will invoke this dialog on Item view (Base.Item.AskExt() method will invoke pop up screen).

<px:PXUploadDialog runat="server" ID="LoadFilePanel" RenderCheckIn="false" AutoSaveFile="false" 
                       SessionKey="ImportStatementFile" Height="120px" Width="560px" Caption="Load Excel File (*.xlsx)" 
                       Key="Item" Style='Position:static;' />

How it looks:

2. Don't forget SessionKey string value, because we will use it to get excel file in bin data from cache of screen (from PXContext) in graph extension.

3. Create object of XLSXReader to read excel file in bin data and then use GetValue() method to parse values from excel file

4. Screen shot of my excel file with new Default Price

5. Last screen shot with new updated

6. Source code logic of Import Excel button and method:

public PXAction<InventoryItem> ImportExcel;
[PXUIField(DisplayName = "Import Excel", MapEnableRights = PXCacheRights.Select, MapViewRights = PXCacheRights.Select)]
[PXButton]
public virtual IEnumerable importExcel(PXAdapter adapter)
{
    if (this.Base.Item.Current == null) return adapter.Get();
 
    if (Base.Item.AskExt() == WebDialogResult.OK)
    {
        ImportFromExcel();
    }
    return adapter.Get();
}
 
public virtual void ImportFromExcel()
{
    PX.SM.FileInfo file = (PX.SM.FileInfo)PX.Common.PXContext.SessionTyped<PXSessionStatePXData>().FileInfo["ImportStatementFile"];
 
    byte[] filebytes = file.BinData;
    using (PX.Data.XLSXReader reader = new XLSXReader(filebytes))
    {
        reader.Reset();
        Dictionary<string, int> indexes = reader.IndexKeyPairs.ToDictionary(p => p.Value, p => p.Key);
 
        string excelFileDataCell = string.Empty;
 
        while (reader.MoveNext())
        {
 
            excelFileDataCell = reader.GetValue(indexes["Default Price"]);
        }
        decimal defaultPrice = decimal.Parse(excelFileDataCell);
        Base.Item.Current.BasePrice = defaultPrice;
        Base.Item.UpdateCurrent();
    }
}