Error copying cells when filtering active.

FLX TMS FlexCel for VCL & FMX
1

I have a simple XLSX file with a few columns of data in the first sheet,
with the column filtering switched on (although all filters are set to
all). I am then trying to copy data from some of the rows in this sheet
to a second sheet.

The code I'm using to copy, say row 5 of sheet 1 to row 2 of sheet 2 is:

XLS.ActiveSheet:=2;
XLS.InsertAndCopyRange(TXLSCellRange.Create(5,1,5,5),2,1,1,TFlxInsertMode.NoneDown,TRangeCopyMode.All,XLS,1,NIL);
 
When I try to do this, I get an error: Invalid Row Index "-2"

If filtering is turned off, the copy works perfectly!

The
reason this has cropped up, is that our application does an import of
some data from an XLSX file, and any invalid data rows are copied to an
"error" sheet. If the supplied file has filtering switched off,
everything works ok, however, a customer has just tried it with
filtering switched on and got the invalid row index error.

I've just upgraded to latest version (6.17.3.0) and still get the error.

As a work around, is there a way within Flexcel to switch off filtering before I attempt the copy?

2

Hi,

Thanks for reporting this, it is indeed a bug. To go a little more in detail what is happening is that FlexCel is trying to copy the AutoFilter even if it is outside the range you are copying. In this case, the autofilter is at row 1, and you are copying row 5 to row 2. So FlexCel is trying to copy the autofilter to row -2 (as the filter is 4 rows below the row you are copying. If row 5 goes to 2, then row 1 gos to -2...)

It has been fixed internally and the fix should be available in the next version which I really hope will come out by the end of this week.

As a workaround, you can indeed remove the filter from the sheet that you are copying from:



XLS.ActiveSheet:=1;
XLS.RemoveAutoFilter();
XLS.ActiveSheet:=2;
XLS.InsertAndCopyRange(TXLSCellRange.Create(5,1,5,5),2,1,1,TFlxInsertMode.NoneDown,TRangeCopyMode.All,XLS,1,NIL);
3

Thanks Adrian.

I'll keep a lookout for the next release, but for now I've implemented a workaround to remove the filter, do the copy, and then re-apply the filter.

4

Hi,

6.17.4 has been released and should fix this.