This project has moved. For the latest updates, please go here.

_x00D_ showing in Excel by using EPPlus 3.0 librery?

May 29, 2012 at 7:56 PM

I am using EPPlus 3.0  library for download excel file. Everything working fine but some columns showing with "_x00D_" string on data. I researched in google, everybody saying it is newline character. I tried all scenarios still doesn't work. Please my code.

Please advice me.

Using pck As New ExcelPackage()
                    'Create the worksheets
   Dim wsAll As ExcelWorksheet = pck.Workbook.Worksheets.Add("All")
   Dim wsManufacturing As ExcelWorksheet = pck.Workbook.Worksheets.Add("Manufacturing")
   Dim wsHealthcare As ExcelWorksheet = pck.Workbook.Worksheets.Add("Healthcare")
   Dim wsManagement As ExcelWorksheet = pck.Workbook.Worksheets.Add("Management")
   Dim wsSelling As ExcelWorksheet = pck.Workbook.Worksheets.Add("Selling")

   'Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
   wsAll.Cells("A1").LoadFromDataTable(dtAllDownload, True)
   If dtManufacturing.Rows.Count > 0 Then
     wsManufacturing.Cells("A1").LoadFromDataTable(dtManufacturing, True)
   End If
   If dtChinaHealthcare.Rows.Count > 0 Then
     wsHealthcare.Cells("A1").LoadFromDataTable(dtHealthcare, True)
   End If
   If dtChinaManagement.Rows.Count > 0 Then
       wsManagement.Cells("A1").LoadFromDataTable(dtManagement, True)
   End If
   If dtSelling.Rows.Count > 0 Then
      wsSelling.Cells("A1").LoadFromDataTable(dtSelling, True)
   End If

   Dim filename As String = "FullExtract_" & Now.Year.ToString & Now.Month.ToString & Now.Day.ToString & ".xlsx"
    Dim fileBytes As [Byte]() = pck.GetAsByteArray()
    HttpContext.Current.Response.Clear()
    HttpContext.Current.Response.ClearContent()
    HttpContext.Current.Response.ClearHeaders()
    HttpContext.Current.Response.Cookies.Clear()
    HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.[Private])
    HttpContext.Current.Response.CacheControl = "private"
    HttpContext.Current.Response.Charset = System.Text.UTF8Encoding.UTF8.WebName
    HttpContext.Current.Response.ContentEncoding = System.Text.UTF8Encoding.UTF8
    HttpContext.Current.Response.AppendHeader("Content-Length", fileBytes.Length.ToString())
    HttpContext.Current.Response.AppendHeader("Pragma", "cache")
    HttpContext.Current.Response.AppendHeader("Expires", "60")
    HttpContext.Current.Response.AddHeader("content-disposition", "attachment;  filename=" & filename)
    HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    HttpContext.Current.Response.BinaryWrite(fileBytes)
    HttpContext.Current.Response.[End]()

Nov 12, 2012 at 7:42 AM

Sure its a newline character issue, had same issue & fix by downloading the latest version of EpPlus package