Recent

Author Topic: [solved by wp] Read font style from cell between char position  (Read 3825 times)

totya

  • Hero Member
  • *****
  • Posts: 720
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!
« Last Edit: March 03, 2018, 04:04:58 pm by totya »

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: Read font style from cell between char position
« Reply #1 on: March 03, 2018, 12:47:36 am »
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][+][-]
  1. program Project1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. uses
  6.   SysUtils, fpstypes, fpspreadsheet, xlsxooxml, typinfo;
  7.  
  8. var
  9.   book: TsWorkbook;
  10.   sheet: TsWorksheet;
  11.   cell: PCell;
  12.   i: Integer;
  13.   rtp: TsRichTextParam;
  14.   fmt: TsCellFormat;
  15. begin
  16.   book := TsWorkbook.Create;
  17.   try
  18.     // Prepare a worksheet containing rich-text in cell A1
  19.     sheet := book.AddWorksheet('Sheet');
  20.     sheet.WriteTextAsHtml(0, 0, 'test1 <b>test2</b> test3');
  21.     //                                    |        |
  22.     //                                   index 7   13
  23.  
  24.     // -----------------------------------------
  25.     // Analyze the fonts used in cell A1
  26.     //------------------------------------------
  27.  
  28.     cell := sheet.FindCell(0, 0);
  29.  
  30.     // Write a "ruler" for counting character positions
  31.     WriteLn('12345678901234567890');
  32.     // Write the unformatted cell text
  33.     WriteLn(sheet.ReadAsText(cell));
  34.     WriteLn;
  35.  
  36.     // characters before the first rich-text parameter have the cell font
  37.     fmt := book.GetCellFormat(cell^.FormatIndex);  // get cell format record which contains the font index
  38.     WriteLn(Format('Initial cell font: #%d (%s)', [fmt.FontIndex, book.GetFontAsString(fmt.FontIndex)]));
  39.  
  40.     // now write the rich-text parameters
  41.     for rtp in cell^.RichTextParams do begin
  42.       WriteLn(Format('Font #%d (%s) starting at character position %d', [
  43.         rtp.FontIndex,
  44.         book.GetFontAsString(rtp.FontIndex),
  45.         rtp.FirstIndex
  46.       ]));
  47.     end;
  48.  
  49. //    book.WriteToFile('test.xlsx', true);
  50.   finally
  51.     book.Free;
  52.   end;
  53.  
  54.   ReadLn;
  55. end.
  56.  
« Last Edit: March 03, 2018, 12:58:25 am by wp »

totya

  • Hero Member
  • *****
  • Posts: 720
Re: Read font style from cell between char position
« Reply #2 on: March 03, 2018, 03:22:46 pm »
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][+][-]
  1. {
  2.  Thanks to wp!
  3.  See: http://forum.lazarus.freepascal.org/index.php/topic,40311.0.html
  4.  By Totya, 2018.03.03
  5.  v1.1
  6.  Remark: Character positions needed for StartPos, StrLength parameters
  7. }
  8. function TExcelKez.GetSameFontStyleOrNot
  9.                     (const Book: TsWorkBook;
  10.                      const Cell: PCell;
  11.                      const ReqFontStyle: TsFontStyle;
  12.                      const StartPos, StrLength: integer): boolean;
  13.  
  14. var
  15.   fmt: TsCellFormat;
  16.   rtp: TsRichTextParam;
  17.   myFont: TSFont;
  18.   FontStyleSame, LastFontStyleSame: boolean;
  19.   EndPos: integer;
  20.  
  21. begin
  22.   fmt := Book.GetCellFormat(Cell^.FormatIndex);
  23.   FontStyleSame:= ReqFontStyle in Book.GetFont(fmt.FontIndex).Style;
  24.  
  25.   Endpos:= StartPos+ StrLength -1;
  26.  
  27.   for rtp in Cell^.RichTextParams do
  28.   begin
  29.     if rtp.FirstIndex > EndPos
  30.       then break;
  31.  
  32.     myFont:= Book.GetFont(rtp.FontIndex);
  33.  
  34.     LastFontStyleSame:= FontStyleSame;
  35.       FontStyleSame:= ReqFontStyle in myFont.Style;
  36.  
  37.     if rtp.FirstIndex > StartPos then
  38.       if LastFontStyleSame = false then
  39.         begin
  40.           FontStyleSame:= false;
  41.           break;
  42.         end
  43.    end;
  44.  
  45.   Result:= FontStyleSame;
  46. end;      
  47.  
« Last Edit: March 09, 2018, 08:23:06 pm by totya »

totya

  • Hero Member
  • *****
  • Posts: 720
Re: [solved by wp] Read font style from cell between char position
« Reply #3 on: March 03, 2018, 06:58:33 pm »
Seems to me real (char count) positions needed of StartPos, EndPos parameters, for example for UTF8 strings needed UTF8Pos/UTF8length etc. functions...

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: [solved by wp] Read font style from cell between char position
« Reply #4 on: March 03, 2018, 10:04:18 pm »
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

  • Hero Member
  • *****
  • Posts: 720
Re: [solved by wp] Read font style from cell between char position
« Reply #5 on: March 03, 2018, 11:19:46 pm »
This is no problem for me, thank you again! :)

 

TinyPortal © 2005-2018