Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

EPPlus Font Family Not Affected

I'm using asp.net MVC 4 and epplus as a nuget package for exporting my data into an excel file. I do that as the following:

var excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("Consumption");
workSheet.View.RightToLeft = true;
for (var col = 1; col <= totalCols; col++)
{
    workSheet.Cells[1, col].Style.Font.Name = "B Zar";
    workSheet.Cells[1, col].Style.Font.Size = 16;
    workSheet.Cells[1, col].Style.Font.Bold = true;
    workSheet.Cells[1, col].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
    workSheet.Cells[1, col].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
    workSheet.Cells[1, col].Value = ds.Tables[0].Columns[col - 1].ColumnName;
}

for (var row = 1; row <= totalRows; row++)
    for (var col = 0; col < totalCols; col++)
    {
        workSheet.Cells[row + 1, col + 1].Style.Font.Name = "B Zar";
        workSheet.Cells[row + 1, col + 1].Style.Font.Size = 11;
        workSheet.Cells[row + 1, col + 1].Value = ds.Tables[0].Rows[row - 1][col];
    }

workSheet.Cells[1, 1, totalRows + 1, totalCols].Style.Border.Top.Style =
    workSheet.Cells[1, 1, totalRows + 1, totalCols].Style.Border.Bottom.Style =
        workSheet.Cells[1, 1, totalRows + 1, totalCols].Style.Border.Right.Style =
            workSheet.Cells[1, 1, totalRows + 1, totalCols].Style.Border.Left.Style =
                OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells[1, 1, totalRows + 1, totalCols].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

using (var memoryStream = new MemoryStream())
{
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;  filename=Consumptions.xlsx");
    excel.SaveAs(memoryStream);
    memoryStream.WriteTo(Response.OutputStream);
    Response.Flush();
    Response.End();
}

The problem is that when I download the file and open it on Excel 2016, the font family not affected but on the font name box, it appears. If I focus on the combo box and press Enter, the font family will be affected.

How can I solve this problem?

like image 924
Varan Sinayee Avatar asked Oct 13 '16 22:10

Varan Sinayee


People also ask

How do I merge cells in EPPlus?

If you want to merge cells dynamically, you can also use: worksheet. Cells[FromRow, FromColumn, ToRow, ToColumn].

What is EPPlus library?

What is EPPlus? A library to manage Excel spreadsheets. EPPlus is a . NET library, which reads and writes Excel 2007/2010 or higher files, using Open Office XML format. It supports .

Why can't I use font charset with epplus?

This issue occurs because EPPlus (version 4.5.3.2) does not support Font Charset. Font Charset for the selected Font ('B Zar') is ARABIC (=178).

How to apply font style in Excel comment box using epplus?

Apply Font Style in Excel Comment Box using EPPlus? You can set Font style by using Font Property and this Font property has FontName, Size, Bold, Italic, UnderLine, Strike, VerticalAlignproperties. These are properties of ExcelRichText class.

What are some common issues with epplus?

Fixed load/save of .xlsm files not having an vbaproject.bin in the package. EPPlus threw an exception when handling extlist logic for space separated data validations. Copy of comments within the same worksheet caused an ArgumentException when loading the workbook again. Range.Copy of conditional formattings with multiple addresses did not work.

What's new in epplus for Excel?

Fixed issue when EPPlus crashes on load if a pivot table uses an external source. EPPlus will now preserve the 'cm' and 'vm' attributes of the sheet xml - 'c' element Formula calculation returns 0 in some cases when Excel returns null/empty in for example the MIN and MAX functions


2 Answers

Try this:

var allCells = sheet.Cells[1, 1, sheet.Dimension.End.Row, sheet.Dimension.End.Column];
var cellFont = allCells.Style.Font;
cellFont.SetFromFont(new Font("Times New Roman", 12));
cellFont.Bold = true;
cellFont.Italic = true;
like image 51
VDWWD Avatar answered Oct 04 '22 13:10

VDWWD


workSheet.Cells.Style.Font.Name = "Arial Narrow";
workSheet.Cells.Style.Font.Size = 10;

This will affect all rows and columns.

like image 39
Fernando Feliciano Jr. Avatar answered Oct 04 '22 14:10

Fernando Feliciano Jr.