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();
}
}
}
