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
Daniele
Hi,
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);
xls.SetNamedRange(Range);
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
Ciao
Daniele
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
apr-15
mag-15
gen-15
set-14
or
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
Ciao
Daniele
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 !!!!
Hi,
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
begin
Result := 'a DateTime value: ' + DateTimeToStr(v.ToDateTime(xls.OptionsDates1904)) + #10 + 'The value is displayed as: ' + CellValue;
end
else
begin
Result := 'a double: ' + FloatToStr(v.AsNumber) + #10 + 'The value is displayed as: ' + CellValue + #10;
end;
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.