hi copy this code into text file and made it as a xpo
Exportfile for AOT version 1.0 or later
Formatversion: 1
***Element: CLS
; Microsoft Dynamics Class: PwCImport unloaded
; --------------------------------------------------------------------------------
CLSVERSION 1
CLASS #PwCImport
PROPERTIES
Name #PwCImport
Extends #ObjectRun
RunOn #Called from
ENDPROPERTIES
METHODS
Version: 3
SOURCE #classDeclaration
#public class PwCImport extends objectrun
#{
#}
ENDSOURCE
SOURCE #readexcel
#public static server PwC_Import readexcel(filenameopen _file)
#{
# #Excel
# #AviFiles
# SysExcelApplication excelApp = SysExcelApplication::construct();
# SysOperationProgress progress = new SysOperationProgress();
# SysExcelWorkSheet excelWorksheet;
# SysExcelRange excelRange;
# SysExcelCells excelCells;
# SysExcelCell excelCell;
# ComVariant cellValue = new ComVariant() ;
# boolean Flag_Found ;
# PwC_Import PwC_ImportLocal;
# int colno, colno_excel, colno_grid;
# int i;
# int TotalRow ;
# str strvalue ;
# COMVariant VariantValue ;
# Description Error;
#
# excelApp.workbooks().open( _File);
# excelWorksheet = excelApp.worksheets().itemFromNum(1);
# excelCells = excelWorksheet.cells();
# startlengthyoperation();
#
# flag_found=true ; TotalRow=1 ;
# //________________________________________________________
# excelCell=excelWorksheet.cells().item(TotalRow+1,1);
# strvalue=StrUpr(excelCell.value().bStr());
# if(strvalue!="**********")
# {
# excelworksheet = null;
# excelapp.quit();
# Throw Error("Invalid Excel Template. Please use template specified");
# }
# //________________________________________________________
# progress.setCaption("Data Import");
# progress.setAnimation(#AviFormLetter);
# startlengthyoperation();
# ttsbegin;
# while (Flag_Found==true)
# {
# progress.setText(strfmt("Scanning Records %1", TotalRow));
# //Marked____________________________________________________
# PwC_ImportLocal.Marked =NoYes::Yes;
# //TransDate_________________________________________________
# if(excelWorksheet.cells().item( TotalRow+2,1).value().date())
# {
# PwC_ImportLocal.Transdate =excelWorksheet.cells().item( TotalRow+2,1).value().date();
# }
# else
# {
# Error=Error+"Date ; ";
# PwC_ImportLocal.Transdate =datenull();
# PwC_ImportLocal.Marked =NoYes::No;
# }
# //Account___________________________________________________
# If(LedgerTable::exist(excelWorksheet.cells().item( TotalRow+2,2).value().bStr()))
# {
# PwC_ImportLocal.Account=excelWorksheet.cells().item( TotalRow+2,2).value().bStr();
# }
# else
# {
# Error =Error+"<A/C>; ";
# PwC_ImportLocal.Account ="";
# PwC_ImportLocal.Marked =NoYes::No;
# }
# //Transaction Text_____________________________________________
# PwC_ImportLocal.TransTxt =excelWorksheet.cells().item( TotalRow+2,3).value().bStr();
# //Offset Account_______________________________________________
# If(LedgerTable::exist(excelWorksheet.cells().item( TotalRow+2,4).value().bStr()))
# {
# PwC_ImportLocal.OffsetAccount =excelWorksheet.cells().item( TotalRow+2,4).value().bStr();
# }
# else
# {
# Error =Error+"<Offset A/C>; ";
# PwC_ImportLocal.OffsetAccount ="";
# PwC_ImportLocal.Marked =NoYes::No;
# }
# //Amount Check_________________________________________________
# if( (excelWorksheet.cells().item(TotalRow+2,5).value().double() && excelWorksheet.cells().item(TotalRow+2,6).value().double()) ||
# (!excelWorksheet.cells().item(TotalRow+2,5).value().double() && !excelWorksheet.cells().item(TotalRow+2,6).value().double()) )
# {
# Error =Error+"<Dr & Cr>; ";
# PwC_ImportLocal.Marked =NoYes::No;
# }
#
# //DebitAmount__________________________________________________
# PwC_ImportLocal.Debit = excelWorksheet.cells().item(TotalRow+2,5).value().double();
# //Credit Amount________________________________________________
# PwC_ImportLocal.Credit = excelWorksheet.cells().item(TotalRow+2,6).value().double();
# //Dimensions___________________________________________________
# PwC_ImportLocal.Dimensions[1] =excelWorksheet.cells().item(TotalRow+2,7).value().bStr();
# PwC_ImportLocal.Dimensions[2] =excelWorksheet.cells().item(TotalRow+2,8).value().bStr();
# PwC_ImportLocal.Dimensions[3] =excelWorksheet.cells().item(TotalRow+2,9).value().bStr();
# //Error Description____________________________________________
# PwC_ImportLocal.ErrorDesc=Error;
#
# Error=""; // Reset to Null
#
# TotalRow++;
# PwC_ImportLocal.insert();
# progress.incCount();
#
# if(excelWorksheet.cells().item(TotalRow+2,2).value().bstr()=="")
# Flag_Found=false;
# }
# ttscommit;
# endlengthyoperation();
# excelworksheet = null;
# excelapp.quit();
#
# return PwC_ImportLocal;
#}
ENDSOURCE
ENDMETHODS
ENDCLASS
***Element: FRM
; Microsoft Dynamics Forms unloaded
; --------------------------------------------------------------------------------
FRMVERSION 5
FORM #PwC_Import
PROPERTIES
Name #PwC_Import
ENDPROPERTIES
METHODS
Version: 3
SOURCE #classDeclaration
#public class FormRun extends ObjectRun
#{
# FormStringControl fileNameControl;
# #define.expImpFileExtension(".xls")
# LedgerJournalNameId LJournalName;
# #define.currentVersion(1)
# #localmacro.CurrentList
# LJournalName
# #endMacro
#
#}
ENDSOURCE
SOURCE #run
#public void run()
#{
# super();
# xSysLastValue::getLast(this);
# JournalName.text(LJournalName);
#}
ENDSOURCE
SOURCE #unpack
#public boolean unpack(container packedClass)
#{
# int version = runbase::getVersion(packedClass);
#
# switch (version)
# {
# case #CurrentVersion:
# [version,#CurrentList] = packedClass;
# return true;
# default :
# return false;
# }
#
# return false;
#}
ENDSOURCE
SOURCE #pack
#public container pack()
#{
#
# return [#CurrentVersion,#CurrentList];
#
#}
ENDSOURCE
SOURCE #close
#public void close()
#{
# super();
# LJournalName=JournalName.text();
# xSysLastValue::saveLast(this);
#}
#
ENDSOURCE
SOURCE #initParmDefault
#/*ARIJIT
#This method is called when no SysLastValue record can be found in xSysLastValue.GetLast()
#*/
#void initParmDefault()
#{
#}
#
ENDSOURCE
SOURCE #lastValueDesignName
#private IdentifierName lastValueDesignName()
#{
# return '';
#}
#
ENDSOURCE
SOURCE #lastValueElementName
#private IdentifierName lastValueElementName()
#{
# return this.name();
#}
#
ENDSOURCE
SOURCE #lastValueType
#private UtilElementType lastValueType()
#{
# return UtilElementType::Form;
#}
#
ENDSOURCE
SOURCE #lastValueUserId
#private UserId lastValueUserId()
#{
# return curuserid();
#}
#
ENDSOURCE
SOURCE #lastValueDataAreaId
#public dataAreaId lastValueDataAreaId()
#{
# return curExt();
#}
#
ENDSOURCE
SOURCE #init
#public void init()
#{
# ;
# xSysLastValue::getLast(this);
# super();
# CreateJournal.enabled(false);
# PrintTable.enabled(false);
#}
#
ENDSOURCE
SOURCE #fileNameLookupFilename
#str fileNameLookupFilename()
#{
# Filename filepath;
# Filename fileName;
# Filename fileType;
#
# [filepath, fileName, fileType] = fileNameSplit(fileNameControl.text());
#
# return fileName + fileType;
#}
ENDSOURCE
SOURCE #fileNameLookupTitle
#// AOSRunMode::client
#str fileNameLookupTitle()
#{
# return 'Lookup Title: ' + fileNameControl.label();
#}
ENDSOURCE
SOURCE #fileNameLookupFilter
#container fileNameLookupFilter()
#{
# #file
# return [WinApi::fileType('.xls'), #allfilesName+'.xls', #allFilesType, #allFiles];
#}
ENDSOURCE
SOURCE #fileNameLookupInitialPath
#// AOSRunMode::client
#str fileNameLookupInitialPath()
#{
# #WinAPI
#
# Filename filepath;
# Filename fileName;
# Filename fileType;
#
# [filepath, fileName, fileType] = Global::FileNameSplit(fileNameControl.text());
#
# return filepath;
#}
ENDSOURCE
ENDMETHODS
OBJECTBANK
DATASOURCE
OBJECTPOOL
PROPERTIES
Name #PwC_Import
Table #PwC_Import
AllowEdit #No
AllowCreate #No
AllowDelete #No
InsertIfEmpty #No
ENDPROPERTIES
FIELDLIST
ENDFIELDLIST
ENDOBJECTPOOL
METHODS
Version: 3
SOURCE #displayOption
#public void displayOption(Common _record, FormRowDisplayOption _options)
#{
# PwC_Import PwC_Importlocal;
# PwC_Importlocal = _record;
# super(_record, _options);
# if(PwC_Importlocal.ErrorDesc != "")
# {
# _options.backColor(8421631); //RED
# }
#}
ENDSOURCE
ENDMETHODS
ENDDATASOURCE
ENDOBJECTBANK
JOINS
ENDJOINS
DESIGN
PROPERTIES
Caption #Data Import
TitleDatasource #PwC_Import
SaveSize #Yes
ENDPROPERTIES
CONTAINER
CONTROL GROUP
;==== controlId:12290, containerId: 0, containerdata: 0
;----------------------------------------------------------
PROPERTIES
Name #Group
Width #Column width
Height #Column height
FrameType #Raised 3D
ENDPROPERTIES
CONTAINER
CONTROL GROUP
;==== controlId:12291, containerId:12290, containerdata:12290
;----------------------------------------------------------
PROPERTIES
Name #Header
Width #Column width
FrameType #Edged 3D
Columns #4
ArrangeGuide
ARRAY INDEX Columns
#0
#0
#0
#0
ENDARRAY
ENDPROPERTIES
CONTAINER
CONTROL WINDOW
;==== controlId:12292, containerId:12291, containerdata:12291
;----------------------------------------------------------
PROPERTIES
Name #Window
Width #25
Height #25
ImageResource #3184
Imagemode #Size to fit
BackStyle #Transparent
BackgroundColor #Button face (3D)
ENDPROPERTIES
ENDCONTROL
CONTROL STRINGEDIT
;==== controlId:12293, containerId:12291, containerdata:12291
;----------------------------------------------------------
PROPERTIES
Name #JournalName
AutoDeclaration #Yes
LabelAlignment #Right
Mandatory #Yes
ExtendedDataType
ARRAY
#LedgerJournalNameId
#
ENDARRAY
ENDPROPERTIES
METHODS
Version: 3
SOURCE #lookup
#void lookup()
#{
# Query query = new Query();
# QueryBuildDataSource queryBuildDataSource;
# QueryBuildRange queryBuildRange;
# SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(LedgerJournalName), this);
# ;
# sysTableLookup.addLookupField(fieldNum(LedgerJournalName, JournalName));
# sysTableLookup.addLookupField(fieldNum(LedgerJournalName, name));
# queryBuildDataSource = query.addDataSource(tableNum(LedgerJournalName));
# queryBuildRange = queryBuildDataSource.addRange(fieldNum(LedgerJournalName, JournalType));
# queryBuildRange.value(Enum2Str(LedgerJournalType::Daily));
# sysTableLookup.parmQuery(query);
# sysTableLookup.performFormLookup();
# // super()
#}
#
#
ENDSOURCE
ENDMETHODS
ENDCONTROL
CONTROL CHECKBOX
;==== controlId:12294, containerId:12291, containerdata:12291
;----------------------------------------------------------
PROPERTIES
Name #PostJournal
AutoDeclaration #Yes
Label #Post Journal
LabelAlignment #Right
ENDPROPERTIES
ENDCONTROL
CONTROL STRINGEDIT
;==== controlId:12295, containerId:12291, containerdata:12291
;----------------------------------------------------------
PROPERTIES
Name #FileOpen
AutoDeclaration #Yes
Width #Column width
Label #Select file to upload
LabelAlignment #Right
ExtendedDataType
ARRAY
#FilenameOpen
#
ENDARRAY
ENDPROPERTIES
METHODS
Version: 3
SOURCE #gotFocus
#public void gotFocus()
#{
# ;
# fileNameControl = this;
# super();
#}
ENDSOURCE
ENDMETHODS
ENDCONTROL
CONTROL BUTTONGROUP
;==== controlId:12296, containerId:12291, containerdata:12291
;----------------------------------------------------------
PROPERTIES
Name #ButtonGroup
ArrangeMethod #Horizontal, flush right
ENDPROPERTIES
CONTAINER
CONTROL BUTTON
;==== controlId:12297, containerId:12296, containerdata:12296
;----------------------------------------------------------
PROPERTIES
Name #UploadData
Text #Upload Data
ENDPROPERTIES
METHODS
Version: 3
SOURCE #clicked
#void clicked()
#{
# PwCImport PwCImport;
#
# if(!FileOpen.text())
# Throw Error("Please select a file");
# if(!JournalName.text())
# Throw Error("Please Select a Journal Name");
# super();
#
# //PayrollImport.readexcel(FileOpen.text());
# PwC_Import.setTmpData(PwCImport::readexcel(FileOpen.text()));
#
# PwC_Import_ds.executeQuery();
#
# this.enabled(false);
# CreateJournal.enabled(true);
# PrintTable.enabled(true);
#}
ENDSOURCE
ENDMETHODS
ENDCONTROL
CONTROL MENUBUTTON
;==== controlId:12298, containerId:12296, containerdata:12296
;----------------------------------------------------------
PROPERTIES
Name #MenuButton
Text #Options
ENDPROPERTIES
CONTAINER
CONTROL BUTTON
;==== controlId:12299, containerId:12298, containerdata:12298
;----------------------------------------------------------
PROPERTIES
Name #CreateJournal
AutoDeclaration #Yes
Text #Create Journal
ENDPROPERTIES
METHODS
Version: 3
SOURCE #clicked
#void clicked()
#{
# LedgerJournalTable LJTable;
# LedgerJournalTrans LJTrans;
# Int Counter;
# Int NumberOfJournalLines;
# LedgerJournalCheckPost LJCP;
# FileOpen.enabled(false);
# super();
# //=====Check if any lines Exist=====
# while select * from PwC_Import
# {
# if(PwC_Import.Marked)
# NumberOfJournalLines++;
# }
# if(NumberOfJournalLines==0)
# Throw Error("No Lines Exist for Uploading");
# //==================================
# startlengthyoperation();
# ttsbegin;
# //======Create Journal Header=======
# LJTable.JournalName =JournalName.text();
# LJTable.initFromLedgerJournalName(LJTable.JournalName);
# LJTable.insert();
# //==================================
# while select * from PwC_Import
# {
# if(PwC_Import.Marked)
# {
# LJTrans.JournalNum =LJTable.JournalNum;
# LJTrans.initValue();
# LJTrans.Voucher =NumberSeq::newGetNumFromCode(LedgerjournalName::Find(LJTable.JournalName).VoucherSeries).num();
# LJTrans.JournalNum =LJTable.JournalNum;
# LJTrans.TransDate =PwC_Import.Transdate;
# LJTrans.AccountNum =PwC_Import.Account;
# LJTrans.AmountCurDebit =PwC_Import.Debit;
# LJTrans.AmountCurCredit =PwC_Import.Credit;
# LJTrans.OffsetAccount =PwC_Import.OffsetAccount;
# LJTrans.Txt =PwC_Import.TransTxt;
# LJTrans.Dimension =PwC_Import.Dimensions;
# LJTrans.insert();
# }
# }
# ttscommit;
# endlengthyoperation();
# Info("Journal "+LJTable.JournalNum + " created successfully for Import");
# CreateJournal.enabled(false);
# //===========Post Journal============
# if(PostJournal.value()==1)
# {
#
# LJCP=LedgerJournalCheckPost::newLedgerJournalTable(LJTable,NoYes::Yes);
# LJCP.run();
# Info("Posted journal "+LJTable.JournalNum);
# }
# //===================================
#}
ENDSOURCE
ENDMETHODS
ENDCONTROL
CONTROL COMMANDBUTTON
;==== controlId:12300, containerId:12298, containerdata:12298
;----------------------------------------------------------
PROPERTIES
Name #PrintTable
AutoDeclaration #Yes
Command #278
ENDPROPERTIES
ENDCONTROL
CONTROL COMMANDBUTTON
;==== controlId:12301, containerId:12298, containerdata:12298
;----------------------------------------------------------
PROPERTIES
Name #Exit
Command #268
ENDPROPERTIES
ENDCONTROL
ENDCONTAINER
ENDCONTROL
ENDCONTAINER
ENDCONTROL
ENDCONTAINER
ENDCONTROL
CONTROL GROUP
;==== controlId:12302, containerId:12290, containerdata:12290
;----------------------------------------------------------
PROPERTIES
Name #Line
Width #Column width
Height #Column height
FrameType #Edged 3D
ENDPROPERTIES
CONTAINER
CONTROL GRID
;==== controlId:12303, containerId:12302, containerdata:12302
;----------------------------------------------------------
PROPERTIES
Name #Grid
Width #Column width
Height #Column height
DataSource #PwC_Import
ENDPROPERTIES
CONTAINER
CONTROL CHECKBOX
;==== controlId:12304, containerId:12303, containerdata:12303
;----------------------------------------------------------
PROPERTIES
Name #PwC_Import_Marked
AllowEdit #No
BackgroundColor #Window background
LabelAlignment #Right
DataSource #PwC_Import
DataField #Marked
ENDPROPERTIES
ENDCONTROL
CONTROL DATEEDIT
;==== controlId:12305, containerId:12303, containerdata:12303
;----------------------------------------------------------
PROPERTIES
Name #PwC_Import_Transdate
AllowEdit #No
LabelAlignment #Right
DataSource #PwC_Import
DataField #Transdate
ENDPROPERTIES
ENDCONTROL
CONTROL STRINGEDIT
;==== controlId:12306, containerId:12303, containerdata:12303
;----------------------------------------------------------
PROPERTIES
Name #PwC_Import_Account
AllowEdit #No
LabelAlignment #Right
DataSource #PwC_Import
DataField #Account
ENDPROPERTIES
ENDCONTROL
CONTROL STRINGEDIT
;==== controlId:12307, containerId:12303, containerdata:12303
;----------------------------------------------------------
PROPERTIES
Name #PwC_Import_TransTxt
AllowEdit #No
LabelAlignment #Right
DataSource #PwC_Import
DataField #TransTxt
ENDPROPERTIES
ENDCONTROL
CONTROL REALEDIT
;==== controlId:12308, containerId:12303, containerdata:12303
;----------------------------------------------------------
PROPERTIES
Name #PwC_Import_Debit
AllowEdit #No
LabelAlignment #Right
DataSource #PwC_Import
DataField #Debit
MinNoOfDecimals #Auto
ENDPROPERTIES
ENDCONTROL
CONTROL REALEDIT
;==== controlId:12309, containerId:12303, containerdata:12303
;----------------------------------------------------------
PROPERTIES
Name #PwC_Import_Credit
AllowEdit #No
LabelAlignment #Right
DataSource #PwC_Import
DataField #Credit
MinNoOfDecimals #Auto
ENDPROPERTIES
ENDCONTROL
CONTROL STRINGEDIT
;==== controlId:12310, containerId:12303, containerdata:12303
;----------------------------------------------------------
PROPERTIES
Name #PwC_Import_OffsetAccount
AllowEdit #No
LabelAlignment #Right
DataSource #PwC_Import
DataField #OffsetAccount
&n