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
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 = "demo@demo.com";
//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();
}
}
}