private void comboBuiltInTableStyles_SelectedIndexChanged(object sender, EventArgs e)
{
workbook.BeginUpdate();
try {
// Get the name of the table style to be applied to a table
// (from the combo box that lists all built-in table styles).
string styleName = comboBuiltInTableStyles.SelectedItem.ToString();
ApplyBuiltInTableStyle(workbook, table, styleName);
}
finally {
workbook.EndUpdate();
}
}
static void CopyRowsColumns(IWorkbook workbook)
{
workbook.BeginUpdate();
try {
Worksheet worksheet = workbook.Worksheets[0];
// Modify the 2nd row.
worksheet.Cells["A2"].Value = "Row 2";
worksheet.Rows["2"].Height = 50;
worksheet.Rows["2"].Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
worksheet.Rows["2"].FillColor = Color.LightCyan;
// Modify the "B" column.
worksheet.Cells["B1"].Value = "ColumnB";
worksheet.Columns["B"].Borders.SetOutsideBorders(Color.CadetBlue, BorderLineStyle.Thick);
#region #CopyRowsColumns
// Copy all data from the 2nd row to the 5th row.
worksheet.Rows["5"].CopyFrom(worksheet.Rows["2"]);
// Copy only borders from the "B" column to the "E" column.
worksheet.Columns["E"].CopyFrom(worksheet.Columns["B"], PasteSpecial.Borders);
#endregion #CopyRowsColumns
}
finally {
workbook.EndUpdate();
}
}
static void CustomNumberFormat(IWorkbook workbook)
{
workbook.BeginUpdate();
try {
Worksheet worksheet = workbook.Worksheets[0];
worksheet["A1"].Value = "Number Format:";
worksheet["A2"].Value = "Values:";
worksheet["A3"].Value = "Formatted Values:";
worksheet["A1:A3"].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
worksheet["A1:E3"].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Left;
worksheet["A1:E3"].ColumnWidthInCharacters = 17;
worksheet.MergeCells(worksheet["B1:E1"]);
worksheet["B1:E1"].Value = "[Green]#.00;[Red]#.00;[Blue]0.00;[Magenta]\"product: \"@";
worksheet["B1:E1"].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
worksheet["B1:E3"].Borders.SetAllBorders(Color.Black, BorderLineStyle.Thin);
#region #CustomNumberFormat
// Set cell values.
worksheet["B2:B3"].Value = -15.50;
worksheet["C2:C3"].Value = 555;
worksheet["D2:D3"].Value = 0;
worksheet["E2:E3"].Value = "Name";
//Apply custom number format.
worksheet["B3:E3"].NumberFormat = "[Green]#.00;[Red]#.00;[Blue]0.00;[Magenta]\"product: \"@";
#endregion #CustomNumberFormat
}
finally {
workbook.EndUpdate();
}
}
static void ProtectRange(IWorkbook workbook)
{
#region #ProtectRange
workbook.BeginUpdate();
Worksheet worksheet = workbook.Worksheets[0];
worksheet["C3:E8"].Borders.SetAllBorders(Color.Black, BorderLineStyle.Thin);
// Give specific user permission to edit a range in a protected worksheet
ProtectedRange protectedRange = worksheet.ProtectedRanges.Add("My Range", worksheet["C3:E8"]);
EditRangePermission permission = new EditRangePermission();
permission.UserName = Environment.UserName;
permission.DomainName = Environment.UserDomainName;
permission.Deny = false;
protectedRange.SecurityDescriptor = protectedRange.CreateSecurityDescriptor(new EditRangePermission[] { permission });
protectedRange.SetPassword("123");
// Protect a worksheet
if (!worksheet.IsProtected)
{
worksheet.Protect("password", WorksheetProtectionPermissions.Default);
}
worksheet.ActiveView.ShowGridlines = false;
workbook.EndUpdate();
#endregion #ProtectRange
}
static void AddRangeConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #RangeConditionalFormatting
// Create the rule to identify values below 7 and above 19 in cells F2 through F15.
RangeConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddRangeConditionalFormatting(worksheet["$F$2:$F$15"], ConditionalFormattingRangeCondition.Outside, "7", "19");
// 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;
#endregion #RangeConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Identify book prices that are below $7 and above $19.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddColorScale3ConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #ColorScale3ConditionalFormatting
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.Formula, "=MIN($C$2:$D$15)");
// Set the midpoint threshold to the 50th percentile.
ConditionalFormattingValue midPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percentile, "50");
// Set the maximum threshold to the highest value in the range of cells using the MAX() formula.
ConditionalFormattingValue maxPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Number, "=MAX($C$2:$D$15)");
// Create the three-color scale rule to determine how values in cells C2 through D15 vary. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.
ColorScale3ConditionalFormatting cfRule = conditionalFormattings.AddColorScale3ConditionalFormatting(worksheet.Range["$C$2:$D$15"], minPoint, Color.Red, midPoint, Color.Yellow, maxPoint, Color.SkyBlue);
#endregion #ColorScale3ConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Examine cost distribution using a gradation of three colors. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddAverageConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #AverageConditionalFormatting
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Create the rule highlighting values that are above the average in cells C2 through C15.
AverageConditionalFormatting cfRule1 = conditionalFormattings.AddAverageConditionalFormatting(worksheet["$C$2:$C$15"], ConditionalFormattingAverageCondition.AboveOrEqual);
// 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 through D15.
AverageConditionalFormatting cfRule2 = conditionalFormattings.AddAverageConditionalFormatting(worksheet["$D$2:$D$15"], ConditionalFormattingAverageCondition.BelowOrEqual, 1);
// 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;
#endregion #AverageConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Determine cost values that are above the average in the first quarter and one standard deviation below the mean in the second quarter.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddColorScale2ConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #ColorScale2ConditionalFormatting
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Set the minimum threshold to the lowest value in the range of cells.
ConditionalFormattingValue minPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
// Set the maximum threshold to the highest value in the range of cells.
ConditionalFormattingValue maxPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
// Create the two-color scale rule to differentiate low and high values in cells C2 through D15. Blue represents the lower values and yellow represents the higher values.
ColorScale2ConditionalFormatting cfRule = conditionalFormattings.AddColorScale2ConditionalFormatting(worksheet.Range["$C$2:$D$15"], minPoint, Color.FromArgb(255, 0x9D, 0xE9, 0xFA), maxPoint, Color.FromArgb(255, 0xFF, 0xF6, 0xA9));
#endregion #ColorScale2ConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Examine cost distribution using a gradation of two colors. Blue represents the lower values and yellow represents the higher values.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddExpressionConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #ExpressionConditionalFormatting
// 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.GreaterThan, "=AVERAGE($F$2:$F$15)");
// 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;
#endregion #ExpressionConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Identify book prices that are greater than the average price.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddTimePeriodConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfTasks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #TimePeriodConditionalFormatting
// Create the rule to highlight today's dates in cells B2 through B6.
TimePeriodConditionalFormatting cfRule =
worksheet.ConditionalFormattings.AddTimePeriodConditionalFormatting(worksheet["$B$2:$B$6"], ConditionalFormattingTimePeriod.Today);
// 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);
#endregion #TimePeriodConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A8:B9"];
ruleExplanation.Value = "Determine the today's task in the TO DO list.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddRankConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #RankConditionalFormatting
// Create the rule to identify top three values in cells F2 through F15.
RankConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddRankConditionalFormatting(worksheet["$F$2:$F$15"], ConditionalFormattingRankCondition.TopByRank, 3);
// 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;
#endregion #RankConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Identify the top three price values.";
}
finally
{
workbook.EndUpdate();
}
}
static void SpecifyCellFont(IWorkbook workbook)
{
workbook.BeginUpdate();
try {
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells["A1"].Value = "Font Attributes";
worksheet.Cells["A1"].ColumnWidthInCharacters = 20;
#region #FontSettings
// Access the Font object.
SpreadsheetFont cellFont = worksheet.Cells["A1"].Font;
// Set the font name.
cellFont.Name = "Times New Roman";
// Set the font size.
cellFont.Size = 14;
// Set the font color.
cellFont.Color = Color.Blue;
// Format text as bold.
cellFont.Bold = true;
// Set font to be underlined.
cellFont.UnderlineType = UnderlineType.Single;
#endregion #FontSettings
}
finally {
workbook.EndUpdate();
}
}
static void MovePictureValue(IWorkbook workbook)
{
#region #movepicture
// Set the measurement unit to Millimeter.
workbook.Unit = DevExpress.Office.DocumentUnit.Millimeter;
workbook.Worksheets.ActiveWorksheet.DefaultRowHeight = 20;
workbook.Worksheets.ActiveWorksheet.DefaultColumnWidth = 20;
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets[0];
// Insert pictures.
Picture pic = worksheet.Pictures.AddPicture("Pictures\\x-spreadsheet.png", worksheet.Cells["A1"]);
worksheet.Pictures.AddPicture("Pictures\\x-spreadsheet.png", worksheet.Cells["A1"]);
// Specify picture name.
pic.Name = "Logo";
pic.AlternativeText = "Spreadsheet logo";
// Move a picture.
pic.Move(30, 50);
// Move and size the picture with underlying cells.
pic.Placement = Placement.MoveAndSize;
worksheet.Rows[1].Height += 20;
worksheet.Columns["D"].Width += 20;
// Move another picture to illustrate OffsetX, OffsetY properties.
worksheet.Pictures[1].Move(pic.OffsetY, pic.OffsetX);
}
finally
{
workbook.EndUpdate();
}
#endregion #movepicture
}
static void ClearCells(IWorkbook workbook)
{
workbook.BeginUpdate();
try {
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A:D"].ColumnWidthInCharacters = 30;
worksheet.Range["B1:D6"].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
worksheet["B1"].Value = "Initial Cell Content and Formatting:";
worksheet.MergeCells(worksheet["C1:D1"]);
worksheet["C1:D1"].Value = "Cleared Cells:";
worksheet["A2"].Value = "Clear All:";
worksheet["A3"].Value = "Clear Cell Content Only:";
worksheet["A4"].Value = "Clear Cell Formatting Only:";
worksheet["A5"].Value = "Clear Cell Hyperlinks Only:";
worksheet["A6"].Value = "Clear Cell Comments Only:";
// Specify initial content and formatting for cells.
CellRange sourceCells = worksheet["B2:D6"];
sourceCells.Value = DateTime.Now;
sourceCells.Style = workbook.Styles[BuiltInStyleId.Accent3_40percent];
sourceCells.Font.Color = Color.LightSeaGreen;
sourceCells.Font.Bold = true;
sourceCells.Borders.SetAllBorders(Color.Blue, BorderLineStyle.Dashed);
worksheet.Hyperlinks.Add(worksheet["B5"], "http://www.devexpress.com/", true, "DevExpress");
worksheet.Hyperlinks.Add(worksheet["C5"], "http://www.devexpress.com/", true, "DevExpress");
worksheet.Hyperlinks.Add(worksheet["D5"], "http://www.devexpress.com/", true, "DevExpress");
worksheet.Comments.Add(worksheet["B6"], "Author", "Cell Comment");
worksheet.Comments.Add(worksheet["C6"], "Author", "Cell Comment");
worksheet.Comments.Add(worksheet["D6"], "Author", "Cell Comment");
#region #ClearCell
// Remove all cell information (content, formatting, hyperlinks and comments).
worksheet.Clear(worksheet["C2:D2"]);
// Remove cell content.
worksheet.ClearContents(worksheet["C3"]);
worksheet["D3"].Value = null;
// Remove cell formatting.
worksheet.ClearFormats(worksheet["C4"]);
worksheet["D4"].Style = workbook.Styles.DefaultStyle;
// Remove hyperlinks from cells.
worksheet.ClearHyperlinks(worksheet["C5"]);
Hyperlink hyperlinkD5 = worksheet.Hyperlinks.GetHyperlinks(worksheet["D5"])[0];
worksheet.Hyperlinks.Remove(hyperlinkD5);
// Remove comments from cells.
worksheet.ClearComments(worksheet["C6"]);
#endregion #ClearCell
}
finally {
workbook.EndUpdate();
}
}
static void AddWorksheet(IWorkbook workbook)
{
workbook.BeginUpdate();
try
{
#region #AddWorksheet
// Add a new worksheet to the workbook.
// The worksheet will be inserted into the end of the existing collection of worksheets.
// Worksheet name is "SheetN", where N is a number following the largest number used in existing worksheet names of the same type.
workbook.Worksheets.Add();
// Add a new worksheet under the specified name.
workbook.Worksheets.Add().Name = "TestSheet1";
workbook.Worksheets.Add("TestSheet2");
// Add a new worksheet at the specified position in the collection of worksheets.
workbook.Worksheets.Insert(1, "TestSheet3");
workbook.Worksheets.Insert(3);
#endregion #AddWorksheet
}
finally
{
workbook.EndUpdate();
}
}
static void InsertShapeValue(IWorkbook workbook)
{
#region #insertshape
Stream imageStream = Assembly.GetExecutingAssembly().GetManifestResourceStream("SpreadsheetControl_WPF_API_Part02.Pictures.chart.png");
SpreadsheetImageSource imageSource = SpreadsheetImageSource.FromStream(imageStream);
workbook.BeginUpdate();
// Set the measurement unit to Millimeter.
workbook.Unit = DevExpress.Office.DocumentUnit.Millimeter;
try
{
Worksheet worksheet = workbook.Worksheets[0];
// Insert a picture from a file so that its top left corner is in the specified cell.
// By default the picture is named Picture1.. PictureNN.
worksheet.Pictures.AddPicture("Pictures\\chart.png", worksheet.Cells["D5"]);
// Insert a picture to fit in the specified range.
worksheet.Pictures.AddPicture("Pictures\\chart.png", worksheet.Range["B2"]);
// Insert a picture from the SpreadsheetImageSource at 120 mm from the left, 80 mm from the top,
// and resize it to a width of 70 mm and a height of 20 mm, locking the aspect ratio.
worksheet.Pictures.AddPicture(imageSource, 120, 80, 70, 20, true);
// Insert the picture to be removed.
worksheet.Pictures.AddPicture("Pictures\\chart.png", 0, 0);
// Remove the last inserted picture.
// Find the shape by its name. The method returns a collection of shapes with the same name.
Shape picShape = worksheet.Shapes.GetShapesByName("Picture 4")[0];
picShape.Delete();
}
finally
{
workbook.EndUpdate();
}
#endregion #insertshape
}
static void BuiltInPropertiesValue(IWorkbook workbook)
{
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Columns[0].WidthInCharacters = 2;
CellRange header = worksheet.Range["B2:C2"];
header[0].Value = "Property Name";
header[1].Value = "Value";
header.Style = workbook.Styles[BuiltInStyleId.Accent2];
#region #Built-inProperties
// Set the built-in document properties.
workbook.DocumentProperties.Title = "Spreadsheet API: document properties example";
workbook.DocumentProperties.Description = "How to manage document properties using the Spreadsheet API";
workbook.DocumentProperties.Keywords = "Spreadsheet, API, properties, OLEProps";
workbook.DocumentProperties.Company = "Developer Express Inc.";
// Display the specified built-in properties in a worksheet.
worksheet["B3"].Value = "Title";
worksheet["C3"].Value = workbook.DocumentProperties.Title;
worksheet["B4"].Value = "Description";
worksheet["C4"].Value = workbook.DocumentProperties.Description;
worksheet["B5"].Value = "Keywords";
worksheet["C5"].Value = workbook.DocumentProperties.Keywords;
worksheet["B6"].Value = "Company";
worksheet["C6"].Value = workbook.DocumentProperties.Company;
#endregion #Built-inProperties
worksheet.Columns.AutoFit(1, 2);
}
finally { workbook.EndUpdate(); }
}
static void ChangeCellColors(IWorkbook workbook)
{
workbook.BeginUpdate();
try {
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Range["C3:H10"].Merge();
worksheet.Range["C3:H10"].Value = "Test";
worksheet.Range["C3:H10"].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
worksheet.Range["C3:H10"].Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
worksheet.Cells["A1"].Value = "Test";
#region #ColorCells
// Format an individual cell.
worksheet.Cells["A1"].Font.Color = Color.Red;
worksheet.Cells["A1"].FillColor = Color.Yellow;
// Format a range of cells.
CellRange range = worksheet.Range["C3:H10"];
Formatting rangeFormatting = range.BeginUpdateFormatting();
rangeFormatting.Font.Color = Color.Blue;
rangeFormatting.Fill.BackgroundColor = Color.LightBlue;
rangeFormatting.Fill.PatternType = PatternType.LightHorizontal;
rangeFormatting.Fill.PatternColor = Color.Violet;
range.EndUpdateFormatting(rangeFormatting);
#endregion #ColorCells
}
finally {
workbook.EndUpdate();
}
}
static void AddComplexRangeConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfReport"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #ComplexRangeConditionalFormatting
// Create a union range to which the rule will be applied.
CellRange complexRange = worksheet.Range.Union(worksheet["G3:G6"], worksheet["G9:G12"]);
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Specify the automatic minimum value for the shortest bar.
ConditionalFormattingValue lowBound = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Auto);
// Specify the automatic maximum value for the longest bar.
ConditionalFormattingValue highBound = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Auto);
// Create the rule to compare yearly total values for different states.
DataBarConditionalFormatting cfRule = conditionalFormattings.AddDataBarConditionalFormatting(complexRange, lowBound, highBound, Color.FromArgb(0x29, 0x3E, 0x6A));
#endregion #ComplexRangeConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["B15"];
ruleExplanation.Value = "Compare values in the \"Yearly Total\" column using data bars.";
}
finally
{
workbook.EndUpdate();
}
}
static void SetDateFormats(IWorkbook workbook)
{
workbook.BeginUpdate();
try {
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:F1"].ColumnWidthInCharacters = 15;
worksheet.Range["A1:F1"].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
#region #DateTimeFormats
worksheet.Range["A1:F1"].Formula = "= Now()";
// Apply different date display formats.
worksheet.Cells["A1"].NumberFormat = "m/d/yy";
worksheet.Cells["B1"].NumberFormat = "d-mmm-yy";
worksheet.Cells["C1"].NumberFormat = "dddd";
// Apply different time display formats.
worksheet.Cells["D1"].NumberFormat = "m/d/yy h:mm";
worksheet.Cells["E1"].NumberFormat = "h:mm AM/PM";
worksheet.Cells["F1"].NumberFormat = "h:mm:ss";
#endregion #DateTimeFormats
}
finally {
workbook.EndUpdate();
}
}
static void CustomPropertiesValue(IWorkbook workbook)
{
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Columns[0].WidthInCharacters = 2;
worksheet["D6"].Value = "Mike Hamilton";
CellRange header = worksheet.Range["B2:C2"];
header[0].Value = "Property Name";
header[1].Value = "Value";
header.Style = workbook.Styles[BuiltInStyleId.Accent2];
header.ColumnWidthInCharacters = 20;
#region #CustomProperties
// Set the custom document properties.
workbook.DocumentProperties.Custom["Checked by"] = "Nancy Davolio";
workbook.DocumentProperties.Custom["Revision"] = 3;
workbook.DocumentProperties.Custom["Completed"] = true;
workbook.DocumentProperties.Custom["Published"] = DateTime.Now;
#endregion #CustomProperties
#region #LinkToContent
//Define a name to the cell to be linked to the custom property
workbook.DefinedNames.Add("checked_by", "D6");
//Link the custom property to the named cell
workbook.DocumentProperties.Custom.LinkToContent("Checked by", "checked_by");
#endregion #LinkToContent
#region #DisplayCustomProperties
// Display the specified custom properties in a worksheet.
IEnumerable <string> customPropertiesNames = workbook.DocumentProperties.Custom.Names;
int rowIndex = 2;
foreach (string propertyName in customPropertiesNames)
{
worksheet[rowIndex, 1].Value = propertyName;
worksheet[rowIndex, 2].Value = workbook.DocumentProperties.Custom[propertyName];
if (worksheet[rowIndex, 2].Value.IsDateTime)
{
worksheet[rowIndex, 2].NumberFormat = "[$-409]m/d/yyyy h:mm AM/PM";
}
rowIndex++;
}
#endregion #DisplayCustomProperties
#region #RemoveCustomProperty
// Remove an individual custom document property.
workbook.DocumentProperties.Custom["Published"] = null;
#endregion #RemoveCustomProperty
#region #ClearCustomProperties
// Remove all custom document properties.
workbook.DocumentProperties.Custom.Clear();
#endregion #ClearCustomProperties
}
finally { workbook.EndUpdate(); }
}
static void CustomTableStyle(IWorkbook workbook)
{
CreateTable(workbook);
workbook.BeginUpdate();
Worksheet worksheet = workbook.Worksheets[0];
#region #CustomTableStyle
// 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, 107, 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;
}
#endregion #CustomTableStyle
workbook.EndUpdate();
}
static void CreateTable(IWorkbook workbook)
{
workbook.BeginUpdate();
Worksheet worksheet = workbook.Worksheets[0];
GenerateTableData(worksheet);
#region #CreateTable
// Insert a table in the worksheet.
Table table = worksheet.Tables.Add(worksheet["B2:F5"], true);
// Format the table by applying a built-in table style.
table.Style = workbook.TableStyles[BuiltInTableStyleId.TableStyleMedium27];
// Access table columns and name them.
TableColumn productColumn = table.Columns[0];
productColumn.Name = "Product";
TableColumn priceColumn = table.Columns[1];
priceColumn.Name = "Price";
TableColumn quantityColumn = table.Columns[2];
quantityColumn.Name = "Quantity";
TableColumn discountColumn = table.Columns[3];
discountColumn.Name = "Discount";
TableColumn amountColumn = table.Columns[4];
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;
#endregion #CreateTable
workbook.EndUpdate();
}
public void Clear()
{
workbook.BeginUpdate();
try
{
foreach (KeyValuePair <string, Dictionary <string, Cell> > sheet in Sheets)
{
foreach (KeyValuePair <string, Cell> tag in sheet.Value)
{
tag.Value.Value = "";
}
}
}
finally
{
workbook.EndUpdate();
}
}
void evaluator_OnBeforeCompile(object sender, EventArgs e)
{
IWorkbook document = spreadsheetControl.Document;
document.BeginUpdate();
codeEditor.BeforeCompile();
spreadsheetControl.CreateNewDocument();
document.Unit = DevExpress.Office.DocumentUnit.Document;
document.EndUpdate();
}
static void FormatCell(IWorkbook workbook)
{
workbook.BeginUpdate();
try {
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells["B2"].Value = "Test";
worksheet.Range["C3:E6"].Value = "Test";
#region #CellFormatting
// Access the cell to be formatted.
Cell cell = worksheet.Cells["B2"];
// Specify font settings (font name, color, size and style).
cell.Font.Name = "MV Boli";
cell.Font.Color = Color.Blue;
cell.Font.Size = 14;
cell.Font.FontStyle = SpreadsheetFontStyle.Bold;
// Specify cell background color.
cell.Fill.BackgroundColor = Color.LightSkyBlue;
// Specify text alignment in the cell.
cell.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
cell.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
#endregion #CellFormatting
#region #RangeFormatting
// Access the range of cells to be formatted.
CellRange range = worksheet.Range["C3:E6"];
// Begin updating of the range formatting.
Formatting rangeFormatting = range.BeginUpdateFormatting();
// Specify font settings (font name, color, size and style).
rangeFormatting.Font.Name = "MV Boli";
rangeFormatting.Font.Color = Color.Blue;
rangeFormatting.Font.Size = 14;
rangeFormatting.Font.FontStyle = SpreadsheetFontStyle.Bold;
// Specify cell background color.
rangeFormatting.Fill.BackgroundColor = Color.LightSkyBlue;
// Specify text alignment in cells.
rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
// End updating of the range formatting.
range.EndUpdateFormatting(rangeFormatting);
#endregion #RangeFormatting
}
finally {
workbook.EndUpdate();
}
}
static void UseFunctionsInFormulas(IWorkbook workbook)
{
workbook.BeginUpdate();
try {
Worksheet worksheet = workbook.Worksheets[0];
// Provide static data.
worksheet.Cells["A1"].Value = "Data";
worksheet.Cells["A2"].Value = 15;
worksheet.Range["A3:A5"].Value = 3;
worksheet.Cells["A6"].Value = 20;
worksheet.Cells["A7"].Value = 15.12345;
worksheet.Cells["B1"].ColumnWidthInCharacters = 30;
worksheet.Cells["B1"].Value = "Formula String";
worksheet.Cells["B2"].Value = @"'=IF(A2<10, ""Normal"", ""Excess"")";
worksheet.Cells["B3"].Value = "'=AVERAGE(A2:A7)";
worksheet.Cells["B4"].Value = "'=SUM(A3:A5,A6,100)";
worksheet.Cells["B5"].Value = "'=ROUND(SUM(A6,A7),2)";
worksheet.Cells["B6"].Value = "'=Today()";
worksheet.Cells["B7"].Value = @"'=UPPER(""formula"")";
worksheet.Cells["C1"].Value = "Formula";
worksheet.Range["A1:C1"].FillColor = Color.LightGray;
worksheet.Range["A1:C7"].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Left;
#region #FunctionsInFormulas
// If the number in cell A2 is less than 10, the formula returns "Normal"
// and this text is displayed in cell C2. Otherwise, cell C2 displays "Excess".
worksheet.Cells["C2"].Formula = @"=IF(A2<10, ""Normal"", ""Excess"")";
// Calculate the average value for cell values within the "A2:A7" range.
worksheet.Cells["C3"].Formula = "=AVERAGE(A2:A7)";
// Add the values contained in cells A3 through A5, add the value contained in cell A6,
// and add 100 to that result.
worksheet.Cells["C4"].Formula = "=SUM(A3:A5,A6,100)";
// Use a nested function in a formula.
// Round the sum of the values contained in cells A6 and A7 to two decimal places.
worksheet.Cells["C5"].Formula = "=ROUND(SUM(A6,A7),2)";
// Add the current date to cell C6.
worksheet.Cells["C6"].Formula = "=Today()";
worksheet.Cells["C6"].NumberFormat = "m/d/yy";
// Convert the specified text to uppercase.
worksheet.Cells["C7"].Formula = @"=UPPER(""formula"")";
#endregion #FunctionsInFormulas
}
finally {
workbook.EndUpdate();
}
}
static void CreateSharedAndArrayFormulas(IWorkbook workbook)
{
workbook.BeginUpdate();
try {
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:D1"].ColumnWidthInCharacters = 10;
worksheet.Range["A1:D1"].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
worksheet.Range["A1:D1"].FillColor = Color.LightGray;
worksheet.MergeCells(worksheet.Range["A1:B1"]);
worksheet.Range["A1:B1"].Value = "Use Shared Formulas:";
worksheet.MergeCells(worksheet.Range["C1:D1"]);
worksheet.Range["C1:D1"].Value = "Use Array Formulas:";
#region #SharedFormulas
worksheet.Cells["A2"].Value = 1;
// Use the shared formula in the "A3:A11" range of cells.
worksheet.Range["A3:A11"].Formula = "=SUM(A2+1)";
// Use the shared formula in the "B2:B11" range of cells.
worksheet.Range["B2:B11"].Formula = "=A2+2";
#endregion #SharedFormulas
#region #ArrayFormulas
// Create an array formula that multiplies values contained in the cell range A2 through A11
// by the corresponding cells in the range B2 through B11,
// and displays the results in cells C2 through C11.
worksheet.Range.FromLTRB(2, 1, 2, 10).ArrayFormula = "=A2:A11*B2:B11";
// Create an array formula that multiplies values contained in the cell range C2 through C11 by 2
// and displays the results in cells D2 through D11.
worksheet.Range["D2:D11"].ArrayFormula = "=C2:C11*2";
// Create an array formula that multiplies values contained in the cell range B2 through D11,
// adds the results, and displays the total sum in cell D12.
worksheet.Cells["D12"].ArrayFormula = "=SUM(B2:B11*C2:C11*D2:D11)";
// Re-dimension an array formula range:
// delete the array formula and create a new range with the same formula.
if (worksheet.Cells["C13"].HasArrayFormula)
{
string af = worksheet.Cells["C13"].ArrayFormula;
worksheet.Cells["C13"].GetArrayFormulaRange().ArrayFormula = string.Empty;
worksheet.Range.FromLTRB(2, 1, 2, 10).ArrayFormula = af;
}
#endregion #ArrayFormulas
}
finally {
workbook.EndUpdate();
}
}
static void CopyCellDataAndStyle(IWorkbook workbook)
{
workbook.BeginUpdate();
try {
#region #CopyCell
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Columns["A"].WidthInCharacters = 32;
worksheet.Columns["B"].WidthInCharacters = 20;
Style style = workbook.Styles[BuiltInStyleId.Input];
// Specify the content and formatting for a source cell.
worksheet.Cells["A1"].Value = "Source Cell";
Cell sourceCell = worksheet.Cells["B1"];
sourceCell.Formula = "= PI()";
sourceCell.NumberFormat = "0.0000";
sourceCell.Style = style;
sourceCell.Font.Color = Color.Blue;
sourceCell.Font.Bold = true;
sourceCell.Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Thin);
// Copy all information from the source cell to the "B3" cell.
worksheet.Cells["A3"].Value = "Copy All";
worksheet.Cells["B3"].CopyFrom(sourceCell);
// Copy only the source cell content (e.g., text, numbers, formula calculated values) to the "B4" cell.
worksheet.Cells["A4"].Value = "Copy Values";
worksheet.Cells["B4"].CopyFrom(sourceCell, PasteSpecial.Values);
// Copy the source cell content (e.g., text, numbers, formula calculated values)
// and number formats to the "B5" cell.
worksheet.Cells["A5"].Value = "Copy Values and Number Formats";
worksheet.Cells["B5"].CopyFrom(sourceCell, PasteSpecial.Values | PasteSpecial.NumberFormats);
// Copy only the formatting information from the source cell to the "B6" cell.
worksheet.Cells["A6"].Value = "Copy Formats";
worksheet.Cells["B6"].CopyFrom(sourceCell, PasteSpecial.Formats);
// Copy all information from the source cell to the "B7" cell except for border settings.
worksheet.Cells["A7"].Value = "Copy All Except Borders";
worksheet.Cells["B7"].CopyFrom(sourceCell, PasteSpecial.All & ~PasteSpecial.Borders);
// Copy information only about borders from the source cell to the "B8" cell.
worksheet.Cells["A8"].Value = "Copy Borders";
worksheet.Cells["B8"].CopyFrom(sourceCell, PasteSpecial.Borders);
#endregion #CopyCell
}
finally {
workbook.EndUpdate();
}
}
//关系数据库导航栏双击事件
private void ucNaviRDB_TreeList_DoubleClick(object sender, EventArgs e)
{
try
{
TreeList tree = sender as TreeList;
TreeListHitInfo hi = tree.CalcHitInfo(tree.PointToClient(Control.MousePosition));
if (hi.Node != null)
{
string nodeName = (string)hi.Node["TABLE_NAME"];
//如果已经有这个tabPage
XtraTabPage ifTabPage = ComponentOperator.IfHasTabPage(nodeName, this.xtraTabControl_Main);
if (ifTabPage != null)
{
this.xtraTabControl_Main.SelectedTabPage = ifTabPage;
return;
}
//如果不包含该TabPage，则新建
DataTable dt = SQLServerConnection.GetDataByTableName(nodeName);
if (dt == null)
{
MessageBox.Show("获取数据失败。", "错误", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//表格控件
SpreadsheetControl ssc = new SpreadsheetControl();
IWorkbook workbook = ssc.Document;
workbook.BeginUpdate();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = nodeName;
worksheet.Import(dt, true, 0, 0); //import方法需要添加DevExpress.Docs命名空间
workbook.EndUpdate();
//TabPage
XtraTabPage xtp = new XtraTabPage();
xtp.Text = nodeName;
xtp.Controls.Add(ssc);
ssc.Dock = DockStyle.Fill;
this.xtraTabControl_Main.TabPages.Add(xtp);
this.xtraTabControl_Main.SelectedTabPage = xtp;
ssc.Refresh();
xtp.Refresh();
this.xtraTabControl_Main.Refresh();
this.Refresh();
}
}
catch
{
}
}
public static async Task<CheckExpressionResultCollection> GetCheckResultsAsync(byte[] checkfileData, Encoding encoding, IWorkbook document)
{
var resultList = new CheckExpressionResultCollection();
try
{
using (Stream ms = new MemoryStream(checkfileData))
{
var expList = await Task.Run(() => CheckExpressionParser.ParseStream(ms, encoding, document));
document.BeginUpdate();
foreach (var exp in expList)
{
try
{
var result = exp.Result;
if (!result.Ok)
{
resultList.Add(result);
}
foreach (var cell in result.Cells)
{
cell.Fill.BackgroundColor = result.Color;
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
}
}
foreach (var result in resultList)
{
foreach (var cell in result.Cells)
{
cell.Fill.BackgroundColor = result.Color;
}
}
document.EndUpdate();
}
}
catch (Exception ex)
{
logger.Fatal<Exception>(ex);
}
return resultList;
}
