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

When using Package. GetAsByteArry the TableStyle is not seen

Apr 26, 2016 at 7:25 PM
Edited Apr 26, 2016 at 7:44 PM
In my code I create a package using
ws.Cells["A3"].LoadFromDataTable(dataTable, true, TableStyles.Light20);

At the end of the method I state
return pck.GetAsByteArray();

Why does the TableStyle not show up in the downloadable XLSX?

full method:

public static Byte[] DataTables(DataTable dataTable, String SheetName, String ReportTitle, out FileInfo info)
    {
        dataTable = SesameCommon.ExportTo.DataTableToExcel2007.UpdateColumnNames(dataTable);
        info = new FileInfo(string.Format("{0}.xlsx", Guid.NewGuid().ToString()));
        using (ExcelPackage pck = new ExcelPackage(info))
        {
            ExcelWorksheet ws = pck.Workbook.Worksheets.Add(SheetName);
            ws.Cells["A1"].Value = string.Format("{0}. [rows: {1}]", ReportTitle, dataTable.Rows.Count);
            ws.Cells[1, 1, 1, dataTable.Columns.Count].Merge = true;
            ws.Cells[1, 1, 1, dataTable.Columns.Count].Style.Font.Bold = true;

            ws.Cells["A3"].LoadFromDataTable(dataTable, true, TableStyles.Light20);

            for (int kolNr = 0; kolNr < dataTable.Columns.Count; kolNr++)
            {
                using (ExcelRange col = ws.Cells[4, kolNr + 1, 4 + dataTable.Rows.Count, kolNr + 1])
                {
                    if (dataTable.Columns[kolNr].DataType == typeof(DateTime))
                    {
                        col.Style.Numberformat.Format = "dd-MMM-yyyy";
                        col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    }
                    else if (dataTable.Columns[kolNr].DataType == typeof(int))
                    {
                        col.Style.Numberformat.Format = "#,0";
                        col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    }
                    else if (dataTable.Columns[kolNr].DataType == typeof(Decimal))
                    {
                        col.Style.Numberformat.Format = "#,0.00";
                        col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    }
                }
            }
            int kolNo = 0;
            int Rowno = 4;
            StyleDataTable(dataTable, ws, kolNo, Rowno);
            // dispose unneeded objects
            dataTable.Dispose();

            ws.Cells[ws.Dimension.Address].AutoFitColumns();
            SetNegativeColors(ws);
            return pck.GetAsByteArray();
        }
    }
ASPX codebehind:
protected void Page_Load(object sender, EventArgs e)
{
    DataTable table = (DataTable)Session["ExcelXmlDataTable"];
    String Sheetname = Session["ExcelXmlSheetName"].ToString();
    string ReportTitle = HttpContext.Current.Session["ExcelXmlReportTitle"].ToString();
    FileInfo myFile;


    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 = UTF8Encoding.UTF8.WebName;
    HttpContext.Current.Response.ContentEncoding = UTF8Encoding.UTF8;

    Byte[] fileBytes = SesameCommon.ExcelXML.WebObjects.DataTables(table, Sheetname, ReportTitle, out myFile);
    HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    HttpContext.Current.Response.AppendHeader("Content-Disposition", string.Format("attachment; filename={0};size={1};", myFile.Name, fileBytes.Length.ToString()));
    HttpContext.Current.Response.AppendHeader("Content-Length", fileBytes.Length.ToString());
    HttpContext.Current.Response.AppendHeader("Pragma", "cache");
    HttpContext.Current.Response.AppendHeader("Expires", "60");

    //Write it back to the client
    HttpContext.Current.Response.BinaryWrite(fileBytes);
    HttpContext.Current.Response.End();
}