How to Export Gridview data to Excel with special characters in ASP.NET

Many times you may get requirement to export data to excel with special characters. When you do normal export with special characters your excel file will have weird characters in place of special character.

So in such cases what you have to do is, Unicode encoding. I have given sample code below.

private void ExportToExcel()

      {

          string Excelfilename = "Test_Excel_Export" + DateTime.Now;

          Response.Clear();

          Response.Buffer = true;

          Response.ContentType = "application/vnd.ms-excel";

          Response.AppendHeader("Content-Disposition:", "attachment; filename=" + Excelfilename + ".xls");

          Response.Charset = "";

          Response.ContentEncoding = Encoding.Unicode;

          Response.BinaryWrite(Encoding.Unicode.GetPreamble());

          this.EnableViewState = false;

          System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

          System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

          GridView1.RenderControl(oHtmlTextWriter);

          Response.Write(oStringWriter.ToString());

          Response.End();

      }

In the above code two import lines of code which does the encoding is below,

Response.ContentEncoding = Encoding.Unicode;

Response.BinaryWrite(Encoding.Unicode.GetPreamble());

To use above code you have to add below namespace otherwise you will get error.

using System.Text;

I hope now you are very clear about “How to Export Gridview data to Excel with special characters in ASP.NET”.

You are always welcome to post your queries below.

ExcelDataReader to read excel

ExcelDataReader is a Lightweight library used for reading Microsoft Excel files in .NET.

This Dll will avoid lot of overhead which you may face during Excel operation in your .NET project.

First download the dll using the link, ExcelDataReader DLL Download

Then use below code,

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

 IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

 IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

 DataSet result = excelReader.AsDataSet();

 excelReader.IsFirstRowAsColumnNames = true;

 DataSet result = excelReader.AsDataSet();

 while (excelReader.Read())

 {

   //excelReader.GetInt32(0);

 }

 excelReader.Close();