1

I am exporting an excel using ClosedXML and a datatable and it's works well. My issue is that the encoding is messing with my language e.g.: "æ" shows as

æ

I've tried quite a few things like adding:

Response.Charset = "UTF-8";
Response.ContentEncoding = Encoding.Unicode;

or changing the stream to bytes with encoding:

MemoryStream memoryStream = new MemoryStream();
wb.SaveAs(memoryStream);
memoryStream.Position = 0;
byte[] bytes = memoryStream.ToArray();
char[] chars = Encoding.UTF8.GetChars(bytes);
Response.BinaryWrite(UTF8Encoding.UTF8.GetBytes(chars));

but I keep ending up with the missing encoding or damaged files. I don't know what else to try so I was hoping you could direct me in the right path?

My code:

using (XLWorkbook wb = new XLWorkbook())
{
    var dt = new DataTable("Bookinglist");
    foreach (TableCell cell in gv.HeaderRow.Cells)
    {
        dt.Columns.Add(cell.Text);
    }

    foreach (GridViewRow row in gv.Rows)
    {
        dt.Rows.Add();
        for (int i = 0; i < row.Cells.Count; i++)
        {
            dt.Rows[dt.Rows.Count - 1][i] = row.Cells[i].Text;
        }
    }

    wb.Worksheets.Add(dt);

    Response.Clear();
    Response.Buffer = true;
    Response.Charset = "UTF-8";
    Response.ContentEncoding = Encoding.Unicode;
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment; filename=BookingList.xlsx");

    using (MemoryStream ms = new MemoryStream())
    {
        wb.SaveAs(ms);
        ms.WriteTo(Response.OutputStream);
        Response.Flush();
        Response.End();
    }
}
Ghost
  • 313
  • 1
  • 2
  • 14
  • 1
    That is [HTML encoding](https://en.wikipedia.org/wiki/Character_encodings_in_HTML) and probably comes from the `GridView` before you put the text in the `DataTable`. It has noting to do with ClosedXML or the `MemoryStream`. Look at other questions like [this](https://stackoverflow.com/q/2168649/2610249). – Raidri Jul 10 '19 at 09:51
  • Perfect, I used the solution: HttpUtility.HtmlDecode() to fix the texts before adding them to the datatable. Thank you plenty. :) – Ghost Jul 10 '19 at 10:31

0 Answers0