* * *

Author Topic: FPSpreadsheet knowing when a cell is in a merge  (Read 604 times)

daveinhull

  • Jr. Member
  • **
  • Posts: 73
FPSpreadsheet knowing when a cell is in a merge
« on: October 07, 2017, 10:50:16 am »
Hi,

Is there a way to know when a cell is part of a merged set of cells?

Also, can anyone point me to the documentation behind FPSpreadsheet

Thanks
Dave
Version #:1.6.4 Date 2017/02/17 FPC Version: 3.0.2 and SVN Revision 54278 for i386-win32-win32/win64

wp

  • Hero Member
  • *****
  • Posts: 3951
Re: FPSpreadsheet knowing when a cell is in a merge
« Reply #1 on: October 07, 2017, 11:01:57 am »
Is there a way to know when a cell is part of a merged set of cells?
Code: Pascal  [Select]
  1. uses
  2.   fpsutils;  // for GetCellString()
  3. var
  4.   cell: PCell;
  5. begin
  6.   cell := Worksheet.FindCell(ARow, ACol);
  7.   if Worksheet.IsMerged(cell) then
  8.     ShowMessage('The cell ' + GetCellString(ARow, ACol) + ' is part of a merged block.');

Also, can anyone point me to the documentation behind FPSpreadsheet
chm help files are contained in the folder "docs" of your fpspreadsheet installation. The fpspreadsheet-api.chm is generated from the in-code comments, the fpspreadsheet-wiki.chm is a copy of the "historic" version of the related wiki articles matching your fpspreadsheet version. The most basic wiki article is http://wiki.lazarus.freepascal.org/FPSpreadsheet (valid for the current trunk version), it links to other related articles.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

daveinhull

  • Jr. Member
  • **
  • Posts: 73
Re: FPSpreadsheet knowing when a cell is in a merge
« Reply #2 on: October 07, 2017, 03:20:15 pm »
Hi wp,

Many thanks for your time and information.

Dave
Version #:1.6.4 Date 2017/02/17 FPC Version: 3.0.2 and SVN Revision 54278 for i386-win32-win32/win64

daveinhull

  • Jr. Member
  • **
  • Posts: 73
Re: FPSpreadsheet knowing when a cell is in a merge
« Reply #3 on: October 07, 2017, 03:35:26 pm »
Hi wp,

OK, I must have got something funder mentally wrong because I can't find .FindCell or .IsMerged.
Could I have an old version of FPSpreadsheet, I've got installed from Online Package Manager V1.8.2.0

Thanks
Dave
Version #:1.6.4 Date 2017/02/17 FPC Version: 3.0.2 and SVN Revision 54278 for i386-win32-win32/win64

wp

  • Hero Member
  • *****
  • Posts: 3951
Re: FPSpreadsheet knowing when a cell is in a merge
« Reply #4 on: October 07, 2017, 04:56:44 pm »
Could I have an old version of FPSpreadsheet, I've got installed from Online Package Manager V1.8.2.0

V1.8.2 by OPM? This should not be - I just checked it, the Online Package manager shoiuld deliver 1.8.4 (or maybe I made yet another stupid error...)

But that's not your problem. Did you add fpspreadsheet to "uses"? Most fpspreadsheet programs need this unit, as well as fptypes and - maybe - fpsutils.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

daveinhull

  • Jr. Member
  • **
  • Posts: 73
Re: FPSpreadsheet knowing when a cell is in a merge
« Reply #5 on: October 07, 2017, 06:11:19 pm »
Hi wp,

I only loaded FPSpreadsheet the other day and I went back to the Online Package Manger and it said there was a new one which was 1.8.4 so I installed this, but as you say that is not my problem.

I've loaded a TsWorkbookSource and a TsWorkbookGrid and I can put stuff in the grid using Grid.Cells [y,x] := etc.
I worked out that I can reference the .IsMegered and .FindCell by referecing Workbook.Worksheet,FindCell (x,y) etc.

Workbook is of type TsWorkbookSource which is what I'd expect, but what is Worksheet if its not the WorkbookGrid? Where is it on the form and how does it relate to the Grid?

This code almost does what I'd expect, well up to the ShowMessage which doesn't show anything. Also the last line has a compile error.

Code: Pascal  [Select]
  1. procedure Tform1.Formcreate(Sender: Tobject);
  2. var
  3.   Cell : PCell;
  4. begin
  5.  
  6.   FPGrid.ColCount := 5 ;
  7.   FPGrid.Cells [1,1]:='Time';
  8.   FPGrid.Cells [1,2]:='09:00' ;
  9.   FPGrid.ColWidths[2] := 120;
  10.   FPGrid.Cells [2,1] := 'Mini Team 1';
  11.  
  12.   //This shows nothing, but would expect it to show '09:00'
  13.   Cell := Workbook.Worksheet.FindCell(1,2);
  14.   if Workbook.Worksheet.IsMerged(Cell) then
  15.      ShowMessage ('Is Merged');
  16.   ShowMessage (Workbook.Worksheet.ReadAsText (cell));
  17.  
  18.  
  19.   //Compile problem - Type is not automamatable
  20.   FPGrid.cells [1,2].contenttype := cctDateTime;
  21.   ;
  22. End;
