Devexpress Spreadsheet 中文教程（4）

条件格式

格式化单元格是高于还是低于平均值

ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;// Create the rule highlighting values that are above the average in cells C2 through C15.AverageConditionalFormatting cfRule1 = conditionalFormattings.AddAverageConditionalFormatting(worksheet["$// Specify formatting options to be applied to cells if the condition is true.// Set the background color to yellow.cfRule1.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);// Set the font color to red.cfRule1.Formatting.Font.Color = Color.Red;// Create the rule highlighting values that are one standard deviation below the mean in cells D2 AverageConditionalFormatting cfRule2 = conditionalFormattings.AddAverageConditionalFormatting(worksheet["$// Specify formatting options to be applied to cells if the conditions is true.// Set the background color to light-green.cfRule2.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0x9F, 0xFB, 0x69);// Set the font color to blue-violet.cfRule2.Formatting.Font.Color = Color.BlueViolet;

格式化单元格值是否在两个值中间

// Create the rule to identify values below 7 and above 19 in cells F2 through F15.RangeConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddRangeConditionalFormatting(// Specify formatting options to be applied to cells if the condition is true.// Set the background color to yellow.cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);// Set the font color to red.cfRule.Formatting.Font.Color = Color.Red;

格式化顶部或者底部排名

// Create the rule to identify top three values in cells F2 through F15.RankConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddRankConditionalFormatting(worksheet["$// Specify formatting options to be applied to cells if the condition is true.// Set the background color to dark orchid.cfRule.Formatting.Fill.BackgroundColor = Color.DarkOrchid;// Set the outline borders.cfRule.Formatting.Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Thin);// Set the font color to white.cfRule.Formatting.Font.Color = Color.White;

基于单元格文本格式化单元格

// Create the rule to highlight values with the given text string in cells A2 through A15.TextConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddTextConditionalFormatting(worksheet["$// Specify formatting options to be applied to cells if the condition is true.// Set the background color to pink.cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xE1, 0x95, 0xC2);

格式化唯一值的或重复值，空白单元格和公式错误

// Create the rule to identify unique values in cells A2 through A15.SpecialConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddSpecialConditionalFormatting(// Specify formatting options to be applied to cells if the condition is true.// Set the background color to yellow.cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);

格式化包含日期的格式单元格

// Create the rule to highlight today's dates in cells B2 through B6.TimePeriodConditionalFormatting cfRule =worksheet.ConditionalFormattings.AddTimePeriodConditionalFormatting(worksheet["$B$2:$B$6"], ConditionalFormattingTimePeriod.// Specify formatting options to be applied to cells if the condition is true.// Set the background color to pink.cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xF2, 0xAE, 0xE3);

格式化单元格小于、大于或等于一个值

// Create the rule to identify values that are above the average in cells F2 through F15.ExpressionConditionalFormatting cfRule =worksheet.ConditionalFormattings.AddExpressionConditionalFormatting(worksheet["$F$2:$F$15"], ConditionalFormattingExpressionCondition.// Specify formatting options to be applied to cells if the condition is true.// Set the background color to yellow.cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);// Set the font color to red.cfRule.Formatting.Font.Color = Color.Red;

使用公式来确定单元格格式

// Create the rule to shade alternate rows without applying a new style.FormulaExpressionConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddFormulaExpressionConditionalFormatting(// Specify formatting options to be applied to cells if the condition is true.// Set the background color to light blue.cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xBC, 0xDA, 0xF7);

使用双色条格式化单元格

ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;// Set the minimum threshold to the lowest value in the range of cells.ConditionalFormattingValue minPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.// Set the maximum threshold to the highest value in the range of cells.ConditionalFormattingValue maxPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.// Create the two-color scale rule to differentiate low and high values in cells C2 through D15. Blue ColorScale2ConditionalFormatting cfRule = conditionalFormattings.AddColorScale2ConditionalFormatting(

使用三色条格式化单元格

ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;// Set the minimum threshold to the lowest value in the range of cells using the MIN() formula.ConditionalFormattingValue minPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.// Set the midpoint threshold to the 50th percentile.ConditionalFormattingValue midPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.// Set the maximum threshold to the highest value in the range of cells using the MAX() formula.ConditionalFormattingValue maxPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.// Create the three-color scale rule to determine how values in cells C2 through D15 vary. Red represents ColorScale3ConditionalFormatting cfRule = conditionalFormattings.AddColorScale3ConditionalFormatting(

