Excel file generation - API/Methods exposed?

Question

Acumatica obviously has the ability to create excel files from various locations in the system.

Is the methods used to generate the excel file exposed publicly by chance and can be used outside of the standard grid, report, import services?

I have the need to be able to generate an excel file from one of my actions and attach this as a file attachment. I'd prefer to use the built in excel methods for simplicity if possible before I go down the route of including EPPlus dll's or similar.

Thank you for any information

Answer 1

You can utilize PX.Export.Excel.Core.Package to generate an Excel file.

And generated Excel can be either downloaded using PXRedirectToFileException redirection exception or can be send as an Email attachment using Notification Template and TemplateNotificationGenerator.

using System;
using System.Linq;
using System.Text;
using PX.Objects.SO;
using PX.Objects.CR;
using PX.Common;
using PX.Data;
using PX.SM;
using System.IO;

namespace PXDemoPkg
{
    public class SOOrderEntryPXExt : PXGraphExtension<SOOrderEntry>
    {
        public PXAction<SOOrder> ExportToExcelAndSendEmailAttachment;
        [PXUIField(DisplayName = "Export To Excel And Send Email Attachment", MapViewRights = PXCacheRights.Select, MapEnableRights = PXCacheRights.Update)]
        [PXButton]
        protected virtual void exportToExcelAndSendEmailAttachment()
        {
            if (Base.Document.Current == null ||
                Base.Document.Cache.GetStatus(Base.Document.Current) == PXEntryStatus.Inserted) return;

            var excel = new PX.Export.Excel.Core.Package();
            var sheet = excel.Workbook.Sheets[1];

            //Add Header
            sheet.Add(1, 1, "Line #");
            sheet.Add(1, 2, "Transaction Description");
            sheet.Add(1, 3, "Ordered Quantity");

            //Add Data
            var index = 2;
            foreach (PXResult<SOLine> lineItem in Base.Transactions.Select())
            {
                SOLine dataRow = (SOLine)lineItem;
                sheet.Add(index, 1, Convert.ToString(dataRow.LineNbr));
                sheet.Add(index, 2, dataRow.TranDesc);
                sheet.Add(index, 3, Convert.ToString(dataRow.OrderQty));
                index++;
            }
            sheet.SetColumnWidth(1, 20);
            sheet.SetColumnWidth(2, 45);
            sheet.SetColumnWidth(3, 35);

            //ExportFile(excel);
            SendEmail(excel);
        }

        //To download generated Excel    
        private void ExportFile(PX.Export.Excel.Core.Package excel)
        {
            using (MemoryStream stream = new MemoryStream())
            {
                excel.Write(stream);
                string path = String.Format("SO-{0}-Transaction Info.xlsx", Base.Document.Current.OrderNbr);
                var info = new PX.SM.FileInfo(path, null, stream.ToArray());
                throw new PXRedirectToFileException(info, true);
            }
        }

        //Email generated Excel as an attachment
        private void SendEmail(PX.Export.Excel.Core.Package excel)
        {
            bool sent = false;

            //Notiftcaion with name "SOTransactionInfo" should be created via screen SM204003 prior to using this code.
            Notification rowNotification = PXSelect<Notification,
                                               Where<Notification.name,
                                                   Equal<Required<Notification.name>>>>
                                           .Select(Base, "SOTransactionInfo");

            if (rowNotification == null)
                throw new PXException("Notification Template for is not specified.");

            var sender = PX.Objects.EP.TemplateNotificationGenerator.Create(Base.Document.Current,
                                                                        rowNotification.NotificationID.Value);
            sender.MailAccountId = rowNotification.NFrom.HasValue ?
                                   rowNotification.NFrom.Value :
                                   PX.Data.EP.MailAccountManager.DefaultMailAccountID;

            sender.To = "[email protected]";

            //Attach Excel
            using (MemoryStream stream = new MemoryStream())
            {
                excel.Write(stream);
                string path = String.Format("SO-{0}-Transaction Info.xlsx", Base.Document.Current.OrderNbr);
                sender.AddAttachment(path, stream.ToArray());
            }
            sent |= sender.Send().Any();
        }
    }
}