Reading and Writing Excel Documents Using Java

Source: Dev.to

Introduction

In modern software development, managing and operating on Excel documents is a common requirement. Whether in data analysis, report generation, or management information systems, Excel plays a vital role. This article introduces how to use the Spire.XLS for Java library to easily read and write Excel documents.

Spire.XLS is a powerful Java Excel component that supports efficient creation, editing, reading, and conversion of Excel files. It handles both .xlsx and .xls formats with ease, offers a comprehensive API, fast performance, and good documentation, helping developers work more efficiently with spreadsheets.

Maven Dependency

If your project uses Maven, add the following to your pom.xml:

<dependency>
    <groupId>com.e-iceblue</groupId>
    <artifactId>e-iceblue</artifactId>
    <version>https://repo.e-iceblue.com/nexus/content/groups/public/</version>
</dependency>

<dependency>
    <groupId>e-iceblue</groupId>
    <artifactId>spire.xls</artifactId>
    <version>15.12.15</version>
</dependency>

Maven will automatically download and include the necessary library files for you.

Reading Data from an Excel File

The following example shows how to load an existing Excel file and output its contents.

import com.spire.xls.CellRange;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class ReadData {

    public static void main(String[] args) {

        // Create a Workbook object
        Workbook wb = new Workbook();

        // Load an existing Excel file
        wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");

        // Get the first worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        // Get the range of cells with data
        CellRange locatedRange = sheet.getAllocatedRange();

        // Iterate over rows
        for (int i = 0; i < locatedRange.getRows().length; i++) {

            // Iterate over columns
            for (int j = 0; j < locatedRange.getColumnCount(); j++) {

                // Get the data from a specific cell
                System.out.print(locatedRange.get(i + 1, j + 1).getValue() + "  ");
            }
            System.out.println();
        }
    }
}

Explanation

Creating a New Excel File

The next example demonstrates how to create a new workbook, set basic worksheet information, and write data.

import com.spire.xls.*;

public class CreateSpreadsheet {

    public static void main(String[] args) {

        // Create a Workbook object
        Workbook wb = new Workbook();

        // Remove the default worksheet
        wb.getWorksheets().clear();

        // Add a worksheet named "Employees"
        Worksheet sheet = wb.getWorksheets().add("Employees");

        // Merge cells from A1 to G1
        sheet.getRange().get("A1:G1").merge();

        // Write data to A1 and apply formatting
        sheet.getRange().get("A1").setValue("Basic Information of Huayu Auto Company 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 the height of the first row
        sheet.setRowHeight(1, 30);

        // Create a two‑dimensional array with sample data
        String[][] twoDimensionalArray = new String[][]{
                {"Name", "Gender", "Birth Date", "Education", "Phone Number", "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"}
        };

        // Import data from the array into the worksheet (starting at row 2, column 1)
        sheet.insertArray(twoDimensionalArray, 2, 1);

        // Set the row height for a range
        sheet.getRange().get("A2:G7").setRowHeight(15);

        // Set column widths
        sheet.setColumnWidth(2, 15);
        sheet.setColumnWidth(3, 21);
        sheet.setColumnWidth(4, 15);

        // Set border styles
        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 as a .xlsx file
        wb.saveToFile("output/NewSpreadsheet.xlsx", FileFormat.Version2016);
    }
}

Explanation

Conclusion

By using the Spire.XLS for Java library, you can conveniently work with Excel documents—whether reading existing data or generating new spreadsheets programmatically. The library’s rich API and straightforward syntax make it an excellent choice for Java‑based Excel manipulation.

Conclusion

New spreadsheets, Spire.XLS offers great convenience. Its simple‑to‑use API and rich features allow Java developers to easily implement various Excel operations. I hope this article helps you get started quickly, and I look forward to seeing you discover its potential in real applications.