Jump to content
Sign in to follow this  
Genelia

Export data in excel in multiple worksheet

Recommended Posts

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.

 

 

 

 

Share this post


Link to post
Share on other sites

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 by rwoods

Share this post


Link to post
Share on other sites

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

 

 

Share this post


Link to post
Share on other sites

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?

 

 

Share this post


Link to post
Share on other sites

@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

Share this post


Link to post
Share on other sites

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 by Richard Stoff
Typo

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.