CODEWITHSUNDEEP
c#closedxml
Joel
Joel

Reputation: 65

Getting value hexadecimal 0x02, character not valid

I am getting the following error:

, value hexadecimal 0x02, caracter not valid.

using this code:

using (XLWorkbook wb = new XLWorkbook())
{
  wb.Worksheets.Add(dt1);
  Response.Clear();
  Response.Buffer = true;
  Response.Charset ="";    
  Response.ContentType = "application/vnd.openxmlformats-     officedocument.spreadsheetml.sheet";
  //Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx");
  Response.AddHeader("content-disposition", "attachment;filename=" + filename);
  using (MemoryStream MyMemoryStream = new MemoryStream())
  {
      wb.SaveAs(MyMemoryStream);
      MyMemoryStream.WriteTo(Response.OutputStream);
      Response.Flush();
      Response.End();
  }

}

I have tried different ways, but I still get the same error. "dt1" is filled from stored procedure.

Upvotes: 1

Views: 2728

Answers (1)

MethodMan
MethodMan

Reputation: 18863

to use ClosedXML I am currently doing the following and I call this method to open the Excel from a web page.

to call the ExportToExcel_SomeReport you would do it like this I create a public static class called Extensions

Extensions.ExportToXcel_SomeReport(dt1, fileName, this.Page);//Call the method on your button click 

//this will be in the static public class you create
internal static void ExportToXcel_SomeReport(DataTable dt, string fileName, Page page)
{
    var recCount = dt.Rows.Count;
    fileName = string.Format(fileName, DateTime.Now.ToString("MMddyyyy_hhmmss"));
    var xlsx = new XLWorkbook();
    var ws = xlsx.Worksheets.Add("Some Custom Report");
    ws.Style.Font.Bold = true;
    ws.Cell("C5").Value = "Some Custom Header Report";
    ws.Cell("C5").Style.Font.FontColor = XLColor.Black;
    ws.Cell("C5").Style.Font.SetFontSize(16.0);
    ws.Cell("E5").Value = DateTime.Now.ToString("MM/dd/yyyy HH:mm");
    ws.Range("C5:E5").Style.Font.SetFontSize(16.0);
    ws.Cell("A7").Value = string.Format("{0} Records", recCount);
    ws.Style.Font.Bold = false;
    ws.Cell(9, 1).InsertTable(dt.AsEnumerable());
    ws.Row(9).InsertRowsBelow(1);
   // ws.Style.Font.FontColor = XLColor.Gray;
    ws.Columns("1-8").AdjustToContents();
    ws.Tables.Table(0).ShowAutoFilter = true;
    ws.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
    DynaGenExcelFile(fileName, page, xlsx);
}

private static void DynaGenExcelFile(string fileName, Page page, XLWorkbook xlsx)
{
    page.Response.ClearContent();
    page.Response.ClearHeaders();
    page.Response.ContentType = "application/vnd.ms-excel";
    page.Response.AppendHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName));

    using (MemoryStream memoryStream = new MemoryStream())
    {
        xlsx.SaveAs(memoryStream);
        memoryStream.WriteTo(page.Response.OutputStream);
        memoryStream.Close();
    }
    page.Response.Flush();
    page.Response.End();
}

Upvotes: 0

Related Questions