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>
/// Overload 2: Constructor, Initialize all fields
/// </summary>
/// <param name="boundColumnName">string</param>
/// <param name="userDefinedColumnName">string</param>
/// <param name="numberFormat"></param>
/// <param name="horizontalAlignment"></param>
/// <param name="showField">bool</param>
/// <param name="fieldOrder">int?</param>
/// <param name="userDefinedNumberFormat">string</param>
/// -----------------------------------------------------------------------------------------------
public Column(
string boundColumnName,
string userDefinedColumnName,
NumberFormats numberFormat = NumberFormats.General,
HorizontalAlignmentValues horizontalAlignment = HorizontalAlignmentValues.Center,
bool showField = true,
int?fieldOrder = null,
string userDefinedNumberFormat = null)
{
try
{
BoundColumnName = boundColumnName;
UserDefinedColumnName = userDefinedColumnName;
NumberFormat = numberFormat;
HorizontalAlignment = horizontalAlignment;
ShowField = showField;
FieldOrder = fieldOrder;
UserDefinedNumberFormat = userDefinedNumberFormat;
}
catch (Exception ex)
{
Log.Error("SpreadsheetLightWrapper.Export.Models.Column.Contructor:Overload 2 -> " + ex.Message + ": " +
ex);
}
}
/// <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)));
}
///<summary>
///Sets the horizontal alignment value
///</summary>
public void SetHorizontalAlignment(HorizontalAlignmentValues value)
{
if (HorizontalAlignment == null)
{
HorizontalAlignment = new EnumValue <HorizontalAlignmentValues>();
}
HorizontalAlignment.Value = value;
}
private Alignment GetDefaultAlignment(Alignment tplAlignment, HorizontalAlignmentValues defHorizontalAlignment)
{
var alignment = tplAlignment != null?tplAlignment.CloneNode(true) as Alignment : new Alignment();
if (defHorizontalAlignment != HorizontalAlignmentValues.General)
{
alignment.Horizontal = defHorizontalAlignment;
}
return(alignment);
}
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 ExcelNumberFormatAttribute(bool applyNumberFormat, UInt32 numberFormat = 0U, double width = 10D,
int horizontalAlignment = -1)
{
NumberFormat = numberFormat;
ApplyNumberFormat = applyNumberFormat;
Width = DoubleValue.FromDouble(width);
if (Enum.IsDefined(typeof(HorizontalAlignmentValues), horizontalAlignment))
{
HorizontalAlignment = (HorizontalAlignmentValues)horizontalAlignment;
}
}
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;
}
/// <summary>CellFormat の取得</summary>
/// <param name="numFmtId">円貨の金額の場合 38 それ以外は、NumberingFormat を追加し、追加したNumberFormatId</param>
/// <param name="fmtId"></param>
/// <param name="fontId">FontId</param>
/// <param name="fillId">FillId</param>
/// <param name="borderId">BorderId</param>
/// <param name="horizontal"><see cref="HorizontalAlignmentValues"/> [配置]-[横位置]</param>
/// <returns></returns>
public static CellFormat GetCellFormat(
int numFmtId = 0,
int fmtId = 0,
int fontId = 0,
int fillId = 0,
int borderId = 0,
HorizontalAlignmentValues horizontal = HorizontalAlignmentValues.General)
{
var format = new CellFormat
{
NumberFormatId = (uint)numFmtId,
FormatId = (uint)fmtId,
FontId = (uint)fontId,
FillId = (uint)fillId,
BorderId = (uint)borderId,
};
if (numFmtId > 0u)
{
format.ApplyNumberFormat = true;
}
if (fillId > 0u)
{
format.ApplyFill = true;
}
if (borderId > 0u)
{
format.ApplyBorder = true;
}
if (horizontal == HorizontalAlignmentValues.General)
{
format.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Center,
});
}
else
{
format.AppendChild(new Alignment
{
Vertical = VerticalAlignmentValues.Center,
Horizontal = horizontal,
});
format.ApplyAlignment = true;
}
return(format);
}
/// -----------------------------------------------------------------------------------------------
/// <summary>
/// Overload 2: Overrides the default bound column names with custom ones and determines the formatting,
/// visibility and order.
/// ** Note: If none are added then the column names from the dataset table will be used and
/// there will no formatting.
/// <para />
/// "boundColumnName" string: Bound column name
/// <para />
/// "userDefinedColumnName" string: Custom column name
/// <para />
/// "numberFormat" NumberFormats: Column format
/// <para />
/// "horizontalAlignment" HorizontalAlignmentValues: Column horizontalAlignment
/// <para />
/// "showField" bool: Show/Hide column
/// <para />
/// "fieldOrder" int: Set the order of the field
/// <para />
/// "userDefinedNumberFormat" string: User-defined excel format
/// * Must be populated when "numberFormat" is set to "User-Defined"
/// </summary>
/// <param name="boundColumnName">string: Bound column name</param>
/// <param name="userDefinedColumnName">string: User-Defined column name</param>
/// <param name="numberFormat">string: Column number format</param>
/// <param name="horizontalAlignment">string: Column horizontalAlignment</param>
/// <param name="showField">bool: Show/Hide column</param>
/// <param name="fieldOrder">int: Set the order of the field</param>
/// <param name="userDefinedNumberFormat">string</param>
/// -----------------------------------------------------------------------------------------------
public void SetUserDefinedColumnNames(
string boundColumnName,
string userDefinedColumnName,
NumberFormats numberFormat,
HorizontalAlignmentValues horizontalAlignment,
bool showField,
int?fieldOrder,
string userDefinedNumberFormat = null)
{
try
{
if (UserDefinedColumns.Any(x => x.BoundColumnName == boundColumnName))
{
UserDefinedColumns.RemoveAt(UserDefinedColumns.FindIndex(x => x.BoundColumnName == boundColumnName));
UserDefinedColumns.Add(new Column
{
BoundColumnName = boundColumnName,
UserDefinedColumnName = userDefinedColumnName,
NumberFormat = numberFormat,
HorizontalAlignment = horizontalAlignment,
ShowField = showField,
FieldOrder = fieldOrder,
UserDefinedNumberFormat = userDefinedNumberFormat
});
}
else
{
UserDefinedColumns.Add(new Column
{
BoundColumnName = boundColumnName,
UserDefinedColumnName = userDefinedColumnName,
NumberFormat = numberFormat,
HorizontalAlignment = horizontalAlignment,
ShowField = showField,
FieldOrder = fieldOrder,
UserDefinedNumberFormat = userDefinedNumberFormat
});
}
}
catch (Exception ex)
{
Log.Error(
"SpreadsheetLightWrapper.Export.Models.ChildSetting.SetUserDefinedColumnNames:Overload 2 -> " +
ex.Message + ": " + ex);
}
}
/// <summary>
/// Inserts the value.
/// </summary>
/// <param name="text">The text.</param>
/// <param name="column">The column.</param>
/// <param name="rowindex">The rowindex.</param>
/// <param name="align">The align.</param>
/// <param name="valueType">Type of the value.</param>
public void InsertValue(string text, string column, int rowindex, HorizontalAlignmentValues? align, CellValues valueType)
{
if (CurrentSheet == null) throw new Exception("No sheet selected");
Cell cell = LocateCell(column, rowindex);
cell.CellValue = new CellValue(text);
cell.DataType = new EnumValue<CellValues>(valueType);
CurrentSheet.Worksheet.Save();
}
/// <summary>
/// Inserts the value.
/// </summary>
/// <param name="text">The text.</param>
/// <param name="column">The column.</param>
/// <param name="rowindex">The rowindex.</param>
/// <param name="align">The align.</param>
public void InsertValue(string text, string column, int rowindex, HorizontalAlignmentValues? align)
{
InsertValue(text, column, rowindex, align, CellValues.String);
}
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 SetHorizontalAlignment(HorizontalAlignmentValues value)
{
Alignment.Horizontal = value;
return(this);
}
/// <summary>
/// Inserts the value.
/// </summary>
/// <param name="value">The value.</param>
/// <param name="column">The column.</param>
/// <param name="rowindex">The rowindex.</param>
/// <param name="align">The align.</param>
public void InsertValue(int value, string column, int rowindex, HorizontalAlignmentValues? align)
{
InsertValue((double)value, column, rowindex, align);
}
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>
/// Inserts the value.
/// </summary>
/// <param name="value">The value.</param>
/// <param name="column">The column.</param>
/// <param name="rowindex">The rowindex.</param>
/// <param name="align">The align.</param>
public void InsertValue(double value, string column, int rowindex, HorizontalAlignmentValues? align)
{
InsertValue(value.ToString(), column, rowindex, align, CellValues.Number);
}
/// <summary>
/// Inserts the value.
/// </summary>
/// <param name="value">if set to <c>true</c> [value].</param>
/// <param name="column">The column.</param>
/// <param name="rowindex">The rowindex.</param>
/// <param name="align">The align.</param>
public void InsertValue(bool value, string column, int rowindex, HorizontalAlignmentValues? align)
{
string svalue = "0";
if (value) svalue = "1";
InsertValue(svalue, column, rowindex, align, CellValues.Boolean);
}
/// <summary>
/// Align text horizontally.
/// </summary>
/// <param name="HorizontalAlignment">Specifies the horizontal alignment. Default value is General.</param>
public void SetHorizontalAlignment(HorizontalAlignmentValues HorizontalAlignment)
{
this.Alignment.Horizontal = HorizontalAlignment;
}
/// <summary>
/// Inserts the value.
/// </summary>
/// <param name="value">The value.</param>
/// <param name="column">The column.</param>
/// <param name="rowindex">The rowindex.</param>
/// <param name="align">The align.</param>
public void InsertValue(DateTime value, string column, int rowindex, HorizontalAlignmentValues? align)
{
if (CurrentSheet == null) throw new Exception("No sheet selected");
string svalue;
if (value.Hour == 0 && value.Minute == 0 && value.Millisecond == 0)
{
svalue = value.ToString("dd/MM-yyyy");
}
else svalue = value.ToString("dd/MM-yyyy HH:mm:ss");
InsertValue(svalue, column, rowindex, align, CellValues.String);
}
public void SetAlignment(HorizontalAlignmentValues hAlign, VerticalAlignmentValues vAlign, bool wrapText = true)
{
AlignmentValue = new Alignment {
Horizontal = hAlign, Vertical = vAlign, WrapText = wrapText
};
}
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);
}
//创建一个单元格样式
//todo 如果参数下次再多，就需要考虑把同类的细节参数放到一个新建立参数类中,以提升代码阅读性。
private static UInt32Value createCellFormat(Stylesheet styleSheet, UInt32Value borderid, UInt32Value fontIndex, UInt32Value fillIndex, UInt32Value numberFormatId, HorizontalAlignmentValues horizontal = HorizontalAlignmentValues.Left)
{
if (styleSheet.CellFormats.Count == null)
{
styleSheet.CellFormats.Count = (UInt32Value)0;
}
CellFormat cellFormat = null;
if (horizontal == HorizontalAlignmentValues.Left)
{
cellFormat = new CellFormat();
}
else
{
Alignment alignment = new Alignment();
alignment.Horizontal = horizontal;
alignment.Vertical = VerticalAlignmentValues.Center;
cellFormat = new CellFormat(alignment);
}
cellFormat.BorderId = 0;
if (fontIndex != null)
{
cellFormat.ApplyFont = true;
cellFormat.FontId = fontIndex;
}
if (borderid != null)
{
cellFormat.ApplyBorder = true;
cellFormat.BorderId = borderid;
}
if (fillIndex != null)
{
cellFormat.FillId = fillIndex;
cellFormat.ApplyFill = true;
}
if (numberFormatId != null)
{
cellFormat.NumberFormatId = numberFormatId;
cellFormat.ApplyNumberFormat = true;
}
styleSheet.CellFormats.Append(cellFormat);
UInt32Value result = styleSheet.CellFormats.Count;
styleSheet.CellFormats.Count++;
return(result);
}
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;
}
