Local object &oWorkApp, &oWorkBook;
Local SQL &Jrnl_hdr_sql, &Jrnl_ln_sql;
Local Record &Jrnl_hdr_rec, &Jrnl_ln_rec;
Local number &Row_count, &Col_count, &Count, &i, &Jrnl_line_nbr, &Jrnl_total_lines;
Local number &Jrnl_ln_Tran_Amt, &Jrnl_ln_Base_Amt, &Jrnl_ln_Stat_Amt, &Jrnl_ln_Ex_Rate;
Local string &Jrnl_ln_Account, &Jrnl_ln_BU, &Jrnl_Hdr_Bu, &Jrnl_hdr_Journal_Date1;
Local date &Jrnl_hdr_Journal_Date, &Jrnl_hdr_Rev_Date;
Local string &Jrnl_hdr_Journal_ID, &Jrnl_hdr_Ledger_GRP, &Jrnl_hdr_Base_CURR, &Jrnl_hdr_Source;
/* Initializing Column and Row Positions for Reading Journal Header Details from Excel */
Local number &Col_Jrnl_ID = 0;
Local number &Col_Journal_Date = 1;
Local number &Col_Ledger_GRP = 2;
Local number &Col_Base_CURR = 3;
Local number &Col_Source = 4;
Local number &Col_Rev_Code = 5;
Local number &Col_Def_ex_rate = 6;
Local number &Col_Def_Rev_Date = 7;
Local number &Col_Rate_Type = 8;
Local number &Col_Verify_Rate = 10;
Local number &Col_Trans_Ref_Num = 12;
Local number &Row_Trans_Ref_Num = 0;
Local number &Col_Descr254 = 1;
Local number &Row_Descr254 = 3;
Local number &Row_Journal_Class = 5;
Local number &Row_Journal_Desc = 3;
Local number &Row_Header = 2;
/* Initializing Column and Row Positions for Reading Journal Line Details from Excel */
Local number &Col_BU = 0;
Local number &Col_Account = 1;
Local number &Col_Product = 2;
Local number &Col_Deptid = 3;
Local number &Col_Oper_Unit = 4;
Local number &Col_Class_Fld = 5;
Local number &Col_Project_ID = 6;
Local number &Col_Affiliate = 7;
Local number &Col_Trans_Curr = 8;
Local number &Col_Tran_Amt = 9;
Local number &Col_Ex_Rate = 10;
Local number &Col_Base_Amt = 11;
Local number &Col_VAT_App = 12;
Local number &Col_VAT_CD = 13;
Local number &Col_VAT_Trans_Type = 14;
Local number &Col_Ref = 15;
Local number &Col_Descr = 16;
Local number &Col_Open_Item = 17;
Local number &Col_Stat_Code = 18;
Local number &Col_Stat_Amt = 19;
Local number &Col_Cust_ID = 20;
Local string &SCB_FilePath = SCB_JUP_UPD_AET.ATTACHUSERFILE.Value;
Local JavaObject &SCB_JUP_Object = CreateJavaObject("jup");
Local JavaObject &SCB_WorkBook = &SCB_JUP_Object.initiateworkbook(&SCB_FilePath);
Local JavaObject &SCB_Sheet = &SCB_WorkBook.getSheet("Journal1");
&SCB_Excel_Ready = &SCB_Sheet.getCell(12, 5).getContents();
&SCB_Version = &SCB_Sheet.getCell(13, 1).getContents();
&SCB_Version = RTrim(LTrim(&SCB_Version));
&SCB_Row_Count = &SCB_Sheet.getCell(14, 1).getContents();
If &SCB_Excel_Ready <> "Ready" Then
&SCB_WorkBook.close();
Error MessageBox(0, "", 23000, 44, "The Excel trying to upload is not in Ready status. Please check and load again. Process Failed.");
Exit (1);
Else
&Jrnl_Hdr_Bu = " ";
/* Read Header row from Excel columns*/
&Jrnl_hdr_Journal_ID = &SCB_Sheet.getCell(&Col_Jrnl_ID, &Row_Header).getContents();
&Jrnl_hdr_Journal_Date1 = &SCB_Sheet.getCell(&Col_Journal_Date, &Row_Header).getContents();
&Jrnl_hdr_Ledger_GRP = &SCB_Sheet.getCell(&Col_Ledger_GRP, &Row_Header).getContents();
&Jrnl_hdr_Base_CURR = &SCB_Sheet.getCell(&Col_Base_CURR, &Row_Header).getContents();
&Jrnl_hdr_Source = &SCB_Sheet.getCell(&Col_Source, &Row_Header).getContents();
&Jrnl_hdr_Rev_Code = &SCB_Sheet.getCell(&Col_Rev_Code, &Row_Header).getContents();
&Jrnl_hdr_Def_ex_rate = &SCB_Sheet.getCell(&Col_Def_ex_rate, &Row_Header).getContents();
&Jrnl_hdr_Rev_Date1 = &SCB_Sheet.getCell(&Col_Def_Rev_Date, &Row_Header).getContents();
&Jrnl_hdr_Rate_Type = &SCB_Sheet.getCell(&Col_Rate_Type, &Row_Header).getContents();
&Jrnl_hdr_Verify_Rate = &SCB_Sheet.getCell(&Col_Verify_Rate, &Row_Header).getContents();
&Jrnl_hdr_Journal_Class = &SCB_Sheet.getCell(2, &Row_Journal_Class).getContents();
&Jrnl_hdr_Journal_Desc = &SCB_Sheet.getCell(1, &Row_Journal_Desc).getContents();
&Jrnl_hdr_Trans_Ref_Num = &SCB_Sheet.getCell(&Col_Trans_Ref_Num, &Row_Trans_Ref_Num).getContents();
&Jrnl_hdr_Descr254 = &SCB_Sheet.getCell(&Col_Descr254, &Row_Descr254).getContents();
<*SQLExec("SELECT to_char(to_date(:1, 'dd-mm-yyyy'),'ddmmyyyy') from PSCLOCK", &Jrnl_hdr_Journal_Date1, &Journal_Date);
rem SQLExec("SELECT %Dateout(:1) from PSCLOCK", &Jrnl_hdr_Journal_Date1, &Jrnl_hdr_Journal_Date);
SQLExec("SELECT to_char(to_date(:1, 'dd-mm-yyyy'),'ddmmyyyy') from PSCLOCK", &Jrnl_hdr_Rev_Date1, &Rev_Date);*>
MessageBox(0, "", 0, 0, "&Jrnl_hdr_Journal_Date1: " | &Jrnl_hdr_Journal_Date1);
&Journal_Date = ConvertDate(&Jrnl_hdr_Journal_Date1);
MessageBox(0, "", 0, 0, "&Journal_Date: " | &Journal_Date);
&Jrnl_hdr_Journal_Date = DateValue(&Journal_Date);
If All(&Jrnl_hdr_Rev_Date1) Then
MessageBox(0, "", 0, 0, "&Jrnl_hdr_Rev_Date1 " | &Jrnl_hdr_Rev_Date1);
&Rev_Date = ConvertDate(&Jrnl_hdr_Rev_Date1);
&Jrnl_hdr_Rev_Date = DateValue(&Rev_Date);
End-If;
MessageBox(0, "", 0, 0, "&Jrnl_hdr_Rev_Date: " | &Jrnl_hdr_Rev_Date);
&Jrnl_hdr_rec = CreateRecord(Record.SCB_JUP_HDR_TMP);
&Jrnl_hdr_sql = CreateSQL("%Insert (:1)");
&Jrnl_hdr_sql.BulkMode = True;
&Jrnl_ln_count = 0;
&Jrnl_ln_rec = CreateRecord(Record.SCB_JUP_LN_TMP);
&Jrnl_ln_sql = CreateSQL("%Insert (:1)");
&Jrnl_ln_sql.BulkMode = True;
For &i = 10 To &SCB_Row_Count - 1
&Jrnl_ln_BU = &SCB_Sheet.getCell(&Col_BU, &i).getContents();
If All(&Jrnl_ln_BU) Then
If &Jrnl_ln_BU <> &Jrnl_Hdr_Bu Then
&Jrnl_ln_count = 0;
&Jrnl_Hdr_Bu = &Jrnl_ln_BU;
SQLExec(SQL.SCB_JUP_GETVER_SQL, &Jrnl_Hdr_Bu, &Version);
If &Version <> &SCB_Version Then
&SCB_WorkBook.close();
Error MessageBox(0, "", 23000, 56, "The version of the excel uploaded is incorrect. Process Failed.");
Exit (1);
End-If;
&Jrnl_hdr_rec.PROCESS_INSTANCE.Value = SCB_JUP_UPD_AET.PROCESS_INSTANCE;
&Jrnl_hdr_rec.RUNDATE.Value = %Date;
&Jrnl_hdr_rec.EMPLID.Value = %OperatorId;
&Jrnl_hdr_rec.BUSINESS_UNIT.Value = &Jrnl_Hdr_Bu;
&Jrnl_hdr_rec.JOURNAL_ID.Value = &Jrnl_hdr_Journal_ID;
&Jrnl_hdr_rec.JOURNAL_DATE.Value = &Jrnl_hdr_Journal_Date;
rem &Jrnl_hdr_rec.JOURNAL_DATE.Value = %Date;
&Jrnl_hdr_rec.LEDGER_GROUP.Value = &Jrnl_hdr_Ledger_GRP;
&Jrnl_hdr_rec.DESCR254.Value = &Jrnl_hdr_Descr254;
&Jrnl_hdr_rec.CURRENCY_CD.Value = &Jrnl_hdr_Base_CURR;
&Jrnl_hdr_rec.SOURCE.Value = &Jrnl_hdr_Source;
&Jrnl_hdr_rec.FOREIGN_CURRENCY.Value = &Jrnl_hdr_Base_CURR;
&Jrnl_hdr_rec.REVERSAL_CD.Value = &Jrnl_hdr_Rev_Code;
&Jrnl_hdr_rec.RATE_DIV.Value = 1;
&Jrnl_hdr_rec.REVERSAL_DATE.Value = &Jrnl_hdr_Rev_Date;
&Jrnl_hdr_rec.RT_TYPE.Value = &Jrnl_hdr_Rate_Type;
&Jrnl_hdr_rec.SCB_JUP_RT_CHK.Value = &Jrnl_hdr_Verify_Rate;
&Jrnl_hdr_rec.TRANS_REF_NUM.Value = &Jrnl_hdr_Trans_Ref_Num;
&Jrnl_hdr_rec.JOURNAL_CLASS.Value = &Jrnl_hdr_Journal_Class;
&Jrnl_hdr_rec.DESCR200.Value = &Jrnl_hdr_Journal_Desc;
&Jrnl_hdr_rec.JRNL_TOTAL_DEBITS.Value = 0;
&Jrnl_hdr_rec.JRNL_TOT_CREDITS.Value = 0;
&Jrnl_hdr_sql.Execute(&Jrnl_hdr_rec);
End-If;
&Jrnl_ln_count = &Jrnl_ln_count + 1;
rem &Jrnl_ln_Ledger = &SCB_Sheet.getCell(&Col_Ledger, &i).getContents();
&Jrnl_ln_Account = &SCB_Sheet.getCell(&Col_Account, &i).getContents();
&Jrnl_ln_Product = &SCB_Sheet.getCell(&Col_Product, &i).getContents();
&Jrnl_ln_Deptid = &SCB_Sheet.getCell(&Col_Deptid, &i).getContents();
&Jrnl_ln_Oper_Unit = &SCB_Sheet.getCell(&Col_Oper_Unit, &i).getContents();
&Jrnl_ln_Class_Fld = &SCB_Sheet.getCell(&Col_Class_Fld, &i).getContents();
&Jrnl_ln_Project_ID = &SCB_Sheet.getCell(&Col_Project_ID, &i).getContents();
&Jrnl_ln_Affiliate = &SCB_Sheet.getCell(&Col_Affiliate, &i).getContents();
&Jrnl_ln_Trans_Curr = &SCB_Sheet.getCell(&Col_Trans_Curr, &i).getContents();
&Jrnl_ln_Tran_Amt = &SCB_Sheet.getCell(&Col_Tran_Amt, &i).getContents();
&Jrnl_ln_Ex_Rate = &SCB_Sheet.getCell(&Col_Ex_Rate, &i).getContents();
&Jrnl_ln_Base_Amt = &SCB_Sheet.getCell(&Col_Base_Amt, &i).getContents();
&Jrnl_ln_VAT_App = &SCB_Sheet.getCell(&Col_VAT_App, &i).getContents();
&Jrnl_ln_VAT_CD = &SCB_Sheet.getCell(&Col_VAT_CD, &i).getContents();
&Jrnl_ln_VAT_Trans_Type = &SCB_Sheet.getCell(&Col_VAT_Trans_Type, &i).getContents();
&Jrnl_ln_Ref = &SCB_Sheet.getCell(&Col_Ref, &i).getContents();
&Jrnl_ln_Descr = &SCB_Sheet.getCell(&Col_Descr, &i).getContents();
&Jrnl_ln_Open_Item = &SCB_Sheet.getCell(&Col_Open_Item, &i).getContents();
&Jrnl_ln_Stat_Code = &SCB_Sheet.getCell(&Col_Stat_Code, &i).getContents();
&Jrnl_ln_Stat_Amt = &SCB_Sheet.getCell(&Col_Stat_Amt, &i).getContents();
&Jrnl_ln_Cust_ID = &SCB_Sheet.getCell(&Col_Cust_ID, &i).getContents();
/* copy to the record */
&Jrnl_ln_rec.PROCESS_INSTANCE.Value = SCB_JUP_UPD_AET.PROCESS_INSTANCE;
&Jrnl_ln_rec.RUNDATE.Value = %Date;
&Jrnl_ln_rec.EMPLID.Value = %OperatorId;
&Jrnl_ln_rec.BUSINESS_UNIT.Value = &Jrnl_ln_BU;
&Jrnl_ln_rec.JOURNAL_ID.Value = &Jrnl_hdr_Journal_ID;
&Jrnl_ln_rec.JOURNAL_DATE.Value = &Jrnl_hdr_Journal_Date;
rem &Jrnl_ln_rec.JOURNAL_DATE.Value = %Date;
&Jrnl_ln_rec.JOURNAL_LINE.Value = &Jrnl_ln_count;
&Jrnl_ln_rec.LINE_NBR.Value = &i + 1;
&Jrnl_ln_rec.LEDGER.Value = &Jrnl_ln_Ledger;
&Jrnl_ln_rec.ACCOUNT.Value = &Jrnl_ln_Account;
&Jrnl_ln_rec.PRODUCT.Value = &Jrnl_ln_Product;
&Jrnl_ln_rec.DEPTID.Value = &Jrnl_ln_Deptid;
&Jrnl_ln_rec.OPERATING_UNIT.Value = &Jrnl_ln_Oper_Unit;
&Jrnl_ln_rec.CLASS_FLD.Value = &Jrnl_ln_Class_Fld;
&Jrnl_ln_rec.PROJECT_ID.Value = &Jrnl_ln_Project_ID;
&Jrnl_ln_rec.AFFILIATE.Value = &Jrnl_ln_Affiliate;
&Jrnl_ln_rec.FOREIGN_CURRENCY.Value = &Jrnl_ln_Trans_Curr;
&Jrnl_ln_rec.FOREIGN_AMOUNT.Value = &Jrnl_ln_Tran_Amt;
&Jrnl_ln_rec.CURRENCY_CD.Value = &Jrnl_hdr_Base_CURR;
&Jrnl_ln_rec.MONETARY_AMOUNT.Value = &Jrnl_ln_Base_Amt;
&Jrnl_ln_rec.RATE_MULT.Value = &Jrnl_ln_Ex_Rate;
&Jrnl_ln_rec.VAT_APPLICABILITY.Value = &Jrnl_ln_VAT_App;
&Jrnl_ln_rec.TAX_CD.Value = &Jrnl_ln_VAT_CD;
&Jrnl_ln_rec.VAT_TXN_TYPE_CD.Value = &Jrnl_ln_VAT_Trans_Type;
&Jrnl_ln_rec.JRNL_LN_REF.Value = &Jrnl_ln_Ref;
&Jrnl_ln_rec.LINE_DESCR.Value = &Jrnl_ln_Descr;
&Jrnl_ln_rec.OPEN_ITEM_KEY.Value = &Jrnl_ln_Open_Item;
&Jrnl_ln_rec.STATISTICS_CODE.Value = &Jrnl_ln_Stat_Code;
&Jrnl_ln_rec.STATISTIC_AMOUNT.Value = &Jrnl_ln_Stat_Amt;
&Jrnl_ln_rec.CUST_ID.Value = &Jrnl_ln_Cust_ID;
&Jrnl_ln_rec.JOURNAL_LINE_DATE.Value = &Jrnl_hdr_Journal_Date;
rem &Jrnl_ln_rec.JOURNAL_LINE_DATE.Value = %Date;
&Jrnl_ln_rec.RT_TYPE.Value = &Jrnl_hdr_Rate_Type;
&Jrnl_ln_sql.Execute(&Jrnl_ln_rec);
End-If;
End-For;
&Jrnl_hdr_sql.Close();
&Jrnl_ln_sql.Close();
End-If;
&SCB_WorkBook.close();
###############################################################
jup.java - Java Code & Related Jar details
this class is created as a wrapper class to avoid error
###############################################################
import java.io.*;
import jxl.*;
public class jup {
public static void main(String[] args){
}
public Workbook initiateworkbook(String Filename){
try {
Workbook ReadExcel = Workbook.getWorkbook(new File(Filename));
return ReadExcel;
}
catch (Exception i)
{
return null;
}
}
}
###############################################################
Jar details
###############################################################
Above referenced jars can be downloaded from the below
mentioned locaiton.
http://www.java2s.com/Code/Jar/j/Downloadjxljar.htm
Place the jup.java file in claases folder of appserver and reboot the appserver.
above code is in reference from Mr.Bhaskaran RajGanesh
No comments:
Post a Comment