public void Format_Background_Text(ExcelRange excelRange, ExcelFillStyle excelFillStyle, ExcelHorizontalAlignment excelHorizontalAlignment, Color color, bool boldText)
{
excelRange.Style.Fill.PatternType = excelFillStyle;
excelRange.Style.Fill.BackgroundColor.SetColor(color);
excelRange.Style.Font.Bold = true;
excelRange.Style.HorizontalAlignment = excelHorizontalAlignment;
}
internal ExcelFillXml(XmlNamespaceManager nameSpaceManager)
: base(nameSpaceManager)
{
_fillPatternType = ExcelFillStyle.None;
_backgroundColor = new ExcelColorXml(NameSpaceManager);
_patternColor = new ExcelColorXml(NameSpaceManager);
}
internal ExcelFillXml(XmlNamespaceManager nameSpaceManager)
: base(nameSpaceManager)
{
_fillPatternType = ExcelFillStyle.None;
_backgroundColor = new ExcelColorXml(NameSpaceManager);
_patternColor = new ExcelColorXml(NameSpaceManager);
}
/// <summary>
/// 设置背景
/// </summary>
/// <param name="worksheet"></param>
/// <param name="style"></param>
/// <param name="img"></param>
/// <param name="showLines"></param>
public static void SetBackground(this ExcelWorksheet worksheet, ExcelFillStyle style, Image img = null, bool showLines = false)
{
// 设置线条样式
worksheet.Cells.Style.Fill.PatternType = style;
// 设置背景图片
worksheet.BackgroundImage.Image = img;
// 设置网格线
worksheet.View.ShowGridLines = showLines;
}
public static void SetColor(this ExcelWorksheet sheet,
int row,
Color color,
ExcelFillStyle fillStyle = ExcelFillStyle.Solid)
{
var cell = sheet.Row(row)
.Style.Fill;
cell.PatternType = fillStyle;
cell.BackgroundColor.SetColor(color);
}
protected void PrepareCells(ExcelWorksheet worksheet, ExcelRange cells, Color fontColor, Color backgroundColor, object value,
ExcelHorizontalAlignment horizontalAlignment = ExcelHorizontalAlignment.Center,
ExcelVerticalAlignment verticalAlignment = ExcelVerticalAlignment.Center,
ExcelFillStyle patternType = ExcelFillStyle.Solid)
{
cells.Merge = true;
cells.Style.VerticalAlignment = verticalAlignment;
cells.Style.HorizontalAlignment = horizontalAlignment;
cells.Style.Font.Bold = true;
cells.Style.Font.Color.SetColor(fontColor);
// Set background color only if specified - it will remove bounding box around cell
if ((backgroundColor != null) && (backgroundColor != Color.Empty))
{
cells.Style.Fill.PatternType = patternType;
cells.Style.Fill.BackgroundColor.SetColor(backgroundColor);
}
// Fill right cell
if (value is string) // For string add text
{
cells.Value = value;
}
else if (value is System.Windows.Controls.Image) // For Image try to insert image - by default it will understand Image as System.Drawing.Image (it is not)
{
System.Windows.Controls.Image parsed = value as System.Windows.Controls.Image;
Image image = parsed.ToDrawingImage();
int rowIndex = cells.Start.Row;
int columnIndex = cells.Start.Column;
ExcelPicture picture = worksheet.Drawings.AddPicture($"Picture for cell [{ rowIndex };{ columnIndex }]", image);
picture.SetPosition(
rowIndex - 1,
(int)(worksheet.Row(rowIndex).Height / 4), // row height center
columnIndex - 1,
(int)(worksheet.Column(columnIndex).Width * 3)); // column width center
}
}
/// <summary>
/// 设置背景着颜色(注意，当ExcelFillStyle.Solid 请不要再设置前景色PatternColor，设置为None将不会填充)
/// </summary>
/// <param name="color"></param>
/// <param name="pattern"></param>
public void SetBackgroundColor(Color color, ExcelFillStyle pattern)
{
if (this._BackgroundColor == null)
{
this._BackgroundColor = new ColorXmlWrapper();
}
if (pattern == ExcelFillStyle.Solid)
{
this._BackgroundColor.Indexed = 64;
if (this._PatternColor == null)
{
this._PatternColor = new ColorXmlWrapper();
}
this._PatternColor.SetColor(color);
}
else
{
this._BackgroundColor.SetColor(color);
}
this.PatternType = pattern;
}
public static void BackgroundColor(this ExcelRange range, Color color, ExcelFillStyle defaultFillStyle = ExcelFillStyle.Solid)
{
range.Style.Fill.PatternType = defaultFillStyle;
range.Style.Fill.BackgroundColor.SetColor(color);
}
public static ExcelFill SetBackgroundColor(this ExcelFill fill, Color color, ExcelFillStyle patternType = ExcelFillStyle.Solid)
{
fill.PatternType = patternType;
fill.BackgroundColor.SetColor(color);
return(fill);
}
public static ExcelRow SetBackgroundColor(this ExcelRow row, Color backgroundColor, ExcelFillStyle fillStyle = ExcelFillStyle.Solid)
{
row.Style.Fill.PatternType = fillStyle;
row.Style.Fill.BackgroundColor.SetColor(backgroundColor);
return(row);
}
public static ExcelWorksheet SetBackgroundColor(this ExcelWorksheet worksheet, ExcelRange cellRange, Color backgroundColor, ExcelFillStyle fillStyle = ExcelFillStyle.Solid)
{
worksheet.Cells[cellRange.Address].Style.Fill.PatternType = fillStyle;
worksheet.Cells[cellRange.Address].Style.Fill.BackgroundColor.SetColor(backgroundColor);
return(worksheet);
}
/// <summary>
/// Sets the background color of ExcelWorksheet cells from a Color object
/// </summary>
/// <param name="worksheet"></param>
/// <param name="backgroundColor"></param>
/// <param name="fillStyle"></param>
/// <returns></returns>
public static ExcelWorksheet SetBackgroundColor(this ExcelWorksheet worksheet, Color backgroundColor, ExcelFillStyle fillStyle = ExcelFillStyle.Solid)
{
worksheet.Cells.SetBackgroundColor(backgroundColor, fillStyle);
return(worksheet);
}
/// <summary>
/// 设置背景着颜色(注意，当ExcelFillStyle.Solid 请不要再设置前景色PatternColor，设置为None将不会填充)
/// </summary>
/// <param name="color"></param>
/// <param name="pattern"></param>
public void SetBackgroundColor(Color color, ExcelFillStyle pattern)
{
if (this._BackgroundColor == null)
{
this._BackgroundColor = new ColorXmlWrapper();
}
if (pattern == ExcelFillStyle.Solid)
{
this._BackgroundColor.Indexed =64;
if (this._PatternColor == null)
{
this._PatternColor = new ColorXmlWrapper();
}
this._PatternColor.SetColor(color);
}
else
{
this._BackgroundColor.SetColor(color);
}
this.PatternType = pattern;
}
/// <summary>
/// Sets the background color of given range from a Color object
/// </summary>
/// <param name="range"></param>
/// <param name="backgroundColor"></param>
/// <param name="fillStyle"></param>
/// <returns></returns>
public static ExcelRangeBase SetBackgroundColor(this ExcelRangeBase range, Color backgroundColor, ExcelFillStyle fillStyle = ExcelFillStyle.Solid)
{
range.Style.SetBackgroundColor(backgroundColor, fillStyle);
return(range);
}
/// <summary>
/// 设置单元格填充样式
/// </summary>
/// <param name="range"></param>
/// <param name="style"></param>
/// <param name="color"></param>
public static void SetFill(this ExcelRange range, ExcelFillStyle style, Color color)
{
range.Style.Fill.PatternType = style;
range.Style.Fill.BackgroundColor.SetColor(color);
}
/// <summary>
/// 设置单元格填充样式
/// </summary>
/// <param name="range"></param>
/// <param name="style"></param>
public static void SetFill(this ExcelRange range, ExcelFillStyle style)
{
range.Style.Fill.PatternType = style;
}
/// <summary>
/// 设置单元格填充样式
/// </summary>
/// <param name="worksheet"></param>
/// <param name="style"></param>
/// <param name="color"></param>
public static void SetFill(this ExcelWorksheet worksheet, ExcelFillStyle style, Color color)
{
worksheet.Cells.Style.Fill.PatternType = style;
worksheet.Cells.Style.Fill.BackgroundColor.SetColor(color);
}
/// <summary>
/// 设置单元格填充样式
/// </summary>
/// <param name="worksheet"></param>
/// <param name="style"></param>
public static void SetFill(this ExcelWorksheet worksheet, ExcelFillStyle style)
{
worksheet.Cells.Style.Fill.PatternType = style;
}
public IStyle CreateStylePatternType(ExcelFillStyle fillStyle)
{
return(new StylePatternType(fillStyle));
}
/// <summary>
/// Sets the background color of ExcelRow from a Color object
/// </summary>
/// <param name="row"></param>
/// <param name="backgroundColor"></param>
/// <param name="fillStyle"></param>
/// <returns></returns>
public static ExcelRow SetBackgroundColor(this ExcelRow row, Color backgroundColor, ExcelFillStyle fillStyle = ExcelFillStyle.Solid)
{
row.Style.SetBackgroundColor(backgroundColor, fillStyle);
return(row);
}
private string SetPatternString(ExcelFillStyle pattern)
{
string newName = Enum.GetName(typeof(ExcelFillStyle), pattern);
return newName.Substring(0, 1).ToLower(CultureInfo.InvariantCulture) + newName.Substring(1, newName.Length - 1);
}
/// <summary>
/// Sets the background color of ExcelColumn from a Color object
/// </summary>
/// <param name="column"></param>
/// <param name="backgroundColor"></param>
/// <param name="fillStyle"></param>
/// <returns></returns>
public static ExcelColumn SetBackgroundColor(this ExcelColumn column, Color backgroundColor, ExcelFillStyle fillStyle = ExcelFillStyle.Solid)
{
column.Style.SetBackgroundColor(backgroundColor, fillStyle);
return(column);
}
/// <summary>
/// Sets background color of Excel style
/// </summary>
/// <param name="thisStyle">The Excel style</param>
/// <param name="color">The color</param>
/// <param name="fillStyle">The fill style of background</param>
/// <returns></returns>
public static ExcelStyle SetBackgroundColor(this ExcelStyle thisStyle, Color color, ExcelFillStyle fillStyle = ExcelFillStyle.Solid)
{
thisStyle.Fill.PatternType = fillStyle;
thisStyle.Fill.BackgroundColor.SetColor(color);
return(thisStyle);
}
internal static string SetPatternString(ExcelFillStyle pattern)
{
string newName = Enum.GetName(typeof(ExcelFillStyle), pattern);
return newName.Substring(0, 1).ToLower() + newName.Substring(1, newName.Length - 1);
}
/// <summary>
/// Set the background to a specific color and fillstyle
/// </summary>
/// <param name="color">The theme color</param>
/// <param name="fillStyle">The fillstyle. Default Solid</param>
public void SetBackground(eThemeSchemeColor color, ExcelFillStyle fillStyle = ExcelFillStyle.Solid)
{
PatternType = fillStyle;
BackgroundColor.SetColor(color);
}
/// <summary>
/// Sets the background color of given cell range from a Color object
/// </summary>
/// <param name="worksheet"></param>
/// <param name="cellRange"></param>
/// <param name="backgroundColor"></param>
/// <param name="fillStyle"></param>
/// <returns></returns>
public static ExcelWorksheet SetBackgroundColor(this ExcelWorksheet worksheet, ExcelRange cellRange, Color backgroundColor, ExcelFillStyle fillStyle = ExcelFillStyle.Solid)
{
worksheet.Cells[cellRange.Address].SetBackgroundColor(backgroundColor, fillStyle);
return(worksheet);
}
/// <summary>
/// Apply conditional formatting style to rule style property
/// </summary>
/// <param name="style"></param>
/// <param name="ssStyle"></param>
internal static void ApplyConditionalFormattingStyle(ExcelDxfStyleConditionalFormatting style, RCConditionalFormatStyleRecord ssStyle)
{
ExcelUnderLineType underline = (ExcelUnderLineType)ssStyle.ssSTConditionalFormatStyle.ssFont.ssSTFontStyle.ssUnderline;
ExcelBorderStyle bTop = (ExcelBorderStyle)ssStyle.ssSTConditionalFormatStyle.ssBorderTop.ssSTBorderStyle.ssStyle;
ExcelBorderStyle bBottom = (ExcelBorderStyle)ssStyle.ssSTConditionalFormatStyle.ssBorderBottom.ssSTBorderStyle.ssStyle;
ExcelBorderStyle bLeft = (ExcelBorderStyle)ssStyle.ssSTConditionalFormatStyle.ssBorderLeft.ssSTBorderStyle.ssStyle;
ExcelBorderStyle bRight = (ExcelBorderStyle)ssStyle.ssSTConditionalFormatStyle.ssBorderRight.ssSTBorderStyle.ssStyle;
ExcelFillStyle patternType = (ExcelFillStyle)ssStyle.ssSTConditionalFormatStyle.ssFill.ssSTFillStyle.ssPatternType;
style.Border.Bottom.Style = bBottom;
if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssBorderBottom.ssSTBorderStyle.ssColor))
{
style.Border.Bottom.Color.Color = ConvertFromColorCode(ssStyle.ssSTConditionalFormatStyle.ssBorderBottom.ssSTBorderStyle.ssColor);
}
style.Border.Left.Style = bLeft;
if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssBorderLeft.ssSTBorderStyle.ssColor))
{
style.Border.Left.Color.Color = ConvertFromColorCode(ssStyle.ssSTConditionalFormatStyle.ssBorderLeft.ssSTBorderStyle.ssColor);
}
style.Border.Right.Style = bRight;
if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssBorderRight.ssSTBorderStyle.ssColor))
{
style.Border.Right.Color.Color = ConvertFromColorCode(ssStyle.ssSTConditionalFormatStyle.ssBorderRight.ssSTBorderStyle.ssColor);
}
style.Border.Top.Style = bTop;
if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssBorderTop.ssSTBorderStyle.ssColor))
{
style.Border.Top.Color.Color = ConvertFromColorCode(ssStyle.ssSTConditionalFormatStyle.ssBorderTop.ssSTBorderStyle.ssColor);
}
if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssFill.ssSTFillStyle.ssBackgroundColor))
{
style.Fill.BackgroundColor.Color = ConvertFromColorCode(ssStyle.ssSTConditionalFormatStyle.ssFill.ssSTFillStyle.ssBackgroundColor);
}
if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssFill.ssSTFillStyle.ssPatternColor))
{
style.Fill.PatternColor.Color = ConvertFromColorCode(ssStyle.ssSTConditionalFormatStyle.ssFill.ssSTFillStyle.ssPatternColor);
}
style.Fill.PatternType = patternType;
style.Font.Bold = ssStyle.ssSTConditionalFormatStyle.ssFont.ssSTFontStyle.ssBold;
style.Font.Italic = ssStyle.ssSTConditionalFormatStyle.ssFont.ssSTFontStyle.ssItalic;
if (ssStyle.ssSTConditionalFormatStyle.ssFont.ssSTFontStyle.ssStrike)
{
style.Font.Strike = ssStyle.ssSTConditionalFormatStyle.ssFont.ssSTFontStyle.ssStrike;
}
else
{
style.Font.Strike = null;
}
style.Font.Underline = underline;
if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssFont.ssSTFontStyle.ssColor))
{
style.Font.Color.Color = ConvertFromColorCode(ssStyle.ssSTConditionalFormatStyle.ssFont.ssSTFontStyle.ssColor);
}
if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssNumberFormat))
{
style.NumberFormat.Format = ssStyle.ssSTConditionalFormatStyle.ssNumberFormat;
}
}
private string SetPatternString(ExcelFillStyle pattern)
{
string newName = Enum.GetName(typeof(ExcelFillStyle), pattern);
return(newName.Substring(0, 1).ToLower() + newName.Substring(1, newName.Length - 1));
}
public StylePatternType(ExcelFillStyle fillStyle)
{
this.FillStyle = fillStyle;
}
private void AssertFill(Color expectedBackgroundColor, Color expectedPatternColor, ExcelFillStyle expectedFillStyle, ExcelFill fill)
{
AssertFill(expectedBackgroundColor, expectedFillStyle, fill);
Assert.AreEqual(expectedPatternColor, fill.PatternColor);
}
/// <summary>
/// Set the background to a specific color and fillstyle
/// </summary>
/// <param name="color">The indexed color</param>
/// <param name="fillStyle">The fillstyle. Default Solid</param>
public void SetBackground(ExcelIndexedColor color, ExcelFillStyle fillStyle = ExcelFillStyle.Solid)
{
PatternType = fillStyle;
BackgroundColor.SetColor(color);
}