Excel Export: Only partially wrapping text in a paragraph

[JStandard]

Greetings,

CR Version 9, SP4:

I have a report with Crystal Parameters where the user enters in a large block of text, usually over bit over 1000 characters. Since the block is variable, I have my parameter field on the design view with "Can Grow" checked.

I run the report and in Crystal Reports the text wraps and is formatted exactly as desired. When I export to Excel, the text will partially wrap, meaning it stops wrapping after a certain point.

I've played around with the wrapping and nailed it down to two possible factors:

- The Excel Export does not wrap the text after 8 lines of wrapped text
- The Excel Export does not wrap after a certain amount of characters.

This behavior is slightly difficult to describe, but very easily duplicated, using the following method:

- Create a new blank crystal report, no database connection needed.
- Create a Discrete Value string parameter (basically, you can probably leave everything to what it defaulted to). I will call it "txtParam"
- Place "txtParam" in the details section in design view, drag it out until it fills the length of the design view.
- Right Click -> Format Field -> Under "Common" tab check "can grow"
- Run the Crystal Report and paste the following text as the value for "txtParam" (sorry, it has to be a lot of text for this to work):

Black Flag has often been considered America's first hardcore band, beginning in 1978. By creating the still-surviving SST label (although it had recently declared bankruptcy), Black Flag single- handedly gave the West Coast hardcore scene international prominence. by the time their first EP Jealous Again came out in 1980, Black Flag had begun touring enough to become a major attraction in nearly every city and inspire others to get into the scene. While Black Flag and their peers wrote cutting songs like "T.V. Party," about commercial culture and middle class suburban life, the sound they made was predominantly a joyful noise, and they rarely preached to their fans.Black Flag has often been considered America's first hardcore band, beginning in 1978. By creating the still-surviving SST label (although it had recently declared bankruptcy), Black Flag single- handedly gave the West Coast hardcore scene international prominence. by the time their first EP Jealous Again came out in 1980, Black Flag had begun touring enough to become a major attraction in nearly every city and inspire others to get into the scene. While Black Flag and their peers wrote cutting songs like "T.V. Party," about commercial culture and middle class suburban life, the sound they made was predominantly a joyful noise, and they rarely preached to their fans.Black Flag has often been considered America's first hardcore band, beginning in 1978. By creating the still-surviving SST label (although it had recently declared bankruptcy), Black Flag single- handedly gave the West Coast hardcore scene international prominence. by the time their first EP Jealous Again came out in 1980, Black Flag had begun touring enough to become a major attraction in nearly every city and inspire others to get into the scene. While Black Flag and their peers wrote cutting songs like "T.V. Party," about commercial culture and middle class suburban life, the sound they made was predominantly a joyful noise, and they rarely preached to their fans.

- Everything should look fine in the Crystal Report after you run it, all of your text is there and formatted properly

- Export to Excel 97-2000

Now in Excel you will notice any text beyond row 12 (or some other row, depending on your settings) is not wrapping properly. It wraps fine and dandy up to row 12, but the last line does not wrap and just cuts off.

I saw a Crystal KBase article on something similar,

http://support.businessobjects.com/library/kbase/articles/c2014348.asp,

but I am of the latest Service Pack (SP4), and this issue is still not resolved.

Let me know if you have any questions or I was not descriptive enough, I'll be glad to do what I can.

Thanks!

Jeff.

 

[synapsevampire]

The Service Pack is the obvious solution, however there may be a maximum number of characters allowed, a standard problem is the 254 limit, which might describe your scenario.

I just tried thsi in CR 10 and a 1078 character parameter exported to Excel without being truncated.

It may be that you'll need to break up the parm into multiple parameters, or try breaking the parameter up into multiple formulas and then drop them into a text object:

Here's an example of 3 formulas, make as many as you feel required:

left({?MyParm},254}

mid({?MyParm},255,254}

mid({?MyParm},509,254}

mid({?MyParm},763,254}

You get the idea. Now just drop them all into a Text object and see what happens.

-k

 

[JStandard]

synapsevampire,

I am of the current service pack (4) for CR 9, so the formatting problem addressed apparently was not the one I am experiencing.

I understand exactly what you are getting at with your example to break up the text field, my problem is that the text field in general, regardless of whether it is a parameter or not, stops word wrapping at the same place.

You can actually duplicate this behavior by cutting and pasting the sample text in my post above into a text field and just straight exporting the report. The text cuts off in Excel, though it is not quite being truncated since all of the text is there, due to the word wrapping stopping after a certain point.

It appears to cut off after 1,081 characters. I've gone into Excel and only copied the visible text, before it stops-word wrapping, adding one characters (literally the whole block of text and just appending a number one on the end, making it 1,082 characters), and it cuts off in the exact same place.

I could understand if the rest of the text was not showing up in Excel, but it is, it is just not word-wrapping it after certain point.

I'll play around with the idea of breaking it up, though I really do need the text all in one block as after someone exports it, they may need to make changes and I do not want the text separated to confuse the users.

Thanks though, interesting idea to break up the parameter.

Jeff.

 

[beanbrain]

Jeff:

I tried your suggestion. I don't think this is a Crystal problem at all. To prove this I took your paragraph and pasted it into a text file. I then imported the text file into excel and have the exact same problem. Crystal was left entirely out of the loop.

I then tried "merge and wrap", "text formatting", and even "Data Validation" > "Text Length" > "Less than or Equal to" > 20,000. The clipping remains.

I think synapseVampire's suggestion may be your best route on this one.

 

[JStandard]

I just implemented synapsevampire's parameter breakup suggestion, unfortunately still no dice. (Not to down play it, I have a feeling it will be of use to me yet)

I dropped them all into one text object and I believe that is still the problem, the fact they are all in one text object still exports the block of text as a whole.

I've checked out quite a few Excel forums since I started to catch wind it might not be Crystal and I am definitely not the only person who has encountered this problem, though none of the forums offered a solution and summed it up as an Excel limitation.

You are right beanbrain, it is not a Crystal limitation but an Excel one, where Excel stops wrapping text after a certain number of characters. (allegedly 1024, though that doesn't seem quite so accurate as I believe I've had as many as 1170 visible before the clipping)

Printing shows the text clipping as well, and since the text is all one block, I cannot think of a pretty way to do this.

Something I am playing around with right now is essentially "tokenizing" the string to break it up into two paragraghs at around the 1000 character mark. I am quite a bit against this, but will probably have to deal with it at this point.

I create 2 formulae, Text Part 1, Text Part 2, where

Text Part 1 = left({?Parameter}, instrrev(left({?Parameter}, 1000), ". ")) //This gives me all full sentences within the first 1000 characters, provided someone doesn't use a ". " for anything else. Flimsy, I know... I still don't like it.

Text Part 2 = mid({?Parameter}, instrrev(left({?Parameter}, 1000), ". ") + 2) //This gives me all the rest of the text, since it will never be over 2000 chars as a whole, I'm safe. I add 2 so my next line starts evenly with the previous.

This works, as when I export into excel it looks at the text as two different cell sets (which it is, since I have two different, separate formulae on the design view), I guess I do not like how flimsy it is, since it is dependent upon the user only using ". " for sentence breaks, and does not handle errors.

Still stabbing away at it though, thanks so much for the help fellows, I at least came to something that works, easily broken unless I inform the users of the ground rules, but it works.

Jeff.

 

[beanbrain]

Well at least the ". " (<-includes a space after the period) makes it a bit more bullet-proof. After searching Excel Help (Excel 2002 SP 1) I'm really surprised that this does not seem to be addressed anywhere. I'd say something about Microsoft, but I think just about everything that can be said has been said.