Hello sir,
My requirement is i want to import 2 fields from table No.273 (i.e Bank account No,statement No.) and other fields from excel into bank acc. reconciliation line table.For this i have created and report .PFB code for the same.
Documentation()
OnInitReport()
OnPreReport()
{
ExcelBuffer.LOCKTABLE;
ExcelBuffer.OpenBook(ServerFilename,SheetName);
ExcelBuffer.ReadSheet;
GetLastRowandColumn;
FOR X:=14 TO Totalrows DO
InsertData(X);
ExcelBuffer.DELETEALL;
MESSAGE('Import Completed.');
}
OnPostReport()
Bank Acc. Reconciliation - OnPreDataItem()
Bank Acc. Reconciliation - OnAfterGetRecord()
Accountnovar:="Bank Acc. Reconciliation"."Bank Account No.";
Statementvar:="Bank Acc. Reconciliation"."Statement No.";
//MESSAGE(Accountnovar);
//MESSAGE(Statementvar);
ExcelBuffer.LOCKTABLE;
ExcelBuffer.OpenBook(ServerFilename,SheetName);
ExcelBuffer.ReadSheet;
GetLastRowandColumn;
FOR X:=14 TO Totalrows DO
InsertData(X);
ExcelBuffer.DELETEALL;
MESSAGE('Import Completed.')
Bank Acc. Reconciliation - OnPostDataItem()
Excel Buffer - OnPreDataItem()
Excel Buffer - OnAfterGetRecord()
Excel Buffer - OnPostDataItem()
GetLastRowandColumn()
ExcelBuffer.SETRANGE("Row No.",1);
Totalcolumn:=ExcelBuffer.COUNT;
ExcelBuffer.RESET;
IF ExcelBuffer.FINDLAST THEN
Totalrows:=ExcelBuffer."Row No.";
OpenBook(FileName : Text;SheetName : Text[250])
IF FileName = '' THEN
ERROR(Text001);
IF FileName = '' THEN
ERROR(Text002);
XlWrkBkReader := XlWrkBkReader.Open(FileName);
IF XlWrkBkReader.HasWorksheet(SheetName) THEN BEGIN
XlWrkShtReader := XlWrkBkReader.GetWorksheetByName(SheetName);
END ELSE BEGIN
QuitExcel;
ERROR(Text004,SheetName);
END;
QuitExcel()
// Close and clear the OpenXml book
CloseBook;
// Clear the worksheet automation
IF NOT ISNULL(XlWrkSht) THEN
CLEAR(XlWrkSht);
// Clear the workbook automation
IF NOT ISNULL(XlWrkBk) THEN
CLEAR(XlWrkBk);
// Clear and quit the Excel application automation
IF NOT ISNULL(XlApp) THEN BEGIN
XlHelper.CallQuit(XlApp);
CLEAR(XlApp);
END;
CloseBook()
IF NOT ISNULL(XlWrkBkWriter) THEN BEGIN
XlWrkBkWriter.ClearFormulaCalculations;
XlWrkBkWriter.ValidateDocument;
XlWrkBkWriter.Close;
CLEAR(XlWrkShtWriter);
CLEAR(XlWrkBkWriter);
END;
IF NOT ISNULL(XlWrkBkReader) THEN BEGIN
CLEAR(XlWrkShtReader);
CLEAR(XlWrkBkReader);
END;
InsertData(RowNo : Integer)
BankaccReconciline."Statement Line No." +=10000;
//EVALUATE(BankaccReconciline."Statement Line No.",GetvalueAtCell(RowNo,1));
EVALUATE(BankaccReconciline."Transaction Date",GetvalueAtCell(RowNo,2));
EVALUATE(BankaccReconciline.Description,GetvalueAtCell(RowNo,3));
EVALUATE(BankaccReconciline."Check No.",GetvalueAtCell(RowNo,5));
IF GetvalueAtCell(RowNo,6) <> '' THEN
//bgk := GetvalueAtCell(RowNo,6)) * -1
EVALUATE(BankaccReconciline."Statement Amount",GetvalueAtCell(RowNo,6))
ELSE
IF GetvalueAtCell(RowNo,7) <> '' THEN BEGIN
EVALUATE(BankaccReconciline."Statement Amount",GetvalueAtCell(RowNo,7));
BankaccReconciline."Statement Amount":=BankaccReconciline."Statement Amount"*(-1);
END;
{
IF GetvalueAtCell(RowNo,6) = '' THEN
//bgk := GetvalueAtCell(RowNo,6)) * -1
EVALUATE(BankaccReconciline."Statement Amount",GetvalueAtCell(RowNo,7));
IF GetvalueAtCell(RowNo,7) = '' THEN
EVALUATE(BankaccReconciline."Statement Amount",GetvalueAtCell(RowNo,6));
}
//EVALUATE(BankaccReconciline."Applied Amount",GetvalueAtCell(RowNo,7));
EVALUATE(BankaccReconciline."Value Date",GetvalueAtCell(RowNo,9));
BankaccReconciline."Bank Account No." := Accountnovar;
BankaccReconciline."Statement No." :=Statementvar;
BankaccReconciline.INSERT(TRUE);
GetvalueAtCell(RowNo : Integer;Column : Integer) : Text
IF NOT ExcelBuffer1.GET(RowNo,Column)
THEN EXIT('') ELSE
EXIT(ExcelBuffer1."Cell Value as Text");
{
ExcelBuffer1.GET(RowNo,Column);
EXIT(ExcelBuffer1."Cell Value as Text");
}
