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>

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.


Like it?

Download Buy

If you want to try the GemBox.Spreadsheet for Java yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.