A Reliable mechanism to read data from Excel Sheets

The following code Loads the entire excel sheet into buffer and returns selected records into a List

[-] ANYTYPE ReadExcel(String SheetName,String keyColumn,String keyValue,String InputFilePath)

                    [ ]

                    [ ] LIST OF ANYTYPE TableBuffer

                    [ ] LIST OF ANYTYPE SelectedRecords

                    [ ] HSQL hSQL

                    [ ] HDATABASE hDB = NULL

                    [ ]

                    [ ] integer i

                    [ ]

                    [ ] String strName,strIgnore,strTable,strColumn,strDtype

                    [ ]

                    [ ]               

[ ] // Clearing the Buffer //

                    [-] for i=1 to listcount(TableBuffer)

                                        [ ] ListDelete(TableBuffer,1)

                                        [ ]

                    [ ] //Connect to the Excel Workbook using Excel DSN //

[ ]

                    [ ] hDB = DB_Connect ("DSN=Excel Files; DBQ={InputFilePath};READONLY=FALSE;UID=; PWD= ;")

                    [ ]

[ ]//Read the First Row (Table Header) to a List (SelectedRecords)

[ ]

                    [ ] hSQL = DB_Columns (hDB,null, null, "{SheetName}$", "%")

                    [ ]

                    [-] while(DB_FetchNext (hSQL, strIgnore, strIgnore, strTable, strColumn, strIgnore, strDtype)== TRUE)

                                        [ ] ListAppend(SelectedRecords,strColumn)

                    [ ]

                    [ ]//Copy the Table header to the TableBuffer//

                    [ ]

                    [ ] ListAppend(TableBuffer,SelectedRecords)

                    [ ]

                    [ ]//Copy the Entire content of the Table to the TableBuffer

                    [ ]

                    [ ] hSQL = DB_ExecuteSQL (hDB, "SELECT * FROM [{SheetName}$]")

                    [ ]

                    [-] while DB_FetchNext(hSQL,SelectedRecords)

                                        [ ] ListAppend(TableBuffer,SelectedRecords)

                    [ ]

                    [ ]

                    [ ] // Selects the Records from the entire sheet based on the KaeyValue and the KeyColumn

                    [ ]

                    [ ] LIST OF ANYTYPE Record

                    [ ] LIST OF ANYTYPE Temp

                    [ ]

                    [ ]//Copy the Table Header to the Temp

                    [ ] ListAppend(Temp,TableBuffer[1])

                    [ ]

                    [ ]// Find the column index (cursor) for the given column name

[ ] Integer Cursor=ListFind (TableBuffer[1],keyColumn)

[ ]

[ ]//Copy the Records from TableBuffer that have the given KeyValue at KeyColumn //

[ ]

                    [-] for each Record in TableBuffer

                                        [-] if(Record[Cursor]==keyValue)

                                                            [ ] ListAppend(Temp,Record)

[ ]

                    [ ] SelectedRecords=Temp

                    [ ]

                    [ ] DB_Disconnect(hDB)

                    [ ] return SelectedRecords

Pass the List returned by the above method as argument 'Table' to the below method, it'll read values from the selected records.

Note : The List returned by the above method has the Header Row included in it. So the actual record starts at '2' i.e., 'Table[1]' has the Header and the records starts at 'Table[2]'

[-] ANYTYPE ReadValue(LIST OF ANYTYPE Table,String ColumnName,Integer row optional)

                    [ ]

                    [ ] Integer column = ListFind (Table[1],ColumnName)

                    [ ]

                    [-] if(isNull(row))

                                        [ ] row=2

                    [ ]

                    [ ] return Table[row][column]

Thank you for viewing this Post, Will try to post the more optimized version of this component in my next post.