calc: increased column / row limit.

[Michael Meeks <michael.meeks@xxxxxxxxxxxxx>]

* 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@xxxxxxxxxxxxx <><, GM Collabora Productivity
Hangout: mejmeeks@xxxxxxxxx, Skype: mmeeks
(M) +44 7795 666 147 - timezone usually UK / Europe
_______________________________________________
LibreOffice mailing list
LibreOffice@xxxxxxxxxxxxxxxxxxxxx
https://lists.freedesktop.org/mailman/listinfo/libreoffice