[solved by wp] Read font style from cell between char position
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
totya · 2018-03-02 21:26

Hi!

See sample cell, like as attached image.

I'd like to read cell string fontstyle(!). In example: word1 is normal
font, word2 is bold font (7-11 char), and word 3 is normal font.

Hogy can I read this font information between char position?

Thank you!

----------------------------------------------------------------------
wp · 2018-03-02 23:47

Formatting of individual characters within a cell is called "rich-text
formatting" according to Excel wordings. Rich-Text parameters are
stored as an array in the cell record and consist of character and
font indexes: the index of the character at which the font with noted
index is used. Fonts are collected by the workbook and can be read by
its methods GetFont(fontindex) (--> TsFont) or
GetFontAsString(fontindex).

Read the fpspreadsheet-wiki about "Rich-Text formating":
http://wiki.lazarus.freepascal.org/FPSpreadsheet#Rich-text_formatting

Here is an example which analyzes the rich-text formatting of the cell
text of your example:

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 program
Project1; {$mode objfpc}{$H+} uses  SysUtils, fpstypes, fpspreadsheet,
xlsxooxml, typinfo; var  book: TsWorkbook;  sheet: TsWorksheet;  cell:
PCell;  i: Integer;  rtp: TsRichTextParam;  fmt: TsCellFormat;begin 
book := TsWorkbook.Create;  try    // Prepare a worksheet containing
rich-text in cell A1    sheet := book.AddWorksheet('Sheet');   
sheet.WriteTextAsHtml(0, 0, 'test1 <b>test2</b> test3');    //       
                           |        |    //                          
       index 7   13     // ----------------------------------------- 
 // Analyze the fonts used in cell A1   
//------------------------------------------     cell :=
sheet.FindCell(0, 0);     // Write a "ruler" for counting character
positions    WriteLn('12345678901234567890');    // Write the
unformatted cell text    WriteLn(sheet.ReadAsText(cell));   
WriteLn;     // characters before the first rich-text parameter have
the cell font    fmt := book.GetCellFormat(cell^.FormatIndex);  // get
cell format record which contains the font index   
WriteLn(Format('Initial cell font: #%d (%s)', [fmt.FontIndex,
book.GetFontAsString(fmt.FontIndex)]));     // now write the rich-text
parameters    for rtp in cell^.RichTextParams do begin     
WriteLn(Format('Font #%d (%s) starting at character position %d', [  
    rtp.FontIndex,        book.GetFontAsString(rtp.FontIndex),       
rtp.FirstIndex      ]));    end; //    book.WriteToFile('test.xlsx',
true);  finally    book.Free;  end;   ReadLn;end.

----------------------------------------------------------------------
totya · 2018-03-03 14:22

Very thank you wp!

With your sample code, I can wrote the code what I wanted:

Edit.: Seems to me real "character positions" needed for StartPos,
StrLength parameters, for example for UTF8 strings needed
UTF8Pos/UTF8length etc. functions...

Edit 2.: v1.1 EndPos -> StrLength

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 { Thanks to
wp! See:
http://forum.lazarus.freepascal.org/index.php/topic,40311.0.html By
Totya, 2018.03.03 v1.1 Remark: Character positions needed for
StartPos, StrLength parameters}function
TExcelKez.GetSameFontStyleOrNot                    (const Book:
TsWorkBook;                     const Cell: PCell;                   
 const ReqFontStyle: TsFontStyle;                     const StartPos,
StrLength: integer): boolean; var  fmt: TsCellFormat;  rtp:
TsRichTextParam;  myFont: TSFont;  FontStyleSame, LastFontStyleSame:
boolean;  EndPos: integer; begin  fmt :=
Book.GetCellFormat(Cell^.FormatIndex);  FontStyleSame:= ReqFontStyle
in Book.GetFont(fmt.FontIndex).Style;   Endpos:= StartPos+ StrLength
-1;   for rtp in Cell^.RichTextParams do  begin    if rtp.FirstIndex >
EndPos      then break;     myFont:= Book.GetFont(rtp.FontIndex);    
LastFontStyleSame:= FontStyleSame;      FontStyleSame:= ReqFontStyle
in myFont.Style;     if rtp.FirstIndex > StartPos then      if
LastFontStyleSame = false then        begin          FontStyleSame:=
false;          break;        end   end;   Result:= FontStyleSame;end;

----------------------------------------------------------------------
totya · 2018-03-03 17:58

Seems to me real (char count) positions needed of StartPos, EndPos
parameters, for example for UTF8 strings needed UTF8Pos/UTF8length
etc. functions...

----------------------------------------------------------------------
wp · 2018-03-03 21:04

Oh, I had completely forgotten about that. I had thought it were byte
positions. But after changing the cell text in the demo above I see
that it is really codepoint positions, and looking in the source code
I see that the UTF8 routines are used (UTF8Length() instead of
Length()). I did this probably because the XLS specification wants
"character positions".

----------------------------------------------------------------------
totya · 2018-03-03 22:19

This is no problem for me, thank you again!