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.

excel_report_template.jpg

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.

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.

** ★ 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.

Recommended Posts

How to create an Excel form using a template file with Spring MVC
Create an excel file with poi
How to create a jar file or war file using the jar command
[Rails 6] How to create a dynamic form input screen using cocoon
I tried connecting to MySQL using JDBC Template with Spring MVC
[rails] How to create a partial template
How to realize huge file upload with Rest Template of Spring
I tried printing a form with Spring MVC and JasperReports 2/3 (form template creation)
How to create a server executable JAR and WAR with Spring gradle
[Rails] How to create a graph using lazy_high_charts
[Spring sample code included] How to create a form and how to get multiple records
I tried to implement file upload with Spring MVC
How to create a Spring Boot project in IntelliJ
[Spring Boot] How to create a project (for beginners)
[Introduction to Spring Boot] Submit a form using thymeleaf
How to create an application
How to create a method
How to request a CSV file as JSON with jMeter
How to create a form to select a date from the calendar
How to make an app using Tensorflow with Android Studio
To receive an empty request with Spring Web MVC @RequestBody
How to register as a customer with Square using Tomcat
A new employee tried to create an authentication / authorization function from scratch with Spring Security
Create an app with Spring Boot 2
[Java] How to create a folder
Create an app with Spring Boot
I tried printing a form with Spring MVC and JasperReports 1/3 (JasperReports settings)
How to make a hinadan for a Spring Boot project using SPRING INITIALIZR
How to make a jar file with no dependencies in Maven
I tried to create a Spring MVC development environment on Mac
How to create your own Controller corresponding to / error with Spring Boot
How to load a Spring upload file and view its contents
How to open a script file from Ubuntu with VS code
How to create a web server on an EC2 instance on AWS
[Xcode] How to add a README.md file
How to execute a contract using web3j
How to sort a List using Comparator
Create a jar file with the command
How to quickly create a reverse proxy that supports HTTPS with Docker
Create a simple on-demand batch with Spring Batch
I made an app to scribble with PencilKit on a PDF file
[Java] How to turn a two-dimensional array with an extended for statement
How to create a Maven repository for 2020
How to achieve file download with Feign
[Swift5] How to create a splash screen
Steps to create a simple camel app using Apache Camel Spring Boot starters
[Spring MVC] How to pass path variables
How to split Spring Boot message file
How to achieve file upload with Feign
How to make an app with a plugin mechanism [C # and Java]
How to start a Docker container with a volume mounted in a batch file
Let's create a TODO application in Java 2 I want to create a template with Spring Initializr and make a Hello world
I want to be able to read a file using refile with administrate [rails6]
[Swift] How to generate an ID to uniquely identify a certain thing (using UUID)
How to deploy a system created with Java (Wicket-Spring boot) to an on-campus server
A memo to simply create a form using only HTML and CSS in Rails 6
I tried printing a form with Spring MVC and JasperReports Extra edition (Variables edition)
How to store data simultaneously in a model associated with a nested form (Rails 6.0.0)
How to perform UT with Excel as test data with Spring Boot + JUnit5 + DBUnit
I tried printing a form with Spring MVC and JasperReports Extra edition (image edition)
From creating a Spring Boot project to running an application with VS Code