使用数据条格式化单元格

ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;// Set the value corresponding to the shortest bar to the lowest value.ConditionalFormattingValue lowBound1 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.// Set the value corresponding to the longest bar to the highest value.ConditionalFormattingValue highBound1 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.// Create the rule to compare values in cells E2 through E15 using data bars.DataBarConditionalFormatting cfRule1 = conditionalFormattings.AddDataBarConditionalFormatting(worksheet.// Set the positive bar border color to green.cfRule1.BorderColor = DXColor.Green;// Set the negative bar color to red.cfRule1.NegativeBarColor = DXColor.Red;// Set the negative bar border color to red.cfRule1.NegativeBarBorderColor = DXColor.Red;// Set the axis position to display the axis in the middle of the cell.cfRule1.AxisPosition = ConditionalFormattingDataBarAxisPosition.Middle;// Set the axis color to dark blue.Spreadsheet Document Server 258© 2015 DevExpress Inc. 258cfRule1.AxisColor = Color.DarkBlue;// Set the value corresponding to the shortest bar to 0 percent.ConditionalFormattingValue lowBound2 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.// Set the value corresponding to the longest bar to 100 percent.ConditionalFormattingValue highBound2 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.// Create the rule to compare values in cells G2 through G15 using data bars.DataBarConditionalFormatting cfRule2 = conditionalFormattings.AddDataBarConditionalFormatting(worksheet.// Set the data bar border color to sky blue.cfRule2.BorderColor = DXColor.SkyBlue;// Specify the solid fill type.cfRule2.GradientFill = false;// Hide values of cells to which the rule is applied.cfRule2.ShowValue = false;

使用图标集格式化单元格

ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;// Set the first threshold to the lowest value in the range of cells using the MIN() formula.ConditionalFormattingIconSetValue minPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.// Set the second threshold to 0.ConditionalFormattingIconSetValue midPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.// Set the third threshold to 0.01.ConditionalFormattingIconSetValue maxPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.// Create the rule to apply a specific icon from the three arrow icon set to each cell in the range IconSetConditionalFormatting cfRule = conditionalFormattings.AddIconSetConditionalFormatting(worksheet.// Specify the custom icon to be displayed if the second condition is true.// To do this, set the IconSetConditionalFormatting.IsCustom property to true, which is false by default.cfRule.IsCustom = true;// Initialize the ConditionalFormattingCustomIcon object.ConditionalFormattingCustomIcon cfCustomIcon = new ConditionalFormattingCustomIcon();// Specify the icon set where you wish to get the icon.

cfCustomIcon.IconSet = IconSetType.TrafficLights13;// Specify the index of the desired icon in the set.cfCustomIcon.IconIndex = 1;// Add the custom icon at the specified position in the initial icon set.cfRule.SetCustomIcon(1, cfCustomIcon);// Hide values of cells to which the rule is applied.cfRule.ShowValue = false;

新建一个Table

Worksheet worksheet = workbook.Worksheets[0];// Insert a table in the worksheet.Table table = worksheet.Tables.Add(worksheet["A1:F12"], false);// Format the table by applying a built-in table style.table.Style = workbook.TableStyles[BuiltInTableStyleId.TableStyleMedium20];

在表中进行计算

Worksheet worksheet = workbook.Worksheets["TableRanges"];workbook.Worksheets.ActiveWorksheet = worksheet;// Access a table.Table table = worksheet.Tables[0];// Access table columns.TableColumn productColumn = table.Columns[0];

TableColumn priceColumn = table.Columns[1];TableColumn quantityColumn = table.Columns[2];TableColumn discountColumn = table.Columns[3];// Add a new column to the end of the table .TableColumn amountColumn = table.Columns.Add();// Set the name of the last column.amountColumn.Name = "Amount";// Set the formula to calculate the amount per product// and display results in the "Amount" column.amountColumn.Formula = "=[Price]*[Quantity]*(1-[Discount])";// Display the total row in the table.table.ShowTotals = true;// Set the label and function to display the sum of the "Amount" column.discountColumn.TotalRowLabel = "Total:";amountColumn.TotalRowFunction = TotalRowFunction.Sum;// Specify the number format for each column.priceColumn.DataRange.NumberFormat = "$#,##0.00";discountColumn.DataRange.NumberFormat = "0.0%";amountColumn.Range.NumberFormat = "$#,##0.00;$#,##0.00;\"\";@";// Specify horizontal alignment for header and total rows of the table.table.HeaderRowRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;table.TotalRowRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;// Specify horizontal alignment to display data in all columns except the first one.for (int i = 1; i < table.Columns.Count; i++){table.Columns[i].DataRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;}// Set the width of table columns.table.Range.ColumnWidthInCharacters = 10;worksheet.Visible = true;

