Report output
here i wants to add
1. before header columns report name should be display ( as like pic )
2. the header columns all fields wants to bold with LIGHT GRAY colour
just now customer confirmed that
full code ****
Class declaration --
SysExcelApplication sysExcelApplication;
SysExcelWorkbooks excelWorkbookCollection;
SysExcelWorkbook excelWorkbook;
SysExcelWorksheets excelWorksheetCollection;
SysExcelWorksheet excelWorksheet;
SysExcelStyles styles;
SysExcelStyle style;
SysExcelFont font;
SysExcelCells cells;
public void excelExecution()
{
Query query;
QueryRun qrRun;
TransDate maxSalesConfrmDate;
QueryBuildDatasource qbds,qbds1;
QueryBuildRange qbr;
RecId chkRecid;
boolean headerPrint = true;
i = 1;
// initlize the SysExcelApplication instance
sysExcelApplication = SysExcelApplication::construct();
//create excel workbook and excel worksheet
excelWorkbookCollection = sysExcelApplication.workbooks();
excelWorkbook = excelWorkbookCollection.add();
excelWorksheetCollection = excelWorkbook.worksheets();
excelWorksheet = excelWorksheetCollection.itemFromNum(1);
// Create new style
styles = excelWorkbook.styles();
style = styles.add('Header');
// Set font for this style to bold and color to red
font = style.font();
font.bold(true);
font.color(255);
// query ( GDJ_CustCustGroupQR )started ***
query = new Query();
qbds = query.addDataSource(tablenum(CustConfirmJour));
// date range
qbr = qbds.addRange(fieldNum(CustConfirmJour,ConfirmDate));
qbr.value(queryRange(fromDate, toDate));
qbds.addGroupByField(fieldNum(CustConfirmJour, SalesId));
qbds.addSelectionField(fieldNum(CustConfirmJour, RecId), SelectionField::Max);
qbds.addRange( fieldNum(CustConfirmJour, SalesId)).value("440-000009");
qbr = qbds.addRange(fieldNum(CustConfirmJour, SalesId));
qbr.value("");
qrRun = new QueryRun(query);
try
{
while (qrRun.next())
{
custConfirmJour = qrRun.get(TableNum(CustConfirmJour));
//info(strFmt("%1", custConfirmJour.RecId));
if(custConfirmJour.RecId != 0 && headerPrint == true)
{
this.createHeader(i);
i++;
headerPrint = false;
}
chkRecid = custConfirmJour.RecId;
// get sales table here***
salesTable = custConfirmJour.salesTable();
orderEntryNo = salesTable.SalesId;
// order date
orderDate = salesTable.lastConfirmDate();
a = date2str(orderDate,213,2,DateSeparator::Slash,2,DateSeparator::Slash,4,DateFlags::None);
// end
customerCode = SalesTable.CustAccount;
customerName = SalesTable.customerName();
customerPONumber = SalesTable.PurchOrderFormNum;
currencyType = SalesTable.CurrencyCode;
// date
partialShipmentDate = SalesTable.lastPackingSlipDate();
b = date2str(partialShipmentDate,123,2,DateSeparator::Slash,2,DateSeparator::Slash,4,DateFlags::None);
// end
// sales responsible
select ContactPersonId from contactPerson where contactPerson.ContactPersonId == salesTable.ContactPersonId;
if(contactPerson)
{
while select NameAlias,RecId from DirPartyTable where DirPartyTable.RecId == contactPerson.Party
{
if(DirPartyTable)
{
contactPersonName = DirPartyTable.NameAlias;
}
}
}
// end
while select SalesId,CustGroup,SalesQty,SalesPrice,LineAmount,RemainSalesPhysical,ShippingDateConfirmed,
ShippingDateRequested,ItemId,defaultDimension from salesLine index hint salesLineIdx
where salesLine.SalesId == orderEntryNo
{
if(salesLine)
{
// financial dimension logic***
defaultDimension = salesLine.DefaultDimension;
while select RecId from DimensionAttributeValueSet
where DimensionAttributeValueSet.RecId == defaultDimension
join RecId, DisplayValue from DimensionAttributeValueSetItem
where DimensionAttributeValueSetItem.DimensionAttributeValueSet == DimensionAttributeValueSet.RecId
join RecId from dimensionAttributeValue
where DimensionAttributeValue.RecId == DimensionAttributeValueSetItem.DimensionAttributeValue
join RecId, Name from DimensionAttribute
where DimensionAttribute.RecId == DimensionAttributeValue.DimensionAttribute
{
SBU = DimensionAttributeValueSetItem.DisplayValue;
Brand = DimensionAttributeValueSetItem.DisplayValue;
}
// end
itemNo = salesLine.ItemId;
// get invent table***
inventTable = InventTable::find(itemNo);
productName = inventTable.productName(SystemParameters::getSystemLanguageId());
// tbd
salesTransType = InventItemGroupItem::findByItemIdLegalEntity(itemNo,curExt()).ItemGroupId;
MG4Code = inventTable.GDJ_MG4Code;
MG4CodeDesc = inventTable.GDJ_MG4Description();
salesType = inventTable.GDJ_SalesTypeCode;
productType = inventTable.GDJ_ProductTypeCode;
newProductofMedical = inventTable.GDJ_NewProductMed;
City = inventTable.GDJ_OriginCity;
// end
itemDescriptionOne = productName;
orderQty = salesLine.SalesQty;
salesUnitPriceFC = salesLine.SalesPrice;
salesAmountFC = salesLine.LineAmount;
partialShipmentShippedQty = salesLine.deliveredInTotal();
backlogQty = salesLine.RemainSalesPhysical;
customerDesiredDeliveryDate = salesLine.ShippingDateRequested; // TBD
pumpType = KCS_InventTable::find(itemNo).KCS_PumpCode; // tbd
marketCode = KCS_InventTable::find(itemNo).KCS_InventCategoryId; // TBD
productRecId = EcoResProduct::findByDisplayProductNumber(itemNo).RecId;
language = custTable::find(customerCode).DirPartyTable_FK().languageId;
itemDescription2 = EcoResProductTranslation::findByProductLanguage(productRecId,language).GDT_TechDataDescr2;
itemDescription3 = EcoResProductTranslation::findByProductLanguage(productRecId,language).GDT_TechDataDescr3;
// end
// date
expectedShippingDatetoCustomer = SalesLine.ShippingDateConfirmed;
c = date2str(expectedShippingDatetoCustomer,123,2,DateSeparator::Slash,2,DateSeparator::Slash,4,DateFlags::None);
shippingDate = SalesLine.ShippingDateRequested;
d = date2str(expectedShippingDatetoCustomer,123,2,DateSeparator::Slash,2,DateSeparator::Slash,4,DateFlags::None);
// end
dimId = salesLine.InventDimId;
Site = InventDim::find(dimId).InventSiteId;
SBU = InventSite::find(Site).Name;
this.createLine(i);
i++;
//check the fileName is already exist
/*if(WinAPI::fileExists(filename))
WinAPI::deleteFile(filename);
excelWorkSheet.columns().autoFit();
// save excel file
//excelWorkbook.saveAs(filename);
// to close the AX instance
excelWorkbook.close();
info(strFmt("%1 ",filename+" "+"@GDJ78"));*/
}
}
}// end while qr.next()
if(WinAPI::fileExists(filename))
WinAPI::deleteFile(filename);
excelWorkSheet.columns().autoFit();
// save excel file
//excelWorkbook.saveAs(filename);
sysExcelApplication.visible(true);
// to close the AX instance
//excelWorkbook.close();
info(strFmt("%1 ",filename+" "+"@GDJ78"));
}
catch
{
Error("@GDJ79");
}
}
createHeader method
public void createHeader(int m)
{
;
currentdate = systemdateget();
ShowDate = date2Str(currentDate,123, DateDay::Digits2,
DateSeparator::Hyphen, // separator1
DateMonth::Digits2,
DateSeparator::Hyphen, // separator2
DateYear::Digits4,
DateFlags::None);
//showFileName = "@GDJ85"+" "+ShowDate;
//Excel columns captions
// for header***
public void createHeader(int m)
{
;
currentdate = systemdateget();
ShowDate = date2Str(currentDate,123, DateDay::Digits2,
DateSeparator::Hyphen, // separator1
DateMonth::Digits2,
DateSeparator::Hyphen, // separator2
DateYear::Digits4,
DateFlags::None);
//showFileName = "@GDJ85"+" "+ShowDate;
//Excel columns captions
// for header***
excelWorksheet.cells().item(m,1).value("@GDT458");
excelWorksheet.cells().item(m,2).value("@GDT459");
excelWorksheet.cells().item(m,3).value("@GDT460");
excelWorksheet.cells().item(m,4).value("@GDT461"); // SBU
excelWorksheet.cells().item(m,5).value("@GDT462"); // BRAND
excelWorksheet.cells().item(m,6).value("@GDT463");
excelWorksheet.cells().item(m,7).value("@GDT464");
excelWorksheet.cells().item(m,8).value("@SYS74488");
excelWorksheet.cells().item(m,9).value("@SYS23780");
excelWorksheet.cells().item(m,10).value("@SYS27874");
excelWorksheet.cells().item(m,11).value("@GDT465");
excelWorksheet.cells().item(m,12).value("@GDT466");
excelWorksheet.cells().item(m,13).value("@GDT467");
excelWorksheet.cells().item(m,14).value("@GDT468");
excelWorksheet.cells().item(m,15).value("@GDT469");
excelWorksheet.cells().item(m,16).value("@GDT470");
excelWorksheet.cells().item(m,17).value("@GDT471");
excelWorksheet.cells().item(m,18).value("@GDT472"); // sales unit price JPY
excelWorksheet.cells().item(m,19).value("@GDT473"); // SALES ORDER AMOUNT JPY
excelWorksheet.cells().item(m,20).value("@GDT474");
excelWorksheet.cells().item(m,21).value("@GDT475"); // CURRENCY SYMBOL
excelWorksheet.cells().item(m,22).value("@GDT476"); // CURRENCY SYMBOL
excelWorksheet.cells().item(m,23).value("@GDT477");
excelWorksheet.cells().item(m,24).value("@GDT478");
excelWorksheet.cells().item(m,25).value("@GDT479");
excelWorksheet.cells().item(m,26).value("@GDT480");
excelWorksheet.cells().item(m,27).value("@GDT481"); // CURRENCY SYMBOL
excelWorksheet.cells().item(m,28).value("@GDT482");
excelWorksheet.cells().item(m,29).value("@GDT483");
excelWorksheet.cells().item(m,30).value("@GDT484");
/*excelWorksheet.cells().item(m,31).value("@GDT461"); // SBU
excelWorksheet.cells().item(m,32).value("@GDT462"); // BRAND*/
excelWorksheet.cells().item(m,31).value("@GDT485");
excelWorksheet.cells().item(m,32).value("@GDT486");
excelWorksheet.cells().item(m,33).value("@SYS133830");
excelWorksheet.cells().item(m,34).value("@GDT487");
excelWorksheet.cells().item(m,35).value("@GDT488");
excelWorksheet.cells().item(m,36).value("@GDT489");
excelWorksheet.cells().item(m,37).value("@GDT490");
excelWorksheet.cells().item(m,38).value("@SYS4081955");
i++;
}
i++;
}
create line method
public void createLine(int k)
{
;
excelWorksheet.cells().item(k,1).value(orderEntryNo);
excelWorksheet.cells().item(k,2).value(c); // expectedShkppkngDatetoCustomer
excelWorksheet.cells().item(k,3).value(d); // shkppkngDate
excelWorksheet.cells().item(k,4).value(SBU); // SBU
excelWorksheet.cells().item(k,5).value(Brand); // BRAND
excelWorksheet.cells().item(k,6).value(a); // orderDate
excelWorksheet.cells().item(k,7).value(salesTransType); // Sales Transactkon Type
excelWorksheet.cells().item(k,8).value(contactPersonName); // Sales Representatkve
excelWorksheet.cells().item(k,9).value(customerCode);
excelWorksheet.cells().item(k,10).value(customerName);
excelWorksheet.cells().item(k,11).value(customerPONumber);
excelWorksheet.cells().item(k,12).value(customerDesiredDeliveryDate); // Descrkptkon (Customer Deskred delkvery date etc.)
excelWorksheet.cells().item(k,13).value(itemNo);
excelWorksheet.cells().item(k,14).value(itemDescriptionOne);
excelWorksheet.cells().item(k,15).value(itemDescription2); // item descrkptkon 2
excelWorksheet.cells().item(k,16).value(itemDescription3); // item descrkptkon 3
excelWorksheet.cells().item(k,17).value(orderQty);
excelWorksheet.cells().item(k,18).value('DUMMY'); // Sales Unkt Prkce JPY
excelWorksheet.cells().item(k,19).value('DUMMY'); // Sales Order Amount JPY
excelWorksheet.cells().item(k,20).value(currencyType);
excelWorksheet.cells().item(k,21).value(salesUnitPriceFC);
excelWorksheet.cells().item(k,22).value(salesAmountFC);
excelWorksheet.cells().item(k,23).value('DUMMY'); // FX-rate
excelWorksheet.cells().item(k,24).value(b); // partkalShkpmentDate
excelWorksheet.cells().item(k,25).value(partialShipmentShippedQty);
excelWorksheet.cells().item(k,26).value(backlogQty);
excelWorksheet.cells().item(k,27).value('DUMMY'); // backlog amount
excelWorksheet.cells().item(k,28).value('DUMMY'); // factory ( Vendor code )
excelWorksheet.cells().item(k,29).value('DUMMY'); // factory ( vendor name )
excelWorksheet.cells().item(k,30).value('DUMMY'); // shkppkng country
/*excelWorksheet.cells().item(k,31).value('DUMMY'); // SBU ( DUPLkCATE )
excelWorksheet.cells().item(k,32).value('DUMMY'); // BRAND ( DUPLkCATE )*/
excelWorksheet.cells().item(k,31).value(marketCode); // market code
excelWorksheet.cells().item(k,32).value(newProductofMedical); // New Product of Medkcal
excelWorksheet.cells().item(k,33).value(productType); // Product type
excelWorksheet.cells().item(k,34).value(pumpType); // Pump Type
excelWorksheet.cells().item(k,35).value(City); // Factory of Medkcal
excelWorksheet.cells().item(k,36).value(MG4Code); // MG4 Groupkng Code
excelWorksheet.cells().item(k,37).value(MG4CodeDesc); // MG4 Groupkng Code Descrkptkon
excelWorksheet.cells().item(k,38).value(salesType); // Sales Type
}
please give me more shed on this.
thanks!
