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