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);
}
}
