Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Need to display TIme Format in excel via XML code in SAP ABAP

753

I am generating excel file through XML code via background Job and the same excel is being sent through email.

I want to display time field like 09:34AM . Through program I am passing time value as 09:30:23 but when it will display in excel it should follow the time format like hh:mm AM. But when I am opening the excel it is displaying like 09:34:23 but when I am double clicking on the cell then it is taking the desired format like 09:34AM. Can you please help me to fix this issue.

Code snippet:

** Style for Data
r_style3 = l_document->create_simple_element( name = 'Style' parent = r_styles ).
r_style3->set_attribute_ns( name = 'ID' prefix = 'ss' value = 'Data2' ). r_format = l_document->create_simple_element( name = 'NumberFormat' parent = r_style3 ).
r_format->set_attribute_ns( name = 'Format' prefix = 'ss' value = 'h:mm AM/PM' ). r_format = l_document->create_simple_element( name = 'Alignment' parent = r_style3 ).
r_format->set_attribute_ns( name = 'Vertical' prefix = 'ss' value = 'Top' ).
r_format->set_attribute_ns( name = 'WrapText' prefix = 'ss' value = '1' ).

lv_time = '122523'.
WRITE lv_time TO lv_data USING EDIT MASK '__:__:__'.
MOVE lv_data TO l_value.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data2' ).
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

11 REPLIES 11

Sandra_Rossi
Active Contributor
254

You can find a solution by yourself (it works for any question in Excel):

  1. open Excel
  2. enter the time in the format as you wish
  3. save the workbook as XML 2003
  4. repeat the same operation with a different time format, and save it to another workbook
  5. open the 2 workbooks with a text editor (it's possible because the workbooks are in XML format) like notepad++ to compare the 2 workbooks
  6. look at the differences which correspond to the time format and reproduce that in your ABAP program

0 Kudos
254

Thanks Rossi for your suggestion. My problem is the desired format is working when double clicking on the cell but the same format is not working at first time when the excel in opening. Unfortunately in my client system I can't install notepad ++

Sandra_Rossi
Active Contributor
0 Kudos
254

Then please attach the 2 XML files, I will compare them for you...

254

Not able to attach the XML file since the portal is not supporting XML or XLSX file type hence attaching the screenshots.

I want similar like screenshot when it will display first time it will display time as 12:23PM but when select the cell then in formula bar it should display 12:23:43PM. Through below code when the excel is displaying first time it is displaying 12:23:43 but upon right click and select format cell it is display correct time format which is 1:30 PM. Also upon double clicking on the cell it displays the correct format but not at the first time when it displays

Below format I have sent through code.

** Style for Data

r_style3 = l_document->create_simple_element( name = 'Style' parent = r_styles ).

r_style3->set_attribute_ns( name = 'ID' prefix = 'ss' value = 'Data2' ).

r_format = l_document->create_simple_element( name = 'Font' parent = r_style3 ).

r_format->set_attribute_ns( name = 'Italic' prefix = 'ss' value = '1').

r_format->set_attribute_ns( name = 'Family' prefix = 'ss' value = 'Arial').

r_format->set_attribute_ns( name = 'Size' prefix = 'ss' value = '10').

r_format = l_document->create_simple_element( name = 'Alignment' parent = r_style3 ). r_format->set_attribute_ns( name = 'Horizontal' prefix = 'ss' value = 'Center' ).

r_format->set_attribute_ns( name = 'WrapText' prefix = 'ss' value = '1' ).

r_format = l_document->create_simple_element( name = 'NumberFormat' parent = r_style3 ). r_format->set_attribute_ns( name = 'Format' prefix = 'ss' value = '[$-409]h:mm AM/PM;@' ).

*Passing Data into Excel

CLEAR: lv_time,l_value. lv_time = '122523'.

WRITE lv_time TO lv_data USING EDIT MASK '__:__:__'.

MOVE lv_data TO l_value.


r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data2' ).

r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ).

r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

0 Kudos
254

Any idea how to achieve this.

Sandra_Rossi
Active Contributor
0 Kudos
254

Just rename them .txt and upload them...

0 Kudos
254

Note sure why it is not allowing .txtx file. Anyways I am attaching the related code of XML .

<?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"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Chiradip Das</Author>
  <LastAuthor>Chiradip Das</LastAuthor>
  <Created>2020-07-19T12:03:48Z</Created>
  <LastSaved>2020-07-20T17:40:17Z</LastSaved>
  <Version>16.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>11160</WindowHeight>
  <WindowWidth>20730</WindowWidth>
  <WindowTopX>32767</WindowTopX>
  <WindowTopY>32767</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s16">
   <NumberFormat ss:Format="[$-409]h:mm\ AM/PM;@"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="6" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Row>
    <Cell ss:StyleID="s16"><Data ss:Type="DateTime">1899-12-31T12:23:43.000</Data></Cell>
   </Row>
   <Row ss:Index="6">
    <Cell ss:Index="4" ss:StyleID="s16"/>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Selected/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

Sandra_Rossi
Active Contributor
254

As you can see in Excel, if you enter the time "09:34:23" in a cell, it's saved in XML 2003 like this (and not ss:Type="String" that you incorrectly used for some reason... a number format doesn't apply to a cell of type String):

<Cell ss:StyleID="s64"><Data ss:Type="DateTime">1899-12-31T09:34:23.000</Data></Cell>

(1899-12-31 means "day 0" because the type Time doesn't exist, only DateTime exists)

(StyleID refers to a style with the right format, for instance the one you used: '[$-409]h:mm AM/PM;@')

0 Kudos
254

I am not clear what should be the solution. Any lead will be helpful.

Sandra_Rossi
Active Contributor
0 Kudos
254

I'm sorry to not be able to explain clearly. That's so simple. I reuse the answer I posted in another question:

I know nothing about XML 2003, but it just took me 30 seconds to find the solution to your question. It's just a matter of methodology: create one workbook with just one cell with time "09:34:23", create another one with the same time but with the format hh:mmAM/PM (or the one you used), save them as XML 2003, compare the two files, and you get the answer. Ask your company to install a file comparator for you, you need one as a developer. You may even use the standard comparator in ABAP, the one which is used for comparing two versions of ABAP source code...

0 Kudos
254

The issue has been resolved. Thanks for your support.