I've been through the tutorial again, but can't seem to get things straight in my head, any guidance would be appreciate to get me going on the right track.

BTW I can't find any help files in the Lazarus install directory, where should I be looking, and when I press the context sensitive help (F1) it says that it can't find any help - does this sound like something has not installed properly?

Thank you for sparing the time to help

Dave

[EDIT] Ok I now know why the ShowMessage isn't showing anything, it appears that Gird column A is 1 where as Workbook.Worksheet column A is 0 - and same for rows.
« Last Edit: October 07, 2017, 06:32:12 pm by daveinhull »
Version #:1.6.4 Date 2017/02/17 FPC Version: 3.0.2 and SVN Revision 54278 for i386-win32-win32/win64

wp

  • Hero Member
  • *****
  • Posts: 3951
Re: FPSpreadsheet knowing when a cell is in a merge
« Reply #6 on: October 07, 2017, 07:26:36 pm »
Workbook is of type TsWorkbookSource which is what I'd expect, but what is Worksheet if its not the WorkbookGrid? Where is it on the form and how does it relate to the Grid?
No. A workbook is a collection of worksheets, and a worksheet is a collection of cells - these are nonvisual data structures. The WorksheetGrid is something like a StringGrid and displays data stored in a worksheet. In the same sense that a TListbox displays data stored in a stringlist.

The entire fpspreadsheet library consists of two parts: the nonvisual data containers with TsWorkbook (type of workbook), TsWorksheet (type of worksheet), TCell (type of cell) etc., and the visual controls such as the grid or the mergeaction. The workbooksource is a link between these two parts. It watches for changes in one part and transfers it to the other part. Example: you type something into the grid then the workbooksource passes the new cell content to the underlying worksheet. Or conversely, you change a cell in the worksheet by code, e.g. worksheet.WriteText(0, 0, 'abc') then the grid is notified to repaint itself to display the new cell content.

I've been through the tutorial again, but can't seem to get things straight in my head, any guidance would be appreciate to get me going on the right track.
In addition to the tutorial, also look at http://wiki.lazarus.freepascal.org/FPSpreadsheet which focuses on the "data-container side" with worksheet and workbook

BTW I can't find any help files in the Lazarus install directory, where should I be looking, and when I press the context sensitive help (F1) it says that it can't find any help - does this sound like something has not installed properly?
No, there is no context-sensitive help, i.e. F1 does not work. But there are chm files in the folder "docs" of the fpspreadsheet installation which you must open by double-clicking. As I wrote earlier these are copies of the "historic" online version and fit to the version you installed; this is because the online version is continuously changing and may describe features which are not yet in your installed version (but only very rarely - and version 1.8.4 is almost the same as the development version).

it appears that Gird column A is 1 where as Workbook.Worksheet column A is 0 - and same for rows.
Yes, this is certainly a major annoyance to a new user: worksheet indexes start at 0, i.e. cell "A1" is row=0, col=0, and worksheet methods expect the indexes in exactly this order - row first, column last. In the grid, indexes start at 0 too, but the 0th column/row are usually occupied by the fixed cols/rows, and the first data cell is at column=1 and row=1! And to confuse you even more, the grid expects indexes in the order column first, row last, i.e. the exact opposite of the worksheet. This is due to historic reasons and cannot be changed any more without breaking user code. So, please be careful.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

daveinhull

  • Jr. Member
  • **
  • Posts: 73
Re: FPSpreadsheet knowing when a cell is in a merge
« Reply #7 on: October 07, 2017, 08:34:52 pm »
Hi wp,

Excellent thank you so much for taking the time to go over this, it really does help.I guess I wasn't really aware of the two parts, visual and non visual, but it makes absolute sense as I read it.

I can certainly now get on with some proper development.

Just one last, hopefully quick, question. You mention that you can type into the grid and it will be detected and written back to the underlying cell, worksheet and workbook, but this doesn't seem to work on my grid, it won't let me enter any test directly.

Again many that's for taking the time to help.

Dave
Version #:1.6.4 Date 2017/02/17 FPC Version: 3.0.2 and SVN Revision 54278 for i386-win32-win32/win64

wp

  • Hero Member
  • *****
  • Posts: 3951
Re: FPSpreadsheet knowing when a cell is in a merge
« Reply #8 on: October 07, 2017, 10:35:13 pm »
The WorksheetGrid has default properties like the well-known StringGrid. In order to make a StringGrid editable you set the Option goEditing. Do the same with the WorksheetGrid.

Have a look at http://wiki.lazarus.freepascal.org/TsWorksheetGrid#Recommended_property_values to learn more about recommended changes in property values.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus