In this step-by-step guide, we’ll show you how to create a Spring Boot web application that allows you to write data to an Excel file, display it on a web page, and even download the Excel file (write, view & download) with a click of a button. We’ll keep things simple so that anyone, regardless of their coding experience, can follow along.

Prerequisites

Before we begin, ensure that you have Java and Spring Boot installed on your computer.

Step 1: Create a Spring Boot Project

  1. Visit Spring Initializr.
  2. Select “Spring Web” and “Thymeleaf” from the dependencies list.
  3. Click “Generate” to download the project ZIP file.
  4. Unzip the downloaded file and open it in your preferred code editor.

Step 2: Add Excel Dependencies

Open the pom.xml file in your project and add the following dependencies for working with Excel files using Apache POI:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

Step 3: Create a Controller for Writing Data

Now, let’s create a controller class that will write data to an Excel file when a button is clicked. We’ll also add a simple web page for this.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

import java.io.FileOutputStream;
import java.io.IOException;

@Controller
public class ExcelController {

    @GetMapping("/")
    public String home() {
        return "home";
    }

    @GetMapping("/writeExcel")
    public String writeExcel() {
        String filePath = "your_excel_file.xlsx";

        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Sheet1");

            // Create the header row
            Row headerRow = sheet.createRow(0);
            String[] headers = {"Column1", "Column2", "Column3"};
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
            }

            // Your data (example)
            String[][] data = {
                {"Value1", "Value2", "Value3"},
                {"Value4", "Value5", "Value6"},
            };

            // Start writing data from row 1 (after the header row)
            int rowNum = 1;
            for (String[] rowData : data) {
                Row row = sheet.createRow(rowNum++);
                for (int i = 0; i < rowData.length; i++) {
                    Cell cell = row.createCell(i);
                    cell.setCellValue(rowData[i]);
                }
            }

            // Save the workbook to a file
            try (FileOutputStream fos = new FileOutputStream(filePath)) {
                workbook.write(fos);
                System.out.println("Data written to Excel file successfully.");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return "redirect:/";
    }
}

Here, we’ve created a controller with two methods: home and writeExcel. The home method returns a simple webpage with a button, and the writeExcel method writes data to an Excel file.

Step 4: Create a Web Interface

Now, let’s create a simple web page that displays a button to write data to Excel.

Create a new HTML file named home.html inside the src/main/resources/templates directory:

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Write to Excel</title>
</head>
<body>
    <h1>Write Data to Excel</h1>
    <a th:href="@{/writeExcel}" class="btn btn-primary">Write to Excel</a>
</body>
</html>

This HTML file displays a button labeled “Write to Excel” which, when clicked, will trigger the Excel writing process.

Step 5: Run the Application

Now, it’s time to run your Spring Boot application. Open a terminal, navigate to your project’s root directory, and run the following command:

./mvnw spring-boot:run
  • Most developers prefer using IDEs like IntelliJ IDEA, Eclipse, or Visual Studio Code for Spring Boot development.
  • In your IDE, open the project and locate the main application class (usually annotated with @SpringBootApplication).
  • Right-click on the main class and select “Run” or “Debug” to start the Spring Boot application.
  • If you are using Maven, navigate to the project’s root directory in your command-line interface.
  • Run the following command to build and run the Spring Boot application:
mvn spring-boot:run

If you are using Gradle, navigate to the project’s root directory and use this command:

./gradlew bootRun

Your application will start, and you can access it in your web browser at http://localhost:8080.

Step 6: Display and Download Excel Data (Continued)

Continuing from where we left off, let’s add the ability to display Excel data on a web page and download the Excel file.

Display Excel Data (write, view & download)

In your ExcelController class, add the following method:

@GetMapping("/viewExcel")
public String viewExcel(Model model) {
    String filePath = "your_excel_file.xlsx";
    List<List<String>> excelData = ExcelDataReader.readExcelData(filePath); // Fetch data dynamically

    model.addAttribute("excelData", excelData);
    return "view-excel";
}

In this code, we’ve created a new method, viewExcel, that reads data from the Excel file and passes it to a Thymeleaf template.

Create an Excel Data Reader Service

Create a new service class named ExcelDataReader:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

@Service
public class ExcelDataReader {

