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?
If you want to merge cells dynamically, you can also use: worksheet. Cells[FromRow, FromColumn, ToRow, ToColumn].
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 .
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).
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.
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.
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
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;
workSheet.Cells.Style.Font.Name = "Arial Narrow";
workSheet.Cells.Style.Font.Size = 10;
This will affect all rows and columns.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With