/*****************************************************************************
*                                                                            *
*   XLPUTCELL.SPL  Copyright (C) 2024 DSP Development Corporation            *
*                               All Rights Reserved                          *
*                                                                            *
*   Author:      Randy Race                                                  *
*                                                                            *
*   Synopsis:    Puts a cell series to an Excel range                        *
*                                                                            *
*   Revisions:   31 Jan 2024  RRR  Creation                                  *
*                                                                            *
*****************************************************************************/


#if @HELP_XLPUTCELL

    XLPUTCELL

    Purpose: Transfers a cell series to an Excel range via COM Automation

    Syntax:  XLPUTCELL("rangestr", value, "bookname", sheet)

              rangestr - a string, Excel input range, defaults to
                         "A1", the first cell

                value  - a cell series

              bookname - an optional string, Excel Workbook name, defaults to
                         "" (empty) indicating use current Workbook

                 sheet - an optional integer or string, Excel Sheet number,
                         or sheet name, defaults to 1, the first sheet if
                         bookname is specified, else the current sheet


    Returns: 1 if successful, else an error

    Example:
             icell = ["test", 5.2, "Another String"];

             xlputcell("A1", icell);

             ocell = xlgetcell("A1:A3");

             ocell[1] == "test"
             ocell[2] == 5.2
             ocell[3] == "Another String"

    Example:
             xlputcell("B1", xlgetcell("A1:A3"));

             Puts the first 3 cells of A1 into B1:B3.

    Remarks:
             If Excel is already running, XLPUTCELL attempts to connect to
             the running instance of Excel, otherwise XLPUTCELL connects
             to a new hidden instance of Excel. See XLSHOW to display the
             new instance of Excel.

             Numeric data is transferred as variants containing
             double precision or date/time values.

             Setting a range to an empty series {}, clears the range.

    See Also:
             Xlclear
             Xlget
             Xlinit
             Xlsave
             Xlsaveas
             Xlshow
#endif



/* transfer a cell series to a range of Excel cells */
xlputcell(rangestr = "", s = refwin(w0), bookname = "", sheet = (isstring(bookname)) ? strlen(bookname) > 0 : 0)
{
        local xl, sheetlen, cv, ta, range, j, k, n, nr, nc;

        if (not(isstring(rangestr)))
        {
                error(sprintf("%s - input range string required", __FUNC__));
        }

        if (not(isstring(bookname)))
        {
                error(sprintf("%s - bookname must be a string or empty string", __FUNC__));
        }

        if (not(isscalar(sheet) || isstring(sheet)))
        {
                error(sprintf("%s - sheet must be a string or integer", __FUNC__));
        }

        /* connect to Excel if necessary */
        xl = xlconnect();

        /* check input range */
        range = xlputcell_getrange(xl, bookname, sheet, rangestr);

        if (not(isobject(range)))
        {
                error(sprintf("%s - invalid range: %s", __FUNC__, rangestr));
        }

        range = {};

        /* cast to variants so we can transfer date/time values */
        cv = getconf("activex_castvariant");
        setconf("activex_castvariant", "1");

        /* do not transpose */
        ta = getconf("activex_array_transpose");
        setconf("activex_array_transpose", "0");

        /* format range string */
        rangestr = xlput_autorange(xl, rangestr, s);

        /* get output range */
        range = xlputcell_getrange(xl, bookname, sheet, rangestr);

        if (not(isobject(range)))
        {
                setconf("activex_castvariant", cv);
                setconf("activex_array_transpose", ta);
                error(sprintf("%s - invalid range: %s", __FUNC__, rangestr));
        }

        n = 1;

        /* set value or clear */
        if (length(s) > 0)
        {
                nr = range.rows.count;
                nc = range.columns.count;

                loop(j = 1..nc)
                {
                        loop(k = 1..nr)
                        {
                                range.cells(k, j).value = s[n];
                                n++;
                        }
                }
        }
        else
        {
                range.clear();
        }

        /* restore state */
        setconf("activex_castvariant", cv);
        setconf("activex_array_transpose", ta);

        return(1);
}


/* build A1 range based on start cell and series size */
xlput_autorange(xl, rangestr, s)
{
        local end, svar, evar, nr, nc;

        if (isarray(s))
        {
                /* find ending range */
                end = strget(2, rangestr, ":");

                if (strlen(end) == 0)
                {
                        /* no end range specified - calculate */
                        svar = strget(1, rangestr, ":");

                        (nr, nc) = size(s);

                        if (nr > 0)
                        {
                                if (iscomplex(s))
                                {
                                        nc *= 2;
                                }

                                evar     = xlput_endvar(xl, svar, nr, nc);
                                rangestr = sprintf("%s:%s", svar, evar);
                        }
                }
        }

        return(rangestr);
}


/* get ending A1 cell reference */
xlput_endvar(xl, svar, nr, nc)
{
        local f1, r, c, evar;

        /* use Excel to convert A1 to R1C1 reference string */
        f1 = xl.ConvertFormula(svar, 1, -4150, 1);

        r = castint(numstr(strextract(f1, strlen(strfind("R", strrev(f1))), -1)));
        c = castint(numstr(strfind("C", f1)));

        /* increment R1C1 and convert to A1 form */
        evar = sprintf("R%dC%d", r + nr - 1, c + nc - 1);
        evar = xl.ConvertFormula(evar, -4150, 1, 4);

        return(evar);
}


/* get a range object */
xlputcell_getrange(xl, bookname, sheet, rangestr)
{
        local sheetlen, xlrange = 0;

        /* see if sheet is a string or numeric */
        if (isstring(sheet))
        {
                sheetlen = strlen(sheet);
        }
        else
        {
                sheetlen = sheet;
        }

        /* now select the range */
        if (strlen(bookname) > 1)
        {
                /* use specified workbook and worksheet */
                xlrange = xl.workbooks(bookname).worksheets(sheet).range(rangestr);
        }
        else
        {
                /* use current workbook */
                if (sheetlen > 0)
                {
                        /* use specified sheet */
                        xlrange = xl.worksheets(sheet).range(rangestr);
                }
                else
                {
                        /* use current sheet */
                        xlrange = xl.range(rangestr);
                }
        }

        return(xlrange);
}