Of course! Converting XML to Excel in Java is a common task. There are several ways to achieve this, ranging from using built-in libraries to more feature-rich third-party tools.

Here’s a comprehensive guide covering the most popular and effective methods.

Method 1: Using Apache POI (The Standard & Powerful Approach)

Apache POI is the go-to library for handling Microsoft Office files in Java. It provides a robust way to create Excel files (.xls and .xlsx). The key is to first parse the XML and then use the parsed data to populate an Excel sheet.

Step 1: Add Dependencies

You'll need two main libraries:

1. Apache POI: For creating the Excel file.
2. JDOM (or DOM4J, JAXB): For easily parsing the XML. JDOM is very straightforward.

If you're using Maven, add these to your pom.xml:

<dependencies>
    <!-- Apache POI for Excel -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
    <!-- JDOM for XML parsing -->
    <dependency>
        <groupId>org.jdom</groupId>
        <artifactId>jdom2</artifactId>
        <version>2.0.6.1</version>
    </dependency>
</dependencies>

Step 2: Prepare Your XML File

Let's assume you have an XML file named data.xml with a simple structure.

data.xml

<?xml version="1.0" encoding="UTF-8"?>
<customers>
    <customer>
        <id>101</id>
        <name>Alice Smith</name>
        <email>alice.smith@example.com</email>
        <country>USA</country>
    </customer>
    <customer>
        <id>102</id>
        <name>Bob Johnson</name>
        <email>bob.johnson@example.com</email>
        <country>Canada</country>
    </customer>
    <customer>
        <id>103</id>
        <name>Charlie Brown</name>
        <email>charlie.brown@example.com</email>
        <country>UK</country>
    </customer>
</customers>

Step 3: Write the Java Code

The logic will be:

1. Parse the XML file using JDOM.
2. Get the list of <customer> elements.
3. Create a new Excel workbook and sheet.
4. Create a header row from the child element names (e.g., id, name).
5. Iterate through each <customer> element.
6. For each customer, create a new row in Excel and populate its cells with the values.
7. Write the workbook to an output file.

