Using Excel as Data Source for Crystal Reports (with Java SDK)

[Former Member]

Hi,

I already tried to find similar topics here but was more or less unable to fix my problem with the suggestion I found so far.

I would like to create a report with a excel file as data source. All my current attempts fail with the following exception "com.businessobjects.reports.sdk.JRCCommunicationAdapter.if(719):  detected an exception: Error finding JNDI name (E:\SampleSalesData.xls)"

This is my current approach:

final ReportClientDocument boReportClientDocument = new ReportClientDocument();
boReportClientDocument.setLocale(Locale.US);
boReportClientDocument.newDocument();

final String fullPathToExcelFile = "E:\\SampleSalesData.xls"

final ConnectionInfo boConnectionInfo = new ConnectionInfo();
final PropertyBag boPropertyBag = boConnectionInfo.getAttributes();
boPropertyBag.put("Server Type", "Access/Excel (DAO)");
boPropertyBag.put("Database Type", "Excel 8.0");
boPropertyBag.put("Database DLL", "crdb_dao.dll");
boPropertyBag.put("System Database Path", "");
boPropertyBag.put("Session UserID", "");
boPropertyBag.put("PreQEServerName", fullPathToExcelFile);
boPropertyBag.put("Database Name", fullPathToExcelFile);
boPropertyBag.put("Server Name", fullPathToExcelFile);

boPropertyBag.put("JNDIOptionalName", fullPathToExcelFile);
boPropertyBag.put("Trusted_Connection", false);
boPropertyBag.put("Use JDBC", false);

boConnectionInfo.setAttributes(boPropertyBag);

// Set the DB Username and Pwd
boConnectionInfo.setUserName(null);
boConnectionInfo.setPassword("");

boConnectionInfo.setKind(ConnectionInfoKind.SQL);

// [...]

Once I have added the datasoruce I add a filed to the report and would like to export as PDF (this works fine).

The current StatckTrace looks like this:

2015-09-14 16:35:54,191 ERROR [MAIN] com.businessobjects.reports.sdk.JRCCommunicationAdapter.if(719):  detected an exception: Error finding JNDI name (E:\SampleSalesData.xls)
        at com.crystaldecisions.reports.queryengine.Connection.t4(SourceFile:3024)
        at com.crystaldecisions.reports.dataengine.dfadapter.DFAdapter.a(SourceFile:697)
        at com.crystaldecisions.reports.dataengine.dfadapter.DFAdapter.for(SourceFile:707)
        at com.crystaldecisions.reports.reportdefinition.ReportHelper.a(SourceFile:198)
        at com.businessobjects.reports.sdk.requesthandler.ReportViewingRequestHandler.long(SourceFile:957)
        at com.businessobjects.reports.sdk.requesthandler.ReportViewingRequestHandler.a(SourceFile:635)
        at com.businessobjects.reports.sdk.requesthandler.ReportViewingRequestHandler.int(SourceFile:672)
        at com.businessobjects.reports.sdk.JRCCommunicationAdapter.do(SourceFile:1943)
        at com.businessobjects.reports.sdk.JRCCommunicationAdapter.if(SourceFile:660)
        at com.businessobjects.reports.sdk.JRCCommunicationAdapter.a(SourceFile:166)
        at com.businessobjects.reports.sdk.JRCCommunicationAdapter$2.a(SourceFile:528)
        at com.businessobjects.reports.sdk.JRCCommunicationAdapter$2.call(SourceFile:526)
        at com.crystaldecisions.reports.common.ThreadGuard.syncExecute(SourceFile:102)
        at com.businessobjects.reports.sdk.JRCCommunicationAdapter.for(SourceFile:524)
        at com.businessobjects.reports.sdk.JRCCommunicationAdapter.int(SourceFile:423)
        at com.businessobjects.reports.sdk.JRCCommunicationAdapter.request(SourceFile:351)
        at com.businessobjects.sdk.erom.jrc.a.a(SourceFile:54)
        at com.businessobjects.sdk.erom.jrc.a.execute(SourceFile:67)
        at com.crystaldecisions.proxy.remoteagent.RemoteAgent$a.execute(SourceFile:716)
        at com.crystaldecisions.proxy.remoteagent.CommunicationChannel.a(SourceFile:125)
        at com.crystaldecisions.proxy.remoteagent.RemoteAgent.a(SourceFile:537)
        at com.crystaldecisions.sdk.occa.report.application.ds.a(SourceFile:186)
        at com.crystaldecisions.sdk.occa.report.application.ReportSource.a(SourceFile:1558)
        at com.crystaldecisions.sdk.occa.report.application.ReportSource.a(SourceFile:337)
        at com.crystaldecisions.sdk.occa.report.application.PrintOutputController.if(SourceFile:223)
        at com.crystaldecisions.sdk.occa.report.application.PrintOutputController.export(SourceFile:147)
        at com.crystaldecisions.sdk.occa.report.application.PrintOutputController.export(SourceFile:128)
        at com.crystaldecisions.sdk.occa.report.application.PrintOutputController.export(SourceFile:111)
        at EXPORT...

