EPPlus Stored Procedure to Excel

Question

Please, give me an opinion, how to make automated table with headers (row1)? I took some of teh code from the Internet, and I adjusted to my needs..

Is tehre a part of a code which I can get automated creation of a table?

I wrote this:

 public void GetExcel()
    {
        StatusPipelineMerge merge = new StatusPipelineMerge();

        var list = merge.Procedure1();
        using (var excelPackage = new ExcelPackage())
        {
            excelPackage.Workbook.Properties.Author = "Web App";
            excelPackage.Workbook.Properties.Title = "Export from the Web";
            var sheet = excelPackage.Workbook.Worksheets.Add("Export Results");
            // output a line for the headers
            //CreateHeader(sheet);
            sheet.Name = "export results";
            // all indexes start at 1
            var rowIndex = 2;
            foreach (var item in list)
            {
                var col = 1;
                sheet.Cells[1, 1].Value = "Client Name";
                sheet.Cells[1, 2].Value = "Field of Cooperation";
                sheet.Cells[1, 3].Value = "Project Value HR";
                sheet.Cells[1, 4].Value = "Project Value Money (EUR)";
                sheet.Cells[1, 5].Value = "Comment";
                sheet.Cells[1, 6].Value = "Sales Responsible";
                sheet.Cells[1, 7].Value = "Created Date";
                sheet.Cells[1, 8].Value = "Modified Date";
                sheet.Cells[1, 9].Value = "Status";

                sheet.Cells[rowIndex, col++].Value = item.ClientName;
                sheet.Cells[rowIndex, col++].Value = item.NameFCO;
                sheet.Cells[rowIndex, col++].Value = item.ProjectValueHr;
                sheet.Cells[rowIndex, col++].Value = item.ProjectValueMoney;
                sheet.Cells[rowIndex, col++].Value = item.CommentPipeline;
                sheet.Cells[rowIndex, col++].Value = item.Name+" "+item.Surname;
                sheet.Cells[rowIndex, col++].Value = item.CreatedTimeSTamp;
                sheet.Cells[rowIndex, col++].Value = item.ModifiedTimeStamp;
                sheet.Cells[rowIndex, col++].Value = item.JobStatusName;
                rowIndex++;
            }
            sheet.Column(3).Style.Numberformat.Format = "$#,##0.00";
            sheet.Column(4).Style.Numberformat.Format = "$#,##0.00";
            // You could just save on ExcelPackage here but we need it in
            // memory to stream it back to the browser
            Response.ClearContent();
            Response.BinaryWrite(excelPackage.GetAsByteArray());
            Response.AddHeader("content-disposition",
                      "attachment;filename=results.xlsx");
            Response.ContentType = "application/excel";
            Response.Flush();
            Response.End();
        }
    }

So, I just need to wrap it in table with headers/filters. Thanks

score 0 · Answer 1 · edited May 23 '17 at 11:44

If you are getting the data from a stored proc, why not use a DataTable and the function LoadFromDataTable()? There are plenty of examples, here is one:

EPPlus LoadFromDataTable() is double escaping ampersands

Keep in mind if it is a very BIG table there is a performance penalty with that function and you are better sticking with what you are doing with for loops.

Another option would be using LoadFromCollection which has the options to print headers as well:

Use 'LoadfromCollection' with a list containing another list inside

EPPlus Stored Procedure to Excel

1 Answers1