/*****************************************************************************
*                                                                            *
*   XLGETCELL.SPL Copyright (C) 2024 DSP Development Corporation             *
*                               All Rights Reserved                          *
*                                                                            *
*   Author:      Randy Race                                                  *
*                                                                            *
*   Synopsis:    Returns a cell series from a range Excel data               *
*                                                                            *
*   Revisions:   31 Jan 2021  RRR  Creation                                  *
*                                                                            *
*****************************************************************************/


#if @HELP_XLGETCELL

    XLGETCELL

    Purpose: Returns a cell series from an Excel range via COM Automation

    Syntax:  XLGETCELL("rangestr", "bookname", sheet)

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

              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: A cell series or array

    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, XLGETCELL attempts to connect to
             the running instance of Excel, otherwise XLGETCELL connects
             to a new hidden instance of Excel. See XLSHOW to display the
             new instance of Excel.

             Numeric data is transferred as double precision values.

    See Also:
             Xlclear
             Xlget
             Xlinit
             Xlput
             Xlputcell
             Xlsave
             Xlsaveas
             Xlshow
#endif



/* get data from a range of Excel cells return as cell series */
xlgetcell(rangestr = "A1", bookname = "", sheet = (isstring(bookname)) ? strlen(bookname) > 0 : 0)
{
        local s, xl, ta, 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 = xlgetcell_getrange(xl, bookname, sheet, rangestr);

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

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

        nr = range.rows.count;
        nc = range.columns.count;

        s = cell(nr, nc);

        n = 1;

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

        setconf("activex_array_transpose", ta);

        return(s);
}



/* get a range object */
xlgetcell_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);
}