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: 

how to covert csv to Excel 2007

himanshu_gupta2
Participant
0 Kudos

Hello

Can anyone knows that how to convert the CSV file to the EXCEL 2007 format (xlsx ) not to excel 2003(xls) .

12 REPLIES 12

Former Member
0 Kudos

hi,

Just open the spread sheet and select 'Save as' from 'File' menu.

In the box 'Save as type' select *.xlsx. This will save a new copy of the csv in the xlsx format.

Is this you required?

0 Kudos

hello,

If i have to do these steps , why would i post in the SAP forum ???

I need a code to convert .

0 Kudos

well you dont NEED to do these steps, you can open a CSV file with ANY version of Excel. So i still refrain from seeing the problem.

0 Kudos

Well , it is necessary to convet into the EXCEL as customer want the Header information and other criteria to put the search option.

0 Kudos

as far as i know you will not be able to achieve this with ABAP coding.

Maybe there are some programs that run under windows that can convert CSV files into specific excel version files, but that has to be scripted outside of SAP then.

Only way i could think of is using an ALV to display your stuff, andmaking use of the EXCEL in place function of it.

But still you´d had to manually save the file from you excel in place.

Just bare in mind, that i´m NOT the top notch developer so there may be a way i just dont know of.

Edited by: Florian Kemmer on May 12, 2011 1:35 PM

0 Kudos

Hello,

Saving a file out with the extension .xls or .xlsx does not convert the contents into the proprietary Microsoft Excel binary format. It just creates the text file with the extension .xls or .xlsx so as you can click on in directly and Excel will open etc.

SAP does not export text files to binary propirietary formats.

To convert a csv text file to MS Excel, one needs to open Excel and open/point to the text file and you will see a conversion tool in Excel to define fields, denominators etc...

I am unaware of any automation tool persay, but I have seen this automated by way of delimited Text file with a direct (fixed)connect to MS Access -> one then runs an Access query and spits out a native Excel file.

Best of luck

Derrick Hurley

Development Workbench

Former Member
0 Kudos

hmmm i dont really get it.

ANY excel event he one from win 3.11 should be able to handle a plain CSV file.

You can then choose to save it as an excel file in your excel save dialog. like the guy above me told yah.

Former Member
0 Kudos

While passing the file name to GUI_DOWNLOAD pass it with .xlsx extension.

Check this link for more details.

0 Kudos

This seems quite nifty, and hopefully for our original poster it will work out.

But i dont see a possibility to tell the system to which version of excel file you want it to be converted. Maybe that this works for 2007, but i dont think this works in general. E.G. if you´d need a file of excel version 2010.

Former Member
0 Kudos

Hi,

If you want to create a real Excel file, the easiest way is certainly to use XML. With the Microsoft documentation and some retro engeneering you should be able to produce XLS files matching your needs. Creating XLSX is possible (I made it for DOCX files), this is however much more complicated.

Hope this will help you.

REPORT  test.
DATA: gt_spfli         TYPE STANDARD TABLE OF spfli,
      gr_spfli         TYPE REF TO spfli,
      gv_xls_template  TYPE string,
      go_xls_xml       TYPE REF TO cl_xml_document,
      go_table_element TYPE REF TO if_ixml_element,
      go_row_element   TYPE REF TO if_ixml_element,
      go_cell_element  TYPE REF TO if_ixml_element,
      go_data_element  TYPE REF TO if_ixml_element,
      gv_cell_value    TYPE string,
      gv_nb_rows       TYPE string,
      gv_nb_columns    TYPE string.

"1. Get some test data
SELECT * INTO TABLE gt_spfli FROM spfli.

"2. XML Excel file template creation
CONCATENATE `<?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">`
            ` </DocumentProperties>`
            ` <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">`
            `  <WindowHeight>12525</WindowHeight>`
            `  <WindowWidth>15195</WindowWidth>`
            `  <WindowTopX>480</WindowTopX>`
            `  <WindowTopY>120</WindowTopY>`
            `  <ProtectStructure>False</ProtectStructure>`
            `  <ProtectWindows>False</ProtectWindows>`
            ` </ExcelWorkbook>`
            ` <Styles>`
            `  <Style ss:ID="Default" ss:Name="Normal">`
            `   <Alignment ss:Vertical="Bottom"/>`
            `   <Borders/>`
            `   <Font/>`
            `   <Interior/>`
            `   <NumberFormat/>`
            `   <Protection/>`
            `  </Style>`
            ` </Styles>`
            ` <Worksheet ss:Name="Feuil1">`
            `  <Table x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60">`
            `  </Table>`
            `  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">`
            `   <PageSetup>`
            `    <Header x:Margin="0.4921259845"/>`
            `    <Footer x:Margin="0.4921259845"/>`
            `    <PageMargins x:Bottom="0.984251969" x:Left="0.78740157499999996"`
            `     x:Right="0.78740157499999996" x:Top="0.984251969"/>`
            `   </PageSetup>`
            `   <Selected/>`
            `   <ProtectObjects>False</ProtectObjects>`
            `   <ProtectScenarios>False</ProtectScenarios>`
            `  </WorksheetOptions>`
            ` </Worksheet>`
            `</Workbook>` INTO gv_xls_template.

"3. Transform the XML String to a real XML document
CREATE OBJECT go_xls_xml.
go_xls_xml->parse_string( stream = gv_xls_template ).
CLEAR gv_xls_template. "No more required

"4. Retrieve the Table node. We will populate this node with the cell's content later on
go_table_element ?= go_xls_xml->find_node( name = '/Workbook/Worksheet/Table' ).

"5. Populating the file with data
LOOP AT gt_spfli REFERENCE INTO gr_spfli.
  "5.1 Creation of a new <Row> tag
  go_row_element ?= go_xls_xml->create_simple_element( name = 'Row' parent = go_table_element ).

  "5.2. Creation of a new <Cell><Data ss:Type="String">my value</Data></Cell>
  "5.2.1. Carrid to String casting
  gv_cell_value = gr_spfli->carrid.
  "5.2.2. <Cell><Data>$gv_cell_value</Data></Cell>
  go_cell_element ?= go_xls_xml->create_simple_element( name = 'Cell' parent = go_row_element ).
  go_data_element ?= go_xls_xml->create_simple_element( name = 'Data' parent = go_cell_element value = gv_cell_value ).
  "5.2.3. Add type description to the <Data> tag
  go_data_element->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
ENDLOOP.

"6. Add the number of rows and columns to the <Table> tag
gv_nb_rows = LINES( gt_spfli ). "Number of rows to be displayed
gv_nb_columns = 1. "Number of columns to displayed

CONDENSE: gv_nb_columns NO-GAPS, gv_nb_rows NO-GAPS.

go_table_element->set_attribute_ns( name = 'ExpandedColumnCount' prefix = 'ss' value = gv_nb_columns ).
go_table_element->set_attribute_ns( name = 'ExpandedRowCount' prefix = 'ss' value = gv_nb_rows ).

"7. Display or download the file
"go_xls_xml->display( ).
go_xls_xml->export_to_file( 'C:/temp/spfli.xls' ).

gregorw
Active Contributor

Please check out the Code Exchange Project https://cw.sdn.sap.com/cw/groups/abap2xlsx . I think it should solve your problem.

0 Kudos

To convert csv to xlsx I used this free online service