How to export data to MS Excel via ADO.RecordSet

Updated: 13.05.2015

Here is an export example.

Job Tutorial_ADORecordSetData2Excel:
void Tutorial_ADORecordSetData2Excel(Args _args)
{
    NETI_ADORecordSetData rsDataOnServer;
    NETI_ADORecordSetData rsDataOnClient;
    COMExcelDocument_RU   excel;
    COM                   recordSet;
    COM                   document;
    COM                   workSheets;
    COM                   workSheet;
    COM                   range;
    MSOfficeBookMark_RU   bookMark;
    ;

    rsDataOnServer = new NETI_ADORecordSetData();

    rsDataOnServer.add([1, 'first', 1.23, NoYes::No, 01\01\2014]);
    rsDataOnServer.add([2, 'second', 4.56, NoYes::Yes, 02\01\2014]);

    rsDataOnClient = NETI_ADORecordSetData::create(rsDataOnServer.pack());

    recordSet = rsDataOnClient.getRecordSet(false); // _writeDefaultValues = false, default values won't be displayed.
                                                    // For example instead of 0 it would be displayed as empty cell.
    excel = new ComExcelDocument_RU();
    excel.newFile('', true);

    document = excel.getComDocument();
    workSheets = document.workSheets();
    workSheet = workSheets.item(1);

    bookMark = ComExcelDocument_RU::numToNameCell(1, 1);
    range = workSheet.range(bookMark);
    range.copyFromRecordset(recordSet);
}


Class NETI_ADORecordSetData:
   
ClassDeclaration:
public class NETI_ADORecordSetData
{
    #CCADO
    #define.Field('Field')

    Map       data;
    COM       recordSet;
    COM       rsFields;
    container conFieldsType;
    int       recordNum;
    boolean   writeDefaultValues;
}   
   
public void add(container _conValues)
{;
    this.validateFieldsType(_conValues);

    this.insertData(_conValues);
}

private void appendFields()
{
    int i;
    int fieldType;
    ;

    for (i = 1; i <= conLen(conFieldsType); i++)
    {
        fieldType = conPeek(conFieldsType, i);

        switch (fieldType)
        {
            case Types::String  : rsFields.append(this.fieldName(i), #adBSTR,    0, #adFldMayBeNull); break;
            case Types::Enum    : rsFields.append(this.fieldName(i), #adInteger, 0, #adFldMayBeNull); break;
            case Types::Integer : rsFields.append(this.fieldName(i), #adInteger, 0, #adFldMayBeNull); break;
            case Types::Int64   : rsFields.append(this.fieldName(i), #adBigInt,  0, #adFldMayBeNull); break;
            case Types::Real    : rsFields.append(this.fieldName(i), #adDouble,  0, #adFldMayBeNull); break;
            case Types::Date    : rsFields.append(this.fieldName(i), #adDBDate,  0, #adFldMayBeNull); break;

            default : throw error(strFmt("Field creation error. Data type in field %1 is not supported!", i));
        }
    }
}

private COMVariant createValue(int _fieldIdx, anyType _value)
{
    int        fieldType = conPeek(conFieldsType, _fieldIdx);
    anyType    defaultValue = defaultValue_RU(fieldType);
    COMVariant ret;
    ;

    switch (fieldType)
    {
        case Types::String  : ret = COMVariant::createFromStr(_value);   break;
        case Types::Enum    : ret = COMVariant::createFromInt(_value);   break;
        case Types::Integer : ret = COMVariant::createFromInt(_value);   break;
        case Types::Int64   : ret = COMVariant::createFromInt64(_value); break;
        case Types::Real    : ret = COMVariant::createFromReal(_value);  break;
        case Types::Date    : ret = COMVariant::createFromDate(_value);  break;

        default : throw error(strFmt("Value creation error. Data type in field %1 is not supported!", _fieldIdx));
    }

    if (!writeDefaultValues && _value == defaultValue)
    {
        ret = new COMVariant();
        ret.variantType(COMVariantType::VT_NULL);
    }

    return ret;
}

private str fieldName(int _fieldIdx)
{;
    return #Field + int2str(_fieldIdx);
}

public COM getRecordSet(boolean _writeDefaultValues = false)
{
    MapEnumerator me = data.getEnumerator();
    COM           rsField;
    container     conValues;
    int           i;
    ;

    writeDefaultValues = _writeDefaultValues;

    this.newRecordSet();

    this.appendFields();

    while (me.moveNext())
    {
        recordNum = me.currentKey();
        conValues = me.currentValue();

        if (recordSet.state() == #adStateClosed)
            recordSet.open();

        recordSet.addNew();

        for (i = 1; i <= conLen(conValues); i++)
        {
            rsField = rsFields.item(this.fieldName(i));
            rsField.value(this.createValue(i, conPeek(conValues, i)));
        }

        recordSet.update();
    }

    return recordSet;
}

private void insertData(container _conValues)
{;
    recordNum++;

    data.insert(recordNum, _conValues);
}

public void new()
{;
    data = new Map(Types::Integer, Types::Container);
}

private void newRecordSet()
{;
    if (recordSet)
    {
        recordSet.close();
        recordSet = null;
        rsFields = null;
    }

    recordSet = new COM('ADODB.RecordSet');
    rsFields = recordSet.fields();
}

public container pack()
{;
    return [conFieldsType, data.pack()];
}

public void unpack(container _packedData)
{
    container conData;
    ;

    [conFieldsType, conData] = _packedData;

    data = Map::create(conData);
}

private void validateFieldsType(container _conValues)
{
    int fieldIdx;
    int fieldType;
    ;

    if (!conFieldsType)
    {
        for (fieldIdx = 1; fieldIdx <= conLen(_conValues); fieldIdx++)
        {
            fieldType = typeOf(conPeek(_conValues, fieldIdx));

            switch (fieldType)
            {
                case Types::String,
                     Types::Enum,
                     Types::Integer,
                     Types::Int64,
                     Types::Real,
                     Types::Date : conFieldsType += fieldType; break;

                default : throw error("Field type validation error. Data type is not supported!");
            }
        }
    }

    if (conLen(conFieldsType) != conLen(_conValues))
        throw error(strFmt("Fields count doesn't match values count in line %1!", recordNum + 1));

    for (fieldIdx = 1; fieldIdx <= conLen(_conValues); fieldIdx++)
    {
        if (typeOf(conPeek(_conValues, fieldIdx)) != conPeek(conFieldsType, fieldIdx))
            throw error(strFmt("Data type is wrong. Line %1, field %2!", recordNum + 1, fieldIdx));
    }
}

public static NETI_ADORecordSetData create(container _packedData)
{
    NETI_ADORecordSetData instance = new NETI_ADORecordSetData();
    ;

    instance.unpack(_packedData);

    return instance;
}
  

 

Other blog posts

24.05.2018
Disclaimer
08.05.2018
We apply OKR methodology in Neti since 2014. We started from a quarterly planning, but then understood that no ambitious goals can be achieved per quarter. So we switched to semi-annual intervals....
11.04.2018
As you know, Microsoft recently has launched Dynamics 365 Business Central. And we are proud to announce we have launched intracompany training for sales and development departments:Why do we need it...

Subscribe for blog updates

Get updated with our blog new articles via your email!


Subscribe now