How to create an Excel form using a template file with Spring MVC

1.First of all

This time, I will explain how to create an Excel form using a template file with Spring MVC = TERASOLUNA 5.x. Excel in the TERASOLUNA 5.x guideline "4.10.2.2. Download Excel File" There is an explanation of the form, but this is a method to create an Excel file from scratch programmatically. When creating an Excel form, I think it is common to prepare a template file in advance and pour data into it. This time, I would like to output the Excel form by pouring the data into the following template file.

The reference system is "terasolunaorg / terasoluna-tourreservation-mybatis3".

2. Source code

ExcelReportController.java

package com.example.reportdemo.app.report;

import java.io.File;
import java.io.IOException;
import javax.inject.Inject;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.example.reportdemo.domain.model.Reserve;
import com.fasterxml.jackson.databind.ObjectMapper;

@Controller
public class ExcelReportController {

    /**
     * [dummy]ObjectMapper for reading form data
     */
    @Inject
    ObjectMapper objectMapper;

    /**
     * [dummy]Json file that stores form data
     */
    @Value("${app.sample.jsonFile:C:/temp/excel/jsonData.json}")
    private File jsonDataFile;

    @RequestMapping(value = "report/excel", method = RequestMethod.GET)
    public String excelReport(Model model) {
        
        // 1. [dummy] get report data
        Reserve reserve = this.findReserve();
        // 2. set report data to model
        model.addAttribute(reserve);
        model.addAttribute("fileName", "Reservation details.xlsx");
        // 3. return excel view bean's name
        return "reservationExcelView";
    }

    /**
     * [dummy]Create dummy reservation information for output to the form
     * @return reservation
     */
    private Reserve findReserve() {
        Reserve reserve = null;
        try {
            reserve = objectMapper.readValue(jsonDataFile, Reserve.class);
            System.out.println(objectMapper.writeValueAsString(reserve));
        } catch (IOException e) {
            e.printStackTrace();
        }
        return reserve;
    }
}

Normally, to output the form, the target data is acquired from the database, but in this sample, I decided to acquire the data from the json file without omission. Others are the same as the normal Controller implementation. The following two points are the points.

• The data you want to link is stored in Model and linked in the same way as the screen display of JSP.
• The return value of the handler method is to return the Bean name of the View to be implemented later.

ReservationExcelView.java

package com.example.reportdemo.app.report;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.view.document.AbstractXlsxView;

import com.example.reportdemo.domain.model.Reserve;
import com.example.reportdemo.domain.model.TourInfo;

/**
 *★ Point 1
 */
@Component
public class ReservationExcelView extends AbstractXlsxView {

    private static final Logger LOGGER = LoggerFactory
            .getLogger(ReservationExcelView.class);

    /**
     *★ Point 2
     *Excel template for tour reservation registration
     */
    @Value("${app.report.resevation.template:C:/temp/excel/reservation.xlsx}")
    private File excelTemplateFile;

    /**
     *★ Point 2
     *Excel file read password
     */
    @Value("${app.report.resevation.pass:locked}")
    private String excelOpenPass;

    /**
     *★ Point 3
     * <p>
     *Create a Workbook using the Excel template specified in excelTemplateFile
     * <p>
     *The Workbook object returned by this method is passed as an argument of the buildExcelDocument method.
     * @see org.springframework.web.servlet.view.document.AbstractXlsxView#createWorkbook(java.util.Map,
     *      javax.servlet.http.HttpServletRequest)
     */
    @Override
    protected Workbook createWorkbook(Map<String, Object> model,
            HttpServletRequest request) {
        Workbook workbook = null;
        try (InputStream is = new ByteArrayInputStream(
                Files.readAllBytes(excelTemplateFile.toPath()));) {
            workbook = WorkbookFactory.create(is);
            // workbook = WorkbookFactory.create(is, excelOpenPass);
            //If the file has a password lock, jce_policy-8.Need to apply zip
        } catch (IOException | EncryptedDocumentException
                | InvalidFormatException e) {
            LOGGER.error("create workbook error", e);
        }
        return workbook;
    }

    /**
     *★ Point 4
     * @see org.springframework.web.servlet.view.document.AbstractXlsView#buildExcelDocument(java.util.Map,
     *      org.apache.poi.ss.usermodel.Workbook, javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
     */
    @Override
    protected void buildExcelDocument(Map<String, Object> model,
            Workbook workbook, HttpServletRequest request,
            HttpServletResponse response) throws Exception {
        //★ Point 5
        // 1.Get the form data stored in Model
        Reserve reserve = (Reserve) model.get("reserve");
        TourInfo tourInfo = reserve.getTourInfo();

        //★ Point 6
        // 2.Sheet selection
        Sheet sheet = workbook.getSheet("Reservation");

        //★ Point 7
        // 3.Set data in cell
        //Set the value of "Reservation number" in row 5 and column F
        getCell(sheet, 4, 5).setCellValue(reserve.getReserveNo());

        //★ Point 8
        // 4.Set the file name in the response header
        String fileName = (String) model.get("fileName");
        String encodedFilename = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-Disposition","attachment; filename*=UTF-8''" + encodedFilename);

        //★ Point 7
        // 3.Set data in cell
        //Set the value of "Reservation date" in 5 rows and AA columns
        getCell(sheet, 4, 26).setCellValue(reserve.getReservedDay());
        //Tour name
        getCell(sheet, 5, 5).setCellValue(tourInfo.getTourName());
        //departure date
        getCell(sheet, 6, 5).setCellValue(tourInfo.getDepDay());
        //Days
        getCell(sheet, 6, 26).setCellValue(tourInfo.getTourDays());
        //Point of departure
        getCell(sheet, 7, 5).setCellValue(tourInfo.getDeparture().getDepName());
        //Destination
        getCell(sheet, 7, 26).setCellValue(tourInfo.getArrival().getArrName());
        //Tour conductor
        getCell(sheet, 8, 5).setCellValue(tourInfo.getConductor());
        //hotel
        getCell(sheet, 9, 5)
                .setCellValue(tourInfo.getAccommodation().getAccomName());
        //contact information
        getCell(sheet, 9, 26)
                .setCellValue(tourInfo.getAccommodation().getAccomTel());
        //Overview
        getCell(sheet, 10, 5).setCellValue(tourInfo.getTourAbs());
        // omitted
    }

    /**
     * <p>
     *Gets and returns the cell specified by the row number and column number of the sheet specified by the argument.
     * <p>
     *Row and column numbers start from 0
     * <p>
     *NullPointerException if the cell is not manipulated in the Excel template
     * @param sheet sheet
     * @param rowIndex row number
     * @param colIndex Column number
     * @return cell
     */
    private Cell getCell(Sheet sheet, int rowIndex, int colIndex) {
        Row row = sheet.getRow(rowIndex);
        return row.getCell(colIndex);
    }

}

** ★ Point 1 ** ʻExtends org.springframework.web.servlet.view.document.AbstractXlsxViewto define your ownViewclass. Since the implementedView requires Bean definition, add @Component` class annotation.

** ★ Point 2 ** Define the Excel file to be used as a template as a resource. Although it is defined as File, it can be read by @ Value in Spring. If you have set a password in the Excel file, define it in the same way. (option)

** ★ Point 3 ** This is the point of this article. Override the createWorkbook method to create an object for Workbook from an Excel template. This is simply a POI operation. The Workbook object that is the return value of this method is passed as an argument to the buildExcelDocument method.

**(reference) You can also use an Excel file with a password. In that case, WorkbookFactory.create (is," password ") will create a Workbook object. To unlock the password jce_policy-8.zip due to the problem of the encryption library (those with high encryption strength are subject to export restrictions) /downloads/jce8-download-2133166.html) needs to be applied. ** **

** ★ Point 4 ** Override the buildExcelDocument method and implement the process to create an Excel form.

• First argument Map <String, Object> model: Model data set in Controller
• Second argument Workbook workbook: Workbook object created by the createWorkbook method
• Third argument HttpServletRequest request: HTTP request
• 4th argument HttpServletResponse response): HTTP response. Operate when setting the response header

** ★ Point 5 ** Get the data to be poured into the form from Model. Data linkage with Controller is performed via Model.

** ★ Point 6 ** It is a POI operation. First, select the target sheet.

** ★ Point 7 ** You need to select a cell to set the data. In the sample, we prepared the getCell method. Set the data in the target cell with the setCellValue method of POI.

**(Caution) Please note that the rows and columns that are not operated in the template Excel file according to the POI specifications will be null. Thinking positively, if you get a NullPointerException, you've found a bug where you've selected another cell instead of the one you should set. ** **

** ★ Point 8 ** Set the file name of the download file in the HTTP response header. When setting the file name in Japanese, encode it with ʻutf-8. If you do not set a file name, the name will be the request path with the extension .xlsx. In the sample, the path is report / excel, so it will be ʻexcel.xlsx.

xml:Bean definition file (TERASOLUNA5.For x: spring-mvc.xml）

    <!-- Settings View Resolver. -->
    <mvc:view-resolvers>
        <!--★ Point 9-->
        <mvc:bean-name />
        <mvc:jsp prefix="/WEB-INF/views/" />
    </mvc:view-resolvers>

** ★ Point 9 ** Add <mvc: bean-name /> to the definition of <mvc: view-resolvers> in the bean definition file. The order of writing is also important. Be sure to define it before <mvc: jsp>.

jsonData.json

{
  "reserveNo" : "12345678",
  "reservedDay" : "2018-01-10T07:02:15.919+0000",
  "adultCount" : 2,
  "childCount" : 1,
  "transfer" : 0,
  "sumPrice" : 75000,
  "remarks" : "Memo",
  "tourInfo" : {
    "tourCode" : "00000001",
    "plannedDay" : "2017-01-10T07:02:15.919+0000",
    "planNo" : "0101",
    "tourName" : "Terra Solna Paradise Tour",
    "tourDays" : 0,
    "depDay" : "2018-01-30",
    "avaRecMax" : 0,
    "basePrice" : 30000,
    "conductor" : 1,
    "tourAbs" : "There is another place, an inn where silence and steam are waiting ...\r\n Please contact us for more information.",
    "departure" : {
      "depCode" : "01",
      "depName" : "Hokkaido"
    },
    "arrival" : {
      "arrCode" : "02",
      "arrName" : "Aomori"
    },
    "accommodation" : {
      "accomCode" : "0001",
      "accomName" : "TERASOLUNA Hotel Daiichiso",
      "accomTel" : "018-123-4567"
    },
    "paymentLimit" : "2018-01-23"
  },
  "customer" : {
    "customerCode" : "00001234",
    "customerName" : "Yamada Taro",
    "customerKana" : "Yamada Taro",
    "customerPass" : null,
    "customerBirth" : "2000-07-07",
    "customerJob" : "Sales",
    "customerMail" : "[email protected]",
    "customerTel" : "01-2345-6789",
    "customerPost" : "123-4567",
    "customerAdd" : "Toyosu, Koto-ku, Tokyo"
  }
}

It is the data to be poured into the Excel form. It's just for the sample, but it's easy to change the data if you read the data from JSON.

3. Finally

This time, I explained how to create an Excel form using a template file with Spring MVC = TERASOLUNA 5.x. The point is to override the createWorkbook method to create aWorkbook object from an Excel template file.