Excel spreadsheets are widely used for organizing and analyzing data in various fields. While there are dedicated tools for working with Excel files, such as Microsoft Excel itself, sometimes you may need to automate Excel operations using Java programming.
Setting up the Dependencies
To get started, you need to include the necessary dependencies in your Java project. In this case, we’ll be using Apache POI, which can be downloaded from the official Apache POI website. Make sure to download the appropriate version of Apache POI based on your Java version and project requirements. Once you have downloaded the library, make sure to add it to your project’s classpath.
Creating the Excel Class
Manipulating Excel files programmatically often requires working with libraries like Apache POI. While these libraries provide extensive functionalities, they can be complex and time-consuming to use directly. By creating a custom Java class, we can encapsulate the complexity and provide a simplified interface for common Excel operations.
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class Excel {
private Workbook workbook;
private int rowNumber;
public Excel() {
this(null);
}
public Excel(String sheetName) {
this.init(sheetName);
}
/**
* Initialize the Excel workbook and set the initial row number.
*
* @param sheetName the name of the sheet to be created (null for default sheet)
*/
public void init(String sheetName) {
this.workbook = new XSSFWorkbook();
if (sheetName != null) {
this.workbook.createSheet(sheetName);
} else {
this.workbook.createSheet();
}
this.rowNumber = 0;
}
/**
* Create a new sheet in the workbook with the specified sheet name.
*
* @param sheetName the name of the sheet to be created
*/
public void createSheet(String sheetName) {
this.workbook.createSheet(sheetName);
}
/**
* Write a row of records to the default sheet.
*
* @param records an array of strings representing the records in the row
*/
public void writeRow(String[] records) {
Sheet sheet = this.workbook.getSheetAt(0);
this.write(sheet, records);
}
/**
* Write a row of records to the specified sheet.
*
* @param sheetName the name of the sheet to write the row to
* @param records an array of strings representing the records in the row
*/
public void writeRow(String sheetName, String[] records) {
Sheet sheet = this.workbook.getSheet(sheetName);
this.write(sheet, records);
}
/**
* Save the Excel workbook to a file.
*
* @param filename the name of the file to save the workbook to
* @throws IOException if an I/O error occurs while saving the workbook
*/
public void save(String filename) throws IOException {
try (FileOutputStream outputStream = new FileOutputStream(filename)) {
workbook.write(outputStream);
}
}
/**
* Write a row of records to the given sheet.
*
* @param sheet the sheet to write the row to
* @param records an array of strings representing the records in the row
*/
private void write(Sheet sheet, String[] records) {
Row row = sheet.createRow(this.rowNumber++);
for (int i = 0; i < records.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(records[i]);
}
}
}
Creating Excel Files
The provided code demonstrates how to create an Excel file, write data to it, and save it with the name “excel.xlsx”:
import java.io.IOException;
public class Main {
public static void main(final String[] args) {
// Create an instance of the Excel class
Excel excel = new Excel();
// Write a row to the default sheet (Sheet0)
excel.writeRow(new String[]{"John Doe", "8", "Python", "Beginner"});
try {
// Save the Excel file with the provided name
excel.save("excel.xlsx");
System.out.println("Excel file saved successfully!");
} catch (IOException exception) {
// Handle any exception occurred during file saving
System.out.println(exception.getMessage());
}
}
}
The code above demonstrates how to create Excel files and write data to them. However, it does not show how to create multiple sheets within the same Excel file or write data to different sheets. The code below demonstrates how to accomplish these tasks:
import java.io.IOException;
public class Main {
public static void main(final String[] args) {
// Create an instance of the Excel class
// and specify the default sheet name
Excel excel = new Excel("Sheet1");
// Create a new sheet named "Sheet2"
excel.createSheet("Sheet2");
// Write a row to the default sheet (Sheet1)
excel.writeRow(new String[]{"John Doe", "8", "Python", "Beginner"});
// Write a row to the specified sheet (Sheet2)
excel.writeRow("Sheet2", new String[]{"John Doe", "8", "Python", "Beginner"});
try {
// Save the Excel file with the provided name
excel.save("excel.xlsx");
System.out.println("Excel file saved successfully!");
} catch (IOException exception) {
// Handle any exception occurred during file saving
System.out.println(exception.getMessage());
}
}
}
Working with Excel files in Java can be useful for various tasks, such as generating reports, exporting data, or manipulating spreadsheet data programmatically. By leveraging the Excel class and its methods, we can efficiently create, write to, and save Excel files. It is important to handle exceptions properly when working with file operations to ensure a robust and reliable program.
That was all I had to share with you guys. If you found this code informative and would love to see more, don’t forget to subscribe to our newsletter! 😊
