anchorPane location pain #34
Description
I'm having a blast with version 3. My business users have been clamoring for multi-row headers and merged cell banners among other things and now it is possible. I see the code. Lots of hard work went into the release and I'm grateful.
I do not have an answer for this. I'm just sharing the pain for your consideration.
I understand and use the absolute location within the sheet to set the active Pane for freezing rows and columns. The fact that Excel makes you think about the upper left corner of the area that is allowed to scroll is backwards from thinking about what rows and columns are frozen. Everybody stumbles on that at first.
When writing code for this though, we are thinking in terms of the query. If I want to freeze the header row and 2 left most columns I need to figure out where the top left cell of the data is within the sheet. When we have the query column headers, the data starts on row 2. So as per your example, A2 freezes the top row but does not freeze any columns. C2 freezes the top (header) row and columns A and B. We implement this with makeCellRef(p_colIdx=>3,p_rowIdx=>2).
That's bad enough. When you throw in that the table may be offset within the sheet (like when I shift the query table down to insert more column header rows), it gets much worse. The fact that we are dealing with two different coordinate systems, one of which is 0 based and the other 1 based is an "I need to get out a piece of paper and draw this" gob-stopper. I've written a helper procedure for my immediate use case. It is messy and I'm not happy with it. Perhaps a version of setting the sheet anchor from the coordinate system of a tableId would be helpful. I want one that lets me use the coordinates of what I want to freeze, but that may be straying too far from what Excel does.
I can and will write my own utility to do what I want, but if I'm having this trouble, I expect others will as well. Something to consider for what I think will be a common use case of multi-row column headers.