Exporting GroupTable to Excel - line feed CRLF not showing in Excel

Support
#1

I have a Client entity with attribute “address”. The address is composed of text in multiple lines so I use “\n” as a line feed. In the client browse GroupTable, the address is displayed nicely once i turn on multiLineCells=“true” for the GroupTable.

However, once I export the data to Excel (using the built-in Excel button), the table cell containing address do not display the address in multiple lines but in one straight line.

In order for me to see multi-line, I have to format the address cells within Excel by turning on “Wrap Text” option

Is there a way in Cuba for me to see the line breaks automatically once exported ?

How do you all handle this scenario ?

#3

Hello Robert,

To achieve this you should extend ExcelExporter class and override formatValueCell(). For instance, for every String value we can do the following:

@Override
protected void formatValueCell(HSSFCell cell, @Nullable Object cellValue, @Nullable MetaPropertyPath metaPropertyPath, int sizersIndex, int notificationRequired, int level, @Nullable Integer groupChildCount) {
    super.formatValueCell(cell, cellValue, metaPropertyPath, sizersIndex, notificationRequired, level, groupChildCount);

    if (cellValue instanceof String) {
        HSSFCellStyle style = cell.getCellStyle();
        style.setWrapText(true);
    }
}

And in the browse screen subscribe on excel action performed event:

Browse controller code 
@Inject
private GroupTable customersTable;

@Inject
private ExportDisplay exportDisplay;

protected ExtExcelExporter excelExporter = new ExtExcelExporter();

@Subscribe("customersTable.excel")
public void onCustomersTableExcel(Action.ActionPerformedEvent event) {
    excelExporter.exportTable(customersTable, exportDisplay);
}

Also, you can create your Excel action and use it from screen descriptor, see how to create custom action.

#5

Thank you Roman.
Quick question:
Where do you recommend me placing this extended class ?
I placed it under modules/web , it works, but is that okay ?

#6

Yes, “modules/web” is ok.

#7

So I decided on creating my own Custom Action Type so that I can easily re-use it in other screens.

So for the final solution I created the following:
(1) New Action type extExcel
(2) New class ExtExcelExporter that extends class ExcelExporter
I created both in modules/web.

New Action Type:

package com.company.lms.web.actions;

import com.haulmont.cuba.core.entity.Entity;
import com.haulmont.cuba.core.global.BeanLocator;
import com.haulmont.cuba.core.global.Messages;
import com.haulmont.cuba.gui.ComponentsHelper;
import com.haulmont.cuba.gui.Dialogs;
import com.haulmont.cuba.gui.Notifications;
import com.haulmont.cuba.gui.Notifications.NotificationType;
import com.haulmont.cuba.gui.components.*;
import com.haulmont.cuba.gui.components.actions.ListAction;
import com.haulmont.cuba.gui.components.data.meta.ContainerDataUnit;
//import com.haulmont.cuba.gui.export.ExcelExporter;
import com.company.lms.web.extensions.ExtExcelExporter;
import com.haulmont.cuba.gui.export.ExportDisplay;
import com.haulmont.cuba.gui.icons.CubaIcon;
import com.haulmont.cuba.gui.icons.Icons;
import com.haulmont.cuba.gui.meta.StudioAction;
import com.haulmont.cuba.gui.meta.StudioPropertiesItem;
import com.haulmont.cuba.gui.model.CollectionContainer;

import javax.inject.Inject;
import java.util.List;
import java.util.stream.Collectors;

@StudioAction(category = "List Actions", description = "Exports a list of entities to XLS file")
@ActionType(ExtExcelAction.ID)
public class ExtExcelAction extends ListAction {

    public static final String ID = "extExcel";

    protected Messages messages;
    protected BeanLocator beanLocator;

    protected String fileName = null;

    /**
     * If true and table is aggregatable will export aggregation row to excel document.
     */
    protected boolean exportAggregation = true;

    public ExtExcelAction() {
        super(ID);
    }

    public ExtExcelAction(String id) {
        super(id);
    }

    @Inject
    protected void setIcons(Icons icons) {
        this.icon = icons.get(CubaIcon.EXCEL_ACTION);
    }

    @Inject
    protected void setMessages(Messages messages) {
        this.messages = messages;
        this.caption = messages.getMainMessage("actions.Excel");
    }

    @Inject
    protected void setBeanLocator(BeanLocator beanLocator) {
        this.beanLocator = beanLocator;
    }

    @Override
    public void actionPerform(Component component) {
        if (!hasSubscriptions(ActionPerformedEvent.class)) {
            execute();
        } else {
            super.actionPerform(component);
        }
    }

