TsWorksheetGrid and others

[straetch]

A few observations/anomalies:
1. Only one WorksheetGrid can de defined in one WorkbookTabControl. So all worksheets of the workbook use the same WorksheetGrid. I have a workbook with "working" data in one worksheet (tab1) and a second worksheet (tab2) that contains metadata (user comments field, help hints, spreadsheet history, etc.). These must be part of the same workbook as these will be maintained in one file.
The formatting of both sheets is completely different (e.g. in tab1 I use autorowheight, in tab2 I use fixed rowheights for the same row). I think it would be better to provide the possibility to define several different worksheetgrids in the same workbooktabcontrol, each belonging to a corresponding worksheet in the same workbook.
2. If I set the option goRowselect in the WorksheetGrid, then the CellIndicator shows also the range of selected cells in the row of the grid. Setting NumbersOnly in the CellIndicator does not change this.
3. If I set the option goRowselect in the WorksheetGrid, then the selected range in the row excludes the column that was set as fixed column in the input file. I assume that column is set as "frozen" when the file is loaded.
4. ColCount cannot be set before FrozenRows.
5. Opening a defect .xls file results in a freeze, although the operation is embedded in a try .. except block.

[wp]

(1) - Multiple grids in same TabControl
Many users do not understand the connection between the worksheet/workbook and the grid. Being used to TStringGrid they think that the data shown in the grid are part of the grid. This is not true here. The grid is just a visual container, all data, all formatting is stored in the invisible workbook/worksheet in the background. So, when you change anything in the grid it will be passed through to the worksheet transparently. Since you can visually work only in one grid it is perfectly logical to provide only a single grid; if you want to go to another sheet you click its tab in the TabControl and it will transparently load the associated worksheet into the grid, the contents and formats of the previously shown sheet are not lost because they are stored in the workbook. There is really no need for several grids inside the same TabControl.

If something is lost when changing worksheets in the Tabcontrol then it is a bug, and you should report it (with detailed instructions and a demo project to show the bug).

(2) - RowSelect - CellIndicator shows range of selected cells.
That's the way it should be. The cell indicator shows the coordinates of the focused cell and - in case of several selected cells - how many cells are selected. Since RowSelect is on the latter case is true. Please note that the focussed cells is not visually enhanced in case of RowSelect. Therefore I do not recommend this operation. The way to select a row by clicking on a row header like in the large Office applications is not implemented yet.

(2) - NumbersOnly in CellIndicator
This should not be there, it makes no sense. The reason why it is there is that the CellIndicator inherits from TEdit, it should inherited from TCustomEdit and should not publish those properties which do not apply to this special edit control. I'll fix that.

(3) - RowSelect and FrozenCols
That's the way it is... The "FrozenCols" are nothing else but custom-painted FixedCells of the TCustomGrid from which the WorksheetGrid inherits. Therefore, you cannot edit frozen cells (which is bad), and they cannot be selected (which is bad as wll). The latter is the reason why the RowSelect option leaves the FrozenCols unselected.

(4) - ColCount & FrozenCols
Please give details instructions what you are doing. It is working correctly for me.

(5) - Defective xls
This should not happen, of course. But, on the other hand, the internal structure of xls is quite complex, and much of the details has been revealed by reverse engineering, thus there is a good chance that it will happen, sorry. Try to remove everything which is not needed from this file and post it here so that I can have a look.

A try-except block will no help because it requires a known error situation, but your problem is an unknown error.

[straetch]

Many thanks for your quick reaction.
Here some further considerations:
1. I use the WorksheetGrid to guide the user to input the data. For instance I highlight the cells that he/she should fill in. This means I manipulate the content of the grid on a temporary basis. Only at the end, the data changes are made permanent when the spreadsheet is saved. Programmatical changes to the content are made directly on the worksheet, some temporary visual changes are made on the worksheetgrid and will be discarded when the spreadsheet is saved - that was my understanding. This was based on the fact that several (visual) WorksheetGrid changes have no direct equivalent on the worksheet level.
2. RowSelect.
Let me explain what I'm doing here.
Objects from our archive are described in the worksheet row by row.
The worksheet contains a unique identification number for each object. With a selected object ( = selected row) I associate a number of files to that object (FormDropFiles). The file names get a prefix with the id number. So I want the user to see clearly what object he/she is working with. Therefore I wanted to highlight the whole selected row. At the same time, the column with the id number is fixed/frozen to remain in view when scrolling.
I know, I expect more from fpspreadsheet then a simulation of a spreadsheet program.
Except from this aspect (which is maybe due to my misunderstanding), fpspreadsheet does a fantastic job!
3. Defective xls
See attached file

[wp]

1. [...]This means I manipulate the content of the grid on a temporary basis. Only at the end, the data changes are made permanent when the spreadsheet is saved. Programmatical changes to the content are made directly on the worksheet, some temporary visual changes are made on the worksheetgrid and will be discarded when the spreadsheet is saved - that was my understanding. This was based on the fact that several (visual) WorksheetGrid changes have no direct equivalent on the worksheet level.

As I wrote this is not correct. The WorkbookSource - which is used by the grid internally and which exists automatically even if you don't create one - is a link between the workbook and the visual components (grid, cellEdit, cellIndicator, actions, etc). It establishes a notification mechanism which tells the workbook that something has been changed in the visual controls and requests the workbook to store the changes made. Similarly if you change something programmatically in the workbook/worksheet then the workbooksource is notified which in turn tells the visual controls to update any data needed from the underlying workbook.

2. RowSelect [...] Objects from our archive are described in the worksheet row by row.
The worksheet contains a unique identification number for each object. With a selected object ( = selected row) I associate a number of files to that object (FormDropFiles). The file names get a prefix with the id number. So I want the user to see clearly what object he/she is working with. Therefore I wanted to highlight the whole selected row. At the same time, the column with the id number is fixed/frozen to remain in view when scrolling.

You are speaking of "archive" - this automatically implies "database" to me. Is there any special reason why you establish a database application using a spreadsheet? I know that Microsoft and others are advocating usage of spreadsheets for database stuff, but to me this is not a good solution. With a real database you have much more possibilities for seaching, combining and evaluating data. Also, for data input, it is very easy to create simple forms, almost without writing any code, in which you can guide the user to enter data correctly without destroying anything.

3. Defective xls

The file you uploaded is an xslx file with extension .xls. After giving it the extension .xlsx fpspreadsheet can read it without any issues. The problem, however, is that fpspreadsheet, on my system, does not report an error, it just displays the file as an empty biff2 file (format of Excel 2) - I'll fix that.

[straetch]

1. I understand the principles. What put me on the wrong foot is the presence of, say, TsWorksheet.ReadHorAlignment/TsWorksheet.WriteHorAlignment and TsWorksheetGrid.HorAlignment (same for TsWorksheet.ReadWordwrap/ TsWorksheet.WriteWordwrap vs. TsWorksheetGrid.Wordwrap and others). Since both are present I assumed the worksheetgrid member would do something else than the corresponding worksheet member. Now I understand the net result is exactly the same, though handled internally through different paths.
2. You are correct, eventually the data is presented in a "real" database on our website.
The programs made with FP/fpspreadsheet are the front-end. In our historical circle we have five people who describe the objects independently. Each one is allocated an ID number range. Their work is done with a spreadsheet program, available on their PC and in a format they understand. These spreadsheets are merged in a "master" spreadsheet, which is then converted and published on our website. This all must be supported to maintain a consistent database. E.g. duplicate ID numbers (due to misunderstandings, typos) must be avoided. Another example is e.g. we have a statue. This gets a description with an ID number. But we also have photos of that statue. These pertain to the same object, hence must have the same ID number in our digital archive. And so on.
The long term solution is the migration to a professional collection handling system, such as opensource Collective Access. However, the learning curve for that is too steep for me, humble Pascal programmer.
3. Thanks for your diagnosis on the defective xls.

[wp]

In the current version the workbooksource should display an error message, if a file cannot be loaded.

[EDIT] ... and TsCellIndicator now publishes only properties needed (--> no more NumbersOnly)

[straetch]

Thanks!

[straetch]

A few clarifications.
1.

(3) - RowSelect and FrozenCols
That's the way it is... The "FrozenCols" are nothing else but custom-painted FixedCells of the TCustomGrid from which the WorksheetGrid inherits. Therefore, you cannot edit frozen cells (which is bad), and they cannot be selected (which is bad as wll). The latter is the reason why the RowSelect option leaves the FrozenCols unselected.

A user typically selects a row to delete it entirely or to copy/paste the entire row somewhere. Therefore he/she expects the entire row to be selected. Also frozen cells are involved in such operations. The anomaly is due to inheritage from TCustomGrid, I understand.
2. Changes to a WorksheetGrid are automatically propagated to the underlying Worksheet and vice-versa. So logically one would expect setting WorksheetGrid.RowCount would actually trim or expand the Worksheet to correspond to the WorksheetGrid. This would set Worksheet.GetLastRowIndex accordingly. It does not do it. The only effect seems to be setting the grid dimensions. If RowCount is set, then the same RowCount applies when a new tab is selected. Just curious.
3. Where in the documentation can I find the Worksheet.Cells members?

[wp]

A few clarifications.
1.

