Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Sunday, 29 March 2020

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:


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.



Click imagination hunt to read the latest blogs.

Posted By – Manish Kumar Gautam

No comments:

Post a comment

Featured post

Think that makes you rich and richer

 Napolean said: “You can think and grow rich, but if you can be brought up like most people with work and you won't starve, this wil...