fpSpread - thousand separator
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
kjteng · 2018-06-11 02:18

How do we set the thousand separator in fpspread?
I tried the following  :

   wksheetGrid.NumberFormat[1, 1] := '#,##.000';  // R0C0
   wksheet.WriteNumberFormat(1, 0,  nfCustom, '#,##.000');  //R1C0  

but these methods only change the format of the numbers to 3 decimal
places with no comma separator.

Did I miss out some steps in setting the thousand separator ?

----------------------------------------------------------------------
wp · 2018-06-11 08:27

This is not a valid number format. The Excel (and fpspreadsheet)
format string parser is very strict here: for using a thousand
separator you must use '#,##0' in front of the fractional part -
nothing else! This is different from fpc's "FormatFloat".

See attached demo

----------------------------------------------------------------------
kjteng · 2018-06-11 10:49

Thanks for your reply.
BTW is there any way to set number format for the whole worksheet?

----------------------------------------------------------------------
wp · 2018-06-11 12:17

Not for the whole sheet, but there are column and row formats which
are applied to all empty and new cells in the corresponding column or
row (http://wiki.lazarus.freepascal.org/FPSpreadsheet#Columns_and_rows
- a bit hard to find, though...).

See attached demo.

----------------------------------------------------------------------
kjteng · 2018-06-11 13:13

Quote from: wp on June 11, 2018, 02:17:23 pmNot for the whole sheet,
but there are column and row formats which are applied to all empty
and new cells in the corresponding column or row
(http://wiki.lazarus.freepascal.org/FPSpreadsheet#Columns_and_rows - a
bit hard to find, though...).

See attached demo.

Wow! This is fast.  While waiting for your reply, I was trying the
following code which is very slow when there are more than 10000
rows:-
    ws.GetCell(9999, 25);
    wsGrid1.NumberFormats[1, 1, GetLastColNumber +1, GetLastRowNumber
+1] := '#,##0.00';  


For the benefit of other user who may be interested, I have modified
your code slightly and put it in an event handler as follows:

procedure TForm1.Button1Click(Sender: TObject);
var  idx, ii: Integer; fmt: TsCellFormat;
begin
  InitFormatRecord(fmt);
  with wbSource do
    begin
      fmt.NumberFormatIndex := Workbook.AddNumberFormat('#,##0.000');
      fmt.UsedFormattingFields := fmt.UsedFormattingFields +
[uffNumberFormat];
      idx := Workbook.AddCellFormat(fmt);
      for ii := 0 to 25 do  // col A to Z
        Worksheet.WriteColFormatIndex(ii, idx);
    end;
end;

----------------------------------------------------------------------
kjteng · 2018-06-12 04:12

Say I have set the number format 
   wsGrid1.NumberFormats[1, 1] := '#,##0.00';  

How do I clear the format and restore the original format?
   Worksheet.GetCell(0, 0 )^.FormatIndex:= 0 seems to work. However
will this cause any problem since I have not removed the unused format
from FCellFormatList ?


What about the following codes:
  id := Worksheet.GetColFormatIndex(ActiveCellCol);
  worksheet.GetCell(ActiveCellRow, ActiveCellCol)^.FormatIndex:= id; 
    
It seems to work too but I am not sure there will be any problem
later.

----------------------------------------------------------------------
wp · 2018-06-12 07:45

Quote from: kjteng on June 12, 2018, 06:12:33 amSay I have set the
number format 
   wsGrid1.NumberFormats[1, 1] := '#,##0.00';  

How do I clear the format and restore the original format?These are
two different operations. Clearing and restoring are different things.
To clear the format simply write
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
 wsGrid1.NumberFormats[1, 1] := '';You cannot restore the original
format (if you mean the format which  the cell had before you set it
to '#,##0.00') because there is no stack to store all previous values.

Quote from: kjteng on June 12, 2018, 06:12:33 am 
 wsGrid1.Worksheet.GetCell(0, 0 )^.FormatIndex:= 0 seems to work.
