Presentation Quality Straight Into Excel…. Can it be done???? 5

[Nuffsaid]

Hi Everyone,

So, the question is......

Presentation Quality Reports Delivered Straight Into Excel…. Can It Be Done????

The answer is NO!!!!

We as Crystal developers have all been asked to design reports that can be “Exported to Excel” in one form or another. Have any of us got it right yet? NO…, Excel is one of the hardest “Formats” to export to!!! Blank columns in-between what we expect to be “continuos”, ”Headers" out of alignment,… and God knows whatever else, They all seem to hamper us in our quest to provide end users with what they require.

But, one thing can be said for sure, “What You See (Exported to Excel) Is What You Get”!!!!

So, OK,…. Here comes the Tip……………..

This is what I do to satisfy my end users;

1) Going on the assumption that my end users are proficient with Excel…..

2) The question is... What do you “really need?” (It’s not going to be pretty!).

3) Then I create a Crystal report (in Excel Format) that delivers “Base” information that can be pasted into “Sheet2 of Excel”. (Excel user, make note of the “distortions”)

4) Creative formulas and LOOKUPS done in Sheet1 of Excel ,(the pretty one), that reference Sheet2 (the not so pretty one) .

5) So, now you have your “Raw Data in sheet 2, (that no one sees, “feeding sheet1” which everyone sees…. Looking pretty good!!!!)

My users seem to like this approach...it just took a bit of time to get them used to the idea…..

Had to hit them with a BIGGER STICK!!!

Nuffsaid.

 

[dgillz]

Well said Nuff, I thought it was just me all along and not excel. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[Ngolem]

I have been asked to make the report such that is can be exported to Excel....

It can be done without blank columns but it is a lot of work....(you insert text fields with a single blank space in approriate positions on each report line...I colorcode these so I know the reason why I did this later)

I also provide a "raw data dump" row/column so that the engineers can go nuts if they want to develope their own spreadsheets...they seem to like that approach too....but that kinda report is not for viewing of course

jim

 

[Guest_imported]

My problem is that some blank rows are inserted everytime I export this CR8-report to Excel..., I was wondering if there's any macro to delete all the blank rows in the Excel-formatted report ?.

 

[Nuffsaid]

Hi JPGURU,

Yes you could probably write some kind of macro to test if the cell is empty and if so delete the row, assuming that's what you want to do. But, if you follow my example posted above, why would you want to ? Nobody is going to see that sheet anyway!

Nuffsaid.

 

[Madawc]

I've used the spaces-in-page-header method, but also found a simpler alternative method:

a) When doing an export to Excel, a grey box called <Format Options> should be displayed.
b) <Format Options> contains an option, <Column widths based on objects in area>. Select this, if it isn't selected already.
c) The basis for column widths is normally <Page Header>. Change it to the detail line (or group footer, in my case).

The output was then based on the data, with the headers correctly adjusting themselves. It had been giving trouble because the header was a series of month totals over a separate number and percentage

Our system uses Crystal 8.5 exporting to Excel 8 Extended under Windows NT. Hope it also works elsewhere Madawc Williams
East Anglia
Great Britain

 

[chele]

I would really like to try Nuffsaid's way of exporting from the beginning of this thread...but I'm realy not sure of a few things:
1) How to create a Crystal report in Excel format?
2) How the export was linked to a pre-set Excel spreadsheet to reference sheet 2 with sheet 1?

 

[synapsevampire]

You can export to Excel, but nuffsaids approach wouldn't work for many users, especially since it involves copying and pasting (though you wouldn't have to)...

You can get a good export out of Excel, the problem is, you can't really get both a good display report AND a good export, so I sometimes create a dual purpose report which prompts the user for the intent (view or export), and suppress sections accordingly, each section having a display version (very readable/printable), and an export version (designed to get the best output when exporting).

Anotehr approach is to export using a CSV, which Excel opens just fine.

Hit the little envelope icon to export the report.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lyanch]

My trick to get &quot;excel nice&quot; reports consists of:

a) using guidelines.
b) aligning fields so that the &quot;end guideline&quot; of one field is the &quot;startguideline&quot; of the next.
c) making all the fields less than one inch wide.
d) don't leave any gap (top or bottom) in any sections
e) set all fields to not grow