    /**
     * Executes the action.
     */
    public void execute() {
        if (target == null) {
            throw new IllegalStateException("ExcelAction target is not set");
        }

        if (needExportAll()) {
            export(ExtExcelExporter.ExportMode.ALL_ROWS);
        } else {
            AbstractAction exportSelectedAction = new AbstractAction("actions.export.SELECTED_ROWS", Status.PRIMARY) {
                @Override
                public void actionPerform(Component component) {
                    export(ExtExcelExporter.ExportMode.SELECTED_ROWS);
                }
            };
            exportSelectedAction.setCaption(messages.getMainMessage(exportSelectedAction.getId()));

            AbstractAction exportAllAction = new AbstractAction("actions.export.ALL_ROWS") {
                @Override
                public void actionPerform(Component component) {
                    export(ExtExcelExporter.ExportMode.ALL_ROWS);
                }
            };
            exportAllAction.setCaption(messages.getMainMessage(exportAllAction.getId()));

            Action[] actions = new Action[]{
                    exportSelectedAction,
                    exportAllAction,
                    new DialogAction(DialogAction.Type.CANCEL)
            };

            Dialogs dialogs = ComponentsHelper.getScreenContext(target).getDialogs();

            dialogs.createOptionDialog()
                    .withCaption(messages.getMainMessage("actions.exportSelectedTitle"))
                    .withMessage(messages.getMainMessage("actions.exportSelectedCaption"))
                    .withType(Dialogs.MessageType.CONFIRMATION)
                    .withActions(actions)
                    .show();
        }
    }

    protected boolean needExportAll() {
        if (target.getSelected().isEmpty()
                || !(target.getItems() instanceof ContainerDataUnit)) {
            return true;
        }
        CollectionContainer container = ((ContainerDataUnit) target.getItems()).getContainer();
        return container != null && container.getItems().size() <= 1;
    }

    public boolean isExportAggregation() {
        return exportAggregation;
    }

    @StudioPropertiesItem(defaultValue = "true")
    public void setExportAggregation(boolean exportAggregation) {
        this.exportAggregation = exportAggregation;
    }

    public String getFileName() {
        return fileName;
    }

    @StudioPropertiesItem
    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    /**
     * Export via {@link ExtExcelExporter}.
     */
    protected void export(ExtExcelExporter.ExportMode exportMode) {
        ExtExcelExporter exporter = new ExtExcelExporter();
        exporter.setExportAggregation(exportAggregation);

        Window window = ComponentsHelper.getWindowNN(target);

        ExportDisplay display = beanLocator.get(ExportDisplay.NAME);
        display.setFrame(window);

        if (target instanceof Table) {
            @SuppressWarnings("unchecked")
            Table<Entity> table = (Table<Entity>) target;
            exporter.exportTable(table, table.getNotCollapsedColumns(), false, display, null, fileName, exportMode);
        }

        if (target instanceof DataGrid) {
            @SuppressWarnings("unchecked")
            DataGrid<Entity> dataGrid = (DataGrid<Entity>) target;
            List<DataGrid.Column> columns = dataGrid.getVisibleColumns().stream()
                    .filter(col -> !col.isCollapsed())
                    .collect(Collectors.toList());
            exporter.exportDataGrid(dataGrid, columns, display, null, fileName, exportMode);
        }

        if (exporter.isXlsMaxRowNumberExceeded()) {
            Notifications notifications = ComponentsHelper.getScreenContext(target).getNotifications();

            notifications.create(NotificationType.WARNING)
                    .withCaption(messages.getMainMessage("actions.warningExport.title"))
                    .withDescription(messages.getMainMessage("actions.warningExport.message"))
                    .show();
        }
    }
}

Extend *ExcelReporter:

/*
Created by Robert Gilbert on 19-May-2020 so that cell contents in Excel will honor CRLF (text will be wrapped)
 */
package com.company.lms.web.extensions;

import com.haulmont.chile.core.model.MetaPropertyPath;
import com.haulmont.cuba.gui.export.ExcelExporter;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import javax.annotation.Nullable;

public class ExtExcelExporter extends ExcelExporter {
    //@Override
    protected void formatValueCell(HSSFCell cell, @Nullable Object cellValue, @Nullable MetaPropertyPath metaPropertyPath, int sizersIndex, int notificationRequired, int level, @Nullable Integer groupChildCount) {
        super.formatValueCell(cell, cellValue, metaPropertyPath, sizersIndex, notificationRequired, level, groupChildCount);

        if (cellValue instanceof String) {
            HSSFCellStyle style = cell.getCellStyle();
            style.setWrapText(true);
        }

        HSSFCellStyle style = cell.getCellStyle();
        style.setVerticalAlignment(VerticalAlignment.TOP);
    }
}