Working with XML and JSON - Apache POI Tutorial

Apache POI, a Java library for working with Microsoft Office documents, also provides functionality to work with XML and JSON data. This tutorial will guide you through the steps of working with XML and JSON files using Apache POI.

Example Code

Let's consider an example that demonstrates how to read data from an XML file and write data to a JSON file using Apache POI:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;

import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;

public class XMLJSONExample {
  public static void main(String[] args) {
    // Read data from XML file
    try (InputStream inputStream = new FileInputStream("data.xml")) {
      Workbook workbook = new XSSFWorkbook(inputStream);
      Sheet sheet = workbook.getSheetAt(0);

      JSONArray jsonArray = new JSONArray();

      for (Row row : sheet) {
        JSONObject jsonObject = new JSONObject();

        Cell keyCell = row.getCell(0);
        Cell valueCell = row.getCell(1);

        String key = keyCell.getStringCellValue();
        String value = valueCell.getStringCellValue();

        jsonObject.put(key, value);

      // Write data to JSON file
      try (Writer writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream("data.json"), StandardCharsets.UTF_8))) {
    } catch (IOException e) {

In this example, we read data from an Excel file and convert it into JSON format using Apache POI. Each row in the Excel sheet is represented as a JSON object, and all the objects are stored in a JSON array. The resulting JSON data is then written to a JSON file.

Steps to Work with XML and JSON

Follow these steps to work with XML and JSON using Apache POI:

  1. Import the necessary Apache POI and JSON-related classes and packages.
  2. Read the XML file using FileInputStream or any other input stream.
  3. Create an instance of the Workbook class, which represents the Excel workbook.
  4. Access the desired sheet within the workbook using getSheetAt() or getSheet() methods.
  5. Iterate through the rows and cells of the sheet and retrieve the data.
  6. Convert the data into the desired JSON format (objects, arrays, etc.).
  7. Write the JSON data to a file using FileWriter, BufferedWriter, or any other suitable writer.
  8. To work with XML, you can use Apache POI's XMLBeans or other XML processing libraries.
  9. Read the XML data using the appropriate APIs or libraries.
  10. Process the XML data as needed (e.g., extract values, create XML documents).
  11. Write the XML data to a file if required.

Common Mistakes

  • Not properly handling exceptions and error conditions when working with XML and JSON files.
  • Incorrectly mapping the XML or JSON data structure to the Excel sheet or vice versa.
  • Not closing the file streams or resources properly, leading to potential resource leaks.

Frequently Asked Questions (FAQs)

  1. Can Apache POI handle complex XML or JSON structures?

    Yes, Apache POI provides various APIs and methods to handle complex XML or JSON structures. You can traverse, extract, modify, and create XML or JSON elements and attributes using the available functionality.

  2. How can I convert an XML file to JSON using Apache POI?

    Apache POI is primarily focused on working with Excel files. To convert XML to JSON, you may need to use other libraries or APIs specifically designed for XML or JSON conversion, such as Jackson, Gson, or XMLBeans.

  3. Is it possible to generate XML or JSON from scratch using Apache POI?

    Apache POI is not primarily designed for generating XML or JSON from scratch. However, you can use other libraries or APIs like XMLBeans or Jackson to create XML or JSON structures and then write them to files using Apache POI.

  4. Can Apache POI handle large XML or JSON files efficiently?

    Apache POI is not specifically optimized for handling large XML or JSON files. If you are working with large files, it is recommended to use dedicated XML or JSON processing libraries that provide better performance and memory management.

  5. What are some alternatives to Apache POI for working with XML and JSON?

    Some popular alternatives for working with XML include JAXB, XStream, and DOM4J. For JSON, alternatives include Jackson, Gson, and JSON.simple. These libraries provide specialized features and better performance for XML and JSON processing.


In this tutorial, we explored how to work with XML and JSON using Apache POI. We saw an example code snippet that demonstrated how to read data from an XML file, convert it to JSON format, and write it to a JSON file. The step-by-step guide explained the process of working with XML and JSON files and provided insights into common mistakes. Additionally, frequently asked questions were addressed to provide further clarity. With Apache POI, you can easily handle XML and JSON data within your Java applications.