Skip to Content

Little Patches for the PHP Excel Reader

By default PHP Excel Reader requires the Excel File to be available in the local file system of the Server running the PHP Script. I don’t want to save it to the file system. So I’ve patched PHP Excel Reader with these small hacks:</p>

reader.php

Find the function read which is located in Line 160. Add the additional parameter $sFileString to it. A few lines further you find the call $this->_ole->read. Also add the parameter $sFileString here.


function read($sFileName, $sFileString) {
$errlevel = error_reporting();
error_reporting($errlevel ^ E_NOTICE);
$res = $this->_ole->read($sFileName, $sFileString);

oleread.inc

In this file goto line 40 where you find also a read function. Add the parameter $sFileString also here and insert the if clause which checks if the sFileString variable is filled and fills then the instance variable data with the string.


function read($sFileName, $sFileString){
if ($sFileString != "") {
$this->data = $sFileString;
} else {
// check if file exist and is readable (Darko Miljanovic)
if(!is_readable($sFileName)) {
$this->error = 1;
return false;
}

$this->data = @file_get_contents($sFileName);
}

First attempt: PHP SOAP Web Service

With the good example of the Book “[Web Services mit PHP | http://www.galileocomputing.de/download/dateien/502/galileocomputing_webservices_php.pdf]” I’ve implemented first a PHP SOAP Web Service using the PEAR SOAP Package .</p>

Server

Client

Client in ABAP?

After I’ve implemented the Client in PHP and tested my Server this way I’ve searched how to implement the Client in ABAP. Unfortunately I found out in the documentation that Web AS 6.20 and also Web AS 6.40 ABAP do not support Attachments in Web Services.</p>

Second step: POST Data directly

So I had to find another solution. I’ve found this Forum Topic: HTTP AND PDF file download where Durairaj Athavan Raja explained how to POST a Document using the ABAP HTTP Client Class cl_http_client.</p>

Function Module

With this help I was able to create this function module which acts as a HTTP Client


FUNCTION z_excel_to_csv.

*”—-


“Lokale Schnittstelle:

*”   IMPORTING

*”         VALUE(EXCEL) TYPE   XSTRING

*”   EXPORTING

*”         VALUE(CSV) TYPE   STRING

*”—-


 

  • Type for HTTP Client

   DATA: client TYPE REF TO if_http_client.

  • Type for Conversion of Charsets

   DATA:   conv TYPE REF TO cl_abap_conv_in_ce.

  • Types for the Content length

   DATA: rlength TYPE i,

               txlen TYPE string.

  • Types for the HTTP Connection settings

   DATA: url TYPE string

                 VALUE ‘http://server.test.com/ws/excel2csvServerPlain.php‘,

               errortext TYPE string, “used for error handling

               csvx TYPE xstring.

 

****Create the HTTP client

   CALL METHOD cl_http_client=>create_by_url

       EXPORTING

           url       = url

       IMPORTING

           client = client

       EXCEPTIONS

           OTHERS = 1.

 

  • Set header fields.

   CALL METHOD client->request->set_header_field

       EXPORTING

           name   = ‘~request_method’

           value = ‘POST’.

 

   CALL METHOD client->request->set_header_field

       EXPORTING

           name   = ‘~server_protocol’

           value = ‘HTTP/1.1’.

 

   CALL METHOD client->request->set_header_field

       EXPORTING

           name   = ‘Content-Type’

           value = ‘text/excel’.

 

  • Set request data

   CALL METHOD client->request->set_data

       EXPORTING

           data     = excel

           offset = 0.

 

  • Send the request

   CALL METHOD client->send

       EXCEPTIONS

           http_communication_failure = 1

           http_invalid_state                 = 2.

  • Retrieve the result

   CALL METHOD client->receive

       EXCEPTIONS

           http_communication_failure = 1

           http_invalid_state                 = 2

           http_processing_failed         = 3.

 

  • Get the return as binary data

   csvx = client->response->get_data( ).

  • Instanciate the Converter and submit the binary data

   conv = cl_abap_conv_in_ce=>create(

                     encoding = ‘1160’

                     endian = ‘L’

                     input = csvx

                 ).

  • Retrieve the converted data

   CALL METHOD conv->read(

       IMPORTING data = csv

   ).

 

ENDFUNCTION.

</pre>

PHP Server

On the other side of the line this Server code waits to be called:


// PHP Excel Reader from http://sourceforge.net/projects/phpexcelreader
require_once('Excel/reader.php');
// Class for our Service
class Service {
// The actual converting Function
function Excel2CSV($datei) {
if($datei == '') {
$fault = 'You must supply a valid string!';
return $fault;
} else {
// A new PHP Excel Reader instance
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('UTF-8');
$data->read('',$datei);
// create CSV from the returned DATA
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
$ret .= ''.$data->sheets[0]['cells'][$i][$j].',';
}
$ret .= " ";
}
// Return the CSV
return $ret;
}
}
}
// Handle only POST requests
if (isset($_SERVER['REQUEST_METHOD']) &&
$_SERVER['REQUEST_METHOD']=='POST') {
// Create a new instance of of our Service
$service = new Service();
// Convert the Data to CSV
$ret = $service->Excel2CSV($HTTP_RAW_POST_DATA);
print $ret;
}
 
?>

3rd possibility: Base64 encode binary data

During this week I had to provide a PDF file from our R/3 Archive via a Web Service. There I found the 3rd possibility to provide binary data via a Web Service: Base64 encoding.

Server

The Server implementation just needs a little change in the Interface definition of the Constructor:


// Constructor
function Service() {
$this->__dispatch_map['Excel2CSV'] =
array('in' => array('input' => 'string'),
'out' => array('output' => 'string'),
);
}

And an adoption of the conversion Function:


// The actual converting Function
function Excel2CSV($import) {
if($import == '') {
/* The SOAP error is generated by the SOAP_Fault class: */
$fault = new SOAP_Fault('You must supply a valid string!','12345');
return $fault->message();
} else {
// Decode Base64
$import = base64_decode($import);
// A new PHP Excel Reader instance
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('UTF-8');
$data->read('', $import);
// create CSV from the returned DATA
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
$ret .= ''.$data->sheets[0]['cells'][$i][$j].',';
}
$ret .= " ";
}
// Return the CSV as and encode the htmlentities
return base64_encode($ret);
}
}