Any help on how to resolve this Problem is highly appreciated.

Thanks,

[former_member183750 · Accepted solution]

What version of CR? There is a new update available for CR for Eclipse here:

SAP BusinessObjects - SAP Crystal Reports, Version For Eclipse Download

see if that helps(?).

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow me on Twitter

[Former Member · Accepted solution]

Hi Ludek,

thanks for moving my question to the right place.

I'm using the SAP Crystal Reports for Java runtime components - Java Reporting Component (JRC).

Had downloaded them from here:

http://scn.sap.com/docs/DOC-29757

http://downloads.businessobjects.com/akdlm/crystalreportsforeclipse/2_0/crjava-runtime_12.2.218.zip

Will check ASAP if the updated Version will fix the Problem.

Thanks,

===

EDIT: Downloaded the latest version from http://downloads.businessobjects.com/akdlm/crystalreportsforeclipse/2_0/crjava-runtime_12.2.219.zip and unfortunately the Problem is still present 😞

[Former Member · Accepted solution]

Just another update:

When I use XML as Datasource everything works fine:

final IByteArray xmlDataByteArray = getResourceAsByteArray("/reportData/reportData.xml");
            final IByteArray xmlSchemaByteArray = getResourceAsByteArray("/reportData/reportData.xsd");

            final IXMLDataSet xmlDataSet = new XMLDataSet();
            xmlDataSet.setXMLData(xmlDataByteArray);
            xmlDataSet.setXMLSchema(xmlSchemaByteArray);


            // Create the DB connection
            final ConnectionInfo boConnectionInfo = new ConnectionInfo();
            final PropertyBag boPropertyBag = boConnectionInfo.getAttributes();

            // Set new table logon properties
            final String pathToSchemaFile = "E:\\reportData\\reportData.xsd";
            final String pathToXmlFile = "E:\\reportData\\reportData.xml";

            boPropertyBag.put("Server Type", "XML and Web Services");
            boPropertyBag.put("Database DLL", "crdb_xml.dll");
            boPropertyBag.put("Convert Mulitivalue to Table", false);
            boPropertyBag.put("Local Schema File", pathToSchemaFile);
            boPropertyBag.put("PreQEServerName", pathToXmlFile + " " + pathToSchemaFile);
            boPropertyBag.put("Server Name", pathToXmlFile + " " + pathToSchemaFile);
            boPropertyBag.put("Local XML File", pathToXmlFile);
            boPropertyBag.put("Trusted_Connection", false);
            boPropertyBag.put("Use JDBC", false);

            boConnectionInfo.setAttributes(boPropertyBag);

            // Set the DB Username and Pwd
            boConnectionInfo.setUserName(null);
            boConnectionInfo.setPassword("");

            // The Kind of connectionInfos is SQL
            boConnectionInfo.setKind(ConnectionInfoKind.SQL);

Any Ideas where the Problem can be?

At least the rest of the Code is working as expected.....

[former_member183750 · Accepted solution]

Hi

My expertise with JAVA is the cup full I pour 1st thing when I come to the office

But I will ping a coworker who knows a whole lot more and see what he suggests. Pls stand by.

- Ludek

[Former Member · Accepted solution]

Hi Ludek,

any help on this is highly appreciated !

Thanks,

[former_member183750 · Accepted solution]

Ok. Got an answer from a coworker. He actually tried to pass the answer here, but ran into some technical issues.

In any case I got an email asking me to add the following for him. Please see if that helps:

------------
When CRJava runs into a problem with a JDBC connection, it fails over to try the JNDI connection.  If it can't find a configured JNDI connection - then it can throw this error.

Most likely you are missing some small property.  The easiest way to figure out what is wrong is to do the following:


1. Obtain the "Display Connection Info" sample from here:

https://smpdl.sap-ag.de/~sapidp/012002523100006008982008E/crxi_r2_jrc_web_smpl.zip


2. Design a report in the report designer that is based off of Excel



3. Change the report names so they are both the same (Your report created of of excel)



4. Run it - it will display the exact properties you need to set in your code.

- Ludek

[Former Member · Accepted solution]

Hi,

actually that's more or less what I tried after a couple of hours of debugging.

