Wednesday, April 18, 2007

Arabic Problem when Export Data to Excel

Hi Folks,

I faced a problem when you are trying to export arabic data binded in a datagrid or gridview in asp.net 1.x or 2.0 in VS.Net.

The problem is the arabic data is exported as a un-recognized letters "@@@???#######",since my system is arabic enabled and i can write arabic in my machine.

I checked my code for export to excel,the data is being streamed in a right format but when its appearing in excel it shows as un-recognized letters.

NOTE : YOU HAVE TO CHECK THAT YOUR OFFICE IS SUPPORTING ARABIC BY Open :
START MENU-> OFFICE->OFFICE TOOLS-> LANGUAGE SETTINGS.

The problem exists in office 2003 or even in office 2007.

If you want to see export data to excel in C#,refer to my earlier post :
http://moustafa-arafa.blogspot.com/2007/04/export-more-than-1-grid-in-excel-file.html

The problem wasn't in to put the character set of the response to UTF-8 or Windows-1256,the problem was when Response.End() is called it throw an exception because the thread has been aborted,simply what you have to do is just replace Response.End()
line by below line :

HttpContext.Current.ApplicationInstance.CompleteRequest();

/* FULL CODE TO EXPORT TO SUPPORT ARABIC *************//////

Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";//You can set UTF-8 or windows-1256 but this will not solve
this.EnableViewState = false;
StringWriter oStringWriter = new StringWriter();
HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);

MyDGrid.RenderControl(oHtmlTextWriter);

Response.Write(oStringWriter.ToString());

Response.Flush();
HttpContext.Current.ApplicationInstance.CompleteRequest();

/********************************END OF THE CODE ****************************/


Enjoy .Netting :)



Regards,
Moustafa arafa

11 comments:

Ovais said...

i tried this but it doesnt work. i mean it works sometime and most of the times it dont. my database is access and i have data both in english and arabic. hwat could be the problem?help please

Moustafa El-Zoughby said...

I submitted this as a BUG to the VS Product Team,and i didnt get the hotfix or workaround for it.

Moustafa El-Zoughby said...

This is the response i got from VS Product team,do the following :

Response.OutputStream.Write(new byte[] { 0xef, 0xbb, 0xbf }, 0, 3);

write this line of code before writing anything on the response.

let me know if you need any other help.

Na77ool said...

Ok thanks for the code, it did solve my problem with office 2003, but it did not work with office 2000, so can you help me with that?

Rania said...

That's cool, but is there a way to let it open in a new excel window.
With this code, it opens an excel window in the same IE page.

Thank you

Moustafa El-Zoughby said...

You have 2 options:

1)Save : Save it to your local then open it.

2) Open : open on the current page.

in case you want to keep the current page,simply select save option on the dialog box.

Anz said...

Thanks for the code

Response.OutputStream.Write(new byte[] { 0xef, 0xbb, 0xbf }, 0, 3);


It solved my problem with exporting arabic characters.

Best Regards,
Ansar

http://www.FlickrMailer.com

Fadz said...

Thanks...
"HttpContext.Current.ApplicationInstance.CompleteRequest();" did the trick!

Anonymous said...

amazing it solved my problem

Anonymous said...

It works , thanks a lot !!!

la-ilaaj said...

Salam Alaykum,

The CompleteRequest had some problems for me that it showed extra response items in excel, but this line was really a savior;
Response.OutputStream.Write(new byte[] { 0xef, 0xbb, 0xbf }, 0, 3);

Shukran, JazzakAllah khair.
Amjad Kayani