Directory

← Back

Excel Exporter

It is a single point solution providing consistent and configurable support for exporting data of Vaadin Table, Tree Table and Grid in Excel format.

Author

Contributors

Rating

Let's first discuss why do we need this add on?

  1. Exporting the screen data is a frequent task required in most of the applications. There are several helper technologies such as POI which help us in bringing the Export feature in place but a developer needs to take it up every time he wants to implement.
  2. Vaadin providing so many components for representation of data such as Table, Tree Table and Grid, which again raises a need for a utility that is easy and configurable to be used with varied component without the pain of writing the logic to generate the Excel.
  3. A consistent excel format throughout the application which would enhance user experience.
  4. Can be integrated with any Vaadin application with just few lines of configuration code.

Now Let's discuss what this add on does?

This add-on utility takes various components like the Tree Table, Grid and Table as inputs and directly generates an excel in the required formats such as .XLS or .XLSX.

It is highly configurable and provides configuration at three levels namely File Level, Sheet Level, Component Level.

For each file you can configure

  • List of Sheets
  • Export File Name
  • Generated By
  • Export Type
  • Export Extension

For each sheet you can configure

  • Sheet Name
  • Report Title Row
  • Generated By Row
  • List of Components inside the sheet
  • Field Group - for showing filters selected on the screen for the selected data
  • Additional Header Info - HashMap for custom data
  • Date Format
  • Number Formats
  • XSSFCellStyle for each content section of the screen

Note that the Number formats by itself manages the thousand seperator and decimal point handling for different locales. For Eg: For English the decimal point is (.) and thousand seperator is (,), but for German locale it is the reverse. But the code handles it by its own.

For each component you can configure

  • Tree table or Grid or Table
  • Visible properties
  • Properties requiring date formatting
  • Properties requiring Float formatting
  • Properties requiring Integer formatting
  • Column Header Texts
  • Component Header and Content Styles
  • Column Freeze and Header Freeze feature
  • Provide Column Level Configurations -- Datatype -- Suffix -- Prefix -- Custom Column Generator -- Thousand Separators in case of Number
  • Multiple Header Rows with cell merging option
  • Multiple Footer Rows with cell merging option

However, if none of these are specified, it would generate the Excel with default values and styles.

What dependencies are required?

JDK 1.7 or above POI 3.9 POI-OOXML 3.9 Apache Commons IO library (http://commons.apache.org/io/) v2.2 net.karneim.pojobuilder 3.4.0 and above

Who developed it?

Kartik Suba @ Direction Software Solutions, India. For Client: Bonprix Handelsgesellschaft mbH Contact on Skype @ k.suba@direction.biz or kartik.suba for quick assistance.

Are there any Future Up gradation Plans?

  • Specifying a row header which can allow horizontal data as well.
  • Export of selected records wherever applicable
  • Legend for better understanding

Sample code

package org.vaadin.addons.demo;

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

import javax.servlet.annotation.WebServlet;

import org.vaadin.addons.ExportExcelComponentConfiguration;
import org.vaadin.addons.ExportExcelConfiguration;
import org.vaadin.addons.ExportExcelSheetConfiguration;
import org.vaadin.addons.ExportToExcelUtility;
import org.vaadin.addons.ExportType;
import org.vaadin.addons.builder.ExportExcelComponentConfigurationBuilder;
import org.vaadin.addons.builder.ExportExcelConfigurationBuilder;
import org.vaadin.addons.builder.ExportExcelSheetConfigurationBuilder;

import com.vaadin.annotations.Theme;
import com.vaadin.annotations.Title;
import com.vaadin.annotations.VaadinServletConfiguration;
import com.vaadin.data.util.BeanItemContainer;
import com.vaadin.server.FontAwesome;
import com.vaadin.server.VaadinRequest;
import com.vaadin.server.VaadinServlet;
import com.vaadin.ui.MenuBar;
import com.vaadin.ui.MenuBar.Command;
import com.vaadin.ui.MenuBar.MenuItem;
import com.vaadin.ui.Table;
import com.vaadin.ui.UI;
import com.vaadin.ui.VerticalLayout;

/**
 *
 * @author Kartik Suba
 *
 */
@Theme("demo")
@Title("vaadin-excel-exporter Add-on Demo")
@SuppressWarnings("serial")
public class DemoShortUI extends UI {

    List<DataModel> orderList;
    private com.vaadin.ui.MenuBar exportToolBar = null;
    private Table tableWithBeanItemContainer;
    private BeanItemContainer<DataModel> beanItemContainer;

    @WebServlet(
            value = "/*",
            asyncSupported = true)
    @VaadinServletConfiguration(
            productionMode = false,
            ui = DemoUI.class,
            widgetset = "org.vaadin.addons.demo.DemoWidgetSet")
    public static class Servlet extends VaadinServlet {
    }

    @SuppressWarnings("unchecked")
    @Override
    protected void init(final VaadinRequest request) {

        createToolBar();
        final VerticalLayout layout = new VerticalLayout();
        layout.setSizeFull();
        layout.addComponent(this.exportToolBar);
        this.beanItemContainer = new BeanItemContainer<DataModel>(DataModel.class);
        layout.addComponent(getExportTableWithBeanItemContainer());
        layout.setExpandRatio(this.exportToolBar, 10f);
        layout.setExpandRatio(this.tableWithBeanItemContainer, 18f);
        getDataForTheComponents();
        this.beanItemContainer.removeAllItems();
        this.beanItemContainer.addAll(this.orderList.subList(0, 2));
        setContent(layout);
    }

    private void createToolBar() {

        this.exportToolBar = new MenuBar();
        this.exportToolBar.addItem("XLSX", FontAwesome.DOWNLOAD, new Command() {

            @Override
            public void menuSelected(final MenuItem selectedItem) {
                ExportToExcelUtility<DataModel> exportToExcelUtility = customizeExportExcelUtility();
                exportToExcelUtility.setSourceUI(UI.getCurrent());
                exportToExcelUtility.setResultantExportType(ExportType.XLSX);
                exportToExcelUtility.export();
            }
        });

        this.exportToolBar.addItem("XLS", FontAwesome.DOWNLOAD, new Command() {

            @Override
            public void menuSelected(final MenuItem selectedItem) {
                ExportToExcelUtility<DataModel> exportToExcelUtility = customizeExportExcelUtility();
                exportToExcelUtility.setSourceUI(UI.getCurrent());
                exportToExcelUtility.setResultantExportType(ExportType.XLS);
                exportToExcelUtility.export();
            }
        });
    }

    private ExportToExcelUtility<DataModel> customizeExportExcelUtility() {
        /* Configuring Components */
        ExportExcelComponentConfiguration componentConfig1 = new ExportExcelComponentConfigurationBuilder().withTable(this.tableWithBeanItemContainer)
                                                                                                           .withVisibleProperties(this.tableWithBeanItemContainer.getVisibleColumns())
                                                                                                           .withColumnHeaderKeys(this.tableWithBeanItemContainer.getColumnHeaders())
                                                                                                           .build();

        /* Configuring Sheets */
        ArrayList<ExportExcelComponentConfiguration> componentList1 = new ArrayList<ExportExcelComponentConfiguration>();
        componentList1.add(componentConfig1);

        ExportExcelSheetConfiguration sheetConfig1 = new ExportExcelSheetConfigurationBuilder().withReportTitle("Excel Report")
                                                                                               .withSheetName("Excel Report")
                                                                                               .withComponentConfigs(componentList1)
                                                                                               .withIsHeaderSectionRequired(Boolean.TRUE)
                                                                                               .build();

        /* Configuring Excel */
        ArrayList<ExportExcelSheetConfiguration> sheetList = new ArrayList<ExportExcelSheetConfiguration>();
        sheetList.add(sheetConfig1);
        ExportExcelConfiguration config1 = new ExportExcelConfigurationBuilder().withGeneratedBy("Kartik Suba")
                                                                                .withSheetConfigs(sheetList)
                                                                                .build();

        return new ExportToExcelUtility<DataModel>(this.tableWithBeanItemContainer.getUI(), config1, DataModel.class);
    }

    public Table getExportTableWithBeanItemContainer() {

        this.tableWithBeanItemContainer = new Table("TableWithBeanItemContainer", this.beanItemContainer);
        this.tableWithBeanItemContainer.setVisibleColumns(new Object[] { "country", "productType", "catalogue", "plannedPrinter", "cheapest", "contractor",
                "totalCosts", "differenceToMin", "comment" });
        this.tableWithBeanItemContainer.setColumnHeaders(new String[] { "COUNTRY", "PRODUCT_TYPE", "CATALOGUE", "PLANNED_PRINTER", "CHEAPEST", "CONTRACTOR",
                "TOTAL_COST", "DIFFERENCE_TO_MIN", "COMMENT" });
        this.tableWithBeanItemContainer.setWidth(100, Unit.PERCENTAGE);
        this.tableWithBeanItemContainer.setHeight(100, Unit.PERCENTAGE);

        return this.tableWithBeanItemContainer;
    }

    public List<DataModel> getDataForTheComponents() {

        this.orderList = new ArrayList<DataModel>();

        DataModel orderPlacementReport1 = new DataModel();
        orderPlacementReport1.setCatalogue("ABC");
        orderPlacementReport1.setCountry("India");
        orderPlacementReport1.setProductType("Cars");
        orderPlacementReport1.setPlannedPrinter("Audi");
        orderPlacementReport1.setCheapest("112365$");
        orderPlacementReport1.setContractor("contractor1");
        orderPlacementReport1.setTotalCosts("123645789$");
        orderPlacementReport1.setDifferenceToMin("1235369$");
        orderPlacementReport1.setComment("Luxury Car");

        DataModel orderPlacementReport2 = new DataModel();
        orderPlacementReport2.setCatalogue("XYZ");
        orderPlacementReport2.setCountry("German");
        orderPlacementReport2.setProductType("Cars");
        orderPlacementReport2.setPlannedPrinter("VW");
        orderPlacementReport2.setCheapest("1125895$");
        orderPlacementReport2.setContractor("contractor2");
        orderPlacementReport2.setTotalCosts("14589725$");
        orderPlacementReport2.setDifferenceToMin("896452$");
        orderPlacementReport2.setComment("Premium Car");

        this.orderList.add(orderPlacementReport1);
        this.orderList.add(orderPlacementReport2);
        return this.orderList;
    }
}
/**
     * Configuring ExportToExcel Utility This configuration allows the end user-developer to \ Add multiple sheets and configure them separately \ Configure
     * components to be added in each sheet and their properties
     *
     * @return ExportToExcelUtility
     */
    private ExportToExcelUtility<DataModel> customizeExportExcelUtility() {

        HashMap<Object, ColumnFormatter> columnFormatters = new HashMap<>();

        // Suffix Formatter provided
        columnFormatters.put("totalCosts", new SuffixColumnFormatter("$"));
        columnFormatters.put("differenceToMin", new SuffixColumnFormatter("$"));
        columnFormatters.put("cheapest", new SuffixColumnFormatter("-quite cheap"));

        // Boolean Formatter provided
        columnFormatters.put("active", new BooleanColumnFormatter("Yes", "No"));

        // Custom Formatting also possible
        columnFormatters.put("catalogue", new ColumnFormatter() {

            @Override
            public Object generateCell(final Object value, final Object itemId, final Object columnId) {
                return (value != null) ? ((String) value).toLowerCase() : null;
            }
        });

        /* Configuring Components with Headers and Footers */
        ExportExcelComponentConfiguration componentConfig1 = new ExportExcelComponentConfigurationBuilder().withTable(this.tableWithBeanItemContainer)
                .withVisibleProperties(this.tableWithBeanItemContainer.getVisibleColumns())
                .withHeaderConfigs(Arrays.asList(new ComponentHeaderConfigurationBuilder().withColumnHeaderKeys(this.tableWithBeanItemContainer.getColumnHeaders())
                                                         .withMergedCells(Arrays.asList(new MergedCellBuilder().withStartProperty("cheapest")
                                                                 .withEndProperty("totalCosts")
                                                                 .withHeaderKey("Costs")
                                                                 .build(), new MergedCellBuilder().withStartProperty("productType")
                                                                 .withEndProperty("plannedPrinter")
                                                                 .withHeaderKey("Types")
                                                                 .build(), new MergedCellBuilder().withStartProperty("differenceToMin")
                                                                 .withEndProperty("counter")
                                                                 .withHeaderKey("Remaining")
                                                                 .build()))
                                                         .build(),
                                                 new ComponentHeaderConfigurationBuilder().withColumnHeaderKeys(this.tableWithBeanItemContainer.getColumnHeaders())
                                                         .build()))
                .withIntegerFormattingProperties(Arrays.asList("counter"))
                .withFloatFormattingProperties(Arrays.asList("totalCosts", "differenceToMin"))
                .withBooleanFormattingProperties(Arrays.asList("active"))
                .withColumnFormatters(columnFormatters)
                .build();

        ExportExcelComponentConfiguration componentConfig2 = new ExportExcelComponentConfigurationBuilder().withTable(this.tableWithIndexedContainer)
                .withVisibleProperties(this.tableWithIndexedContainer.getVisibleColumns())
                .withHeaderConfigs(Arrays.asList(new ComponentHeaderConfigurationBuilder().withColumnHeaderKeys(this.tableWithIndexedContainer.getColumnHeaders())
                                           .build()))
                .withIntegerFormattingProperties(Arrays.asList("counter"))
                .withFloatFormattingProperties(Arrays.asList("totalCosts", "differenceToMin"))
                .withBooleanFormattingProperties(Arrays.asList("active"))
                .withColumnFormatters(columnFormatters)
                .build();

        ExportExcelComponentConfiguration componentConfig3 = new ExportExcelComponentConfigurationBuilder().withTreeTable(this.treeTable)
                .withVisibleProperties(this.treeTable.getVisibleColumns())
                .withHeaderConfigs(Arrays.asList(new ComponentHeaderConfigurationBuilder().withColumnHeaderKeys(this.treeTable.getColumnHeaders())
                        .build()))
                .withColRowFreeze(3)
                .withIntegerFormattingProperties(Arrays.asList("counter"))
                .withFloatFormattingProperties(Arrays.asList("totalCosts", "differenceToMin"))
                .withBooleanFormattingProperties(Arrays.asList("active"))
                .withColumnFormatters(columnFormatters)
                .build();

        ExportExcelComponentConfiguration componentConfig4 = new ExportExcelComponentConfigurationBuilder().withGrid(this.gridWithIndexedContainer)
                .withVisibleProperties(this.visibleColumns)
                .withHeaderConfigs(Arrays.asList(new ComponentHeaderConfigurationBuilder().withColumnHeaderKeys(this.columnHeaders)
                        .build()))
                .withIntegerFormattingProperties(Arrays.asList("counter"))
                .withFloatFormattingProperties(Arrays.asList("totalCosts", "differenceToMin"))
                .withBooleanFormattingProperties(Arrays.asList("active"))
                .withColumnFormatters(columnFormatters)
                .build();

        ExportExcelComponentConfiguration componentConfig5 = new ExportExcelComponentConfigurationBuilder().withGrid(this.gridWithBeanItemContainer)
                .withVisibleProperties(this.visibleColumns)
                .withHeaderConfigs(Arrays.asList(new ComponentHeaderConfigurationBuilder().withHeaderRow(this.gridWithBeanItemContainer.getDefaultHeaderRow())
                        .withMergedCells(Arrays.asList(new MergedCellBuilder().withStartProperty("cheapest")
                                .withEndProperty("totalCosts")
                                .withHeaderKey("Costs")
                                .build()))
                        .build(), new ComponentHeaderConfigurationBuilder().withColumnHeaderKeys(this.columnHeaders)
                        .build()))
                .withIntegerFormattingProperties(Arrays.asList("counter"))
                .withFloatFormattingProperties(Arrays.asList("totalCosts", "differenceToMin"))
                .withBooleanFormattingProperties(Arrays.asList("active"))
                .withColumnFormatters(columnFormatters)
                .build();

        /* Configuring Sheets */
        ExportExcelSheetConfiguration sheetConfig1 = new ExportExcelSheetConfigurationBuilder().withIsDefaultGeneratedByRequired(Boolean.FALSE)
                .withComponentConfigs(Arrays.asList(componentConfig1, componentConfig2))
                .withIsDefaultSheetTitleRequired(Boolean.FALSE)
                .withIsHeaderSectionRequired(Boolean.FALSE)
                .withDateFormat("dd-MMM-yyyy")
                .build();

        ExportExcelSheetConfiguration sheetConfig2 = new ExportExcelSheetConfigurationBuilder().withReportTitle("Exported Tree Table")
                .withSheetName("Excel Tree Table")
                .withComponentConfigs(Arrays.asList(componentConfig3))
                .withIsHeaderSectionRequired(Boolean.TRUE)
                .build();

        ExportExcelSheetConfiguration sheetConfig3 = new ExportExcelSheetConfigurationBuilder().withReportTitle("Exported Grid")
                .withSheetName("Excel Grid")
                .withComponentConfigs(Arrays.asList(componentConfig4, componentConfig5))
                .withIsHeaderSectionRequired(Boolean.TRUE)
                .build();

        ExportExcelConfiguration config1 = new ExportExcelConfigurationBuilder().withGeneratedBy("Kartik Suba")
                .withSheetConfigs(Arrays.asList(sheetConfig1, sheetConfig2, sheetConfig3))
                .build();

        return new ExportToExcelUtility<DataModel>(this.tableWithBeanItemContainer.getUI(), config1, DataModel.class);
}

Compatibility

(Loading compatibility data...)

Was this helpful? Need more help?
Leave a comment or a question below. You can also join the chat on Discord or ask questions on StackOverflow.

Version

  • Bug Fix: Datatype formatting was working only on odd rows. Now it has been fixed. No code change required.
Released
2018-01-16
Maturity
EXPERIMENTAL
License
MIT License

Compatibility

Framework
Vaadin 7.0+
Vaadin 8.0+ in 2.0
Browser
N/A
Online