Export to Excel

Export to excel :

public class ItemmasterMaterialCatalog extends RunBaseBatch {     Dialog dialog;     DialogField     dialogExportpath;     FilePath        exportFilePath;       ProdId prodid;     QueryRun queryRun;       #define.CurrentVersion(1)     #define.version(1)     #localmacro.CurrentList         prodid,         exportFilePath     #endmacro   }

public boolean canGoBatchJournal() {     return true; }

protected Object dialog() {     ;     dialog = super();     // Set a title for dialog     dialog.caption("@CIT344");     dialog.addGroup("@SYS26056");     dialogExportPath   = dialog.addField(extendedTypeStr(FilePath));     return dialog; }

public void dialogPostRun(DialogRunbase _dialog) {     super(_dialog); }

private void exportToExcel() {     SysExcelApplication  xlsApplication;     SysExcelWorkBooks    xlsWorkBookCollection;     SysExcelWorkBook     xlsWorkBook;     SysExcelWorkSheets   xlsWorkSheetCollection;     str                 filePathName;     SysExcelWorkSheet    xlsWorkSheet;     str                  dateString;     int                  row = 1;     InventTable          inventtable;     SysExcelCells        cells;       SysExcelCell         cell;     dateString ="@CIT345"+"_"+ date2Str(today(),321,DateDay::Digits2,DateSeparator::None, DateMonth::Digits2,DateSeparator::None,DateYear::Digits4);     filePathName = dialogExportPath.value()+"\\"+dateString +".xlsx";     xlsApplication           = SysExcelApplication::construct();     xlsWorkBookCollection    = xlsApplication.workbooks();     xlsWorkBook              = xlsWorkBookCollection.add();     xlsWorkSheetCollection   = xlsWorkBook.worksheets();     xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);     cells = xlsWorkSheet.cells();     cells.range('A:A').numberFormat('@');     xlsWorkSheet.cells().item(row,1).value("@SYS40783");     xlsWorkSheet.cells().item(row,2).value("@CIT346");     xlsWorkSheet.cells().item(row,3).value("@CIT347");     xlsWorkSheet.cells().item(row,4).value("@CIT348");     xlsWorkSheet.cells().item(row,5).value("@CIT349");     xlsWorkSheet.cells().item(row,6).value("@CIT350");     xlsWorkSheet.cells().item(row,7).value("@CIT343");     xlsWorkSheet.cells().item(row,8).value("@CIT351");     row++;     while(queryRun.next())     {         inventtable = queryRun.get(tableNum(Inventtable));         xlsWorkSheet.cells().item(row,1).value("\'"+EcoResProductTranslation::findByProductLanguage(inventtable.Product,CompanyInfo::Find().LanguageId).Name); // okv, 06/27/2016         xlsWorkSheet.cells().item(row,2).value(inventtable.inventUnitId());         xlsWorkSheet.cells().item(row,3).value("\'"+inventtable.ItemId); // okv, 06/27/2016         //xlsWorkSheet.cells().range(strFmt("C%1:C%1",row)).numberFormat("000000"); // okv, 06/27/2016         xlsWorkSheet.cells().item(row,4).value("\'"+CompanyInfo::Find().CoRegNum); // okv, 06/27/2016         xlsWorkSheet.cells().item(row,5).value(CompanyInfo::Find().Name);         xlsWorkSheet.cells().item(row,6).value(CompanyInfo::Find().DataArea);         xlsWorkSheet.cells().item(row,7).value("");         xlsWorkSheet.cells().item(row,8).value("");                   row++;     }       if(WinApi::fileExists(filePathName))       WinApi::deleteFile(filePathName);    //Save Excel document     xlsWorkbook.saveAs(filePathName);       // okv, 06/27/2016 -->     xlsWorkbook.comObject().save();     xlsWorkbook.saved(true);     xlsApplication.quit();     // okv, 06/27/2016 <-- }

public boolean getFromDialog() {     exportFilePath     = dialogexportpath.value();     return super(); }

public boolean init() {     return true; }

public void initParmDefault() {     Query query;     QueryBuildDataSource    qbd;     QueryBuildRange     qbr;     ;     query = new Query();     qbd = query.addDataSource(tableNum(InventTable));     qbr = qbd.addRange(fieldNum(InventTable, ItemId));     queryRun = new QueryRun(query);      super(); }

protected void new() {     super(); }

public container pack()   {     ;       return [#CurrentVersion, #CurrentList, queryRun.pack()];   }

public QueryRun queryRun() {     ;     return queryRun; }

public void run() {       this.exportToExcel();   }

public boolean runsImpersonated() {     return true; }

public boolean showBatchTab(boolean _showBatchTab = false) {     return true; }

boolean showQueryValues() {     ;     return true; }

public boolean unpack(container _packedClass) {     Version version = runbase::getVersion(_packedClass);     Container packedQuery;     ;     switch (version)     {         case #CurrentVersion:               [version, #CurrentList, packedQuery] = _packedClass;         if (packedQuery)             queryRun = new QueryRun(packedQuery);         break;         default:             return false;         }         return true;   }

public boolean validate(Object _calledFrom = null) {     boolean ret = true;     if (!exportFilePath)         return checkFailed(strfmt("@SYS26332","@SYS124357"));       return ret; }

server static ItemmasterMaterialCatalog construct() {     return new ItemmasterMaterialCatalog(); } public static void main (Args _args) {      ItemmasterMaterialCatalog itemmasterMaterialCatalog;      ;      itemmasterMaterialCatalog = new ItemmasterMaterialCatalog();     if(itemmasterMaterialCatalog.prompt())           itemmasterMaterialCatalog.run(); }

Export to excel - For each ProdId it should create separate Excel file

public class MaterialStructure extends RunBaseBatch {     Dialog      dialog;     DialogField dialogExportpath;     FilePath    exportFilePath;     int         row;     ProdId      prodid;     QueryRun    queryRun;       #define.CurrentVersion(1)     #define.version(1)     #localmacro.CurrentList         prodid,         exportFilePath     #endmacro   }

public boolean canGoBatchJournal() {     return true; }

protected Object dialog() {     ;     dialog = super();     // Set a title for dialog     dialog.caption("@CIT368");     dialog.addGroup("@SYS126516");     dialogExportPath   = dialog.addField(extendedTypeStr(FilePath));     return dialog; }

public void dialogPostRun(DialogRunbase _dialog) {     super(_dialog);   }

private void exportToExcel() {     SysExcelApplication xlsApplication;     SysExcelWorkBooks   xlsWorkBookCollection;     SysExcelWorkBook    xlsWorkBook;     SysExcelWorkSheets  xlsWorkSheetCollection;     str                 filePathName;     SysExcelWorkSheet   xlsWorkSheet;     str                 dateString;     ProdTable           prodtable;     InventDim           inventdim;     ProdId              prodidchk;     ProdBOM             prodBOM;     boolean             filerecexists;     ;       row = 1;       while(queryRun.next())     {         prodtable = queryRun.get(tableNum(ProdTable));         inventdim = queryRun.get(tableNum(InventDim));         if(filePathName && prodidchk !=prodtable.ProdId)         {             if(WinApi::fileExists(filePathName))             WinApi::deleteFile(filePathName);             //Save Excel document             xlsWorkbook.saveAs(filePathName);             filerecexists = false;               // okv, 06/27/2016 -->             xlsWorkbook.comObject().save();             xlsWorkbook.saved(true);             xlsApplication.quit();             // okv, 06/27/2016 <--         }         if(prodidchk == "" || prodidchk !=prodtable.ProdId)         {             row =   1;             filerecexists = true;             prodidchk   =   prodtable.ProdId;             dateString ="@CIT354"+"_"+prodtable.ProdId+"_"+date2Str(today(),321,DateDay::Digits2,DateSeparator::None, DateMonth::Digits2,DateSeparator::None,DateYear::Digits4)+"_"+inventdim.inventSerialId;             filePathName = exportFilePath+"\\"+dateString +".xlsx";             xlsApplication           = SysExcelApplication::construct();             xlsWorkBookCollection    = xlsApplication.workbooks();             xlsWorkBook              = xlsWorkBookCollection.add();             xlsWorkSheetCollection   = xlsWorkBook.worksheets();             xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);             xlsWorkSheet.cells().item(row,1).value("@CIT355");             xlsWorkSheet.cells().item(row,2).value("@CIT356");             xlsWorkSheet.cells().item(row,3).value("@CIT343");             xlsWorkSheet.cells().item(row,4).value("@CIT357");             xlsWorkSheet.cells().item(row,5).value("@CIT358");             xlsWorkSheet.cells().item(row,6).value("@CIT359");             xlsWorkSheet.cells().item(row,7).value("@CIT360");             xlsWorkSheet.cells().item(row,8).value("@CIT361");             xlsWorkSheet.cells().item(row,9).value("@CIT362");             xlsWorkSheet.cells().item(row,10).value("@CIT363");             xlsWorkSheet.cells().item(row,11).value("@CIT364");             xlsWorkSheet.cells().item(row,12).value("@CIT365");             xlsWorkSheet.cells().item(row,13).value("@CIT366");             xlsWorkSheet.cells().item(row,14).value("@CIT367");             row++;         }         while select ProdId,ItemId,dataAreaId,BOMQty,BOMQtySerie from  prodBOM             where prodBOM.ProdId ==  prodtable.ProdId         {             if(InventItemSetupSupplyType::find(prodBOM.ItemId,prodBOM.dataAreaId).DefaultOrderType ==  ReqPOType::Production)             {                 xlsWorkSheet.cells().item(row,1).value("\'"+inventdim.inventSerialId); // okv, 06/27/2016                 xlsWorkSheet.cells().item(row,2).value(InventTable::find(prodtable.ItemId).inventUnitId());                 xlsWorkSheet.cells().item(row,3).value("");                 xlsWorkSheet.cells().item(row,4).value(prodtable.RealDate);                 xlsWorkSheet.cells().item(row,5).value("\'"+prodbom.ItemId); // okv, 06/27/2016                 xlsWorkSheet.cells().item(row,6).value("");                 xlsWorkSheet.cells().item(row,7).value("");                 xlsWorkSheet.cells().item(row,8).value("");                 xlsWorkSheet.cells().item(row,9).value(0);                 xlsWorkSheet.cells().item(row,10).value("");                 xlsWorkSheet.cells().item(row,11).value("");                 xlsWorkSheet.cells().item(row,12).value(prodBOM.UnitId);                 xlsWorkSheet.cells().item(row,13).value("");                 xlsWorkSheet.cells().item(row,14).value("");                 row++;                   this.insertLines(prodBOM.ItemId, prodtable.RealDate, xlsWorkSheet, 1);             }             else if (InventItemSetupSupplyType::find(prodBOM.ItemId,prodBOM.dataAreaId).DefaultOrderType ==  ReqPOType::Purch)             {                     xlsWorkSheet.cells().item(row,1).value("\'"+inventdim.inventSerialId); // okv, 06/27/2016                     xlsWorkSheet.cells().item(row,2).value(InventTable::find(prodtable.ItemId).inventUnitId());                     xlsWorkSheet.cells().item(row,3).value("");                     xlsWorkSheet.cells().item(row,4).value(prodtable.RealDate);                     xlsWorkSheet.cells().item(row,5).value("\'"+prodbom.ItemId); // okv, 06/27/2016                     xlsWorkSheet.cells().item(row,6).value("");                     xlsWorkSheet.cells().item(row,7).value("");                     xlsWorkSheet.cells().item(row,8).value("");                     xlsWorkSheet.cells().item(row,9).value(prodbom.BOMQty/(prodBOM.BOMQtySerie ? prodBOM.BOMQtySerie : 1));                     xlsWorkSheet.cells().item(row,10).value("");                     xlsWorkSheet.cells().item(row,11).value("");                     xlsWorkSheet.cells().item(row,12).value(prodBOM.UnitId);                     xlsWorkSheet.cells().item(row,13).value("");                     xlsWorkSheet.cells().item(row,14).value("");                     row++;             }         }     }     if(filerecexists)     {         if(WinApi::fileExists(filePathName))             WinApi::deleteFile(filePathName);         //Save Excel document         xlsWorkbook.saveAs(filePathName);         filerecexists = false;           // okv, 06/27/2016 -->         xlsWorkbook.comObject().save();         xlsWorkbook.saved(true);         xlsApplication.quit();         // okv, 06/27/2016 <--     } }

public boolean getFromDialog() {     exportFilePath     = dialogexportpath.value();     return super(); }

public void initParmDefault() {     Query query;     QueryBuildDataSource    qbdprodtbl,qbdinventdim;     QueryBuildRange         qbrprodid,qbrprodstatus,qbrsite;     ;       query           =   new Query();     qbdprodtbl      =   query.addDataSource(tableNum(ProdTable));     qbrprodid       =   qbdprodtbl.addRange(fieldNum(ProdTable, ProdId));     qbrprodstatus   =   qbdprodtbl.addRange(fieldNum(ProdTable, ProdStatus));     qbdinventdim    =   qbdprodtbl.addDataSource(tableNum(InventDim));     qbrsite         =   qbdinventdim.addRange(fieldNum(InventDim, InventSiteId));     qbdinventdim.addLink(fieldNum(ProdTable,InventDimId),fieldNum(InventDim,InventDimId));     qbdprodtbl.joinMode(JoinMode::InnerJoin);     queryRun        =   new SysQueryRun(query);       super(); }

private void insertLines(ItemId _itemId, TransDate _date, SysExcelWorkSheet _xlsWorkSheet, int _level) {     BomVersion  bomVersion;     BOM         bom;     ;       if (_level > BOMParameters::find().BOMMAxLevel)         return;       while select bom         join DataAreaId from bomVersion             where bomVersion.BOMId  == bom.BOMId                && bomVersion.Active == NoYes::Yes                && bomVersion.ItemId == _ItemId     {         if (InventItemSetupSupplyType::find(_ItemId,bomVersion.dataAreaId).DefaultOrderType ==  ReqPOType::Purch)         {             _xlsWorkSheet.cells().item(row,1).value("\'"+bom.inventdim().inventSerialId); // okv, 06/27/2016             _xlsWorkSheet.cells().item(row,2).value(InventTable::find(bom.ItemId).inventUnitId());             _xlsWorkSheet.cells().item(row,3).value("");             _xlsWorkSheet.cells().item(row,4).value(_date);             _xlsWorkSheet.cells().item(row,5).value("\'"+bom.ItemId); // okv, 06/27/2016             _xlsWorkSheet.cells().item(row,6).value("");             _xlsWorkSheet.cells().item(row,7).value("");             _xlsWorkSheet.cells().item(row,8).value("");             _xlsWorkSheet.cells().item(row,9).value(bom.BOMQty/(bom.BOMQtySerie ? bom.BOMQtySerie : 1));             _xlsWorkSheet.cells().item(row,10).value("");             _xlsWorkSheet.cells().item(row,11).value("");             _xlsWorkSheet.cells().item(row,12).value(bom.UnitId);             _xlsWorkSheet.cells().item(row,13).value("");             _xlsWorkSheet.cells().item(row,14).value("");             row++;         }         else         {             _xlsWorkSheet.cells().item(row,1).value("\'"+bom.inventdim().inventSerialId); // okv, 06/27/2016             _xlsWorkSheet.cells().item(row,2).value(InventTable::find(bom.ItemId).inventUnitId());             _xlsWorkSheet.cells().item(row,3).value("");             _xlsWorkSheet.cells().item(row,4).value(_date);             _xlsWorkSheet.cells().item(row,5).value("\'"+bom.ItemId); // okv, 06/27/2016             _xlsWorkSheet.cells().item(row,6).value("");             _xlsWorkSheet.cells().item(row,7).value("");             _xlsWorkSheet.cells().item(row,8).value("");             _xlsWorkSheet.cells().item(row,9).value(0);             _xlsWorkSheet.cells().item(row,10).value("");             _xlsWorkSheet.cells().item(row,11).value("");             _xlsWorkSheet.cells().item(row,12).value(bom.UnitId);             _xlsWorkSheet.cells().item(row,13).value("");             _xlsWorkSheet.cells().item(row,14).value("");             row++;               this.insertLines(bom.ItemId, _date, _xlsWorkSheet, _level+1);         }     }       return; }

protected void new() {     super(); }

public container pack() {     ;     return [#CurrentVersion, #CurrentList, queryRun.pack()]; }

public QueryRun queryRun() {     ;     return queryRun; }

public void run() {     this.exportToExcel(); }

public boolean runsImpersonated() {     return true; }

public boolean showBatchTab(boolean _showBatchTab = false) {     return true; }

boolean showQueryValues() {     ;     return true; }

public boolean unpack(container _packedClass) {     Version version = runbase::getVersion(_packedClass);     Container packedQuery;     ;     switch (version)     {         case #CurrentVersion:               [version, #CurrentList, packedQuery] = _packedClass;         if (packedQuery)             queryRun = new QueryRun(packedQuery);         break;         default:             return false;     }         return true;   }

public boolean validate(Object _calledFrom = null) {     boolean ret = true;     if (!exportFilePath)         return checkFailed(strfmt("@SYS26332","@SYS124357"));       return ret; }

server static MaterialStructure construct() {     return new MaterialStructure(); }

public static void main (Args _args) {     MaterialStructure materialStructure;     ;     materialStructure = new MaterialStructure();     if(materialStructure.prompt())         materialStructure.run(); }