headers
rooftop99 | 9 Oct 05:35

Automatically creating Excel Docs from within 4D 2004.x

Hi All,

I need to programatically create some formated Excel spreadsheets from  
data in a 4D application.  At first it seemed very doable by using the  
Office Object Library but unless I am missing something it appears  
this approach require manual input each time the report is created  
(correct??)

Overview:  1). pull a selection of records via a query.  2). "Export"  
them in some fashion so as to end up with a formated Excel document.   
3). Attach the doc to an email and send it to the customer.

Step 2 is where I need help.  Thanks in advance!!

These spreadsheets will be created off a 4D 2004.7 server/client app  
running on a Mac.

Best!
Kirk
**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
(Continue reading)

Permalink | Reply |
headers
Mike Erickson | 9 Oct 05:46

Re: Automatically creating Excel Docs from within 4D 2004.x

Kirk

I dont know if this will suffice, but I do this same thing by  
exporting a tab delimited file and saving it as <filename>.xls and the  
setting the creator code to that of an Excel document (dont know it  
off the top of my head)

This way, if you "launch" the document, it will open it within excel  
automatically.

Regards

Mike Erickson
Automated Solutions Group	

On Oct 8, 2008, at 8:36 PM, rooftop99@... wrote:

> I need to programatically create some formated Excel spreadsheets  
> from data in a 4D application.  At first it seemed very doable by  
> using the Office Object Library but unless I am missing something it  
> appears this approach require manual input each time the report is  
> created (correct??)
>
> Overview:  1). pull a selection of records via a query.  2).  
> "Export" them in some fashion so as to end up with a formated Excel  
> document.  3). Attach the doc to an email and send it to the customer.
>
> Step 2 is where I need help.  Thanks in advance!!
>
> These spreadsheets will be created off a 4D 2004.7 server/client app
(Continue reading)

Permalink | Reply |
headers
Justin Carr | 9 Oct 06:02
Favicon

Re: Automatically creating Excel Docs from within 4D 2004.x

On 09/10/2008, at 1:36 PM, rooftop99@... wrote:

> I need to programatically create some formated Excel spreadsheets  
> from data in a 4D application.  At first it seemed very doable by  
> using the Office Object Library but unless I am missing something it  
> appears this approach require manual input each time the report is  
> created (correct??)
>
> Overview:  1). pull a selection of records via a query.  2).  
> "Export" them in some fashion so as to end up with a formated Excel  
> document.  3). Attach the doc to an email and send it to the customer.
>
> Step 2 is where I need help.  Thanks in advance!!
>
> These spreadsheets will be created off a 4D 2004.7 server/client app  
> running on a Mac.

If the target of the spreadsheet is Windows with Office 2003 (or later  
I expect) you can create the spreadhseet in XML format. If you have  
access to Office on Windows, the easiest way is to format it how you  
like and then Save As XML formatted spreadsheet. Then you can  
replicate the formatting by manually building the spreadsheet using  
4D's XML commands. As long as you save it with a .xls suffix Office  
will nkow what to do with it.

Note that the last time I checked, the Mac version of Office didn't  
understand XML formatted files but this may have changed in the  
meantime so you may want to check this if your target is a Mac.

Regards
(Continue reading)

Permalink | Reply |
headers
Arnaud de Montard | 9 Oct 09:25

Re: Automatically creating Excel Docs from within 4D 2004.x

Le 9 oct. 08 à 06:02, Justin Carr a écrit :

> On 09/10/2008, at 1:36 PM, rooftop99@... wrote:
>> I need to programatically create some formated Excel spreadsheets  
>> from data in a 4D application. [...]
>
> If the target of the spreadsheet is Windows with Office 2003 (or  
> later I expect) you can create the spreadhseet in XML format. [...]

If excel documents you have to produce use a very constant "matrix",  
maybe you'll find easier to produce html than xml (microsoft's xml  
is... mmm... difficult).

--

-- 
Arnaud de Montard

**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Permalink | Reply |
headers
Julio Carneiro | 10 Oct 16:12

Re: Automatically creating Excel Docs from within 4D 2004.x

an improvement on that technique is to use 4D HTML tags....

You create your excel template with all the formatting you want. You  
put placeholders where data coming from 4D will sit. Then save it as  
xml.
Open the xml with any text editor and replace your place holders with  
4Dvar, 4DScript, 4DLoop,... html tags.

To generate your excel export you basically do this:
     DOCUMENT TO BLOB(the excel template;templateBlob)
     PROCESS HTML TAGS(templateBlob;excelBlob)
     BLOB TO DOCUMENT(the excel file;excelBlob)

I've been doing this for years and it works beautiful. In fact the  
same technique can be used for .rtf or any other text file format.

FYI, PROCESS HTML TAGS does not require web server to be active or any  
web serve license...

There is a 4D tech note that covers this subject and suggests the  
technique above.(http://www.4d.com/knowledgebase?CaseID=30789)

hth
julio

On Oct 9, 2008, at 1:02 AM, Justin Carr wrote:

> On 09/10/2008, at 1:36 PM, rooftop99@... wrote:
>
>> I need to programatically create some formated Excel spreadsheets
(Continue reading)

Permalink | Reply |
headers
keisuke miyako | 9 Oct 13:45
Favicon

Re: Automatically creating Excel Docs from within 4D 2004.x

what a coincidence...

my summit session today happens to be precisely on this topic.

mac and windows.

create, xls, xlsx, read xls, xlsx, change font/color/backgound/size/ 
style etc., add reference charts, retrieve charts as pictures, read/ 
write cell/named item/range values and much more.

mac only.

send xls/xlsx over the net from the desktop, convert to pdf and send  
files as e-mail attachment.

miyako.

On 2008/10/08, at 20:36, rooftop99@... wrote:

> Hi All,
>
> I need to programatically create some formated Excel spreadsheets  
> from data in a 4D application.  At first it seemed very doable by  
> using the Office Object Library but unless I am missing something it  
> appears this approach require manual input each time the report is  
> created (correct??)
>
> Overview:  1). pull a selection of records via a query.  2).  
> "Export" them in some fashion so as to end up with a formated Excel  
> document.  3). Attach the doc to an email and send it to the customer.
(Continue reading)

Permalink | Reply |
headers
Julio Carneiro | 10 Oct 16:15

Re: Automatically creating Excel Docs from within 4D 2004.x

Keisuke,

can you elaborate a bit on the technique you use to deal with the new  
xlsx format? xlsx is a zipped package, so what do you use to access  
the files inside the package?

tia,
julio

On Oct 9, 2008, at 8:45 AM, keisuke miyako wrote:

> what a coincidence...
>
> my summit session today happens to be precisely on this topic.
>
> mac and windows.
>
> create, xls, xlsx, read xls, xlsx, change font/color/backgound/size/ 
> style etc., add reference charts, retrieve charts as pictures, read/ 
> write cell/named item/range values and much more.
>
> mac only.
>
> send xls/xlsx over the net from the desktop, convert to pdf and send  
> files as e-mail attachment.
>
> miyako.
>
> On 2008/10/08, at 20:36, rooftop99@... wrote:
>
(Continue reading)

Permalink | Reply |
headers
keisuke miyako | 13 Oct 12:07
Favicon

Re: Automatically creating Excel Docs from within 4D 2004.x

you can use zip, zipinfo and unzip (UNIX commands) to access the  
content of the xlsx documents.

alternatively you could run the save as... command via scripts to let  
excel convert the documents to and from .xml.

miyako

On 2008/10/10, at 7:15, Julio Carneiro wrote:

> Keisuke,
>
> can you elaborate a bit on the technique you use to deal with the  
> new xlsx format? xlsx is a zipped package, so what do you use to  
> access the files inside the package?
>
> tia,
> julio
>
**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
(Continue reading)

Permalink | Reply |
headers
Julio Carneiro | 13 Oct 13:37

Re: Automatically creating Excel Docs from within 4D 2004.x

The problem is that the .xml format has a limited set of features. You  
loose a lot of formating options in the .xml format, besides the fact  
that it is considered 'old' in Office 2007.
I use the technique of creating a .xml spreadsheet with the .xls  
suffix so one can double-click it to launch Excel on Windows. Works  
fine with Office XP but Office 2007 shows up a stupid warning message  
which scares off a lot of users.

Thus the idea of using .xlsx is more appealing with the latest Office.

So you suggest using unzip on OS X, via LEP. What about Windows? same  
idea, run an external command line unzip tool, do whatever you need  
with the .xml files and repackage them with an external zip tool?
Or do you use/suggest a zip plugin (Zip4D for example)?

tia,
julio

On Oct 13, 2008, at 8:07 AM, keisuke miyako wrote:

> you can use zip, zipinfo and unzip (UNIX commands) to access the  
> content of the xlsx documents.
>
> alternatively you could run the save as... command via scripts to  
> let excel convert the documents to and from .xml.
>
> miyako
>
> On 2008/10/10, at 7:15, Julio Carneiro wrote:
>
(Continue reading)

Permalink | Reply |
headers
keisuke miyako | 13 Oct 16:30
Favicon

Re: Automatically creating Excel Docs from within 4D 2004.x

Hello,

I did play around with the IE object on Windows using vbscript to zip  
and unzip files, the target being not to depend on any external dlls  
or plugins, though I could not quite establish a way to see the  
hierarchical structure inside the zip package... perhaps we might have  
to reach out for external resources at some point. in any case i  
decided to depart from the process html tags approach, for the very  
reason you have mentioned, and deal directly with xls or xlsx  
documents. Excel is not fooled by the extension, as you have pointed  
out, but I don't think passing an .xls file whose content is an xml  
spreadsheet is a very good idea. If Excel 2007/2008 is present in the  
system then you can instruct it to do whatever you like. the html tags/ 
xml solution is only a last resort when you can not assume that Excel  
is installed.

miyako

On 2008/10/13, at 4:37, Julio Carneiro wrote:

> The problem is that the .xml format has a limited set of features.  
> You loose a lot of formating options in the .xml format, besides the  
> fact that it is considered 'old' in Office 2007.
> I use the technique of creating a .xml spreadsheet with the .xls  
> suffix so one can double-click it to launch Excel on Windows. Works  
> fine with Office XP but Office 2007 shows up a stupid warning  
> message which scares off a lot of users.
>
> Thus the idea of using .xlsx is more appealing with the latest Office.
>
(Continue reading)

Permalink | Reply |
headers
kenajcooper | 10 Oct 09:06
Favicon

Re: Automatically creating Excel Docs from within 4D 2004.x

Hi

Sorry if I am late on this subject

I have been creating xml style documents on 2004 for about 3 years now.

these are then saved to a folder and emailed around to various intranet Users
then can be loaded in IE or excel as required

If anyone wants an example of what I have been doing just email me at kenajcooper@...
only to glad to help

Best Regards

Ken
________________________________________________________________________
AOL Email goes Mobile! You can now read your AOL Emails whilst on the move. Sign up for a free AOL Email account
with unlimited storage today.
**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
(Continue reading)

Permalink | Reply |

Gmane