* * *

Author Topic: Column Moving not working as expected  (Read 575 times)

witenite

  • New member
  • *
  • Posts: 17
Column Moving not working as expected
« on: February 07, 2017, 10:53:53 am »
Hi,
firstly, thank you for an outstanding product. Your efforts are greatly appreciated.

I am using FPSpreadsheet to generate a grid in an application. Tried using TstringGrid however it lacks some features that appear to be standard in FPSpreadsheet. One of which is the ability to move columns of data.

I am using/compiling for (Ubuntu) Linux. On my form I have a tsWorksheetGrid (called tblProperties) and it is linked back to a TsWorkbookSource (called sDataSource). I have also tried this without TsWorkbookSource (IE  tblProperties links to tblProperties.internal). I have enabled goColMoving both in code and in the Object Inspector. goEditing is also enabled, though I have tried entering data programmatically too, but get the same result. When I attempt to move a column by clickin and holding the fixed column header,  a vertical red line appears, and the cursor changes to indicate the column is now being moved. However when I drag the column somewhere else and drop it, no data is moved! I have noticed that the columns properties are moved, such as the column width, but not the data contained in the column.

I have tried a variety of options (enabled and disabled) but cannot get the table to successfully move the column data. What am I doing wrong?

Note ultimately my program will not be retrieving data from a csv or excel spreadsheet file. Do I need to use TsWorkbookSource at all? Is the column moving issue due to me not having things setup correctly between tsWorksheetGrid and TsWorkbookSource? As mentioned already, I have tried running the code with tsWorksheetGrid linked to TsWorkbookSource via its WorkbookSource property.

Thanks for the help.

wp

  • Hero Member
  • *****
  • Posts: 3266
Re: Column Moving not working as expected
« Reply #1 on: February 07, 2017, 11:33:51 am »
The problem is that the WorksheetGrid does not propagate the new position of the column to the worksheet underneath the grid which stores all the information. This would be a quite complicated process because the moved column can contain formulas which must be adapted for the new location. Or formulas in other cells can refer to a cell in the moved column which is now somewhere else.

I don't know yet when I will fix this, my priorities at the moment are somewhere else. Could you please file a bug report and assign it to myself (maybe referring to this discussion) to avoid that the issue will be forgotten.

It think it would be safe to skip the WorkbookSource and to use the internal one provided by the grid.
Lazarus trunk / fpc 3.0.0 / Win32

witenite

  • New member
  • *
  • Posts: 17
Re: Column Moving not working as expected
« Reply #2 on: February 08, 2017, 06:43:38 am »

Will do. Where should I report the bug. Here:
http://bugs.freepascal.org/main_page.php

or here:
http://free-pascal-lazarus.989080.n3.nabble.com/