    public static List<List<String>> readExcelData(String filePath) {
        List<List<String>> excelData = new ArrayList<>();

        try (Workbook workbook = new XSSFWorkbook(new FileInputStream(filePath))) {
            Sheet sheet = workbook.getSheetAt(0); // Assuming the data is in the first sheet

            for (Row row : sheet) {
                List<String> rowData = new ArrayList<>();
                for (Cell cell : row) {
                    rowData.add(cell.toString());
                }
                excelData.add(rowData);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return excelData;
    }
}

This service class reads the data from the Excel file and returns it as a list of lists of strings.

Create an HTML Template for Viewing Excel Data

Now, create an HTML template named view-excel.html in the resources/templates directory:

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>View Excel Data</title>
</head>
<body>
    <h1>Excel Data</h1>
    <table>
        <thead>
            <tr>
                <th th:each="header : ${excelData[0]}" th:text="${header}"></th>
            </tr>
        </thead>
        <tbody>
            <tr th:each="row : ${excelData}" th:if="${not #lists.isEmpty(row)}">
                <td th:each="cell : ${row}" th:text="${cell}"></td>
            </tr>
        </tbody>
    </table>
</body>
</html>

This Thymeleaf template dynamically renders the Excel data in a tabular format.

Step 7: Enable Excel File Download

Now, let’s add the capability to download the Excel file.

In your ExcelController class, add the following method:

@GetMapping("/downloadExcel")
public void downloadExcel(HttpServletResponse response) {
    String filePath = "your_excel_file.xlsx";

    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition", "attachment; filename=your_excel_file.xlsx");

    try (InputStream inputStream = new FileInputStream(filePath);
         OutputStream outputStream = response.getOutputStream()) {

        byte[] buffer = new byte[1024];
        int bytesRead;
        while ((bytesRead = inputStream.read(buffer)) != -1) {
            outputStream.write(buffer, 0, bytesRead);
        }
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Here, we’ve added a new downloadExcel method that handles the download process. It sets the appropriate response headers and streams the Excel file for download.

Step 8: Add a Link to the Home Page

To make it easy for users to access all these features, let’s add links on the home page.

In your home.html template, add the following link:

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Home Page</title>
</head>
<body>
    <h1>Welcome to the Home Page</h1>
    <a th:href="@{/writeExcel}" class="btn btn-primary">Write to Excel</a>
    <a th:href="@{/viewExcel}" class="btn btn-primary">View Excel Data</a>
    <a th:href="@{/downloadExcel}" class="btn btn-primary">Download Excel</a>
</body>
</html>

With these links, you can easily navigate to the different features of your application.

Step 9: Run the Application (Again)

Now, run your Spring Boot application again:

./mvnw spring-boot:run

Visit http://localhost:8080 in your web browser, and you’ll find the home page with buttons to write data to Excel, view Excel data, and download the Excel file.

Congratulations! You’ve successfully created a Spring Boot web application for Excel data management. You can write data to Excel, display it on a web page, and download the Excel file.

This tutorial aimed to keep things simple, making it accessible for beginners and those new to Spring Boot. Feel free to explore and expand upon this foundation to meet your specific requirements and enhance your application further.

Happy coding!

FAQs

Q1: How do I set up my development environment for this Spring Boot project?

A: To set up your development environment for this project, you need Java Development Kit (JDK), Spring Boot, and a code editor or Integrated Development Environment (IDE) like IntelliJ IDEA or Visual Studio Code. You can follow the steps provided in the tutorial for detailed instructions.

Q2: How can I customize the Excel file’s structure and data?

A: You can customize the Excel file structure and data in the ExcelController class. Modify the writeExcel method to create the desired headers and data. You can also replace the static data with dynamic data from your application or database.

Q3: What if I encounter errors while running the application?

A: Common errors include missing dependencies, incorrect file paths, or issues with your code. Double-check your project setup, dependencies, and file paths. Also, review the error messages in your console for clues to the issue. Feel free to ask for help on developer forums or communities if needed.

Q4: How can I enhance this application further?

A: There are many ways to enhance this application. You can add authentication and authorization, improve the UI, allow users to upload Excel files, or integrate it with a database to manage data dynamically. Explore Spring Boot’s features and libraries to extend its capabilities.

Q5: Can I use this application to manage large Excel files?

A: This example is designed for small to medium-sized Excel files. For large files, consider implementing streaming techniques to avoid loading the entire file into memory. Apache POI provides streaming capabilities for handling large Excel files efficiently.

Q6: How can I secure the download endpoint?

A: To secure the download endpoint (/downloadExcel), you can use Spring Security. Configure authentication and authorization rules to restrict access to authorized users only. Spring Security provides robust security features for web applications.

Q7: What if I want to support different Excel formats (e.g., .xls)?

A: This example uses the .xlsx format. To support other formats like .xls, you’ll need to update the Apache POI dependencies and adapt the code accordingly, as different formats may have different APIs and requirements.

Q8: How do I deploy this Spring Boot application to a production server?

A: To deploy your Spring Boot application to a production server, you typically package it as a standalone executable JAR file and deploy it to a server of your choice (e.g., Tomcat, Jetty). Ensure you have appropriate server configurations, environment variables, and security measures in place for production deployment.

Q9: Can I schedule automatic Excel data updates?

A: Yes, you can schedule automatic updates using Spring’s scheduling features. Consider using Spring’s @Scheduled annotation to periodically run a method that updates the Excel file’s data.

Q10: Where can I find more resources for Spring Boot development?

A: You can find more resources, tutorials, and documentation on Spring Boot on the official Spring Boot website. Additionally, there are numerous online courses, blogs, and forums where you can learn and seek assistance from the Spring Boot community.

Feel free to reach out if you have more questions or encounter specific issues while working on your Spring Boot Excel data management project. Happy coding!

Similar Posts

Custom ClassLoaders and Garbage Collection in Java: A Comprehensive Guide

Custom ClassLoaders and Garbage Collection in Java: A Comprehensive Guide

ByPramit Kumar Sharma

Java’s ClassLoader plays a crucial role in loading classes into the Java Virtual Machine (JVM). In this guide, we explore the relationship between custom ClassLoaders and garbage collection, providing code examples and addressing frequently asked questions. Table of Contents 1. Introduction Understanding ClassLoaders in Java Java uses a hierarchical class loading mechanism. Each ClassLoader, except…

| |

Logging SQL Statements in Spring Boot: A Comprehensive Guide

ByPramit Kumar Sharma

Introduction: Logging SQL statements in a Spring Boot application can be incredibly useful for debugging and monitoring database interactions. However, it’s essential to configure your application correctly to capture these statements. In this comprehensive guide, we’ll walk you through the process, ensuring that even students can grasp the concept. Configuring SQL Statement Logging in Spring…

| |

Java’s Pass-by-Value and Its Impact on Code Behavior: Explained

ByPramit Kumar Sharma

Introduction Hey there, fellow Java explorer! Today, we’re going to dive into a concept that can sometimes puzzle even experienced programmers: Java’s pass-by-value. We’ll break it down in a way that’s easy to grasp, even if you’re just starting your Java journey. Java’s Pass-by-Value: What Does It Really Mean? Okay, let’s start with the basics….

| |

DataSource Errors: Mastering Spring Boot – Expert Tips

ByPramit Kumar Sharma

Introduction: As a developer working with Spring Boot, encountering errors during application setup, including ‘DataSource Errors,’ is not uncommon. One such error message that can be encountered relates to database configuration: ‘Failed to configure a DataSource: ‘url’ attribute is not specified and no embedded datasource could be configured.’ In this article, we will delve into…

| | |

Turbocharge Your Java Testing with These Mocking Secrets!

ByPramit Kumar Sharma

Introduction: When it comes to writing robust and reliable Java applications, testing plays a pivotal role in ensuring their quality. One critical aspect of testing is mocking, and there are various techniques and tools available to help us create mock objects for our tests. In this article, we’ll explore the art of mocking in Java…

Thread Pinning The Hidden Threat in HttpClient’s
| | |

Thread Pinning The Hidden Threat in HttpClient’s

ByPramit Kumar Sharma

Java 21 introduced a new built-in HTTP client API called HttpClient to replace the older HttpURLConnection. HttpClient uses an asynchronous, non-blocking approach for better performance. However, its threading model includes one controversial design choice – thread pinning. In this comprehensive guide, we’ll dig into how it works, the issues it can cause, and techniques to…

4 Comments

  1. This Artical is very helpful for those student who is fresher and working on real time project after reading and following these all step i solved my real time project problem

    Reply

    1. I’m delighted to hear that the article helped you overcome your real-time project challenges. If you have any more questions or need assistance in the future, feel free to reach out. Best of luck with your ongoing projects!

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *