Date Conversion issue from XLSX to internal table field from application server

[former_member206479]

I'm trying to convert the .XLSX file which is available in network shared folder(application server). i able to extract the data using XSLT but i'm i'm facing a problem with date column from the file.

After extraction i'm getting date column value like below

Here is my XSLT code. please help out to get the date value properly.

<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:ss="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:sap="http://www.sap.com/sapxsl"
xmlns:asx="http://www.sap.com/abapxml" exclude-result-prefixes="c" version="1.0">
<xsl:param name="P_SHARED_STRING" select=""/>
<xsl:strip-space elements="*"/>
<xsl:output encoding="utf-8" indent="yes" omit-xml-declaration="yes"/>
<xsl:variable name="V_SHARED_STRING">
<xsl:if test="$P_SHARED_STRING">
<xsl:copy-of select="$P_SHARED_STRING"/>
</xsl:if>
</xsl:variable>
<xsl:template match="/">
<asx:abap version="1.0">
<asx:values>
<LT_DATA>
<xsl:for-each select="ss:worksheet/ss:sheetData/ss:row">
<xsl:if test="position() > 1">
<item>
<C1>
<xsl:variable name="cell_id" select="concat('A', position())"/>
<xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
<xsl:if test="$v_index">
<xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
</xsl:if>
<xsl:if test="not($v_index)">
<xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
</xsl:if>
</C1>
<C2>
<xsl:variable name="cell_id" select="concat('B', position())"/>
<xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
<xsl:if test="$v_index">
<xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
</xsl:if>
<xsl:if test="not($v_index)">
<xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
</xsl:if>
</C2>
<C3>
<xsl:variable name="cell_id" select="concat('C', position())"/>
<xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
<xsl:if test="$v_index">
<xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
</xsl:if>
<xsl:if test="not($v_index)">
<xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
</xsl:if>
</C3>
<C4>
<xsl:variable name="cell_id" select="concat('D', position())"/>
<xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
<xsl:if test="$v_index">
<xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
</xsl:if>
<xsl:if test="not($v_index)">
<xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
</xsl:if>
</C4>
<C5>
<xsl:variable name="cell_id" select="concat('E', position())"/>
<xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
<xsl:if test="$v_index">
<xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
</xsl:if>
<xsl:if test="not($v_index)">
<xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
</xsl:if>
</C5>
</item>
</xsl:if>
</xsl:for-each>
</LT_DATA>
</asx:values>
</asx:abap>
</xsl:template>
</xsl:transform>

[Sandra_Rossi]

The integer part of an Excel date/time field is the number of days since 1/1/1900 (and the fractional part is the division of 24 hours).

But wouldn't you want to use abap2xlsx?

[Former Member]

As sandra.rossi mentioned what is actually stored in the XLSX file is a number marking the days passed from the baseline date. It is the Excel application that hides this behind a language-dependent date representation, just like an output conversion exit in ABAP.

This is how the value can be converted to ABAP date format:

CONSTANTS: baseline_date TYPE d VALUE '19000101'.

DATA: days_passed TYPE i,
      result_date TYPE d.

days_passed = excel_date_string.

result_date = baseline_date + days_passed - 2.

BR,
Gábor

[former_member206479]

Thank you Gabor. You made my day.

[Sandra_Rossi]

Rule of thumb: if you have to work with XLSX, use abap2xlsx (search). Period.

PS: don't use the ANSWER button, edit your question or add a comment. As SAP says below:

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem

[DoanManhQuynh]

FYI: using transformation you can read the value in there but that value format is depend on the definition in sharedstring.xml (for some cases directly in sheet.xml). I didn't find other easier way than manually convert it in transform or in ABAP. I suggest to use CL_XML_DOCUMENT to handle xml file other than transformation...

[former_member206479]

Hi Sandra,

I tried to change the format of the excel date column to text and then it is showing the value what we have received in our internal table.

Actually, what i'm doing here is - i'm reading file in binary mode as it is .xlsx file in the application server and then converting into xstring then XML using XSLT and from there we are receiving data into our internal table. So at the time of transfer to XSLT our date column is converted to text format. is there anyway to keep the file column format in the XSLT?

Thanks,

Venkat.

[former_member767340]

CALL METHOD zcl_excel_common=>excel_string_to_date

EXPORTING
ip_value = ls_cell-cell_value
RECEIVING
ep_value = lv_date.

use this method!

BR,