/// <summary> /// Create a number format in the stylesheet and apply to the cellFormat /// </summary> /// <param name="cellInfo"></param> /// <param name="formatCode"></param> /// <param name="stylesheet"></param> /// <param name="cellFormat"></param> private static void UpdateNumberFormat(ExcelStylesManager stylesManager, ExcelCellStyleInfo cellInfo, string formatCode, ref Stylesheet stylesheet, ref CellFormat cellFormat) { if (cellFormat == null) { throw new ArgumentNullException("cellFormat"); } string numFormat = cellInfo.NumberFormat ?? formatCode; // Anything to apply? if (string.IsNullOrEmpty(numFormat)) { return; } // Look up existing... var item = stylesManager.numberFormats.Find(numFormat); UInt32Value id = item.Value; if (id == null) { // Create (and append?) a new numbering format in the stylesheet id = stylesheet.AddNumberingFormat(cellInfo.NumberFormat ?? formatCode); stylesManager.numberFormats.Add(numFormat, id); } cellFormat.NumberFormatId = id; }
/// <summary> /// Create an Allignment object which manages indentation, alignment and text rotation and applies it to the cellFormat. /// </summary> /// <param name="cellInfo"></param> /// <param name="stylesheet"></param> /// <param name="cellFormat"></param> private static void UpdateTextAlignmentAndRotation(ExcelCellStyleInfo cellInfo, ref CellFormat cellFormat) { if (cellFormat == null) { throw new ArgumentNullException("cellFormat"); } if (cellInfo.AlignmentInfo != null) { cellFormat.Alignment = new Alignment() { Indent = new UInt32Value((uint)cellInfo.AlignmentInfo.LeftMargin / 2), Horizontal = new EnumValue <HorizontalAlignmentValues>(StyleTranslator.Translate(cellInfo.AlignmentInfo.TextAlignment)), Vertical = new EnumValue <VerticalAlignmentValues>(StyleTranslator.Translate(cellInfo.AlignmentInfo.VerticalAlignment)), WrapText = new BooleanValue(cellInfo.AlignmentInfo.TextWrapping != TextWrapping.NoWrap), TextRotation = (uint)cellInfo.AlignmentInfo.TextRotationAngle, }; } }
/// <summary> /// Updates the supplied <see cref="CellFormat"/> with a colour index which relates to the Fill Colour. /// The colour is created in the excel <see cref="Stylesheet"/> if it is not already there. /// </summary> /// <param name="stylesheet"></param> /// <param name="cellInfo"></param> /// <returns></returns> private static void UpdateBorder(ExcelStylesManager stylesManager, ExcelCellStyleInfo cellInfo, ref Stylesheet stylesheet, ref CellFormat cellFormat) { if (cellFormat == null) { throw new ArgumentNullException("cellFormat"); } if (cellInfo.BorderInfo != null && cellInfo.BorderInfo.HasBorder) { var item = stylesManager.borders.Find(cellInfo.BorderInfo); UInt32Value id = item.Value; if (id == null) { // Add and return the index of a fill colour id = CreateNewBorder(stylesheet, cellInfo.BorderInfo); stylesManager.borders.Add(cellInfo.BorderInfo, id); } cellFormat.BorderId = id; } }
/// <summary> /// Updates the supplied <see cref="CellFormat"/> with a colour index which relates to the Fill Colour. /// The colour is created in the excel <see cref="Stylesheet"/> if it is not already there. /// </summary> /// <param name="stylesheet"></param> /// <param name="cellInfo"></param> /// <returns></returns> private static void UpdateFillColour(ExcelStylesManager stylesManager, ExcelCellStyleInfo cellInfo, ref Stylesheet stylesheet, ref CellFormat cellFormat) { if (cellFormat == null) { throw new ArgumentNullException("cellFormat"); } if (cellInfo.FillColour != null && cellInfo.FillColour.HasValue && cellInfo.FillColour.Value != System.Windows.Media.Colors.Transparent) { var fillItem = stylesManager.fills.Find(cellInfo.FillColour.Value); UInt32Value fillId = fillItem.Value; if (fillId == null) { // Add and return the index of a fill colour (Badly named as simply returns index of existing colour if it already exists) fillId = stylesheet.AddIndexedColor(cellInfo.FillColour.Value); stylesManager.fills.Add(cellInfo.FillColour.Value, fillId); } cellFormat.FillId = fillId; } }
/// <summary> /// Get a style based on supplied <see cref="ExcelCellInfo"/> and excel format code.<br/> /// The style may have to be created in the stylesheet. /// </summary> /// <param name="cellInfo"></param> /// <param name="formatCode"></param> /// <returns>Index of the style in the stylesheet</returns> public uint GetStyle(ExcelCellStyleInfo cellInfo, string formatCode) { // No cell styling information to apply, so return 0 if (!cellInfo.HasCellInfo) { return(0); } uint styleIndex; var existing = this.styles.Find(cellInfo); if (existing.Key != null) { styleIndex = existing.Value; } else { styleIndex = CreateNewStyle(this, this.stylesheet, cellInfo, formatCode); this.styles.Add((ExcelCellStyleInfo)cellInfo.Clone(), styleIndex); } return(styleIndex); }
/// <summary> /// Updates the supplied <see cref="CellFormat"/> with a font index which relates to the fund in the supplied <see cref="ExportStyle"/> /// </summary> /// <param name="stylesheet"></param> /// <param name="cellInfo"></param> /// <returns></returns> private static void UpdateFont(ExcelStylesManager stylesManager, ExcelCellStyleInfo cellInfo, ref Stylesheet stylesheet, ref CellFormat cellFormat) { if (cellFormat == null) { throw new ArgumentNullException("cellFormat"); } // Anything to apply? if (cellInfo.FontInfo == null) { return; } // Look up existing... var fontItem = stylesManager.fonts.Find(cellInfo.FontInfo); UInt32Value id = fontItem.Value; if (id == null) { // Fonts Font font = new Font(); if (cellInfo.FontInfo.FontFamily != null) { font.FontName = new FontName() { Val = new StringValue(cellInfo.FontInfo.FontFamily.ToString()) }; } font.FontSize = new FontSize() { Val = new DoubleValue(cellInfo.FontInfo.FontSize) }; if ((cellInfo.FontInfo.FontWeight != null) && (cellInfo.FontInfo.FontWeight == FontWeights.Bold)) { font.Bold = new Bold(); } if (cellInfo.FontInfo.FontStyle != null) { if (cellInfo.FontInfo.FontStyle == FontStyles.Italic) { font.Italic = new Italic(); } } font.Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue(StyleTranslator.Translate(cellInfo.FontInfo.FontColour)) }; if (cellInfo.FontInfo.FontUnderlined) { font.Underline = new Underline(); } id = stylesheet.AddFont(font); stylesManager.fonts.Add(cellInfo.FontInfo, id); } cellFormat.FontId = id; }
/// <summary> /// Creates a new style in the excel <see cref="Stylesheet"/> based on the supplied <see cref="ExportStyle"/>.<br/> /// Return the index of the newly created style. /// </summary> /// <param name="stylesheet"></param> /// <param name="cellInfo"></param> /// <param name="formatCode"></param> /// <returns></returns> private static uint CreateNewStyle(ExcelStylesManager stylesManager, Stylesheet stylesheet, ExcelCellStyleInfo cellInfo, string formatCode) { // Create a cellFormat to which style attributes can be applied. var cellFormat = new CellFormat(); // Looks up/creates a new fill colour in the stylesheet and applies it to the cellFormat UpdateFillColour(stylesManager, cellInfo, ref stylesheet, ref cellFormat); // Create and append a new font to the stylesheet and applies it to the cellFormat UpdateFont(stylesManager, cellInfo, ref stylesheet, ref cellFormat); // Create a number format in the stylesheet and apply to the cellFormat UpdateNumberFormat(stylesManager, cellInfo, formatCode, ref stylesheet, ref cellFormat); // Creates a border in the stylesheet and applies it to the cellFormat UpdateBorder(stylesManager, cellInfo, ref stylesheet, ref cellFormat); // Create an Allignment object which manages indentation, alignment and text rotation and applies it to the cellFormat. UpdateTextAlignmentAndRotation(cellInfo, ref cellFormat); uint styleIndex = stylesheet.AddCellFormat(cellFormat); return(styleIndex); }
/// <summary> /// Get a style based on supplied <see cref="ExcelCellStyleInfo"/>.<br/> /// The style may have to be created in the stylesheet. /// </summary> /// <param name="cellInfo"></param> /// <returns>Index of the style in the stylesheet</returns> public uint GetOrCreateStyle(ExcelCellStyleInfo cellInfo) { return(this.GetStyle(cellInfo, string.Empty)); }
/// <summary> /// Ctor. Initialises an empty instance of a <see cref="ExcelCellInfo"/> /// </summary> public ExcelCellInfo() { this.styleInfo = new ExcelCellStyleInfo(); this.value = string.Empty; }