Customize excel exporting

Support
#1

Hello everyone,

I’m trying to create a button which could export excel format with data from my database (It’s like export feature on your model table, but just without a table).
So my questions are: Does cuba support to do that thing? And if it does, how can I do it (even a trick is ok)?

Thank you guys!

Cuba excel exporting
#3

Hello,

I guess the best option is to use the Report add-on, but there are some other options:

  1. Use Apache Poi library directly and export workbook with ExportDisplay bean. This is what ExcelExporter does. You can find examples there (ExcelExporter).

  2. In the controller or service map loaded data to the entity (persistent or non-persistent). Create table and CollectionContainer with loaded data and export this table. Demo project: fexport.zip (83.8 KB)

Second option example 
@Inject
private ExportDisplay exportDisplay;
@Inject
private LoadCustomerService loadCustomerService;
@Inject
private UiComponents uiComponents;
@Inject
private DataComponents dataComponents;

@Subscribe("exportBtn")
public void onExportBtnClick(Button.ClickEvent event) {
    List<Customer> list = loadCustomerService.load();
    CollectionContainer<Customer> container = dataComponents.createCollectionContainer(Customer.class);
    container.setItems(list);

    Table<Customer> table = uiComponents.create(Table.NAME);
    table.setItems(new ContainerTableItems<>(container));
    ExcelExporter exporter = new ExcelExporter();

    exporter.exportTable(table, exportDisplay);
}
#5

The second option is what I’m looking for. One more thing is if we dont use entity classes, how the code will be look like? Can you please give me some more example for this?

Because I aim to get data from db using native query, so entity classes may not be invoked.
Thank you!

#6

Without entity you should use the first approach: get data from database and creating manually Excel workbook using POI.

For the second approach you can get data from database and map it to the non-persistent entity (like a DTO):

Non-persistent entity 
@MetaClass(name = "fexport_CustomerDto")
@NamePattern("%s|name")
public class ExcelData extends BaseUuidEntity {
    private static final long serialVersionUID = -3853743432884465744L;

    @MetaProperty
    private String name;

    @MetaProperty
    private Date date;

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

@Override
public List<ExcelData> load() {
    List<Object[]> list = persistence.callInTransaction(em ->
            em.createNativeQuery("select NAME, DATE_ from FEXPORT_CUSTOMER")
                    .getResultList());

    List<ExcelData> result = new ArrayList<>(list.size());
    for (Object[] row : list) {
        ExcelData excelRow = metadata.create(ExcelData.class);
        excelRow.setName((String) row[0]);
        excelRow.setDate((Date) row[1]);
        result.add(excelRow);
    }
    return result;
}

See reworked demo project: fexport.zip (84.6 KB)

1 Like
#7

image

I got this message, the ExcelData has the same structure as yours.
Do you have any idea?

#8

Check metadata.xml in the global module it should contain the following:

<metadata-model root-package="com.company.fexport" namespace="fexport">
    <class>com.company.fexport.entity.ExcelData</class>
</metadata-model>
#9

It works, thank you so much!