Create and Save an Excel file with Spring and Maven

GemBox.Spreadsheet for Java is a platform and framework independent library that works with all major web frameworks.

The following example is a simple Spring MVC application that exports data to a specified output file format.

Apache Maven is used as a project management tool.

An Spring application that exports data with GemBox.Spreadsheet for Java
Screenshot of an Spring application that exports data with GemBox.Spreadsheet for Java
import com.gembox.spreadsheet.*;
import org.springframework.http.*;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.*;

@Controller
@RequestMapping("workbook")
public class WorkbookController {

    static {
        SpreadsheetInfo.setLicense("FREE-LIMITED-KEY");
    }

    private static final List<WorkbookItem> DATA = Arrays.asList(
            new WorkbookItem(100, "John", "Doe"),
            new WorkbookItem(101, "Fred", "Nurk"),
            new WorkbookItem(102, "Hans", "Meier"),
            new WorkbookItem(103, "Ivan", "Horvat"),
            new WorkbookItem(104, "Jean", "Dupont"),
            new WorkbookItem(105, "Mario", "Rossi")
    );

    @RequestMapping(value = "/create", method = RequestMethod.GET)
    public String create(Model model) {
        model.addAttribute("workbookItemsWithFormat", new WorkbookItemsWithFormat("XLSX", DATA));
        return "create";
    }

    @RequestMapping(value = "/create", method = RequestMethod.POST)
    public HttpEntity<byte[]> create(@ModelAttribute("workbookItemsWithFormat") WorkbookItemsWithFormat workbookItemsWithFormat) throws IOException {
        SaveOptions options = getSaveOptions(workbookItemsWithFormat.getSelectedFormat());
        ExcelFile book = new ExcelFile();
        ExcelWorksheet sheet = book.addWorksheet("Sheet1");

        CellStyle style = sheet.getRow(0).getStyle();
        style.getFont().setWeight(ExcelFont.BOLD_WEIGHT);
        style.setHorizontalAlignment(HorizontalAlignmentStyle.CENTER);
        sheet.getColumn(0).getStyle().setHorizontalAlignment(HorizontalAlignmentStyle.CENTER);

        sheet.getColumn(0).setWidth(50, LengthUnit.PIXEL);
        sheet.getColumn(1).setWidth(150, LengthUnit.PIXEL);
        sheet.getColumn(2).setWidth(150, LengthUnit.PIXEL);

        sheet.getCell("A1").setValue("ID");
        sheet.getCell("B1").setValue("First Name");
        sheet.getCell("C1").setValue("Last Name");

        for (int row = 1; row <= workbookItemsWithFormat.getItems().size(); row++) {
            WorkbookItem item = workbookItemsWithFormat.getItems().get(row - 1);
            sheet.getCell(row, 0).setValue(item.getId());
            sheet.getCell(row, 1).setValue(item.getFirstName());
            sheet.getCell(row, 2).setValue(item.getLastName());
        }

        byte[] bytes = getBytes(book, options);

        HttpHeaders header = new HttpHeaders();
        header.set(HttpHeaders.CONTENT_TYPE, options.getContentType());
        header.set(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Create." + workbookItemsWithFormat.getSelectedFormat().toLowerCase());
        header.setContentLength(bytes.length);

        return new HttpEntity<>(bytes, header);
    }

    private byte[] getBytes(ExcelFile book, SaveOptions options) throws IOException {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        book.save(outputStream, options);
        return outputStream.toByteArray();
    }

    private static SaveOptions getSaveOptions(String format) {
        switch (format.toUpperCase()) {
            case "XLSX":
                return SaveOptions.getXlsxDefault();
            case "XLS":
                return SaveOptions.getXlsDefault();
            case "ODS":
                return SaveOptions.getOdsDefault();
            case "CSV":
                return SaveOptions.getCsvDefault();
            case "HTML":
                return SaveOptions.getHtmlDefault();
            default:
                throw new IllegalArgumentException("Format '" + format + "' is not supported.");
        }
    }


    public static class WorkbookItem {

        private int id;
        private String firstName;
        private String lastName;

        public WorkbookItem(int id, String firstName, String lastName) {
            this.id = id;
            this.firstName = firstName;
            this.lastName = lastName;
        }

        public WorkbookItem() {
        }

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public String getFirstName() {
            return firstName;
        }

        public void setFirstName(String firstName) {
            this.firstName = firstName;
        }

        public String getLastName() {
            return lastName;
        }

        public void setLastName(String lastName) {
            this.lastName = lastName;
        }
    }

    public static class WorkbookItemsWithFormat {

        public String selectedFormat;
        public List<WorkbookItem> items = new ArrayList<>();

        public WorkbookItemsWithFormat(String selectedFormat, List<WorkbookItem> items) {
            this.selectedFormat = selectedFormat;
            this.items = items;
        }

        public WorkbookItemsWithFormat() {
        }

        public String getSelectedFormat() {
            return selectedFormat;
        }

        public void setSelectedFormat(String selectedFormat) {
            this.selectedFormat = selectedFormat;
        }

        public List<WorkbookItem> getItems() {
            return items;
        }

        public void setItems(List<WorkbookItem> items) {
            this.items = items;
        }
    }
}
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
		 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
		 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.gembox.examples</groupId>
	<artifactId>spring</artifactId>
	<version>1.0</version>
	<packaging>war</packaging>

