InsertAndCopyRange & Fixed row

Good afternoon,

thank's for all your help, now j'm here for another 2 (easy) questions !

1) there's a way to store in memory a range of selected cells (like InsertAndCopyRange procedure) in order to paste this range in a second time ?

2) there's a way to fix a row in order to get, in printing procedure, the same first row in each sheet ?

Thank's for your help

Best regards



1) Not really, what we have is InsertAndCopyRange. This was done for efficiency ( because InsertAndCopyRange is called thousands on times when doing reports, so it has to be very fast, and copying the cells to memory and then from memory back to another sheet isn't a performant solution)

But you could "fake it" if you want. Just create a new XlsFile object, let's call it xls2, empty with one sheet: xls2 := TXlsFile.Create(1, true);
Then InsertandCopyRange() from xls to xls2, and then InsertAndCopyRange fro xls2 to all the other xls objects you want. Xls2 will be your "in memory range"

Of course, you can also actualy copy and paste with FlexCel (look at the copy and paste demo) but you should use this only if the user is actually copying ot the clipboard. (you don't want to change the clipboard of the user if he didn't press "copy")

2)Yes, in Excel it is Page Layout->Print Titles-> "Rows to repeat at the top"
And of course, if you know how to do it in Excel, you just need to ask APIMate to see how to do it in FlexCel:

 //Named Ranges
  RangeName := TXlsNamedRange.GetInternalName(TInternalNameRange.Print_Titles);
  Range := TXlsNamedRange.Create(RangeName, 1, 32, '=''Sheet1''!$1:$2');
  //You could also use: Range := TXlsNamedRange.Create(RangeName, 1, 1, 1, 1, 2, TFlxConsts.Max_Columns + 1, 32);

Hi Adrian,

thank's for you suggestion.

Now a new one on insertcopyrange (again).

With TRangeCopyMode j have this

- All

- OnlyFormulas

- None

- OnlyFormulasAndNoObjects

- AllIncludingDontMoveAndSizeObjects

- Formats

here new one that is missing

- OnlyText

Where j copy just text (without format, style, font type, color ecc.....)

Thank's for all



Indeed, only text would be an interesting option. I just wonder how it would be implemented, since just copying the values is not probably what an user would expect.

For example, imagine you have the date 1/1/2000.  This is the number 36526 formatted as "dd/mm/yyyy" or similar. If you copied just the value, it would becopied as the number 36526 which is most likely not what you want. So some formatting has to be copied too. While font color and the like wouldn't, at least the numeric format should be copied with the value.

Hi Adrian,

   wow ... your reply is so fast .... very fast !!!

> Indeed, only text would be an interesting option

Yes ... but is not so easy ....

> For example, imagine you have the date 1/1/2000

This is the second (ops ... j forgot the counter) problem !!!

Yes the date !!!!

J got via e-mail one Excel file with one column indicated with "minimum expired date" and the cells value are






04 2015

05 2015

01 2015

09 2014

My work on this file is change the code column in order to be the first one, and change the price column in order to be the second one.

After, j compare all the Excel files and all the rows (for each file) with the highest price for the same code are deleted.

After that .... reload the new Excel file and .... surprise !!!!

The column with the date are changed ... and the new values are

42095 (for apr-2015 or 04 2015)

21125 (for mag-2015 or 05 2105)

and so on ....

J have to consider that this column is not an optional one, but it must be declared and,

dependig from the operator's persaonl daily humor, the date will have different format (31/04/2015 or 04/2015 or aprile 2015 or apr 2015 or apr-2015 or any Others)

J though to remove the column .... but is not a great idea !!!! because this date indicate the minimum date validity for the good(s).

So, once more j'm here to ask your help for this matter !!! (not so easy .....)

One way is read all the cells of the good row (not deleted) and detect the date, convert it in string and reset the cell with a new string.

The problem is .... which is the correct cell ??? due the fact that the row contain minimum 12 cells and more then 6 have numeric value (as the date is) ?

Too crazy !!!

Now j give you all the time you need to drink a good cup of coffe after readed this message.

Thank's for your patience



PS: Just for change the matter ..... any idea on how to read aqcobol database (called file system).

Ok, j will post a new post in the correct forum ....

OK, here the second cup of coffe !!!!


About dates, in Excel dates are numbers formatted with date formatting. If you want to be more specific, the number is the number of days that passed since jan-2-1900, and the fractional part is the fraction of the day. (so 0.5 means 12 hours). But there is no need to know those specific things (and they might change if you use a 1904 date system). You only need to know that dates are numbers formatted.

If you open excel, write 42095 and format it as "mmm-yyyy" it will show "apr-2015". If you instead format it as "mm yyyy" it will show 04 2015.  The value is always the same, only the format changes.

So if FlexCel isn't showing the right date, just change the format of the cell to be a date format. If you want to convert the double with FlexCel, you can use code like this: (from the reading files demo)

      CellColor := TUIColor.Empty;
      CellValue := TFlxNumberFormat.FormatValue(v, xls.GetCellVisibleFormatDef(Row, Col).Format, CellColor, xls, HasDate, HasTime).ToString;
      if HasDate or HasTime then
        Result := 'a DateTime value: ' + DateTimeToStr(v.ToDateTime(xls.OptionsDates1904)) + #10 + 'The value is displayed as: ' + CellValue;
        Result := 'a double: ' + FloatToStr(v.AsNumber) + #10 + 'The value is displayed as: ' + CellValue + #10;

About having customers only enter dates, I am not sure if they enter them in an Excel spreadsheet, but if they do, you can use "Data Validation" (In Excel, go to the ribbon->Data->Data Validation) to force them to enter only dates in column A. You also have full support for data validation from FlexCel, and APIMate will show you the code.

About aqcobol, sorry, it is the first time I hear about it. No idea on how to read it.