I created a Sample Report (with a Sub-Report) with the Crystal Report Designer and after that I created my custom "Information Extractor" (haven't used the one in the examples you provided.

Anyhow I downloaded the "Information Extractor" from the Example and just executed it. Here is the Result:

<table width='100%' height='100%'>
    <tr align='center' valign='top'>
        <td><table border=0 cellspacing=1>
                <tr>
                    <td colspan=2 align=center bgcolor='#0C0C6E'>
                        <strong><font color='white'>Main Report: Excel-Report02.rpt</font></strong>
                    </td>
                </tr>
                <tr>
                    <td colspan=2 bgcolor='#CFD0E2'> </td>
                </tr>
                <tr>
                    <td colspan=2 bgcolor='#0C0C6E'><strong>
                         <font color='white'>Table Properties</font></strong></td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Name</td>
                    <td bgcolor='#F1EFE2'>'Report$'</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Alias</td>
                    <td bgcolor='#F1EFE2'>'Report_'</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Qualified Name</td>
                    <td bgcolor='#F1EFE2'>'Report$'</td>
                </tr>
                <tr>
                    <td colspan=2 bgcolor='#0C0C6E'><strong><font
                            color='white'>ConnectionInfo Properties</font></strong></td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>User Name</td>
                    <td bgcolor='#F1EFE2'>null</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Password</td>
                    <td bgcolor='#F1EFE2'></td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Kind</td>
                    <td bgcolor='#F1EFE2'>SQL</td>
                </tr>
                <tr>
                    <td colspan=2 bgcolor='#0C0C6E'><strong><font
                            color='white'>ConnectionInfo Atttribute Properties</font></strong></td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Database Type</td>
                    <td bgcolor='#F1EFE2'>Excel 8.0</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>System Database Path</td>
                    <td bgcolor='#F1EFE2'></td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>PreQEServerName</td>
                    <td bgcolor='#F1EFE2'>E:\SampleSalesData.xls</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Server Type</td>
                    <td bgcolor='#F1EFE2'>Access/Excel (DAO)</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Database DLL</td>
                    <td bgcolor='#F1EFE2'>crdb_dao.dll</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Session UserID</td>
                    <td bgcolor='#F1EFE2'></td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Database Name</td>
                    <td bgcolor='#F1EFE2'>E:\SampleSalesData.xls</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Server Name</td>
                    <td bgcolor='#F1EFE2'>E:\SampleSalesData.xls</td>
                </tr>
            </table>
            <table border=0 cellspacing=1>
                <tr>
                    <td colspan=2 bgcolor='#CFD0E2'> </td>
                </tr>
                <tr>
                    <td colspan=2 align=center bgcolor='#0C0C6E'><strong><font
                            color='white'>Subreport: SampleSubreportGoesHere</font></strong></td>
                </tr>
                <tr>
                    <td colspan=2 bgcolor='#CFD0E2'> </td>
                </tr>
                <tr>
                    <td colspan=2 bgcolor='#0C0C6E'><strong><font
                            color='white'>Table Properties</font></strong></td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Name</td>
                    <td bgcolor='#F1EFE2'>'Report$'</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Alias</td>
                    <td bgcolor='#F1EFE2'>'Report_'</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Qualified Name</td>
                    <td bgcolor='#F1EFE2'>'Report$'</td>
                </tr>
                <tr>
                    <td colspan=2 bgcolor='#0C0C6E'><strong><font
                            color='white'>ConnectionInfo Properties</font></strong></td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>User Name</td>
                    <td bgcolor='#F1EFE2'>null</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Password</td>
                    <td bgcolor='#F1EFE2'></td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Kind</td>
                    <td bgcolor='#F1EFE2'>SQL</td>
                </tr>
                <tr>
                    <td colspan=2 bgcolor='#0C0C6E'><strong><font
                            color='white'>ConnectionInfo Atttribute Properties</font></strong></td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Database Type</td>
                    <td bgcolor='#F1EFE2'>Excel 8.0</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>System Database Path</td>
                    <td bgcolor='#F1EFE2'></td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>PreQEServerName</td>
                    <td bgcolor='#F1EFE2'>E:\SampleSalesData.xls</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Server Type</td>
                    <td bgcolor='#F1EFE2'>Access/Excel (DAO)</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Database DLL</td>
                    <td bgcolor='#F1EFE2'>crdb_dao.dll</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Session UserID</td>
                    <td bgcolor='#F1EFE2'></td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Database Name</td>
                    <td bgcolor='#F1EFE2'>E:\SampleSalesData.xls</td>
                </tr>
                <tr>
                    <td bgcolor='#CFD0E2'>Server Name</td>
                    <td bgcolor='#F1EFE2'>E:\SampleSalesData.xls</td>
                </tr>
            </table></td>
    </tr>
</table>

If you spot a missing Property or a Property that is set wrong, please let me know.

I just checked and couldn't spot a difference (I set a bunch of more properties, but removing them does not help here).

Thanks,

BTW: With my custom "Information Extractor" I was able to get the correct Properties for the XML-DataSource.

EDIT: I should note that the Report I want to create with the Java Code does not have a Sub-Report (yet).

[Former Member · Accepted solution]

Anybody else any suggestions, or Idea what I'm doing wrong?

Otherwise I'll just move to XML or any other JDBC Connection (Oracle, Mysql...)

Thanks,

[former_member203619 · Accepted solution]

The one additional note I would add here is that your table alias is different than your table name (In addition to using special characters).  Two changes I would suggest are:

1. Make sure you are specifying both the table name and alias in code.  The code you have listed does not.

2. I haven't tested with Excel - but is it possible to have a table name that doesn't use special characters ($ and _ )

Thanks

Shawn

[Former Member · Accepted solution]

Hi Shawn,

thanks for your helpful answer.

However the Excel-Spreadsheet itself is called "Report" and the special characters ($ and _ ) will come from crystal reports designer GUI. In this case I do not have Control over those special chars in the table name and/or alias.

I think I will go for XML, Excel is just not working.

Thanks everybody for trying to help out here!