	<repositories>
		<repository>
			<id>com.gembox</id>
			<name>GemBox API</name>
			<url>https://gemboxsoftware.com/repo/</url>
		</repository>
	</repositories>

	<dependencies>
		<dependency>
			<groupId>com.gembox</groupId>
			<artifactId>gembox-spreadsheet</artifactId>
			<version>1.1.1062</version>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>javax.servlet-api</artifactId>
			<version>4.0.1</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet.jsp</groupId>
			<artifactId>javax.servlet.jsp-api</artifactId>
			<version>2.3.3</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>
		<dependency>
			<groupId>org.apache.taglibs</groupId>
			<artifactId>taglibs-standard-impl</artifactId>
			<version>1.2.5</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>5.1.2.RELEASE</version>
		</dependency>
	</dependencies>

	<build>
		<defaultGoal>tomcat7:run</defaultGoal>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-war-plugin</artifactId>
				<version>3.2.2</version>
				<configuration>
					<failOnMissingWebXml>false</failOnMissingWebXml>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.apache.tomcat.maven</groupId>
				<artifactId>tomcat7-maven-plugin</artifactId>
				<version>2.2</version>
				<configuration>
					<path>/spring</path>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>
<%@ page contentType="text/html;charset=UTF-8" pageEncoding="utf-8" trimDirectiveWhitespaces="true" session="false" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <meta charset="utf-8" />
    <title>Create an Excel Workbook</title>
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    <style type="text/css">
        thead tr {
            text-align: center;
        }

        .table th {
            color: white;
            background-color: rgb(242, 101, 34);
            padding: 0.5rem;
        }

        .table td {
            padding: 0.5rem;
        }

        .table input {
            font-size: 0.9rem;
            padding: 0.3rem 0.35rem;
        }

        .first-column {
            text-align: center;
        }
    </style>
</head>
<body style="padding:20px; font-size: 0.9rem">

    <form:form action="${create}" method="POST" modelAttribute="workbookItemsWithFormat">
        <table class="table table-bordered table-condensed">
            <colgroup>
                <col style="width: 10%" />
                <col style="width: 45%" />
                <col style="width: 45%" />
            </colgroup>
            <thead>
                <tr>
                    <th>ID</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                </tr>
            </thead>
            <tbody>

                <c:forEach items="${workbookItemsWithFormat.items}" varStatus="i">
                    <tr>
                        <td><form:input path="items[${i.index}].id" type="text" class="form-control-plaintext first-column" readonly="true" /></td>
                        <td><form:input path="items[${i.index}].firstName" type="text" class="form-control" /></td>
                        <td><form:input path="items[${i.index}].lastName" type="text" class="form-control"/></td>
                    </tr>
                </c:forEach>
            </tbody>
        </table>
        <div>
            <h4>Output format:</h4>
            <div class="form-check">
                <form:radiobutton path="selectedFormat" value="XLSX" class="form-check-input"/>
                <label for="XLSX" class="form-check-label">XLSX</label>
            </div>
            <div class="form-check">
                   <form:radiobutton path="selectedFormat" value="XLS" class="form-check-input"/>
                <label for="XLS" class="form-check-label">XLS</label>
            </div>
            <div class="form-check">
                <form:radiobutton path="selectedFormat" value="ODS" class="form-check-input"/>
                <label for="ODS" class="form-check-label">ODS</label>
            </div>
            <div class="form-check">
                <form:radiobutton path="selectedFormat" value="CSV" class="form-check-input"/>
                <label for="CSV" class="form-check-label">CSV</label>
            </div>
            <div class="form-check">
                <form:radiobutton path="selectedFormat" value="HTML" class="form-check-input"/>
                <label for="HTML" class="form-check-label">HTML</label>
            </div>
        </div>
        <hr>
        <button type="submit" class="btn btn-default">Export</button>
    </form:form>
</body>
</html>

See also


Next steps

GemBox.Spreadsheet for Java is a component that enables you to read, write, edit and convert spreadsheet files from your applications using one simple API.

Download Buy