Krutik Jayswal

For exporting data in excel there no special case.The only artifact which we need to consider is java backed webscript. So let's begin with same.

As usual we will be needing one descriptor file.

  Export to Excel Webscript
  Sample Export to excel webscript
  /export/excel/
  argument
  user
  required

Now we need to create one spring bean for java baked webscript.

Java File For Exporting Data

package com.export;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.alfresco.repo.content.MimetypeMap;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.extensions.webscripts.AbstractWebScript;
import org.springframework.extensions.webscripts.WebScriptRequest;
import org.springframework.extensions.webscripts.WebScriptResponse;

public class ExportDataToExcel extends AbstractWebScript {
 private String fileName = "ExcelSheet";
 private String format = "xlsx";

 /**
  * This method is inherited from AbstractWebscript class and called
  * internally.
  */
 @Override
 public void execute(WebScriptRequest req, WebScriptResponse res) throws IOException {
  writeDataInExcel(res);
 }

 /**
  * 
  * @param res
  *            Used for controlling the response.In our case we want to give
  *            file as response.
  * @param data
  *            Content which we need to write in excel file
  * @throws IOException
  */
 public void ResponseDataInExcel(WebScriptResponse res, byte[] data) throws IOException {
  String filename = this.fileName + "." + this.format;
  // Set Header for downloading file.
  res.addHeader("Content-Disposition", "attachment; filename=" + filename);
  // Set spredsheet data
  byte[] spreadsheet = null;
  res.setContentType(MimetypeMap.MIMETYPE_OPENXML_SPREADSHEET);
  res.getOutputStream().write(data);
 }

 /**
  * 
  * @return List of header which we need to write in excel
  */
 public List<String> getHeaderList() {
  List<String> listString = new ArrayList<>();
  listString.add("Header1");
  listString.add("Header2");
  listString.add("Header3");
  listString.add("Header4");
  return listString;
 }

 /**
  * This method is used for creating multiple row.So the inner list object
  * will contain the cell details and the outer one will contain row details
  * 
  * @return List of values which we need to write in excel
  */
 public List<List<Object>> getData() {
  List<List<Object>> listString = new ArrayList<>();
  // Adding sample data
  // ******************
  // Creating sample Row 1
  List<Object> sampleRow1 = new ArrayList<>();
  sampleRow1.add("sampleCell11");
  sampleRow1.add("sampleCell12");
  sampleRow1.add("sampleCell13");
  sampleRow1.add("sampleCell14");

  // Creating sample Row 2
  List<Object> sampleRow2 = new ArrayList<>();
  sampleRow2.add(new Employee("Jhon"));
  sampleRow2.add(new Employee("Ethan"));
  sampleRow2.add(new Employee("Kevin"));
  sampleRow2.add(new Employee("Mike"));

  // Adding sample row in row list
  listString.add(sampleRow1);
  listString.add(sampleRow2);

  return listString;
 }

 /**
  * 
  * This method is used for Writing data in byte array.It is calling other
  * methods as well which will help to generate data.
  * 
  * @param res
  *            Writing content in output stream
  * @throws IOException
  */
 public void writeDataInExcel(WebScriptResponse res) throws IOException {
  // Create Work Book and WorkSheet
  Workbook wb = new XSSFWorkbook();
  Sheet sheet = wb.createSheet("ExcelFile");
  sheet.createFreezePane(0, 1);

  generateHeaderInExcel(sheet);
  generateDataInExcel(sheet);
  ByteArrayOutputStream baos = new ByteArrayOutputStream();
  wb.write(baos);
  ResponseDataInExcel(res, baos.toByteArray());
 }

 /**
  * Used for generating header in excel.
  * 
  * @param sheet
  *            It is an excel sheet object, where the header will be created
  */
 public void generateHeaderInExcel(Sheet sheet) {
  List<String> headerValues = getHeaderList();
  Row hr = sheet.createRow(0);
  for (int i = 0; i < headerValues.size(); i++) {
   Cell c = hr.createCell(i);
   c.setCellValue(headerValues.get(i));
  }
 }

 /**
  * Used for generating data in excel sheet
  * 
  * @param sheet
  *            sheet It is an excel sheet object, where the data will be
  *            written
  */
 public void generateDataInExcel(Sheet sheet) {
  List<List<Object>> listOfData = getData();

  // Give first row as 1 and column as 0
  int rowNum = 1, colNum = 0;
  for (List<Object> rowValues : listOfData) {
   Row r = sheet.createRow(rowNum);
   colNum = 0;
   for (Object obj : rowValues) {
    Cell c = r.createCell(colNum);
    // Here you can add n number of condition for identifying the
    // type of object and based on that fetch value of it
    if (obj instanceof String) {
     c.setCellValue(obj.toString());
    } else if (obj instanceof Employee) {
     c.setCellValue(((Employee) obj).getName());
    }
    colNum++;
   }
   rowNum++;
  }
 }
}

