Column headings are mismatched when export to excel (Data Only)

[Former Member]

Hi All,

We have an old Crystal Web Portal designed using CE10 in VS 2003. The crystal viewer shipped with VS 2003 exports to excel (Data only) in correct order where all the columns headers are matched with the corresponding data.

We have re-written the entire new web portal in VS 2008 with using CE10 on windows 2003 server. Everything works we can view the report, page navigation, export to PDF, export to MS Excel 97-2000. However when export to MS Excel 97-2000 (Data only) option, the column headings are mismatched with the corresponding data. I don't understand what changed between VS 2003 Crystal Viewer and VS 2008 Crystal Viewer. I think this has to be the crystal viewer problem because I'm exporting from the viewer tool bar.

Here is the API referencing in the code:

In Visual Studio I'm referencing all CE10 10.0.3300 version api's. Howerver since Crystal Viewer with VS 2008 is newer and has 10.5.3700 version I'm using CrystalDecision.Shared and CrystalDecision.Web are version 10.5.3700. These seems to have no problem. The entire web portal works fine.

More about Web Portal:

-User is validated upon login with CE10

-use selects the report from the list

-Report ID is then passed to reportdocument

-reportdocument API loads the report and

-Viwer.reportsource= reportdocument.reportsource

We cannot update Crystal Enterprise 10 right now. I'm in the middle of deploying new web portal with VS 2008. I have to resolve the Export excel (Data only) option and have the column headers matched with the data in excel. I have tried everthing.

Can some help here.

thanks

[Former Member · Accepted solution]

it may have something to do with your export, but when i export in 2008 to xls i have to ensure the 1st column is flush with th left margin or it throws everything off.

if you are exporting based upon page headings or details you need to make sure they are aligned the same or it will not export correctly. test one report to see if that helps

[Former Member · Accepted solution]

Thanks but I don't understand. I have the same report in .rpt extension. It works perfectly alright if I use the old Crystal Basic for VS 2003 viewer control ver: 10.0.3300. The output excel (data only) has no mismatch column at all. However with crystal basic for vs2008 viewer it is screws up.

As you stated I tried the first column by indenting left, right and center but negative result. Also if I run a report where column headers are not wrapped (i.e., single row) the output is much better and not a single column is mismatched. It only happens when some columns names are big and they are wrapped underneath. How do we solve this issue because I have over 100 reports which are like that? I cannot simply go and fix every report it's not feasible.

Thanks

[Former Member · Accepted solution]

Crystal and excel have never been very good together. Data only excludes all formatting but can be very user UNfriendly.

You must use vertical guides and lock each field/cell together on the vertical guides, there must be no spaces between cells. Equally all field/cells must be same height and clamped into section top and bottom otherwise spurious rows will be introduced.

Any Header text must be extended and tied to next available vertical guide otherwise they will introduce another spurious column.

Ian

[Former Member · Accepted solution]

Thanks for the reply.

But I still don't have the answer as of why this behavior was introduced in Visual Basic for 2008 Crystal Viewer control. As I explaned earlier, I have the same web portal and running same report on Visual Basic for 2003 Crystal Viewer control. If I export to excel (data only) from the VS 2003 viewer, column headings and corresponding data are exported to excel fine without any problem. I don't have to do anything in report designer. So why this behavior was introduced in VS 2008 Viewer control or should I say its a bug in VS 2008 Viewer control.

I tried everything in the report designer such as creating Vertical Margin bars, locking the cell, changing cell to text object instead of field headings and etc. But negative result. When I export from viewer control column headers are all screwed up. So it does not work.

thanks

[Former Member · Accepted solution]

Sorry I have no experience with Visual Basic 2003 or 2008.

Do you have a data only option for 2008?

Ian

[Former Member · Accepted solution]

Thanks Ian.

Let me elaborate more on the .net versions.

The web portal was originally written using .Net Visual Studio 2003 with CE10. Visual Studio 2003 IDE comes with Crystal Basic for VS 2003 viewer control ( Version 10.0.3300). The reports are viewed using this viewer control on the web page. The crystal viewer control (i.e,,control is same for application or web) has tool bar where there is a export button. We click on export button and we get following format options:

