/// <summary>
/// 字体
/// </summary>
/// <param name="workbook"></param>
/// <param name="size"></param>
/// <param name="weight"></param>
/// <param name="name"></param>
/// <returns></returns>
public static HSSFFont GetFont(HSSFWorkbook workbook, short size, FontBoldWeight weight = FontBoldWeight.NORMAL, string name = "宋体")
{
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontName = name; //设置字体
font.Boldweight = (short)weight; //设置加粗
font.FontHeightInPoints = size; //设置字体大小
return(font);
}
/// <summary>
/// 设置字体格式
/// </summary>
/// <param name="workBook">表格对象</param>
/// <param name="fontName">字体名称</param>
/// <param name="fontHeightInPoints">字体大小</param>
/// <returns></returns>
public IFont SetFont(HSSFWorkbook workBook, FontBoldWeight weight = FontBoldWeight.BOLD, string fontName = "宋体", short fontHeightInPoints = 11)
{
IFont font = workBook.CreateFont();
font.Boldweight = (short)weight;
font.FontName = fontName;
font.FontHeightInPoints = fontHeightInPoints;
return(font);
}
/// <summary>
/// 字体加粗
/// </summary>
/// <param name="cell"></param>
public static void SetBold(this ICell cell, FontBoldWeight bold = FontBoldWeight.Bold)
{
if (cell == null)
{
return;
}
cell.DealParam();
IFont font = cellStyle.GetFont(workBook);
font.Boldweight = (short)bold;
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
}
/// <summary>
/// 創建一個新的單元格格式對象
/// </summary>
public ExcelCellStyle()
{ // ICellStyle e;
_dataFormart = string.Empty;
_fontSize = 9;
_fontBold = FontBoldWeight.None;
_fontName = "Arial";
_alignment = HorizontalAlignment.LEFT;
_verticalAlignment = VerticalAlignment.CENTER;
_borderBottom = BorderStyle.NONE;
_borderLeft = BorderStyle.NONE;
_borderRight = BorderStyle.NONE;
_borderTop = BorderStyle.NONE;
_styleValueIsChange = true;
_cellStyle = null;
_warpText = false;
_underline = Excel.UnderLine.NONE;
_fontColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
_isItalic = false;
_isStrikeout = false;
_fontTypeOffset = 0;
}
private ICellStyle GetCellStyle(IWorkbook wb, FontBoldWeight boldweight)
{
ICellStyle cellStyle = wb.CreateCellStyle();
IFont font = wb.CreateFont();
font.FontHeightInPoints = 10;
font.FontName = "微软雅黑";
font.Color = 32767;
font.Boldweight = (short)boldweight;
cellStyle.SetFont(font);
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.FillForegroundColor = 9;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.WrapText = false;
cellStyle.Indention = 0;
return(cellStyle);
}
/// <summary>
///
/// </summary>
/// <param name="wb"></param>
/// <param name="boldweight"></param>
/// <returns></returns>
private ICellStyle GetCellStyle(IWorkbook wb, FontBoldWeight boldweight)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//字体样式
IFont font = wb.CreateFont();
font.FontHeightInPoints = 10;
font.FontName = "微软雅黑";
font.Color = (short)FontColor.Normal;
font.Boldweight = (short)boldweight;
cellStyle.SetFont(font);
//对齐方式
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//边框样式
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//设置背景色
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
//是否自动换行
cellStyle.WrapText = false;
//缩进
cellStyle.Indention = 0;
return(cellStyle);
}
public void AddCellStyle(String StyleName, String FontName = "Arial", Int16 FontSize = 8,
Boolean IsItalic = false, FontUnderlineType UnderlineType = FontUnderlineType.None,
FontBoldWeight BoldWeight = FontBoldWeight.None, HorizontalAlignment HorizontalAlign = HorizontalAlignment.Left,
VerticalAlignment VerticalAlign = VerticalAlignment.Top, BorderStyle TopBorder = BorderStyle.None,
BorderStyle BottomBorder = BorderStyle.None, BorderStyle RightBorder = BorderStyle.None,
BorderStyle LeftBorder = BorderStyle.None, IndexedColors FontColor = null,
IndexedColors BackgroundColor = null, short HSSFBackgroundColorIndex = 64, byte[] XSSFColorByte = null)
{
IFont font = this.hssworkbook.CreateFont();
font.Color = ((FontColor == null) ? IndexedColors.Black.Index : FontColor.Index);
font.FontName = FontName;
font.FontHeightInPoints = FontSize;
font.IsItalic = IsItalic;
if (font.Underline != FontUnderlineType.None)
{
font.Underline = UnderlineType;
}
font.Boldweight = (short)BoldWeight;
if (this.IsNewFormat)
{
XSSFCellStyle style = (XSSFCellStyle)this.hssworkbook.CreateCellStyle();
style.SetFont(font);
style.Alignment = HorizontalAlign;
style.VerticalAlignment = VerticalAlign;
style.BorderTop = TopBorder;
style.BorderBottom = BottomBorder;
style.BorderRight = RightBorder;
style.BorderLeft = LeftBorder;
if (BackgroundColor != null)
{
style.FillForegroundColor = BackgroundColor.Index;
style.FillPattern = FillPattern.SolidForeground;
}
if (XSSFColorByte != null)
{
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundXSSFColor = new XSSFColor(XSSFColorByte);
}
if (!this.XFontStyle.ContainsKey(StyleName))
{
this.XFontStyle.Add(StyleName, style);
}
else
{
this.XFontStyle[StyleName] = style;
}
}
else
{
HSSFCellStyle style2 = (HSSFCellStyle)this.hssworkbook.CreateCellStyle();
style2.SetFont(font);
style2.Alignment = HorizontalAlign;
style2.VerticalAlignment = VerticalAlign;
style2.BorderTop = TopBorder;
style2.BorderBottom = BottomBorder;
style2.BorderRight = RightBorder;
style2.BorderLeft = LeftBorder;
if (BackgroundColor != null)
{
style2.FillForegroundColor = BackgroundColor.Index;
style2.FillPattern = FillPattern.SolidForeground;
}
else if (HSSFBackgroundColorIndex != 64)
{
style2.FillPattern = FillPattern.SolidForeground;
style2.FillForegroundColor = HSSFBackgroundColorIndex;
}
if (!this.HFontStyle.ContainsKey(StyleName))
{
this.HFontStyle.Add(StyleName, style2);
}
else
{
this.HFontStyle[StyleName] = style2;
}
}
}
/// <summary>
/// 获取字体样式
/// </summary>
/// <param name="hssfworkbook">Excel操作类</param>
/// <param name="fontfamily">字体名</param>
/// <param name="fontcolor">字体颜色</param>
/// <param name="fontsize">字体大小</param>
/// <param name="bold">是否加粗</param>
/// <param name="typeOffset">上下标</param>
/// <returns></returns>
public static IFont GetFontStyle(XSSFWorkbook hssfworkbook, string fontfamily, XSSFColor fontcolor, int fontsize, FontBoldWeight bold = FontBoldWeight.Normal, FontSuperScript typeOffset = FontSuperScript.None)
{
XSSFFont xfont = hssfworkbook.CreateFont() as XSSFFont;
if (string.IsNullOrEmpty(fontfamily))
{
xfont.FontName = fontfamily;
}
if (fontcolor != null)
{
xfont.SetColor(fontcolor);
}
xfont.IsItalic = false;
xfont.FontHeightInPoints = (short)fontsize;
xfont.Boldweight = (short)bold;
xfont.TypeOffset = typeOffset;
return xfont;
}
/// <summary>
/// Sets the font weight.
/// </summary>
/// <param name="cell">The cell.</param>
/// <param name="fontWeight">The font weight.</param>
/// <returns>A <seealso cref="FluentCell"/>, for further styling.</returns>
public static FluentCell FontWeight(this ICell cell, FontBoldWeight fontWeight)
{
return(new FluentCell(cell).FontWeight(fontWeight));
}
/// <summary>
/// 創建一個新的單元格格式對象
/// </summary>
/// <param name="fontSize">字體大小</param>
/// <param name="fontBold">字體字形</param>
/// <param name="fontName">字體名稱</param>
public ExcelCellStyle(int fontSize, FontBoldWeight fontBold, string fontName)
: this(fontSize, fontBold)
{
this.FontName = fontName;
}
//public ExcelCellStyle(int fontSize, string fontName)
// : this(fontSize)
//{
// this._fontName = fontName;
//}
/// <summary>
/// 創建一個新的單元格格式對象
/// </summary>
/// <param name="fontSize">字體大小</param>
/// <param name="fontBold">字體字形</param>
public ExcelCellStyle(int fontSize, FontBoldWeight fontBold)
: this(fontSize)
{
this._fontBold = fontBold;
}
/// <summary>
/// 自定义单元格样式
/// </summary>
/// <returns></returns>
public HSSFCellStyle CreateStyle(HSSFWorkbook hssfworkbook, short fontHeight = 13 * 14,
BorderStyle borderTop = BorderStyle.None, BorderStyle borderBottom = BorderStyle.None, BorderStyle borderLeft = BorderStyle.None, BorderStyle borderRight = BorderStyle.None,
HorizontalAlignment alignment = HorizontalAlignment.Center, VerticalAlignment verticalAlignment = VerticalAlignment.Center,
bool wrapText = false, string fontName = "新宋体", FontBoldWeight boldweight = FontBoldWeight.Normal
)
{
HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
HSSFFont font = (HSSFFont)hssfworkbook.CreateFont();
font.FontName = fontName;
font.FontHeight = fontHeight; //设置字体大小
font.Boldweight = (short)boldweight;
style.SetFont(font); //样式中加载字体样式
style.Alignment = alignment; //水平居中
style.VerticalAlignment = verticalAlignment; //居中
style.BorderBottom = borderBottom; //设置单元格边框
style.BorderRight = borderRight;
style.BorderLeft = borderLeft;
style.BorderTop = borderTop;
style.WrapText = wrapText;
return(style);
}
/// <summary>
/// Set the font weight.
/// </summary>
/// <param name="fontWeight">The font weight.</param>
/// <returns>The cell.</returns>
public FluentCell FontWeight(FontBoldWeight fontWeight)
{
Style.FontWeight = fontWeight;
return(this);
}
/// <summary>
/// Set the font weight.
/// </summary>
/// <param name="fontWeight">The font weight.</param>
/// <returns>The cell.</returns>
public FluentCell FontWeight(FontBoldWeight fontWeight)
{
Style.FontWeight = fontWeight;
return this;
}
private static ICellStyle GetStyle(this IWorkbook workbook, short fontSize = 9, Color color = Color.Black, FontBoldWeight fontBoldWeight = FontBoldWeight.None, string fontName = "Calibri")
{
var font = workbook.GetFont(fontSize, fontName, color, fontBoldWeight);
return(workbook.GetStyle(font));
}
/// <summary>
/// 获取字体样式
/// </summary>
/// <param name="hssfworkbook">Excel操作类</param>
/// <param name="fontfamily">字体名</param>
/// <param name="fontcolor">字体颜色</param>
/// <param name="fontsize">字体大小</param>
/// <param name="bold">是否加粗</param>
/// <param name="typeOffset">上下标</param>
/// <returns></returns>
public static IFont GetFontStyle(XSSFWorkbook hssfworkbook, string fontfamily, XSSFColor fontcolor, int fontsize, FontBoldWeight bold = FontBoldWeight.Normal, FontSuperScript typeOffset = FontSuperScript.None)
{
XSSFFont xfont = hssfworkbook.CreateFont() as XSSFFont;
if (string.IsNullOrEmpty(fontfamily))
{
xfont.FontName = fontfamily;
}
if (fontcolor != null)
{
xfont.SetColor(fontcolor);
}
xfont.IsItalic = false;
xfont.FontHeightInPoints = (short)fontsize;
xfont.Boldweight = (short)bold;
xfont.TypeOffset = typeOffset;
return(xfont);
}
private static IFont GetFont(this IWorkbook workbook, short fontSize, string fontName, Color color = Color.Black, FontBoldWeight fontBoldWeight = FontBoldWeight.None)
{
var font = workbook.CreateFont();
font.FontHeightInPoints = fontSize;
font.FontName = fontName;
font.Boldweight = (short)fontBoldWeight;
font.Color = IndexedColors.ValueOf(color.ToString()).Index;
return(font);
}
private ICellStyle GetCellStyle(IWorkbook wb, FontBoldWeight boldweight)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//字体样式
IFont font = wb.CreateFont();
font.FontHeightInPoints = 10;
font.FontName = "微软雅黑";
font.Color = (short)FontColor.Normal;
font.Boldweight = (short)boldweight;
cellStyle.SetFont(font);
//对齐方式
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//边框样式
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
//设置背景色
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
//是否自动换行
cellStyle.WrapText = false;
//缩进
cellStyle.Indention = 0;
return cellStyle;
}
