* * *

Author Topic: fpSpread - thousand separator  (Read 1296 times)

kjteng

  • Full Member
  • ***
  • Posts: 126
fpSpread - thousand separator
« on: June 11, 2018, 04:18:36 am »
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

  • Hero Member
  • *****
  • Posts: 4830
Re: fpSpread - thousand separator
« Reply #1 on: June 11, 2018, 10:27:13 am »
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
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 126
Re: fpSpread - thousand separator
« Reply #2 on: June 11, 2018, 12:49:09 pm »
Thanks for your reply.
BTW is there any way to set number format for the whole worksheet?

wp

  • Hero Member
  • *****
  • Posts: 4830
Re: fpSpread - thousand separator
« Reply #3 on: June 11, 2018, 02:17:23 pm »
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.

Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 126
[Solved] fpSpread - thousand separator
« Reply #4 on: June 11, 2018, 03:13:49 pm »
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.

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; 
« Last Edit: June 11, 2018, 04:23:26 pm by kjteng »

kjteng

  • Full Member
  • ***
  • Posts: 126
Re: fpSpread - thousand separator
« Reply #5 on: June 12, 2018, 06:12:33 am »
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.
« Last Edit: June 12, 2018, 06:21:45 am by kjteng »

wp

  • Hero Member
  • *****
  • Posts: 4830
Re: fpSpread - thousand separator
« Reply #6 on: June 12, 2018, 09:45:12 am »
Say 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]
  1.  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.

   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]
  1. 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!
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 126
Re: fpSpread - thousand separator
« Reply #7 on: June 13, 2018, 05:46:27 am »
Quote
These are two different operations. Clearing and restoring are different things.
To clear the format simply write
Code: Pascal  [Select]
  1.  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?


Quote
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.  ...
Thank you for telling me this. This also answered some other question which I wanted to ask (but havent posted).

     

wp

  • Hero Member
  • *****
  • Posts: 4830
Re: fpSpread - thousand separator
« Reply #8 on: June 14, 2018, 12:25:55 am »
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?
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]
  1. procedure RestoreColOrRowFormat(ACell: PCell);
  2. var
  3.   worksheet: TsWorksheet;
  4.   fmtIndex: Integer;
  5. begin
  6.   if ACell <> nil then
  7.   begin
  8.     worksheet := TsWorksheet(ACell^.Worksheet);
  9.     ACell^.FormatIndex := 0;
  10.     fmtIndex := worksheet.GetEffectiveCellFormatIndex(ACell);
  11.     worksheet.WriteCellFormatIndex(ACell, fmtIndex);
  12.   end;
  13. end;

Note that the worksheet method WriteCellFormatIndex requires the current revision. Alternatively you can use
Code: Pascal  [Select]
  1.   ACell^.FormatIndex := fmtIndex;
  2.   worksheet.ChangedCell(ACell^.Row, ACell^.Col);

Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 126
Re: fpSpread - thousand separator
« Reply #9 on: June 14, 2018, 11:35:49 am »
RestoreColOrRowFormat method is exactly what I needed. Thanks.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus