How to Generate Multiple Worksheets in MuleSoft (The "Multi-Tab" Excel Solution)

Let’s talk about data transfer. In the integration world, we often rely on the trusty CSV (Comma Separated Values) format. It is simple, text-based, and creates clean tables where values are separated by commas. You see them everywhere—spreadsheets, databases, and web apps love them because they are lightweight and easy to read.

The Problem: The Single-Sheet Limitation

If you have ever tried to create a CSV manually using Excel, Google Sheets, or a text editor like Notepad, you have likely hit a specific wall. While these tools make creating a file easy, standard CSV files have a major limitation: they can only save a single worksheet.

If your business requirement creates complex data that needs to be organized into separate tabs (worksheets) within a single file, a standard CSV simply won't cut it. It creates one flat table, and that's it. To get around this, we need to upgrade our format.

The MuleSoft Solution: DataWeave and XLSX

So, how do we solve this? If you are using MuleSoft and need to output a file that contains multiple tabs of data, we have to get a little clever with our DataWeave code.

To achieve those multiple tabs, we switch from CSV to the application/xlsx output format. Since Excel files (XLSX) support multiple tabs naturally, we just need to structure our payload in a specific way so MuleSoft knows which data goes on which tab.

1. The Data Structure

To tell MuleSoft, "Hey, put this data on Sheet 1 and that data on Sheet 2," you need to organize your payload as an Object of Arrays.

• The Key becomes the Sheet Name.
• The Value (which must be an Array) becomes the Rows of data in that sheet.

The Conceptual Format:

{
  SheetName1: ArrayOfData,
  SheetName2: ArrayOfData
}

2. The DataWeave Script

To make this magic happen, we need to set our output type to Excel and enable headers. This ensures your keys (like "Id" and "Name") automatically become the column headers in the spreadsheet row 1.

Here is the exact DataWeave transformation you need to generate a file with two distinct tabs ("Sheet1" and "Sheet2"):

%dw 2.0
output application/xlsx header=true
---
{
  Sheet1: [
    {
      Id: 12312,
      Name: "George Wong"
    },
    {
      Id: 45688,
      Name: "Lucas Khan"
    }
  ],
  Sheet2: [
    {
      Id: 123,
      Name: "George"
    },
    {
      Id: 456,
      Name: "Lucas"
    }
  ]
}

3. Implementation Steps

Now that you have the logic, where does it go? You need to place the script logic inside the File Write Connector, specifically within the Content section of the connector configuration.

Dynamic Payload Approach:
In a real-world scenario, you usually won't hardcode the names like we did above. If your payload coming from a previous step (like a Database Select or an API call) is already formatted correctly (an object containing arrays), your script in the File Write Connector acts as a simple pass-through.

Use this code in your Write Connector:

%dw 2.0
output application/xlsx header=true
---
payload

Critical Note: Ensure your incoming payload matches the accepted format (Object with keys as sheet names) before feeding it into this final write step! If the payload is just an Array, DataWeave won't know how to name the sheet.

Conclusion

Generating multi-tab Excel files in MuleSoft is all about structure. By organizing your data into an Object where keys represent sheet names and values represent rows, you can utilize the full power of the application/xlsx format. This is a great way to keep complex reports organized in a single file for your end-users.