venerdì 2 ottobre 2020

D365FFO - Dialog per caricare e leggere un file excel

In questo post vediamo come creare una semplice dialog che consente di caricare e leggere un file excel in locale. Ho preso spunto da questo post per creare la classe: 


Questa è la classe che effettua la lettura del file:
 class LILFileExcelDataReader  
 {  
   str     fileURL;  
   str     sheetName;  
   container  dataContainer;  
   
   public static LILFileExcelDataReader construct(str _fileUrl, str _sheetName)  
   {  
     LILFileExcelDataReader instance = new LILFileExcelDataReader();  
   
     instance.parmFileURL(_fileUrl);  
   
     instance.parmSheetName(_sheetName);  
   
     return instance;  
   }  
   
   public str parmFileURL(str _fileUrl = fileURL)  
   {  
     fileURL = _fileUrl;  
   
     return fileURL;  
   }  
   
   private str getTMPSheetName()  
   {  
     return strFmt("TMP_%1_%2",date2str(  
                   today(),  
                   321,  
                   DateDay::Digits2,  
                   DateSeparator::None,  
                   DateMonth::Digits2,  
                   DateSeparator::None,  
                   DateYear::Digits4  
                   ),timeNow());  
   }  
   
   public str parmSheetName(str _sheetName = sheetName)  
   {  
     sheetName = _sheetName;  
   
     return sheetName;  
   }  
   
   public container getData()  
   {  
     return dataContainer;  
   }  
   
   private boolean validate()  
   {  
     boolean ret = true;  
   
     if(!this.parmFileURL())  
     {  
       return checkFailed("@SYS91305");  
     }  
   
     if(!this.parmSheetName())  
     {  
       return checkFailed("@GEE33476");  
     }  
   
     return ret;  
   }  
   
   public void read()  
   {  
     System.Byte[] byteArray;  
     System.IO.Stream   stream;  
   
     try  
     {  
       if(!this.validate())  
       {  
         throw error("@SYS93835");  
       }  
   
       stream = File::UseFileFromURL(this.parmFileURL());  
       //this.fillSheetList(stream);  
       dataContainer = this.readExcelData(stream);  
     }  
     catch(Exception::Error)  
     {  
       info(strFmt("%1 %2",Exception::Error,fileUrl));  
     }  
   }  
   
   public static List fillSheetList(System.IO.Stream   _stream)  
   {  
     OfficeOpenXml.ExcelWorksheets  workSheets;  
     OfficeOpenXml.ExcelWorksheet  sheet;  
     List    sheetList = new List(Types::String);  
     OfficeOpenXml.ExcelPackage   package = new OfficeOpenXml.ExcelPackage(_stream);  
   
     workSheets = package.get_Workbook().get_Worksheets();  
   
     var enumerator = workSheets.GetEnumerator();  
   
     while (enumerator.MoveNext())  
     {  
       sheet = enumerator.Current as OfficeOpenXml.ExcelWorksheet;  
   
       sheetList.addEnd(sheet.Name);  
     }  
   
     return sheetList;  
   }  
   
   private container readExcelData(System.IO.Stream   _stream)  
   {  
     OfficeOpenXml.ExcelWorksheet  _worksheet;  
       
     OfficeOpenXml.ExcelPackage   package = new OfficeOpenXml.ExcelPackage(_stream);  
     int               iRowCount,iCellCount;  
     anytype             anyData;  
     container            conRow,  
                     conData;  
     try  
     {  
       if(package)  
       {  
         //copy data to temporany sheet  
         _worksheet = package.get_Workbook().get_Worksheets().Copy(this.parmSheetName(),this.getTMPSheetName());  
         var cells = _worksheet.get_Cells();  
         iRowCount = _worksheet.get_Dimension().get_End().get_Row();  
         iCellCount = _worksheet.get_Dimension().get_End().get_Column();  
   
         for (int i=2;i<=iRowCount;i++)  
         {  
           conRow = conNull();  
   
           for (int j=1;j<=iCellCount;j++)  
           {  
   
             anyData= cells.get_Item(i, j).get_Value();  
   
             if(!anyData && j ==1)  
             {  
               break;  
             }  
   
             if(anyData)  
             {  
               conRow += anyData;  
             }  
             else  
             {  
               conRow += "";  
             }  
           }  
   
           if(conRow)  
           {  
             conRow += iRowCount;  
             conData = conIns(conData,i,conRow);  
           }  
         }  
       }  
     }  
     catch (Exception::CLRError)  
     {  
       throw error("@SYS135884");  
     }  
   
     return conData;  
   }  
   
 }  

