How to read and write excel file
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>