Generating and Formatting excelsheet in c# using EPPlus

Getting data from database in DataTable format and then generating Excel sheet and formatting it using EPPlus is very easy in C# MVC. Here I am showing how you can generate excel using DataTable. If you do not want any formatting and just want to genrate Excel file you can do like below.I have created a method which takes 2 parameters first as Datatable and second name of the file including full path. Before get started make sure you have added below namespaces in your controller.

using OfficeOpenXml;
using OfficeOpenXml.Style;

public void GenerateExcel(DataTable DT, string fullFileName)
{
    using (ExcelPackage excel = new ExcelPackage(fullFileName))
    {
        ExcelWorksheet sheetcreate = excel.Workbook.Worksheets.Add("Accounts");
        sheetcreate.Cells["A1"].LoadFromDataTable(DT, true);
        pck.Save();
    }
}

Now if you want to format data inside the excel sheet here is same method with formatting options:

public void GenerateExcel(DataTable DT, string fullFileName)
{
var file = new FileInfo(fullFileName);
    string currentFileName = System.IO.Path.GetFileName(fullFileName);
    ExcelPackage excel = new ExcelPackage(file);
    var sheetcreate = excel.Workbook.Worksheets.Add("Sheet1");
           
    int col = 0;
    foreach (DataColumn column in DT.Columns)  //printing column headings
    {
        sheetcreate.Cells[1,++col].Value = column.ColumnName;
        sheetcreate.Cells[1, col].Style.Font.Bold = true;
        sheetcreate.Cells[1, col].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
        sheetcreate.Cells[1, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
    }
    if (DT.Rows.Count > 0)
    {
        int row = 1;
        decimal checkDecimal;
        for (int eachRow = 0; eachRow < DT.Rows.Count; )    //looping each row
        {

            for (int eachColumn = 1; eachColumn <= col; eachColumn++)   //looping each column in a row
            {
                var eachRowObject = sheetcreate.Cells[row + 1, eachColumn];
                eachRowObject.Style.Fill.PatternType = ExcelFillStyle.Solid;
                eachRowObject.Value = DT.Rows[eachRow][(eachColumn - 1)].ToString();

                if (decimal.TryParse(DT.Rows[eachRow][(eachColumn - 1)].ToString(), out checkDecimal))      //verifying value is number to make it right align
                {
                    eachRowObject.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                }
                eachRowObject.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);     // adding border to each cells

                if (eachRow % 2 == 0)       //alternatively adding color to each cell.
                    eachRowObject.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#e0e0e0"));
                else
                    eachRowObject.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#ffffff"));
            }
            eachRow++;
            row++;
                    
        }        
    }
    sheetcreate.Cells.AutoFitColumns();
    excel.Save();
}

The above code will generate and save excel file in location specified in "fullFileName", but if you want to download immediately after creation replace below code with "excel.save()"

excel.Workbook.Properties.Title = "Attempts";
this.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
this.Response.AddHeader("content-disposition",string.Format("attachment;  filename={0}", "ExcellData.xlsx"));
this.Response.BinaryWrite(excel.GetAsByteArray());

Few tips for formattings:

1) Formatting text, possible values:"Italic", "Bold"

sheetcreate.Cells[1, 1].Style.Font.Bold = true;

2) For merging cells

sheetcreate.Cells[1, 1, 1, 5].Merge = true; //Address "A1:A5"

3) Setting up border

sheetcreate.Cells[1, 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);

4) Setting up cell alignment. It can be "Center", "Right" and "Left".

 sheetcreate.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

5) Setting up background color

 sheetcreate.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#e0e0e0"));

6) To avoid word wrap in excel use blow function. It should be written after creation of excel sheet.

sheetcreate.Cells.AutoFitColumns();