An example on using OLE or DDE

Question

I have never used OLE controls nor DDE servers or clients, so my experience on this matter is null.

I am exporting data from datasets or data grids creating CSV files, which MS Excel understands and open them like they were Excel sheets.

I am not able to merge cells nor set colors to any cell or region of the sheet and save the sheet as a real Excel file, and I would like to do that.

I have searched for help on those components, OLE/DDE, but I did not find any example that could help me to get my task done.

Can anybody help me on that or point me any example on the Internet ?

I have found a code in a Russian site here:

    /* ###########################################################
   ## SgGeracao is a TStringGrid object                     ##
   ########################################################### */
const int maxRowsInXL = SgGeracao->RowCount + 4; //  65535 (XL 2002-2003) ;1048576 (XL 2007)
Variant Cell1, Cell2, fRange;
String aFile = "E:\\V2\\Win32\\Debug\\GeracaoExport.xls";

try
{
    Variant XLApp = CreateOleObject("Excel.Application");
    // Hide Excel
    XLApp.OlePropertySet("Visible", false);
    // Add new Workbook
    XLApp.OlePropertyGet("WorkBooks").OleFunction("Add", 1);
    Variant wbk = XLApp.OlePropertyGet("Workbooks").OlePropertyGet("Item", 1);

    Variant Sheet =  wbk.OlePropertyGet("Worksheets").OlePropertyGet("Item", 1);

    //Sheet.OlePropertySet("Name", "Export"); // Raise exception

    int currStart = 0;
    
    while( currStart <= SgGeracao->RowCount)
    {

        int bounds[4] = {0, maxRowsInXL, 0 , SgGeracao->ColCount };
        Variant Array = VarArrayCreate(bounds, 3, varVariant);
        // Fill the array with data
        Array.PutElement("Titulo do Relatório",0,0);
        for(int i = 0; i < min(SgGeracao->RowCount, maxRowsInXL); i++)
        {
            for(int j = 0; j< SgGeracao->ColCount; j++)
            {
                Array.PutElement(SgGeracao->Cells[j][currStart + i], i+2, j );
            }
        }

        // Set values to the range
        Cell1 = Sheet.OlePropertyGet("Cells", 1, 1);
        Cell2 = Sheet.OlePropertyGet("Cells", maxRowsInXL + 1, SgGeracao->ColCount + 1);
        fRange = Sheet.OlePropertyGet("Range", Cell1, Cell2);
        fRange.OlePropertySet("Value", Array);

        currStart += maxRowsInXL;

    }

    XLApp.OlePropertySet("DisplayAlerts",false);
    //XLApp.OlePropertySet("Visible", true);
    wbk.OleProcedure("SaveAs", aFile.c_str());
    XLApp.OleProcedure("Quit");
}
catch(Exception &E)
{
    ShowMessage(E.Message);
}

The code works fine, but I have not succeeded on save to an Excel file, nor setting the cells background colors and the number formats.

Some examples on Delphi show how, but the code seems not to work.

I'd appreciate some help on this matter

Thank you very much.

Jayme Jeffman