|
Vern, Jonathan & all,
Thanks for the helpful replies. I have a solution but I have one more question. When I go to open the file saved as .xls I get the following pop-up in excel "The file you are trying to open, 'xxx.xls', is in a different format than specified by the file extension. Is there anything I can change in the xml header to prevent this? The users actually want to be able to pass this information off without any fussing. I'm running MO 2010.
Vern to address some of your questions. I am creating this via websmart which I think would be fairly close to CGIDEV2. " Are you talking about the 2003 Spreadsheet XML format for Excel?" Is there a difference? I see about 4 references to excel in the header.
<?mso-application progid="Excel.Sheet"?>
xmlns:x="urn:schemas-microsoft-com:office:excel"
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
Jonathan - I thought I was going to have to build a full time stamp - but the following works:
<Style ss:ID="s21">
<NumberFormat ss:Format="Short Date"/>
</Style>
<Cell ss:StyleID="s21"><Data ss:Type="DateTime">2013-09-12</Data></Cell>
I appreciate your response because I drove in this morning thinking I needed to build the full timestamp. I still might have to - I will let the users take a whack at it.
Thanks again for the reponses. I was kind of surprised that I had never done one of these before and we didn't have any examples in the shop.
Vicki
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rpg400-l-request@xxxxxxxxxxxx
Sent: Thursday, March 12, 2015 2:59 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: RPG400-L Digest, Vol 14, Issue 93
Send RPG400-L mailing list submissions to
rpg400-l@xxxxxxxxxxxx
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/rpg400-l
or, via email, send a message with subject or body 'help' to
rpg400-l-request@xxxxxxxxxxxx
You can reach the person managing the list at
rpg400-l-owner@xxxxxxxxxxxx
When replying, please edit your Subject line so it is more specific than "Re: Contents of RPG400-L digest..."
*** NOTE: When replying to this digest message, PLEASE remove all text unrelated to your reply and change the subject line so it is meaningful.
Today's Topics:
1. Re: Date formats in XML for Excel Spreadsheet download?
(Vernon Hamberg)
2. Re: /Copy books - yes or no? (Ken Sims)
3. Re: /Copy books - yes or no? (John Yeung)
4. Re: /Copy books - yes or no? (Vernon Hamberg)
5. Re: /Copy books - yes or no? (John Yeung)
6. Re: /Copy books - yes or no? (John Yeung)
7. Re: /Copy books - yes or no? (Paul Raulerson)
8. AW: /Copy books - yes or no? (Birgitta Hauser)
9. RE: Date formats in XML for Excel Spreadsheet download?
(Jonathan Mason)
----------------------------------------------------------------------
message: 1
date: Wed, 11 Mar 2015 20:24:05 -0500
from: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
subject: Re: Date formats in XML for Excel Spreadsheet download?
Vicki - I might be able to help if I have a little more detail.
Are you talking about the 2003 Spreadsheet XML format for Excel?
Are you using CGIDEV2 or some similar tool to write the XML?
Or are you doing something different? Like does Excel read the XML directly?
Anyhow, Excel stores dates and times as numbers, known as serial numbers, the dates are the number of days after (or since?) Jan 1, 1900 (or 1904, if you set it up that way).
Hope I'm anywhere on the same planet as you are!! Glad to help if I can.
I've been doing a lot work with Excel lately, so may actually know something.
Vern
On 3/11/2015 6:45 PM, Vicki Wilson wrote:
Hi all,
Having trouble find the right XML format for downloading data to an excel spreadsheet.
The user would like the date in "short date" or "long date" format.
I'm starting with a 6 digit number. I can get it converted to iso no problem.
But I'll be darned if I can find the right xml to excel format.
I've tried various combinations including the latest:
This is in the header:
<Style ss:ID="s21">
<NumberFormat ss:Format="MM/DD/YY"/>
</Style>
This is the detail:
<Cell ss:StyleId="s21"><Data
ss:Type="Date">2013-09-16</Data></Cell>
I'm googling and finding lots of specific examples - particularly one's that include a full date timestamp - but nothing with just the date. And nothing that really explains the mapping.
Thanks.
Vicki Wilson
------------------------------
message: 2
date: Wed, 11 Mar 2015 18:29:15 -0700
from: Ken Sims <mdrg8066@xxxxxxxxxxx>
subject: Re: /Copy books - yes or no?
On Wed, 11 Mar 2015 22:37:57 +0000, Bob Cagle <bcagle@xxxxxxxxxxx>
wrote:
Pro: Procedure prototypes don't have to be rekeyed everywhereWe don't use them a lot, and we don't have any with executable code, but we do use them for procedure prototypes for service programs and we use them for standardized H-specs.
Con: Sometimes you need to see the code in the copy book, and you have to go to a separate source to view it.
Ken
Opinions expressed are my own and do not necessarily represent the views of my employer or anyone in their right mind.
------------------------------
message: 3
date: Wed, 11 Mar 2015 21:42:37 -0400
from: John Yeung <gallium.arsenide@xxxxxxxxx>
subject: Re: /Copy books - yes or no?
I agree with the prevailing responses you've already received.
A small thing I'd like to add regarding C and C++: Their include files
are typically called "header files" or simply "headers". This makes it
very clear that they're meant for prototypes and other setup, not
arbitrary code.
I am going to guess that there is a decent-sized minority of RPG
programmers who "grew up" with pre-ILE versions of RPG. I'm not going
to say that these are categorically bad programmers. I think some
percentage of them are "modularity minded", making extensive use of
subroutines and using manageable, reusable *PLIST-called OPM programs.
Granted, not as nice as subprocedures and service programs, but still
a far cry from what I would call "monolithic".
Now, the point of that verbose preamble is this: I think THOSE
programmers are the ones who are most opposed to copy blocks. Because
really, what are you going to do with copy blocks in OPM? They don't
really gain you very much in OPM, and they still have all the
disadvantages.
Full disclosure: I consider myself a modularity-minded OPM RPG
programmer, and our codebase where I work is almost exclusively
"modularly OPM RPG IV". C was my primary programming language
throughout high school and college; I never heard of RPG or AS/400
until my first job after graduating.
John Y.
------------------------------
message: 4
date: Wed, 11 Mar 2015 20:52:31 -0500
from: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
subject: Re: /Copy books - yes or no?
There was a technique used before service programs arrived - and even
afterwards, perhaps - to use a pair of /copy members, the first as a
header, the 2nd as executable code at the end of a source member.
I do not say it is a desirable way to work, but you may run across this
usage - it DOES make it possible to have maintainable utility code in
one place, so you are not using copy/paste techniques, which are prone
to all kinds of error of forgetting where it all is.
Now one downside - programs will all be larger than they need to be, if
you have a service program with all those utility functions.
Once in place, such practices take time to bring forward to using more
modern methods - time we don't always have.
All I've said lines up, I hope, with what John and the others have
offered, though.
HTH
Vern
On 3/11/2015 8:42 PM, John Yeung wrote:
I agree with the prevailing responses you've already received.
A small thing I'd like to add regarding C and C++: Their include files
are typically called "header files" or simply "headers". This makes it
very clear that they're meant for prototypes and other setup, not
arbitrary code.
I am going to guess that there is a decent-sized minority of RPG
programmers who "grew up" with pre-ILE versions of RPG. I'm not going
to say that these are categorically bad programmers. I think some
percentage of them are "modularity minded", making extensive use of
subroutines and using manageable, reusable *PLIST-called OPM programs.
Granted, not as nice as subprocedures and service programs, but still
a far cry from what I would call "monolithic".
Now, the point of that verbose preamble is this: I think THOSE
programmers are the ones who are most opposed to copy blocks. Because
really, what are you going to do with copy blocks in OPM? They don't
really gain you very much in OPM, and they still have all the
disadvantages.
Full disclosure: I consider myself a modularity-minded OPM RPG
programmer, and our codebase where I work is almost exclusively
"modularly OPM RPG IV". C was my primary programming language
throughout high school and college; I never heard of RPG or AS/400
until my first job after graduating.
John Y.
------------------------------
message: 5
date: Wed, 11 Mar 2015 22:09:21 -0400
from: John Yeung <gallium.arsenide@xxxxxxxxx>
subject: Re: /Copy books - yes or no?
On Wed, Mar 11, 2015 at 7:29 PM, Bradley Stone <bvstone@xxxxxxxxx> wrote:
I use them with great enthusiasm.I think this is another thing that really got me when I first
Have't run into an issue with them unless you're sloppy and keep different
"versions" of them in your library list. But that's user error.
encountered copy blocks. I'd just graduated from college where almost
everything was done in C. So I was no stranger to header files. But
RPG IV had not been introduced yet (at least not where I worked, and
this was before Google, so it's not like I had lots of IBM midrange
information pouring in). So they seemed kind of useless to me.
And then, on top of that, there was the very broad-but-shallow "file
system" consisting of just libraries, files, and members. Given how
shallow it was, and how rigid the shop conventions were (in large part
to cope with the shallowness), and how restrictive the green screen
and PDM/SEU were, it seemed like keeping track of copy blocks was
much, much more of a pain than keeping track of headers.
So yeah, /COPY felt simultaneously less useful and more difficult to
manage than #include, even though on some level they are the same
thing.
I will say ILE brought a lot of the C feel to RPG.
John Y.
------------------------------
message: 6
date: Wed, 11 Mar 2015 22:22:50 -0400
from: John Yeung <gallium.arsenide@xxxxxxxxx>
subject: Re: /Copy books - yes or no?
Oh, I didn't fully elaborate one of my main points:
On Wed, Mar 11, 2015 at 10:09 PM, John Yeung <gallium.arsenide@xxxxxxxxx> wrote:
On Wed, Mar 11, 2015 at 7:29 PM, Bradley Stone <bvstone@xxxxxxxxx> wrote:I wanted to agree with this, but also contrast with the situation in C
Have't run into an issue with them unless you're sloppy and keep different
"versions" of them in your library list. But that's user error.
on other platforms. Typically, C projects are clumped into directories
(or folders if you're into Windows GUI terminology). There's not much
danger of picking up the wrong version of a header, because typically
the header is either part of the standard library or it's local to
your project. With arbitrary directories and much more flexible naming
options, I don't remember "wrong header" (or "someone else messed up
my header without telling me") ever being an issue for me in C (on
Unix).
John Y.
------------------------------
message: 7
date: Wed, 11 Mar 2015 22:40:07 -0500
from: Paul Raulerson <paul.raulerson@xxxxxxx>
subject: Re: /Copy books - yes or no?
Good Lord, YES. I don?t know anyone who opposes them actually.
What are the reasons your friend has for not wanting to use copybooks?
-Paul
On Mar 11, 2015, at 5:37 PM, Bob Cagle <bcagle@xxxxxxxxxxx> wrote:
Here's a fun topic:
During lunch with my friend today I brought up the subject of copy books, and I was surprised by his complete and total vehemence against them. His son was there also, who is currently learning C++, and he was surprised as well, since C++ makes frequent use of #Include statements.
Personally, I have been using /Include in my RPG instead of /Copy, just because it looks more modern to me.
So, do you use copy books in your RPG? Why, why not? Pros and Cons?
I'll start:
Pro: Procedure prototypes don't have to be rekeyed everywhere
Con: Sometimes you need to see the code in the copy book, and you have to go to a separate source to view it.
Thanks
Bob Cagle
IT Manager
Lynk, Inc.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
------------------------------
message: 8
date: Thu, 12 Mar 2015 07:06:53 +0100
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: AW: /Copy books - yes or no?
We use a copy member with the Standard H-Specs and a copy member with
reference fields and data structures, but all information within these copy
members are conditioned with compiler directives, so we can activate or
deactivate special information within these compy members whenever we want.
We also use copy members that include prototypes.
For everything else we used copy members within RPGIII we no use external
ILE procedures and functions.
Mit freundlichen Gr??en / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Urspr?ngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Bob
Cagle
Gesendet: Wednesday, 11.3 2015 23:38
An: RPG400-L@xxxxxxxxxxxx
Betreff: /Copy books - yes or no?
Here's a fun topic:
During lunch with my friend today I brought up the subject of copy books,
and I was surprised by his complete and total vehemence against them. His
son was there also, who is currently learning C++, and he was surprised as
well, since C++ makes frequent use of #Include statements.
Personally, I have been using /Include in my RPG instead of /Copy, just
because it looks more modern to me.
So, do you use copy books in your RPG? Why, why not? Pros and Cons?
I'll start:
Pro: Procedure prototypes don't have to be rekeyed everywhere
Con: Sometimes you need to see the code in the copy book, and you have
to go to a separate source to view it.
Thanks
Bob Cagle
IT Manager
Lynk, Inc.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
------------------------------
message: 9
date: Thu, 12 Mar 2015 09:58:15 +0000
from: Jonathan Mason <Jonathan.Mason@xxxxxxxxxxx>
subject: RE: Date formats in XML for Excel Spreadsheet download?
Hi Vicki
I wrote a service program to generate SpreadsheetML (XML) documents from RPG and found that for any date values you want to have treated as dates you need to output the date as a full timestamp. The following snippets of code are taken from a spreadsheet generated using the service program:
<Style ss:ID="s26">
<Alignment ss:Horizontal="Left" ss:ReadingOrder="Context" ss:Vertical="Bottom" ss:WrapText="1" />
<Font ss:FontName="Calibri" x:Family="Automatic" ss:Size="10" ss:Color="#000000"/>
<NumberFormat ss:Format="dd/mm/yyyy"/>
</Style>
...
...
<Row ss:AutoFitHeight="1" ss:Height="12.75" ss:StyleID="s27">
<Cell ss:StyleID="s24">
<Data ss:Type="String">Run Date:</Data>
<NamedCell ss:Name="Print_Area" />
<NamedCell ss:Name="Print_Titles" />
</Cell>
<Cell ss:StyleID="s26">
<Data ss:Type="DateTime">2015-03-10T14:49:55.000</Data>
<NamedCell ss:Name="Print_Area" />
<NamedCell ss:Name="Print_Titles" />
</Cell>
</Row>
I have a sub-procedure that returns the timestamp format for a given date and (optional) time:
P**************************************************************************
P* Procedure: xl_CvtDate - Convert Date to Excel Format...
P**************************************************************************
P xl_CvtDate b Export
D xl_CvtDate pi 23a
D iDate 8s 0 Const
D iDateFmt 5i 0 Const
D iTime 6s 0 Const Options(*NoPass)
D wExcelDate s 23a
D wDate s d
D wTime s t
D wCharDate s 10a
D wCharTime s 8a
/Free
Monitor;
// Convert the passed in date to an iso date...
Select;
When iDateFmt = FMT_DMY;
wDate = %Date(iDate : *DMY);
When iDateFmt = FMT_DMYY;
wDate = %Date(iDate : *EUR);
When iDateFmt = FMT_MDY;
wDate = %Date(iDate : *MDY);
When iDateFmt = FMT_MDYY;
wDate = %Date(iDate : *USA);
When iDateFmt = FMT_YMD;
wDate = %Date(iDate : *YMD);
When iDateFmt = FMT_CYMD;
wDate = %Date(iDate : *CYMD);
When iDateFmt = FMT_YYMD;
wDate = %Date(iDate : *ISO);
When iDateFmt = FMT_JULIAN;
Other;
Return '*ERROR';
EndSl;
// Convert the passed in time to a time field...
wTime = t'00.00.00';
If %Parms >= 3;
wTime = %Time(iTime : *HMS);
EndIf;
On-Error;
Return '*ERROR';
EndMon;
// To get this far, the date and time were both valid so convert them
// to character fields...
wCharDate = %Char(wDate : *ISO);
wCharTime = %Char(wTime : *ISO);
// Finally build the Excel format...
wExcelDate = %Trim(wCharDate) + 'T' + %Trim(wCharTime) + '.000';
%Subst(wExcelDate : 14 : 1) = ':';
%Subst(wExcelDate : 17 : 1) = ':';
Return wExcelDate;
/End-Free
P xl_CvtDate e
All the best
Jonathan
------------------------------
date: Wed, 11 Mar 2015 23:45:38 +0000
from: Vicki Wilson <VWilson@xxxxxxxxxxxxx>
subject: Date formats in XML for Excel Spreadsheet download?
Hi all,
Having trouble find the right XML format for downloading data to an excel spreadsheet.
The user would like the date in "short date" or "long date" format.
I'm starting with a 6 digit number. I can get it converted to iso no problem.
But I'll be darned if I can find the right xml to excel format.
I've tried various combinations including the latest:
This is in the header:
<Style ss:ID="s21">
<NumberFormat ss:Format="MM/DD/YY"/>
</Style>
This is the detail:
<Cell ss:StyleId="s21"><Data ss:Type="Date">2013-09-16</Data></Cell>
I'm googling and finding lots of specific examples - particularly one's that include a full date timestamp - but nothing with just the date. And nothing that really explains the mapping.
Thanks.
Vicki Wilson
Proud partner of The Ageas Bowl and the Ageas Salisbury International Arts Festival.
Registered Address: Ageas House, Hampshire Corporate Park, Templars Way, Eastleigh, Hampshire, SO53 3YA Registered Number: 354568 England
Authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and the Prudential Regulation Authority
This e-mail together with any attachments are intended for the addressee only and may be private and confidential. If you are not the intended recipient, or the person responsible for delivering it to the intended recipient, you must not open any attachments, or copy, disclose, distribute, retain or use this e-mail, including any attachments, in any way whatsoever; please return it to us immediately using the reply facility on e-mail.
Consider the environment and think before you print this email.
------------------------------
Subject: Digest Footer
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
