Add Multiple Sheets to Exported xlsx

Queries and Resources
1

Hi, I'm trying to export two tables (for now) into one .xlsx file. I tried firing two separate events but with the same filename (different sheet names). It just tried doing a bulk download. Is there a way to do this in the event handlers?

2

Hi @jocen :wave: thanks for reaching out with this!

Unfortunately, exporting data from multiple tables into a single Excel workbook with multiple sheets does not appear to be currently supported via Retool's utils.exportData method, nor the related event handlers.

That said, you could potentially consider importing the xlsx NPM package using Retool's support for preloading custom JavaScript code, then implementing the table-joining and Excel workbook creation/download logic within a JS query that could be called from within your app 😄

I hope that that's helpful, but please feel free to let us know if you have any other related questions!

1 Like
3

Hi,
I too faced this problem and tried to implement what you said, but I can't seem to get the file to download.

/* original data */
var data = [
	{"name":"John", "city": "Seattle"},
	{"name":"Mike", "city": "Los Angeles"},
	{"name":"Zach", "city": "New York"}
];


/* make the worksheet */
var ws = XLSX.utils.json_to_sheet(data);

/* add to workbook */
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "People");

/* generate an XLSX file */
XLSX.writeFile(wb, "sheetjs.xlsx");

If I put the code in a button click, I get the console outputs but no file created.
Likewise if I put it in a JS Query.

Any advice, please?

Thanks

Martin

4

I tried it out in a separate clean HTML file like:

<html>
<head>
<script lang="javascript" src="https://rawgit.com/SheetJS/js-xlsx/master/dist/xlsx.full.min.js"></script>
<script>

function loaded() {
	/* original data */
	var data = [
		{"name":"John", "city": "Seattle"},
		{"name":"Mike", "city": "Los Angeles"},
		{"name":"Zach", "city": "New York"}
	];


	/* make the worksheet */
	var ws = XLSX.utils.json_to_sheet(data);

	/* add to workbook */
	var wb = XLSX.utils.book_new();
	XLSX.utils.book_append_sheet(wb, ws, "People");

	/* generate an XLSX file */
	XLSX.writeFile(wb, "sheetjs.xlsx");
}

</script>
</head>
<body onload='loaded()'>
</body>
</html>

And that works fine, but just not through Retool.

Any ideas please?

5

Hello, I succeeded to download an Excel file with multiple sheets within a query using the utils.downloadFile function like this:

const data = [
  { name: "John", city: "Seattle" },
  { name: "Mike", city: "Los Angeles" },
  { name: "Zach", city: "New York" },
];
const sheet = XLSX.utils.json_to_sheet(data);

const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, sheet, "People1");
XLSX.utils.book_append_sheet(workbook, sheet, "People2");

const result = XLSX.write(workbook, { type: "base64" });

utils.downloadFile({ base64Binary: result }, "people", "xlsx");

Note that this requires that you have added the xlsx library in preloaded libraries, for example using the unpkg CDN

2 Likes
6

Finally been able to use this. Great stuff and thank you @aturiot!

1 Like
7

By the way for those that are interested, we ended up using ExcelJS instead of SheetJS because it has a nice API for styles and tables.

You can add it to the Script and Styles > Libraries: https://cdnjs.cloudflare.com/ajax/libs/exceljs/4.3.0/exceljs.js

And then use it for example like this in a JS query:

async function exportData() { 
  const workbook = new ExcelJS.Workbook();
  const sheet = workbook.addWorksheet('My Sheet');

  sheet.addTable({
    name: 'MyTable',
    ref: 'A1',
    style: { theme: 'TableStyleDark3', showRowStripes: true },
    columns: [{ name: 'Date' }, { name: 'Amount' }],
    rows: [
      [new Date('2019-07-20'), 70.10],
      [new Date('2019-07-21'), 70.60],
      [new Date('2019-07-22'), 70.10],
    ],
  });
 
  const buffer = await workbook.xlsx.writeBuffer()
  utils.downloadFile({ base64Binary: buffer.toString('base64')}, "file-name", "xlsx")
}
 
return exportData()
4 Likes
8

Hi, i want a sheet from json and and also with some styling and alignments most imp "autoSizeColumn". How can i get all this with ExcelJS

9

@Sanjay I think you can use this solution here to auto size the width and this one below for the height.

10

Is there a straightforward way to push a retool table into into ExcelJS? given the way they want the data for columns and rows formatted it seems remarkably complicated.

I'm not sure how to easily take table1.data and convert it into the columns and rows arrays that ExcelJS seems to want. But there must be a way if this is the preferred solution.

11

Hey @cfn!

Doing some testing and this is the quickest way I've found so far to convert the table data into ExcelJS format:

const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('My Sheet');
sheet.columns = _.map(table1.columns, (key) => ({key}));
sheet.addRows(table1.data);

Based on these docs it looks like each column needs to be assigned as an object with at least a key property. Which should be accomplished by taking the column names from table1.columns and assigning them to an object that look like {key: name} with the following mapper (key) => ({key}).

From there you can add rows formatted as an array of objects, as per these docs. Just remember the caveat for SQL query data!

You'll likely want to add some more formatting as well!
table_data_to_xlsx.json

1 Like