Suspend answering the following question, I just took a look at the class structure and think I have found what I am looking for (sorry, I should have done that first  :( )

As my application is a lot simpler (no formulas just fixed or editable values, I may be able to create my own procedure to do this. Are there any events I could trigger to (when a move occurs) or flags that I can use to determine what column has moved and to where? Will save me a considerable amount of time trawling through code I'm not familiar with. I'm also by no means an "advanced Delphi/Lazarus programmer" so your help in this regard will definitely be of great assistance.

Furthermore, I wish to limit the number of rows and columns (this is to be a fixed table, so I don't want the user to have the ability to step outside of a prescribed range)? I see there is a ColCount, which I have tried setting. This works visually, however if I run the program and then  move the focused cell to the right using the up/down/left/right arrow keys, new columns are added...
« Last Edit: February 08, 2017, 06:56:53 am by witenite »

wp

  • Hero Member
  • *****
  • Posts: 3266
Re: Column Moving not working as expected
« Reply #3 on: February 08, 2017, 07:51:42 am »
You find the link to bugtracker in the left sidebar of this forum (under topic "Lazarus"), but your first link is correct as well. You must register for being able to post.

You can hook into the OnColRowMoved event of the grid, it has the indexes "From" and "To" as parameters. Since a lot of administrative work is needed also for the columns between these indexes I think it would be easiest to call Worksheet.InsertCol for the "To" index, copy all cells from the "From" to the "To" column by calling Worksheet.CopyCell (but note that "To" may have changed upon the insertion of the new column) - CopyCell takes care of related jobs like hyperlinks or comments. Finally you delete the "From" column by calling Worksheet.DeleteCol. InsertCol and DeleteCol take care of the renumbering of the col indexes of all cells to the right of the inserted/deleted column.

For limiting the row/column count set the RowCount/ColCount properties of the grid accordingly and set the property "AutoExpand" of the grid to [].
Lazarus trunk / fpc 3.0.0 / Win32

witenite

  • New member
  • *
  • Posts: 17
Re: Column Moving not working as expected
« Reply #4 on: February 14, 2017, 06:40:35 am »
I had hardly written my reply (dated feb 8th) and I had the idea of seeing what events you provided for. Consequently I used the OnColRowMoved event to write the following code:

// Column moving procedure
procedure TForm1.tblPropertiesColRowMoved(Sender: TObject; IsColumn: Boolean;
  sIndex, tIndex: Integer);
var
 Row : Integer; // Count for row number busy being copied/pasted
 Col : Integer; // Count for columns being swapped (Note columns are swapped progressively until the source column has been correctly inserted into its target position, and all columns between source and target are shifted accordingly)
 RowCount : longint; //
 TempColumn : array of variant; // Dynamic array to hold source column
 ET: TEpikTimer; //// DELETE THIS PROCEDURAL TIMER FROM THE FINAL CODE
 Duration : extended;

 begin

 ET := TEpikTimer.Create(Application); // Create timer to assess how long a routine takes
 ET.Clear; // optional... timer is cleared at creation
 ET.Start;  // Start timer

 tblProperties.BeginUpdate; // Disable table control update until after the whole table has been corrected

  RowCount := tblProperties.RowCount-1; // Grab local copy of the number of rows in table
  label1.caption := inttostr(RowCount);
  if (RowCount > 10000) then RowCount := 10000;
  setlength(TempColumn,RowCount); // Assuming the table rowcount is a believable number, dimension array to suit the column about to be moved

  // If the source index is to the left of the target, then cyclically process column by column from left to right
  if (sIndex < tIndex) then
  begin
    for Row := 0 to RowCount do TempColumn[Row] := tblProperties.cells[sIndex,Row]; // Create a copy of source column to be moved
    for Col := sIndex to tIndex-1 do
    for Row := 0 to RowCount do tblProperties.Cells[Col,Row] := tblProperties.Cells[Col+1,Row]; // Shift data from left column to right column for all columns between source and target columns
    for Row := 0 to RowCount do tblProperties.cells[tIndex,Row] := TempColumn[Row]; // Copy moved column into its final target position
  end;

  // If the source index is to the right of the target, then cyclically process column by column from right to left
  if (sIndex > tIndex) then
  begin
    for Row := 0 to RowCount do TempColumn[Row] := tblProperties.cells[sIndex,Row]; // Create a copy of source column to be moved
    for Col := sIndex downto tIndex+1 do
    for Row := 0 to RowCount do tblProperties.Cells[Col,Row] := tblProperties.Cells[Col-1,Row]; // Shift data from left column to right column for all columns between source and target columns
    for Row := 0 to RowCount do tblProperties.cells[tIndex,Row] := TempColumn[Row]; // Copy moved column into its final target position
  end;
  tblProperties.EndUpdate(True); // Columns have finished being moved. Reactivate updating of table control


  ET.Stop; // the timer is actually paused and can be restarted later

  Duration := ET.elapsed;
  label2.caption := floattostr(Duration) + ' seconds';
  ET.Free;
  end;

This takes about 16 seconds (on a quad core I7 4.2GHz machine!) to move column A about 30 columns down (and all subsequent column moves) for 1000 rows of cells with numbers in them. Does this seem inordinately long to you? Would I be better off using the "DeleteCol" and "CopyCell" etc. you have suggested? I don't mind re-writing my code if need be, it all makes for good practice :)
« Last Edit: February 14, 2017, 09:27:45 am by witenite »

NoP

  • Newbie
  • Posts: 1
Re: Column Moving not working as expected
« Reply #5 on: February 14, 2017, 08:50:36 am »
I had hardly written my reply (dated feb 8th) and I had the idea of seeing what events you provided for. Consequently I used the OnColRowMoved event to write the following code:

(...)
 tblProperties.EndUpdate(True); // Disable table control update until after the whole table has been corrected
(...)
  tblProperties.BeginUpdate; // Columns have finished being moved. Reactivate updating of table control
(...)

This takes about 16 seconds (on a quad core I7 4.2GHz machine!) to move column A about 30 columns down (and all subsequent column moves) for 1000 rows of cells with numbers in them. Does this seem inordinately long to you? Would I be better off using the "From" and "To indexes you have suggested? I don't mind re-writing my code if need be, it all makes for good practice :)

Are you sure you don't have those EndUpdate/Beginupdate calls backwards? I have never used the component you are referring to, but with other visual components beginupdate is used to signal "I'm modifying data, please do not update visually", and endupdate means "ok, I'm done, go ahead and repaint".

witenite

  • New member
  • *
  • Posts: 17
Re: Column Moving not working as expected
« Reply #6 on: February 14, 2017, 09:12:54 am »
Good Spotting, glad to know somebody is looking at my code. I had tried beginupdate/endupdate the other way around, however it appears to make absolutely no difference which way around they are, or whether I even use them at all. Stlil comes out at almost exactly 16 seconds to shift around 1000x30 = 30000 cells.

I have swapped BeginUpdate and EndUpdate procedures accordingly to be correct and avoid potential confusion for anybody else reading this thread.
« Last Edit: February 14, 2017, 09:29:11 am by witenite »

wp

  • Hero Member
  • *****
  • Posts: 3266
Re: Column Moving not working as expected
« Reply #7 on: February 15, 2017, 11:08:15 pm »
Please try the current revision of fpspreadsheet. It has built-in support for moving of columns now. Normally you don't need to write any code for the OnColRowMoved event any more, just set the option goColMoving to enable the feature. I followed your idea of mutually exchanging adjacent cells, but at a very low level, and this means that - unlike your code - cell formats are moved along with cell content. Hyperlinks and comments are treated in the same way, merged cells are currently ignored. Not sure about formulas.

Still very experimental and not heavyily tested. Althought I tested a 1000-row grid only for moving across a distance of a few columns I think that the method is reasonably fast.

Please test and feed-back your observations (but note that currently there's a bug in painting the insertion marker.)

P.S.
No idea what's wrong with your code (I did not look at it in detail).
Lazarus trunk / fpc 3.0.0 / Win32

 

Recent

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