This is a continuation of my blog http://scn.sap.com/community/bi-platform/java-sdk/blog/2013/11/29/getting-the-folder-location-of-all-the-reportscrystalwebideski-in-the-enterprise-using-java-sdk

I have thought of including the features of exporting the results directly to an excel file.

However, the Business Objects SDKs do not give you an API call to have these kind of results exported to an excel file.

Using the features of poi jar files, it is possible to include the code for having the results in an excel file.

Below is the code for exporting the results in an excel file. The version poi jars used for compiling this code is 2.5.1-final-20040804

You would need to update the location in the below code where you want to have the exported excel file.

Note:

The part of exporting the results to an excel file does not come under the support of BusinessObjects as you would be using third party jars with BusinessObjects jar files to have the results exported to excel.

Export folder path to Excel

<%@ page import=”com.crystaldecisions.sdk.plugin.desktop.program.*,

com.crystaldecisions.sdk.framework.*,

com.crystaldecisions.sdk.occa.infostore.*,

com.crystaldecisions.sdk.exception.*,

java.io.FileWriter, java.io.IOException,

com.crystaldecisions.sdk.occa.report.lib.PropertyBag,

com.crystaldecisions.sdk.properties.IProperties,

com.crystaldecisions.sdk.properties.IProperty,

com.crystaldecisions.sdk.plugin.desktop.folder.*,

java.util.*”

%>

<%@ page import=”org.apache.poi.hssf.usermodel.HSSFSheet”%>

<%@ page import=”org.apache.poi.hssf.usermodel.HSSFWorkbook”%>

<%@ page import=”org.apache.poi.hssf.usermodel.HSSFCell”%>

<%@ page import=”org.apache.poi.hssf.usermodel.HSSFRow”%>

<%@ page import=”java.io.*” %>

<%@ page import=”org.apache.poi.hssf.usermodel.HSSFPrintSetup”%>

<html>

<head></head>

<body>

<table border=”2″ cellpadding=”0″ style=”border-collapse: collapse” bordercolor=”#111111″ width=”100%” id=”AutoNumber1″>

<tr>

  <th width=”10%”>Report Id</th>

  <th width=”20%”>Report Name</th>

  <th width=”10%”>Owner </th>

  <th width=”30%”>Creation Time </th>

  <th width=”30%”>Last Modified Time </th>

  <th width=”40%”>Folder Path </th>

</tr>

