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

Read More »

The device is not ready. Error in Visual Studio - Solved

Hello developers, I am writing this article to give a solution to an error which came across to me recently. I am working on a Console Application where I am saving my files to a system. Everything is working perfectly fine. But suddenly the system starts throwing Error: The device is not ready. I googled and find nothing over this with respect to Visual Studio Console Application.

 I am sharing the file and code on which the error is occurring for future reference.

In Program.cs File

class Program

{

    static void Main(string[] args)

    {

        try

        {

            //Determine whether the ImaginationHunt directory exist

            if (Directory.Exists(System.Configuration.ConfigurationManager.AppSettings["FilePath"]) == false)

            {

                //Creating directory

                DirectoryInfo di = Directory.CreateDirectory(System.Configuration.ConfigurationManager.AppSettings["FilePath"]);

            }

        }

        catch (Exception ex)

        {

            Console.WriteLine(ex.Message);

        }

    }

}

In App.Config File

<appSettings>

    <add key="FilePath" value="F:/ImaginationHunt Reports/" />

</appSettings>

While googling, I noticed that the “Device is not ready” occurs when the Flash drive/HDD is not working. So, I go to the path that I have mentioned in App.Config file. And what I find out is the “F:/” drive is not available means the system is searching for the path is unavailable and hence could not able to find a part of the path which results in throwing the title error.

Solution: I change the path to an existing drive and also put a check on it.

Hope you find the article helpful & interesting.

For any query, comment us below.

Previous – How to: Start Reporting Services Configuration Manager

Next – Types of SQL Server Joins #1

Click imagination hunt to read latest blogs.

Posted By - +Manish Kumar Gautam +LIVE VIAR +ASP.NET SOLUTIONS 

Keep learning and sharing...

Read More »