Copy font

[straetch]

Hi,
In a spreadsheet I want to copy the font used in one cell to all other cells, regardless of the actual font properties. The purpose is to clean up a spreadsheet filled in by several people.
I am puzzled. I see the method ReadCellFont and was expecting a mirror method, a sort of "WriteCellFont". How is this solved?
I also get an error on myWorksheet.ReadCellFont: unknown.
Other formatting methods work ok, eg. WriteFontstyle, WriteBorders, WriteWordWrap. I have  fpstypes, fpspreadsheet,fpsallformats in the uses clause.
Thanks for your advice.

[wp]

All fonts are stored in a list of the workbook and are shared between all worksheets and cells. A cell font is defined by its index in this list. Therefore, if you want to copy a font from one cell to another cell, get the font index of this cell by "ReadCellFontIndex" and write it to the other cell by "WriteCellFontIndex".

"ReadCellFont", on the other hand, returns an instance of the TsFont class as stored in this list; it is used for example in the WorksheetGrid when painting a cell. There is not corresponding "WriteCellFont" because it would require you to create an instance of TsFont first, check if such a font already is contained in the workbook's fontlist, and, if it already exists, use this instance and destroy the newly created one. These steps require some knowledge of the inner structure of fpspreadsheet which I cannot assume. Writing of a particular font, on the other hand, is done by other "WriteFont*" methods where individual members of the TsFont can be specified instead of a complete TsFont instance.

I also get an error on myWorksheet.ReadCellFont: unknown.

I don't see a reason why this should not work... Maybe except that, in contrast to the writing methods, most of the read methods want a "cell" parameter instead of "row","col". Could you post a reduced sample  project from which I can see how you call this method? Which fpspreadsheet version are you using?

[straetch]

Thanks for the prompt response!
Attached a code sample.
The compiler fails on  the ReadCellFont statement. Error: Identifier not Found "ReadCellFont". The method is listed in fpspreadsheet-api.
The method ReadCellFontIndex, you suggested, also fails to compile (same reason). I did not find it in fpspreadsheet-api. Same for WriteCellFontIndex.
fpspreadsheet version is 1.6.0

[wp]

Oh, you are right. It looks as if ReadCellFontIndex was added after releasing fps1.6, and my description in above post was not quite correct: there is no WriteFontIndex method in trunk either, it is called WriteFont and exists already in 1.6.

There are two options for you:

• Switch to the trunk version of fpspreadsheet. This provides the missing method ReadFontIndex.
• Or, if you want to stay with 1.6 you could patch the fpspreadsheet.pas unit and add this code:

Code: Pascal  [Select][+][-]

1. interface
2. ...
3. type
4.   TWorksheet = class
5.   ...
6.   public
7.   ...
8.     function ReadCellFontIndex(ACell: PCell): Integer;
9.   end;
10. ...
11. implementation
12. ...
13.  
14. function TsWorksheet.ReadCellFontIndex(ACell: PCell): Integer;
15. var
16.   fmt: PsCellFormat;
17. begin
18.   Result := DEFAULT_FONTINDEX;
19.   if ACell <> nil then
20.   begin
21.     fmt := Workbook.GetPointerToCellFormat(ACell^.FormatIndex);
22.     Result := fmt^.FontIndex;
23.   end;
24. end;
25. ...

Note that - in order to reduce the number of overloaded methods - I decided to implement ReadCellFont only as a variant which gets a pointer to the cell as a parameter, not row/column as in other cases. Therefore, you must search the cell first if you only know its row and column indexes:

Code: Pascal  [Select][+][-]

1. var
2.   font_index: Integer;
3. begin  
4.   font_index := Worksheet.ReadCellFontIndex(Worksheet.Findcell(ARow, ACol));
5.   ...

or, if you want to extract some more information from the same cell and maybe write to it again, it is advisable to store the pointer to the cell and used it in the subsequent methods:

Code: Pascal  [Select][+][-]

1. var
2.   cell: PCell;
3.   font_index: Integer;
4.   borders: TsCellBorders;
5. begin
6.   cell := Worksheet.Findcell(ARow, ACol);
7.   if cell <> nil then
8.   begin
9.     font_index := Worksheet.ReadCellFontIndex(cell);
10.     borders := Worksheet.ReadCellBorders(cell);
11.   end else
12.   begin
13.     font_index := -1;
14.     borders := [];
15.   end;
16.   ...

Instead of FindCell you can also use GetFont. This has the disadvantage that a new empty cell is created if there is no cell at the queried location, and it has the advantage that the check for nil is not required for this reason.

One comment on your code, probably you know this, but just in case you don't: Row and column indexes in fpspreadsheet always begin at 0, the fixed cells knows from a TStringGrid are not considered. I mention this because you begin the i loops (row index) always at index 1 (probably intentionally).

[straetch]

Thanks a lot.
After struggling with updating the fpspreadsheet package (I will post a seperate topic), my program works great.
One observation: if the text in a cell contains several fonts, then changing the font with WriteFont has no effect on internal font changes. Shouldn't WriteFont overrule all font settings for the cell? This would correspond to selecting the entire cell content and alter the font in Exell/OO/LO.
The workaround looks like: read the cell text, apply WriteFont, rewrite the text.

[wp]

I think the priorities are correct: If a cell has no formatting at all it uses the default font. If you apply a font it is used instead. It can be overridden for some characters by the rich-text parameters, i.e. rich-text must have the highest priority.

Your problem seems to be that rich-text parameters are still there after you replace the cell font. I think that's fine because a user may want to replace only the font used by non-formatted characters. What is missing is a function like "DeleteRichTextParams" which deletes all the rich-text parameters and restores the cell font entirely - I'll add something like that in the next days. In the meantime you can do this by yourself because the rich-text formatting parameters are directly stored in the TCell record. I think this should work:

Code: Pascal  [Select][+][-]

1. procedure DeleteRichTextParams(ACell: PCell);
2. begin
3.   if ACell <> nil then SetLength(ACell^.RichTextParams, 0);
4. end;

[EDIT]
Just saw that the fpspreadsheet.pas of my development version is not much altered from the trunk version. Therefore, I implemented the DeleteRichTextParams already and uploaded it to trunk.