<%

        String user = “Administrator”;

     String password = “Secure01”;     

  String cmsName = “localhost:6400”;     

  String cmsAuthType = “secEnterprise”;

  String reportOwner=null;

  String reportCreationTime=null;

  String reportUpdateTime=null;

  //String finalFolderPath=null;

  HSSFWorkbook wb = new HSSFWorkbook();

  HSSFSheet sheet = wb.createSheet(“new sheet”);

  HSSFRow rowhead = sheet.createRow((short)0);

  rowhead.createCell((short) 0).setCellValue(“Report Name”);

  rowhead.createCell((short) 1).setCellValue(“Owner”);

  rowhead.createCell((short) 2).setCellValue(“Creation Time”);

  rowhead.createCell((short) 3).setCellValue(“Last Modified Time”);

  rowhead.createCell((short) 4).setCellValue(“FolderPath”);

  IEnterpriseSession es=null;

  int index = 1;

  try

  {

  es = CrystalEnterprise.getSessionMgr().logon( user, password, cmsName, cmsAuthType);             

  IInfoStore iStore = (IInfoStore) es.getService(“”, “InfoStore”);

  IInfoObjects infoobjects = iStore .query(“SELECT * from CI_INFOOBJECTS WHERE  si_instance=0 and si_kind in (‘crystalreport’,’webi’)”);        

  for(int i=0;i<infoobjects.size();i++)

  {

  int count=i+1;

  IInfoObject infoobject=(IInfoObject) infoobjects.get(i);

  ISchedulingInfo schedInfo=infoobject.getSchedulingInfo();

  int reportID=infoobject.getID();

  String reportName=infoobject.getTitle();

  out.println(“<tr><td>”+reportID+”</td>”);

  out.println(“<td>”+reportName+”</td>”);

  IProperties reportProperties=(IProperties)infoobject.properties();

  IProperty reportProperty=reportProperties.getProperty(“SI_OWNER”);

  if(reportProperty != null)

  {

  reportOwner=reportProperty.getValue().toString();

  out.println(“<td>”+reportOwner+”</td>”);

  }

  IProperty reportProperty1=reportProperties.getProperty(“SI_CREATION_TIME”);

  if(reportProperty1 != null)

  {

  reportCreationTime=reportProperty1.getValue().toString();

  out.println(“<td>”+reportCreationTime+”</td>”);

  }

  IProperty reportProperty2=reportProperties.getProperty(“SI_UPDATE_TS”);

  if(reportProperty2 != null)

  {

  reportUpdateTime=reportProperty2.getValue().toString();

  out.println(“<td>”+reportUpdateTime+”</td>”);

  }

  HSSFRow row = sheet.createRow((short)index);

  row.createCell((short) 0).setCellValue(reportName);

  row.createCell((short) 1).setCellValue(reportOwner);

  row.createCell((short) 2).setCellValue(reportCreationTime);

  row.createCell((short) 3).setCellValue(reportUpdateTime);

  IProperties prop = infoobject.properties();

  IProperty getProp = prop.getProperty(“SI_PARENTID”);

  String FolderID = getProp.toString();

  IInfoObjects folder = iStore .query(“select si_id,si_name,si_parentid,si_path from ci_infoobjects where si_id=” + FolderID);

  IInfoObject ifolder=(IInfoObject)folder.get(0);

  if(ifolder.getKind().equals(“Folder”))

  {

   IFolder iifolder=(IFolder)ifolder;

   String finalFolderPath=””;

   if(iifolder.getPath()!= null)

   {

    String path[]=iifolder.getPath();

    for(int fi=0;fi<path.length;fi++)

    {

     finalFolderPath = path[fi] + “/” + finalFolderPath;

    }

    finalFolderPath = finalFolderPath + iifolder.getTitle();

   }

   else

   {

    finalFolderPath=finalFolderPath+iifolder.getTitle();

   }

   out.println(“<td>” + finalFolderPath + “</td></tr>”);

   row.createCell((short) 4).setCellValue(finalFolderPath);

  }

  else if((ifolder.getKind().equals(“FavoritesFolder”)))

  {

   out.println(“<td><b>FavoritesFolder</b>  ::  ” + ifolder.getTitle() + “</td></tr>”);

   row.createCell((short) 4).setCellValue(“FavoritesFolder  ::  ” + ifolder.getTitle() + “”);

  }

  else if((ifolder.getKind().equals(“Inbox”)))

  {

   out.println(“<td><b>Inbox</b>  ::  ” + ifolder.getTitle() + “</td></tr>”);

   row.createCell((short) 4).setCellValue(“Inbox  ::  ” + ifolder.getTitle() + “”);

  }

  else if((ifolder.getKind().equals(“ObjectPackage”)))

  {

   //out.println(“<td><b>ObjectPackage</b>  ::  ” + ifolder.getTitle() + “</td></tr>”);

   IProperties prop1 = ifolder.properties();

  IProperty getProp1 = prop1.getProperty(“SI_PARENTID”);

  String FolderID1 = getProp1.toString();

  IInfoObjects folder1 = iStore .query(“select * from ci_infoobjects where si_id=” + FolderID1);

  IInfoObject ifolder1=(IInfoObject)folder1.get(0);

  if(ifolder1.getKind().equals(“Folder”))

  {

   IFolder iifolder1=(IFolder)ifolder1;

   String finalFolderPath1=””;

   if(iifolder1.getPath()!= null)

   {

    String path[]=iifolder1.getPath();

    for(int j=0;j<path.length;j++)

    {

     finalFolderPath1= path[j] + “/” + finalFolderPath1;

    }

    finalFolderPath1 = finalFolderPath1 + iifolder1.getTitle()+”/”+ifolder.getTitle();

   }

   else

   {

    finalFolderPath1=finalFolderPath1+iifolder1.getTitle()+”/”+ifolder.getTitle();

   }

   out.println(“<td>” + finalFolderPath1 + “</td></tr>”);

   row.createCell((short) 4).setCellValue(finalFolderPath1);

  }

  }

  index++;

  }

  }

      catch(SDKException e)

  {

  out.println(e.getMessage());

  }

  finally

  {

  FileOutputStream fileOut = new FileOutputStream(“C://Result.xls”);

  wb.write(fileOut);

  fileOut.close();

  es.logoff();

  }

%>

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. green bear

    I was actually trying to run this code to get the list of reports and including folder location for my BOBJ environment.

    deployed to the web tier,browsing the url to call the jsp it just stays there without any response.

    –GreenBear

    (0) 

Leave a Reply