Java tutorial
/** * Copyright (C) 2005-2012 BetaCONCEPT Limited * * This file is part of Astroboa. * * Astroboa is free software: you can redistribute it and/or modify * it under the terms of the GNU Lesser General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * Astroboa is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public License * along with Astroboa. If not, see <http://www.gnu.org/licenses/>. */ package org.betaconceptframework.astroboa.commons.excelbuilder; import java.util.ArrayList; import java.util.Calendar; import java.util.Collection; import java.util.Collections; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.RichTextString; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.betaconceptframework.astroboa.api.model.BinaryChannel; import org.betaconceptframework.astroboa.api.model.CmsProperty; import org.betaconceptframework.astroboa.api.model.ComplexCmsProperty; import org.betaconceptframework.astroboa.api.model.ContentObject; import org.betaconceptframework.astroboa.api.model.SimpleCmsProperty; import org.betaconceptframework.astroboa.api.model.StringProperty; import org.betaconceptframework.astroboa.api.model.Topic; import org.betaconceptframework.astroboa.api.model.definition.CalendarPropertyDefinition; import org.betaconceptframework.astroboa.api.model.definition.CmsPropertyDefinition; import org.betaconceptframework.astroboa.api.model.definition.ContentObjectTypeDefinition; import org.betaconceptframework.astroboa.api.model.definition.StringPropertyDefinition; import org.betaconceptframework.astroboa.api.model.io.ResourceRepresentationType; import org.betaconceptframework.astroboa.api.service.DefinitionService; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * Class responsible to create an Excel sheet which * contains a list of one or more content objects of the same content type. * * Each sheet contains one or more rows which represent the values of one or * more content objects of the same content type. * * @author Gregory Chomatas (gchomatas@betaconcept.com) * @author Savvas Triantafyllou (striantafyllou@betaconcept.com) * */ public class ExcelSheetBuilder { private final Logger logger = LoggerFactory.getLogger(getClass()); private CreationHelper creationHelper; private HSSFCellStyle propertyNameCellStyle; private HSSFCellStyle stringPropertyValueCellStyle; private HSSFCellStyle integerPropertyValueCellStyle; private HSSFCellStyle doublePropertyValueCellStyle; private HSSFCellStyle datePropertyValueCellStyle; private HSSFCellStyle dateTimePropertyValueCellStyle; private String locale; private List<HelperRow> rows = new ArrayList<HelperRow>(); //Property Paths. Order is significant private List<String> propertyPaths = new ArrayList<String>(); private Map<String, RichTextString> headersForProperty = new HashMap<String, RichTextString>(); private int currentPropertyPathIndex; private String contentType; private DefinitionService definitionService; public ExcelSheetBuilder(DefinitionService definitionService, String contentType, String locale, CreationHelper creationHelper, HSSFCellStyle propertyNameCellStyle, HSSFCellStyle stringPropertyValueCellStyle, HSSFCellStyle integerPropertyValueCellStyle, HSSFCellStyle doublePropertyValueCellStyle, HSSFCellStyle datePropertyValueCellStyle, HSSFCellStyle dateTimePropertyValueCellStyle) { this.locale = locale; this.contentType = contentType; this.definitionService = definitionService; this.creationHelper = creationHelper; this.propertyNameCellStyle = propertyNameCellStyle; this.stringPropertyValueCellStyle = stringPropertyValueCellStyle; this.integerPropertyValueCellStyle = integerPropertyValueCellStyle; this.doublePropertyValueCellStyle = doublePropertyValueCellStyle; this.datePropertyValueCellStyle = datePropertyValueCellStyle; this.dateTimePropertyValueCellStyle = dateTimePropertyValueCellStyle; } public void clear() { locale = null; creationHelper = null; propertyNameCellStyle = null; stringPropertyValueCellStyle = null; integerPropertyValueCellStyle = null; doublePropertyValueCellStyle = null; datePropertyValueCellStyle = null; dateTimePropertyValueCellStyle = null; } public void addContentObject(ContentObject contentObject) { if (contentObject == null) { return; } if (!StringUtils.equals(contentType, contentObject.getContentObjectType())) { logger.warn( "Try to add object {} of type {} in sheet which is responsible to contain objects of type {}", new Object[] { contentObject.getSystemName(), contentObject.getContentObjectType(), contentType }); return; } //Create a new row with the values of all properties of the object HelperRow helperRow = new HelperRow(); rows.add(helperRow); fillRowWithValues(contentObject.getComplexCmsRootProperty(), helperRow); currentPropertyPathIndex = -1; } public void createSheet(HSSFWorkbook workbook) { Sheet sheet = instantiateSheet(workbook); addRowWithHeaders(sheet); for (HelperRow helperRow : rows) { Row contentObjectRow = sheet.createRow(sheet.getLastRowNum() + 1); int cellIndex = 1; for (String propertyPath : propertyPaths) { Cell contentObjectPropertyCell = contentObjectRow.createCell(cellIndex++); helperRow.addValueToCellForPath(propertyPath, contentObjectPropertyCell); } } autoSizeColumns(sheet); } private Sheet instantiateSheet(HSSFWorkbook workbook) { ContentObjectTypeDefinition contentObjectTypeDefinition = (ContentObjectTypeDefinition) definitionService .getCmsDefinition(contentType, ResourceRepresentationType.DEFINITION_INSTANCE, false); //Reorder property paths DefinitionOrderHelper definitionOrderHelper = new DefinitionOrderHelper(); contentObjectTypeDefinition.accept(definitionOrderHelper); Collections.sort(propertyPaths, definitionOrderHelper); // create a new sheet if (contentObjectTypeDefinition.getDisplayName() == null || StringUtils .isBlank(contentObjectTypeDefinition.getDisplayName().getAvailableLocalizedLabel(locale))) { return workbook.createSheet(contentType); } else { String sheetName = contentObjectTypeDefinition.getDisplayName().getAvailableLocalizedLabel(locale); //Remove invalid characters (according to POI) sheetName = StringUtils.replaceChars(sheetName, "/\\?*[]", ""); //SheetName must be at most 31 chars if (sheetName.length() > 31) { sheetName = sheetName.substring(0, 30); } return workbook.createSheet(sheetName); } } private void addRowWithHeaders(Sheet sheet) { Row headerRow = sheet.createRow(1); int headerCellIndex = 1; for (String propertyPath : propertyPaths) { Cell headerCell = headerRow.createCell(headerCellIndex++); headerCell.setCellStyle(propertyNameCellStyle); if (headersForProperty.containsKey(propertyPath)) { headerCell.setCellValue(headersForProperty.get(propertyPath)); } else { logger.warn("Coludn not find header for property {}. Will put Property path instead", propertyPath); } } } private void autoSizeColumns(Sheet sheet) { for (short columnIndex = 1; columnIndex <= propertyPaths.size(); ++columnIndex) { sheet.autoSizeColumn(columnIndex); } } private void fillRowWithValues(ComplexCmsProperty complexCmsProperty, HelperRow helperRow) { if (complexCmsProperty == null) { return; } if (StringUtils.equals(complexCmsProperty.getPath(), "accessibility")) { return; } Collection<List<CmsProperty<?, ?>>> childProperties = complexCmsProperty.getChildProperties().values(); for (List<CmsProperty<?, ?>> childPropertyList : childProperties) { for (CmsProperty<?, ?> childProperty : childPropertyList) { if (childProperty instanceof ComplexCmsProperty) { fillRowWithValues((ComplexCmsProperty) childProperty, helperRow); } else { fillRowWithValues((SimpleCmsProperty) childProperty, helperRow); } } } } private void fillRowWithValues(SimpleCmsProperty simpleProperty, HelperRow helperRow) { if (simpleProperty == null || simpleProperty.getPropertyDefinition() == null) { return; } CmsPropertyDefinition propertyDefinition = simpleProperty.getPropertyDefinition(); //Property of type password in not exported if (propertyDefinition instanceof StringPropertyDefinition && ((StringPropertyDefinition) propertyDefinition).isPasswordType()) { return; } if (simpleProperty.hasNoValues()) { return; } boolean multiple = propertyDefinition.isMultiple(); String path = simpleProperty.getPath(); if (multiple) { int index = 0; for (Object value : simpleProperty.getSimpleTypeValues()) { setPropertyCellStyleAndValue(path + "[" + index++ + "]", propertyDefinition, value, helperRow, creationHelper, locale); } } else { setPropertyCellStyleAndValue(path, propertyDefinition, simpleProperty.getSimpleTypeValue(), helperRow, creationHelper, locale); } } private void setPropertyCellStyleAndValue(String propertyPath, CmsPropertyDefinition propertyDefinition, Object value, HelperRow row, CreationHelper creationHelper, String locale) { appendPropertyPathList(propertyPath); appendHeaderPropertyList(propertyPath, propertyDefinition); switch (propertyDefinition.getValueType()) { case String: { row.addValueAndStyleForPath(propertyPath, creationHelper.createRichTextString(((String) value)), stringPropertyValueCellStyle); break; } case Date: { if (((CalendarPropertyDefinition) propertyDefinition).isDateTime()) { row.addValueAndStyleForPath(propertyPath, (Calendar) value, dateTimePropertyValueCellStyle); } else { row.addValueAndStyleForPath(propertyPath, (Calendar) value, datePropertyValueCellStyle); } break; } case TopicReference: { String cellValue = getTopicPath((Topic) value, locale); row.addValueAndStyleForPath(propertyPath, creationHelper.createRichTextString(cellValue), stringPropertyValueCellStyle); break; } case Double: { row.addValueAndStyleForPath(propertyPath, (Double) value, doublePropertyValueCellStyle); break; } case Long: { row.addValueAndStyleForPath(propertyPath, (Long) value, integerPropertyValueCellStyle); break; } case ObjectReference: { String cellValue = getContentObjectTitle((ContentObject) value, locale); row.addValueAndStyleForPath(propertyPath, creationHelper.createRichTextString(cellValue), stringPropertyValueCellStyle); break; } case Binary: { String cellValue = ((BinaryChannel) value).buildResourceApiURL(null, null, null, null, null, true, false); row.addValueAndStyleForPath(propertyPath, creationHelper.createRichTextString(cellValue), stringPropertyValueCellStyle); break; } default: break; } } private void appendHeaderPropertyList(String propertyPath, CmsPropertyDefinition propertyDefinition) { if (!headersForProperty.containsKey(propertyPath)) { headersForProperty.put(propertyPath, creationHelper .createRichTextString(propertyDefinition.getDisplayName().getLocalizedLabelForLocale(locale))); } } private void appendPropertyPathList(String propertyPath) { if (!propertyPaths.contains(propertyPath)) { if (currentPropertyPathIndex == -1) { propertyPaths.add(propertyPath); } else { propertyPaths.add(currentPropertyPathIndex, propertyPath); } } currentPropertyPathIndex = propertyPaths.indexOf(propertyPath); } private String getTopicPath(Topic topic, String locale) { if (topic != null) { return topic.getAvailableLocalizedLabel(locale); } return ""; /*List<Topic> topicPath = new ArrayList<Topic>(); createTopicPath(topicPath, topic); String topicPathAsString = topicPath.get(0).getLocalizedLabelForLocale(locale); for(Topic topicInPath : topicPath.subList(1, topicPath.size())) { topicPathAsString = topicPathAsString + "/" + topicInPath.getLocalizedLabelForLocale(locale); } return topicPathAsString;*/ } private String getContentObjectTitle(ContentObject object, String locale) { if (object == null) { return ""; } String label = object.getLabel(locale); if (StringUtils.isNotBlank(label)) { return label; } StringProperty titleProperty = (StringProperty) object.getCmsProperty("profile.title"); if (titleProperty == null || titleProperty.hasNoValues()) { //this should never happen return object.getSystemName(); } return titleProperty.getFirstValue(); } }