DEV Community

jelizaveta
jelizaveta

Posted on

Reading and Writing Excel Documents Using Java

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 will introduce how to use the Spire.XLS for Java library to easily read and write Excel documents.

Introduction to Spire.XLS for Java

Spire.XLS is a powerful Java Excel component that supports efficient creation, editing, reading, and conversion of Excel files. This library can handle both .xlsx and .xls formats with ease. It not only provides a comprehensive API but also exhibits fast performance and good documentation support, making developers more efficient when handling spreadsheets.

Installing Spire.XLS for Java with Maven

If your project uses Maven as a build tool, you can install Spire.XLS by adding the following dependency to your pom.xml file:

<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>15.12.15</version>
    </dependency>
</dependencies>
Enter fullscreen mode Exit fullscreen mode

With this, Maven will automatically download and include the necessary library files for you to use in your project.

Reading Excel Files in Java

In this section, we will introduce how to read data from an Excel file. Here is a simple code example that 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();
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Code Explanation

  1. Workbook Object : Create a Workbook object to load the Excel file.
  2. Loading the File : Load an existing Excel file using the loadFromFile method.
  3. Getting the Worksheet : Obtain the first worksheet using the getWorksheets().get(0) method.
  4. Traversing the Data : Use a nested loop to iterate over each row and column, printing the values in the cells.

Writing Excel Files in Java

Next, we will demonstrate how to create a new Excel file, set basic information for the worksheet, 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
        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
        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);
    }
}
Enter fullscreen mode Exit fullscreen mode

Code Explanation

  1. Workbook Object : Create a new Workbook object.
  2. Remove Default Worksheet : Clear the default worksheet using the clear method.
  3. Add Worksheet : Create a worksheet named "Employees."
  4. Merge Cells : Merge cells from A1 to G1.
  5. Write Data : Set the value of cell A1 and adjust its format.
  6. Insert Array Data : Insert data from a two-dimensional array into the worksheet.
  7. Set Borders and Formats : Set row heights, column widths, and cell border styles.
  8. Save the File : Save the workbook as a new Excel file.

Conclusion

By using the Spire.XLS for Java library, we can conveniently work with Excel documents. Whether reading existing data or generating 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.

Top comments (0)