• You can update header values from generateHeaderInExcel  function.
• Use spring injection to use alfresco services.
• For adding your data , you can write your own logic in generateDataInExcel function.Required parameters can be passed here.For an example if you want to get details of node, then you can write one function which will return list of nodes and useing nodeService, fetch details and set to to the excel cell
• Excel file is created using apache poi api. You can use this API for any kind of formatting. There is a very good example explained in this link

 
  
   Export Action On Folders
   1.0
   true
   
    
     
      
     
    
    
     
      
       onActionExportData
       evaluator.doclib.action.isFolderType
      
     
     
      
       
      
      
       
      
     
    
   
  
 

In above code there are few things two consider, First one is custom action which we have added, custom action is type of javascript action, so we have specified function name as argument.Now this javascript function need to be defined in some javascript file, because of this reason only we have included a file called export-doclib-actions.js. Content of this file are as below.

(function() {
 YAHOO.Bubbling.fire("registerAction", {
  actionName : "onActionExportData",
  fn : function com_pdf_flipview_onActionExportData(file) {
   window.location.href = Alfresco.constants.PROXY_URI+"export/excel/?nodeRef="+file.nodeRef;
  }
 });
})();

If you see function, there is an argument called file.This is an javascript object , which has the data related to node on which the action is performed.As we will need nodeRef of folder, we will pass it as argument to repository webscript.

Now let's make required changes in webscript.As we need to fetch properties from node we need to inject nodeService in webscript.For that we need to update bean definition as below.

Apart from this there are few function which we need to update.The first one is execute(WebScriptRequest req, WebScriptResponse res),In this function we need to fetch the nodeRef which is passed from client side and send it to another function , for using it for future purpose.

@Override
 public void execute(WebScriptRequest req, WebScriptResponse res) throws IOException {
  String nodeRefParam=req.getParameter("nodeRef");
  if(nodeRefParam!=null){
   
   writeDataInExcel(res,new NodeRef(nodeRefParam));
  }
 }

Second function which we need to update is getHeaderList This function includes the details of header.We need to add list of column name which we need to populate.

public List getHeaderList() {
  List listString = new ArrayList<>();
  listString.add("Name");
  listString.add("Title");
  listString.add("Description");
  listString.add("Created Date");
  return listString;
 }

public void generateDataInExcel(Sheet sheet,NodeRef folderNode) {
  List<List<Object>> listOfData = getData(folderNode);

  // Give first row as 1 and column as 0
  int rowNum = 1, colNum = 0;
  for (List<Object> rowValues : listOfData) {
   Row r = sheet.createRow(rowNum);
   colNum = 0;
   for (Object obj : rowValues) {
    Cell c = r.createCell(colNum);
    // Here you can add n number of condition for identifying the
    // type of object and based on that fetch value of it
    if (obj instanceof String) {
     c.setCellValue(obj.toString());
    } else if (obj instanceof Employee) {
     c.setCellValue(((Employee) obj).getName());
    } else if (obj instanceof Date) {
     c.setCellValue(obj.toString());
    }
    colNum++;
   }
   rowNum++;
  }
 }

Now in getData function, we need to get actual data from children and return an list of it.Code should be updated for that function like below.

public List<List<Object>> getData(NodeRef folderNode) {
  List<List<Object>> listData = new ArrayList<>();
  
  List<ChildAssociationRef> childAssociationRefList=nodeService.getChildAssocs(folderNode);
  for(ChildAssociationRef child:childAssociationRefList){
   List<Object> row = new ArrayList<>();
   row.add(nodeService.getProperty(child.getChildRef(),ContentModel.PROP_NAME));
   row.add(nodeService.getProperty(child.getChildRef(),ContentModel.PROP_TITLE));
   row.add(nodeService.getProperty(child.getChildRef(),ContentModel.PROP_DESCRIPTION));
   row.add(nodeService.getProperty(child.getChildRef(),ContentModel.PROP_CREATED));
   listData.add(row);
  }
  return listData;
 }

Once all above things are done , you will find an action on foldernamed as Export Data, on click of it , it will export data of its children in excel.

Github link for above example is here.