Subject: RE: [xsl] date calculation from Excel 1900 Format From: Xiaocun Xu <xiaocunxu@xxxxxxxxx> Date: Mon, 11 Jun 2001 19:46:52 -0700 (PDT) |
Hi, Jason: Thanks for the tip, that is pretty interesting indeed. Unfortunely, I am converting Excel in Java environment using Saxon, so I don't think I would able to take advantage of build-in VB functions such as CDate(). Anything similar exists can be used within Saxon/Java? Thanks, Xiaocun --- "Diamond, Jason" <Jason.Diamond@xxxxxxx> wrote: > Since you're converting from Excel, I'm assuming > you're using Windows and > probably also MSXML. If you don't mind using > extension functions the > following transform almost gets you what you want: > > <xsl:transform > version="1.0" > xmlns:xsl="http://www.w3.org/1999/XSL/Transform" > xmlns:msxsl="urn:schemas-microsoft-com:xslt" > > xmlns:e2w="urn:foo:excel-to-w3c-date-time-format" > > > > <xsl:output method="text" encoding="UTF-8"/> > > <msxsl:script > language="VBScript" > implements-prefix="e2w"> > > <![CDATA[ > > Function convert(excelDate) > dt = CDate(excelDate) > convert = _ > Year(dt) & "-" & _ > Month(dt) & "-" & _ > Day(dt) & " " & _ > Hour(dt) & ":" & _ > Minute(dt) & ":" & _ > Second(dt) > End Function > > ]]> > > </msxsl:script> > > <xsl:template match="/"> > <xsl:value-of > select="e2w:convert(37257.041667)"/> > </xsl:template> > > </xsl:transform> > > The output looks like this: > > 2002-1-1 1:0:0 > > so you'd have to left pad the digits to really get > it into the proper > format. > > Hope this helps, > Jason. > > -----Original Message----- > From: Xiaocun Xu [mailto:xiaocunxu@xxxxxxxxx] > Sent: Monday, June 11, 2001 11:50 AM > To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx > Subject: [xsl] date calculation from Excel 1900 > Format > > > Hi, > > During my conversion from Excel to XML, I needed > to > convert dates. Excel stores date in so called "1900 > format", which is the number of days since 1/1/1900 > in > decimal format, e.g. 37257.041667 for 1/1/2002 1AM. > I > need to convert this back to YYYY-MM-DD HH:MM:SS > format when I convert the Excel into XML. > Has anyone did date conversion calculation, esp. > Excel 1900 format, with XSL? > > Much thanks, > Xiaocun > > __________________________________________________ > Do You Yahoo!? > Get personalized email addresses from Yahoo! Mail - > only $35 > a year! http://personal.mail.yahoo.com/ > > XSL-List info and archive: > http://www.mulberrytech.com/xsl/xsl-list > > XSL-List info and archive: > http://www.mulberrytech.com/xsl/xsl-list > __________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
Current Thread |
---|
|
<- Previous | Index | Next -> |
---|---|---|
RE: [xsl] date calculation from Exc, Diamond, Jason | Thread | [xsl] Solution for word wrapping, Wajihuddin Ahmed |
Re: [xsl] HTML in CDATA, Mike Brown | Date | Re: [xsl] date calculation from Exc, Xiaocun Xu |
Month |