How to read and write excel file

Cover Image for How to read and write excel file
Matheus Mello
Matheus Mello
published a few days ago. updated a few hours ago

How to Read and Write Excel Files in Java: A Simple Guide 💻📝

<p>So you want to read and write an Excel file in Java, huh? No worries! In this guide, we'll walk you through the process step by step. Whether you're a Java newbie or a seasoned developer, we've got you covered. Let's dive in! 🚀</p>

The Challenge 🤔

<p>The task at hand is to read and write an Excel file with 3 columns and N rows, where each cell contains a string. You're looking for a simple code snippet to achieve this. The burning question on your mind is: "Do I need to use any external library or does Java have built-in support for it?" 🤷‍♂️</p>

The Solution 🙌

<p>Fortunately, Java does not have built-in support for reading and writing Excel files. However, there are several popular external libraries that you can leverage to accomplish this task effortlessly. One such library is Apache POI, a powerful and widely-used Java library for manipulating various file formats, including Excel. Let's see how you can use Apache POI to get the job done! 😎</p>

Step 1: Add Apache POI to Your Project 📥

<p>Before we get started, you need to add the Apache POI dependencies to your project. You can do this by adding the following Maven dependencies to your `pom.xml` file:</p>

<dependencies>
    <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>
</dependencies>

<p>If you're not using Maven, you can manually download the Apache POI JAR files and add them to your project's classpath.</p>

Step 2: Reading an Excel File 📖

<p>Reading an Excel file is fairly straightforward with Apache POI. Here's a code snippet that demonstrates how to read the values in all cells of columns 1, 2, and 3:</p>

import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.*;

public class ExcelReader {
    public static void main(String[] args) {
        try {
            FileInputStream file = new FileInputStream("path/to/your/excel/file.xlsx");
            Workbook workbook = WorkbookFactory.create(file);
            Sheet sheet = workbook.getSheetAt(0);

            for (Row row : sheet) {
                Cell cell1 = row.getCell(0);
                Cell cell2 = row.getCell(1);
                Cell cell3 = row.getCell(2);

                String value1 = cell1.getStringCellValue();
                String value2 = cell2.getStringCellValue();
                String value3 = cell3.getStringCellValue();

                System.out.println(value1 + ", " + value2 + ", " + value3);
            }

            file.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

<p>Make sure to replace `"path/to/your/excel/file.xlsx"` with the actual path to your Excel file. This code snippet opens the file, retrieves the first sheet, and iterates over its rows to fetch the values in each cell of columns 1, 2, and 3. Finally, it prints the values to the console.</p>

Step 3: Writing to an Excel File ✍️

<p>Writing to an Excel file using Apache POI is just as easy. Here's a code snippet that demonstrates how to write values to columns 1, 2, and 3 of each row:</p>

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;

public class ExcelWriter {
    public static void main(String[] args) {
        try {
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Sheet1");

            // Sample data
            String[][] data = {{"Value1", "Value2", "Value3"},
                               {"Value4", "Value5", "Value6"},
                               // Add more rows as needed
                              };

            int rowCount = 0;

            for (String[] row : data) {
                Row excelRow = sheet.createRow(rowCount++);

                int columnCount = 0;

                for (String value : row) {
                    Cell cell = excelRow.createCell(columnCount++);
                    cell.setCellValue(value);
                }
            }

            FileOutputStream file = new FileOutputStream("path/to/your/excel/file.xlsx");
            workbook.write(file);
            file.close();

            System.out.println("Excel file created successfully!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

<p>Similar to Step 2, make sure to replace `"path/to/your/excel/file.xlsx"` with the desired path for your Excel file. This code snippet creates a new workbook, adds a sheet named "Sheet1", and populates it with values from the `data` array. Finally, it saves the workbook to the specified file location.</p>

The Call-to-Action 📢

<p>There you have it! You now know how to read and write Excel files in Java using Apache POI. Go ahead and give it a try in your own project. Feel free to experiment, add more functionality, and unleash your Java coding skills! 🔥</p>

<p>If you found this guide helpful or have any further questions, we'd love to hear from you! Leave a comment below and let us know your thoughts. Happy coding! 😄💻</p>


More Stories

Cover Image for How can I echo a newline in a batch file?

How can I echo a newline in a batch file?

updated a few hours ago
batch-filenewlinewindows

🔥 💻 🆒 Title: "Getting a Fresh Start: How to Echo a Newline in a Batch File" Introduction: Hey there, tech enthusiasts! Have you ever found yourself in a sticky situation with your batch file output? We've got your back! In this exciting blog post, we

Matheus Mello
Matheus Mello
Cover Image for How do I run Redis on Windows?

How do I run Redis on Windows?

updated a few hours ago
rediswindows

# Running Redis on Windows: Easy Solutions for Redis Enthusiasts! 🚀 Redis is a powerful and popular in-memory data structure store that offers blazing-fast performance and versatility. However, if you're a Windows user, you might have stumbled upon the c

Matheus Mello
Matheus Mello
Cover Image for Best way to strip punctuation from a string

Best way to strip punctuation from a string

updated a few hours ago
punctuationpythonstring

# The Art of Stripping Punctuation: Simplifying Your Strings 💥✂️ Are you tired of dealing with pesky punctuation marks that cause chaos in your strings? Have no fear, for we have a solution that will strip those buggers away and leave your texts clean an

Matheus Mello
Matheus Mello
Cover Image for Purge or recreate a Ruby on Rails database

Purge or recreate a Ruby on Rails database

updated a few hours ago
rakeruby-on-railsruby-on-rails-3

# Purge or Recreate a Ruby on Rails Database: A Simple Guide 🚀 So, you have a Ruby on Rails database that's full of data, and you're now considering deleting everything and starting from scratch. Should you purge the database or recreate it? 🤔 Well, my

Matheus Mello
Matheus Mello