XmlToExcelConverter.java

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jdom2.Document;
import org.jdom2.Element;
import org.jdom2.JDOMException;
import org.jdom2.input.SAXBuilder;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
public class XmlToExcelConverter {
    public static void main(String[] args) {
        // Define file paths
        String xmlFilePath = "data.xml";
        String excelFilePath = "customers.xlsx";
        try {
            // 1. Parse the XML file
            SAXBuilder saxBuilder = new SAXBuilder();
            Document document = saxBuilder.build(new File(xmlFilePath));
            Element rootElement = document.getRootElement();
            // 2. Create a new Excel workbook and sheet
            Workbook workbook = new XSSFWorkbook(); // Use .XSSFWorkbook for .xlsx
            Sheet sheet = workbook.createSheet("Customers");
            // 3. Get the list of customer elements
            List<Element> customers = rootElement.getChildren("customer");
            // 4. Create the header row
            Row headerRow = sheet.createRow(0);
            List<Element> firstCustomer = customers.get(0).getChildren();
            int headerCellIndex = 0;
            for (Element header : firstCustomer) {
                Cell cell = headerRow.createCell(headerCellIndex++);
                cell.setCellValue(header.getName());
            }
            // 5. Populate the data rows
            int rowIndex = 1; // Start from the second row (after header)
            for (Element customer : customers) {
                Row row = sheet.createRow(rowIndex++);
                int cellIndex = 0;
                for (Element dataElement : customer.getChildren()) {
                    Cell cell = row.createCell(cellIndex++);
                    cell.setCellValue(dataElement.getText());
                }
            }
            // 6. Auto-size columns for better readability
            for (int i = 0; i < firstCustomer.size(); i++) {
                sheet.autoSizeColumn(i);
            }
            // 7. Write the workbook to the output file
            try (FileOutputStream fileOut = new FileOutputStream(excelFilePath)) {
                workbook.write(fileOut);
            }
            System.out.println("Excel file created successfully at: " + excelFilePath);
        } catch (JDOMException | IOException e) {
            e.printStackTrace();
        } finally {
            // Ensure workbook is closed
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Method 2: Using a Library Specifically for XML-to-Excel (Easier for Complex Mappings)

If your XML structure is complex or you need to define mappings explicitly, a library like EasyExcel by Alibaba can be a great choice. It's designed for high performance and ease of use.

Step 1: Add Dependencies

Maven:

<dependencies>
    <!-- EasyExcel for Excel handling -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.3.2</version>
    </dependency>
    <!-- EasyExcel needs a SAX parser for XML, StAX is a good choice -->
    <dependency>
        <groupId>com.fasterxml</groupId>
        <artifactId>aalto-xml</artifactId>
        <version>1.3.2</version>
    </dependency>
</dependencies>

Step 2: Create a Data Model (POJO)

You need to create a Java class that mirrors the structure of your XML data elements.

Customer.java

public class Customer {
    private Integer id;
    private String name;
    private String email;
    private String country;
    // Getters and Setters are required by EasyExcel
    public Integer getId() { return id; }
    public void setId(Integer id) { this.id = id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    public String getCountry() { return country; }
    public void setCountry(String country) { this.country = country; }
    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", country='" + country + '\'' +
                '}';
    }
}

Step 3: Write the Java Code

EasyExcel has a convenient ExcelWriter and AnalysisEventListener for streaming data, which is very memory-efficient.

XmlToExcelEasyExcel.java

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.fasterxml.aalto.stax.InputFactoryImpl;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;
import javax.xml.stream.XMLEventReader;
import javax.xml.stream.XMLInputFactory;
import javax.xml.stream.events.XMLEvent;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
public class XmlToExcelEasyExcel {
    public static void main(String[] args) {
        String xmlFilePath = "data.xml";
        String excelFilePath = "customers_easyexcel.xlsx";
        List<Customer> customers = parseXmlToList(xmlFilePath);
        if (customers != null && !customers.isEmpty()) {
            writeListToExcel(customers, excelFilePath);
        }
    }
    private static List<Customer> parseXmlToList(String filePath) {
        List<Customer> customers = new ArrayList<>();
        Customer currentCustomer = null;
        try (FileInputStream fis = new FileInputStream(filePath)) {
            XMLInputFactory factory = XMLInputFactory.newInstance();
            XMLEventReader eventReader = factory.createXMLEventReader(fis);
            while (eventReader.hasNext()) {
                XMLEvent event = eventReader.nextEvent();
                if (event.isStartElement()) {
                    String elementName = event.asStartElement().getName().getLocalPart();
                    if ("customer".equals(elementName)) {
                        currentCustomer = new Customer();
                    } else if (currentCustomer != null) {
                        // This is a simplified parser. A production version would be more robust.
                        // For example, it doesn't handle attributes or nested structures well.
                        String value = eventReader.nextEvent().asCharacters().getData();
                        switch (elementName) {
                            case "id":
                                currentCustomer.setId(Integer.parseInt(value));
                                break;
                            case "name":
                                currentCustomer.setName(value);
                                break;
                            case "email":
                                currentCustomer.setEmail(value);
                                break;
                            case "country":
                                currentCustomer.setCountry(value);
                                break;
                        }
                    }
                } else if (event.isEndElement()) {
                    String elementName = event.asEndElement().getName().getLocalPart();
                    if ("customer".equals(elementName) && currentCustomer != null) {
                        customers.add(currentCustomer);
                        currentCustomer = null;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return customers;
    }
    private static void writeListToExcel(List<Customer> customers, String filePath) {
        // EasyExcel.write(filePath, Customer.class).sheet("Customers").doWrite(customers);
        // The above one-liner is sufficient, but let's break it down for clarity.
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(filePath, Customer.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet("Customers").build();
            excelWriter.write(customers, writeSheet);
            System.out.println("Excel file created successfully at: " + filePath);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }
}

Comparison and Recommendation

Which One Should You Choose?

• Choose Apache POI if:

  • You need maximum flexibility and control over the Excel file (e.g., complex styling, multiple sheets, charts).
  • Your project is already integrated with the Apache POI ecosystem.
  • You are comfortable with manual iteration over your XML data.

• Choose EasyExcel if:

  • Your primary goal is to quickly convert structured XML data to an Excel sheet.
  • You are working with very large XML files and need a memory-efficient solution.
  • You prefer a more modern, fluent API and working with Java objects (POJOs).

For most standard use cases, Apache POI (Method 1) is the most reliable and widely adopted solution. It's a powerful tool to have in your Java skill set.