?
Solved

Excel Export: Only partially wrapping text in a paragraph

Posted on 2005-03-22
7
Medium Priority
?
635 Views
Last Modified: 2006-11-17
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.

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.

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.
0
Comment
Question by:JMoridin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
7 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 13604505
Excel does have some limits on number of characters.  You might ask the question in the Excel forum.

mlmcc
0
 

Author Comment

by:JMoridin
ID: 13605985
mlmcc,

I tested and found this is an excel limitation where the number of characters Excel can display, though a particularly shoddy one since if I enlarge the cell even further, it will display it.  It just stops wrapping after a point.

I've developed a workaround for this using the following:

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.

Not the cleanest solution, but a few thoughts at least.

Jeff.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13607188
I agree it isn't the best solution but if the limitation is in Excel there isn't much you can do in Crystal to overcome it.

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13851924
PAQ
0
 

Accepted Solution

by:
PAQ_Man earned 0 total points
ID: 13940949
PAQed with points refunded (125)

PAQ_Man
Community Support Moderator
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question