MySQL

To get the table list:

show tables;

To get the column list for a table:

show columns from <tableName>;

PostgreSQL

To get the table list:

SELECT table_name FROM information_schema.tables WHERE table_catalog ='<dbName>' and table_schema = 'public';

To get the column list for a table:

SELECT column_name FROM information_schema.columns where table_catalog = '<dbName>' and table_name = '<tableName>';

SQL Server

To get the table list:

SELECT TABLE_NAME FROM <dbName>.INFORMATION_SCHEMA.Tables;

To get the column list for a table:

SELECT COLUMN_NAME FROM <dbName>.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<tableName>';

On this example, we will add a smile image to the bottom of the PDF first page.

We start by loading the PDF we want to modify, and getting the reference for the first page:

    PdfReader reader = new PdfReader(srcPdf);
    PdfStamper stamper = new PdfStamper(reader, new FileOutputStream(destPdf));
    PdfContentByte content = stamper.getOverContent(1);

srcPdf is a String with the full path to the existing PDF file, while destPdf is the full path where the modified PDF file will be created.

Then, we load the image (imagePath is the full path for the imagem file):

Image image = Image.getInstance(imagePath);

// scale the image to 50px height
image.scaleAbsoluteHeight(50);
image.scaleAbsoluteWidth((image.getWidth() * 50) / image.getHeight());

Since the image dimensions are large, it's scaled to 50 pixels height before we add it to the PDF.

We then set the page coordinates where we want it. Be aware that the 0 value for the Y axis is the bottom of the page, not the top:

image.setAbsolutePosition(70, 140);

All we need to do now is add it to the page reference and close the stamper:

content.addImage(image);

stamper.close();

That's it!

The sample project with all the code, example PDF and image is available at github, here

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);

To enable JMX connections from remote hosts all that's needed is to add -Djava.rmi.server.hostname=<hostname> in the JVM Options of the instance of GlassFish, where <hostname> is the external hostname of the server where glassfish is running.