static void RB_SysExcelWorksheetHelper(Args _args)
{
PdsApprovedVendorList pdsApprovedVendorList;
SysExcelWorksheetHelper worksheetHelper;
SysExcelHelper sysExcelHelper;
SysExcelWorksheet worksheet;
int currentRow = 1;
str worksheetName;
int redColor = WinAPI::RGB2int(255, 0, 0);
SysExcelRange range;
COMVariant cellValue = new COMVariant(COMVariantInOut::Out);
str fileName;
ItemId previousItemId, newItemId;
str attachmentPath = "C:\\";
// Sets the font color for a range of cells
void setRangeFont(int _fromColumn, int _fromRow, int _toColumn, int _toRow, int _rgbIntColor)
{
range = worksheetHelper.getWorksheetRange(_fromColumn, _fromRow, _toColumn, _toRow);
worksheetHelper.setFontColor(range, _rgbIntColor);
}
// Defines the columns of the spreadsheet
#define.ItemId(1)
#define.ItemName(2)
#define.SupplierId(3)
#define.SupplierName(4)
worksheetName = "Approved Supplier";
sysExcelHelper = SysExcelHelper::construct();
sysExcelHelper.initialize();
worksheet = sysExcelHelper.addWorksheet(worksheetName);
worksheetHelper = SysExcelWorksheetHelper::construct(worksheet);
// Populate the header row with the appropriate field labels and format the columns
worksheetHelper.addColumnFromTableField(#ItemId, tablenum(PdsApprovedVendorList), fieldnum(PdsApprovedVendorList, ItemId));
worksheetHelper.addColumn(#ItemName, "Item Name", Types::String);
worksheetHelper.addColumnFromTableField(#SupplierId, tablenum(PdsApprovedVendorList), fieldnum(PdsApprovedVendorList, PdsApprovedVendor));
worksheetHelper.addColumn(#SupplierName, "Supplier Name", Types::String);
// Business logic includes grouping , you can write your logic as per the requirements
while select pdsApprovedVendorList
where pdsApprovedVendorList.ValidTo >= systemDateGet()
{
newItemId = pdsApprovedVendorList.ItemId;
if (newItemId != previousItemId)
{
currentRow ++;
worksheetHelper.setCellValue(#ItemId, currentRow, pdsApprovedVendorList.ItemId);
worksheetHelper.setCellValue(#ItemName, currentRow, InventTable::find(pdsApprovedVendorList.ItemId).itemName());
worksheetHelper.setCellValue(#SupplierId, currentRow, pdsApprovedVendorList.PdsApprovedVendor);
worksheetHelper.setCellValue(#SupplierName, currentRow, VendTable::find(pdsApprovedVendorList.PdsApprovedVendor).name());
}
else
{
currentRow ++;
worksheetHelper.setCellValue(#ItemId, currentRow, '');
worksheetHelper.setCellValue(#ItemName, currentRow, '');
worksheetHelper.setCellValue(#SupplierId, currentRow, pdsApprovedVendorList.PdsApprovedVendor);
worksheetHelper.setCellValue(#SupplierName, currentRow, VendTable::find(pdsApprovedVendorList.PdsApprovedVendor).name());
}
previousItemId = pdsApprovedVendorList.ItemId;
}
worksheetHelper.autoFitColumns();
worksheetHelper.formatWorksheetTableStyle(sysExcelHelper.getOfficeVersion());
// Generate the file using the current UTC date time (without the ‘:’ character)
// since it is not allowed for file names.
fileName = strfmt('%1%2%3', attachmentPath, strReplace(DateTimeUtil::toStr(DateTimeUtil::utcNow()), ':',''), sysExcelHelper.getFileExtension());
sysExcelHelper.save(filename);
sysExcelHelper.launchExcel();
}
Subscribe to:
Posts (Atom)
D365 F&O Release Pipeline Step by Step Configuration Without ISV's
Step-by-Step Guide: Creating D365FO Build and Deploy Pipelines Azure DevOps Build Pipeline I will walk through the standard procedures...
-
X++ code to read the Excel file static void RB_ReadExcel(Args _args) { SysExcelApplication application; SysExcelWorkbooks w...
-
//Create a new job and paste the code static void RB_ReadTextFile(Args _args) { Filename ...
-
// If the file path comes from the dialog , it automatically works ,else you need to Specify '\\' --------------------------------...