Hi,
Is this the best way to write code to upload file from excel and do x++ logic based on the records in the excel file?
the file has 1 coloumn for InventTransId and we need to do some updates for each record
[
DataContractAttribute,
SysOperationContractProcessingAttribute(
classStr(Service1UIBuilder),
SysOperationDataContractProcessingMode::CreateSeparateUIBuilderForEachContract)
]
class Service1Contract implements SysOperationValidatable
str fileUpload;
[DataMemberAttribute]
public str parmFileUploadResult(str _fileUpload = fileUpload)
{
fileUpload = _fileUpload;
return fileUpload;
}
public boolean validate()
{
boolean ret = true;
if (fileUpload == '')
{
ret = checkFailed("Select File");
}
return ret;
}
}
class Service1UIBuilder extends SysOperationAutomaticUIBuilder
{
public void postBuild()
{
DialogGroup dialogGroup;
FormBuildControl formBuildControl;
FileUploadBuild dialogFileUpload;
DialogField fileUploadField;
dialogGroup = dialog.addGroup("@SYS319541");
formBuildControl = dialog.formBuildDesign().control(dialogGroup.name());
dialogFileUpload = formBuildControl.addControlEx(classStr(FileUpload), 'File Upload');
dialogFileUpload.style(FileUploadStyle::MinimalWithFilename);
dialogFileUpload.baseFileUploadStrategyClassName(classStr(FileUploadTemporaryStorageStrategy));
// Restrict picker to Excel files only
dialogFileUpload.fileTypesAccepted('.xlsx');
fileUploadField = this.bindInfo().getDialogField(
this.dataContractObject(),
methodStr(Service1Contract, parmFileUploadResult));
fileUploadField.value('');
fileUploadField.visible(false);
}
public void postRun()
{
FileUpload fileUpload = dialog.formRun().control(dialog.formRun().controlId('File Upload'));
fileUpload.notifyUploadCompleted += eventHandler(this.uploadCompleted);
super();
}
protected void uploadCompleted()
{
FileUpload fileUpload = dialog.formRun().control(dialog.formRun().controlId('File Upload'));
fileUpload.notifyUploadCompleted -= eventHandler(this.uploadCompleted);
FileUploadTemporaryStorageResult result = fileUpload.getFileUploadResult();
this.importExcelResult(result);
}
private void importExcelResult(FileUploadTemporaryStorageResult _result)
{
System.IO.Stream stream;
OfficeOpenXml.ExcelPackage package;
OfficeOpenXml.ExcelWorksheet worksheet;
OfficeOpenXml.ExcelWorkbook workbook;
OfficeOpenXml.ExcelWorksheets worksheets;
System.Exception ex;
int rowCount, colCount;
int row, col;
str lineText;
Notes uploadResult = '';
boolean isFirstRow = true;
try
{
stream = _result.openResult();
package = new OfficeOpenXml.ExcelPackage(stream);
workbook = package.get_Workbook();
worksheets = workbook.get_Worksheets();
worksheet = worksheets.get_Item(1); // First sheet
if (worksheet.get_Dimension())
{
rowCount = worksheet.get_Dimension().get_End().get_Row();
colCount = worksheet.get_Dimension().get_End().get_Column();
// Skip the header row.
for (row = 2; row <= rowCount; row++)
{
lineText = '';
for (col = 1; col <= colCount; col++)
{
if (col > 1)
{
lineText += ',';
}
lineText += worksheet.get_Cells().get_Item(row, col).get_Text();
}
if (isFirstRow)
{
uploadResult = lineText;
isFirstRow = false;
}
else
{
uploadResult += strFmt('\n%1', lineText);
}
}
}
}
catch (Exception::CLRError)
{
ex = ClrInterop::getLastException();
if (ex)
{
error(ex.get_Message());
}
throw error("@ElectronicReporting:SelectedFileCouldNotBeImported");
}
finally
{
if (package)
{
package.Dispose();
}
}
this.bindInfo().getDialogField(
this.dataContractObject(),
methodStr(Service1Contract, parmFileUploadResult))
.value(uploadResult);
}
}
public class Service1Controller extends SysOperationServiceController
{
protected void new()
{
super(
classStr(Service1),
methodStr(Service1, processOperation),
SysOperationExecutionMode::Synchronous);
}
public ClassDescription caption()
{
return "@SYS309953";
}
public static void main(Args _args)
{
Service1Controller controller = new Service1Controller();
controller.parmShowDialog(true);
controller.showBatchTab(false);
controller.parmArgs(_args);
controller.parmDialogCaption("Service1");
controller.startOperation();
}
}
using OfficeOpenXml;
using OfficeOpenXml.ExcelWorksheet;
using OfficeOpenXml.ExcelRange;
using System.IO;
public class Service1 extends SysOperationServiceBase
{
public void processOperation(Service1Contract _contract)
{
container inventTransIdList;
InventTransId inventTransId;
if (!_contract.parmFileUploadResult())
{
error("@SysSecLabels:ErrorMessage_FileUploadFailure");
return;
}
inventTransIdList = str2con(_contract.parmFileUploadResult(), '\n');
for (int i = 1; i <= conLen(inventTransIdList); i++)
{
inventTransId = conPeek(inventTransIdList, i);
if (inventTransId != '')
{
if (this.validateInventTransId(inventTransId))
{
this.processSingleLine(inventTransId);
}
}
}
info("Complete");
}
public boolean validateInventTransId(InventTransId _inventTransId)
{
boolean ret = true;
SalesLine salesLine = SalesLine::findInventTransId(_inventTransId);
if (!salesLine)
{
ret = checkFailed(strFmt("Doesn't exist %1 %2", _inventTransId, curExt()));
}
return ret;
}
protected void processSingleLine(InventTransId _inventTransId)
{
SalesLine salesLine;
ttsbegin;
select firstonly forupdate salesLine
where salesLine.InventTransId == _inventTransId
&& salesLine.SalesStatus == SalesStatus::Delivered
if (salesLine.RecId)
{
salesLine.RemainSalesFinancial = 0;
salesLine.LineAmount = 0;
salesLine.SalesQty = 0;
salesLine.update();
}
ttscommit;
}
}
