public void AddAlignment(string name, HorizontalAlignmentValues hv, VerticalAlignmentValues vv, string c)
{
var filepath = getOutputDir() + name;
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(filepath, true)) {
var iter = spreadSheet.WorkbookPart.WorksheetParts.GetEnumerator();
iter.MoveNext();
var workSheetPart = iter.Current;
Cell cell = InsertCellInWorksheet(GetColumnName(c), GetRowIndex(c), workSheetPart);
Fills fs = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Fills;
var cf = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = 0, FontId = 0,
FillId = (UInt32)(fs.Elements <Fill>().Count() - 1),
BorderId = 0,
FormatId = 0,
Alignment = new Alignment()
{
Horizontal = hv, Vertical = vv
},
ApplyFill = true
};
cf.Append(cellFormat2);
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
cell.StyleIndex = (UInt32)(spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Elements <CellFormat>().Count() - 1);
workSheetPart.Worksheet.Save();
}
}
///<summary>
///Sets the vertical alignment value
///</summary>
public void SetVerticalAlignment(VerticalAlignmentValues value)
{
if (VerticalAlignment == null)
{
VerticalAlignment = new EnumValue <VerticalAlignmentValues>();
}
VerticalAlignment.Value = value;
}
public StyleExcel(StyleFont font, StyleFill fill, CellFormat format, VerticalAlignmentValues vertical, HorizontalAlignmentValues horizontal, bool iswordWrap, bool isBorder)
{
Font = font;
Fill = fill;
Format = format;
Vertical = vertical;
Horizontal = horizontal;
IsWordWrap = iswordWrap;
IsBorder = isBorder;
}
public OpenXmlExStyleCell(KeyValuePair <uint, OpenXmlExStyleFont> Font,
KeyValuePair <uint, OpenXmlExStyleFill> Fill,
KeyValuePair <uint, OpenXmlExStyleBorderGrand> Border,
bool Wrap, HorizontalAlignmentValues h_align, VerticalAlignmentValues v_align)
{
FontStyle = Font;
FillStyle = Fill;
BorderStyle = Border;
HorizontalAlignment = h_align;
VerticalAlignment = v_align;
WrapText = Wrap;
}
private void SetAllNull()
{
this.vHorizontal = HorizontalAlignmentValues.General;
this.HasHorizontal = false;
this.vVertical = VerticalAlignmentValues.Bottom;
this.HasVertical = false;
this.TextRotation = null;
this.WrapText = null;
this.Indent = null;
this.RelativeIndent = null;
this.JustifyLastLine = null;
this.ShrinkToFit = null;
this.vReadingOrder = SLAlignmentReadingOrderValues.LeftToRight;
this.HasReadingOrder = false;
}
private void SetAllNull()
{
vHorizontal = HorizontalAlignmentValues.General;
HasHorizontal = false;
vVertical = VerticalAlignmentValues.Bottom;
HasVertical = false;
TextRotation = null;
WrapText = null;
Indent = null;
RelativeIndent = null;
JustifyLastLine = null;
ShrinkToFit = null;
vReadingOrder = SLAlignmentReadingOrderValues.LeftToRight;
HasReadingOrder = false;
}
private void SetAllNull()
{
this.vHorizontal = HorizontalAlignmentValues.General;
this.HasHorizontal = false;
this.vVertical = VerticalAlignmentValues.Bottom;
this.HasVertical = false;
this.TextRotation = null;
this.WrapText = null;
this.Indent = null;
this.RelativeIndent = null;
this.JustifyLastLine = null;
this.ShrinkToFit = null;
this.vReadingOrder = SLAlignmentReadingOrderValues.LeftToRight;
this.HasReadingOrder = false;
}
/// <summary>
/// 水直方向のアライメントを指定します。
/// </summary>
/// <param name="value">アライメント</param>
/// <returns>スタイル（メソッドチェーン用）</returns>
public SpreadsheetStyle SetVerticalAlignment(VerticalAlignmentValues value)
{
Alignment.Vertical = value;
return(this);
}
public Stylesheet CreateStylesheet()
{
try
{
HorizontalAlignmentValues leftHorizontal = HorizontalAlignmentValues.Left;
HorizontalAlignmentValues rightHorizontal = HorizontalAlignmentValues.Right;
HorizontalAlignmentValues centerHorizontal = HorizontalAlignmentValues.Center;
VerticalAlignmentValues topVertical = VerticalAlignmentValues.Top;
VerticalAlignmentValues centerVertical = VerticalAlignmentValues.Center;
return(new Stylesheet(
new Fonts(
/*Index 0 - Black*/ CreateFont("000000", false),
/*Index 1 - Bold Black*/ CreateFont("000000", true),
/*Index 2 - Purple*/ CreateFont("660066", false),
/*Index 3 - Bold Purple*/ CreateFont("660066", true),
/*Index 4 - Red*/ CreateFont("990000", false),
/*Index 5 - Bold Red*/ CreateFont("990000", true),
/*Index 6 - Orange*/ CreateFont("FF6600", false),
/*Index 7 - Bold Orange*/ CreateFont("FF6600", true),
/*Index 8 - Blue*/ CreateFont("0066FF", false),
/*Index 9 - Bold Blue*/ CreateFont("0066FF", true),
/*Index 10 - Green*/ CreateFont("339900", false),
/*Index 11 - Bold Green*/ CreateFont("339900", true),
/*Index 12 - Bold Black Large*/ CreateFont("000000", true)
),
new Fills(
/*Index 0 - Default Fill (None)*/ CreateFill(string.Empty, PatternValues.None),
/*Index 1 - Default Fill (Gray125)*/ CreateFill(string.Empty, PatternValues.Gray125),
/*Index 2 - Dark Gray Fill*/ CreateFill("BBBBBB", PatternValues.Solid),
/*Index 3 - Light Gray Fill*/ CreateFill("EEEEEE", PatternValues.Solid),
/*Index 4 - Yellow Gray Fill*/ CreateFill("FFCC00", PatternValues.Solid)
),
new Borders(
/*Index 0 - Default Border (None)*/ CreateBorder(false, false, false, false),
/*Index 1 - All Borders*/ CreateBorder(true, true, true, true),
/*Index 2 - Top & Bottom Borders*/ CreateBorder(true, false, true, false)
),
new CellFormats(
/*Index 0 - Black Font, No Fill, No Borders, Wrap Text*/
CreateCellFormat(0, 0, 0, leftHorizontal, null, true),
/*Index 1 - Black Font, No Fill, No Borders, Horizontally Centered*/
CreateCellFormat(0, 0, 0, centerHorizontal, null, false),
/*Index 2 - Bold Black Font, Dark Gray Fill, All Borders*/
CreateCellFormat(1, 2, 1, null, null, false),
/*Index 3 - Bold Black Font, Dark Gray Fill, Top & Bottom Borders, Centered*/
CreateCellFormat(1, 2, 2, centerHorizontal, centerVertical, false),
/*Index 4 - Bold Black Font, Dark Gray Fill, All Borders, Centered*/
CreateCellFormat(1, 2, 1, centerHorizontal, centerVertical, false),
/*Index 5 - Bold Purple Font, Light Gray Fill, Top & Bottom Borders, Centered*/
CreateCellFormat(3, 3, 2, centerHorizontal, centerVertical, false),
/*Index 6 - Bold Red Font, Light Gray Fill, Top & Bottom Borders, Centered*/
CreateCellFormat(5, 3, 2, centerHorizontal, centerVertical, false),
/*Index 7 - Bold Orange Font, Light Gray Fill, Top & Bottom Borders, Centered*/
CreateCellFormat(7, 3, 2, centerHorizontal, centerVertical, false),
/*Index 8 - Bold Blue Font, Light Gray Fill, Top & Bottom Borders, Centered*/
CreateCellFormat(9, 3, 2, centerHorizontal, centerVertical, false),
/*Index 9 - Bold Green Font, Light Gray Fill, Top & Bottom Borders, Centered*/
CreateCellFormat(11, 3, 2, centerHorizontal, centerVertical, false),
/*Index 10 - Purple Font, No Fill, Top & Bottom Borders, Centered*/
CreateCellFormat(2, 0, 2, centerHorizontal, centerVertical, false),
/*Index 11 - Red Font, No Fill, Top & Bottom Borders, Centered*/
CreateCellFormat(4, 0, 2, centerHorizontal, centerVertical, false),
/*Index 12 - Orange Font, No Fill, Top & Bottom Borders, Centered*/
CreateCellFormat(6, 0, 2, centerHorizontal, centerVertical, false),
/*Index 13 - Blue Font , No Fill, Top & Bottom Borders, Centered*/
CreateCellFormat(8, 0, 2, centerHorizontal, centerVertical, false),
/*Index 14 - Green Font, No Fill, Top & Bottom Borders, Centered*/
CreateCellFormat(10, 0, 2, centerHorizontal, centerVertical, false),
/*Index 15 - Bold Black Font, Yellow Fill, All Borders, Centered, Wrap Text*/
CreateCellFormat(1, 4, 1, centerHorizontal, centerVertical, true),
/*Index 16 - Bold Black Font, No Fill, All Borders, Wrap Text*/
CreateCellFormat(1, 0, 1, centerHorizontal, centerVertical, true),
/*Index 17 - Bold Black Font, Light Gray Fill, Top & Bottom Borders, Centered*/
CreateCellFormat(1, 3, 2, centerHorizontal, centerVertical, false),
/*Index 18 - Bold Black Font, No Fill, Top & Bottom Borders, Centered*/
CreateCellFormat(0, 0, 2, centerHorizontal, centerVertical, false),
/*Index 19 - Bold Black Font, Dark Gray Fill, Top & Bottom Borders, Centered Vertically*/
CreateCellFormat(1, 2, 1, null, centerVertical, false),
/*Index 20 - Black Font, No Fill, All Borders, Top Aligned, Wrap Text*/
CreateCellFormat(0, 0, 1, null, topVertical, true),
/*Index 21 - Black Font, No Fill, All Borders, Centered Vertically, Wrap Text*/
CreateCellFormat(0, 0, 1, null, centerVertical, true),
/*Index 22 - Black Font, No Fill, All Borders, Centered, Wrap Text*/
CreateCellFormat(0, 0, 1, centerHorizontal, centerVertical, true),
/*Index 23 - Black Font, No Fill, All Borders, Centered Vertically, Right Aligned*/
CreateCellFormat(0, 0, 1, rightHorizontal, centerVertical, false),
/*Index 24 - Black Font, No Fill, All Borders, Centered Horizontally, Top Aligned, Wrap Text*/
CreateCellFormat(0, 0, 1, centerHorizontal, topVertical, true),
/*Index 25 - Bold Black Font, No Fill, No Borders, Wrap Text*/
CreateCellFormat(1, 0, 0, leftHorizontal, topVertical, false),
/*Index 26 - Bold Black Font, Dark Gray Fill, All Borders, Centered, Wrap Text*/
CreateCellFormat(1, 2, 1, centerHorizontal, centerVertical, true)
)
));
}
catch (Exception exception)
{
LogWriter.LogError("Unable to create Excel report Stylesheet.");
throw exception;
}
}
/// <summary>
/// Align text vertically.
/// </summary>
/// <param name="VerticalAlignment">Specifies the vertical alignment. Default value is Bottom.</param>
public void SetVerticalAlignment(VerticalAlignmentValues VerticalAlignment)
{
this.Alignment.Vertical = VerticalAlignment;
}
/// <summary>
/// Добавление формата ячейки
/// </summary>
/// <param name="valueHorizontalAlignment">Выравнивание по горизонтали</param>
/// <param name="valueVerticalAlignment">Выравнивание по горизонтали</param>
/// <param name="fontId">Ссылка на шрифт</param>
/// <param name="borderId">Ссылка на границу</param>
/// <param name="fillId">Ссылка на fill</param>
/// <param name="numberingFormatsId">Id Формата</param>
/// <returns></returns>
public CellFormat GenerateCellFormat(HorizontalAlignmentValues valueHorizontalAlignment = HorizontalAlignmentValues.Left, VerticalAlignmentValues valueVerticalAlignment = VerticalAlignmentValues.Bottom, uint fontId = 0, uint borderId = 0, uint fillId = 0, uint numberingFormatsId = 0)
{
CellFormat cellFormat = new CellFormat(new Alignment()
{
WrapText = true, Horizontal = valueHorizontalAlignment, Vertical = valueVerticalAlignment
})
{
ApplyAlignment = true,
};
if (fontId != 0)
{
cellFormat.FontId = fontId;
cellFormat.ApplyFont = true;
}
if (borderId != 0)
{
cellFormat.BorderId = borderId;
cellFormat.ApplyBorder = true;
}
if (fillId != 0)
{
cellFormat.FillId = fillId;
cellFormat.ApplyFill = true;
}
if (numberingFormatsId != 0)
{
cellFormat.NumberFormatId = numberingFormatsId;
cellFormat.ApplyNumberFormat = true;
cellFormat.FormatId = numberingFormatsId;
}
return(cellFormat);
}
/// <summary>
/// Привязка к шрифту и указывать полная ли граница или нет!
/// </summary>
/// <param name="border">Граница</param>
/// <param name="font">Модель шрифта</param>
/// <param name="valueAlignment">Выравнивание по Горизонтали</param>
/// <param name="valueVerticalAlignment">Выравнивание по Вертикале</param>
/// <param name="isFill">Наполнение Fill</param>
/// <param name="isFormatDouble">Формат с плавающей точкой</param>
/// <param name="fill">Наполнение</param>
/// <returns></returns>
public uint StyleTimesNewRoman(Font font, Border border = null, HorizontalAlignmentValues valueAlignment = HorizontalAlignmentValues.Left, VerticalAlignmentValues valueVerticalAlignment = VerticalAlignmentValues.Bottom, bool isFill = false, bool isFormatDouble = false, Fill fill = null)
{
uint idBorder = 0;
uint idFill = 0;
uint idFont = 0;
uint idnumberingFormats = 0;
idFont = InsertFont(font);
if (isFill)
{
idFill = InsertFill(GenerateFill());
}
if (border != null)
{
idBorder = InsertBorder(border);
}
if (fill != null)
{
idFill = InsertFill(fill);
}
if (isFormatDouble)
{
idnumberingFormats = InsertNumberingFormats(GenerateNumberingFormats());
}
return(InsertCellFormat(GenerateCellFormat(valueAlignment, valueVerticalAlignment, idFont, idBorder, idFill, idnumberingFormats)));
}
private void LCreateStyle(string numberFormatCode,
double fontSize, string fontName, System.Drawing.Color fontColor
, bool fontBold, bool fontItalic, PatternValues fillPattern, System.Drawing.Color fillForeGroundColor, System.Drawing.Color borderLeftColor
, BorderStyleValues borderLeftStyleValue, System.Drawing.Color borderBottomColor, BorderStyleValues borderBottomStyleValue, System.Drawing.Color borderRightColor
, BorderStyleValues borderRightStyleValue, System.Drawing.Color borderTopColor, BorderStyleValues borderTopStyleValue, HorizontalAlignmentValues alignmentHorizontal, VerticalAlignmentValues alignmentVertical)
{
if (numberFormatCode != null)
{
NumberingFormat numberingFormat = new NumberingFormat();
numberingFormat.FormatCode = numberFormatCode;
_oNumberingFormat = numberingFormat;
}
//font
Font font = new Font();
FontSize _fontSize = new FontSize()
{
Val = DoubleValue.FromDouble(fontSize)
};
font.Append(_fontSize);
if (fontName != null)
{
FontName _fontName = new FontName()
{
Val = fontName
};
font.Append(_fontName);
}
if (fontColor != null)
{
Color _fontColor = new Color()
{
Rgb = new HexBinaryValue()
{
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(fontColor.R, fontColor.G, fontColor.B)).Replace("#", "")
}
};
font.Append(_fontColor);
}
if (fontBold)
{
Bold _fontBold = new Bold();
font.Append(_fontBold);
}
if (fontItalic)
{
Italic _fontItalic = new Italic();
font.Append(_fontItalic);
}
_oFont = font;
//fill
Fill _fontFill = null;
if (fillForeGroundColor == null)
{
_fontFill = new Fill(new PatternFill()
{
PatternType = fillPattern
});
}
else
{
_fontFill = new Fill(new PatternFill(
new ForegroundColor()
{
Rgb = new HexBinaryValue()
{
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(fillForeGroundColor.R, fillForeGroundColor.G, fillForeGroundColor.B)).Replace("#", "")
}
}
)
{
PatternType = PatternValues.Solid
});
}
_oFill = _fontFill;
//border
Border _border = new Border();
LeftBorder leftBorder = new LeftBorder()
{
Style = borderLeftStyleValue
};
Color colorLeftBorder = null;
if (borderLeftColor != null)
{
colorLeftBorder = new Color()
{
Rgb = new HexBinaryValue {
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderLeftColor.R, borderLeftColor.G, borderLeftColor.B)).Replace("#", "")
}
};
}
else
{
colorLeftBorder = new Color();
}
leftBorder.Append(colorLeftBorder);
_border.Append(leftBorder);
RightBorder rightBorder = new RightBorder()
{
Style = borderRightStyleValue
};
Color colorRightBorder = null;
if (borderRightColor != null)
{
colorRightBorder = new Color()
{
Rgb = new HexBinaryValue {
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderRightColor.R, borderRightColor.G, borderRightColor.B)).Replace("#", "")
}
};
}
else
{
colorRightBorder = new Color();
}
rightBorder.Append(colorRightBorder);
_border.Append(rightBorder);
TopBorder topBorder = new TopBorder()
{
Style = borderTopStyleValue
};
Color colorTopBorder = null;
if (borderTopColor != null)
{
colorTopBorder = new Color()
{
Rgb = new HexBinaryValue {
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderTopColor.R, borderTopColor.G, borderTopColor.B)).Replace("#", "")
}
};
}
else
{
colorTopBorder = new Color();
}
topBorder.Append(colorTopBorder);
_border.Append(topBorder);
BottomBorder bottomBorder = new BottomBorder()
{
Style = borderBottomStyleValue
};
Color colorBottomBorder = null;
if (borderBottomColor != null)
{
colorBottomBorder = new Color()
{
Rgb = new HexBinaryValue {
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderBottomColor.R, borderBottomColor.G, borderBottomColor.B)).Replace("#", "")
}
};
}
else
{
colorBottomBorder = new Color();
}
bottomBorder.Append(colorBottomBorder);
_border.Append(bottomBorder);
_oBorder = _border;
Alignment _alignment = new Alignment();
_alignment.Horizontal = alignmentHorizontal;
_alignment.Vertical = alignmentVertical;
_oAlignment = _alignment;
_oAlignment.WrapText = wrapText;
_oAlignment.TextRotation.Value = textRotate;
}
public void CreateStyle(string numberFormatCode,
double fontSize, string fontName, string fontColor
, bool fontBold, bool fontItalic, PatternValues fillPattern, string fillForeGroundColor, string borderLeftColor
, BorderStyleValues borderLeftStyleValue, string borderBottomColor, BorderStyleValues borderBottomStyleValue, string borderRightColor
, BorderStyleValues borderRightStyleValue, string borderTopColor, BorderStyleValues borderTopStyleValue, HorizontalAlignmentValues alignmentHorizontal, VerticalAlignmentValues alignmentVertical)
{
LCreateStyle(numberFormatCode,
fontSize, fontName, fontColor
, fontBold, fontItalic, fillPattern, fillForeGroundColor, borderLeftColor
, borderLeftStyleValue, borderBottomColor, borderBottomStyleValue, borderRightColor
, borderRightStyleValue, borderTopColor, borderTopStyleValue, alignmentHorizontal, alignmentVertical, textRotate);
}
public void SetAlignment(HorizontalAlignmentValues hAlign, VerticalAlignmentValues vAlign, bool wrapText = true)
{
AlignmentValue = new Alignment {
Horizontal = hAlign, Vertical = vAlign, WrapText = wrapText
};
}