Problems with Adapter module converting Excel file to XML

[Former Member]

Hi All,

I have created an adapter moduel to convert the input excel file to XML and have deployed it. While testing we found that there is an extra empty record with empty elements added additionally. Can anyone help on this?

Below is the convert function used for it.

public byte[] convert(byte src[],String msgType,String nameSpace) throws Exception 
				{
				String xmldata = "";
				try 
				{
					  ByteArrayInputStream byteArr= new ByteArrayInputStream(src);
					  Workbook wb = Workbook.getWorkbook(byteArr);
					  Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Conversion Started.");
					  xmldata ="<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n\n"+ "<ns0:"+msgType+" "+"xmlns:ns0=\""+nameSpace+"\">";
					  Cell[] cells ;
					  Cell[] cellNames ;
					  cellNames = wb.getSheet(0).getRow(4);
					  for(int j=5;j<wb.getSheet(0).getRows();j++)
					  {
							xmldata = xmldata+"<Record>";
							cells = wb.getSheet(0).getRow(j);
							for(int i=1;i<wb.getSheet(0).getColumns();i++)
							{
								
								xmldata = xmldata+"<"+cellNames<i>.getContents().replaceAll(" ","")+">"+cells<i>.getContents()+"</"+cellNames<i>.getContents().replaceAll(" ","")+">";
							}
							xmldata = xmldata+"</Record>";
																
												  }
					   xmldata = xmldata + "</ns0:"+msgType+">";	 
					   					   wb.close();
					 }
				 catch (Exception e)
				 {
					Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Exception in Convert Method.");
				 }
				 return xmldata.getBytes();
				}

Regards,

Malini

Edited by: malini balasubramaniam on Mar 5, 2010 7:28 AM

[Former Member]

Ref:/people/sap.user72/blog/2005/07/04/read-excel-instead-of-xml-through-fileadapter

[santhosh_kumarv]

This could be because of a row at the last position, which appers to be blank but contains some space in it.

Check the cells of the last row for a space. You can add a print statement as shown below to dubug this..

	for(int i=1;i<wb.getSheet(0).getColumns();i++)
	{
			
	xmldata = xmldata+"<"+cellNames<i>.getContents().replaceAll(" ","")+">"+cells<i>.getContents()+"</" ","")+">";
	System.out.println(" Cell Value is " + cells<i>.getContents().replaceAll(" ","space"));
	
	}

~SaNv...

[Former Member]

Hi Santhosh,

I tried the code provided by you.

For last row the values displayed were

cell Value is 
cell Value is

so there were no spaces in the last row.

Regards,

Malini

[Former Member]

Hi,

xmldata = xmldata+"<Record>";

cells = wb.getSheet(0).getRow(j);

for(int i=1;i<wb.getSheet(0).getColumns();i++)

{

* *

xmldata = xmldata"<"cellNames<i>.getContents().replaceAll(" ","")">"cells<i>.getContents()"</" ","")">";

}

xmldata = xmldata+"</Record>";

Can u please change this part of code as

Hi,

cells = wb.getSheet(0).getRow(j);

if(cells.size()>0)

{

xmldata = xmldata+"<Record>";

for(int i=1;i<wb.getSheet(0).getColumns();i++)

{

xmldata = xmldata"<"cellNames<i>.getContents().replaceAll(" ","")">"cells<i>.getContents()"</" ","")">";

}

xmldata = xmldata+"</Record>";

}

Check first if the row is empty...

im nt sure it is cells.size() or cells.length.. please try both

Thanks & Regards

Babu

[garimellavenkata_phaneend]

Hi Malini,

i think there is a syntax error in the given code.

xmldata = xmldatau201D<u201DcellNames<i>.getContents().replaceAll(u201C u201C, u201Cu201D)u201D>u201Dcells<i>.getContents()u201D</u201D u201C,u201Du201D)+u201D>u201D;

please check the above line in your code, which is not properly ended. There is a mismatch in the quotes(").

Regards

Venkat

[former_member187339]

Hi Malini,

If I understood correctly, then you have the Column names in 4th Row of the excel and all the remaining excel rows have data.

I can say you to try this:

>>for(int j=5;j<wb.getSheet(0).getRows()-1;j++)

Regards

Suraj

[Former Member]

Hi Suraj,

Yes It worked. But what is the reason for last row getting added even if there are no elements..

Regs,

Malini

[santhosh_kumarv]

Reason could be one of the cells in the last row having a space. Confirm this with testing the excel with the code that I have given earlier.... if all the file comes with an empty row at the end, then it is wise to have (row-1) in your XML conversion logic.

~SaNv...

[former_member187339]

Hi Malini,

for this you need to open the Excel file and see whether there were any contents in it or not. I have faced a similar problem in which the client process which generate the excel have one extra column. So we have used this logic as suggested to you

Regards

Suraj