Generate a DOCX Report From Your Trello Board

I have been using the Trello board over the last few months for project management. During this period, I needed to generate reports via Word or DOCX, and among the constraints, I found that there are cards written in different languages.

In this article, I will provide a detailed Java program that can generate a DOCX report of a Trello board and translate content into a single output language, basing it on Google Translation and Cloud API Translation.

The generated DOCX document is based on a prepared DOCX template where fields are predefined as below:

Trello board content must replace the field PROJECT_TITLE with the board name. Along with that, replace the WRITER_NAME with the writer name and DATE with the current date.

We'll need these dependencies in pom.xml:

<dependency>
    <groupId>com.julienvey.trello</groupId>
    <artifactId>trello-java-wrapper</artifactId>
    <version>0.3.2</version>
</dependency>
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
</dependency>
<dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>google-cloud-translate</artifactId>
    <version>1.31.0</version>
</dependency>
<dependency>
    <groupId>com.optimaize.languagedetector</groupId>
    <artifactId>language-detector</artifactId>
    <version>0.6</version>
</dependency>
<dependency>
    <groupId>org.docx4j</groupId>
    <artifactId>docx4j</artifactId>
    <version>3.3.7</version>
</dependency>
<dependency>
    <groupId>org.apache.velocity</groupId>
    <artifactId>velocity</artifactId>
    <version>1.7</version>
</dependency>
<dependency>
    <groupId>com.google.guava</groupId>
    <artifactId>guava</artifactId>
    <version>23.6-jre</version>
</dependency>

Here are some important terms to note:

 language-detector : Used to detect the language

 docx4j : Used to create the DOCX document from the template

 trello-java-wrapper : The Trello Java framework, for that reason, contains some required security configurations. You can check out the details in this link.

 google-cloud-translate : Used to translate cards content, note that the developer must have Google Cloud account to use the Google Translation API.

Algorithm

1. Authenticate the Trello board with the API key and access tokens

2. Read the data of the given board key 

3. Detect the language in every card

4. Translate when needed

5. Insert the board data in the DOCX template 

6. Generate a DOCX Document

Configuration Requirements

Below are the required security parameters:

accessToken=YOUR_TRELLO_ACCESS_TOKEN
applicationKey=YOUR_TRELLO_APPLICATION_KEY
private_key=YOUR_GOOLE_CLOUD_PRIVATE_KEY
defaultLanguage=en

Business Layer Trello Service is shown below:

package com.roc.trello.service;

import java.text.SimpleDateFormat;
import java.time.Duration;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CompletableFuture;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.julienvey.trello.Trello;
import com.julienvey.trello.domain.Argument;
import com.julienvey.trello.domain.Board;
import com.julienvey.trello.domain.Card;
import com.julienvey.trello.domain.TList;
import com.julienvey.trello.impl.TrelloImpl;
import com.roc.trello.enums.BoardKeyEnum;
import com.roc.trello.model.RocCard;
import com.roc.trello.model.RocList;
import com.roc.trello.utils.TranslationUtils;

@Service
public class TrelloService {

private static final String DURATION_TIME_PATTERN = "HH:mm:ss";

private static final String ID_BOARD_KEY = "idBoard";

protected static final SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-YYYY");

private final static Logger LOG = LoggerFactory.getLogger(TrelloService.class);

protected final Gson gson = new GsonBuilder().setPrettyPrinting().create();

/**
 * 
 * @param boardId
 * @param writerName
 * @param applicationKey
 * @param accessToken
 * @return map board details
 */
public HashMap<String, String> getBoardDetail(final String boardId, final String writerName,
final String applicationKey, final String accessToken) {
long startTime = System.currentTimeMillis();

Trello trelloApi = new TrelloImpl(applicationKey, accessToken);
Board board = trelloApi.getBoard(boardId);

List<String> members = new ArrayList<>();

board.fetchMembers().stream().forEach(m -> {
members.add(m.getFullName());
});

final String projectTitle = board.getName();

final Argument anyArg = new Argument("", "");

final List<TList> lists = board.fetchLists(anyArg);

final Map<String, RocList> rocListMap = new HashMap<>();

lists.stream().forEach(tl -> {
RocList rocList = new RocList();
rocList.setIdList(tl.getId());
rocList.setListName(tl.getName());
rocListMap.put(tl.getId(), rocList);

});

List<Card> cards = board.fetchCards(new Argument(ID_BOARD_KEY, boardId));

cards.stream().forEach(c -> {

RocCard rocCard = new RocCard();

rocCard.setIdCard(c.getId());
rocCard.setIdList(c.getIdList());
rocCard.setCardName(c.getName());

c.getActions(anyArg).stream().forEach(a -> {
String text = a.getData().getText();
String language = TranslationUtils.detectLanguage(text);
LOG.info("Detected language: "+language);

rocCard.addAction(text);

});

rocListMap.get(c.getIdList()).addCard(rocCard);
});

final HashMap<String, String> details = new HashMap<>();

details.put(BoardKeyEnum.DATE.name(), sdf.format(new Date()));
details.put(BoardKeyEnum.PROJECT_TITLE.name(), projectTitle);
details.put(BoardKeyEnum.WRITER_NAME.name(), writerName);
details.put(BoardKeyEnum.MEMBERS.name(), StringUtils.arrayToCommaDelimitedString(members.toArray()));
details.put(BoardKeyEnum.DOC_CONTENT.name(), gson.toJson(rocListMap));


long millsDiff = System.currentTimeMillis() - startTime;

Duration d = Duration.ofMillis(millsDiff);

String durationTime = LocalTime.MIDNIGHT.plus(d).format(DateTimeFormatter.ofPattern(DURATION_TIME_PATTERN));

LOG.info("Execution time ====>" + durationTime);

return details;
}

}

The Business Layer Document Management System Service is also shown below:

package com.roc.trello.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.xml.bind.JAXBElement;

import org.docx4j.openpackaging.exceptions.Docx4JException;
import org.docx4j.openpackaging.packages.WordprocessingMLPackage;
import org.docx4j.openpackaging.parts.WordprocessingML.MainDocumentPart;
import org.docx4j.wml.ContentAccessor;
import org.docx4j.wml.Text;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.roc.trello.enums.BoardKeyEnum;
import com.roc.trello.utils.TranslationUtils;