This eliminates blank lines and columns in exports - however it causes the report to be pretty much illegible in any other format. I usually include in the header a line that states that the report is intended for export to excel for viewing.

Lisa

 

[CrystalReporter]

I think one of the big problems is that CR exports spatially and not logically.
I've heard that with version 8.5 Crystal introduced a new Excel export driver: crxf_xls.dll. This offers the original export option, but also a second &quot;Data only&quot; export option that is intended to fill in an Excel spreadsheet properly (&quot;logically&quot.

However, I am not seeing anything in the code reference that allows a programmer to actually specify this alternative format for exporting to Excel............

 

[khartman]

I have been looking for the code to modify the ReportViewer export options. My users waht to be able to export the report to Excel using the &quot;Data-Only&quot; option they see in the Format option under the Scheduler. Does anyone have a clu on how to &quot;add&quot; this export option to the ReportViewer export dropdown list?
Kenneth Hartman
Manager, EIS
Hughes Network Systems

http://www.hns.com

 

[trialbyfire]

Another little helpful hint for export to Excel. When using multiple row headers you can stop one header from &quot;sliding&quot; by simply increasing the text box size. Ex...
/Headings below
Customer Address City State Zip
Contact Phone Fax E-mail

In the above instance each header is a seperate text box. When exporting to excel many times the bottom line will slide to the left in the Excel spreadsheet. By simply increasing the text box size for the Customer Heading it keeps the remaining headers in their appropriate columns. Has worked for me.

 

[beardenb]

Regarding the Data Only option, this is for sure associated with crxf_xls.dll which ships with CR 9. If you plop the file in the Windows\Crystal folder (or WinNt\Crystal for NT-2000 users) on a machine with CR 8.5 , you will see the two options:

- MS-Excel 97-2000
- MS-Excel 97-2000 (Data only)

instead of the older options. Although it's a v9 dll it appears to work just fine with 8.5.

In a call with CD support last week the rep I spoke with seemed to think crxf_xls was going out with the latest hot fixes for 8.5, but I haven't verified. I have 8.5 and 9.0 installed on my machine and I've applied hot fixes recently, so I may have gotten it either way -- with 9 for sure.

Note that the export results for the normal Excel export (not the data only option) under crxf_xls are quite different from the prior export (with u2fxls) in that it uses more of a WYSISYG strategy. The tweaking tips in this thread still apply as you can still end up with a bunch of extra columns, and now more than ever, merged cells. But with some work, you can have a report that looks good and exports to Excel nicely.

Another benefit of using the newer export dll for CE users is your exports will be consistent between CR and CE since they're both using the same dll (if you keep up-to-date with fixes).

--Bearden

 

[SurfingGecko]

Excel can certainly be a most troublesome beast. The solutions mentioned here are all certainly worthy and Nuffsaid's approach is something I am going to look at. Pretty nifty.

The actual Crystal Report is an image file which [one reason] why exporting to Excel (a cell based format) can be tricky. How to splice and place the images. CD has changed this format several times trying to get something that is consistent for a wide user base.

Often I see reports with graphics and fancy stuff which do not lend itself well to an Excel export.
I like the idea of using guidelines to minimize 'white space' and only keep data on the report. In tricky cases, going to csv and opening this way is a valued option.

bla bla bla

We have a CD white paper listed that deals with this issue that may help out some users --

http://www.surfinggeckosoftware.com/crystal_white_papers.htm

May also want to have a look at the Export Limitations doc to see what the various formats are up against.


Cheers,

SurfingGecko

http://www.surfinggeckosoftware.com

Home of Crystal Ease

 

[Madawc]

For Crystal 8.5, there are some updated dll files, which were specially written to produce better output in Word and Excel. I was amazed at the improved output when they were installed by the Technical people in our department. You can find them at

http://support.crystaldecisions.com/updates/.

For their usefulness in word, see thread767-526388

Madawc Williams
East Anglia
Great Britain

 

[SurfingGecko]

The file you want to grab is the Database and Export driver update ---> dbexdrvrwin_en.zip

Here is the link if you haven't already been to the site --

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/dbexdrvrwin_en.zip

Cheers,

SurfingGecko

http://www.surfinggeckosoftware.com

Home of Crystal Ease