-Crystal Report(RPT)

-Acrobat Format (PDF)

-MS Word

-MS Excel 97-2000 (this is complete copy of the report)

-MS Excel 97-2000 (Data only)

-Rich text format

So When I export in old web portal using VS 2003 viewer control, data only option does not screws up column headings.

We have re-written portal to use latest .Net Visual Studio 2008 with CE10. Visual Studio 2008 comes with Crystal Basic for VS 2008 crystal viewer control. The version is 10.5.3700. When I export to excel (data Only) option from Viewer control, the column headings are mismatched.

In conclusion in VS 2003 Crystal Viewer control it works and in VS 2008 Crystal Viewer control it mismatched column headings.

Let me know if you need more info.

[Former Member · Accepted solution]

Sorry but I really cant help, this looks like a bug which should be logged with SAP.

Unless Don Williams who often comments on posts can help.

Ian

[Former Member · Accepted solution]

I also have another issue with the same crystal viewer control version 10.5.3700. When I click on print, the print settings are not loaded from the report itself. Hence print settings always default to OS Print settings. This is kind of weird and I cannot have user changing print setting everytime they click on print.

do you think the control has bug for both Export to Excel (Data Only) column headers are mismatched and unable to load report print settings or its purely a code error. Here is what I do behind the View page where the reports are processed:

protected void Page_Init(object sender, EventArgs e)

{

report.ItemID = (string)Session["report_id"];

if (report.ItemID != null)

{

if (!this.IsPostBack)

{

try

{

mPreviousPage = (string)Session["previousPage"];

crystalReportDocument = new ReportDocument();

crystalReportDocument.Load(report.InfoObject, report.InfoObject.ParentInfoObjects.InfoStore.EnterpriseSession);

//report.InfoObject.ParentInfoObjects.InfoStore.EnterpriseSession

this.Viewer1.ReportSource = crystalReportDocument.ReportSource;

ReportParams = (CrystalDecisions.Shared.ParameterFields)Session["params"];

this.Viewer1.ParameterFieldInfo = ReportParams;

Session["report"] = crystalReportDocument.ReportSource;

}

catch (Exception ex)

{

lblError.Text = ex.Message + "Report Name: " + report.InfoObject.Title.ToString();

}

}

else

{

mPreviousPage = (string)Session["previousPage"];

this.Viewer1.ReportSource = (ReportSource)Session["report"];

}

}

}

[former_member292966 · Accepted solution]

Hi,

A question first about CE. When you export the report in CE with Data Only format, does it export properly? I just want to confirm where the exporting problem exists. If the exported file is not aligned when exporting directly from CE then we need to look at the report.

If the report exports fine then we can have a closer look at Visual Studio. Based on the code you have below it looks like you are just running an existng instance and not creating or scheduling a new one. When the report comes up in the viewer, are your users pressing the export button or are you coding that somewhere?

It's possible the Excel export drivers have changed between versions but I've worked with them and haven't had any real issues that haven't come back to how the report is designed.

Exporting the Excel is always very tricky because Crystal determines the columns based on the size of the object and the amount of space between the objects. All titles must be the same length and the detail. If they are different then you will end up with Merged columns in Excel.

I use the Grid Lines and have almost no spaces between my columns. If you need a column a certain width, stretch you fields to that size, do not leave a large between the columns. I can't stress this enough because it will mess you up all the time.

When exporting to Excel Data Only, set the Column width to a section that has the columns in the proper width. If the report has different sections with different field lengths, I have a hidden section with empty text boxes all lined up properly that I point this to. In most cases I just set it to the section with the most detailed information. Play with the other options as well.

From your application, try the same options then you can see how the viewer will handle the formatting.

Exporting to Excel is very complicated and must be precise. I've had to export both as regular Excel and with Data Only because of different requirements. My standard design practice now is how will this report look if I had to export it to Excel because most of my users preferred Excel than viewing it.

