DL: Tips And Tricks: ADO Database Reader (Jet way) - Excel Example - Part 2

Hello,
here goes next part of Excel reader using ADO. Now we will create Two functions identical by result to native functions from Navision 370 table (Excel Buffer).
Two functions were created:
SelectSheetName(filename);

Function reads Excel schema and shows up dialog with sheet names to select. Let's look at this code (SelectSheetName):

//Checking Filename
IF filename <> '' THEN BEGIN 
 
IF NOT EXISTS(filename) THEN
   
     
ERROR(Text001,filename);
 
END
  ELSE
  EXIT(''); 
//Creating ADO Connection
IF NOT CREATE(Connection) THEN  ERROR(Text002); 
SheetList:='';
SheetName:=''; 
//As described in previous article
Connection.ConnectionString:=DATASOURCE+filename;
Connection.Provider:=PROVIDERSTRING;
Connection.Open;
//Reading Table Schema to RecordSet – Value 20 means reading TABLESCHEMA
Recordset:=Connection.OpenSchema(20); 
//Reading Table Schema and sheet names  (looks similar to Excel Buffer Function) huh?
//Next steps are the very similar to SelectSheetName function of Excel Buffer and it‘s
//Easy to understand I hope
WHILE NOT Recordset.EOF DO
BEGIN 
 
Fields:=Recordset.Fields;
 
 
SheetName:=FORMAT(Fields.Item(2).Value);
  
  SheetName:=COPYSTR(SheetName,1,STRLEN(SheetName)-1); 
 
IF (SheetName <> '') AND (STRLEN(SheetList) + STRLEN(SheetName) < 250) THEN
    SheetList := SheetList + SheetName + ',';
  Recordset.MoveNext;
END; 
Recordset.Close;
Connection.Close;
CLEAR(Connection); 
OptionNo := STRMENU(SheetList,1);
IF OptionNo <> 0 THEN  EXIT(SELECTSTR(OptionNo,SheetList))
 
ELSE  EXIT('');

Now we must reorganize previous article function to read data to Excel Buffer (ReadSheet(filename,sheetname)):

 //Checking Filename
IF filename <> '' THEN  IF NOT EXISTS(filename) THEN  ERROR(Text001,filename); 
//Creating Connection
IF NOT CREATE(Connection) THEN  ERROR(Text002);
 //Now we‘re opening defined sheet
Connection.ConnectionString:=DATASOURCE+filename;
Connection.Provider:=PROVIDERSTRING;
Connection.Open();CREATE(Recordset);
Recordset.Open(STRSUBSTNO(SELECTSTRING,sheetname),Connection,1,1); 
//Now we are reading Excel SheetRowNo:=0;
WHILE NOT Recordset.EOF DO
BEGIN 
 
Fields:=Recordset.Fields;
 
 
ColumnNo:=0;
 
 
WHILE ColumnNo<>Fields.Count DO
  
  
BEGIN
   
    
Field:=Fields.Item(ColumnNo);
   
//Checking if value is null (if we are trying to read null value we will get error)   
    
IF FORMAT(Field.ActualSize)<>'0' THEN
   
    
BEGIN
   
//Inserting Retrieved data to ExcelBuffer Table     
      
ExcelBuffer.RESET;
     
      
ExcelBuffer.INIT;
      
       ExcelBuffer.VALIDATE("Column No.",ColumnNo+1);
      
       ExcelBuffer.VALIDATE("Row No.",RowNo+1);
     
       
ExcelBuffer.VALIDATE("Cell Value as Text",FORMAT(Field.Value));
     
       
ExcelBuffer.INSERT(TRUE);
   
    END;
   
ColumnNo+=1;
 
END;
 
RowNo+=1;
 
Recordset.MoveNext;
END;
Recordset.Close;
CLEAR(Recordset);
Connection.Close();
CLEAR(Connection);

So as You can see it's very simple to implement and it works very fast! Try for Your own.
Full source code is provided here: http://igor.beeone.googlepages.com/DLJET2a.zip

 

Comment List
Related
Recommended