Export table as excel

Queries and Resources
#1

Every table offers a "download as CSV" button.
On mac I can just download and then open with Excel and the data looks correct.
But on windows this does not work.

I figured out that there is the JS function utils.exportData(table.data, 'filename', 'csv')

I figured out that instead of csv I can also put tsv, but this also doesn't work. Is it possible to specify Excel as a format?

#2

Hey there! Would the 'xlsx' data type (instead of 'csv') work for you (see option 1 in the screenshot)?

You can also run an event handler to download data as Excel (option 2). :blush:

3108×1438 453 KB

#3

Superb! That's exactly what I was looking for! The event handler option is even easier as I don't need to create a javascript query.

Is it documented somewhere? Because I was looking for it and couldn't find it.

#4

Very happy to hear that! 😊

We do have these docs on event handlers: https://docs.retool.com/docs/event-handlers

#5

Hi Victoria, What is the syntax to add the Sheet name to the .xlsx export as shown in Option 1 above?

#6

Hello :blush: By add the sheet name, do you mean set the exported file name? You would replace ‘filename’ in section 1 of my screenshot!

#7

Hi No I meant can you specify what the sheet would be called within the file. For example when you open a new Excel file workbook, the sheet name by default is Sheet1. Can we send an option to do this via the code above with an additional option? Also, the image above for Option 2 has "Sheet name" as an option. But how do you specify that in code?

#8

Ah, in code that's not as directly possible. You'd need to first specify that sheet name in a Google Sheets query by either typing it in directly, or dynamically! And then you can export the data from that query using the JS function.

Does this help get you any closer at all? Either way, let me know! :slightly_smiling_face:

1862×984 81.3 KB

#9

Okay, I am actually using Excel, not Google sheets (are they interchangeable?) Anyways, yes I do have the filenames, sheet names, source queries all ready to go, but just trying to figure out a way to export them all into an Excel object (one query result per Excel worksheet) and then when all done, download the Excel file with the pre specified name. My main issue I am trying to solve with this is I have my database in Snowflake and need to export some queries from there into a Single Excel fie so I can then load that single Excel file into a 3rd party program. Have the Retool tool completely built for this and it works excellently! EXCEPT for this very last piece of functionality.

Here's an example of my code for one of the queries and one of sheets of the file that I am trying to create and export (download).

            var wb = XLSX.utils.book_new();
        	wb.Props = {
                Title: textInputEnCompassFileName.value,
                Subject: 'Power Dispatch EnCompass Input File',
                Author: userNameFirst + ' ' + userNameLast,
                CreatedDate: new Date(year,month,day)
            };
        
            wb.SheetNames.push("BA");
            var ws_data = SELECT_BA.data;
            var ws = XLSX.utils.aoa_to_sheet(ws_data);
            wb.Sheets["BA"] = ws;
            var wbout = XLSX.write(wb, {bookType:'xlsx',  type: 'binary'})
            XLSX.utils.downloadFile(ws_data, textInputEnCompassFileName.value, '.xlsx')
#10

Thank you for sharing that! There is no native way to set the specific sheet name with JS in Retool, but you can add a sheet name inside of an event handler directly.

Regarding your specific query, is everything working except that very last line? And just to double check that last line, does your XLSX library can call a utils.openUrl function, or are you attempting to use the Retool function utils.openUrl?

https://docs.retool.com/docs/scripting-retool#utilsopenurlurl--newtab-boolean--true-forcereload-boolean--false-

606×784 53.3 KB

#11

Hi there,

Not sure if I should create another post for this, but it is related to the export table as excel.

I managed to export dummy data as excel successfully. However, when I try to open the downloaded Excel file, I have a prompt that says :

excel prompt
excel prompt1576×238 14.2 KB

I click Yes and everything is fine, but I feel like this could be fixed to give a seamless experience. :blush:

Is it a known issue on your side ?

#12

Downloaded data :

[{"animal": "duck", "sound": "quack"},
{"animal": "dog", "sound": "wooof"},
{"animal": "cat", "sound": "meow"}]

OS : Windows 11
Excel Version : Microsoft 365 MSO (Version 2212 Build 16.0.15928.20196) 64-bit

#13

Hey @Etienne_P! Creating a new post is always ideal, so we can link unique bug report tickets internally :slight_smile:

Would you mind creating a new post and then posting a link to that new thread here so I can follow up with you there?

Thank you in advance! :pray:

#14

Yeah sure,

Here's the link Problem when exporting data to Excel

#15

Perfect, thank you! See ya over there :slight_smile: