Ticket T311454

Visible to All Users

How to copy Cell-Range contianing relativ references programmatically w/o Clipboard

Torsten Voigt

created 9 years ago

Hello,

maybe I did not found this, but how can I achieve to copy a given range containing formulas (with relative references) programatically?

I have a valid Excel-File with some formulas as a template. In order to get everyhting automatically done, I have to copy some formulars for each new record downwards and therefore the formula-references have to be updated. However, I do not want to touch the clipboard, as I see no reason to scrap its content.

I've seen that in the clipboard-helper you use TcxWrite r with dxSpreadSheetBinaryFormatVersion. Does this automatically update the references as it maybe stores relativ instead of absolut links, like in R1C1?
Because when pasting back from the clipboard the formulas get the correct references.

So how can I achieve to copy a given range to another range on the same TdxSpreadSheetTableView (or any other TdxSpreadSheet-Object)

Best regards,
Peter

Comments (1)

DevExpress Support Team 9 years ago

Hello Peter,

We are working on your request, but it can take us some time to examine it. We will get back to you once we have any results or need additional information. Thank you for your patience.

Answers approved by DevExpress Support

DevExpress Support Team

created 9 years ago

You need to restore the previously stored SpreadSheetView.ViewInfo.FirstScrollableColumn and SpreadSheetView.ViewInfo.FirstScrollableRow properties before calling SpreadSheet.EndUpdate. I attached the modified MainUnit.pas for your reference.

MainUnit.pas

Comments (1)

Torsten Voigt 9 years ago

Hi and thanks for the info.
It works, but for anyone, who would like to adept it: please keep in mind that the ViewInfo-Property is (at least in the current release 2015 v1.6) a protected property, so you have to change your code like that to get it work:
[DELPHI]

Delphi
type  
 TdxSpreadSheetTableViewAccess = class( TdxSpreadSheetTableView );  
...  
 procedure InternalStoreScrollPos(AView: TdxSpreadSheetTableView; var AScrollPos: TPoint);  
 begin  
   AScrollPos.X := TdxSpreadSheetTableViewAccess(AView).ViewInfo.FirstScrollableColumn;  
   AScrollPos.Y := TdxSpreadSheetTableViewAccess(AView).ViewInfo.FirstScrollableRow;  
 end;  

 procedure InternalRestoreScrollPos(AView: TdxSpreadSheetTableView; const AScrollPos: TPoint);  
 begin  
   TdxSpreadSheetTableViewAccess(AView).ViewInfo.FirstScrollableColumn := AScrollPos.X;  
   TdxSpreadSheetTableViewAccess(AView).ViewInfo.FirstScrollableRow := AScrollPos.Y;  
 end;

[/DELPHI]

DevExpress Support Team

created 9 years ago (modified 9 years ago)

Hello,
I have attached a small sample to demonstrate how to accomplish this task.

dxSample.zip

Show previous comments (4)

Torsten Voigt 9 years ago

Hello and thanks for the example,
it's almost the same solution as I've came out.
And yes, it works as long as the destination cell is focused. If, however you want to work completely programmatically it should not matter, where the focus is.
Instead I'd like to give address programmatically, but if I modify in your example the line
[DELPHI]
//ADestArea := cxRectOffset(R, AView.Selection.FocusedColumn, AView.Selection.FocusedRow);
ADestArea := cxRectOffset(R, 10, 25 );
[/DELPHI]
I would expect that the output always comes to Column 10=K and row 25(+1), however this does not work.
I've adapted your example and split it in a seperate class so I can use it mutiplie times, but in the initial test the result was always posted to cell (0; 0). While examinin the result it was totally clear: it pasted it only the selected cell. I, however, wantet to specifiy a target.
Therefore I've changed the "DataArea"-Parameter to map the destination area. I don't understand what the difference between the selection/data-area is - I'm currently suppliing both with the same data and it works, but are there any drawbacks or do I have to take something other into account?
Best regards,
Peter

DatacopyHelper.pas

Torsten Voigt 9 years ago

OK, my solution is also somewhat of an interim solution, as It still depends on the focused row (in my template I've defaultet them to Cell[ 0, 0 ]…
I just cannot see a valid reason to decrease the target-corrdinates by the current focusedrow/column…

DevExpress Support Team 9 years ago

Hello Peter,

Attached is the modified project demonstrating how to copy the content into any selected position in the spreadsheet.

dxSample2.zip

Torsten Voigt 9 years ago

Hello,
I've adapted the changes to my helper class and it works, but still has a drawback:
I've some fixed header-rows (8 rows) and when copying down some hundred data rows (from line 9 ongoing), the SpreadSheetTableView also scrolls down, even if i've cached the original focused cell (because it is in the fixed view area…)
In the result I'll always see only the last (instead of the first rows) if I view and/or save the created excel-file and this is confusing.
So is there
a) a way, to save and restore the visible area of the not fixed view?
b) use the copy function w/o changing the focused cell?

DatacopyHelper.pas

Torsten Voigt 9 years ago

Hello,
the provided solution does not work anymore with VCL Beta 15.2.1

Torsten Voigt 9 years ago

OK,
+1 point for providing the class TdxSpreadSheetTableViewClipboardData which encapsulates all this nicely, however I do not see the reason, why I can only paste to an TRect instead of a TPoint

DevExpress Support Team 9 years ago

Hello Peter,

Thank you for your report. Indeed, in 15.2.1, this workaround does not work anymore. Moreover, I do not think that it have sense to restore it back because it was not reliable. In my opinion, it is better to implement a general public solution that will help you copy and paste certain cell regions between different positions and different sheets. I have created a separate ticket (T324222) to implement this functionality in future builds.

Created: November 12, 2015 1:20 PM

Modified: December 14, 2015 1:45 PM

Platform: VCL

Product: ExpressSpreadSheet

Build: 15.1.6

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

How to copy Cell-Range contianing relativ references programmatically w/o Clipboard

Answers approved by DevExpress Support

Recently viewed tickets