@Service("dmsService")
public class DmsService {

private static final String DOCX_EXTENSION = ".docx";
private static final String TEMPLATE_NAME = "Template.docx";

@Value("${defaultLanguage}")
protected String defaultLanguage;

private final static Logger LOG = LoggerFactory.getLogger(DmsService.class);

private WordprocessingMLPackage getTemplate(String templatePath) throws Docx4JException, FileNotFoundException {
WordprocessingMLPackage template = WordprocessingMLPackage.load(new FileInputStream(new File(templatePath)));
return template;
}

private void writeDocxToStream(WordprocessingMLPackage template, String target)
throws IOException, Docx4JException {
File f = new File(target);
f.createNewFile();
template.save(f);

}

/**
 * 
 * @param template
 * @param name
 * @param placeholder
 * @throws UnsupportedEncodingException
 */
private void replacePlaceholder(WordprocessingMLPackage template, String name, String placeholder) {
List<Object> texts = getAllElementFromObject(template.getMainDocumentPart(), Text.class);

for (Object text : texts) {
Text textElement = (Text) text;
if (textElement.getValue().trim().equals(placeholder.trim())) {
textElement.setValue(name);
return;
}
}
}

@SuppressWarnings("unchecked")
private void insertListParagraph(WordprocessingMLPackage template, HashMap<String, Object> tList) {
MainDocumentPart mdp = template.getMainDocumentPart();

String listName = (String) tList.get(BoardKeyEnum.listName.name());
mdp.addStyledParagraphOfText("RocCardTitle", listName);
List<Object> cards = (List<Object>) tList.get(BoardKeyEnum.cards.name());
cards.stream().forEach(o -> {
LinkedHashMap<String, Object> c = (LinkedHashMap<String, Object>)o;
mdp.addStyledParagraphOfText("CardNameTitle", (String) c.get("cardName"));
((List<String>)c.get("actions")).stream().forEach(a -> {
String lang = TranslationUtils.detectLanguage(a);
String langText = a;
if (!StringUtils.isEmpty(lang) && !lang.equals(defaultLanguage)) {
langText = TranslationUtils.translate(a, lang, defaultLanguage);
}
mdp.addStyledParagraphOfText("CardAction", langText);
});

});

}

/**
 * 
 * @param obj
 * @param toSearch
 * @return
 */
private static List<Object> getAllElementFromObject(Object obj, Class<?> toSearch) {
List<Object> result = new ArrayList<Object>();
if (obj instanceof JAXBElement)
obj = ((JAXBElement<?>) obj).getValue();

if (obj.getClass().equals(toSearch))
result.add(obj);
else if (obj instanceof ContentAccessor) {
List<?> children = ((ContentAccessor) obj).getContent();
for (Object child : children) {
result.addAll(getAllElementFromObject(child, toSearch));
}

}
return result;
}


@SuppressWarnings("unchecked")
public void generateDoc(HashMap<String, String> details) {

WordprocessingMLPackage template;
try {
String templatePath = DmsService.class.getClassLoader().getResource(TEMPLATE_NAME).getFile();
template = getTemplate(templatePath);

replacePlaceholder(template, String.valueOf(details.get(BoardKeyEnum.DATE.name())), BoardKeyEnum.DATE.name());
replacePlaceholder(template, String.valueOf(details.get(BoardKeyEnum.MEMBERS.name())), BoardKeyEnum.MEMBERS.name());
replacePlaceholder(template, String.valueOf(details.get(BoardKeyEnum.PROJECT_TITLE.name())),
BoardKeyEnum.PROJECT_TITLE.name());

replacePlaceholder(template, String.valueOf(details.get(BoardKeyEnum.WRITER_NAME.name())), BoardKeyEnum.WRITER_NAME.name());

Map<String, HashMap<String, Object>> listMap = new ObjectMapper()
.readValue(details.get(BoardKeyEnum.DOC_CONTENT.name()), Map.class);


listMap.values().stream().forEach(tList -> {
insertListParagraph(template, tList);
});



writeDocxToStream(template, System.getProperty("user.dir") + "\\generated\\"
+ details.get(BoardKeyEnum.PROJECT_TITLE.name()) + DOCX_EXTENSION);

} catch (Docx4JException | IOException e) {
LOG.error(e.getMessage(), e);
}

}

}
-

Note that the  RocCardTitle , CardNameTitle , and  CardAction are customized and created on the DOCX template.

Models

Here is a look at the board model:

package com.roc.trello.model;

import java.util.ArrayList;
import java.util.List;

public class RocBoard {

private String idBoard, boardName;
List<String> members;

private List<RocList> lists = new ArrayList<>();

public String getIdBoard() {
return idBoard;
}

public void setIdBoard(String idBoard) {
this.idBoard = idBoard;
}

public void setBoardName(String boardName) {
this.boardName = boardName;
}

public String getBoardName() {
return boardName;
}

public List<RocList> getLists() {
return lists;
}

public List<String> getMembers() {
return members;
}

public void setMembers(List<String> members) {
this.members = members;
}
public void setLists(List<RocList> lists) {
this.lists.clear();
this.lists.addAll(lists);
}

public void addList(RocList list) {
this.lists.add(list);
}

}

Card Model:

package com.roc.trello.model;

import java.util.ArrayList;
import java.util.List;

public class RocCard {

private String cardName, idCard, idList;

private List<String> actions = new ArrayList<>();

public RocCard() {

}

public String getCardName() {
return cardName;
}

public void setCardName(String cardName) {
this.cardName = cardName;
}

public String getIdCard() {
return idCard;
}

public void setIdCard(String idCard) {
this.idCard = idCard;
}

public String getIdList() {
return idList;
}

public void setIdList(String idList) {
this.idList = idList;
}

public List<String> getActions() {
return actions;
}

public void setActions(List<String> actions) {
this.actions.clear();
this.actions.addAll(actions);
}

public void addAction(String action) {
this.actions.add(action);
}

@Override
public String toString() {
return "RocCard [cardName=" + cardName + ", actions=" + actions.toString() + "]";
}
}

