This is an old question but the 4D lansdcape has changed so there might be some new answers. What is the current thinking as the best way to generate an Excel spreadsheet from 4D? We are not talking about amending existing spreadsheets nor importing into 4D. CSV export will not be sufficient since we often need multi-page spreadsheets. 4D version 17, 18. All Windows and no 4D View Pro available.
Pluggers Excel Plugin
HTML can be used too, better than csv (styled text), but it won’t suit the multi pages need. It’s a pleasure to recommend Rob’s plugin as Armin and Olivier, it’s great.
Thank you for the quick responses. I too have heard good reports about Rob’s plugins but the company bosses would prefer not to use plugins. I was thinking more along the lines of using a web area or PHP.
I use xl-plugin for a while, and it always works, whatever the 4D versions it sees…
And it is not expensive, as you paid once as a developper, you then deploy as you wish.
It is a bit of work, but 4D 18 has all the tools you need to read and write Excel files directly.
if you want to help me work through it, we can probably adapt the google sheets library i’ve got going to office.
Without wanting to be rude, the bosses should change their mind, unless they accept the application to be slow and unstable if they have php in mind. I’ve used a php library a few years ago for a “one shot import” of hundreds of excel sheets in a 4d database: I ended up getting there with php but it was a stupid loss of time not to use the plugin. I’ll never use php for final users…
Other solutions: excel has a flat xml format, a simple xml file (“excel 2004 (xml)” in the “Save as” dialog). It can be a solution to produce xl files using process 4d tags + a template document, if the export structure is always the same.
If the document is opened into a view pro area you can use VP EXPORT DOCUMENT
My $0.02 based on my experiences:
Rob’s plugins are first rate. They just work and he provides great support. This will probably be the least time consuming for you personally.
If you can use v18 then 4D View Pro is a good choice but you have to spend time learning how to create what you need and so forth. You don’t need to buy a lot of licenses. I suppose you could just buy one and simply not allow any users access to it to allow the server to run it. In v17, however, this option has no value as View Pro is severely limited.
When you get into the PHP, XML, whatever solutions you have no, or very little, license costs but you will spend more time getting it to work and more time again when something changes. If you really want to dig into this look at using VBA or Powershell to interact with Excel to create the file.
If you are an in-house dev go for #3. It will be interesting and maybe even fun. Otherwise consider something that will resolve this quickly and move on.
On that note - you can still simply export a tab-delimited text file and stick a “.xls” extension on it. Excel squawks about the format the first time it opens but will open it. I happen to know that NetSuite is still using this for their ‘Excel’ downloads.
I have a component to write SpreadsheetML version 2003
It uses the plain 4D xml commands and it works well. SpreadsheetML is limited but it is simple…
I have used it to create Excel documents from scratch and also I use templates (Excel documents in resources) to help set all the properties.
It works with callbacks.
If there is some interest and if I can get some time, I could look into explaining how it works.
I fully agree with this. If you want, I can give you some generic methods to an export of a table selection and all fields.
I have the same to export an arrays listbox content.
It is easier to have a first start.
If your needs are simple columnar reporting like dumping a selection or group of arrays my talk for the 4D Summit this year contains a component that creates native xlsx files without plugins. It can also output quick reports to xlsx. Hope that’s helpful.
Thank you all for these valuable suggestions.
Is that without using 4D View Pro?
Yes, without using 4D View Pro. Excel files are essentially a zipped archive of XML files. You can read and write them directly in 4D 18 without any plugins.
I found this to be a useful guide to getting the format right: