Spring MVC - Excel View

This example demonstrates how to use Excel document as the view technology in a Spring MVC application. Spring supports Apache POI library which is used for reading and writing Microsoft Office document formats.

In this example, we are going to use AbstractXlsView which is a convenient subclass of Spring View. This class supports Excel format.

Example

Additional Maven Dependency

pom.xml

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.16</version>
</dependency>

Java Config class

In this example, we are going to use BeanNameViewResolver for resolving Excel view. That means we need to register an implementation of AbstractXlsView as a bean. The bean name will be used as the view name returned from the controller. Check out this simple example of BeanNameViewResolver as well.

@EnableWebMvc
@Configuration
@ComponentScan
public class MyWebConfig {
    @Bean
    public ViewResolver beanNameViewResolver() {
        BeanNameViewResolver resolver = new BeanNameViewResolver();
        return resolver;
    }

    @Bean
    public ViewResolver jspViewResolver() {
        InternalResourceViewResolver viewResolver = new InternalResourceViewResolver();
        viewResolver.setPrefix("/WEB-INF/views/");
        viewResolver.setSuffix(".jsp");
        return viewResolver;
    }
}

Implementing AbstractXlsView

package com.logicbig.example;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.view.document.AbstractXlsView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.time.format.DateTimeFormatter;
import java.time.format.FormatStyle;
import java.util.List;
import java.util.Map;

@Component("forexView")
public class ForexExcelView extends AbstractXlsView {

    @Override
    protected void buildExcelDocument(Map<String, Object> model, Workbook workbook,
                                      HttpServletRequest request, HttpServletResponse response)
            throws Exception {
        List<CurrencyRate> currencyRates = (List<CurrencyRate>) model.get("todayCurrencyRates");
        Sheet sheet = workbook.createSheet("Today Currency Rates");
        sheet.setFitToPage(true);

        int rowCount = 0;
        Row header = sheet.createRow(rowCount++);
        header.createCell(0).setCellValue("Currency Pair");
        header.createCell(1).setCellValue("Bid Price");
        header.createCell(2).setCellValue("Ask Price");
        header.createCell(3).setCellValue("Date");

        for (CurrencyRate currencyRate : currencyRates) {
            Row currencyRow = sheet.createRow(rowCount++);
            currencyRow.createCell(0).setCellValue(currencyRate.getCurrencyPair());
            currencyRow.createCell(1).setCellValue(currencyRate.getBidPrice().doubleValue());
            currencyRow.createCell(2).setCellValue(currencyRate.getAskPrice().doubleValue());
            currencyRow.createCell(3).setCellValue(
                    currencyRate.getDateTime().format(
                            DateTimeFormatter.ofLocalizedDate(FormatStyle.SHORT)));
        }
        response.setHeader("Content-Disposition", "attachment; filename=forex-rates.xls");
    }
}

public class CurrencyRate {
    private String currencyPair;
    private LocalDateTime dateTime;
    private BigDecimal askPrice;
    private BigDecimal bidPrice;
    .............
}

Writing a Spring Controller

@Controller
@RequestMapping("/")
public class MyController {

    @GetMapping("/")
    public String mainView() {
        return "main";
    }

    @GetMapping("/exchangeRates")
    public String handleForexRequest(Model model) {
        model.addAttribute("todayCurrencyRates", getTodayForexRates());
        return "forexView";
    }

    private List<CurrencyRate> getTodayForexRates() {
        //dummy rates
        List<CurrencyRate> currencyRates = new ArrayList<>();
        LocalDateTime today = LocalDateTime.now();
        List<Currency> currencies = new ArrayList<>(Currency.getAvailableCurrencies());

        for (int i = 0; i < currencies.size(); i += 2) {
            String currencyPair = currencies.get(i) + "/" + currencies.get(i + 1);
            CurrencyRate cr = new CurrencyRate();
            cr.setCurrencyPair(currencyPair);
            cr.setDateTime(today);
            BigDecimal bidPrice = new BigDecimal(Math.random() * 5 + 1);
            bidPrice = bidPrice.setScale(3, RoundingMode.CEILING);
            cr.setBidPrice(bidPrice);
            BigDecimal askPrice = new BigDecimal(bidPrice.doubleValue() + Math.random() * 2 + 0.5);
            askPrice = askPrice.setScale(3, RoundingMode.CEILING);
            cr.setAskPrice(askPrice);

            currencyRates.add(cr);
        }
        return currencyRates;
    }
}

src/main/webapp/WEB-INF/views/main.jsp

<html>
<body>
<h2>Spring Excel View Example</h2>
<a href="/exchangeRates">Get Today Exchange Rates</a>

</body>
</html>

Output

clicking on the link:

Example Project

Dependencies and Technologies Used:

• spring-webmvc 4.3.10.RELEASE: Spring Web MVC.
• javax.servlet-api 3.1.0 Java Servlet API
• poi 3.16: Apache POI - Java API To Access Microsoft Format Files.
• JDK 1.8
• Maven 3.3.9