cancel
Showing results for 
Search instead for 
Did you mean: 

Export to Excel Data only

Former Member
0 Kudos
122

Is there any way to export to excel data only format through web services? I can figure out how to export to excel but not data only.

Thanks in advance...

Accepted Solutions (1)

Accepted Solutions (1)

joshua_morin
Explorer
0 Kudos

Agreed Caleb. The ViewReport object and refresh don't do anything. I have a support ticket with SAP on this and I will make sure to post the answer when I get it.

Ciao.

joshua_morin
Explorer
0 Kudos

Caleb,

I'm told that executing the report by scheduling it in code, then exporting to Excel in the scheduling job logic will work. Sit tight - will post when I know more.

aasavaribhave
Product and Topic Expert
Product and Topic Expert
0 Kudos

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using BusinessObjects.DSWS;

using BusinessObjects.DSWS.Session;

using BusinessObjects.DSWS.BIPlatform;

using BusinessObjects.DSWS.ReportEngine;

using BusinessObjects.DSWS.BIPlatform.Desktop;

Edited by: Aasavari Bhave on Oct 14, 2009 12:42 PM

aasavaribhave
Product and Topic Expert
Product and Topic Expert
0 Kudos

public partial class ExportExcelDataOnly : System.Web.UI.Page

{

String connectionURL = "http://<server name:8080/dswsbobje/services/Session";

String username = "administrator";

String password = "";

String cmsname = "<cms name>";

String reportname = "<report name>";

protected void Page_Load(object sender, EventArgs e)

{

BusinessObjects.DSWS.Connection boConnection = new BusinessObjects.DSWS.Connection(connectionURL);

EnterpriseCredential boCredential = new EnterpriseCredential();

boCredential.Login = username;

boCredential.Password = password;

boCredential.Domain = cmsname;

Session boSession = new BusinessObjects.DSWS.Session.Session(boConnection);

SessionInfo boSI = boSession.Login(boCredential);

string[] strRepEngURL = boSession.GetAssociatedServicesURL("ReportEngine");

ReportEngine repEngine = ReportEngine.GetInstance(boSession, strRepEngURL[0]);

string[] strBIPlatformURL = boSession.GetAssociatedServicesURL("BIPlatform");

BIPlatform bipService = BIPlatform.GetInstance(boSession, strBIPlatformURL[0]);

string query = "path://InfoObjects/Root Folder/Report Samples/Demonstration/world sales report@*";

ResponseHolder rh = bipService.Get(query, null);

if (rh == null)

return;

InfoObjects oInfoObjects = rh.InfoObjects;

CrystalReport oInfoObject = (CrystalReport)oInfoObjects.InfoObject[0];

if (oInfoObject.PluginProcessingInterface == null)

oInfoObject.PluginProcessingInterface = new ReportProcessingInfo();

if (oInfoObject.PluginProcessingInterface.ReportFormatOptions == null)

oInfoObject.PluginProcessingInterface.ReportFormatOptions = new CrystalReportFormatOptions();

oInfoObject.PluginProcessingInterface.ReportFormatOptions.FormatSpecified = true;

oInfoObject.PluginProcessingInterface.ReportFormatOptions.Format = ReportFormatEnum.EXCEL_DATA_ONLY;

aasavaribhave
Product and Topic Expert
Product and Topic Expert
0 Kudos

InfoObject schedObject = bipService.Schedule(oInfoObjects).InfoObject[0];

query = "cuid://<" + schedObject.NewJobID + ">";

rh = bipService.Get(query, null);

Excel newInstance = (Excel)rh.InfoObjects.InfoObject[0];

while (newInstance.SchedulingInfo.Status != ScheduleStatusEnum.COMPLETE)

{

rh = bipService.Get(query, null);

newInstance = (Excel)rh.InfoObjects.InfoObject[0];

}

Response.Clear();

Response.ContentType = "xls";

Response.AddHeader("Content-disposition", "inline;filename=myreport.xls");

String downloadFileID = bipService.StartSingleDownload(newInstance.CUID, 0);

System.Int64 startIndex = 0;

DownloadStatus dlStatus = bipService.DownloadFile(downloadFileID, startIndex);

Response.OutputStream.Write(dlStatus.BinaryData, 0, dlStatus.BinaryData.Length);

while(dlStatus.EndOfFile != true)

{

dlStatus = bipService.DownloadFile(downloadFileID, dlStatus.NextReferencePosition);

Response.OutputStream.Write(dlStatus.BinaryData, 0, dlStatus.BinaryData.Length);

}

Response.End();

bipService.FinishDownload(downloadFileID);

boSession.Logout();

}

}

aasavaribhave
Product and Topic Expert
Product and Topic Expert
0 Kudos

Couldn't post the code in 1 reply so I have broke it in last 3 replies. This code using WS to schedule a Crystal Report to Excel data Only, checks that the scheduled instance is successful and then using response object views the Excel data only instance with MS Excel. You can add the code to delete the report instance after it is viewed.

Former Member
0 Kudos

Thanks Aasavari, I will test this method out in our project soon.

Is it necessary to use the scheduler for this?

Edited by: Caleb Cittadino on Oct 15, 2009 7:57 PM

aasavaribhave
Product and Topic Expert
Product and Topic Expert
0 Kudos

you can't do it without sceduling in WS.

joshua_morin
Explorer
0 Kudos

The code that's been posted in this thread works in our environment. Thanks Aasavari.

aasavaribhave
Product and Topic Expert
Product and Topic Expert
0 Kudos

Can the the person who started this thread mark it as answered?

Former Member
0 Kudos

Hi,

I am using WCF to retrieve reports as PDF.

When I tried to run

....

rh = bipService.Get(query, null);

Excel newInstance = (Excel)rh.InfoObjects.InfoObject[0];

while (newInstance.SchedulingInfo.Status != ScheduleStatusEnum.COMPLETE)

{

rh = bipService.Get(query, null);

newInstance = (Excel)rh.InfoObjects.InfoObject[0];

}

...

I am getting CrystalReport type in rh.InfoObjects.InfoObject[0]; and not Excel.

Any ideas why?

Thank you very much,

Robert

Answers (2)

Answers (2)

joshua_morin
Explorer
0 Kudos

Ping...

I've taken over for the OP and am still interested in this capability. Has anyone successfully exported as Excel in data only format? I'm currently at the same point as the OP and that I get the "unable to retrieve binary view"

HELP!!!

Regards.

Edited by: Joshua Morin on Oct 6, 2009 7:11 PM

aasavaribhave
Product and Topic Expert
Product and Topic Expert
0 Kudos

Try adding

1. ViewSupport object and set OutputFormatType is Excel.

2. Add refresh() action in getDocumentInformation call.

Former Member
0 Kudos

I am also unable to get the plugin exporter working. Does anyone understand how altering the Crystal Reports object in memory (as this example suggests) would affect the docinfo object you retrieve later on? They appear to be completely unrelated in my testing. Here are my findings when setting the EXCEL_DATA_ONLY format for the plugin:

- Set the ViewSupport to basic binary

-- Results: I get a binary report result, same result as if you don't touch the original Crystal Report *(not readable by excel)

OR

- Set the ViewSupport to EXCEL

-- Results: I get an Excel document (not in data only format), again same result as if you don't touch the original Crystal Report

With both tests I tried 'refreshing': before the prompts, after the prompts, not at all.

Has anyone figured this out?

aasavaribhave
Product and Topic Expert
Product and Topic Expert
0 Kudos

There is ExcelDataOnlyFormat class in WS SDK. You can use it.

Former Member
0 Kudos

I have seen this, but I don't understand how to implement it. Would you happen to have sample code for this?

Here is where I am at:

if (viewType == OutputFormatType.PDF || viewType == OutputFormatType.WORD || viewType == OutputFormatType.EXCEL)
{
         ViewSupport objViewSupport = new ViewSupport()
         {
              ViewMode = ViewModeType.DOCUMENT,
              ViewType = ViewType.BINARY,
              OutputFormat = viewType
         };

         RetrieveBinaryView objRetrieveBinaryView = new RetrieveBinaryView() { ViewSupport = objViewSupport };
         CallbackOption[] boCallOpt = new CallbackOption[1];
         boCallOpt[0] = new ImageManagement()
         {
                 CallbackScript = "getImage.aspx",
                 ImageManagementHolder = "imageName",
                 DocumentReferenceHolder = "docRef"
         };

         ExcelDataOnlyFormat excelDataOnlyFormat = new ExcelDataOnlyFormat();
         objRetrieveBinaryView.CallbackOption = boCallOpt;
         boDocInfo = boRepEng.GetDocumentInformation(boDocInfo.DocumentReference, null, oActions, new NavigateToPage()
                                                                                {
                                                                                Page = vCurrentPage
                                                                                }, new RetrieveData() {                                                                                
RetrieveView =    objRetrieveBinaryView                                                                                
});
         string strToken = boDocInfo.DocumentReference;
         BinaryView myBOView = (BinaryView)boDocInfo.View;
         byte[] docContents = (byte[])myBOView.Content;

         // Clear the Response object and stream the file using the BinaryWrite() method.
         string tempFileName = "attachment;filename=" + (reportName == string.Empty ? "Report" : reportName);
         Response.Clear();
         Response.ClearHeaders();
         if (viewType == OutputFormatType.PDF) { Response.ContentType = "application/pdf"; tempFileName += ".pdf"; }
         else if (viewType == OutputFormatType.WORD) { Response.ContentType = "application/ms-word"; tempFileName += ".doc";}
         else if (viewType == OutputFormatType.EXCEL) { Response.ContentType = "application/ms-excel"; tempFileName += ".xls"; }
 
         Response.AddHeader("content-disposition", tempFileName);
         Response.BinaryWrite(docContents);
         Response.End();
}

Edited by: mori0043 on Jul 7, 2009 2:58 PM

Edited by: mori0043 on Jul 7, 2009 2:59 PM

Former Member
0 Kudos

I can't seem to get the code to post properly...

Edited by: mori0043 on Jul 7, 2009 3:05 PM

aasavaribhave
Product and Topic Expert
Product and Topic Expert
0 Kudos

Here is a cs.net code that I have tested and works fine and exports to data only. It currently exports Crystal Report. Something similar should be doable for webi or FullClient.

EnterpriseCredential credential = new EnterpriseCredential();

credential.Login = CMS_USER;

credential.Password = CMS_USER_PASSWORD;

credential.Domain = CMS_CLUSTER_NAME;

credential.AuthType = CMS_AUTHENTICATION;

BusinessObjects.DSWS.Session.Session wSession = new BusinessObjects.DSWS.Session.Session(new BusinessObjects.DSWS.Connection(WS_URL_SESSION));

wSession.Login(credential);

BIPlatform bipService = new BIPlatform(new BusinessObjects.DSWS.Connection(WS_URL_BIPLATFORM), wSession.ConnectionState);

String qryStr = "query://{select * from ci_infoobjects where si_name='world sales report' and si_instance=0}";

ResponseHolder rh = bipService.Get(qryStr, null);

CrystalReport document = ((CrystalReport)(rh.InfoObjects.InfoObject[0]));

string documentCUID = document.CUID;

ReportEngine engineService = new ReportEngine(new BusinessObjects.DSWS.Connection(WS_URL_REPORTENGINE), wSession.ConnectionState);

document.PluginProcessingInterface.ReportFormatOptions.Format = ReportFormatEnum.EXCEL_DATA_ONLY;

RetrieveData retBOData = new RetrieveData();

RetrieveBinaryView retBOView = new RetrieveBinaryView();

retBOData.RetrieveView = retBOView;

DocumentInformation documentInformation = engineService.GetDocumentInformation(documentCUID, null, null, null, retBOData);

BinaryView myBOView = (BinaryView)documentInformation.View;

byte[] docContents = (byte[])myBOView.Content;

Response.Clear();

Response.AddHeader("content-disposition", "inline;filename=world sales report.xls");

Response.ContentType = "application/Excel";

Response.BinaryWrite(docContents);

Response.End();

wSession.Logout();

aasavaribhave
Product and Topic Expert
Product and Topic Expert
0 Kudos

Please read line

byte] docContents = (byte[)myBOView.Content;

as

byte [] docContents = (byte [] ) myBOView.Content;

I think the "]" got removed because of formatting.

aasavaribhave
Product and Topic Expert
Product and Topic Expert
0 Kudos

byte [ ] docContents = (byte[ ] )myBOView.Content;

Former Member
0 Kudos

Had to change user ids... Are you sure that there isn't more code to this? Shouldn't I have to set the ViewSupport() object? I run your code and I keep getting the error: "Failed to retrieve binary view of the report"

Thanks in advance!