Recent

Author Topic: [SOLVED] FPSpreadSheet - Newline in WriteText  (Read 3266 times)

JD

  • Hero Member
  • *****
  • Posts: 1848
[SOLVED] FPSpreadSheet - Newline in WriteText
« on: April 17, 2017, 03:52:04 pm »
Hi there everyone,

I create Excel worksheets using FPSpreadSheet and most of the cells are of the format below


11:30 - 13:30: Travail divers; 15:00 - 18:00: Linguistique;


The semi-colon ';' is used as a delimiter in this context. Is there any way to write the text into the cells in such a way that when the semi-colon (or any chosen delimiter e.g '|') is encountered, a new line is created and the end result as shown below is what will be written to the cells:


11:30 - 13:30: Travail divers
15:00 - 18:00: Linguistique


I'm thinking of something like a new WriteCell method or a WriteChar method.

Thanks,

JD
« Last Edit: April 18, 2017, 12:28:10 pm by JD »
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: FPSpreadSheet - Newline in WriteText
« Reply #1 on: April 17, 2017, 07:19:07 pm »
I won't add another WriteXXX method for this simple use case - there are already too many overloaded methods...

Why don't you split the string yourself at the semicolon and add the parts individually?
Code: Pascal  [Select][+][-]
  1. program project1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. uses
  6.   Classes, SysUtils, fpspreadsheet, fpstypes, xlsxooxml;
  7.  
  8. var
  9.   book: TsWorkbook;
  10.   sheet: TsWorksheet;
  11.  
  12.   procedure AddMultiCell(ARow, ACol: Integer; AText: String);
  13.   var
  14.     strArray: TStringArray;
  15.     i: Integer;
  16.   begin
  17.     strArray := AText.Split(';');
  18.     for i := 0 to High(strArray) do
  19.       if strArray[i] <> '' then
  20.         sheet.WriteText(ARow + i, ACol, Trim(strArray[i]));
  21.   end;
  22.  
  23. begin
  24.   book := TsWorkbook.Create;
  25.   try
  26.     sheet := book.AddWorksheet('Sheet 1');
  27.     AddMultiCell(0, 0, '11:30 - 13:30: Travail divers; 15:00 - 18:00: Linguistique;');
  28.     book.WriteToFile('test.xlsx', sfOOXML, true);
  29.   finally
  30.     book.Free;
  31.   end;
  32.  
  33. end.

JD

  • Hero Member
  • *****
  • Posts: 1848
Re: FPSpreadSheet - Newline in WriteText
« Reply #2 on: April 18, 2017, 11:20:05 am »
Hi there wp,

Thanks for your reply. I understand your reluctance to add more overloaded methods.  :D

I forgot to add that it should be in the same cell on the same row. In LibreOffice/OpenOffice, I had to type CTRL+ENTER in the position after the delimiter ';'.

In Excel, it is ALT+ENTER.

Thanks,

JD
« Last Edit: April 18, 2017, 11:58:39 am by JD »
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: FPSpreadSheet - Newline in WriteText
« Reply #3 on: April 18, 2017, 12:21:05 pm »
Then replace the semicolon by FPS_LINE_ENDING (which is #10) defined in fpstypes (the following code also removes the space after the semicolon), activate word-wrap, and set column width and row height appropriately.

Code: Pascal  [Select][+][-]
  1.   procedure AddWrappedCell(ARow, ACol: Integer; AText: String);
  2.   var
  3.     strArray: TStringArray;
  4.     i: Integer;
  5.     s: String;
  6.   begin
  7.     strArray := AText.Split(';');
  8.     s := trim(strArray[0]);
  9.     for i := 1 to High(strArray) do
  10.       if strArray[i] <> '' then
  11.         s := s + FPS_LINE_ENDING + trim(strArray[i]);
  12.     sheet.WriteText(ARow, ACol, s);
  13.     sheet.WriteWordwrap(ARow, ACol, true);
  14.   end;
  15.  
  16. begin
  17.   book := TsWorkbook.Create;
  18.   try
  19.     sheet := book.AddWorksheet('Sheet 1');
  20.     AddWrappedCell(3, 0, '11:30 - 13:30: Travail divers; 15:00 - 18:00: Linguistique;');
  21.     sheet.WriteColWidth(0, 50, suMillimeters);    // column width: 50 mm
  22.     sheet.WriteRowHeight(3, 2.5, suLines);        // row height: 2 lines plus some margin
  23.     book.WriteToFile('test.xlsx', sfOOXML, true);
  24.  
  25.   finally
  26.     book.Free;
  27.   end;
  28. end.  

JD

  • Hero Member
  • *****
  • Posts: 1848
Re: FPSpreadSheet - Newline in WriteText
« Reply #4 on: April 18, 2017, 12:27:43 pm »
Wonderful. Works perfectly. Thanks a lot!  :D
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

 

TinyPortal © 2005-2018