;; Sri Ganeshji : Sri Balaji : Sri Pitreshwarji : Sri Durgaji : Sri Venkateshwara

/*
Objective(s)  
- 	This code demonstrates how Excel File can be opened to perform Read and Write Operations.
*/

;;Function will be called from gate way of Tally to read and write in excel sheet
[#Menu: Gateway of Tally]
    
	Add	: Key Item	: Before : @@locQuit	: File Creation - Excel : X : CALL		: TSPL Smp Write Ledgers to Excel
	Add	: Key Item	: Before : @@locQuit	: File Read - Excel 	: R : Call 		: TSPL Smp Read Ledgers from Excel
    
;;Function to write in excell sheet
[Function: TSPL Smp Write Ledgers to Excel]

	Variable	: RowIndex 	: Number	: 0      ;;variable to set row index
	Variable	: ColIndex 	: Number             ;;variable to set column index
	Variable    : Temp 		: String	: ""

	10	: OPEN FILE			: "ChartofAccounts.xlsx" 		: Excel : Write   ;;open excel "ChartofAccounts.xlsx" to write, if not present the will create it.

	30  : WALK COLLECTION 	: TSPL Smp Ledger Info
	40  : 	IF : $$IsEmpty:##Temp OR $Parent != ##Temp
	50  : 		INCREMENT 	: RowIndex
	60  :   	SET 		: ColIndex 		: 1
	70  :   	WRITE CELL  : ##RowIndex 	: ##ColIndex	: $Parent
	80  :   	INCREMENT  	: ColIndex
	90  : 		WRITE CELL	: ##RowIndex 	: ##ColIndex 	: $Name
	100 : 	ELSE:
	110 :  		INCREMENT  	: ColIndex 
	120 :  		WRITE CELL 	: ##RowIndex 	: ##ColIndex 	: $Name
	130 : 	END IF
	140 : 	SET 			: Temp 			: $Parent
	150 : END WALK
	160 : CLOSE TARGET FILE
	170	: EXEC COMMAND		: Excel			: "ChartofAccounts.xlsx"

;;Collection of all the groups and ledger of a company
[Collection: TSPL Smp Ledger Info]

;;Syntax for loop collections
	Collection	: TSPL Smp LedgerColl 		: TSPL Smp Group Coll

;;Collecction of all the ledgers
[Collection: TSPL Smp Ledger Coll]

	Type		: Ledger
	Child of 	: $Name
	Fetch 		: Name, Parent
	
;;Collection of all the accounting groups
[Collection: TSPL Smp GroupColl]

	Type 		: Group
	Child of 	: ""
	Belongs To 	: Yes


;; Function to Read from Excel File
[Function: TSPL Smp Read Ledgers from Excel]

	Variable	: RowIndex 	: Number	: 1   ;;Variable to set row index to 1
	Variable	: ColIndex 	: Number	: 1   ;;Variable to set column index to 1

	10	: OPEN FILE		: "ChartofAccounts.xlsx" : Excel : Read      ;;Open excel file "ChartofAccounts.xlsx" to read
	20	: WHILE			: NOT $$IsEmpty:($$FileReadCell:##RowIndex:1)   ;;if the row is not empty then read
	40	: 	LOG			: $$FileReadCell:##RowIndex:1  ;;Inbuild function $$FileReadCell will read the row indexed 1 value and log it
	50	: 	LOG			: $$FileReadCell:##RowIndex:2  ;;Inbuild function $$FileReadCell will read the row indexed 2 value and log it
	60	: 	INCREMENT	: RowIndex
	70	: END WHILE
	80	: EXEC COMMAND	: "TDLFunc.LOG"   ;;Action "EXEC COMMAND" will display the output after reading

;; End-of-File