创建、修改、删除Table Styles

using DevExpress.Spreadsheet;// ...// Access the table style to be modified.TableStyle tableStyle = workbook.TableStyles["tableStyleName"];// Change the required formatting characteristics of the style elements.tableStyle.BeginUpdate();try {TableStyleElement wholeTable = tableStyle.TableStyleElements[TableStyleElementType.WholeTable];// wholeTable.Fill...// wholeTable.Borders...// wholeTable.Font...TableStyleElement tableHeader = tableStyle.TableStyleElements[TableStyleElementType.HeaderRow];// tableHeader.Fill.BackgroundColor...// tableHeader.Font...TableStyleElement firstColumn = tableStyle.TableStyleElements[TableStyleElementType.FirstColumn];// firstColumn.Clear();// ...}finally {tableStyle.EndUpdate();}

Create Your Own Custom Table Style

(TableActions.cs)Worksheet worksheet = workbook.Worksheets["Custom Table Style"];workbook.Worksheets.ActiveWorksheet = worksheet;// Access a table.Table table = worksheet.Tables[0];String styleName = "testTableStyle";// If the style under the specified name already exists in the collection,if (workbook.TableStyles.Contains(styleName)){// apply this style to the table.table.Style = workbook.TableStyles[styleName];}else{// Add a new table style under the "testTableStyle" name to the TableStyles collection.TableStyle customTableStyle = workbook.TableStyles.Add("testTableStyle");// Modify the required formatting characteristics of the table style.// Specify the format for different table elements.customTableStyle.BeginUpdate();try{customTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Font.Color = Color.FromArgb(107, // Specify formatting characteristics for the table header row.TableStyleElement headerRowStyle = customTableStyle.TableStyleElements[TableStyleElementType.HeaderRow];headerRowStyle.Fill.BackgroundColor = Color.FromArgb(64, 66, 166);headerRowStyle.Font.Color = Color.White;headerRowStyle.Font.Bold = true;// Specify formatting characteristics for the table total row.TableStyleElement totalRowStyle = customTableStyle.TableStyleElements[TableStyleElementType.TotalRow];totalRowStyle.Fill.BackgroundColor = Color.FromArgb(115, 193, 211);totalRowStyle.Font.Color = Color.White;totalRowStyle.Font.Bold = true;// Specify banded row formatting for the table.TableStyleElement secondRowStripeStyle = customTableStyle.TableStyleElements[TableStyleElementType.SecondRowStripe];secondRowStripeStyle.Fill.BackgroundColor = Color.FromArgb(234, 234, 234);secondRowStripeStyle.StripeSize = 1;}finally{customTableStyle.EndUpdate();}// Apply the created custom style to the table.table.Style = customTableStyle;}worksheet.Visible = true;

Duplicate an Existing Table Style

Worksheet worksheet = workbook.Worksheets["Custom Table Style"];workbook.Worksheets.ActiveWorksheet = worksheet;// Access a table.Table table = worksheet.Tables[0];String styleName = "testTableStyle";// If the style under the specified name already exists in the collection,if (workbook.TableStyles.Contains(styleName)){// apply this style to the table.table.Style = workbook.TableStyles[styleName];}else{// Add a new table style under the "testTableStyle" name to the TableStyles collection.TableStyle customTableStyle = workbook.TableStyles.Add("testTableStyle");// Modify the required formatting characteristics of the table style.// Specify the format for different table elements.customTableStyle.BeginUpdate();try{customTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Font.Color = Color.FromArgb(107, // Specify formatting characteristics for the table header row.TableStyleElement headerRowStyle = customTableStyle.TableStyleElements[TableStyleElementType.HeaderRow];headerRowStyle.Fill.BackgroundColor = Color.FromArgb(64, 66, 166);headerRowStyle.Font.Color = Color.White;headerRowStyle.Font.Bold = true;// Specify formatting characteristics for the table total row.TableStyleElement totalRowStyle = customTableStyle.TableStyleElements[TableStyleElementType.TotalRow];totalRowStyle.Fill.BackgroundColor = Color.FromArgb(115, 193, 211);totalRowStyle.Font.Color = Color.White;totalRowStyle.Font.Bold = true;// Specify banded row formatting for the table.TableStyleElement secondRowStripeStyle = customTableStyle.TableStyleElements[TableStyleElementType.SecondRowStripe];secondRowStripeStyle.Fill.BackgroundColor = Color.FromArgb(234, 234, 234);secondRowStripeStyle.StripeSize = 1;}finally{customTableStyle.EndUpdate();}// Apply the created custom style to the table.table.Style = customTableStyle;}worksheet.Visible = true;