EPPlus - Excel as Mail attachment - Column Names are missing

Question

I am using EPPlus. My requirement is to send excel file as an attachment. So I populate the DataTable by defining the Columns & then adding rows.

Everything is working fine. Till the Attachment reaches the Email. But when the Excel file is opened then the Columns which i have defined are missing. The rows are appearing correctly.

.....
MemoryStream ms = new MemoryStream();
ms = DataTableToExcelXlsx(dt, "Attendance");
ms.Position = 0;
Attachment file = new Attachment(ms, "Attendance.xlsx");
message.Attachments.Add(file);
.....
smtp.Send(message);

...

public static MemoryStream DataTableToExcelXlsx(DataTable table, string sheetName)
        {
            MemoryStream Result = new MemoryStream();
            ExcelPackage pack = new ExcelPackage();
            ExcelWorksheet ws = pack.Workbook.Worksheets.Add(sheetName);

            int col = 1;
            int row = 1;
            foreach (DataRow rw in table.Rows)
            {
                foreach (DataColumn cl in table.Columns)
                {
                    if (rw[cl.ColumnName] != DBNull.Value)
                        ws.Cells[row, col].Value = rw[cl.ColumnName].ToString();
                    col++;
                }
                row++;
                col = 1;
            }

            pack.SaveAs(Result);
            return Result;
        }

Why columns are not appearing in the Excel. They are present in the Datatable. What is solution for this issue?

Answer 1

I didn't add the Columns thats why they were not appearing :-

// Columns
            int rowIndex = 1;
            int colIndex = 1;
            foreach (DataColumn dc in table.Columns) //Creating Headings
            {
                var cell = ws.Cells[rowIndex, colIndex];

                //Setting the background color of header cells to Gray
                var fill = cell.Style.Fill;
                fill.PatternType = ExcelFillStyle.Solid;
                fill.BackgroundColor.SetColor(Color.LightGray);

                //Setting Top/left,right/bottom borders.
                var border = cell.Style.Border;
                border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;

                //Setting Value in cell
                cell.Value = dc.ColumnName;

                colIndex++;
            }

            // Rows
            int col = 1;
            int row = 2;
            foreach (DataRow rw in table.Rows)
            {
                foreach (DataColumn cl in table.Columns)
                {
                    if (rw[cl.ColumnName] != DBNull.Value)
                        ws.Cells[row, col].Value = rw[cl.ColumnName].ToString();
                    col++;
                }
                row++;
                col = 1;
            }

            pack.SaveAs(Result);
            return Result;