Hi Team,

I am trying to upload an excel file from a DSP page and then parse the file using Apache POI jars and insert the data in DB. The issue is the excel content is not getting passed to the IS service from the DSP page. Below is the DSP code.

Welcome To The Excel File Upload Page .

<form name="connectForm" id="connectForm" action="fileStatus.dsp" enctype="multipart/form-data" method="POST">


<table>

<tr>
<td><b><font color="red">Select Input File (.xlsx)</font></b></td>
<td>
<input type="file" name="fileData" id="fileData" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" />
</td>
</tr>
<tr>
<td></td>
</tr>
<tr>
<td></td>
</tr>
<tr>
<td><input type="image" src="images/submit-button.png" width="95" height="20"/></td>
</tr>
</table>
<br/>
<br/>

</form>
</BODY>
</HTML>

This dsp is routing it to the “fileStatus.dsp” where I am calling a flow service. Please see the HTML below.

File Status

%invoke excelUtility.flowServices.dsp:readExcelWrapper%

%ifvar fileReadStatus equals('success')%

<table border="1">
<tr>
<td>File Status</td><td>File uploaded successfully</td>
</tr>

</table>

%endinvoke%



</BODY>
</HTML>

Kindly please help.

Regards
Abir Banerjee

Hi Team,

The issue is primarily resolved. The data is coming to the flow service from browser. However , the InputStream data is getting corrupted during transfer.
This is mainly happening for excel files. The code works fine for pdf,txt,jpg files. Anyone if you have faced this please share your inputs on this.

Regards
Abir

Hi Abir,

might it be that an Excel file contains some characters which are not valid for transport at this place.

There is a Sample in the Samples Section of TechCommunity.
You might to check it out to see if this one can handle Excel files correctly.

Regards,
Holger

Hi Holger,

Thanks for your response. Are you referring to the below sample?

http://techcommunity.softwareag.com/ecosystem/communities/public/webmethods/products/esb_and_integration/codesamples/2595931b-b1d4-11e4-8d00-cd8d7ef22065/?title=Saving+file%2Fattachment+from+a+HTTP+Post+based+on+file+size+restrictions

Well I have tried this one also. When I am doing a http post using “pub.client:http” by passing the excel file , it is working file , the excel is getting successfully written in the receiver server and I am able to open the file. The issue is when I use the HTML page to pass the file to the receiver server. I even added the below mime type in the settings (Resources>Mime Types), still the InputStream gets corrputed. Sending from browser seems to be the issue. The same dsp successfully uploads pdf,txt,jpg files , but not xlsx.

application/vnd.openxmlformats-officedocument.spreadsheetml.sheet xlsx

Please find the dsp code.

Welcome To The Excel File Upload Page .

<form name="connectForm" id="connectForm" action="/invoke/excelUtility.flowServices.dsp:readExcelWrapper" enctype="multipart/form-data" method="POST">



<table>

<tr>

<td><b><font color="red">Select Input File (.xlsx)</font></b></td>
<td>
<input type="file" name="fileData" id="fileData" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" />


</td>
</tr>

<tr>
<td></td>
</tr>
<tr>
<td></td>
</tr>
<tr>
<td><input type="image" src="images/submit-button.png" width="95" height="20"/></td>
</tr>
</table>
<br/>
<br/>

</form>



</BODY>
</HTML>

The receiving service has “contentStream” as input and I am writing the stream to a file in disk. The snapshot of the code is provided as attachment.

Regards
Abir Banerjee

Hi Abir,

as trying to point out earlier, Excel (xslx) files might contain characters which cause the stream to get closed during transmission before the file is complete.

Regards,
Holger

Hi Holger,

I am trying a very simple excel file.There are no special characters in the file. I am attaching the same.
Kindly take a look.

Regards
Abir
test123.zip (5.73 KB)

Hi Abir,

the problem is not with the visible data you enter in the Excel file, instead it is intrinsic caused by th fiile format xlax.

When opening the file with an editor which can display control charactes (like Notepad++) you will see the culprit.
There are some control characters among them which cause the stream to be closed before the file is transferred completey.

Regards,
Holger

Hi Holger,

Is there any workaround/solution for this? As the scenario is working fine when I am using pub.client:http service to send the data. Only when browser is involved , I am seeing this issue and that too for excel files. For pdf , txt ,jpg its working absolutely fine. Is there any content-type/content-handler setting that I can do to correct this?

Regards
Abir Banerjee

Hi Abir,

You may try using “.xls” excel file in the DSP page while uploading.

Regards,
AG

As I have been searching for this … and I couldn’t find good solution - I’ll give you my example.

The DSP page:

The webMethods part:

1. pub.flow:getTransportInfo
2. pub.mime:mergeHeaderAndBody
   map contentStream to body/stream and transport/http/requestHdrs to headerLines
3. pub.mime:createMimeData
   map stream to input
4. pub.mime:getNumParts
5. pub.mime:getBodyPartHeader
   from there you can take mimeHeader/Content-Disposition and parse it to get file name
6. pub.mime:getBodyPartContent
   index 0 will give you content of the file
7. write steam into the file. That I’ve written in Java.

Java service:
String fileName = IDataUtil.getString( pipeline.getCursor(), “filename” );
InputStream in = (InputStream) IDataUtil.get( pipeline.getCursor(), “data” );
// pipeline
try {
OutputStream out = new FileOutputStream(fileName);
// Transfer bytes from in to out
byte buf = new byte[1024];
int len;
while ((len = in.read(buf)) > 0) {
out.write(buf, 0, len);
}
in.close();
out.close();
}
catch (Exception e) {throw new ServiceException(e);}

Hope it will help something in future

Regards,
?ukasz Konkol