private void CreateTestCellStyle(
CellStyle cellStyle, out CellStyle listHeaderStyle,
out CellStyle listTextStyle, out CellStyle listNumberStyle, out CellStyle listDateTimeStyle
)
{
CellFont listHeaderFont = cellStyle.Font
.CloneAndSetStyle(cellStyle.Font.Style | FontStyles.IsBold);
listHeaderStyle = cellStyle
.CloneAndSetFont(listHeaderFont)
.CloneAndSetHorizontalAlignment(HorizontalAlignment.Center)
.CloneAndSetBorder(true)
.CloneAndSetFont(listHeaderFont);
listTextStyle = cellStyle
.CloneAndSetBorder(true)
.CloneAndSetHorizontalAlignment(HorizontalAlignment.Left);
listNumberStyle = cellStyle
.CloneAndSetBorder(true)
.CloneAndSetHorizontalAlignment(HorizontalAlignment.Right);
listDateTimeStyle = cellStyle
.CloneAndSetBorder(true)
.CloneAndSetHorizontalAlignment(HorizontalAlignment.Right);
}
private IFont ParseFont(CellFont font) {
// 聽說CellStyle建立太多，可能會出現問題，所以如果有已存在的，就直接使用
if (fonts.ContainsKey(font)) {
return fonts[font];
}
IFont excelFont = workbook.CreateFont();
if (!string.IsNullOrWhiteSpace(font.Name)) {
excelFont.FontName = font.Name;
}
if (font.Size != 0) {
excelFont.FontHeightInPoints = font.Size;
}
if (font.Color != Color.Empty) {
if (excelFont is HSSFFont) {
excelFont.Color = ParseColor(font.Color);
} else {
((XSSFFont)excelFont).SetColor(new XSSFColor(font.Color));
}
}
excelFont.IsBold = (font.Style & FontStyles.IsBold) == FontStyles.IsBold;
excelFont.IsItalic = (font.Style & FontStyles.IsItalic) == FontStyles.IsItalic;
if ((font.Style & FontStyles.HasUnderline) == FontStyles.HasUnderline) {
excelFont.Underline = FontUnderlineType.Single;
}
excelFont.IsStrikeout = (font.Style & FontStyles.IsStrikeout) == FontStyles.IsStrikeout;
fonts.Add(font, excelFont);
return excelFont;
}
public void Constructor_UseAction_ReturnTrue()
{
CellFont cellFont = new CellFont("新細明體", 10, Color.Black, FontStyles.None);
CellStyle cellStyle = new CellStyle(HorizontalAlignment.Center, VerticalAlignment.Middle, false, false, Color.Empty, cellFont);
CreateTestCellStyle(cellStyle, out CellStyle listHeaderStyle,
out CellStyle listTextStyle, out CellStyle listNumberStyle, out CellStyle listDateTimeStyle
);
SpreadsheetConfiguration actual = new SpreadsheetConfiguration(x => {
x.CellStyle = cellStyle;
x.ListHeaderStyle = listHeaderStyle;
x.ListTextStyle = listTextStyle;
x.ListNumberStyle = listNumberStyle;
x.ListDateTimeStyle = listDateTimeStyle;
});
actual.CellStyle.Should().Be(cellStyle);
actual.ListHeaderStyle.Should().Be(listHeaderStyle);
actual.ListTextStyle.Should().Be(listTextStyle);
actual.ListNumberStyle.Should().Be(listNumberStyle);
actual.ListDateTimeStyle.Should().Be(listDateTimeStyle);
}
CT_Font ConvertFont(CellFont font)
{
List <object> item = new List <object>();
List <ItemsChoiceType3> name = new List <ItemsChoiceType3>();
if (font.Name != null)
{
item.Add(new CT_FontName {
val = font.Name
});
name.Add(ItemsChoiceType3.name);
}
if (font.Size.HasValue)
{
item.Add(new CT_FontSize {
val = font.Size.Value
});
name.Add(ItemsChoiceType3.sz);
}
if (font.Bold)
{
item.Add(new CT_BooleanProperty {
val = font.Bold
});
name.Add(ItemsChoiceType3.b);
}
if (font.Italic)
{
item.Add(new CT_BooleanProperty {
val = font.Italic
});
name.Add(ItemsChoiceType3.i);
}
if (font.Shadow)
{
item.Add(new CT_BooleanProperty {
val = font.Shadow
});
name.Add(ItemsChoiceType3.shadow);
}
if (font.Outline)
{
item.Add(new CT_BooleanProperty {
val = font.Outline
});
name.Add(ItemsChoiceType3.outline);
}
if (font.Strike)
{
item.Add(new CT_BooleanProperty {
val = font.Strike
});
name.Add(ItemsChoiceType3.strike);
}
if (font.Underline.HasValue)
{
item.Add(new CT_UnderlineProperty {
val = (ST_UnderlineValues)font.Underline.Value
});
name.Add(ItemsChoiceType3.u);
}
if (font.Script.HasValue)
{
item.Add(new CT_VerticalAlignFontProperty {
val = (ST_VerticalAlignRun)font.Script.Value
});
name.Add(ItemsChoiceType3.vertAlign);
}
if (font.Color != null)
{
item.Add(ConvertColor(font.Color));
name.Add(ItemsChoiceType3.color);
}
return(new CT_Font()
{
Items = item.ToArray(),
ItemsElementName = name.ToArray()
});
}
/// <summary>
/// Initializes a new instance of the <see cref="SpreadsheetConfiguration"/> class.
/// <para> CellFont: 來自Config的預設字型樣式</para>
/// <para> CellStyle: 來自Config的預設樣式</para>
/// <para> ListHeaderFont: 預設格式、粗體</para>
/// <para> ListHeaderStyle: 預設格式、粗體、置中</para>
/// <para> ListTextStyle: 預設格式、置左</para>
/// <para> ListNumberStyle: 預設格式、置右</para>
/// <para> ListDateTimeStyle: 預設格式、置右</para>
/// </summary>
public SpreadsheetConfiguration(string basicPath, string jsonFile = "Spreadsheet.json")
{
IConfigurationRoot config = new ConfigurationBuilder()
.SetBasePath(basicPath)
.AddJsonFile(jsonFile, optional: false, reloadOnChange: true)
.Build();
ChangeToken.OnChange(() => config.GetReloadToken(), () => {
Initialize();
});
Initialize();
void Initialize()
{
ConfigSettings configSettings = config.GetSection("Spreadsheet").Get <ConfigSettings>();
CellSettings cellSettings = configSettings.Cell;
FontStyles style = FontStyles.None;
if (cellSettings.Font.IsBold)
{
style |= FontStyles.IsBold;
}
if (cellSettings.Font.IsItalic)
{
style |= FontStyles.IsItalic;
}
if (cellSettings.Font.HasUnderline)
{
style |= FontStyles.HasUnderline;
}
if (cellSettings.Font.IsStrikeout)
{
style |= FontStyles.IsStrikeout;
}
CellStyle = new CellStyle(
cellSettings.HorizontalAlignment,
cellSettings.VerticalAlignment,
cellSettings.HasBorder,
cellSettings.WrapText,
Color.Empty,
new CellFont(
cellSettings.Font.FontName,
cellSettings.Font.FontSize,
Color.Black,
style
)
);
CellFont listHeaderFont = CellStyle.Font
.CloneAndSetStyle(CellStyle.Font.Style | FontStyles.IsBold);
ListHeaderStyle = CellStyle
.CloneAndSetFont(listHeaderFont)
.CloneAndSetHorizontalAlignment(HorizontalAlignment.Center)
.CloneAndSetBorder(true)
.CloneAndSetFont(listHeaderFont);
ListTextStyle = CellStyle
.CloneAndSetBorder(true)
.CloneAndSetHorizontalAlignment(HorizontalAlignment.Left);
ListNumberStyle = CellStyle
.CloneAndSetBorder(true)
.CloneAndSetHorizontalAlignment(HorizontalAlignment.Right);
ListDateTimeStyle = CellStyle
.CloneAndSetBorder(true)
.CloneAndSetHorizontalAlignment(HorizontalAlignment.Right);
}
}
private void LoadStyles(string path)
{
var styles = ReadFile<CT_Stylesheet>(path);
indexedColors = new List<Color>();
if (styles.colors != null && styles.colors.indexedColors != null)
foreach (var color in styles.colors.indexedColors)
indexedColors.Add(new Color(color.rgb));
borders = new List<CellBorder>();
if (styles.borders!=null && styles.borders.border!=null)
foreach (var border in styles.borders.border)
{
CellBorder b = new CellBorder
{
Bottom = ConvertBorderEdge(border.bottom),
Right = ConvertBorderEdge(border.right),
Left = ConvertBorderEdge(border.left),
Top = ConvertBorderEdge(border.top),
Diagonal = ConvertBorderEdge(border.diagonal),
DiagonalDown = border.diagonalDown,
DiagonalUp = border.diagonalUp,
Vertical = ConvertBorderEdge(border.vertical),
Horizontal = ConvertBorderEdge(border.horizontal),
Outline = border.outline
};
borders.Add(b);
}
fills = new List<CellFill>();
if (styles.fills != null && styles.fills.fill != null)
foreach (var fill in styles.fills.fill)
{
CT_PatternFill pf;
CellFill f = new CellFill();
if (Util.TryCast(fill.Item, out pf))
{
f.Background = ConvertColor(pf.bgColor);
f.Foreground = ConvertColor(pf.fgColor);
f.Pattern = (FillPattern)pf.patternType;
}
fills.Add(f);
}
fonts = new List<CellFont>();
if (styles.fonts!=null && styles.fonts.font!=null)
foreach (var font in styles.fonts.font)
{
CellFont f = null;
if (font.ItemsElementName != null && font.Items != null)
{
f = new CellFont();
for (int i = 0; i < font.ItemsElementName.Length; i++)
{
var item = font.Items[i];
CT_BooleanProperty bp;
switch (font.ItemsElementName[i])
{
case ItemsChoiceType3.name:
CT_FontName name;
if (Util.TryCast(item, out name))
f.Name = name.val;
break;
case ItemsChoiceType3.sz:
CT_FontSize size;
if (Util.TryCast(item, out size))
f.Size = size.val;
break;
case ItemsChoiceType3.b:
if (Util.TryCast(item, out bp))
f.Bold = bp.val;
break;
case ItemsChoiceType3.strike:
if (Util.TryCast(item, out bp))
f.Strike = bp.val;
break;
case ItemsChoiceType3.shadow:
if (Util.TryCast(item, out bp))
f.Shadow = bp.val;
break;
case ItemsChoiceType3.outline:
if (Util.TryCast(item, out bp))
f.Outline = bp.val;
break;
case ItemsChoiceType3.i:
if (Util.TryCast(item, out bp))
f.Italic = bp.val;
break;
case ItemsChoiceType3.u:
CT_UnderlineProperty up;
if (Util.TryCast(item, out up))
f.Underline = (FontUnderline)up.val;
break;
case ItemsChoiceType3.vertAlign:
CT_VerticalAlignFontProperty vafp;
if (Util.TryCast(item, out vafp))
f.Script = (FontScript)vafp.val;
break;
case ItemsChoiceType3.color:
CT_Color1 color;
if (Util.TryCast(item, out color))
f.Color = ConvertColor(color);
break;
}
}
}
fonts.Add(f);
}
if (fonts.Count > 0)
workbook.DefaultFont = fonts[0];
numFmts = new Dictionary<uint, string>();
if (styles.numFmts!=null && styles.numFmts.numFmt!=null)
foreach (var nfmt in styles.numFmts.numFmt)
{
numFmts[nfmt.numFmtId] = nfmt.formatCode;
}
xfs = new List<CellStyle>();
if (styles.cellXfs != null && styles.cellXfs.xf!=null)
foreach (var xf in styles.cellXfs.xf)
{
var entry = new CellStyle();
if (xf.applyBorderSpecified && xf.applyBorder && xf.borderIdSpecified)
entry.Border = GetBorder((int)xf.borderId);
if (xf.applyFillSpecified && xf.applyFill && xf.fillIdSpecified)
entry.Fill = GetFill((int)xf.fillId);
if (xf.applyFontSpecified && xf.applyFont && xf.fontIdSpecified)
entry.Font = GetFont((int)xf.fontId);
if (xf.applyNumberFormatSpecified && xf.applyNumberFormat && xf.numFmtIdSpecified)
entry.Format = GetNumFmt(xf.numFmtId);
if (xf.applyAlignmentSpecified && xf.applyAlignment && xf.alignment != null)
entry.Alignment = ConvertAlignment(xf.alignment);
xfs.Add(entry);
}
dxfs = new List<CellStyle>();
if (styles.dxfs != null && styles.dxfs.dxf != null)
{
foreach (CT_Dxf dxf in styles.dxfs.dxf)
{
var entry = new CellStyle();
if (dxf.font != null)
entry.Font = ConvertDxfFont(dxf.font);
if (dxf.numFmt != null)
entry.format = GetNumFmt(dxf.numFmt.numFmtId);
if (dxf.fill != null)
entry.fill = ConvertDxfCellFill(dxf.fill);
if (dxf.alignment != null)
entry.alignment = ConvertAlignment(dxf.alignment);
if (dxf.border != null)
entry.border = ConvertDxfBorder(dxf.border);
dxfs.Add(entry);
}
}
}
private CellFont ConvertDxfFont(CT_Font font)
{
var cellFont = new CellFont();
List<object> items = font.Items.ToList();
List<ItemsChoiceType3> name = new List<ItemsChoiceType3>();
int counter = 0;
foreach (var item in items)
{
if (font.ItemsElementName[counter] == ItemsChoiceType3.name)
{
var fontName = item as CT_FontName;
if (fontName != null)
cellFont.Name = fontName.val;
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.sz)
{
var fontSize = item as CT_FontSize;
if (fontSize != null)
cellFont.Size = fontSize.val;
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.b)
{
var boldItem = item as CT_BooleanProperty;
if (boldItem != null)
cellFont.Bold = boldItem.val;
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.i)
{
var italicItem = item as CT_BooleanProperty;
if (italicItem != null)
cellFont.Italic = italicItem.val;
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.shadow)
{
var shadowItem = item as CT_BooleanProperty;
if (shadowItem != null)
cellFont.Shadow = shadowItem.val;
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.outline)
{
var outlineItem = item as CT_BooleanProperty;
if (outlineItem != null)
cellFont.Outline = outlineItem.val;
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.strike)
{
var strikeItem = item as CT_BooleanProperty;
if (strikeItem != null)
cellFont.Strike = strikeItem.val;
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.u)
{
var underlineItem = item as CT_UnderlineProperty;
if (underlineItem != null)
cellFont.Underline = (FontUnderline)underlineItem.val;
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.vertAlign)
{
var vertAlignItem = item as CT_VerticalAlignFontProperty;
if (vertAlignItem != null)
cellFont.Script = (FontScript)vertAlignItem.val;
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.color)
{
var colorItem = item as CT_Color1;
if (colorItem != null)
cellFont.Color = ConvertColor(colorItem);
}
counter++;
}
return cellFont;
}
private CellFont ConvertDxfFont(CT_Font font)
{
var cellFont = new CellFont();
List <object> items = font.Items.ToList();
List <ItemsChoiceType3> name = new List <ItemsChoiceType3>();
int counter = 0;
foreach (var item in items)
{
if (font.ItemsElementName[counter] == ItemsChoiceType3.name)
{
var fontName = item as CT_FontName;
if (fontName != null)
{
cellFont.Name = fontName.val;
}
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.sz)
{
var fontSize = item as CT_FontSize;
if (fontSize != null)
{
cellFont.Size = fontSize.val;
}
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.b)
{
var boldItem = item as CT_BooleanProperty;
if (boldItem != null)
{
cellFont.Bold = boldItem.val;
}
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.i)
{
var italicItem = item as CT_BooleanProperty;
if (italicItem != null)
{
cellFont.Italic = italicItem.val;
}
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.shadow)
{
var shadowItem = item as CT_BooleanProperty;
if (shadowItem != null)
{
cellFont.Shadow = shadowItem.val;
}
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.outline)
{
var outlineItem = item as CT_BooleanProperty;
if (outlineItem != null)
{
cellFont.Outline = outlineItem.val;
}
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.strike)
{
var strikeItem = item as CT_BooleanProperty;
if (strikeItem != null)
{
cellFont.Strike = strikeItem.val;
}
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.u)
{
var underlineItem = item as CT_UnderlineProperty;
if (underlineItem != null)
{
cellFont.Underline = (FontUnderline)underlineItem.val;
}
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.vertAlign)
{
var vertAlignItem = item as CT_VerticalAlignFontProperty;
if (vertAlignItem != null)
{
cellFont.Script = (FontScript)vertAlignItem.val;
}
}
if (font.ItemsElementName[counter] == ItemsChoiceType3.color)
{
var colorItem = item as CT_Color1;
if (colorItem != null)
{
cellFont.Color = ConvertColor(colorItem);
}
}
counter++;
}
return(cellFont);
}
private void LoadStyles(string path)
{
var styles = ReadFile <CT_Stylesheet>(path);
indexedColors = new List <Color>();
if (styles.colors != null && styles.colors.indexedColors != null)
{
foreach (var color in styles.colors.indexedColors)
{
indexedColors.Add(new Color(color.rgb));
}
}
borders = new List <CellBorder>();
if (styles.borders != null && styles.borders.border != null)
{
foreach (var border in styles.borders.border)
{
CellBorder b = new CellBorder
{
Bottom = ConvertBorderEdge(border.bottom),
Right = ConvertBorderEdge(border.right),
Left = ConvertBorderEdge(border.left),
Top = ConvertBorderEdge(border.top),
Diagonal = ConvertBorderEdge(border.diagonal),
DiagonalDown = border.diagonalDown,
DiagonalUp = border.diagonalUp,
Vertical = ConvertBorderEdge(border.vertical),
Horizontal = ConvertBorderEdge(border.horizontal),
Outline = border.outline
};
borders.Add(b);
}
}
fills = new List <CellFill>();
if (styles.fills != null && styles.fills.fill != null)
{
foreach (var fill in styles.fills.fill)
{
CT_PatternFill pf;
CellFill f = new CellFill();
if (Util.TryCast(fill.Item, out pf))
{
f.Background = ConvertColor(pf.bgColor);
f.Foreground = ConvertColor(pf.fgColor);
f.Pattern = (FillPattern)pf.patternType;
}
fills.Add(f);
}
}
fonts = new List <CellFont>();
if (styles.fonts != null && styles.fonts.font != null)
{
foreach (var font in styles.fonts.font)
{
CellFont f = null;
if (font.ItemsElementName != null && font.Items != null)
{
f = new CellFont();
for (int i = 0; i < font.ItemsElementName.Length; i++)
{
var item = font.Items[i];
CT_BooleanProperty bp;
switch (font.ItemsElementName[i])
{
case ItemsChoiceType3.name:
CT_FontName name;
if (Util.TryCast(item, out name))
{
f.Name = name.val;
}
break;
case ItemsChoiceType3.sz:
CT_FontSize size;
if (Util.TryCast(item, out size))
{
f.Size = size.val;
}
break;
case ItemsChoiceType3.b:
if (Util.TryCast(item, out bp))
{
f.Bold = bp.val;
}
break;
case ItemsChoiceType3.strike:
if (Util.TryCast(item, out bp))
{
f.Strike = bp.val;
}
break;
case ItemsChoiceType3.shadow:
if (Util.TryCast(item, out bp))
{
f.Shadow = bp.val;
}
break;
case ItemsChoiceType3.outline:
if (Util.TryCast(item, out bp))
{
f.Outline = bp.val;
}
break;
case ItemsChoiceType3.i:
if (Util.TryCast(item, out bp))
{
f.Italic = bp.val;
}
break;
case ItemsChoiceType3.u:
CT_UnderlineProperty up;
if (Util.TryCast(item, out up))
{
f.Underline = (FontUnderline)up.val;
}
break;
case ItemsChoiceType3.vertAlign:
CT_VerticalAlignFontProperty vafp;
if (Util.TryCast(item, out vafp))
{
f.Script = (FontScript)vafp.val;
}
break;
case ItemsChoiceType3.color:
CT_Color1 color;
if (Util.TryCast(item, out color))
{
f.Color = ConvertColor(color);
}
break;
}
}
}
fonts.Add(f);
}
}
if (fonts.Count > 0)
{
workbook.DefaultFont = fonts[0];
}
numFmts = new Dictionary <uint, string>();
if (styles.numFmts != null && styles.numFmts.numFmt != null)
{
foreach (var nfmt in styles.numFmts.numFmt)
{
numFmts[nfmt.numFmtId] = nfmt.formatCode;
}
}
xfs = new List <CellStyle>();
if (styles.cellXfs != null && styles.cellXfs.xf != null)
{
foreach (var xf in styles.cellXfs.xf)
{
var entry = new CellStyle();
if (xf.applyBorderSpecified && xf.applyBorder && xf.borderIdSpecified)
{
entry.Border = GetBorder((int)xf.borderId);
}
if (xf.applyFillSpecified && xf.applyFill && xf.fillIdSpecified)
{
entry.Fill = GetFill((int)xf.fillId);
}
if (xf.applyFontSpecified && xf.applyFont && xf.fontIdSpecified)
{
entry.Font = GetFont((int)xf.fontId);
}
if (xf.applyNumberFormatSpecified && xf.applyNumberFormat && xf.numFmtIdSpecified)
{
entry.Format = GetNumFmt(xf.numFmtId);
}
if (xf.applyAlignmentSpecified && xf.applyAlignment && xf.alignment != null)
{
entry.Alignment = ConvertAlignment(xf.alignment);
}
xfs.Add(entry);
}
}
dxfs = new List <CellStyle>();
if (styles.dxfs != null && styles.dxfs.dxf != null)
{
foreach (CT_Dxf dxf in styles.dxfs.dxf)
{
var entry = new CellStyle();
if (dxf.font != null)
{
entry.Font = ConvertDxfFont(dxf.font);
}
if (dxf.numFmt != null)
{
entry.format = GetNumFmt(dxf.numFmt.numFmtId);
}
if (dxf.fill != null)
{
entry.fill = ConvertDxfCellFill(dxf.fill);
}
if (dxf.alignment != null)
{
entry.alignment = ConvertAlignment(dxf.alignment);
}
if (dxf.border != null)
{
entry.border = ConvertDxfBorder(dxf.border);
}
dxfs.Add(entry);
}
}
}