Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Bold and Italics not working in excel with EPPLUS

I am using the below code for updating excel data format, here I want the heading to be in bold and entire data in italics format but when I run the code all the features in it seems to be working fine except for Bold and Italics. Code also completes execution without any errors but in the excel file none of the cells are having data in either bold or italics format.

    public void FormatExcel()
    {
        string currentDate = DateTime.Now.ToString("yyyyMMdd");
        FileInfo File = new FileInfo("G:\\Selenium\\Test66.xlsx");
        using (ExcelPackage excel = new ExcelPackage(File))
        {
            ExcelWorksheet worksheet = excel.Workbook.Worksheets[currentDate];
            int totalRows = worksheet.Dimension.End.Row;
            int totalCols = worksheet.Dimension.End.Column;
            var headerCells = worksheet.Cells[1, 1, 1, totalCols];
            var headerFont = headerCells.Style.Font;
            headerFont.Bold = true;
            headerFont.Italic = true;
            headerFont.SetFromFont(new Font("Times New Roman", 12));
            headerFont.Color.SetColor(Color.DarkBlue);
            var headerFill = headerCells.Style.Fill;
            headerFill.PatternType = ExcelFillStyle.Solid;
            headerFill.BackgroundColor.SetColor(Color.Gray);
            var dataCells = worksheet.Cells[2, 1, totalRows, totalCols];
            var dataFont = dataCells.Style.Font;
            dataFont.Italic = true;
            dataFont.SetFromFont(new Font("Times New Roman", 10));
            dataFont.Color.SetColor(Color.DarkBlue);
            var dataFill = dataCells.Style.Fill;
            dataFill.PatternType = ExcelFillStyle.Solid;
            dataFill.BackgroundColor.SetColor(Color.Silver);
            var allCells = worksheet.Cells[1, 1, totalRows, totalCols];
            allCells.AutoFitColumns();
            allCells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            var border = allCells.Style.Border;
            border.Top.Style = border.Left.Style = border.Bottom.Style = border.Right.Style = ExcelBorderStyle.Thin;
            excel.Save();
        }
    }
like image 354
Ash1994 Avatar asked Jul 11 '15 22:07

Ash1994


People also ask

Why is bold not working in Excel?

To check if this issue is due to add-ins, we suggest that you start Excel in safe mode, and then try using the bold button again. To open Excel in safe mode, refer to the following link: Open Office apps in safe mode on a Windows PC. If the issue persists, we recommend that you repair Office.

Does EPPlus work with XLS?

Does EPPlus support XLS? EPPlus does not work with the XLS format.

How do I bold a cell in Excel in C#?

Cells. Font. Bold = true; .

What is EPPlus DLL?

EPPlus is a very helpful open-source 3rd party DLL for writing data to excel. EPPlus supports multiple properties of spreadsheets like cell ranges, cell styling, charts, pictures, shapes, comments, tables, protection, encryption, pivot tables, data validation, conditional formatting, formula calculation, etc.


1 Answers

The problem is you are setting/overwriting the font after you set bold/italic. Just set the font first like this:

headerFont.SetFromFont(new Font("Times New Roman", 12)); //Do this first
headerFont.Bold = true;
headerFont.Italic = true;

or you can even shorten it a bit like this:

headerFont.SetFromFont(new Font("Times New Roman", 12, FontStyle.Italic | FontStyle.Bold));
like image 96
Ernie S Avatar answered Oct 14 '22 08:10

Ernie S