BI Universe - Export Business Layer object definitions to Excel

[Former Member]

I have developed a Java class which can export the business layer object definitions to an excel document. The code is in a crude form but it works well. Please feel free to use it and improve it.

Next steps,

• to clean up the code
• to parameterize the cms name, user name, password, business layer and data foundation path
• to include data foundation export option
• to have the option of changing the business layer object definition (such as description and SQL)
• create a web interface

import com.crystaldecisions.sdk.exception.SDKException;

import com.sap.sl.datasource.BusinessLayer;

import com.sap.sl.sdk.authoring.businesslayer.BlContainer;

import com.sap.sl.sdk.authoring.businesslayer.BlItem;

import com.sap.sl.sdk.authoring.businesslayer.BusinessObject;

import com.sap.sl.sdk.authoring.businesslayer.ItemState;

import com.sap.sl.sdk.authoring.businesslayer.RelationalBusinessLayer;

import com.sap.sl.sdk.authoring.datafoundation.DataFoundation;

import com.sap.sl.sdk.authoring.connection.RelationalConnection;

import com.sap.sl.sdk.authoring.local.LocalResourceService;

import com.sap.sl.sdk.framework.SlContext;

import com.sap.sl.sdk.authoring.samples.util.AuthenticationMode;

import com.crystaldecisions.sdk.framework.IEnterpriseSession;

import com.crystaldecisions.sdk.framework.CrystalEnterprise;

import com.crystaldecisions.sdk.occa.infostore.CeSecurityCUID.RootFolder;

import com.sap.sl.sdk.framework.cms.CmsSessionService;

import com.sap.sl.sdk.authoring.businesslayer.BusinessObject;

import com.sap.sl.sdk.authoring.businesslayer.CustomProperty;

import java.io.FileOutputStream;

import java.util.List;

import java.util.Iterator;

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.ss.usermodel.*;

import org.eclipse.core.internal.runtime.PrintStackUtil;

public class ExportBusinessLayer {

    //

    // This sample is used to edit a business layer stored in a local project

    // and modify one business item (name, description, state)

    //

    //

    // !! Edit the sample parameters here !!

    //

 /** CMS System */

 private static final String CMS_LOG_HOST = "cms_server";

 /** User name used to log in to the CMS */

    private static final String CMS_LOG_USER = "username";

 /** User password */

 private static final String CMS_LOG_PASS = "password";

 /** Authentication mode used to log in to the CMS. Here: Enterprise */

    private static final String CMS_AUTH_MODE = AuthenticationMode.ENTERPRISE;

 /** Business layer name */

    private static final String BLX_PATH = "BusinessLayerName.blx";

    private static final String DFX_PATH = "DataFoundationName.dfx";

   

    private IEnterpriseSession enterpriseSession;

    private SlContext context;

    public static void main(String args[])

    {

     try

     {

     

      ExportBusinessLayer eBL = new ExportBusinessLayer();

      eBL.setUp();

         eBL.exportLocalBusinessLayer();

        

     }

     catch(Exception e)

     {

      e.printStackTrace();

     }

   

    }

   

    public void setUp() throws Exception {

        context = SlContext.create();

        enterpriseSession = CrystalEnterprise.getSessionMgr().logon(CMS_LOG_USER, CMS_LOG_PASS, CMS_LOG_HOST, CMS_AUTH_MODE);

        context.getService(CmsSessionService.class).setSession(enterpriseSession);    

    }

   

    public void tearDown() throws Exception {

        context.close();

    }

    public void exportLocalBusinessLayer() throws SDKException {

     LocalResourceService service = context.getService(LocalResourceService.class);

        RelationalBusinessLayer businessLayer = (RelationalBusinessLayer) service.load(BLX_PATH);

        List <BlItem> blRootItems = businessLayer.getRootFolder().getChildren();

       

        Workbook wb = new HSSFWorkbook();

        Sheet sheet = wb.createSheet("BusinessLayer");

        sheet = exportBlHeaders(sheet);

        sheet = exportBlItems(blRootItems,sheet);

       

        for(int i=0;i<=25;i++)

        {

         sheet.autoSizeColumn(i);

        }

       

        sheet.createFreezePane(1, 1);

       

        try{

      FileOutputStream fos = new FileOutputStream("BusinessLayer.xls");

         wb.write(fos);

         fos.close();

        }

        catch(Exception e)

        {

         e.printStackTrace();

        }

       

        service.close(businessLayer);

  System.out.println("\n The Business Object has been updated.");

 

 }

    private String getBlItemParentPath(BlItem blItem)

    {

     String parentPath ="";

     if(blItem.getParent() != null)

     {

      parentPath = getBlItemParentPath(blItem.getParent()) + "\\" + blItem.getParent().getName();

     }

     return parentPath;

    }

    private Sheet exportBlHeaders(Sheet sheet)

    {

     Row row = sheet.createRow(0);

     row.createCell(0).setCellValue("Parent Folder Path");

     row.createCell(1).setCellValue("Parent Name");

     row.createCell(2).setCellValue("Object Name");

     row.createCell(3).setCellValue("Unique Id");

     row.createCell(4).setCellValue("State");

     row.createCell(5).setCellValue("Description");

     row.createCell(6).setCellValue("SELECT");

     row.createCell(7).setCellValue("WHERE");

     row.createCell(8).setCellValue("Access Level");

     row.createCell(9).setCellValue("Data Type");

     return sheet;

    }

    private Sheet exportBlItems(List<BlItem> blItems, Sheet sheet)

    {

    

      Iterator it = blItems.iterator();

            int rownum;

            while (it.hasNext())

            {

             rownum = sheet.getLastRowNum() + 1;

            

             BlItem blItem = (BlItem) it.next();

            

             Row row = sheet.createRow(rownum);

             row.createCell(0).setCellValue(getBlItemParentPath(blItem));

             row.createCell(1).setCellValue(blItem.getParent().getName());

             row.createCell(2).setCellValue(blItem.getName());

             row.createCell(3).setCellValue(blItem.getIdentifier());

             row.createCell(4).setCellValue(blItem.getState().toString());

             row.createCell(5).setCellValue(blItem.getDescription());

             if(blItem instanceof BusinessObject)

             {

              BusinessObject bObject = (BusinessObject) blItem;

              row.createCell(6).setCellValue(bObject.getSelect());

              row.createCell(7).setCellValue(bObject.getWhere());

              row.createCell(8).setCellValue(bObject.getAccessLevel().toString());

              row.createCell(9).setCellValue(bObject.getDataType().toString());

              List<CustomProperty> customProperties = blItem.getCustomProperties();

              Iterator itProps = customProperties.iterator();

              String strCustProperties ="";

              while(itProps.hasNext())

              {

               System.out.println("Custom Property");

               CustomProperty custProp = (CustomProperty) itProps.next();

               strCustProperties = strCustProperties + custProp.getKey() + " => " + custProp.getValue();

              

              

              }

              row.createCell(10).setCellValue(strCustProperties);

             

             }

             if(blItem instanceof BlContainer)

             {

              List<BlItem> blChildItems = ((BlContainer) blItem).getChildren();

              sheet = exportBlItems(blChildItems,sheet);

             }

            

            }

           

            return sheet;

    }

   

    public static String repeat(int count, String with) {

        return new String(new char[count]).replace("\0", with);

    }

    public static String repeat(int count) {

        return repeat(count, " ");

    }

    // Looks for a business item with the given name in the given container

    private BlItem findBlItem(BlItem container, String name) {

        if (name.equals(container.getName()))

            return container;

        if (container instanceof BlContainer) {

            for (BlItem item : ((BlContainer) container).getChildren()) {

                BlItem searchResult = findBlItem(item, name);

                if (searchResult != null)

                    return searchResult;

            }

        }

        return null;

    }

}