(3) - RowSelect and FrozenCols
That's the way it is... The "FrozenCols" are nothing else but custom-painted FixedCells of the TCustomGrid from which the WorksheetGrid inherits. Therefore, you cannot edit frozen cells (which is bad), and they cannot be selected (which is bad as wll). The latter is the reason why the RowSelect option leaves the FrozenCols unselected.

A user typically selects a row to delete it entirely or to copy/paste the entire row somewhere. Therefore he/she expects the entire row to be selected. Also frozen cells are involved in such operations. The anomaly is due to inheritage from TCustomGrid, I understand.

No, TCustomGrid is not guilty. The main problem is that fpspreadsheet does not support row and column selection in all required details.

2. Changes to a WorksheetGrid are automatically propagated to the underlying Worksheet and vice-versa. So logically one would expect setting WorksheetGrid.RowCount would actually trim or expand the Worksheet to correspond to the WorksheetGrid. This would set Worksheet.GetLastRowIndex accordingly. It does not do it.

The worksheet, per se, does not have a RowCount. Unlike the grid it can create cells anywhere within the physical limitations (Row index = Cardinal) and the limitations of the file formats. This is because the worksheet stores only pointers to existing cell, column and row records in tree datastructures. The grid, however, allocates a pointer for every cell within the RowCount x ColCount matrix, used or not.

3. Where in the documentation can I find the Worksheet.Cells members?

For historical reasons, the worksheet/workbook do not follow a strictly object-oriented path. The cells stored in the worksheet are pointers to a TCell record, the pointer is type PCell and is returned by many of the Read/Write methods of the worksheet -- see http://wiki.lazarus.freepascal.org/FPSpreadsheet#Cell. In addition, since fpc now supports advanced records, a pseudo-object-oriented look-and-feel for formatting properties can be obtained by "using" fpscell along with {$modeswitch advancedrecords} and {$mode delphi} which allows constructs like this:

Code: Pascal  [Select][+][-]

1. var
2.   worksheet: TsWorksheet;
3.   cell: PCell;
4. begin
5.   cell := worksheet.WriteNumber(3, 5, 3.141592);
6.   cell.HorAlignment := haCenter;

[straetch]

The worksheet, per se, does not have a RowCount. Unlike the grid it can create cells anywhere within the physical limitations (Row index = Cardinal) and the limitations of the file formats. This is because the worksheet stores only pointers to existing cell, column and row records in tree datastructures. The grid, however, allocates a pointer for every cell within the RowCount x ColCount matrix, used or not.

If WorksheetGrid.RowCount is set to a certain value and the grid is switched to a second worksheet by clicking on it's tab, then that RowCount value is also valid for the grid in that tab, hence also for the visualisation of the second worksheet. The user gets the impression that the worksheet is smaller than it really is (in case LastRowIndex of the second worksheet is larger than that WorksheetGrid.RowCount). In many (most?) cases this is not intended. A warning in the documentation may help. My own conclusion: stay away from setting WorksheetGrid.RowCount.

For historical reasons, the worksheet/workbook do not follow a strictly object-oriented path. The cells stored in the worksheet are pointers to a TCell record, the pointer is type PCell and is returned by many of the Read/Write methods of the worksheet -- see http://wiki.lazarus.freepascal.org/FPSpreadsheet#Cell. In addition, since fpc now supports advanced records, a pseudo-object-oriented look-and-feel for formatting properties can be obtained by "using" fpscell along with {$modeswitch advancedrecords} and {$mode delphi} which allows constructs like this:

Such constructs are beyond my comprehension. I was merely asking where in the documentation of fpspreadsheet I can find the description of Worksheet.Cells.GetLastCell and other Worksheet.Cells members (The only thing I find is: Cells = List of cells of the worksheet).

[wp]

If WorksheetGrid.RowCount is set to a certain value and the grid is switched to a second worksheet by clicking on it's tab, then that RowCount value is also valid for the grid in that tab, hence also for the visualisation of the second worksheet. The user gets the impression that the worksheet is smaller than it really is (in case LastRowIndex of the second worksheet is larger than that WorksheetGrid.RowCount). In many (most?) cases this is not intended. A warning in the documentation may help. My own conclusion: stay away from setting WorksheetGrid.RowCount.

Please provide a sample file or sample code to demonstrate this issue.

Such constructs are beyond my comprehension. I was merely asking where in the documentation of fpspreadsheet I can find the description of Worksheet.Cells.GetLastCell and other Worksheet.Cells members (The only thing I find is: Cells = List of cells of the worksheet).

Originally you were asking for "documentation of the Worksheet.Cells members". Please understand that I want to provide only the basic information in the wiki article which is already way too long. And, of course, you should know that fpspreadsheet comes with a chm help file built from the inline source code documentation (docs/fpspreadsheet-api.chm). Don't say that fpspreadsheet is poorly documented.

[straetch]

