cancel
Showing results for 
Search instead for 
Did you mean: 

Error of Special Characters in Export to Excel

dhruv_shah3
Active Contributor
0 Kudos

Hi All,

I am using [https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/3596] [original link is broken] [original link is broken] [original link is broken]; Blog for exporting data into the Excel file.

But i am getting error whenever my text contains special characters like &

The export is failed when this type of special characters are coming

So how can i remmove this type of errors

So pls help me...asap.

Thanks & Regards,

Dhruv Shah

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

I guess the error you are getting is a Parser error. To solve this problem, before putting the data on the XML file, you have to pass the data through an encoding process. For UTF-8 the code for encoding should be something like.


  public java.lang.String encodeSpecialChar( java.lang.String attributeValue )
  {
    //@@begin encodeSpecialChar()
		//Created By : AVIK SANYAL Date : 28 March 2008.
		//		Check for special characters like "&" in the String.
		//      replace "&" with "&"
		//      Returns the new encoded String.

		try {
			if (attributeValue != null) {
				attributeValue = attributeValue.replaceAll("&", "&");
				attributeValue = attributeValue.replaceAll("<", ""&lt;"");// this is & lt
				attributeValue = attributeValue.replaceAll(">", ""&gt;"");//this is & gt

			} else {

			}

		} catch (Exception ex) {
			//		wdComponentAPI.getMessageManager().reportException(
			//			"Failed to convert the character.",
			//			true);

		}
		//Returns the encoded String.
		return attributeValue;

    //@@end
  }

Try this and let me know if this works. The reason is that in Binary Cache method, the data is passed in an XML format and the MS-Excel application reads this to show it in the spreadsheet. The characters '&', '<' and '>' can cause failure in reading from this file.

Thank You

Avik

Edited by: AVIK SANYAL on Sep 12, 2008 10:45 AM

dhruv_shah3
Active Contributor
0 Kudos

Hi,

I am getting this error:

File Cannot be opened because:

Whitespace is not allowd at this location.

Line 63 Position 23

<Company> Abc xyz & sqa Limited </Company>

Regards,

Dhruv

Answers (6)

Answers (6)

dhruv_shah3
Active Contributor
0 Kudos

Solved it by Using [;

Reply given by Christopher Luke

Former Member
0 Kudos

O ho..... My mistake in typing.....

Add a semi colon after &amp

Edited by: AVIK SANYAL on Sep 12, 2008 2:08 PM

dhruv_shah3
Active Contributor
0 Kudos

Hi,

I already Tried using this before your replied again. but in vain..

Error remains same.

Regards,

Dhruv

Former Member
0 Kudos

Hi,

I would recomend you to use jxl api which is free to use and would not use the xml format and allows you to play with any type of data,

regards

pankaj prasoon

Former Member
0 Kudos

What seems to be the problem Dhruv?????

Former Member
0 Kudos

I have an idea...... To check if the solution works........

Replace the & with &amp in the Wordpad and then Save it as .xls file.

Try to open the saved file with MS-Excel. That should solve the problem.

If you get further errors....... Try to replace the strings with proper encoded characters.......

Then open the file in MS-Excel.

dhruv_shah3
Active Contributor
0 Kudos

Hi,

Its not working mate.

Regards,

Dhruv

Former Member
0 Kudos

Then you have to pass the data through the encoding code that I have sent you. That will convert the & to &amp and the problem will be solved.

If you need any help then do not hesitate.

Former Member
0 Kudos

OK....!!!!!

just download the Excel file (*.xls) and try open with Word-MS. In the document search by the string that you have mentioned in the question. (Use Ctrl +F).

If you see something like "ABC bla bla ba & instead of &amp , then you have to pass the data through the code I've mentioned.

You can get more details on encoding special characters UTF-8 in Google.com or Sun Websites etc.

You can also encode the & in hexadecimal format to get the desired output.

dhruv_shah3
Active Contributor
0 Kudos

Hi,

I xml file is it coming as & only.

Regards,

Dhruv