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.