multi-line memo with SetCellValue

[Verhagen_Michael]

When memo data contains CR/LF, SetCellValue truncates output after one line but I need the cells to contain the word-wrapped data.  Am I using the wrong method?  Is there a way to format the cell ahead of time such that SetCellValue will give the desired behavior?

[adrian]

Hi,
If the cell has word-wrapping or not is defined by the format of
the cell. So in order to have the cell display with word-wrap, you need
to set the cell format to wordwrap. You can set both the format and
value of the cell in a single call. For example:

  fmt := xls.GetFormat(xls.GetCellFormat(row, col));
  fmt.WrapText := true;
  xls.SetCellValue(row, col, memo, xls.AddFormat(fmt));

Some extra notes:
1)The
character for separating lines in Excel is always #10 (LF), even in
Windows. So you might want to replace the CR/LF by LF before entering
the text. If you don't it will likely work anyway in modern Excels
(because the CR won't show), but in (very) old Excels the CR will
display as an empty square.

2)If you use SetCellFromString
instead of SetCellValue, it will automatically set WrapText to true if
the line contains CR/LF, and remove the CRs too. But it will alsow
convert a text like "1" into a number like 1, or text that can be a date
to a date.

The rationale is that SetCellValue does exactly what
you ask it to do. It won't change the cell format (wrap text) if you
don't do it, and it won't remove characters like CR either.
SetCellFromString behaves more like Excel itself: It tries to be "smart"
and sets the cell format to word-wrap if the line contains line feeds,
removes the CR it knows are not needed, converts the text "1/1/2000" to
the date 1/1/2000, etc. All just like Excel does.

I normally
prefer to use SetCellValue and be explicit about what I want to do, and
use SetCellFromString only when you have user entered data that you want
to convert to the "best" representation. SetCellFromString is not only
slower than SetCellValue because all the guessing it has to do, but also
it is more unreliable. For example, it might convert 1.2.3 to a date
feb/1/2003 ina german locale (where . is the date separator).