Skip to Content
Author's profile photo Prithviraj Shekhawat

Export folder location of all the reports in the enterprise to Excel using Java SDK

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();

  }

%>

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy

      Exporting to Excel is cool, but I usually just export to .csv files (building the comma-delimited strings in my code) which can be easily imported to Excel.

      -Dell

      Author's profile photo Prithviraj Shekhawat
      Prithviraj Shekhawat
      Blog Post Author

      Thanks for the feedback Dell.

      Author's profile photo Former Member
      Former Member

      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