private Font BuildFont(int fontSize, string hexBinaryColor, string fontName, bool bold = false, bool italic = false)
{
var font = new Font();
if (bold)
{
font.AppendChild(new Bold());
}
if (italic)
{
font.AppendChild(new Italic());
}
font.AppendChild(new FontSize()
{
Val = fontSize
});
font.AppendChild(new Color()
{
Rgb = new HexBinaryValue()
{
Value = hexBinaryColor
}
});
font.AppendChild(new FontName()
{
Val = fontName
});
return(font);
}
private Fonts CreateFonts()
{
var defaultFont = new Font();
var boldFont = new Font();
boldFont.AppendChild(new Bold());
var fonts = new Fonts();
AppendWithIndexSave(fonts, defaultFont, ExcelStylesheetFontIndex.Default);
AppendWithIndexSave(fonts, boldFont, ExcelStylesheetFontIndex.Bold);
return(fonts);
}
private void AddFontToCellFormat(ExcelCellStyle item, CellFormat cellFormat)
{
if (item.Font != null)
{
var font = new Font();
if (!string.IsNullOrWhiteSpace(item.Font.FontName))
{
font.AppendChild(new FontName {
Val = item.Font.FontName
});
}
if (item.Font.Size.HasValue)
{
font.AppendChild(new FontSize {
Val = item.Font.Size.Value
});
}
if (item.Font.Color != null)
{
font.AppendChild(new Color {
Rgb = item.Font.Color.ColorHexCode
});
}
if (item.Font.IsBold)
{
font.AppendChild(new Bold {
Val = true
});
}
if (item.Font.IsItalic)
{
font.AppendChild(new Italic {
Val = true
});
}
if (item.Font.IsUnderline)
{
font.AppendChild(new Underline {
Val = UnderlineValues.Single
});
}
cellFormat.FontId = GetElementIndexOrAdd(this.fonts, font);
cellFormat.ApplyFont = true;
}
}
///<summary>
///Returns a new Font object from the style information provided
///</summary>
public Font ToFont()
{
Font font = new Font();
if (Italic != null)
{
font.AppendChild <Italic>(new Italic());
}
if (Bold != null)
{
font.AppendChild <Bold>(new Bold());
}
if (Underline != null)
{
font.AppendChild <Underline>(new Underline());
}
if (Color != null)
{
font.AppendChild <Color>(Color.CloneElement <Color>());
}
if (FontSize != null)
{
font.AppendChild <FontSize>(FontSize.CloneElement <FontSize>());
}
if (FontName != null)
{
font.AppendChild <FontName>(FontName.CloneElement <FontName>());
}
if (FontFamily != null)
{
font.AppendChild <FontFamily>(FontFamily.CloneElement <FontFamily>());
}
if (FontScheme != null)
{
font.AppendChild <FontScheme>(FontScheme.CloneElement <FontScheme>());
}
return(font);
}
public void CompareFontTest()
{
Font x = new Font();
Font y = new Font();
FontSize size1 = new FontSize();
FontSize size2 = new FontSize();
Color color1 = new Color();
Color color2 = new Color();
FontName fontName1 = new FontName();
FontName fontName2 = new FontName();
FontFamily fontFamily1 = new FontFamily();
FontFamily fontFamily2 = new FontFamily();
FontScheme fontScheme1 = new FontScheme();
FontScheme fontScheme2 = new FontScheme();
color1.Rgb = "FFFF0000";
color2.Rgb = "FFFF0000";
size1.Val = 12;
size2.Val = 12;
fontName1.Val = "Calibri";
fontName2.Val = "Calibri";
fontFamily1.Val = 2;
fontFamily2.Val = 2;
fontScheme1.Val = FontSchemeValues.Minor;
fontScheme2.Val = FontSchemeValues.Minor;
x.AppendChild <Italic>(new Italic());
x.AppendChild <FontSize>(size1);
x.AppendChild <Color>(color1);
x.AppendChild <FontName>(fontName1);
x.AppendChild <FontFamily>(fontFamily1);
x.AppendChild <FontScheme>(fontScheme1);
y.AppendChild <Italic>(new Italic());
y.AppendChild <FontSize>(size2);
y.AppendChild <Color>(color2);
y.AppendChild <FontName>(fontName2);
y.AppendChild <FontFamily>(fontFamily2);
y.AppendChild <FontScheme>(fontScheme2);
//check they match
Assert.IsTrue(SpreadsheetStyle.CompareFont(x, y), "Equal fonts do not compare.");
//change a value
size2.Val = 13;
Assert.IsFalse(SpreadsheetStyle.CompareFont(x, y), "Unequal fonts do compare.");
}
private static Font GetNewFont(FontInfo fontInfo, Boolean ignoreMod = true)
{
var font = new Font();
var bold = (fontInfo.Font.BoldModified || ignoreMod) && fontInfo.Font.Bold ? new Bold() : null;
var italic = (fontInfo.Font.ItalicModified || ignoreMod) && fontInfo.Font.Italic ? new Italic() : null;
var underline = (fontInfo.Font.UnderlineModified || ignoreMod) &&
fontInfo.Font.Underline != XLFontUnderlineValues.None
? new Underline {Val = fontInfo.Font.Underline.ToOpenXml()}
: null;
var strike = (fontInfo.Font.StrikethroughModified || ignoreMod) && fontInfo.Font.Strikethrough
? new Strike()
: null;
var verticalAlignment = fontInfo.Font.VerticalAlignmentModified || ignoreMod
? new VerticalTextAlignment {Val = fontInfo.Font.VerticalAlignment.ToOpenXml()}
: null;
var shadow = (fontInfo.Font.ShadowModified || ignoreMod) && fontInfo.Font.Shadow ? new Shadow() : null;
var fontSize = fontInfo.Font.FontSizeModified || ignoreMod
? new FontSize {Val = fontInfo.Font.FontSize}
: null;
var color = fontInfo.Font.FontColorModified || ignoreMod ? GetNewColor(fontInfo.Font.FontColor) : null;
var fontName = fontInfo.Font.FontNameModified || ignoreMod
? new FontName {Val = fontInfo.Font.FontName}
: null;
var fontFamilyNumbering = fontInfo.Font.FontFamilyNumberingModified || ignoreMod
? new FontFamilyNumbering {Val = (Int32)fontInfo.Font.FontFamilyNumbering}
: null;
if (bold != null)
font.AppendChild(bold);
if (italic != null)
font.AppendChild(italic);
if (underline != null)
font.AppendChild(underline);
if (strike != null)
font.AppendChild(strike);
if (verticalAlignment != null)
font.AppendChild(verticalAlignment);
if (shadow != null)
font.AppendChild(shadow);
if (fontSize != null)
font.AppendChild(fontSize);
if (color != null)
font.AppendChild(color);
if (fontName != null)
font.AppendChild(fontName);
if (fontFamilyNumbering != null)
font.AppendChild(fontFamilyNumbering);
return font;
}
private static Stylesheet SetDefault()
{
//Fonts
var fonts = new Fonts();
var font0 = new Font();
var fontSize0 = new FontSize {
Val = 10
};
font0.AppendChild(fontSize0);
var font1 = new Font();
var fontSize1 = new FontSize {
Val = 12
};
var fontBold1 = new Bold();
var fontColor1 = new Color {
Rgb = "FFFFFF"
};
font1.AppendChild(fontSize1);
font1.AppendChild(fontBold1);
font1.AppendChild(fontColor1);
fonts.AppendChild(font0);
fonts.AppendChild(font1);
// Alignments
var alignmentCenter = new Alignment();
alignmentCenter.Horizontal = HorizontalAlignmentValues.Center;
//Fill
var fills = new Fills();
var fill0 = new Fill();
var patternFill0 = new PatternFill {
PatternType = PatternValues.None
};
fill0.Append(patternFill0);
var fillSkip = new Fill(); // Not valid needs to skip
var patternFillSkip = new PatternFill {
PatternType = PatternValues.None
};
fillSkip.Append(patternFillSkip);
var fill1 = new Fill();
var patternFill1 = new PatternFill {
PatternType = PatternValues.Solid
};
var foregroundColor1 = new ForegroundColor {
Rgb = "00000000"
};
var backgroundColor1 = new BackgroundColor {
Indexed = 64U
};
patternFill1.Append(foregroundColor1);
patternFill1.Append(backgroundColor1);
fill1.Append(patternFill1);
fills.Append(fill0);
fills.Append(fillSkip);
fills.Append(fill1);
//Border
var borders = new Borders();
var border0 = new Border();
var border1 = new Border();
borders.AppendChild(border0);
borders.AppendChild(border1);
var cellFormats = new CellFormats(
new CellFormat {
FontId = 0, FillId = 0, BorderId = 0, ApplyFill = true
}, // Index 0
new CellFormat {
FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true, Alignment = alignmentCenter
} // Index 1
);
return(new Stylesheet(fonts, fills, borders, cellFormats));
}
void WriteDefaultStyles()
{
var stylePart = _document.WorkbookPart.AddNewPart<WorkbookStylesPart>();
stylePart.Stylesheet = new Stylesheet();
stylePart.Stylesheet.CellFormats = new CellFormats();
var cellFormatZero = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
};
stylePart.Stylesheet.CellFormats.AppendChild(cellFormatZero);
stylePart.Stylesheet.CellStyleFormats = new CellStyleFormats();
var cellStyleFormatZero = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
};
stylePart.Stylesheet.CellStyleFormats.AppendChild(cellStyleFormatZero);
stylePart.Stylesheet.Fonts = new Fonts();
var fonts = stylePart.Stylesheet.Fonts;
var font = new Font();
font.AppendChild(new FontSize { Val = 11 });
font.AppendChild(new Color { Theme = 1 });
font.AppendChild(new FontName { Val = "Calibri" });
font.AppendChild(new FontScheme { Val = FontSchemeValues.Minor });
fonts.AppendChild(font);
stylePart.Stylesheet.Fills = new Fills();
stylePart.Stylesheet.Fills.AppendChild(new Fill(new PatternFill
{
PatternType = PatternValues.None,
}));
stylePart.Stylesheet.Borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
stylePart.Stylesheet.Borders.AppendChild(border);
}
