Transform Microsoft Excel .xls (NOT .xlsx) with Mule 4

We need Mule 4.3 to transform an old MS Excel 2003 file to CSV format. We have no control over the source system so receiving the file in another format is not an option.

On the mule site here it states that they don't support the older format, and there also doesn't seem to be a workaround online:

> Only .xlsx files are supported (Excel 2007). .xls files are not supported by Mule.

However I'm wondering if we could do something like this:

1. Read the input as a 'blob' with no associated format
2. Immediately hand it off to a Mule 4 Java module
3. The Java module would make use of a java plugin for transformation
4. Pass the transformed payload back to dataweave as CSV

Is this at all possible?

Perhaps you can suggest another solution?

This was achieved as proposed in the question.

The finished sample flow looks like this:

Add the Mule Java Module dependency, and Apache POI for handling the Microsoft xls file:

    <dependency>
		<groupId>org.mule.module</groupId>
	    <artifactId>mule-java-module</artifactId>
	    <version>1.2.5</version>
	    <classifier>mule-plugin</classifier>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi</artifactId>
	    <version>4.1.2</version>
	</dependency>

The file was then read by a On New or Updated file reader with no mime type or encoding configured. This is because we are trying to avoid Mule knowing anything about the file type. The file could be received in any way of course - e.g. over FTP.

At this point the payload just looks like a pile of gobbledygook (the raw xls file).

The source (file reader) is then immediately followed by a transform converting the payload to plain text and base64 encoding it:

%dw 2.0
import * from dw::core::Binaries
output text/plain
---
toBase64(payload as Binary)

This is done because initially we had a lot of trouble passing the raw file to Java, with issues like (however if you have a better solution let me know!):

• Cannot convert so and so to object
• invalid end of file

Which make perfect sense because Java doesn't know what we are passing it and so how would it know to transform it to a specific object type.

Next we instantiate the Java class with Mule's Java 'New' event. The class itself looks like:

public class Transformer {

    public String transform(String file) {
	    String cellValue = "";
	    try {
		    // Decode base64:
		    byte[] decoded = Base64.getDecoder().decode(file);
		    // Steam decoded file to an input stream (as if we were reading it from disk)
		    InputStream targetStream = new ByteArrayInputStream(decoded);
		    // Create the .xls Apache POI object
		    HSSFWorkbook workbook = new HSSFWorkbook(targetStream);
		    // Process the rows/cells etc...
		    HSSFSheet sheet = workbook.getSheetAt(0);
		    // For example...
		    cellValue = sheet.getRow(0).getCell(0).getStringCellValue();
		
		
	    } catch (Exception e) {
		    System.out.println("FAIL" + e.getMessage());
	    }
	    return cellValue;
    }
}

Next we pass the payload to this method with Mule's Java Invoke event with the following configuration:

• Instance: vars.instanceName
• Args: {arg0: payload as String}
• Class: package and class name of the Java class
• Method: the method to invoke, ours was transform(java.lang.String)

It is passed as a String because Java knows how to handle the String object, and basically we are hiding the fact that it is a file.

From the there Java does the following (see the above Java file):

• Decodes the file:
• Reads it to an InputStream:
• Creates the Apache POI class:
• performs transformation:

In the sample above we are just returning the value of one cell as a String to Mule. But you can also create a CSV type String such as a,b,c\nd,e,f (\n for new line)and then transform it to a CSV with a Transform event:

%dw 2.0
output application/java
---
write( (read(payload,"application/csv",{"header" : false})),"application/csv",{"quoteValues" : "false","header" : false})

which would output a csv file like this:

a,b,c
d,e,f

And there you have it. Mule can now process a Microsoft Excel xls file.

Yes, You can pass an InputStream to Java module method invocation and use for example Apache POI (capable of reading xls and xlsx as well) for Your stream to csv conversion.