Hope this helps,

Brian

[Former Member · Accepted solution]

Hi Brian,

thanks for you reply.

I don't think its a report designing issue. As I stated earlier in my post, we already have crystal web portal in VS 2003 using 10.0.3300 version along with CE10. These are the same reports we use with Old web Portal. When we click on Export to Excel (Data Only) in Crystal Basics for VS 2003 viewer control on web, the excel file looks correct. There are no mismatch in Column headings.

I have re-written the same old web portal using Net 3.5 in VS 2008. With Crystal Basics for VS 2008, when we export using Viewer it exports mis-match column.

Further, when we print using Crystal Basics for VS 2008 Viewer control, report print settings are not loaded in the print dialog. In fact, print settings are default to OS Print settings. For instance when we save report from the designer to CE10 database, we specify the print settings such the Paper Orientation and etc. When I click on print on new viewer control, the Print settings are default to OS print settings instead of using from the report. However in VS 2003 viewer control, the print settings are loaded from the report and not from the OS.

We have 100 over reports designed using Crystal Designer 10. In VS 2003 viewer control, both issues describes above are not present. It works perfectly alright. So I think there is something wrong with VS 2008 Viewer control or the way I have use to load report using ReportDocument API.

These issues are dead lock from my project. I need help if someone has seen or have any better ideas.

Thanks

[former_member292966 · Accepted solution]

Hi,

Sorry for dragging this on and getting things frustrated but there's one more test I need you to do to see if it is VS2008 that's got the problem.

Are you using Crystal Reports for Visual Studio or do you have an actual copy of Crystal Reports?

If you have a copy of Crystal Reports newer than version 10 try this:

Run the report in CE and save it as a Crystal Report.

Open the saved report in Crystal.

Export it to Excel Data Only.

Do the columns line up?

If they don't then we know it really is a Crystal problem.

If you have Crystal Reports for Visual Studio:

Run the report in CE and save it as a Crystal Report.

Create a project in VS2008 and add the report to the project.

Open the report in the report designer and preview the report.

Export the report to Excel Data Only.

Do the columns line up?

Again if they do not then it really is a Crystal problem and nothing in your code can fix it.

The only real way to deal with this is to have a closer look at the reports. I understand you have lots of reports and they worked in an earlier version but I've upgraded reports between versions as well and not have this problem once I figured out how the Excel export driver works and what it expects.

Sorry, not good news.

Brian

[Former Member · Accepted solution]

Thanks for the reply Brian,

The reports are designed using Crystal Report Designer 10. I followed the steps you described by openning up the report, previewing the report in designer and then exporting to excel (Data Only) option. When I select Data Only option and click Ok I get the Excel Format Option dialog. If I leave everything by default and click export, the columns are mis-matched. If I select custom and select Export object Formatting, Export Images, Use worksheet functions for summaries, maintain relative object position, maintain column alignment, export page header and page footer, and simplify page headers, then all columns are in correct order.

I have another bigger issue as I described earlier in my post. The print settings are not loaded from the report when user clicks on the print from the VS 2008 Crystal Viewer on web page. Do you have any idea why its not loading print settings from report? The reportdocument object [if seen in "Add Watch" window] does include print options object that has all the print settings loaded from the report. However this settings are not passed to the Crystal viewer control. Is it possible the new control does not have this feature?

thanks

[Former Member · Accepted solution]

Brian,

Here is what I have setup:

Crystal Enterprise 10 installed on the Win 2003 server and on my development box.

Crysal Report Designer 10

Crystal Basics for Visual Studio 2008 ( Comes with Visual studio Professional 2008).

Hope this help.

thanks

[former_member292966 · Accepted solution]

Hi,

The report saves the printer information and if it can connect to the exact same printer then the print options will get passed to the printer.

If the report is being sent to a different printer, Crystal doesn't know if this new printer will support the same settings, ie. duplex or color. In our case we were setting the report to print duplex printing and landscape. When our application went to print it actually crashed because some of the printers didn't support duplex.

What we did is disable the print button on the viewer and added the PrintDialog control to our form. Use the ReportDocument.PrintOptions method to set the properties for the PrintDialog control.

var printDialog1 = new PrintDialog();
printDialog1.UseEXDialog = true;
printDialog1.AllowSomePages = true;
if (myLink.Parent.SavedPrinterSettings != null)
	printDialog1.PrinterSettings = myLink.Parent.SavedPrinterSettings; //Use any saved printer settigns
	printDialog1.PrinterSettings.Copies = Convert.ToInt16(copies);
	dr = printDialog1.ShowDialog();
	if (dr == DialogResult.OK)
	{
	myLink.Parent.SavedPrinterSettings = printDialog1.PrinterSettings;	//Save the latest printer settings
	nCopy = printDialog1.PrinterSettings.Copies;			//Get the number of Copies
	sPage = printDialog1.PrinterSettings.FromPage;			//Get the number of Start Page
	ePage = printDialog1.PrinterSettings.ToPage;				//Get the number of End Page
	myReport.PrintOptions.PrinterName = printDialog1.PrinterSettings.PrinterName;	//Get the printer name
	myReport.PrintOptions.CustomPaperSource = printDialog1.PrinterSettings.DefaultPageSettings.PaperSource;
	if (printDialog1.PrinterSettings.Duplex == System.Drawing.Printing.Duplex.Vertical)
		myReport.PrintOptions.PrinterDuplex = PrinterDuplex.Vertical;
	else if (printDialog1.PrinterSettings.Duplex == System.Drawing.Printing.Duplex.Horizontal)
		myReport.PrintOptions.PrinterDuplex = PrinterDuplex.Horizontal;
	else if (printDialog1.PrinterSettings.Duplex == System.Drawing.Printing.Duplex.Simplex)
		myReport.PrintOptions.PrinterDuplex = PrinterDuplex.Simplex;
	}

Hope this helps,

Brian

[Former Member · Accepted solution]

thanks brain,

My problem is if I add custom print dialog and load print option from the report document, the actually printing will happen on the server and not at the client machine. Our internal users have different sets of printers setup to print report in different format. Hence this option is not going to be feasible given, the print dialog code is going to run on the server.

[Former Member · Accepted solution]

Thanks brian,

My problem is that I'm working on ASP.net 3.5. So if any code I write it will run on the server. Hence if I add a customize print dialog and load print options from reportdocument, the printing will happen on the server and not at the client machine. This will be a huge problem because lot of our internal users have different sets of printer. Futher the server is also not at the same building. This will not be feasible given, the printing will happen on the server. The viewer toolbar runs it on the client side and gives us flexibility of different printers for different users.

thanks

[former_member292966 · Accepted solution]

Hi,

The problem isn't you can't print the problem is the application will not be able to see or connect to the User's printer. You need to be able to capture the User's printer collection and pass it to your application on the server.

Somehow, you need to get the User's local information to the server. That unfortunately is out of the Crystal's realm. Once you do that then the report will be able to use the printer.

Good luck,

Brian

Edited by: Brian Dong on Jun 20, 2011 10:57 PM

[former_member11696]

CE 10 is no long in support and all patches have been removed for downloading. Upgrading is the optioon option now.

[Former Member]

Hi All,

We have built Crystal web portal on Windows 2003 with .net 2008 version using Crystal Enterprise 10 version. We have similar Crystal Web Portal on Windows 200 using VS 2003 with Crystal Enterprise 10. The old web portal has no problem export to Excel (in Data only) format. The Excel records are exported alogn with appropriate column headers.

However, with new Web Portal using same CE 20 version APIs column headings are mistmatched.

Ok after a long reasearch I found out the problem. It appears that CE10 Enterprise version has gone through many Hot fixes. There is a hot fix for this particular fix and I cannot find in SAP download forum.

does any know here all the hot fixes, patches and SP for CE10 and the location where I can download it?

I have this problem since many many months and I cannot move forward with new web portal if we don't have Export to Excel Data only issue resolved.

Thanks in advance.