Need a special offer?Find out if your project fits.
+

Export To Plain Excel

Answered
Scott Oliver asked on June 26, 2020

Hello,

We are having some difficulties exporting to "plain" with Excel.

We can get the array buffer out as a variable, but we are unable to successfully transfer this to a string and base encode it. Where it will be transferred to another software platform where we look to "build" the XLSX file.

When we attempt to do so, with a number of different encoding functions which we have found, the end result is the XLSX file not being recognisable/corrupt.
Is there any other method you have at your disposal for us to extract the data and publish an Excel file. We unfortunately cannot do this locally, and with the lack of support to JS Cloud, the plain option is really our ideal output.

Do you have any successful JS examples of how we can use the array buffer and convert this back into an Excel file, or convert it to a base encoded string?
 

9 answers

Public
Mykhailo Halaida Mykhailo Halaida Flexmonster June 30, 2020

Hi Scott,
 
Thank you for writing to us.
 
We would suggest trying the following code line inside your exportTo() API call callback handler:
 

 const base64String = btoa(String.fromCharCode(...result.data));

 
The code above returns a Base64 string representing the exported Excel file – we've tested this on our side and everything seems to be working correctly. 
 
Please let us know if this helps.
 
Best regards,
Mykhailo

Public
Scott Oliver June 30, 2020

Hi Mykhailo,
 
Thanks again for your help. Unfortunately I had difficulty opening the Excel file.
 
My exportTo() looks like this:
 

function exportData()
{
var params = {destinationType: "plain"} ;
flexmonster.exportTo('excel', params, function(result)
{
const file = btoa(String.fromCharCode(...result.data));
var script = 'Pivot' ;
FileMaker.PerformScript(script, file);
})
}

 
The "FileMaker.PerformScript" step is where we transfer the base encoded string over to our third party software. In the third party software we Base 64 Decode the string and generate an Excel file.
 
The base encoded result I am receiving I have attached for you. To reiterate my point, I'd like to a base encoded string, base decode the same string and have a working XSLX file.

Public
Mykhailo Halaida Mykhailo Halaida Flexmonster July 2, 2020

Hi Scott,
 
Thank you for your fast response.
 
In this case, we can suggest using the SheetJS js-xlsx npm module for base64 encoding and building a valid Excel file with the pivot table contents.
 
Feel free to check out the following JSFiddle sample demonstrating how to use the xlsx package in combination with Flexmonster: https://jsfiddle.net/flexmonster/xo698bLm/
 
Please let us know if you find this helpful.
 
Regards,
Mykhailo

Public
Scott Oliver July 3, 2020

Hi Mykhailo,
 
Thank you for your input. I was able to do a little bit of research and come up with a solution:
 

	flexmonster.exportTo('excel', params, function(result)
	{
		var base64String = btoa(String.fromCharCode(...result.data));
		var wb = XLSX.read(base64String, {type:'base64'});
		var wbout = XLSX.write(wb, {bookType:'xlsx', bookSST:true, type:'base64'});
	} )

 
There is an issue however with large datasets. I'm unable to successfully create the Base 64 String variable in your first suggested line of code. Any suggestions?
 
Also, the ellipsis (...) in your code:
 

btoa(String.fromCharCode(...result.data))

 
Appears to break our FM table (nothing appears), when running Internet Explorer (IE11) on a Windows Server box as the client. Works fine in Safari on a Mac client.

Public
Mykhailo Halaida Mykhailo Halaida Flexmonster July 3, 2020

Hi Scott,
 
Thank you for your fast response.
 
We didn't manage to reproduce the issue with btoa() method not working on large data sets. Do you think you could provide more details on this, such as browser console screenshots, the size of your data, a sample dataset etc.?
 
This information will help us with our further investigation.
 
Addressing the second issue, in order to achieve compatibility with IE11, the spread operator (...) can be replaced with the apply() method of the fromCharCode() function:
 

var base64String = btoa(String.fromCharCode.apply(this, result.data));

 
Unlike the spread operator, the apply() method is available in IE11 while also providing the same functionality in this situation.
 
Looking forward to your response.
 
Regards,
Mykhailo

Public
Scott Oliver July 7, 2020

Hey Mykhailo,
 
I will test out your proposed solution. Attached is a HTML file which is our working report that we produce inside of a web viewer that is integrated into the software package we develop from. The web viewer uses the computers native browser (IE for Windows and Safari for macOS).
 
There are two buttons, one which exports and one which expands the columns and rows. Please note that once the dataset is loaded, the export works perfectly fine. If you expand however (so that all data is shown), the export function stops working.

Attachments:
Export Excel Test.html

Public
Mykhailo Halaida Mykhailo Halaida Flexmonster July 7, 2020

Hi Scott,
 
Thank you for providing us with the corresponding HTML file. We've tested it on our side and everything seems to be working fine: that is, export works as expected both before and after the pivot table is expanded. Please see the video illustrating this (tested on IE11, the earlier mentioned spread operator fix had to be done).
 
Please note that this behavior might actually not be related to Flexmonster – the machine running the page with the pivot table might simply not be powerful enough.
 
Being a client-side component, Flexmonster relies on resources available to the browser, which affects the loading time and the maximum size of the data that can be handled on every particular machine. This means that the client machine’s RAM determines how much data can be loaded at once, and CPU capabilities affect how much time is spent on the data analysis.
 
Please let us know if you find our response helpful.
 
Best regards,
Mykhailo

Public
Scott Oliver July 8, 2020

Hi Mykhailo,
 
I've done a little further testing, and provided you with the outcomes below. The function works flawlessly when the tables haven't been expanded on all platforms.
 

  • Windows
    • Chrome: Working as expected
    • IE 11: Working as expected
    • IE 11 via FileMaker:
      • Mozilla/5.0 (Windows NT 6.2; Win64; x64; Trident/7.0; rv:11.0): Fails to complete the string from char code step
  • Mac
    • Chrome: Working as expected
    • Safari: Fails to complete the string from char code step
    • Safari via FileMaker: Fails to complete the string from char code step
      • Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5; AppleWebKit/605.1.15; Safari/601.6.17): Fails to complete the string from char code step

 
I'm identifying that it is falling over here, by alerts after each line of code (attached is the file I am using for my testing).
 
If I use a different pivot table, one with fewer columns, and repeat the same steps (expanding all columns before export), it works on all of the platforms noted above. So I believe there is a clear link between slice size and the success of string from char code function.

Public
Mykhailo Halaida Mykhailo Halaida Flexmonster July 10, 2020

Hi Scott,
 
After some further testing, we've actually managed to reproduce something similar to what you've mentioned when using Safari on macOS – more specifically, a "Maximum call stack size exceeded error" error appears when trying to execute the btoa() function. 
 
Nevertheless, there is really not much we can do here since this issue is rather browser-specific and is not linked to Flexmonster. Using the btoa() method is simply one of the possible solutions in this situation – there might be a lot of other different ways of achieving this, so we would suggest researching this a little bit to see if there is a better solution for your particular use case.
 
In case you decide to go through with the btoa() option regardless, it might probably be a good idea to first send the array of UTF-8 code units to the server-side and perform the base64 encoding there – this way, the costly operation will be performed on the server ensuring that the browser's resources are not overloaded.  
 
Please let us know if you have any other questions we can help you with.
 
Best regards,
Mykhailo

Please login or Register to Submit Answer