cancel
Showing results for 
Search instead for 
Did you mean: 

error exporting to excel

Former Member
0 Kudos
84

when exporting report (via java) to excel we get format is not supported error

also when exporting to csv the csv output includes headers and footers, any way to turn off?

any ideas?

Accepted Solutions (1)

Accepted Solutions (1)

ted_ueda
Employee
Employee
0 Kudos

No idea what version and SDK you're using.

Sincerely,

Ted Ueda

Former Member
0 Kudos

that's a good question, not sure how to answer it

We have crystal reports developer edition xi r2. However, the jar files included with this release are older then then ones we are currently using. When I copied the jars from the crystal installation directory our reports no longer work at all. So I am assume the jars we are using are from something newer.

Is their a specific SDK for Java development? If, so how to get it?

ted_ueda
Employee
Employee
0 Kudos

I would use the latest, that has Excel export support. Runtime available here:

[http://www.businessobjects.com/campaigns/forms/downloads/crystal/eclipse/datasave.asp]

It also respects the CSV header config option.

Sincerely,

Ted Ueda

Former Member
0 Kudos

thanks

there seems to be alot less jars in this then we currently have, is this correct? We have a java application (partial code below) that we use for report generation (see run method). We do not server reports via web, reports are stored/emailed.


package com.surecomp.allMATCH;

import com.crystaldecisions.reports.sdk.ISubreportClientDocument;
import com.crystaldecisions.reports.sdk.ParameterFieldController;
import com.crystaldecisions.reports.sdk.ReportClientDocument;
import com.crystaldecisions.reports.sdk.SubreportController;
import com.crystaldecisions.report.web.viewer.CrystalReportViewer;
import com.crystaldecisions.sdk.occa.report.application.OpenReportOptions;
import com.crystaldecisions.sdk.occa.report.exportoptions.ReportExportFormat;
import com.crystaldecisions.sdk.occa.report.data.*;
import com.crystaldecisions.sdk.occa.report.lib.IStrings;
import com.crystaldecisions.sdk.occa.report.lib.PropertyBag;
import com.crystaldecisions.sdk.occa.report.lib.ReportSDKException;

    public void run() {
        try {
            if (!new File(reportSourceFileName).exists()) {
            	failure=true;
            	failuremessage="Report File Not Found:" + reportSourceFileName;
                return;
            }
            
            int Julian = DateTime.CalculateJulianDate(DateTime.getCalendar(systemdate));
            
            ReportClientDocument reportClientDoc = new ReportClientDocument();
           	reportClientDoc.open(reportSourceFileName, 0);
            
            changeReportDatabaseConnection(reportClientDoc, dbconnectionURL, dbdriverName, servername, databasename, dbusername, dbpassword);

            ByteArrayInputStream byteArrayInputStream = null; 
            switch (iFormat) {
            case 1: // pdf
            	byteArrayInputStream = (ByteArrayInputStream) reportClientDoc.getPrintOutputController().export(ReportExportFormat.PDF);
            	break;
            case 2: // cvs
            	byteArrayInputStream = (ByteArrayInputStream) reportClientDoc.getPrintOutputController().export(ReportExportFormat.characterSeparatedValues);
            	break;
            case 3: // excel
            	byteArrayInputStream = (ByteArrayInputStream) reportClientDoc.getPrintOutputController().export(ReportExportFormat.recordToMSExcel);
            	break;
            }
            //byteArrayInputStream = (ByteArrayInputStream) reportClientDoc.getPrintOutputController().export(ReportExportFormat.PDF);
              
            reportClientDoc.close();

            writeFileFromInputStream(fullyQaulifiedOutputFn,byteArrayInputStream);

            String[] emailaddresses = emailaddress.split(";");
            // now notify user that the report has finished and/or email it to them
            for (int c=0; c < emailaddresses.length;c++) {
	            if (emailreport == 1) {
	            	Email.SendEmail(emailaddresses[c], Configuration.getEmailFrom(), "allMATCH - Your Requested Report", "Your allMATCH requested report has been generated.\r\nFileName: " + fullyQaulifiedOutputFn + "\r\nThe generated report is attached.", fullyQaulifiedOutputFn);
	            } else {
	            	Email.SendEmail(emailaddresses[c], Configuration.getEmailFrom(), "allMATCH - Your Requested Report", "Your allMATCH requested report has been generated.\r\nFileName: " + fullyQaulifiedOutputFn);
	            }
            }
        } catch (Exception e) {
        	failuremessage = e.getMessage();
        	failure = true;
        }
    }

    
    protected static void changeReportDatabaseConnection(ReportClientDocument aRCD, String aS_connectionURL,String aS_driverClass, String aS_server, String aS_dbName,String aS_username, String aS_password) throws Exception {
        changeReportDatabaseConnection(aRCD, aS_connectionURL, aS_driverClass, aS_server, aS_dbName,aS_username, aS_password, null, null);
    }

    /**
     * @param aRCD             ReportClientDocument  representing the report being used, it is the opened .rpt file
     * @param aS_connectionURL typical jdbc connection string eg jdbc:microsoft:sqlserver://10.5.5.165:1433
     * @param aS_driverClass
     * @param aS_server        db server name/IP
     * @param aS_dbName        db name
     * @param aS_username      db username
     * @param aS_password      db password
     * @param reportName       "" for main report, name of subreport for subreport, null for all reports, I honestly do not know
     *                         exactly what this means, maybe it is needed for super fancy reports, i left it here just incase
     *                         it is needed in the future, right now, all calls would be null
     * @param tableName        name of table to change.  null for all tables., as far as allTRA is concerned, this will always be null
     *                         allTRA does not currently do cross db
     * @throws ReportSDKException
     */
    protected static void changeReportDatabaseConnection(ReportClientDocument aRCD, String aS_connectionURL,
                                                         String aS_driverClass, String aS_server, String aS_dbName,
                                                         String aS_username, String aS_password,
                                                         String reportName, String tableName) throws Exception {

        //this is VERY similar to a basic sample provided by Business Objects (changeDataSource)
        //one would think that you could specify the connection for the report itself
        //the person who wrote the api interface thought so as well
        //unfortunately the people who implemented the interface and made the jars did not feel like making it work
        //so changing connections on the report/document level either crash with errors that the functionality is not
        //implemented or simply run and do nothing
        //we are stuck changing the connection of each table
        //if the autohrs of the api had not provided this sample, I would simply have ignored it,
        // since it seems that this could have been abstracted
        //away from a developer simply executing a report, alas,
        // it appears BO is more interested in their .NET API (which is far more refined) than the Java API

        ITable lT_origTable;
        ITable lT_newTable;
        PropertyBag lPB_connectionValues = new PropertyBag();

        // Overwrite any existing properties with updated values
        lPB_connectionValues.put("Trusted_Connection", "false");            //always
        lPB_connectionValues.put("Server Type", "JDBC (JNDI)");             //always
        lPB_connectionValues.put("Use JDBC", "true");                       //always
        lPB_connectionValues.put("Database DLL", "crdb_jdbc.dll");          // a dll in java!?!?!  I do not know, but this is apparently
        //                                                                  always the case, I do not think it has anythinng to with a client running
        //                                                                  the report but am keeping it here since all examples i can find use it
        // propertyBag.put("JNDIOptionalName", JNDI_OPTIONAL_NAME);         //we do not use jndi
        lPB_connectionValues.put("Connection URL", aS_connectionURL);       //typical jdbc connection string eg jdbc:microsoft:sqlserver://10.5.5.165:1433
        lPB_connectionValues.put("Database Class Name", aS_driverClass);    //jdbc class eg com.microsoft.jdbc.sqlserver.SQLServerDriver
        lPB_connectionValues.put("Server Name", aS_server);                 //name or IP
        // lPB_connectionValues.put("Connection String", CONNECTION_STRING);
        lPB_connectionValues.put("Database Name", aS_dbName);               //db name
        // lPB_connectionValues.put("URI", URI);


        if (reportName == null || reportName.equals("")) {
            Tables lTS_tables = aRCD.getDatabaseController().getDatabase().getTables();
            for (int i = 0; i < lTS_tables.size(); i++) {
                lT_origTable = lTS_tables.getTable(i);
                if (tableName == null || lT_origTable.getName().equals(tableName)) {
                    lT_newTable = (ITable) lT_origTable.clone(true);
                    changeTableConnection(lT_origTable, lT_newTable, lPB_connectionValues, aS_username, aS_password);
                    aRCD.getDatabaseController().setTableLocation(lT_origTable, lT_newTable);
                }
            }
        }

        if (reportName == null || !(reportName.equals(""))) {
            IStrings subNames = aRCD.getSubreportController().getSubreportNames();
            Tables lTS_tables;
            for (int subNum = 0; subNum < subNames.size(); subNum++) {
                lTS_tables = aRCD.getSubreportController().getSubreport(subNames.getString(subNum)).getDatabaseController().getDatabase().getTables();
                for (int i = 0; i < lTS_tables.size(); i++) {
                    lT_origTable = lTS_tables.getTable(i);
                    if (tableName == null || lT_origTable.getName().equals(tableName)) {
                        lT_newTable = (ITable) lT_origTable.clone(true);
                        changeTableConnection(lT_origTable, lT_newTable, lPB_connectionValues, aS_username, aS_password);
                        aRCD.getSubreportController().getSubreport(subNames.getString(subNum)).getDatabaseController().setTableLocation(lT_origTable, lT_newTable);
                    }
                }
            }
        }
    }

    private static void changeTableConnection(ITable aT_origTable, ITable aT_newTable, PropertyBag aPB_connectionValues,
                                              String aS_username, String aS_password) {
        // We set the Fully qualified name to the Table Alias to keep
        // the method generic
        // This workflow may not work in all scenarios and should likely
        // be customized to work
        // in the developer's specific situation. The end result of this
        // statement will be to strip
        // the existing table of it's db specific identifiers. For
        // example Xtreme.dbo.Customer becomes just Customer
        //for now it uses the examples philosphy
    	aT_newTable.setQualifiedName(aT_origTable.getName());
        aT_newTable.setAlias(aT_origTable.getAlias());

        IConnectionInfo aCI = aT_newTable.getConnectionInfo();
        aCI.setAttributes(aPB_connectionValues);
        aCI.setUserName(aS_username);
        aCI.setPassword(aS_password);
    }

}

Edited by: robert jenkin on Feb 2, 2010 9:06 PM

ted_ueda
Employee
Employee
0 Kudos

There's less jars - there's been refactoring and consolidation of contents.

Since there's no one-to-one correspondence, one has to ensure removing all the older jars, or you'd encounter version conflict issues.

Sincerely,

Ted Ueda

Former Member
0 Kudos

Hi

I cleaned out the jars copied the ones from the sdk (you suggested I down load) and to my surprise our reports still work. The export to excel worked as well. However, ehe csv export still includes header/footer. Can you direct me where to look to turn them off?

thanks, you been great help!!!

Answers (0)