After exporting Xls reports to Pdf format I got all the "multiline" cells in the spreadsheet flattened.
For example, a document like this:
-----------
text text |
-----------
line1 |
line2 |
-----------
After exporting in pdf
text text
line1 ?line2
I suppose that during the pdf exporting all the CRLF are misinterpreted as invalid characters (they are shown as squares in the actual pdf file).
Here is the code I use for generating the pdf:
UnicodeString pathpdf;
TXlsFile* xls = new TXlsFile("abc.xls", true);
try
{
TFlexCelPdfExport* pdf = new TFlexCelPdfExport(xls, true);
try
{
pdf->FontEmbed = TFontEmbed::None;
pdf->FontMapping = TFontMapping::ReplaceAllFonts;
pathpdf = "abc.pdf";
pdf->Export(pathpdf);
}
__finally
{
delete pdf;
}
}
__finally
{
delete xls;
}
return pathpdf;
Hi,
There are many possible causes here:
1)The most likely: The cells don't have "Wrap text" = true. If in Excel, you write "hello" Alt->Enter "world", Excel will set automatically wrap text to true. But if you are filling the cells with FlexCel, you need to manually do it. If in Excel you set wrap text to false, you'll see it goes all to one line too. Excel 2003 will show the same squares, Excel 2007 will show nothing, but text will not wrap (as expected)
2)Note that the "enter" character in Excel isn't CRLF, but LF (char 10) only. If filling the xls file with reports, then FlexCel will automatically convert the CRLF to LF. But if filling with the API, FlexCel will enter in the cell just what you pass to it, so you need to pass LF alone. But while the CR will show as squares, text will still wrap it wrap text is true. So that's what I think you have wrap text to false.
The first thing to verify then would be how Excel shows the file: Open abc.xls in Excel and see if it wraps the text. If it does in Excel, please send me the file abc.xls to adrian@tmssoftware.com and I'll investigate it further. If it doesn't in Excel either, then you need to right click the cell, and set "wrap text" to true.
FlexCel fully supports line breaks when exporting to pdf, and that works fine as far as I know. So my guess is that the problem is in abc.xls
I was using Apache OpenOffice to show the file (I have Excel 2003 installed on another computer). In OO the file was fine, so I was fooled.
Wrapping the text did the trick! PDF are fine! Thank you.
But now that I open the xls I see squares so have another problem.
You say that FlexCel reports convert automatically CRLF to LF, so why my report still have CRs?
The text is generated from a User Defined Function:
<#GetText(MemoComment)>
GetText reads the text (UnicodeString with CRLFs), converts it to a Variant, calls the TCellValue constructor and then casts it to ReportValue... may be some type information is lost during this process? I can paste the full source but I don't want to spam the forum..
For now I work around this problem by ensuring that the string does not contain CR (StringReplace).
Thanks Adrian.
Hi,
First of all, I wonder why so many conversions? Is this C++ builder? (where indeed conversions have to be done manually). In Delphi this should be done all automatically, and you should never need to convert to a variant. You can just return the string.
That is, you could have the function to be:
function TOrdersImp.Evaluate(
const parameters: TFormulaValueArray): TReportValue;
begin
if Length(Parameters) <> 0 then raise Exception.Create('Bad parameter count in call to Orders() user-defined function.');
Result := 'my text string';
end;
No need to do any casting, TReportValue converts from and to a string automatically.
Now, this was just a side comment. The problem here is that FlexCelReport only normalizes the text when directly entering the text, like
<#MemoComment>
If you are using an user defined function, FlexCel doesn't do anything, and trusts you to return the right string. This is done this way because:
1)For tags, like <#MemoComment> you don't have control over the line feeds, and it makes sense that we convert them to what is the most needed case (you want to remove those #13)
2)But for udfs, we don't do the automatic conversion, because you can do it easily yourself if you want to, but if we did it, there would be no way for an udf to return a #13, if for any reason you need it.
So, as a shor answer, jusr remove the #13 from the string before returning it in GetText. You ca just do a replace of #13 by #32 before returning the string.
Yes I develop in C++.
StringReplace it is.
Thanks Adrian.