The XML file is located at https://github.com/jbaysolutions/xml-to-excel/blob/master/Publication1.xml?raw=true.

The XML file's main nodes are "Substances", each one has a few properties "Name", "entry_force", "directive" and a list of "Product". We're going to create an excel row for each Product. Each row will also have the Product parent Substance details.

Below is a sample of the XML structure:

<?xml version="1.0" encoding="UTF-8"?>
<Pesticides>
<Header>
    <Creation_Date>09/07/2015 13:45</Creation_Date>
</Header>
<Substances>
    <Name>Garlic extract (++)</Name>
    <entry_force>01/09/2008</entry_force>
    <directive>Reg. (EC) No 839/2008</directive>
    <Product>
        <Product_name>FRUITS, FRESH or FROZEN; TREE NUTS</Product_name>
        <Product_code>0100000</Product_code>
        <MRL/>
        <ApplicationDate>01/09/2008</ApplicationDate>
    </Product>
    <Product>
        <Product_name>Oranges (Bergamots, Bitter oranges/sour oranges, Blood oranges, Cara caras, Chinottos,
            Trifoliate oranges, Other hybrids of Citrus sinensis, not elsewhere mentioned,)
        </Product_name>
        <Product_code>0110020</Product_code>
        <MRL/>
        <ApplicationDate>01/09/2008</ApplicationDate>
    </Product>
    <Product>
        <Product_name>Lemons (Buddha's hands/Buddha's fingers, Citrons,)</Product_name>
        <Product_code>0110030</Product_code>
        <MRL/>
        <ApplicationDate>01/09/2008</ApplicationDate>
    </Product>
    <Product>
        <Product_name>Limes (Indian sweet limes/Palestine sweet limes, Kaffir limes, Sweet limes/mosambis, Tahiti
            limes,)
        </Product_name>
        <Product_code>0110040</Product_code>
        <MRL/>
        <ApplicationDate>01/09/2008</ApplicationDate>
    </Product>
</Substances>
<Substances>
(...)
</Substances>

As usual, we use Apache POI, to create the excel file.

You can get the sample project used in this post at GitHub.

Downloading the file

We start by downloading the file from it's original URL location:

File xmlFile = File.createTempFile("substances", "tmp");
String xmlFileUrl = "http://ec.europa.eu/food/plant/pesticides/eu-pesticides-database/public/?event=Execute.DownLoadXML&id=1";
URL url = new URL(xmlFileUrl);
System.out.println("downloading file from " + xmlFileUrl + " ...");
FileUtils.copyURLToFile(url, xmlFile);
System.out.println("downloading finished, parsing...");

Preparing the Excel file

To create the Excel file where we're writing, we start by creating a new workbook, an empty sheet and writing the first line with the column headers:

workbook = new XSSFWorkbook();

CellStyle style = workbook.createCellStyle();
Font boldFont = workbook.createFont();
boldFont.setBold(true);
style.setFont(boldFont);
style.setAlignment(CellStyle.ALIGN_CENTER);

Sheet sheet = workbook.createSheet();
rowNum = 0;
Row row = sheet.createRow(rowNum++);
Cell cell = row.createCell(SUBSTANCE_NAME_COLUMN);
cell.setCellValue("Substance name");
cell.setCellStyle(style);

cell = row.createCell(SUBSTANCE_ENTRY_FORCE_COLUMN);
cell.setCellValue("Substance entry_force");
cell.setCellStyle(style);

cell = row.createCell(SUBSTANCE_DIRECTIVE_COLUMN);
cell.setCellValue("Substance directive");
cell.setCellStyle(style);

cell = row.createCell(PRODUCT_NAME_COLUMN);
cell.setCellValue("Product name");
cell.setCellStyle(style);

cell = row.createCell(PRODUCT_CODE_COLUMN);
cell.setCellValue("Product code");
cell.setCellStyle(style);

cell = row.createCell(PRODUCT_MRL_COLUMN);
cell.setCellValue("MRL");
cell.setCellStyle(style);

cell = row.createCell(APPLICATION_DATE_COLUMN);
cell.setCellValue("Application Date");
cell.setCellStyle(style);

Parsing

For this sample, the XML file is parsed using DOM.

We get the reference to the excel file sheet:

Sheet sheet = workbook.getSheetAt(0);

We start by loading the XML document using DOM and getting the Substances node list:

DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
Document doc = dBuilder.parse(xmlFile);

NodeList nList = doc.getElementsByTagName("Substances");

Then we iterate through the Substances list and get the Substance properties:

for (int i = 0; i < nList.getLength(); i++) {
    System.out.println("Processing element " + (i+1) + "/" + nList.getLength());
    Node node = nList.item(i);
    if (node.getNodeType() == Node.ELEMENT_NODE) {
        Element element = (Element) node;
        String substanceName = element.getElementsByTagName("Name").item(0).getTextContent();
        String entryForce = element.getElementsByTagName("entry_force").item(0).getTextContent();
        String directive = element.getElementsByTagName("directive").item(0).getTextContent();

        NodeList prods = element.getElementsByTagName("Product");

When we get to the Product element, we get it as a NodeList and iterate it to get it's details:

for (int j = 0; j < prods.getLength(); j++) {
    Node prod = prods.item(j);
    if (prod.getNodeType() == Node.ELEMENT_NODE) {
        Element product = (Element) prod;
        String prodName = product.getElementsByTagName("Product_name").item(0).getTextContent();
        String prodCode = product.getElementsByTagName("Product_code").item(0).getTextContent();
        String lmr = product.getElementsByTagName("MRL").item(0).getTextContent();
        String applicationDate = product.getElementsByTagName("ApplicationDate").item(0).getTextContent();

Now that we have all the details we want to write on the excel file, we create a row with all the details:

Row row = sheet.createRow(rowNum++);
Cell cell = row.createCell(SUBSTANCE_NAME_COLUMN);
cell.setCellValue(substanceName);

cell = row.createCell(SUBSTANCE_ENTRY_FORCE_COLUMN);
cell.setCellValue(entryForce);

cell = row.createCell(SUBSTANCE_DIRECTIVE_COLUMN);
cell.setCellValue(directive);

cell = row.createCell(PRODUCT_NAME_COLUMN);
cell.setCellValue(prodName);

cell = row.createCell(PRODUCT_CODE_COLUMN);
cell.setCellValue(prodCode);

cell = row.createCell(PRODUCT_MRL_COLUMN);
cell.setCellValue(lmr);

cell = row.createCell(APPLICATION_DATE_COLUMN);
cell.setCellValue(applicationDate);

When all the elements are written, we write the excel to the filesystem:

FileOutputStream fileOut = new FileOutputStream("C:/Temp/Excel-Out.xlsx");
workbook.write(fileOut);
workbook.close();
fileOut.close();

Finally, we delete the downloaded XML file:

if (xmlFile.exists()) {
    System.out.println("delete file-> " + xmlFile.getAbsolutePath());
    if (!xmlFile.delete()) {
        System.out.println("file '" + xmlFile.getAbsolutePath() + "' was not deleted!");
    }
}

Conclusion

The sample project used in this post at GitHub has a main class XmlToExcelConverter to download, parse the file and create the excel file.

Feel free to copy and adapt the code to read other XML files!
Hope it helped anyone having the same issues as us!

References

DOM

DOM Tutorial

Apache POI

Usually we parse excel files using Apache POI, but it has no support for Excel spreadsheet XML. The solution was to create our own parser.

For this specific case, we will cover the process of obtaining the substance name (column A), ADI value (column P) and ARfD value (column S).

You can get the sample project used in this post at GitHub.

Downloading the file

This class was to be used by a timer that runs once a day. Starts by downloading the file:

File file = File.createTempFile("substances", "tmp");

String excelFileUrl = "http://ec.europa.eu/sanco_pesticides/public/?event=activesubstance.exportList";
URL url = new URL(excelFileUrl);
System.out.println("downloading file from " + excelFileUrl + " ...");
FileUtils.copyURLToFile(url, file);
System.out.println("downloading finished, parsing...");

Parsing

Since the file format is XML, it can be parsed using SAX.

Since this file has only one sheet, we are keeping the parser simple, so we will only need to find the sheet rows and cells. We declare a class to represent the row:

class XmlRow {
    ArrayList<String> cellList = new ArrayList<>();

    @Override
    public String toString() {
        return cellList.toString();
    }
}

And a SAX handler to load the rows and cell ("Data" in the XML) contents:

class SAXHandler extends DefaultHandler {

    List<XmlRow> xmlRowList = new ArrayList<>();
    XmlRow xmlRow = null;
    String content = null;

    @Override
    //Triggered when the start of tag is found.
    public void startElement(String uri, String localName, String qName, Attributes attributes)
            throws SAXException {
        switch (qName) {
            //Create a new Row object when the start tag is found
            case "Row":
                xmlRow = new XmlRow();
                break;
        }
    }

    @Override
    public void endElement(String uri, String localName,
                           String qName) throws SAXException {
        switch (qName) {
            case "Row":
                xmlRowList.add(xmlRow);
                break;
            case "Data":
                xmlRow.cellList.add(content);
                break;
        }
    }

    @Override
    public void characters(char[] ch, int start, int length)
            throws SAXException {
        content = String.copyValueOf(ch, start, length).trim();
    }
}

Once the file is downloaded, it's loaded to a string to be supplied to the SAX Parser:

String fileContent = IOUtils.toString(new FileInputStream(file));

SAXParserFactory parserFactor = SAXParserFactory.newInstance();
SAXParser parser = parserFactor.newSAXParser();
SAXHandler handler = new SAXHandler();

ByteArrayInputStream bis = new ByteArrayInputStream(fileContent.getBytes());

parser.parse(bis, handler);

When we try and run this the first time, and exception occurs:

Exception in thread "main" org.xml.sax.SAXParseException; lineNumber: 2; columnNumber: 14; The processing instruction target matching "[xX][mM][lL]" is not allowed.

This happens because some blank space or other visible content exists before the declaration. For this file, we have to remove the first 2 lines, so it can be parsed:

removeLineFromFile(file.getAbsolutePath(), 1, 2);

String fileContent = IOUtils.toString(new FileInputStream(file));

SAXParserFactory parserFactor = SAXParserFactory.newInstance();
SAXParser parser = parserFactor.newSAXParser();
SAXHandler handler = new SAXHandler();

ByteArrayInputStream bis = new ByteArrayInputStream(fileContent.getBytes());

parser.parse(bis, handler);

The removeLineFromFile() method can be found on the sample project source.

When we try to parse it again, another exception is thrown:

Exception in thread "main" org.xml.sax.SAXParseException; lineNumber: 1313; columnNumber: 70; The entity "nbsp" was referenced, but not declared.

This happens because the entity   isn't one of XML's predefined entities. To fix it, we have to prepend this to the file before parsing it:

<?xml version="1.0"?>
<!DOCTYPE some_name [ 
    <!ENTITY nbsp "&#160;"> 
    <!ENTITY acute "&#180;">
]>

The ´ entity was also found on the file, so we added it as well.

There was also the text "JECFA&ECCO" somewhere in a cell which would cause the parser to throw another exception.

The code now looks like this:

removeLineFromFile(file.getAbsolutePath(), 1, 2);

String fileContent = IOUtils.toString(new FileInputStream(file));
fileContent = fileContent.replaceAll("&ECCO", "&ECCO");
fileContent = "<?xml version=\"1.0\"?>\n" +
        "<!DOCTYPE some_name [ \n" +
        "<!ENTITY nbsp \"&#160;\"> \n" +
        "<!ENTITY acute \"&#180;\"> \n" +
        "]>" + fileContent;


SAXParserFactory parserFactor = SAXParserFactory.newInstance();
SAXParser parser = parserFactor.newSAXParser();
SAXHandler handler = new SAXHandler();

ByteArrayInputStream bis = new ByteArrayInputStream(fileContent.getBytes());

parser.parse(bis, handler);

We now have a row list, where each row has a cell list. We are going to use this to get our values:

//Parsing the substances list obtained from XML
int count = 0;
for (XmlRow subsRow : handler.xmlRowList) {
    if (subsRow.cellList.size() > 19) {
        String substance = subsRow.cellList.get(0);
        if (substance.equals("Substance")) {
            continue;
        }

        count++;
        String adi = subsRow.cellList.get(15); // column P
        String arfd = subsRow.cellList.get(18); // column S

        System.out.println("Substance name='" + substance + "', ADI='" + adi + "', ARfD='" + arfd + "'");
    }
}

In this example we only care for the substance rows, so we ignore any row that has less than 19 cells. On our project class we use these values to update a DB table, but for simplicity here we are only printing them.

If our goal was to create a regular Excel file, we could change the code to:

Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet();

//Converts all rows to POI rows 
int rowCount = 0;
for (XmlRow subsRow : handler.xmlRowList) {
    Row row = sheet.createRow(rowCount);
    int cellCount = 0;
    for (String cellValue : subsRow.cellList) {
        Cell cell = row.createCell(cellCount);
        cell.setCellValue(cellValue);
        cellCount++;
    }
    rowCount++;
}

String fileOutPath = "/tmp/fileOut.xls";
FileOutputStream fout = new FileOutputStream(fileOutPath);
workbook.write(fout);
workbook.close();
fout.close();

As you can see, it can be easily modified depending on the purpose!

Finally, we delete the downloaded file:

if (file.exists()) {
    System.out.println("delete file-> " + file.getAbsolutePath());
    if (!file.delete()) {
        System.out.println("file '" + file.getAbsolutePath() + "' was not deleted!");
    }
}
//System.out.println(result);
System.out.println("getAndParseFile finished, processed " + count + " substances!");

Conclusion

For the purpose of reading data from an Excel spreadsheet XML file, it's relatively simple to do it.

The sample project used in this post at GitHub has two main classes. ExcelXmlReader to download, parse the file and print the values to the console and the ExcelXmlConverter class, to download, parse the file and converts it to OOXML file format.

The code can be modified to read multiple sheets or even the styles if necessary. It just wasn't needed for our use case.

Hope it helped anyone having the same issues as us!

References

Excel spreadsheet XML

SAX

Apache POI

I found myself spending a few hours searching for a way to translate the indexed column numbers that POI uses to the column letters I needed in those formulas.

It turns out the solution is quite simple, you just have to use org.apache.poi.hssf.util.CellReference:

String columnLetter = CellReference.convertNumToColString(columnNumber);