Create and Save an Excel file with Spring and Maven
GemBox.Spreadsheet for Java has been retired
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.
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
Published: December 13, 2018 | Modified: April 6, 2023 | Author: Marek Turis