i currently developed a custom solution that imports csv file of type xlsx (Excel) to the system, below is 1st screenshot of the excel sheet. its a static sheet that has company logo from column 1 till 7. with all the fields that needs to be imported to the system.
on the bank statement form , in the action pane, i added the button that opens a dialog to add the excel sheet.
for the below class, it contains the update bank reconcilation and create bank statement method. what is happening is that the excel is not being imported as its taking alot of time and at the end its failing, even that the excel has only 1 record, its taking forever. i debugged the code and found out that fileUploadResult is coming null, so its exiting to the error message "file error upload" and keeps going in infinit loop.
if anyone can provide me solution in order to resolve it. the main purpose of this functionality is to import bank reconcilation using static excel sheet.
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class ImportBankReconcilation
{
public void run()
{
this.updateBankReconcilation();
}
void updateBankReconcilation()
{
System.IO.Stream stream;
FileUploadBuild fileUpload;
DialogGroup dlgUploadGroup;
FileUploadBuild fileUploadBuild;
FormBuildControl formBuildControl;
BankReconcilation bankReconcilationRecord, insertBankReconcilation ;
str UserName , CompanyName , AccountName , ShortName , Filters , DateOrder , TotalCreditTransactions ;
str TotalDebitAmount , AccountNumber , TotalCreditAmount , ProcessingTime , CreditDebit , ResultsPerPage;
str DateRange , TransactionType , TotalDebitTransactions , TransactionDescription ;
str Description,DescriptionExtra,Reference,Remarks,Debit,Credit,Balance;
str DateTo , DateFrom , FieldDate;
utcDateTime DateTime;
Dialog dialog = new Dialog("Bank Reconciliation Data Import");
dlgUploadGroup = dialog.addGroup("@SYS54759");
formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());
fileUploadBuild = formBuildControl.addControlEx(classstr(FileUpload), "Upload");
fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
fileUploadBuild.fileTypesAccepted(".xlsx");
if (dialog.run() && dialog.closedOk())
{
FileUpload fileUploadControl = dialog.formRun().control(dialog.formRun().controlId("Upload"));
FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
if (fileUploadResult != null && fileUploadResult.getUploadStatus())
{
stream = fileUploadResult.openResult();
using (ExcelPackage package = new ExcelPackage(stream))
{
int rowCount, i, j , columnCount;
package.Load(stream);
ExcelWorksheet worksheet = package.get_Workbook().get_Worksheets().get_Item(1); // Assumes data is in the first sheet
OfficeOpenXml.ExcelRange range = worksheet.Cells;
rowCount = worksheet.Dimension.End.Row;
columnCount = worksheet.Dimension.End.Column;
delete_from bankReconcilationRecord;
for (i =9; i <= rowCount; i++) // i=9
{
if(i == 9)
{
UserName = (range.get_Item(i, 2).value);
insertBankReconcilation.UserName = UserName;
}
if(i == 10)
{
DateTime = (range.get_Item(i, 2).value);
insertBankReconcilation.DateTime = DateTime;
}
if(i == 11)
{
CompanyName = (range.get_Item(i, 2).value);
insertBankReconcilation.CompanyName = CompanyName;
}
if(i == 12)
{
AccountName = (range.get_Item(i, 2).value);
insertBankReconcilation.AccountName = AccountName;
}
if(i == 13)
{
ShortName = (range.get_Item(i, 2).value);
insertBankReconcilation.ShortName = ShortName;
}
if(i == 15)
{
DateOrder = (range.get_Item(i, 2).value);
insertBankReconcilation.DateOrder = DateOrder;
}
if(i == 16)
{
TotalCreditTransactions = (range.get_Item(i, 2).value);
insertBankReconcilation.TotalCreditTransactions = TotalCreditTransactions;
}
if(i == 18)
{
TotalDebitAmount = (range.get_Item(i, 2).value);
insertBankReconcilation.TotalDebitAmount = TotalDebitAmount;
}
if(i == 19)
{
AccountNumber = (range.get_Item(i, 2).value);
insertBankReconcilation.AccountNumber = AccountNumber;
}
if(i == 20)
{
TotalCreditAmount = (range.get_Item(i, 2).value);
insertBankReconcilation.TotalCreditAmount = TotalCreditAmount;
}
if(i == 21)
{
ProcessingTime = (range.get_Item(i, 2).value);
insertBankReconcilation.ProcessingTime = ProcessingTime;
}
if(i == 22)
{
CreditDebit = (range.get_Item(i, 2).value);
insertBankReconcilation.CreditDebit = CreditDebit;
}
if(i == 23)
{
ResultsPerPage = (range.get_Item(i, 2).value);
insertBankReconcilation.ResultsPerPage = ResultsPerPage;
}
if(i == 24)
{
DateTo = (range.get_Item(i, 2).value);
insertBankReconcilation.DateTo = str2Date(DateTo,DateSeparator::Slash);
}
if(i == 25)
{
DateFrom = (range.get_Item(i, 2).value);
insertBankReconcilation.DateFrom = str2Date(DateFrom,DateSeparator::Slash);
}
if(i == 26)
{
DateRange = (range.get_Item(i, 2).value);
insertBankReconcilation.DateRange = DateRange;
}
if(i == 27)
{
TransactionType = (range.get_Item(i, 2).value);
insertBankReconcilation.TransactionType = TransactionType;
}
if(i == 28)
{
TotalDebitTransactions = (range.get_Item(i, 2).value);
insertBankReconcilation.TotalDebitTransactions = TotalDebitTransactions;
}
if(i == 31)
{
for(j = 32 ; j <= 87 ; j++)
{
FieldDate = (range.get_Item(i, j).value);
TransactionDescription = (range.get_Item(i, j).value);
Description = (range.get_Item(i, j).value);
DescriptionExtra = (range.get_Item(i, j).value);
// Reference = (range.get_Item(i, 4).value);
Reference = (range.get_Item(i, j).value);
Remarks = (range.get_Item(i, j).value);
Debit = (range.get_Item(i, j).value);
Credit = (range.get_Item(i, j).value);
Balance = (range.get_Item(i, j).value);
insertBankReconcilation.FieldDate = str2Date(FieldDate,DateSeparator::Slash);
insertBankReconcilation.TransactionDescription = TransactionDescription;
insertBankReconcilation.Description = Description;
insertBankReconcilation.DescriptionExtra = DescriptionExtra;
insertBankReconcilation.Reference = Reference;
insertBankReconcilation.Remarks = Remarks;
insertBankReconcilation.Debit = Debit;
insertBankReconcilation.Credit = Credit;
insertBankReconcilation.Balance = Balance;
ttsbegin;
insertBankReconcilation.insert();
ttscommit;
}
}
}
}
}
else
{
error("File upload error.");
}
}
this.createBankStatement();
}
public bankAccountTable createBankStatement()
{
BankStmtISOReportEntry BankStmtISOReportEntry;
BankAccountTable bankAccountTable;
BankReconcilation BankReconcilation;
BankStmtISOCashBalance BankStmtISOCashBalance;
BankStmtISOAccountStatement BankStmtISOAccountStatement;
real EndingBalance;
ttsbegin;
bankAccountTable.initValue();
BankStmtISOReportEntry.initValue();
BankStmtISOCashBalance.initValue();
BankStmtISOAccountStatement.initValue();
while select BankReconcilation
{
select BankAccountTable where BankAccountTable.Name == BankReconcilation.CompanyName;
bankAccountTable.AccountNum = BankReconcilation.AccountNumber;
BankStmtISOReportEntry.editCreditAmount(true,str2Num(BankReconcilation.Debit)) ;
BankStmtISOReportEntry.editDebitAmount(true,str2Num(BankReconcilation.Credit)) ;
BankStmtISOReportEntry.BookingDateTime = BankReconcilation.FieldDate;
BankStmtISOReportEntry.AdditionalEntryInformation = BankReconcilation.Description;
BankStmtISOReportEntry.ReferenceNumber = BankReconcilation.Reference;
EndingBalance = str2Num(BankReconcilation.Balance) + str2Num(BankReconcilation.Debit) - str2Num(BankReconcilation.Credit);
BankStmtISOCashBalance.editEndingBalance(true , EndingBalance );
BankStmtISOCashBalance.editOpeningBalance(true , str2Num(BankReconcilation.Balance));
select RecId from bankStmtISOAccountStatement
where bankStmtISOAccountStatement.BankAccountTable == bankAccountTable.AccountID;
if (!bankStmtISOAccountStatement.RecId)
{
bankStmtISOAccountStatement.BankAccountTable = BankReconcilation.AccountNumber;
}
//bankAccountTable.insert();
BankStmtISOReportEntry.insert();
BankStmtISOCashBalance.insert();
BankStmtISOAccountStatement.insert();
}
ttscommit;
return bankAccountTable;
}
public static void main(Args args)
{
ImportBankReconcilation ImportBankReconcilation = new ImportBankReconcilation();
ImportBankReconcilation.run();
}
}