Client

On this ABAP Client I’ve worked two sleepless nights because CALL TRANSFORMATION has a different behaviour on our 6.20 Unicode System than on the other systems where I’ve tested the transformation of the Geocode Business Partner with Google Maps. I’ve used a variable of type STRING for the XML input. According to the documentation there should be no difference. But there is a difference. With the STRING I’ve always got the error: “No valid source context supplied”. This was solved using a variable from type XSTRING. OSS told me that for transformations always a XSTRING should be used.</p>

XSLT – Z_SOAP_EXCEL2CSV

Function Module


FUNCTION z_excel_to_csv_ws.

*”—-


“Local interface:

*”   IMPORTING

*”         VALUE(EXCEL) TYPE   XSTRING

*”   EXPORTING

*”         VALUE(CSV) TYPE   STRING

*”—-


 

  • Type for HTTP Client

   DATA: client TYPE REF TO if_http_client.

  • Type for Conversion of Charsets

   DATA:   conv TYPE REF TO cl_abap_conv_in_ce.

  • Type for Base64 Encoded Excel File

   DATA: excel_base64 TYPE string,

               return_base64 TYPE string.

  • Type for the SOAP Request and Response

   DATA: soap_request TYPE string,

               xsoap_response TYPE xstring.

 

  • Encode Excel to Base64

   CALL FUNCTION ‘SSFC_BASE64_ENCODE’

       EXPORTING

           bindata = excel

       IMPORTING

           b64data = excel_base64.

 

  • Wrap Base64 encoded Excel into SOAP Request

   CONCATENATE ‘cr_lf.

 

  • Types for the HTTP Connection settings

   DATA: url TYPE string

                 VALUE ‘http://server.test.com/ws/excel2csvServerBase64.php‘,

               errortext TYPE string, “used for error handling

               csvx TYPE xstring.

 

****Create the HTTP client

call method cl_http_client=>create_by_url

   exporting

       url       = url

   importing

       client = client

   exceptions

       others = 1.

 

  • Set header fields.

   CALL METHOD client->request->set_header_field

       EXPORTING

           name   = ‘~request_method’

           value = ‘POST’.

 

   CALL METHOD client->request->set_header_field

       EXPORTING

           name   = ‘Content-Type’

           value = ‘text/xml; charset=UTF-8’.

 

  • Set request data

   CALL METHOD client->request->set_cdata

       EXPORTING

           data     = soap_request

           offset = 0.

 

  • Send the request

   CALL METHOD client->send

       EXCEPTIONS

           http_communication_failure = 1

           http_invalid_state                 = 2.

  • Retrieve the result

   CALL METHOD client->receive

       EXCEPTIONS

           http_communication_failure = 1

           http_invalid_state                 = 2

           http_processing_failed         = 3.

 

  • Get the return as xstring

   xsoap_response = client->response->get_data( ).

 

  • Parse with Z_SOAP_EXCEL2CSV

   DATA: xslt_err TYPE REF TO cx_xslt_exception.

   DATA: base64 TYPE string.

   TRY.

           CALL TRANSFORMATION z_soap_excel2csv

                           SOURCE XML xsoap_response

                           RESULT base64 = base64.

       CATCH cx_xslt_exception INTO xslt_err.

           csv = xslt_err->get_text( ).

           EXIT.

 

   ENDTRY .

  • Decode Base64 to CSV

   CALL FUNCTION ‘SCMS_BASE64_DECODE_STR’

       EXPORTING

           input   = base64

       IMPORTING

           output = csvx.

 

  • Instanciate the Converter and submit the binary data

   conv = cl_abap_conv_in_ce=>create(

                     encoding = ‘1160’

                     endian = ‘L’

                     input = csvx

                 ).

  • Retrieve the converted data

   CALL METHOD conv->read(

       IMPORTING data = csv

   ).

 

ENDFUNCTION.

</pre>

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply