05-12-2011 9:23 AM
Hello
Can anyone knows that how to convert the CSV file to the EXCEL 2007 format (xlsx ) not to excel 2003(xls) .
05-12-2011 9:52 AM
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?
05-12-2011 10:24 AM
hello,
If i have to do these steps , why would i post in the SAP forum ???
I need a code to convert .
05-12-2011 10:31 AM
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.
05-12-2011 12:08 PM
Well , it is necessary to convet into the EXCEL as customer want the Header information and other criteria to put the search option.
05-12-2011 12:35 PM
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
05-12-2011 2:48 PM
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
05-12-2011 9:56 AM
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.
05-12-2011 12:43 PM
05-12-2011 12:51 PM
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.
05-13-2011 9:18 AM
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' ).
06-29-2012 4:08 PM
Please check out the Code Exchange Project https://cw.sdn.sap.com/cw/groups/abap2xlsx . I think it should solve your problem.
01-11-2021 8:39 AM