private void ApplyCellFormat(int CurrentRowNum, string startCol, CellFillPattern cellFillFormat) { string regionName = startCol + (CurrentRowNum + 1).ToString() + ":L" + (CurrentRowNum + 1).ToString(); WorksheetRegion region = ws.GetRegion(regionName); foreach (WorksheetCell wCell in region) { wCell.CellFormat.Fill = cellFillFormat; } }
private void CreateRowStyles() { cfpTitle = CellFill.CreateSolidFill(Colors.LightGray); // create the fill pattern for the table header cfpColHeader = CellFill.CreateSolidFill(Colors.AliceBlue); // create the fill pattern for the table rows // create the fill pattern for the table alternate rows cfpGrandTotalRow = CellFillPattern.CreateSolidFill(Colors.LightGray); cfpVoidRow = CellFillPattern.CreateSolidFill(Colors.Red); wb = new Workbook(); ws = wb.Worksheets.Add("Crown"); ws.DisplayOptions.PanesAreFrozen = true; ws.DisplayOptions.FrozenPaneSettings.FrozenRows = 3; }
/// <summary> /// Raises the <see cref="E:System.Windows.Forms.Form.Shown" /> event. /// </summary> /// <param name="e">A <see cref="T:System.EventArgs" /> that contains the event data.</param> protected override void OnShown(EventArgs e) { base.OnShown(e); var format = adapter.Format; var font = format.Font; // Initialize controls on the Numbers string formatString = format.FormatString; if (string.IsNullOrEmpty(formatString) == false) { FormatInfo formatInfo = null; foreach (NumberFormatInfo numberFormat in this.numberFormats) { formatInfo = numberFormat.FindFormat(formatString); if (formatInfo != null) { this.treeCategory.GetNodeByKey(numberFormat.CategoryName).Selected = true; this.treeFormats.Nodes[numberFormat.Formats.IndexOf(formatInfo)].Selected = true; break; } } if (formatInfo == null) { var customGroup = this.numberFormats.First(x => x.IsCustom); this.treeCategory.GetNodeByKey(customGroup.CategoryName).Selected = true; formatInfo = customGroup.AddFormatInfo(formatString); this.treeFormats.Nodes[customGroup.Formats.IndexOf(formatInfo)].Selected = true; } } // Initialize controls on the Alignment tab #region Alignment tab this.cboHorizontal.Value = format.Alignment; this.cboVertical.Value = format.VerticalAlignment; this.nmeIndent.Value = format.Indent; this.cbWrapText.Checked = format.WrapText == ExcelDefaultableBoolean.True; this.cbShrinkToFit.Checked = format.ShrinkToFit == ExcelDefaultableBoolean.True; #endregion // Alignment tab // Initialize controls on the Font tab #region Font tab Infragistics.Win.UltraWinTree.UltraTreeNode node = this.treeFontFamily.GetNodeByKey(font.Name); if (node != null) { node.Selected = true; node.BringIntoView(); } FontStylesCustom style = FontStylesCustom.Regular; if (font.Italic == ExcelDefaultableBoolean.True) { style = (font.Bold == ExcelDefaultableBoolean.True) ? FontStylesCustom.BoldItalic : FontStylesCustom.Italic; } else if (font.Bold == ExcelDefaultableBoolean.True) { style = FontStylesCustom.Bold; } node = this.treeFontStyle.GetNodeByKey(style.ToString()); if (node != null) { node.Selected = true; node.BringIntoView(); } node = this.treeFontSize.GetNodeByKey((font.Height / 20).ToString()); if (node != null) { node.Selected = true; node.BringIntoView(); } this.cboUnderline.Value = font.UnderlineStyle; this.cbSubscript.Checked = (font.SuperscriptSubscriptStyle == FontSuperscriptSubscriptStyle.Subscript); this.cbSuperscript.Checked = (font.SuperscriptSubscriptStyle == FontSuperscriptSubscriptStyle.Superscript); this.cbStrikethrough.Checked = (font.Strikeout == ExcelDefaultableBoolean.True); this.cpFontColor.Color = adapter.ForegroundColor; this.shouldEvaluateIsNormalFont = true; this.EvaluateIsNormalFont(); #endregion //Font tab // Initialize controls on the Fill tab #region Fill tab CellFillPattern fillPattern = format.Fill as CellFillPattern; if (fillPattern != null) { this.cboPatternStyle.Value = fillPattern.PatternStyle; this.cpBackColor.Color = adapter.FillColor; if (fillPattern.PatternColorInfo.IsAutomatic) { this.cpPatternColor.Color = Color.Empty; } else { if (fillPattern.PatternColorInfo.Color != null) { this.cpPatternColor.Color = fillPattern.PatternColorInfo.Color.Value; } else { this.cpPatternColor.Color = Color.Empty; } } } #endregion // Fill tab this.recordPropertiesChanges = true; }
private void CommitChanges() { var format = this.adapter.Format; var font = format.Font; WorkbookColorInfo newBackgroundColorInfo = null; WorkbookColorInfo newPatternColorInfo = null; bool updatePatternStyle = false; foreach (var propertyName in this.changedProperties.Distinct()) { switch (propertyName) { case BackgroundColorPropertyName: #region BackgroundColorPropertyName if (!this.cpBackColor.Color.IsEmpty) { try { newBackgroundColorInfo = new WorkbookColorInfo(this.cpBackColor.Color); } catch (Exception ex) { MessageBox.Show(ex.Message); } } break; #endregion //BackgroundColorPropertyName case FillPatternStylePropertyName: #region FillPatternStylePropertyName updatePatternStyle = true; break; #endregion //FillPatternStylePropertyName case FontColorPropertyName: #region FontColorPropertyName if (!this.cpFontColor.Color.IsEmpty) { try { font.ColorInfo = new WorkbookColorInfo(this.cpFontColor.Color); } catch (Exception ex) { MessageBox.Show(ex.Message); } } break; #endregion //FontColorPropertyName case FontNamePropertyName: #region FontNamePropertyName font.Name = (string)this.treeFontFamily.ActiveNode.Cells["Value"].Value; break; #endregion //FontNamePropertyName case FontSizePropertyName: #region FontSizePropertyName font.Height = (int)this.treeFontSize.ActiveNode.Cells["DataValue"].Value; break; #endregion //FontSizePropertyName case FontStylePropertyName: #region FontStylePropertyName ExcelDefaultableBoolean isBold = ExcelDefaultableBoolean.False; ExcelDefaultableBoolean isItalic = ExcelDefaultableBoolean.False; switch ((FontStylesCustom)this.treeFontStyle.ActiveNode.Cells["Value"].Value) { case FontStylesCustom.Bold: isBold = ExcelDefaultableBoolean.True; break; case FontStylesCustom.Italic: isItalic = ExcelDefaultableBoolean.True; break; case FontStylesCustom.BoldItalic: isBold = ExcelDefaultableBoolean.True; isItalic = ExcelDefaultableBoolean.True; break; } font.Bold = isBold; font.Italic = isItalic; break; #endregion //FontStylePropertyName case HorizontalCellAlignmentPropertyName: #region HorizontalCellAlignmentPropertyName format.Alignment = (HorizontalCellAlignment)this.cboHorizontal.Value; break; #endregion //HorizontalCellAlignmentPropertyName case IndentPropertyName: #region IndentPropertyName format.Indent = (int)this.nmeIndent.Value; break; #endregion //IndentPropertyName case PatternColorPropertyName: #region PatternColorPropertyName if (!this.cpPatternColor.Color.IsEmpty) { try { newPatternColorInfo = new WorkbookColorInfo(this.cpPatternColor.Color); } catch (Exception ex) { MessageBox.Show(ex.Message); } } break; #endregion //PatternColorPropertyName case ShrinkToFitPropertyName: #region ShrinkToFitPropertyName format.ShrinkToFit = (this.cbShrinkToFit.Checked) ? ExcelDefaultableBoolean.True : ExcelDefaultableBoolean.False; break; #endregion //ShrinkToFitPropertyName case StrikethroughPropertyName: #region StrikethroughPropertyName font.Strikeout = this.cbStrikethrough.Checked ? ExcelDefaultableBoolean.True : ExcelDefaultableBoolean.False; break; #endregion //StrikethroughPropertyName case SubscriptSuperscriptPropertyName: #region SubscriptSuperscriptPropertyName if (this.cbSubscript.Checked) { font.SuperscriptSubscriptStyle = FontSuperscriptSubscriptStyle.Subscript; } else if (this.cbSuperscript.Checked) { font.SuperscriptSubscriptStyle = FontSuperscriptSubscriptStyle.Superscript; } break; #endregion //SubscriptSuperscriptPropertyName case UnderlinePropertyName: #region UnderlinePropertyName font.UnderlineStyle = (FontUnderlineStyle)this.cboUnderline.Value; break; #endregion //UnderlinePropertyName case VerticalCellAlignmentPropertyName: #region VerticalCellAlignmentPropertyName format.VerticalAlignment = (VerticalCellAlignment)this.cboVertical.Value; break; #endregion //VerticalCellAlignmentPropertyName case WrapTextPropertyName: #region WrapTextPropertyName format.WrapText = (this.cbWrapText.Checked) ? ExcelDefaultableBoolean.True : ExcelDefaultableBoolean.False; break; #endregion //WrapTextPropertyName case FormatPropertyName: #region FormatPropertyName if (this.treeFormats.SelectedNodes.Count > 0) { format.FormatString = ((FormatInfo)this.treeFormats.SelectedNodes[0].ListObject).Mask; } break; #endregion //FormatPropertyName default: Debug.Fail("Unhandled " + propertyName); break; } } if (updatePatternStyle || newBackgroundColorInfo != null || newPatternColorInfo != null) { CellFillPattern oldPattern = format.Fill as CellFillPattern; format.Fill = new CellFillPattern( newBackgroundColorInfo ?? oldPattern.BackgroundColorInfo, newPatternColorInfo ?? oldPattern.PatternColorInfo, updatePatternStyle ? (FillPatternStyle)this.cboPatternStyle.Value : oldPattern.PatternStyle); } }