Questa classe RunBase mostra come utilizzarla:

 class LILExcelReaderDialog extends RunBase  
 {  
   str       fileUrl;  
   str       sheetName;  
   
   DialogRunbase  dialog;  
   
   FormBuildStringControl sheetNameCtrl;  
   
   DialogField   dlgSheetName;  
   
   container    dataContainer;  
   
   #define.CurrentVersion(1)  
   #define.Version1(1)  
   #localmacro.CurrentList  
     sheetName  
   #endmacro  
   
   public container pack()  
   {  
     return [#CurrentVersion,#CurrentList];  
   }  
   
   public boolean unpack(container _packedClass)  
   {  
     Integer   version   = RunBase::getVersion(_packedClass);  
   
     switch (version)  
     {  
       case #CurrentVersion  :  
         [version,#CurrentList] = _packedClass;  
         break;  
       default :  
         return false;  
     }  
   
     return true;  
   }  
   
   protected boolean canRunInNewSession()  
   {  
     return false;  
   }  
   
   private str getSessionID()  
   {  
     return strFmt("%1_%2",date2str(  
                   today(),  
                   321,  
                   DateDay::Digits2,  
                   DateSeparator::None,  
                   DateMonth::Digits2,  
                   DateSeparator::None,  
                   DateYear::Digits4  
                   ),timeNow());  
   }  
   
   public void sheetName_lookup(FormStringControl _executionControl)  
   {  
     if(!fileUrl)  
     {  
       warning("Please upload a file first!");  
   
       return;  
     }  
   
     List  valueList = LILFileExcelDataReader::fillSheetList(File::UseFileFromURL(fileUrl));  
   
     FormRun lookupForm = classFactory.createSysLookupPicklist(_executionControl);  
     lookupForm.init();  
       
     sysPickList pickList = new sysPickList(lookupForm);  
     lookupForm.choices(list2Con(valueList));  
     _executionControl.performFormLookup(lookupForm);  
   }  
   
   public Object dialog()  
   {  
     dialog = super();  
     FormBuildButtonControl buttonControl;  
     DialogGroup       dlgGroup;  
     FormBuildGroupControl  buttonGroup;  
     #resAppl  
     ;  
   
     dlgGroup    = dialog.addGroup("@SYS7764");  
     dlgGroup.columns(1);  
   
     buttonGroup  = dialog.formBuildDesign().control(dlgGroup.formBuildGroup().id());  
     buttonControl = buttonGroup.addControl(FormControlType::Button, "@ElectronicReporting:Upload");  
     buttonControl.text("@DMF1951");  
     buttonControl.registerOverrideMethod(methodStr(FormButtonControl, clicked),  
                       methodStr(LILExcelReaderDialog, uploadClickedEvent),  
                       this);  
   
     dlgSheetName = dialog.addFieldValue(extendedTypeStr(Name),   sheetName, "Sheet name");  
     sheetNameCtrl = dlgSheetName.fieldControl();  
     sheetNameCtrl.extendedDataType(extendedtypenum(Name));  
     sheetNameCtrl.registerOverrideMethod(methodStr(FormStringControl, lookup),  
                       methodStr(LILExcelReaderDialog, sheetName_lookup),  
                       this);  
   
       
     return dialog;  
   }  
   
   private void uploadClickedEvent(FormButtonControl _formButtonControl)  
   {  
     FileUploadTemporaryStorageResult result = File::GetFileFromUser() as FileUploadTemporaryStorageResult;  
   
     if (result && result.getUploadStatus())  
     {  
       result.getFileContentType();  
       fileUrl = result.getDownloadUrl();  
   
       info("@DMF1952");  
     }  
   }  
   
   Public void  readExcel()  
   {  
     LILFileExcelDataReader fileExcelDataReader = LILFileExcelDataReader::construct(fileUrl,sheetName);  
     fileExcelDataReader.read();  
   
     dataContainer = fileExcelDataReader.getData();  
   }  
   
   public boolean validate(Object calledFrom = null)  
   {  
     boolean ret;  
     
     ret = super(calledFrom);  
   
     if(ret && !fileUrl)  
     {  
       ret = checkFailed(strFmt("@MCR11498",fileUrl));  
     }  
   
     if(ret && !sheetName)  
     {  
       ret = checkFailed(strFmt("@SYS26332","@ElectronicReportingForAx:ExcelSheet"));  
     }  
    
     return ret;  
   }  
   
   protected void update()  
   {  
     //put logic code in this method...  
   }  
   
   public void run()  
   {  
     Voucher   voucher;  
     int     i;  
     container  line;  
     str         s;  
     #OCCRetryCount  
           
   
     if (!this.validate())  
     {  
       throw error("@SYS18447");  
     }  
   
     this.readExcel();  
   
     this.update();  
   }  
   
   public static LILExcelReaderDialog construct()  
   {  
     return new LILExcelReaderDialog();  
   }  
   
   public boolean getFromDialog()  
   {  
     sheetName   = dlgSheetName.value();  
   
     return super();  
   }  
   
   public boolean runsImpersonated()  
   {  
     return true;  
   }  
   
   public boolean init()  
   {  
     return true;  
   }  
   
   public static void main(Args _args)  
   {  
     LILExcelReaderDialog excelReaderDialog = LILExcelReaderDialog::construct();  
   
     if(excelReaderDialog.prompt())  
     {  
       excelReaderDialog.runOperation();  
     }  
   }  
   
 }  

La dialog si presenta così:





Nessun commento:

Posta un commento