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

11.01.2018
How small business shall work with major contracts. Opinion of Ruslan Kafiatullin from Neti Projects from scratch are always riskyAs a rule, implementation projects from scratch are always...
14.11.2017
Why outsourcing company is more effective than In-house IT-specialists There are situations when in-house specialists spend most of their time awaiting requests or they cannot get their work...
26.09.2017
Microsoft Dynamics AX program decisions are constantly improving. At first, innovations come to European companies, and only after that to Russian enterprises.We aspire to be one step ahead in this...

Subscribe for blog updates

Get updated with our blog new articles via your email!


Subscribe now