Export to Excel in Oracle WebCenter Sites

 

Following demonstrate exporting content present in a Map object to Excel sheet using java.

 

Create a Template in JSP

Insert the following code in ExportToExcel.jsp Template  

<%@page import=”java.io.FileNotFoundException”%>
<%@page import=”java.io.OutputStream”%>
<%@ taglib prefix=”cs” uri=”futuretense_cs/ftcs1_0.tld”
%><%@ taglib prefix=”asset” uri=”futuretense_cs/asset.tld”
%><%@ taglib prefix=”assetset” uri=”futuretense_cs/assetset.tld”
%><%@ taglib prefix=”commercecontext” uri=”futuretense_cs/commercecontext.tld”
%><%@ taglib prefix=”ics” uri=”futuretense_cs/ics.tld”
%><%@ taglib prefix=”listobject” uri=”futuretense_cs/listobject.tld”
%><%@ taglib prefix=”render” uri=”futuretense_cs/render.tld”
%><%@ taglib prefix=”siteplan” uri=”futuretense_cs/siteplan.tld”
%><%@ taglib prefix=”searchstate” uri=”futuretense_cs/searchstate.tld”
%><%@ page import=”COM.FutureTense.Interfaces.*,
COM.FutureTense.Util.ftMessage,
COM.FutureTense.Util.ftErrors”
%><%@ page import=”java.io.File,
java.io.FileOutputStream,
java.util.Map,
java.util.Set,
java.util.TreeMap,
org.apache.poi.ss.usermodel.Cell,
org.apache.poi.xssf.usermodel.XSSFRow,
org.apache.poi.xssf.usermodel.XSSFSheet,
org.apache.poi.xssf.usermodel.XSSFWorkbook”
%><cs:ftcs><%– /ExportToExcel
INPUT
OUTPUT
–%>
<%– Record dependencies for the Template –%>
<ics:if condition=’<%=ics.GetVar(“tid”)!=null%>’><ics:then><render:logdep cid=’<%=ics.GetVar(“tid”)%>’ c=”Template”/></ics:then></ics:if>
<%
//Create blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
//Create a blank sheet
XSSFSheet spreadsheet = workbook.createSheet(” CellLineResults “);
//Create row object
XSSFRow row;
//This data needs to be written (Object[])
Map < String, Object[] > cellrslt = new TreeMap < String, Object[] >();
cellrslt.put( “1″, new Object[] { “Title”, “Authors”, “Journal”, “Publication date”, “Research area”, “Cell Line”, “Cell Type”, “Species”, “XF Format”, “XF Assay”, “Seeding density”, “Plate coating” });
cellrslt.put( “2″, new Object[] { “10E,12Z-conjugated linoleic acid impairs adipocyte triglyceride storage by enhancing fatty acid oxidation, lipolysis, and mitochondrial reactive oxygen species”, ” Den Hartigh LJ, Han CY, Wang S, Omer M, Chait A”, ” J Lipid Res”, “01-11-2013″, “Obesity, Diabetes & Metabolic Disorders”, “3T3-L1″, “Fibroblasts”, “Mouse”, “96″, “Fatty Acid Oxidation”, “Not Specified”, “Not Specified” });
cellrslt.put( “3″, new Object[] { “10E,12Z-conjugated linoleic acid impairs adipocyte triglyceride storage by enhancing fatty acid oxidation, lipolysis, and mitochondrial reactive oxygen species”, ” Den Hartigh LJ, Han CY, Wang S, Omer M, Chait A”, ” J Lipid Res”, “01-11-2013″, “Obesity, Diabetes & Metabolic Disorders”, “3T3-L1″, “Fibroblasts”, “Mouse”, “96″, “Basal Metabolic Assay”, “Not Specified”, “Not Specified” });
cellrslt.put( “4″, new Object[] { “11β-HSD1 reduces metabolic efficacy and adiponectin synthesis in hypertrophic adipocytes”, “Koh EH, Kim AR, Kim H, Kim JH, Park HS, Ko MS, Kim MO, Kim HJ, Kim BJ, Yoo HJ, Kim SJ, Oh JS, Woo CY, Jang JE, Leem J, Cho MH, Lee KU”, “J Endocrinol”, “01-06-2015″, “Obesity, Diabetes & Metabolic Disorders”, “3T3-L1″, “Preadipocytes”, “Mouse”, “24″, “Cell Mitochondrial Stress Test”, “4.0 x10^4 cells/well”, “Not Specified” });
cellrslt.put( “5″, new Object[] { “11β-HSD1 reduces metabolic efficacy and adiponectin synthesis in hypertrophic adipocytes”, “Koh EH, Kim AR, Kim H, Kim JH, Park HS, Ko MS, Kim MO, Kim HJ, Kim BJ, Yoo HJ, Kim SJ, Oh JS, Woo CY, Jang JE, Leem J, Cho MH, Lee KU”, “J Endocrinol”, “01-06-2015″, “Obesity, Diabetes & Metabolic Disorders”, “3T3-L1″, “Preadipocytes”, “Mouse”, “24″, “Basal Metabolic Assay”, “4.0 x10^4 cells/well”, “Not Specified” });
cellrslt.put( “6″, new Object[] { “18O-Tracer Metabolomics Reveals Protein Turnover and CDP-Choline Cycle Activity in Differentiating 3T3-L1 Pre-Adipocytes”, “Kirkwood JS, Miranda CL, Bobe G, Maier CS, Stevens JF”, “PLoS One”, “01-06-2016″, “Obesity, Diabetes & Metabolic Disorders”, “3T3-L1″, “Preadipocytes”, “Mouse”, “24″, “Basal Metabolic Assay”, “3.0 x10^4 cells/well”, “Not Specified” });
cellrslt.put( “7″, new Object[] { “1α,25-Dihydroxyvitamin D3 Regulates Mitochondrial Oxygen Consumption and Dynamics in Human Skeletal Muscle Cells”, “Ryan ZC, Craig TA, Folmes CD, Wang X, Lanza IR, Schaible NS, Salisbury JL, Nair KS, Terzic A, Sieck GC, Kumar R”, “J Biol Chem”, “01-11-2015″, “Cell Physiology”, “Skeletal Muscle Cells”, “Muscle cells”, “Human”, “24″, “Cell Mitochondrial Stress Test”, “3.5 x10^3 cells/well”, “Not Specified” });
//Iterate over data and write to sheet
Set < String > keyid = cellrslt.keySet();
int rowid = 0;
for (String key : keyid)
{
row = spreadsheet.createRow(rowid++);
Object [] objectArr = cellrslt.get(key);
int cellid = 0;
for (Object obj : objectArr)
{
Cell cell = row.createCell(cellid++);
cell.setCellValue((String)obj);
}
}
try{
//Write the workbook in file system
FileOutputStream OutFile=new FileOutputStream(new File(“D://CellLineResults.xlsx”));
workbook.write(OutFile);
OutFile.close();
out.println( “CellLineResults.xlsx File is downloaded successfully” );
}
catch(Exception e){
e.printStackTrace();
}
%>
</cs:ftcs>

Useful POI Classes:

Apache POI main classes usually start with either HSSF, XSSF or SXSSF.

  • HSSF is the POI Project’s pure Java implementation of the Excel ’97(-2007) file format.
    e.g. HSSFWorkbook, HSSFSheet.
  • XSSF is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
    e.g. XSSFWorkbook, XSSFSheet.
  • SXSSF (since 3.8-beta3) is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited.
    e.g. SXSSFWorkbook, SXSSFSheet.
  1. Run the Template
  2. Open the browser.
  3. Enter the below URL to run the template:

localhost:port/cs/ContentServer?pagename=SiteName/TemplateName
For instance: (“http://localhost:9080/cs/ContentServer?pagename=TrainingSite/ExportToExcel”)

2_1

Output of Downloaded File in Excel

3_2

 

Creating Flex Family

5_0

Creating Child Definition and Associating Attributes to the Definition

6_0

 

 

Error Logging & Debugging in Oracle WebCenter Sites

Adding Dynamic Loggers
To add dynamic loggers, add the following code to log4j.properties file.

Screenshot: log4j.properties file

1

Screenshot: mylogs.log file is created inside the logs folder

errorlog

 

Updating Maximum File size for sites.log file
Screenshot: log4j.properties file

logger4j

 

Backup Files for sites.log file
Screenshot: log4j.properties file

backup

 

Configure log4j
1. Setting up Apache log4j:

If WebCenter Sites was not set up to use log4j during the upgrade process, you can manually switch to log4j by configuring the commons-logging.properties and log4j.properties files in the WEB-INF/classes directory.

To set up log4j:
In the commons-logging.properties file, update the following property to set log4j as WebCenter Sites’ logging system:
org.apache.commons.logging.Log= org.apache.commons.logging.impl.Log4JLogger

2. Viewing Loggers:

Loggers that are registered in the log4j.properties file, in the WEB-INF/classes directory. The file is read only on system start up. If, during runtime, you make changes to the log4j.properties file and save them, your changes will not be displayed in the log4j Configuration form. They will be applied and displayed when WebCenter Sites is restarted.

Note: The futuretense.txt file is typically located in the WebCenter Sites installation directory.

Loggers that are detected during runtime. The loggers are detected when:

  • System features or add-on components are accessed for the first time.
  • The loggers are added to the system manually by developers using the Add Logger.

To view loggers
On the Admin tab, expand System Tools and double-click Configure log4j.
The list of loggers with explicitly assigned levels is displayed.

Screenshot: log4j configuration formviewloggers

 

Creating Webroot for Web Center Sites

Below is a step by step illustration of how to create webroot for Web center Sites!

  • Go to the Admin Interface i.e Admin>WebRoots. Out of the box you will find the application deployed in the webapps>cs. Here I am building it for FirstSite(FSII)

Step 1

 

  • Add the URL Pattern, For that in the Admin Interface, goto>Admin>Asset Types>Page>URL Pattern>Add New

Step 2
Add the pattern as ${f:spaceToUnderscore(name). toLowerCase()}.jsp , this will be available on the right tab under functions.

 

  • Generate the URL in the contributor Interface: Go to the Home Page asset>Clicki Edit and then Enter the URL

Step 3

 

  • Next come back to the Admin Interface, Go to Admin>SystemTools>URL: You will find the URL that has been created. Make sure WebRoot is created

Step 4

 

  • Next configure URL rewrite filter, In my case the application is already configured, If it is a new application then configure it in the section mentioned in Italics <init-param> <param-name>SitePrefix</param-name> <param-value>fsii,avi</param-value> </init-param>

Step 5