Programmatic Excel manipulation is a common requirement in enterprise Java applications. Whether you need to generate reports, process data imports, or automate spreadsheet workflows, the ability to create, read, and update Excel documents reliably is valuable.
This article demonstrates three fundamental Excel operations using Spire.XLS for Java, a library that processes Excel files without requiring Microsoft Office installation on the server.
Project Setup
Add the library to your project using Maven. Include the following repository and dependency in your pom.xml:
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls</artifactId>
<version>16.4.1</version>
</dependency>
</dependencies>
Refresh your project to download the required JAR files. The library requires Java 8 or later. A free version is available with limitations on the number of worksheets per workbook; a paid license removes these restrictions.
Creating an Excel Workbook
The Workbook class represents an Excel workbook model. You can add worksheets, write data to cells, apply formatting, and save in various Excel formats.
Here's an example that creates a spreadsheet with formatted headers and structured data:
import com.spire.xls.*;
public class CreateSpreadsheet {
public static void main(String[] args) {
//Create a Workbook object
Workbook wb = new Workbook();
//Remove default worksheets
wb.getWorksheets().clear();
//Add a worksheet and name it "Employee"
Worksheet sheet = wb.getWorksheets().add("Employee");
//Merge cells A1 to G1 for a title row
sheet.getRange().get("A1:G1").merge();
//Write title text and apply formatting
sheet.getRange().get("A1").setValue("Basic Information of Employees");
sheet.getRange().get("A1").setHorizontalAlignment(HorizontalAlignType.Center);
sheet.getRange().get("A1").setVerticalAlignment(VerticalAlignType.Center);
sheet.getRange().get("A1").getStyle().getFont().isBold(true);
sheet.getRange().get("A1").getStyle().getFont().setSize(13);
//Set row height for the title row (1-based index)
sheet.setRowHeight(1, 30);
//Define data as a two-dimensional array
String[][] data = new String[][]{
{"Name", "Gender", "Birth Date", "Education", "Phone", "Position", "ID"},
{"Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021"},
{"Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022"},
{"Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023"},
{"Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024"},
{"Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025"}
};
//Insert array starting at row 2, column 1
sheet.insertArray(data, 2, 1);
//Set row height for data rows
sheet.getRange().get("A2:G7").setRowHeight(15);
//Adjust column widths for readability
sheet.setColumnWidth(2, 15);
sheet.setColumnWidth(3, 21);
sheet.setColumnWidth(4, 15);
//Apply border styles to the data range
sheet.getRange().get("A2:G7").borderAround(LineStyleType.Medium);
sheet.getRange().get("A2:G7").borderInside(LineStyleType.Thin);
sheet.getRange().get("A2:G2").borderAround(LineStyleType.Medium);
sheet.getRange().get("A2:G7").getBorders().setKnownColor(ExcelColors.Black);
//Save to an .xlsx file
wb.saveToFile("output/NewSpreadsheet.xlsx", FileFormat.Version2016);
wb.dispose();
}
}
Key methods in this example:
-
getWorksheets().add()creates a new named worksheet -
getRange().get()references specific cells or ranges -
setValue()writes content to a cell -
insertArray()populates a range from a two-dimensional array in one operation -
saveToFile()writes the workbook to disk
Reading Data from a Worksheet
To retrieve data from an existing file, load the workbook, access the target worksheet, and iterate through cells to extract their values.
import com.spire.xls.*;
public class ReadData {
public static void main(String[] args) {
//Create a Workbook instance
Workbook wb = new Workbook();
//Load an Excel file
wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
//Access the first worksheet
Worksheet sheet = wb.getWorksheets().get(0);
//Get the range containing actual data
CellRange locatedRange = sheet.getAllocatedRange();
//Loop through rows and columns
for (int i = 0; i < locatedRange.getRows().length; i++) {
for (int j = 0; j < locatedRange.getColumnCount(); j++) {
System.out.print(locatedRange.get(i + 1, j + 1).getValue() + " ");
}
System.out.println();
}
wb.dispose();
}
}
The getAllocatedRange() method automatically detects the area of the worksheet that contains data, which is useful when you don't know the exact boundaries in advance. The getValue() method returns cell contents, handling both text and numeric values.
Updating an Existing Workbook
Modifying a workbook follows a straightforward pattern: load the file, change specific cells or ranges, and save the result.
import com.spire.xls.*;
public class UpdateExcel {
public static void main(String[] args) {
//Create a Workbook instance
Workbook wb = new Workbook();
//Load an Excel file
wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
//Access the first worksheet
Worksheet sheet = wb.getWorksheets().get(0);
//Update a single cell
sheet.getRange().get("A1").setValue("Updated Employee Information");
//Save changes to a new file
wb.saveToFile("output/Updated.xlsx", FileFormat.Version2016);
wb.dispose();
}
}
This approach works well for targeted modifications. For bulk updates, you can combine cell-range iteration with conditional logic to apply changes selectively. When saving, writing to a new file rather than overwriting the original preserves the source data, which can be useful during debugging.
Other Libraries for Excel Processing in Java
Several alternatives exist in the Java ecosystem for Excel manipulation:
| Library | License | Notable Characteristics |
|---|---|---|
| Apache POI | Apache 2.0 (open source) | Mature project with broad format support; API can be verbose for simple tasks |
| EasyExcel | Apache 2.0 (open source) | Memory-efficient streaming approach; well-suited for large file processing |
| Aspose.Cells | Commercial | Extensive feature set including charts and advanced formatting |
The choice depends on your project's requirements. Apache POI provides comprehensive functionality without licensing costs but can require more boilerplate code. EasyExcel, originally developed by Alibaba, emphasizes memory efficiency when handling large datasets. Aspose.Cells is a commercial product with support for advanced Excel features beyond basic data manipulation.
Conclusion
Creating, reading, and updating Excel documents in Java with Spire.XLS for Java involves working with the Workbook and Worksheet classes to manipulate cell ranges and their values. The examples in this article covered writing formatted data from arrays, reading cell contents through iteration, and modifying specific cells before saving. These operations serve as fundamental building blocks for more complex Excel automation tasks such as merging multiple workbooks, converting spreadsheets to PDF format, or generating pivot tables. When evaluating this library for your projects, consider the free version's worksheet limitations and whether they align with the scale of your typical documents. For further information, the library's documentation covers additional topics including chart creation, formula calculation, and data import from external sources.
Top comments (0)