creating an excel spreadsheet.

[Allan Biggs]

Sorry a bit of a cheek here as this is not a question about OpenRoad.


I am trying to find a way of creating an excel spreadsheet from a Fortran (embedded sql etc) generated image on a Linux box which has Ingres on it.

I am able to create a csv file but this does not meet the formatting requirements.

e.g some cells have numbers such as 1234567890 others 123.56 etc

writing a csv file loses the formatting and using tricks like this

"=""this is a title """
"=""1234567890""","=""1234567.890"""
"=""1234567890""","=""1234567.890"""

makes the numbers characters.


We don't have OpenRoad on any Linux boxes

I was just wondering if anyone had had this problem and would care to share the solution

thanks
Allan

This communication is for use by the intended recipient and contains
information that may be Privileged, confidential or copyrighted under
applicable law. If you are not the intended recipient, you are hereby
formally notified that any use, copying or distribution of this e-mail,
in whole or in part, is strictly prohibited. Please notify the sender by
return e-mail and delete this e-mail from your system. Unless explicitly
and conspicuously designated as "E-Contract Intended", this e-mail does
not constitute a contract offer, a contract amendment, or an acceptance
of a contract offer. This e-mail does not constitute a consent to the
use of sender's contact information for direct marketing purposes or for
transfers of data to third parties.

The dupont.com http://dupont.com web address may be used for a limited period of time by the following
divested businesses that are no longer affiliated in any way with DuPont:
Borealis Polymers NV
Jacob Holm & Sonner Holding A/S (Jacob Holm)
Kuraray Co., Ltd

DuPont accepts no liability or responsibility for the content or use of communications
sent or received on behalf of such divested businesses or for the consequences of
any actions taken on the basis of such communications.

Francais Deutsch Italiano  Espanol  Portugues  Japanese  Chinese  Korean

          http://www.DuPont.com/corp/email_disclaimer.html

[Maxime Richez]

maybe using python to generate you excel file (but don't know if python ingres driver is fully operational)

Le 26/05/15 13:13, Allan Biggs a écrit :

--
You received this message because you are subscribed to the Google Groups "OpenROAD Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openroad-user...@googlegroups.com.
To post to this group, send email to openroa...@googlegroups.com.
Visit this group at http://groups.google.com/group/openroad-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/OF9D21D36C.C75EABD4-ON80257E51.003C5918-80257E51.003DC823%40CDCLN05.LVS.DUPONT.COM.
For more options, visit https://groups.google.com/d/optout.

[Allan Biggs]

Maxime,

I know very little about excel and assumed that xlsx was xml , it isn't !! I have discovered that excel can output and read XML as a .xml extension.
I am pursuing that , I don't know XML but it seems straightforward and the spreadsheet version retains formatting.

Thanks
Allan

          http://www.DuPont.com/corp/email_disclaimer.html

.
To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/55645947.8010208%40saluc.com.
For more options, visit https://groups.google.com/d/optout.

This communication is for use by the intended recipient and contains
information that may be Privileged, confidential or copyrighted under
applicable law. If you are not the intended recipient, you are hereby
formally notified that any use, copying or distribution of this e-mail,
in whole or in part, is strictly prohibited. Please notify the sender by
return e-mail and delete this e-mail from your system. Unless explicitly
and conspicuously designated as "E-Contract Intended", this e-mail does
not constitute a contract offer, a contract amendment, or an acceptance
of a contract offer. This e-mail does not constitute a consent to the
use of sender's contact information for direct marketing purposes or for
transfers of data to third parties.

[Paul White]

Hi all,

 

I agree. xml would seem to be the way to go for portable reports.

 

But, steering back on to the Open ROAD J, you may be able to adapt this approach.

 

We have a standard module for writing a TableField to an Excel spreadsheet with page header and columns titles each column having its own formatting.  To enable printing for an enquiry screen, you paste in the standard TableField control button,  insert a couple of lines into the code and voila!  An Excel report which presents the contents of the tablefield.  No ActiveX is needed.

 

The OpenROAD module writes data to a file with a fixed format. Something like this.

 

report title

number of columns, number of rows

col1_title, width, type

col2_title, width, type

col3_title, width, type

…

c1 data, c2 data, c3 data, …

c1 data, c2 data, c3 data, …

c1 data, c2 data, c3 data, …

 

Then the program calls SYSTEM “generic_table_printing.xls”.  A macro fires up which reads in the formatting info and the data and displays a neat table to the user. Extra flags in the header of the data file allow the macro to redirect the report to the printer or to email without having to request input from the user.  The interface is forgiving. It doesn’t complain if you accidentally put text in a number column.  The downside is you need to enable macros.

 

 

By the way, XLSX format is actually a compressed file with several branches with XML files.  You can explore the structure by renaming it to ZIP and opening with your favourite unzip browsing tool.  I’ve used this feature in the past to recover from Excel crash.

 

Let me know how you go

 

Paul

 

&
Shift Seven Solutions
www.shift7solutions.com.au

 

 

[Allan Biggs]

Thanks Paul.
This processing is going on , on a Linux box , we don't have OpenROAD on that box.
We produce .html files from 3GL (Fortran ) all the time and I am very familiar with html scripting so xml should be straightforward.

One issue you may know the answer to though on a Windows PC , If I create the .xml file using a text editor and then open it , it opens in a browser. If I force it to open in excel and then save it from excel it subsequently opens in excel. The windows file icon changes accordingly. I am assuming some of the Microsoft header info needs to be included to achieve this.

Allan
--
Sent from my Android phone with mail.com Mail. Please excuse my brevity.

Paul White <paul....@shift7solutions.com.au> wrote:

--

You received this message because you are subscribed to the Google Groups "OpenROAD Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openroad-user...@googlegroups.com.
To post to this group, send email to openroa...@googlegroups.com.
Visit this group at http://groups.google.com/group/openroad-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/55648c70.0169440a.2bce.5bc6%40mx.google.com.

[Paul White]

Hi Allan,

 

In Windows it is generally the file extension which determines which program opens from windows explorer.

I presume you have set the file extension to xml.

 

Before accessing the file with Excel, try opening the file with notepad to inspect the file.

 

Right click or Shift-right click, open with, choose program…

or

open a command window and type

notepad c:\temp\myfile.xml

or

                change the file extension to .txt

                double click on file.

 

 

After you save with Excel you will see something like this..

 

<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

 xmlns:o="urn:schemas-microsoft-com:office:office"

 xmlns:x="urn:schemas-microsoft-com:office:excel"

…

 

Paul

To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/trinity-55db4d42-f444-4ff4-bd8e-dd5707e17d30-1432655963003%40msvc-mesg-gmxus001.