Generating and Formatting excelsheet in c# using EPPlus

Posted on August 13, 2018 by Suresh Kamrushi in asp.net, SQL Server

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();
Tagged as , ,