Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
‎09-12-2014 9:39 AM


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...

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

  }

%>

3 Comments
Labels in this area
Popular Blog Posts