calc: increased column / row limit.

* Meeting @ FOSDEM hackfest: - rough minutes & code ptrs.

* Present:
        + Eike, Noel, Michael

* Issues are:
        + ODF
                + serializes A:A as A1:A1million
                + serializes 1:1 as A1:AMJ1
                + need to store it as 1:1 ...

* Because older versions fail:
        + immediately on the reference if cells are unused.
        + decided -> can't read.

* test older versions:
        + if we have data outside the range, and/or ...
          larger formulae - do these work ?

* wrap-around columns:
        + the last column can reference the first via wrap-around
        + completely different if you have more columns ...
        + have same problem importing Excel files ...
                + do we handle it somehow here (?)
                + we discard data so ...
        + how do we cope with loading old XLS files (?)

* concern:
        1. saving small files with A:A that may break in older versions.
                + less of an issue these days -> old (5.0+ versions have A:A)

        2. wrapping around the code:
                + can we detect that.
                + think only for named-ranges;
                  perhaps for conditional formatting
                => this works with wrap-from A1 to AMJ1
                        + despite invalid ref. displayed in browser.
                        + doesn't save - as REF# in XLSX
                => but not working for AMJ1 -> A1 wrap-around.
                + is it only from XLS ?
                + XFD1 -> most right-most column ...
                + Investigate how Excel solves the same problem.
                        + with a 'dimension ref=""'


* Tentative decisions.

=> Split & push Noel's patch as of now:
        + push the dynamic column creation magic now.
                + but keep the column limit to 1024
        + so it doesn't change anything (in theory).

=> switch to dynamic dimensions instead of MAXROW/MAXCOL ~everywhere.
        + anchor this on the document - as per Excel.
        + store a 'libo:max_range="A1:AMJ100000"' extended attribute
          on each ODF spreadsheet.

        => so MAXROW/MAXCOL becomes the real upper limit and/or
           defaults for new docs.
           + create new defines for default below:

        + on-load we need to set max_range to something:

        Type                     max_range
        ---------------------------------------------
        new-doc                  cols: 16k, rows: 1m
        XLS load - special[1]    cols: 256, rows: 64k
        XLSX load                as per new-doc.
        ODS load - special[1]    cols: 1k, rows: 1m
        ODS load - no names      as per new-doc.

        [1] -> has names with relative references
               optionally has no data outside the smaller range.

            -> warn in this case and point to UI options:

=> add UI options to enlarge sheet sizes for stuck / smaller sheets ...
        + investigate what Excel does upgrading XLS -> XLSX (?)

=> ideally discourage ctrl-right-arrow / down-arrow from using
   full range as Excel does - to avoid accidental use of cells
   outside the range.

=> for old releases:
        + would we bother patching / re-spinning 6.0 / 6.1
          to warn with more columns ? ... prolly not.

* misc. code ptrs.

sc/source/core/tool/compiler.cxx:

    virtual void makeRefStr( OUStringBuffer&   rBuf,
                     formula::FormulaGrammar::Grammar eGram,
                     const ScAddress& rPos,
                     const OUString& rErrRef, const std::vector<OUString>& rTabNames,
                     const ScComplexRefData& rRef,
                     bool bSingleRef,
                     bool bFromRangeName ) const override
        * Think Excel - stores with implicit range of 0,0 (Eike)

   We should be doing the wrapping:

// Wrap-adjust relative references of a RangeName to current position,
// don't call for other token arrays!
void ScCompiler::MoveRelWrap()

sc/source/filter/excel/excform8.cxx:

void ExcelToSc8::ExcRelToScRel8( sal_uInt16 nRow, sal_uInt16 nC, ScSingleRefData &rSRD, const bool 
bName )
                // relative column references wrap around
                nRelCol = static_cast<sal_Int16>(256 + static_cast<int>(nRelCol));
            if (nDiff < 0)
            {
                // relative row references wrap around
                nRelRow = 65536 + nRelRow;

        + Looks good - pre-wrapping / conversion but ...
                + can't do this for relative named ranges.


-- 
michael.meeks@collabora.com <><, GM Collabora Productivity
Hangout: mejmeeks@gmail.com, Skype: mmeeks
(M) +44 7795 666 147 - timezone usually UK / Europe