However will this cause any problem since I have not removed the
unused format from FCellFormatList ?
In general, I do not recommend accessing the cell records directly -
this is what GetCell() returns - because some related operations may
not be executed if you change a record field alone. fpspreadsheet is
not well-designed in this context to allow access to the internal
fields but it cannot be changed without breaking everybody's code.
Always use the corresponding methods of the worksheet or workbook (or
the properties of the grid). The worksheet method here would be
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Worksheet.WriteNumberFormat(0, 0, nfGeneral);I would not care about
the "unused format from the FCellFormatList"? Are you sure that it is
really unused? Maybe another cell is referencing it? And after
deleting, you'd also have to update all format records because the
number format index of the formats following the deleted one will be
shifted down by 1. - No, too much hassle for a few bytes!

----------------------------------------------------------------------
kjteng · 2018-06-13 03:46

QuoteThese are two different operations. Clearing and restoring are
different things.
To clear the format simply write
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
 wsGrid1.NumberFormats[1, 1] := '';You cannot restore the original
format (if you mean the format which  the cell had before you set it
to '#,##0.00') because there is no stack to store all previous values.
Yes this is what I need.

If I have set number format for the column (
Worksheet.WriteColFormatIndex(0, idx) ) and then set the format for
cell A1 (wsGrid1,NumberFormat[1, 1] := ....).  Is there a way to clear
the format in cell A1 and apply the format set for column A to cell
A1?    or do I have to clear the format for A1 first and then reset
the column format again?


QuoteIn general, I do not recommend accessing the cell records
directly - this is what GetCell() returns - because some related
operations may not be executed if you change a record field alone.
fpspreadsheet is not well-designed in this context to allow access to
the internal fields but it cannot be changed without breaking
everybody's code.  ...
Thank you for telling me this. This also answered some other question
which I wanted to ask (but havent posted).

----------------------------------------------------------------------
wp · 2018-06-13 22:25

Quote from: kjteng on June 13, 2018, 05:46:27 amIf I have set number
format for the column ( Worksheet.WriteColFormatIndex(0, idx) ) and
then set the format for cell A1 (wsGrid1,NumberFormat[1, 1] := ....). 
Is there a way to clear the format in cell A1 and apply the format set
for column A to cell A1?    or do I have to clear the format for A1
first and then reset the column format again?
Like in Excel, column and row formats are applied only when a cell is
created, i.e. the format index stored in the column or row record is
copied to the cell record. When you delete the format of the cell, the
cell will displayed with the default cell format, but not with that of
the column or row.

There are two ways to reset the format of the cell back to that of the
row or column:
Delete the cell - Worksheet.DeleteCell(Cell) - and recreate it. When
the cell is recreated it is formatted with the appropriate row/col
format. Of course you must save the cell content (values, formula,
comments, hyperlinks) and restore it afterwards. And the formula
engine may create a lot of trouble because when a cell is deleted
formulas may be changed somewhere else in the sheet. A mess!Less
trouble, but a bit more tricky: Reset the FormatIndex of the cell to 0
(this is the index of the default format). Call
Worksheet.GetEffectiveCellFormatIndex(cell) - this method calculates
the overall format used for a cell at the crossing point of a column
and a row - each one of the three (cell, row, column) can have its own
format but is ignored when its format index is 0 (the default). Take
this index and put it back into the cell's FormatIndex. See the
following code:Code: Pascal  [Select][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i procedure RestoreColOrRowFormat(ACell: PCell);var  worksheet:
TsWorksheet;  fmtIndex: Integer;begin  if ACell <> nil then  begin   
worksheet := TsWorksheet(ACell^.Worksheet);    ACell^.FormatIndex :=
0;    fmtIndex := worksheet.GetEffectiveCellFormatIndex(ACell);   
worksheet.WriteCellFormatIndex(ACell, fmtIndex);  end;end;
Note that the worksheet method WriteCellFormatIndex requires the
current revision. Alternatively you can use
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
ACell^.FormatIndex := fmtIndex;  worksheet.ChangedCell(ACell^.Row,
ACell^.Col);

----------------------------------------------------------------------
kjteng · 2018-06-14 09:35

RestoreColOrRowFormat method is exactly what I needed. Thanks.