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
*"----
DATA: client TYPE REF TO if_http_client.
DATA: conv TYPE REF TO cl_abap_conv_in_ce.
DATA: rlength TYPE i,
txlen TYPE string.
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.
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'.
CALL METHOD client->request->set_data
EXPORTING
data = excel
offset = 0.
CALL METHOD client->send
EXCEPTIONS
http_communication_failure = 1
http_invalid_state = 2.
CALL METHOD client->receive
EXCEPTIONS
http_communication_failure = 1
http_invalid_state = 2
http_processing_failed = 3.
csvx = client->response->get_data( ).
conv = cl_abap_conv_in_ce=>create(
encoding = '1160'
endian = 'L'
input = csvx
).
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
*"----
DATA: client TYPE REF TO if_http_client.
DATA: conv TYPE REF TO cl_abap_conv_in_ce.
DATA: excel_base64 TYPE string,
return_base64 TYPE string.
DATA: soap_request TYPE string,
xsoap_response TYPE xstring.
CALL FUNCTION 'SSFC_BASE64_ENCODE'
EXPORTING
bindata = excel
IMPORTING
b64data = excel_base64.
CONCATENATE 'cr_lf.
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.
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'.
CALL METHOD client->request->set_cdata
EXPORTING
data = soap_request
offset = 0.
CALL METHOD client->send
EXCEPTIONS
http_communication_failure = 1
http_invalid_state = 2.
CALL METHOD client->receive
EXCEPTIONS
http_communication_failure = 1
http_invalid_state = 2
http_processing_failed = 3.
xsoap_response = client->response->get_data( ).
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 .
CALL FUNCTION 'SCMS_BASE64_DECODE_STR'
EXPORTING
input = base64
IMPORTING
output = csvx.
conv = cl_abap_conv_in_ce=>create(
encoding = '1160'
endian = 'L'
input = csvx
).
CALL METHOD conv->read(
IMPORTING data = csv
).
ENDFUNCTION.
</pre>
