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();
}
}