Alfresco Webscript : Export Data In Excel
There has been always requirement in one or another way to export data in excel or CSV. There are many developers who are find difficulties in developing a feature like this.Gagravarr has given a very good example on exporting datalist in to an excel file.Main files which are responsible for downloading excel file and generating data are on git. One problem in this code is that parent webscript class is DeclarativeWebscript. This shouldn't have been used as in this kind of webscript controlling the response becomes more complicated. In the code written by Gagravarr , controlling the response type is implemented by overriding methods named as createTemplateParameters and creating a new subclass named as WriteExcel and putting object of a WriteExcel class in model variable and finally it calls the write method of WriteExcel class from the response templete(freemarker).
We can definitely customize this for any kind of data which we want to export in excel file.The main issue with this code is , its too complicated to understand,So i finally decided to create a simpler version of it for those who are starting up with alfresco.In real time scenario I have taken a reference from code written by Gagravarr , as I am able to fully understood it.
So let us begin on how we can export any alfresco data in a excel spreadsheet.
We can definitely customize this for any kind of data which we want to export in excel file.The main issue with this code is , its too complicated to understand,So i finally decided to create a simpler version of it for those who are starting up with alfresco.In real time scenario I have taken a reference from code written by Gagravarr , as I am able to fully understood it.
So let us begin on how we can export any alfresco data in a excel spreadsheet.
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++;
}
}
}
Now let's understand few of the things from above code and how to customize it.I have added comments on method, they are well explanatory.There are few things which I would like to explain further more on this.So let's begin on that.
- 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
Action For Exporting Children Details in Excel
We have seen generic example on how we can export data in excel from alfresco using webscript. Now let's add some real time example in it. We will add one custom action in alfresco share on folder named as Export Data. What this action will do is that it will export all the data of its children in excel file.Exported excel file will have only few common properties in it like Name, Title , Created Date etc..
For adding action on share side you can refer Share DocumentLibrary Action blog for more details.For adding custom action you need to follow below steps.
There are multiple ways of adding custom action in share, you can add it in share-config-custom.xml or as a new approach you can add xml file inside site-data/extension. We will go by second approach.
Export Action On Folders 1.0 true onActionExportData evaluator.doclib.action.isFolderType
(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 ListgetHeaderList() { List listString = new ArrayList<>(); listString.add("Name"); listString.add("Title"); listString.add("Description"); listString.add("Created Date"); return listString; }
Third function which we need to update is generateDataInExcel,In this we need to include an argument folder node and pass it in to getData function.One more change in this function which we need to do it handling the Date object, there things which we need to export are of string type those are already handled in this function.We need to handle an date object here.Function will be updated like below for that.
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.
