/// <summary>
/// Font
/// </summary>
/// <param name="format"></param>
/// <returns></returns>
private IFont GetFont(FormatItem format)
{
// Font
IFont font = _wb.CreateFont();
try
{
// Color
if (format.TextColor.Length > 0)
{
if (Workbook.Excel.IsOldExcel(Workbook.Excel.FileName))
{
font.Color = GetColorIndex(format.TextColor);
}
else
{
((XSSFFont)font).SetColor(GetColor(format.TextColor));
}
}
// Font Name
if (format.FontName != null && format.FontName.Length > 0)
{
font.FontName = format.FontName;
}
// Size
if (format.FontSize > 0)
{
font.FontHeightInPoints = format.FontSize;
}
else
{
font.FontHeightInPoints = 10;
}
// Bold
if (format.FontBold)
{
font.Boldweight = (short)FontBoldWeight.Bold;
}
// Italic
font.IsItalic = format.FontUnderline;
// Underline
if (format.FontUnderline)
{
font.Underline = FontUnderlineType.Single;
}
}
catch (Exception xcp)
{
Logger.Log(xcp, EventLogEntryType.Error);
throw new ExcelException("NPOIWorksheet GetFont Exception", xcp);
}
return font;
}
private Boolean CreateNamedStyle(FormatItem format, String styleName)
{
Boolean ok = false;
try
{
ExcelNamedStyleXml namedStyle = _wb.Styles.CreateNamedStyle(styleName);
// Color
if (format.BackgroundColor != null && format.BackgroundColor.Length > 0)
{
namedStyle.Style.Fill.PatternType = ExcelFillStyle.Solid;
namedStyle.Style.Fill.BackgroundColor.SetColor(GetColor(format.BackgroundColor));
}
else
{
namedStyle.Style.Fill.PatternType = ExcelFillStyle.None;
}
// Font
FontStyle fontStyle = FontStyle.Regular;
fontStyle = format.FontBold ? fontStyle | FontStyle.Bold : fontStyle | FontStyle.Regular;
fontStyle = format.FontItalic ? fontStyle | FontStyle.Italic : fontStyle | FontStyle.Regular;
fontStyle = format.FontUnderline ? fontStyle | FontStyle.Underline : fontStyle | FontStyle.Regular;
namedStyle.Style.Font.SetFromFont(new Font(format.FontName, format.FontSize, fontStyle));
namedStyle.Style.Font.Color.SetColor(GetColor(format.TextColor));
// Horizontal Align
namedStyle.Style.HorizontalAlignment = GetHorizontalAlign(format.HorizontalAlignValue);
// Vertical Align
namedStyle.Style.VerticalAlignment = GetVerticalAlign(format.VerticalAlignValue);
// Top Border
namedStyle.Style.Border.Top.Style = GetBorderStyle(format.TopBorderStyleValue);
if (namedStyle.Style.Border.Top.Style != ExcelBorderStyle.None)
{
namedStyle.Style.Border.Top.Color.SetColor(GetColor(format.TopBorderColor));
}
// Bottom Border
namedStyle.Style.Border.Bottom.Style = GetBorderStyle(format.BottomBorderStyleValue);
if (namedStyle.Style.Border.Bottom.Style != ExcelBorderStyle.None)
{
namedStyle.Style.Border.Bottom.Color.SetColor(GetColor(format.BottomBorderColor));
}
// Left Border
namedStyle.Style.Border.Left.Style = GetBorderStyle(format.LeftBorderStyleValue);
if (namedStyle.Style.Border.Left.Style != ExcelBorderStyle.None)
{
namedStyle.Style.Border.Left.Color.SetColor(GetColor(format.LeftBorderColor));
}
// Right Border
namedStyle.Style.Border.Right.Style = GetBorderStyle(format.RightBorderStyleValue);
if (namedStyle.Style.Border.Right.Style != ExcelBorderStyle.None)
{
namedStyle.Style.Border.Right.Color.SetColor(GetColor(format.RightBorderColor));
}
ok = true;
}
catch (Exception xcp)
{
Logger.Log(xcp, EventLogEntryType.Error);
throw new ExcelException("EPPlusWorsheet CreateNamedStyle Exception", xcp);
}
return ok;
}
/// <summary>
/// Style
/// </summary>
/// <param name="format"></param>
/// <returns></returns>
private ICellStyle GetStyle(FormatItem format)
{
ICellStyle style = _wb.CreateCellStyle();
try
{
// Color
if (format.BackgroundColor != null && format.BackgroundColor.Length > 0)
{
style.FillPattern = FillPattern.SolidForeground;
if (Workbook.Excel.IsOldExcel(Workbook.Excel.FileName))
{
style.FillForegroundColor = GetColorIndex(format.BackgroundColor);
}
else
{
((XSSFCellStyle)style).SetFillForegroundColor(GetColor(format.BackgroundColor));
}
}
else
{
style.FillPattern = FillPattern.NoFill;
}
// Font
style.SetFont(GetFont(format));
// Horizontal Align
style.Alignment = GetHorizontalAlign(format.HorizontalAlignValue);
// Vertical Align
style.VerticalAlignment = GetVerticalAlign(format.VerticalAlignValue);
// Top Border
style.BorderTop = GetBorderStyle(format.TopBorderStyleValue);
if (Workbook.Excel.IsOldExcel(Workbook.Excel.FileName))
{
style.TopBorderColor = GetColorIndex(format.TopBorderColor);
}
else
{
((XSSFCellStyle)style).SetTopBorderColor(GetColor(format.TopBorderColor));
}
// Bottom Border
style.BorderBottom = GetBorderStyle(format.BottomBorderStyleValue);
if (Workbook.Excel.IsOldExcel(Workbook.Excel.FileName))
{
style.BottomBorderColor = GetColorIndex(format.BottomBorderColor);
}
else
{
((XSSFCellStyle)style).SetBottomBorderColor(GetColor(format.BottomBorderColor));
}
// Left Border
style.BorderLeft = GetBorderStyle(format.LeftBorderStyleValue);
if (Workbook.Excel.IsOldExcel(Workbook.Excel.FileName))
{
style.LeftBorderColor = GetColorIndex(format.LeftBorderColor);
}
else
{
((XSSFCellStyle)style).SetLeftBorderColor(GetColor(format.LeftBorderColor));
}
// Right Border
style.BorderRight = GetBorderStyle(format.RightBorderStyleValue);
if (Workbook.Excel.IsOldExcel(Workbook.Excel.FileName))
{
style.RightBorderColor = GetColorIndex(format.RightBorderColor);
}
else
{
((XSSFCellStyle)style).SetRightBorderColor(GetColor(format.RightBorderColor));
}
}
catch (Exception xcp)
{
Logger.Log(xcp, EventLogEntryType.Error);
throw new ExcelException("NPOIWorkshett GetStyle Exception", xcp);
}
return style;
}
