created excel document has xml inside of cells

Jun 12, 2010 at 3:38 PM

The header rows appear to be coming in correctly, but the content in the cells is coming in with the open xml formatting appearing in the cells. any suggestions?

Here's the code I'm using.

    Sub DumpExcel(ByVal tbl As DataTable)
        Using pck As New ExcelPackage()
            'Create the worksheet
            Dim ws As ExcelWorksheet = pck.Workbook.Worksheets.Add("Demo")

            'Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
            ws.Cells("A1").LoadFromDataTable(tbl, True)

            'Format the header for column 1-3
            Using rng As ExcelRange = ws.Cells("A1:Z1")
                rng.Style.Font.Bold = True
                rng.Style.Fill.PatternType = ExcelFillStyle.Solid
                'Set Pattern for the background to Solid
                rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189))
                'Set color to dark blue
                rng.Style.Font.Color.SetColor(System.Drawing.Color.White)
            End Using

            'Example how to Format Column 1 as numeric 
            Using col As ExcelRange = ws.Cells(2, 1, 2 + tbl.Rows.Count, 1)
                col.Style.Numberformat.Format = "#,##0.00"
                col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right
            End Using

            'Write it back to the client

            Response.Clear()
            Response.AddHeader("content-disposition", "attachment;  filename=" & tbl.TableName & ".xlsx")
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            Response.BinaryWrite(pck.GetAsByteArray())
            Response.End()

        End Using
    End Sub

Jun 12, 2010 at 3:39 PM

btw, here's a sample of the cell content


<Cell ss:StyleID="s65"><Data ss:Type="String">May / 7</Data></Cell>

Coordinator
Jun 13, 2010 at 6:52 PM

Sounds strange.

The LoadFromDataset only sets the values from the datatable.

What kind of data do you have in the datatable? 

Jan

Jun 14, 2010 at 1:11 PM
Nevermind... I figured it out. The data was being run through a filter that someone had used to attempt to write excel xml. Once I removed that filter it came out fine.