EPPlus Life Saver Hacks in C#

Hi developers, if you are following my Excel development series and find the EPPlus library really helpful while reading and write excel in C#, then below article is going to be a lifesaver for you.

Naming your sheet

var worksheet = excel.Workbook.Worksheets.Add("Phone Call");

Giving your Sheet Tab a Background color

worksheet.TabColor = System.Drawing.Color.Black;

Styling the rows and columns

worksheet.Row(1).Height = 20;

worksheet.Column(1).Width = 4;

worksheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

worksheet.Row(1).Style.Font.Bold = true;

worksheet.Row(1).Style.Font.Italic = true;

worksheet.Row(1).Style.Font.Name = "Arial"; //Font-Family

worksheet.Row(1).Style.Font.Color.SetColor(System.Drawing.Color.White); //Font-Color

worksheet.Row(1).Style.Fill.PatternType = ExcelFillStyle.Solid;            worksheet.Row(1).Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(0, 176, 240)); //Row Background Color

worksheet.Row(1).Style.WrapText = true; //Wrap Text in Row

worksheet.Column(1).Style.WrapText = true; //Wrap Text in Column

worksheet.Row(1).Style.Border.Top.Style = ExcelBorderStyle.Thin;

worksheet.Row(1).Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

worksheet.Row(1).Style.Border.Left.Style = ExcelBorderStyle.Thin;

worksheet.Row(1).Style.Border.Right.Style = ExcelBorderStyle.Thin;

Setting for Report Header and Footer

worksheet.HeaderFooter.ScaleWithDocument = true;

ExcelHeaderFooterText header = worksheet.HeaderFooter.FirstHeader;

header.LeftAlignedText = "Phone Call List\n & BZone - III";

header.RightAlignedText = DateTime.Now.Date.ToString("MM/dd/yyyy");

ExcelHeaderFooterText footer = worksheet.HeaderFooter.FirstFooter;

footer.RightAlignedText = $"Page {ExcelHeaderFooter.PageNumber} of {ExcelHeaderFooter.NumberOfPages}";

footer.CenteredText = ExcelHeaderFooter.SheetName;

footer.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;

Changing Page Layout

worksheet.View.PageLayoutView = true;

worksheet.Column(10).PageBreak = true;

worksheet.Row(10).PageBreak = true;

Setting printer settings

worksheet.PrinterSettings.FitToWidth = 1;

worksheet.PrinterSettings.FitToHeight = 1;

worksheet.PrinterSettings.FooterMargin = .70M;

worksheet.PrinterSettings.TopMargin = .70M;

worksheet.PrinterSettings.LeftMargin = .60M;

worksheet.PrinterSettings.RightMargin = .60M;

worksheet.PrinterSettings.PaperSize = ePaperSize.A3;

worksheet.PrinterSettings.Orientation = eOrientation.Landscape;

worksheet.PrinterSettings.Scale = 74;

worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"];

worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:E"];

Setting Header in Excel Sheet

worksheet.Cells[1, 1].Value = "S.No";

worksheet.Cells[1, 2].Value = "Call Id";

worksheet.Cells[1, 3].Value = "Company";

worksheet.Cells[1, 4].Value = "Contact";

worksheet.Cells[1, 5].Value = "Phone";

worksheet.Cells["A1:E1"].Style.Font.Color.SetColor(System.Drawing.Color.White);

worksheet.Cells["A1:E1"].Style.Fill.PatternType = ExcelFillStyle.Solid;

worksheet.Cells["A1:E1"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(0, 176, 240));

Styling Cells

worksheet.Cells[1, 1].Value = recordIndex - 1;

worksheet.Cells[1, 1].Style.WrapText = true;

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

worksheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

Setting Cell border

worksheet.Cells["A" + recordIndex + ":E" + recordIndex].Style.Border.Top.Style = ExcelBorderStyle.Thin; //Top

worksheet.Cells["A" + recordIndex + ":E" + recordIndex].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; //Bottom

worksheet.Cells["A" + recordIndex + ":E" + recordIndex].Style.Border.Left.Style = ExcelBorderStyle.Thin; //Left

worksheet.Cells["A" + recordIndex + ":E" + recordIndex].Style.Border.Right.Style = ExcelBorderStyle.Thin; //Right

Setting Column AutoFit

worksheet.Column(1).AutoFit();

worksheet.Column(2).AutoFit();

worksheet.Column(3).AutoFit();

worksheet.Column(4).AutoFit();

worksheet.Column(5).AutoFit();

Way – 1: Writing and Reading Files to/from excel

//Saving File to Excel

FileInfo excelFile1 = new FileInfo(@"E:\DemoEPPLUS.xlsx");

excel.SaveAs(excelFile1);

//Reading File from Excel

var fileBytes = File.ReadAllBytes(@"E:\DemoEPPLUS.xlsx");

Way – 2: Writing and Reading Files to/from excel

//File name with .xlsx extension 

string strPath = @"E:\DemoEPPLUS.xlsx";

//Checking if file exist then create else delete then re-create

if (File.Exists(strPath))

    File.Delete(strPath);

//Creating Excel file

FileStream fileStream = File.Create(strPath);

fileStream.Close();

//Write calls content to excel file 

File.WriteAllBytes(strPath, excel.GetAsByteArray());

  

Disposing Excel Object

excel.Dispose();

Related Questions:

#newtoprgmtry the first program

Ques- What does Dispose() method do?

Ans- It releases all resources held by any managed object that these Excel references.

Hope you find the article helpful and interesting. 

Comment us any other useful properties while developing Excel using EPPlus.

Previous – EPPlus Life Saver Hacks in C#

Next – How to create Excel file in C#

Click imagination hunt to read the latest blogs.

Posted By – Manish Kumar Gautam