Export data in excel in multiple worksheet

[Genelia]

Hi To All,

 

During export records in excel format, we only allows to save data in single worksheet. MY quesetion is that

 

"Is there any possibility to save data in excel in multiple tab(worksheet) for single excel?"  actually i have 3 different format of data in single report and i have to export it and when i am exporting in single worksheet, it is saving the data but format is disturbing for other 2 format design as i can only export in 1 specific design.

 

Thanks in Advance,

 

I tried out lots of R & D but not found any meaningful  reply.

 

 

 

 

[rwoods]

Hi Genelia

This is tricky area. FileMaker will only export as a single Excel sheet on a spreadsheet, and you have no control over the formatting (i.e. all the columns are narrow, headings are restricted to the names of your fields) etc etc. Plus you can't include any nice formatting, such as colours, bold text, spacing etc. FileMakers features are really just a raw data export, and the formatting has to happen elsewhere.

If you want more control over the Excel output then we have found the best way is to generate an HTML file within FileMaker with your data formatted exactly how you want it, and then use a web API to convert the HTML file to Excel. That way you get a file formatted exactly as you wish.

You will need to see if they can also do two or more sheets in a document, we've never tried that.

The API we use is https://grabz.it/htmltabletofile.aspx . There is a free trial and free tier.

 

You could also look at https://docraptor.com/how-it-works (another API) or https://www.coolutils.com/HTML-XLS-CommandLine which is a Windows command line interface.

Edited November 7, 2017 by rwoods

[Mike Duncan]

I have a sample file you can get for free that uses javascript to output an excel file with multiple sheets. You can get it here:

https://github.com/SoliantMike/FM-js_ExcelSheets

It uses a javascript library and is all open, so you can modify to your own needs.

Mike

[Genelia]

Thanks for your solutions. I am absolutely going to try Mike idea. Thank you very much guys.

 

 

[Genelia]

Hi  Mike ,

Thanks for your solutions. I have tried this and its working great!!!!!. Thank you very much guys. One small query " In script code for text type we are sending data in Quote function (Quote(field name text) and for number it is working without using Quote like just (field name number ) but for date type how can i pass date data  as when i am using with QUote it is taking date as number and when using without Quote it is converting it number?

 

Below example taking company  as text  and  Zip as number:

$sheet.data & ",[" & Quote ( Companies::Company ) & "," &  ( Companies::Zip ) & "]"

 

For Date type how to set?

 

 

[AD34]

@Mike Duncan

I've downloaded your example and it works great, thanks. My question is how/what do I modify to change the number of worksheets being created and where do I set the Excel Filename? I've successfully changed the worksheet names in the FileMaker Script but I'd like to add additional worksheets.

Any help you can provide would be appreciated.

Thanks

[Richard Stoff]

Just come across this thread and thank you to Mike Duncan for the sample. I too struggled at first to add additional (or rename existing) sheets. 

Take a look at row 100 in the Save_XLSX script, add (or amend) as necessary there and also mirror the changes in the js field of the Resources table.

Got it to do a six worksheet book very nicely - anyone got any help with formatting (auto column width specifically!)?

R.

Edited November 14, 2018 by Richard Stoff
Typo

[toolhead]

On 11/14/2018 at 9:37 PM, Richard Stoff said:

Just come across this thread and thank you to Mike Duncan for the sample. I too struggled at first to add additional (or rename existing) sheets. 

Take a look at row 100 in the Save_XLSX script, add (or amend) as necessary there and also mirror the changes in the js field of the Resources table.

Got it to do a six worksheet book very nicely - anyone got any help with formatting (auto column width specifically!)?

R.

found your note, what did you amend update to achieve six, sure i've missed somthing
any infor greawtly appreicated