Import data from excel file

Create a class firstly :
// gl00mie, import data from Excel via ADO, 20061220 -->
// How to use this class:
// 1. Create class instance, specify filename and (optional) cursor type e.g.
//    doc = new ExcelImportADO(strFilename, #adOpenStatic);
// 2. Read names of worksheets present in the file, suppress error messages if necessary
//    con = doc.getExcelSheetNames();
// 3. Specify the name of the worksheet to open ADODB.Recordset on, otherwise the first one will be used
//    doc.excelSheetName(conpeek(con,2));
// 4. Open the file, i.e. open ADODB.Recordset (and ADODB.Connection if it's not opened yet)
//    suppress error messages if necessary and check for result:
//    if(doc.openFile(false)) ...
// 5. Check if there is a correct number of columns in the Recordset
//    if(doc.getFieldsCount() > 7) ...
// 6. Get the total record count if #adOpenStatic cursor type is used
//    cnRecords = doc.getRecordCount();
// 7. Read records in a loop, use indexes (1..n) or field names to identify fields
//    By default field values are returned as strings
//    int   i;
//    real  r;
//    str   s;
//    while(!doc.eof())
//    {
//        i = doc.getFieldValue(1, false);
//        s = doc.getFieldValue('stringField');
//        r = doc.getFieldValue('numericField', false);
//        doc.moveNext();
//    }
// 8. Cleanup - close ADODB.Recordset and ADODB.Connection
//    doc.finalize();
class ExcelImportADO
{
    COM         cnnExcel, rstExcel, flds;                       // ADO: Connection, Recordset, Fields
    str         strFileName, strSheetName;
    int         nCursorType;                                    // ADO: CursorTypeEnum
    container   conSheetNames;                                  // Excel Worksheets names

    // %1 - full file name
    #localmacro.ADODBExcelConnString
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + %1 + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
    #endmacro
    #CCADO
}
// gl00mie, import data from Excel via ADO, 20061220 <--


// take a variant value recieved via ADO and transform it from
// Excel-related types subset into a suitable type for Axapta
protected anytype adoValue(COMVariant _val, int _type)
{
    switch (_type)
    {
        // constants for types recognized by ADO for Excel
        case #adDouble:         return _val.double();
        case #adCurrency:       return _val.currency();
        case #adDate:           return _val.date();
        case #adBoolean:        return _val.boolean();
        case #adVarWChar,
             #adLongVarWChar:   return _val.bStr();
    }
    return '';
}


// take a variant value recieved via ADO and transform it from
// Excel-related types subset into a STRING value suitable for Axapta info-windows
protected str adoValueStr(COMVariant _val, int _type, boolean bDblRound = false)
{
  //  if(_type == 5)
  //  _type = 202;

    switch (_type)
    {
        case #adDouble:         return num2str(_val.double(),-1,(bDblRound ? 0 : -1),1,0);
        case #adCurrency:       return num2str(_val.currency(),-1,-1,1,0);
        case #adDate:           return date2str(_val.date(),123,2,2,2,2,4);
        case #adBoolean:        return int2str(_val.boolean());
        case #adVarWChar,
             #adLongVarWChar:   return _val.bStr();
    }
    return '';
}


// return Recordset.EOF()
boolean eof()
{
    return (rstExcel ? rstExcel.EOF() : true);
}


// get Excel book first sheet's name or previously
// set sheet name to use as a table name in select
str excelSheetName(str _name = '')
{
    if(!prmisdefault(_name))
        strSheetName = _name;
    else
    if(!strSheetName)                   // have we been already called successfully?
        strSheetName = conpeek(this.getExcelSheetNames(), 1);
    return strSheetName;
}


// close ADO RecordSet and Connection
public void finalize()
{
    // RecordSet is closed automatically upon Connection closure
    if (cnnExcel && cnnExcel.State() != #adStateClosed)
        cnnExcel.Close();
}


// return ADO Connection object
// create and open the connection if necessary
public COM getConnection()
{
    if(!cnnExcel)
    {
        cnnExcel = new COM(@"ADODB.Connection");
        cnnExcel.connectionString(#ADODBExcelConnString(strFileName));
        cnnExcel.Open();
    }
    return cnnExcel;
}


// get Excel worksheet names
container getExcelSheetNames(boolean _suppressErrors = false)
{
    COM sheets, sheet;
    COM adoxCatalog = new COM(@"ADOX.Catalog");
    int cnSheets, i;
    int nLines;
    str strSheet;
    ;
    if(conSheetNames)
        return conSheetNames;
    try
    {
        nLines   = infolog.line();                      // save current infolog level
        adoxCatalog.ActiveConnection(this.getConnection());
        sheets   = adoxCatalog.Tables();
        cnSheets = sheets.count();
        for(i=0; i < cnSheets; i++)
        {
            sheet    = sheets.item(i);
            strSheet = sheet.name();
            // strip '$' and anything beyond it (in case there are autofilters in the workbook)
            // also strip "'" for quoted names
            strSheet = strrem(substr(strSheet, 1, strscan(strSheet, '$', 1, strlen(strSheet))-1),'\'');
            if(!confind(conSheetNames, strSheet))
                conSheetNames += strSheet;
        }
    }
    catch (Exception::Error)
    {
        if (cnnExcel && cnnExcel.State() != #adStateClosed)
            cnnExcel.Close();
        cnnExcel = null;                                // release ADODB.Connection object
        if (_suppressErrors)
            infolog.clear(nLines);                      // delete infolog messages generated in try block
        else
            error("@SYS59927");
    }
    return conSheetNames;
}


// return Recordset.Fields.Count
int getFieldsCount()
{
    return (flds ? flds.Count() : -1);
}


// read _fldId-th field from the current ADO RecordSet
// and transform it into a type suitable for Axapta.
// _fldId can be an int index starting 1 or a column name
anytype getFieldValue(anytype _fldId, boolean _asString = true)
{
    COM fld;                                // ADO: Field
    ;
    setprefix(strfmt(@"%1::%2()", classId2Name(classIdGet(this)), funcname()));
    if(!flds)
        throw error(strfmt('%1 %2', "@SYS68420", @"Fields"));
    if(typeof(_fldId) == Types::Integer)
        _fldId--;                           // ADO uses indexes starting 0, not 1
    fld = flds.Item(_fldId);
    return (_asString ? this.adoValueStr(fld.Value(), fld.Type(), true) :
                        this.adoValue   (fld.Value(), fld.Type()));
}


// return record count for the opened RecordSet
// not valid for forward-only cursor or closed recordset
// MAN
// If the Recordset object supports approximate positioning or bookmarks - that is,
// Supports(adApproxPosition) or Supports(adBookmark), respectively, return True
// this value will be the exact number of records in the Recordset, regardless of
// whether it has been fully populated. If the Recordset object does not support
// approximate positioning, this property may be a significant drain on resources
// because all records will have to be retrieved and counted to return an accurate
// RecordCount value.
// NOTE: In ADO versions 2.8 and earlier, the SQLOLEDB provider fetches all records
// when a server-side cursor is used despite the fact that it returns True for both
// Supports (adApproxPosition) and Supports (adBookmark).
int getRecordCount()
{
    if(rstExcel && rstExcel.State() != #adStateClosed)
        return rstExcel.RecordCount();
    return -1;
}


// return ADO Recordset object
public COM getRecordset()
{
    return rstExcel;
}


// call Recordset.MoveNext()
void moveNext()
{
    if (rstExcel)
        rstExcel.MoveNext();
}


public void new(str _fileName, int _cursorType = #adOpenForwardOnly)
{
    rstExcel    = null;
    cnnExcel    = null;
    strFileName = _fileName;
    nCursorType = _cursorType;              // use #adOpenStatic to be able to read RecordCount
}


// try to open a sheet from an Excel file which name
// has been passed to new() or got by excelSheetName()
boolean openFile(boolean _suppressErrors = false)
{
    boolean ok = false;
    int     nLines;
    ;
    nLines = infolog.line();                            // save current infolog level
    try
    {
        if(this.excelSheetName() != '')
        {
            rstExcel    = new COM(@"ADODB.Recordset");
            rstExcel.Open(@"SELECT * FROM [" + strSheetName + @"$]", this.getConnection(), nCursorType);
            flds        = rstExcel.Fields();
            ok = true;
        }
    }
    catch  (Exception::Error)
    {
        if (cnnExcel && cnnExcel.State() != #adStateClosed)
            cnnExcel.Close();
        if (_suppressErrors)
            infolog.clear(nLines);                      // delete infolog messages generated in try block
    }
    return ok;
}



Job that will import data from excel file :
static void Import_Data(Args _args)
{
    ExcelImportADO          excelimport;
    ;
    excelimport = new ExcelImportADO('D:\\ImportData\\Student.xls');
    excelimport.openFile(false);
    while (!excelimport.eof())
      {
       info(excelimport.getFieldValue(2));
       excelimport.moveNext();
     }
}