Please provide a sample file or sample code to demonstrate this issue.

I will prepare a small project to demonstrate the point and come back to you.

Originally you were asking for "documentation of the Worksheet.Cells members". Please understand that I want to provide only the basic information in the wiki article which is already way too long. And, of course, you should know that fpspreadsheet comes with a chm help file built from the inline source code documentation (docs/fpspreadsheet-api.chm). Don't say that fpspreadsheet is poorly documented.

On the contrary, fpspreadsheet is well documented. It is thanks to the wiki and the chm help file that I could achieve very good results with fpspreadsheet. I realise that extensive documentation is essential to give the application programmer access to the numerous  possibilities of fpspreadsheet. To me this is a lifesaver. In the other post (on empty cells from .ods files), you gave me  a good solution to solve the issue - I appreciate your prompt to-the-point reactions very much. In your solution you used methods such as Worksheet.Cells.GetLastCell. I want to learn more on these, so I digged in the wiki and the help file, but didn't find it (except for GetCol/Row/RangeEnumerator in the wiki). I fully understand not every nut and bold can be covered by even the best documentation. Therefore my question.

[straetch]

1. Attached a small project as promised. It demonstrates that WorksheetGrid.RowCount setting is lost when switching the active worksheet.
The behaviour of my application I mentioned before was due to my efforts to define a seperate RowCount for both grid tabs, which clearly does not work.
I have a workbook with two worksheets which each contains a (for the user) different significant area. The user should not enter any data outside of that area, and should not see it (similar to another recent post of another user).
Since the grid itself has no persistent memory (the grid is cleared and reloaded from the worksheet each time another worksheet becomes active), other solutions may be considered.
- Limit the worksheet itself to the significant area. The application knows where the boundaries are. The grid will follow the worksheet. I tried that, but there are many complications (see my earlier post).
- A generic solution whereby things such as RowCount are stored "with" the worksheet. Such solution seems not justified as there are numerous other cases imaginable beyond RowCount and ColCount. (Example: the application could work with different cell colors to guide the user in filling in the cells, but these temporary colors should not be part of the saved workbook - I considered this at one time).
- The application could take care of storing/restoring items such as RowCount only as needed. But this would require something like an On WorkbookTabControlActiveSwitch published event with parameters FromActiveWorksheetIndex and ToActiveWorksheetIndex.

2. I checked on Worksheet.Cells.GetLastCell and its siblings. The documentation is in the source, but seemingly not picked up by the help file generator. Am I correct?

[wp]

I am convinced now. The problem is that the grid blows up small dimensions to DEFAULT_ROW_COUNT (100) and DEFAULT_COL_COUNT (26) to get a more "Excel or Calc-like" appearance.

I added a new AutoExand mode, aeDefault, which is on by default and results in this behavior. But if it is off then the original worksheet dimensions are always retained in the grid. So, all you have to do is to remove aeDefault from the set AutoExpand of the grid, and the grid will use the correct size if several worksheets are toggled in the TabControl. The size of the grid is determined by the "last" cells of the worksheet(s).

If you want to add an empty column to the right of the data don't use the ColCount because its value is not stored in the worksheet and will be lost when the TabControl comes back to the sheet - like it is today. Instead, write a blank cell into this column to fool the worksheet's GetLastColumnIndex method.

Here is code for your modified demo as a further explanation:

Code: Pascal  [Select][+][-]

1. procedure TMainForm.FormShow(Sender: TObject);
2. var
3.   MyWorksheet1,MyWorksheet2: TsWorksheet;
4.   i: integer;
5. begin
6.   sWorksheetGrid1.AutoExpand := sWorksheetGrid1.AutoExpand - [aeDefault];
7.  
8.   // dummy workbook with worksheet "Sheet1" preloaded
9.   sWorkbookSource1.Workbook.AddWorksheet('Sheet2');
10.   MyWorksheet1 := sWorkbookSource1.Workbook.GetWorksheetByIndex(0);
11.   MyWorksheet2 := sWorkbookSource1.Workbook.GetWorksheetByIndex(1);
12.  
13.   // populate MyWorksheet1
14.   for i := 0 to 9 do
15.     MyWorksheet1.WriteNumber(i,0,i);
16.   MyWorksheet1.WriteBlank(9, 1);
17.  
18.   // populate MyWorksheet2
19.   for i := 0 to 14 do
20.     MyWorksheet2.WriteNumber(i,0,i);
21.   MyWorksheet2.WriteBlank(14, 2);
22.  
23.   sWorksheetGrid1.SelectSheetByIndex(0);  // show Sheet1 in grid
24. end;  

As for the help file: the chm generation script seems to be broken by my restructuring of the source folders some months ago. It is working again now. chm files were updated. Thanks for mentioning this.