package com.roc.trello.model;

import java.util.ArrayList;
import java.util.List;

public class RocList {

private String idList, listName;

private List<RocCard> cards = new ArrayList<>();

public RocList() {

}

public String getIdList() {
return idList;
}

public void setIdList(String idList) {
this.idList = idList;
}

public String getListName() {
return listName;
}

public void setListName(String listName) {
this.listName = listName;
}

public List<RocCard> getCards() {
return cards;
}

public void setCards(List<RocCard> cards) {
this.cards.clear();
this.cards.addAll(cards);
}

public void addCard(RocCard card) {
this.cards.add(card);
}

}

Utils

 BoardUtils  manages reading board data via the board key:

package com.roc.trello.utils;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;

import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

public final class BoardUtils {

/**
 * get idBoard from board key
 * @param boardKey
 * @param accessKey
 * @param token
 * @return ID Borad
 * @throws MalformedURLException
 * @throws IOException
 */
public static String getBordId(String boardKey, String accessKey, String token)
throws MalformedURLException, IOException {
String sURL = "https://trello.com/b/" + boardKey + "/reports.json?key=" + accessKey + "&token=" + token;

URL url = new URL(sURL);
URLConnection request = url.openConnection();
request.connect();

// Convert to a JSON object to print data
JsonParser jp = new JsonParser(); // from gson
JsonElement boardRoot = jp.parse(new InputStreamReader((InputStream) request.getContent()));
JsonObject boardObj = boardRoot.getAsJsonObject();

String idBoard = boardObj.get("id").getAsString();

return idBoard;
}
}

 TranslationUtils manages the ability to detect and translate languages:

package com.roc.trello.utils;

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

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import com.google.cloud.translate.Translate;
import com.google.cloud.translate.TranslateOptions;
import com.google.cloud.translate.Translation;
import com.google.cloud.translate.Translate.TranslateOption;
import com.google.common.base.Optional;
import com.optimaize.langdetect.LanguageDetector;
import com.optimaize.langdetect.LanguageDetectorBuilder;
import com.optimaize.langdetect.i18n.LdLocale;
import com.optimaize.langdetect.ngram.NgramExtractors;
import com.optimaize.langdetect.profiles.LanguageProfile;
import com.optimaize.langdetect.profiles.LanguageProfileReader;
import com.optimaize.langdetect.text.CommonTextObjectFactories;
import com.optimaize.langdetect.text.TextObject;
import com.optimaize.langdetect.text.TextObjectFactory;

public final class TranslationUtils {

private static final Logger LOG = LoggerFactory.getLogger(TranslationUtils.class);

public static String translate(String text, String sourceLanguage, String targetLanguage) {
Translate translate = TranslateOptions.getDefaultInstance().getService();
Translation translation = translate.translate(text, TranslateOption.sourceLanguage(sourceLanguage),
TranslateOption.targetLanguage(targetLanguage));
return translation.getTranslatedText();
}

/**
 * Example
 * <p>
 * text: Hi how you doing
 * </p>
 * <p>
 * detected language : en
 * </p>
 * 
 * @param text
 *            Given text
 * @return detected language
 */
public static String detectLanguage(String text) {
if (StringUtils.isEmpty(text)) {
return "Unknown";
}
try {
List<LanguageProfile> languageProfiles = new LanguageProfileReader().readAllBuiltIn();

LanguageDetector languageDetector = LanguageDetectorBuilder.create(NgramExtractors.standard())
.withProfiles(languageProfiles).build();

TextObjectFactory textObjectFactory = CommonTextObjectFactories.forDetectingOnLargeText();

TextObject textObject = textObjectFactory.forText(text);

Optional<LdLocale> lang = languageDetector.detect(textObject);

if (lang.isPresent()) {
return lang.get().getLanguage();

}
} catch (IOException e) {
LOG.error(e.getMessage(), e);
}
return "Unknown";
}

}

The code above is intended to help anyone create a DOCX document with the Trello board.

Hope this helps with your next Trello board project. Let me know what you think in the comments below!