[Bug]: Excel File Uploaded Via FilePicker Not Converted to Array of JSON Objects When Data Format Set to Array of Objects

[ame-appsmith]

Is there an existing issue for this?

• [X] I have searched the existing issues

Description

When uploading an XLS(X) file via the FilePicker widget and setting the Data format property to Array of Objects, the data property of the sheet is not converted to an array of JSON objects as it happens for CSV files, where users are able to access the data directly or display it in tables without using helper JS functions for conversion.

Workaround: At this juncture, we can use a JS function that converts the data from the excel format to an array of JSON objects like this:

export default {
transformXLStoJSON () {
    let data = FilePicker1.files[0].data[0].data;

const columns = data[0];

let jsonData = [];

for (let i = 1; i < data.length; i++) {
jsonData.push({})
for (let j = 0; j < data.length; j++) {
const columnName = columns[j]
jsonData[i-1][columnName] = data[i][j];
}
}
return jsonData;
}
}

Steps To Reproduce

1. Add a FilePicker widget to the canvas.
2. Set the Data format property to Array of Objects.
3. Upload an XLS(X) file using the FilePicker widget.
4. Try to display it in a Table widget and note that you have to use a JS function to convert the data to an array of JSON objects.
5. Try the same thing with a CSV file and note that the conversion is done automatically and it gets displayed directly in the Table widget.

Public Sample App

No response

Environment

Production

Issue video log

No response

Version

v1.9.37.1

[dilippitchika]

This issue cannot be confidently fixed without creating a new version of the widget, which will be done in the new widgets being built for the platform via the Widget Design System.

Fixing this might introduce regression for users already using the filepicker data by formatting the data.

[somangshu]

This will be fixed under the WDS project cc @jsartisan

[khushRD]

This will be fixed under the WDS project cc @jsartisan

What is the WDS project?

[Nikhil-Nandagopal]

@khushRD it's a project we have internally to improve our existing widgets. You can try parsing an excel on the client by finding a 3rd party JS library but I personally haven't tried whether that would work

[khushRD]

I tried parsing it using XLSX and FileReader and it worked