DeleteColumn(s)

FLX TMS FlexCel for VCL & FMX
1

Hi


In FlexCel 5.6.5, how do I delete (remove) a whole column, for example TXlsFile.DeleteColumn(5)   or a range of columns like TXlsFile.DeleteColumns(5,6) ?

TXlsFile.DeleteRange operates seemingly only on cell ranges and not on whole rows or columns.
I can't find any info on it in the documentation, demos or the source code.

Thank's.
2

Hi,

DeleteRange operates in ranges, full columns or full rows. It is the same method for everything, and in the same way InsertAndCopyRange can insert or copy or insert and copy a range, a group of rows or a group of columns from one sheet to the same, to another, or to a different workbook.

The idea is to have a small number of methods that do everything, instead of having a gazillion "InsertRange / InsertRow / InsertColumn /CopyColumn/ etc".
You have:
InsertAndCopyRange to deal with all the copying and inserting cells/rows/column stuff
DeleteRange to deal with deletions
MoveRange to deal with moving blocks of cells/columns/rows.

As for how it works, it is automatic. If you do 
xls.DeleteRange(TXlsCellRange(1, 2, FlxConsts.Max_Rows + 1, 3, TFlxInsertMode.ShiftRangeRight);
FlexCel will detect you are deleting the full columns (2 and 3), and act as such.

But, there is also a simpler method. In TFlxInsertMode, there is one value that is "TFlxInsertMode.ShiftColRight"
which will always insert/remove full columns, no matter the range. So this call:

xls.DeleteRange(TXlsCellRange.Create(1,2,1,3), TFlxInsertMode.ShiftColRight);
will also remove the full column, no matter which rows you pass in the range. Whenever you are inserting/deleting rows or columns, it is simpler to use ShifColRight/ShiftRowsDown so you don't have to worry about all the rows/columns being selected.

A final note: I know some customers find it confusing that the enumeration is "ShiftColRight", and not "ShiftColLeft" (which is what actually happens when you remove a column). this is because it is the same enumeration used to insert columns, so when you insert "Right" is the correct direction. (and actually internally DeleteRange works by inserting -1 columns). But well, if I had to do it again, I might use 2 different enumerations, TFlxInsertMode.ShiftColRight and TFlxDeleteMode.ShiftColLeft. But well, other than the "strange" shifting right, when you are shifting left, DeleteRange(TClsCellrange.Create(1, Col1, 1, Coln), TFlxInsertMode.ShiftColRight) will remove columns from col1 to coln.

Regards,
   Adrian.

3

Thank's. That helped a lot. However, I have a related question and the API still poses difficulties.


Now I'm trying to Insert a new, empty row at row number 3.
Iow: All contents from row 3 is moved downwards one row and a new empty row 3 appears.
My best attempt was this:

InsertAndCopyRange(TXLSCellRange.Create(3, 1, 3, 1),  3, 1, 1,  TFlxInsertMode.ShiftRowDown);

But this leaves rows 3 and 4 identical, and not row 3 blank as desired.

Thank's in advance.
4

Hi,

Use 
      xls.InsertAndCopyRange(TXLSCellRange.Create(3, 1, 3, 1),  3, 1, 1,  TFlxInsertMode.ShiftRowDown, TRangeCopyMode.None);

5

Followup:


My next attempt was 

MoveRange(TXLSCellRange.Create(3, 1, TFlxConsts.Max_Rows, 1), 4, 1,  TFlxInsertMode.NoneDown);

This seems to produce the correct result, but the parameters seem a little unorthodox.
Please advise.

6

TFlxIndertMode is, as the name implies, for how you want to insert the row. NoneDown will not insert a row, but just copy a range of cells.


TRangeCopyMode determines how cells are copied (if copied at all)

By mixing them, you can insert, copy and insertandcopy cells. (or formats,or fomulas only, etc).

In your case, to insert an empty row, the parameters are TFlxInsertMode.ShiftRowDown (so we insert a row) and TRangecopyMode.None (so we don't copy the other row in the inserted row).