public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Value = 1;
worksheet.Range["A2"].Formula = "=A1";
worksheet.Range["A3"].Formula = "=SUM(A1, A2)";
//when get value, calc engine will first calculate and cache the result, then returns the cached result.
var value_A2 = worksheet.Range["A2"].Value;
var value_A3 = worksheet.Range["A3"].Value;
//disable calc engine.
workbook.EnableCalculation = false;
//Dirty() method will clear the cached value of the workbook.
workbook.Dirty();
//Calculate() will not work, because of workbook.EnablCalculation is false.
workbook.Calculate();
//it returns 0 because of no cache value exist.
var value_A2_1 = worksheet.Range["A2"].Value;
var value_A3_1 = worksheet.Range["A3"].Value;
worksheet.Range["A1"].Value = 2;
//enable calc engine.
workbook.EnableCalculation = true;
//Dirty() method will clear the cached value of Range A2:A3.
worksheet.Range["A2:A3"].Dirty();
//Calculate() method will calculate and cache the result, it will return the cache value directly when get value later.
worksheet.Range["A2:A3"].Calculate();
//it returns cache value directly, does not calculate again.
var value_A2_2 = worksheet.Range["A2"].Value;
var value_A3_2 = worksheet.Range["A3"].Value;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
object[,] data = new object[, ] {
{ "Name", "City", "Birthday", "Eye color", "Weight", "Height" },
{ "Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67, 165 },
{ "Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62, 134 },
{ "Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72, 180 },
{ "Natalie", "Washington", new DateTime(1972, 8, 8), "Blue", 66, 163 },
{ "Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", 76, 176 },
{ "Angela", "Washington", new DateTime(1993, 2, 15), "Brown", 68, 145 }
};
worksheet.Range["A1:F7"].Value = data;
//three color scale.
IColorScale threeColorScaleRule = worksheet.Range["E2:E7"].FormatConditions.AddColorScale(ColorScaleType.ThreeColorScale);
threeColorScaleRule.ColorScaleCriteria[0].Type = ConditionValueTypes.LowestValue;
threeColorScaleRule.ColorScaleCriteria[0].FormatColor.Color = Color.Red;
threeColorScaleRule.ColorScaleCriteria[1].Type = ConditionValueTypes.Percent;
threeColorScaleRule.ColorScaleCriteria[1].Value = 30;
threeColorScaleRule.ColorScaleCriteria[1].FormatColor.Color = Color.Yellow;
threeColorScaleRule.ColorScaleCriteria[2].Type = ConditionValueTypes.HighestValue;
threeColorScaleRule.ColorScaleCriteria[2].FormatColor.Color = Color.Green;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
object[,] data = new object[, ] {
{ "Name", "City", "Birthday", "Eye color", "Weight", "Height" },
{ "Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67, 165 },
{ "Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62, 134 },
{ "Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72, 180 },
{ "Natalie", "Washington", new DateTime(1972, 8, 8), "Blue", 66, 163 },
{ "Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", 76, 176 },
{ "Angela", "Washington", new DateTime(1993, 2, 15), "Brown", 68, 145 }
};
worksheet.Range["A1:F7"].Value = data;
//data bar rule.
IDataBar dataBar = worksheet.Range["E2:E7"].FormatConditions.AddDatabar();
dataBar.MinPoint.Type = ConditionValueTypes.LowestValue;
dataBar.MinPoint.Value = null;
dataBar.MaxPoint.Type = ConditionValueTypes.HighestValue;
dataBar.MaxPoint.Value = null;
dataBar.BarFillType = DataBarFillType.Gradient;
dataBar.BarColor.Color = Color.Green;
dataBar.Direction = DataBarDirection.Context;
dataBar.AxisColor.Color = Color.Red;
dataBar.AxisPosition = DataBarAxisPosition.Automatic;
dataBar.NegativeBarFormat.BorderColorType = DataBarNegativeColorType.Color;
dataBar.NegativeBarFormat.BorderColor.Color = Color.Blue;
dataBar.NegativeBarFormat.ColorType = DataBarNegativeColorType.Color;
dataBar.NegativeBarFormat.Color.Color = Color.Pink;
dataBar.ShowValue = false;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
GrapeCity.Documents.Excel.Drawing.IShape shape = worksheet.Shapes.AddChart(GrapeCity.Documents.Excel.Drawing.ChartType.AreaStacked, 250, 20, 360, 230);
worksheet.Range["A1:C13"].Value = new object[, ] {
{ 0, 59.18, 27.14 },
{ 44.64, 52.22, 25.08 },
{ 45.21, 49.80, 57.99 },
{ 24.32, 37.30, 42.73 },
{ 58.34, 34.43, 28.34 },
{ 31.89, 69.78, 46.88 },
{ 41.79, 63.94, 56.24 },
{ 67.94, 57.40, 27.78 },
{ 49.87, 48.26, 52.06 },
{ 62.39, 67.43, 33.33 },
{ 54.76, 22.95, 50.36 },
{ 28.33, 36.60, 36.61 },
{ 22.77, 55.65, 65.64 },
{ 20.34, 49.35, 45.60 },
{ 32.10, 47.60, 20.62 },
{ 26.37, 63.00, 53.97 },
{ 35, 75, 60 },
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:C13"], GrapeCity.Documents.Excel.Drawing.RowCol.Columns);
shape.Chart.ChartTitle.Text = "Area Stacked Chart";
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
object[,] data = new object[, ]
{
{ "Number", "Date", "Customer", "Description", "Trend", "0-30 Days", "30-60 Days", "60-90 Days", ">90 Days", "Amount" },
{ "1001", new DateTime(2017, 5, 21), "Customer A", "Invoice 1001", null, 1200.15, 1916.18, 1105.23, 1806.53, null },
{ "1002", new DateTime(2017, 3, 18), "Customer B", "Invoice 1002", null, 896.23, 1005.53, 1800.56, 1150.49, null },
{ "1003", new DateTime(2017, 6, 15), "Customer C", "Invoice 1003", null, 827.63, 1009.23, 1869.23, 1002.56, null }
};
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["B2:K5"].Value = data;
worksheet.Range["B:K"].ColumnWidth = 15;
worksheet.Tables.Add(worksheet.Range["B2:K5"], true);
worksheet.Tables[0].Columns[9].DataBodyRange.Formula = "=SUM(Table1[@[0-30 Days]:[>90 Days]])";
//create a new group of sparklines.
worksheet.Range["F3:F5"].SparklineGroups.Add(SparkType.Line, "G3:J5");
worksheet.Range["G7:J7"].Value = new object[] { new DateTime(2011, 12, 16), new DateTime(2011, 12, 17), new DateTime(2011, 12, 18), new DateTime(2011, 12, 19) };
worksheet.Range["F3"].SparklineGroups[0].DateRange = "G7:J7";
worksheet.Range["F3"].SparklineGroups[0].Axes.Horizontal.Axis.Visible = true;
worksheet.Range["F3"].SparklineGroups[0].Axes.Horizontal.Axis.Color.Color = Color.Green;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
object[,] data = new object[, ] {
{ "Name", "City", "Birthday", "Eye color", "Weight", "Height" },
{ "Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67, 165 },
{ "Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62, 134 },
{ "Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72, 180 },
{ "Natalie", "Washington", new DateTime(1972, 8, 8), "Blue", 66, 163 },
{ "Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", 76, 176 },
{ "Angela", "Washington", new DateTime(1993, 2, 15), "Brown", 68, 145 }
};
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:F7"].Value = data;
worksheet.Range["A:F"].ColumnWidth = 15;
worksheet.Range["F2"].Interior.Color = Color.LightPink;
worksheet.Range["F3"].Interior.Color = Color.LightGreen;
worksheet.Range["F4"].Interior.Color = Color.LightPink;
worksheet.Range["F5"].Interior.Color = Color.LightGreen;
worksheet.Range["F6"].Interior.Color = Color.LightBlue;
worksheet.Range["F7"].Interior.Color = Color.LightPink;
//"F4" will in the top.
worksheet.Sort.SortFields.Add(new CellColorSortField(worksheet.Range["F2:F7"], worksheet.Range["F4"].DisplayFormat.Interior, SortOrder.Ascending));
worksheet.Sort.Range = worksheet.Range["A2:F7"];
worksheet.Sort.Orientation = SortOrientation.Columns;
worksheet.Sort.Apply();
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
var range = worksheet.Range["A5:B7"];
//set value for cell A7.
range.Cells[4].Value = "A7";
//cell is B6
range.Cells[1, 1].Value = "B6";
//row count is 3 and range is A6:B6.
var rowCount = range.Rows.Count;
var row = range.Rows[1].ToString();
//set interior color for row range A6:B6.
range.Rows[1].Interior.Color = Color.LightBlue;
//column count is 2 and range is B5:B7.
var columnCount = range.Columns.Count;
var column = range.Columns[1].ToString();
//set values for column range B5:B7.
range.Columns[1].Interior.Color = Color.LightSkyBlue;
//entire rows are from row 5 to row 7
var entirerow = range.EntireRow.ToString();
//entire columns are from column A to column B
var entireColumn = range.EntireColumn.ToString();
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
object[,] data = new object[, ]
{
{ "Number", "Date", "Customer", "Description", "Trend", "0-30 Days", "30-60 Days", "60-90 Days", ">90 Days", "Amount" },
{ "1001", new DateTime(2017, 5, 21), "Customer A", "Invoice 1001", null, 1200.15, 1916.18, 1105.23, 1806.53, null },
{ "1002", new DateTime(2017, 3, 18), "Customer B", "Invoice 1002", null, 896.23, 1005.53, 1800.56, 1150.49, null },
{ "1003", new DateTime(2017, 6, 15), "Customer C", "Invoice 1003", null, 827.63, 1009.23, 1869.23, 1002.56, null }
};
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["B2:K5"].Value = data;
worksheet.Range["B:K"].ColumnWidth = 15;
worksheet.Tables.Add(worksheet.Range["B2:K5"], true);
worksheet.Tables[0].Columns[9].DataBodyRange.Formula = "=SUM(Table1[@[0-30 Days]:[>90 Days]])";
//create a new group of sparklines.
worksheet.Range["F3"].SparklineGroups.Add(SparkType.Line, "G3:J3");
//create another new group of sparklines.
worksheet.Range["F4:F5"].SparklineGroups.Add(SparkType.Column, "G4:J5");
//clear F3 and F4 cell's sparkline. F5 cell's sparkline still exist.
worksheet.Range["F3, F4"].SparklineGroups.Clear();
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A2:A6"].NumberFormat = "m/d/yyyy";
worksheet.Range["A1:D6"].Value = new object[, ]
{
{ null, "S1", "S2", "S3" },
{ new DateTime(2015, 10, 7), 10, 25, 25 },
{ new DateTime(2015, 10, 24), 51, 36, 27 },
{ new DateTime(2015, 11, 8), 52, 85, 30 },
{ new DateTime(2015, 11, 25), 22, 65, 65 },
{ new DateTime(2015, 12, 10), 23, 69, 69 }
};
GrapeCity.Documents.Excel.Drawing.IShape shape = worksheet.Shapes.AddChart(GrapeCity.Documents.Excel.Drawing.ChartType.ColumnClustered, 250, 20, 360, 230);
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], GrapeCity.Documents.Excel.Drawing.RowCol.Columns, true, true);
GrapeCity.Documents.Excel.Drawing.IAxis category_axis = shape.Chart.Axes.Item(GrapeCity.Documents.Excel.Drawing.AxisType.Category);
category_axis.MaximumScale = new DateTime(2015, 12, 20).ToOADate();
category_axis.MinimumScale = new DateTime(2015, 10, 1).ToOADate();
category_axis.BaseUnit = GrapeCity.Documents.Excel.Drawing.TimeUnit.Months;
category_axis.MajorUnitScale = GrapeCity.Documents.Excel.Drawing.TimeUnit.Months;
category_axis.MajorUnit = 1;
category_axis.MinorUnitScale = GrapeCity.Documents.Excel.Drawing.TimeUnit.Days;
category_axis.MinorUnit = 15;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
GrapeCity.Documents.Excel.Drawing.IShape shape = worksheet.Shapes.AddChart(GrapeCity.Documents.Excel.Drawing.ChartType.ColumnClustered, 250, 20, 360, 230);
worksheet.Range["A1:B13"].Value = new object[, ] {
{ "Blue Column", "Red Line" },
{ 75, 20 },
{ 149, 50 },
{ 105, 30 },
{ 55, 80 },
{ 121, 40 },
{ 76, 110 },
{ 128, 50 },
{ 114, 140 },
{ 75, 60 },
{ 105, 170 },
{ 145, 70 },
{ 110, 100 },
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:B13"], GrapeCity.Documents.Excel.Drawing.RowCol.Columns);
shape.Chart.ChartTitle.Text = "Combination Chart";
//change series type
GrapeCity.Documents.Excel.Drawing.ISeries series2 = shape.Chart.SeriesCollection[1];
series2.ChartType = GrapeCity.Documents.Excel.Drawing.ChartType.LineMarkers;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
object[,] data = new object[, ] {
{ "Name", "City", "Birthday", "Sex", "Weight", "Height" },
{ "Bob", "NewYork", new DateTime(1968, 6, 8), "male", 80, 180 },
{ "Betty", "NewYork", new DateTime(1972, 7, 3), "female", 72, 168 },
{ "Gary", "NewYork", new DateTime(1964, 3, 2), "male", 71, 179 },
{ "Hunk", "Washington", new DateTime(1972, 8, 8), "male", 80, 171 },
{ "Cherry", "Washington", new DateTime(1986, 2, 2), "female", 58, 161 },
{ "Eva", "Washington", new DateTime(1993, 2, 5), "female", 71, 180 }
};
//Set data.
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1:F7"].Value = data;
sheet.Tables.Add(sheet.Range["A1:F7"], true);
//Save csv options
CsvSaveOptions options = new CsvSaveOptions();
options.SeparatorString = "-";
//Change the path to real export path when save.
workbook.Save(this.CurrentDirectory + "dest.csv", options);
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
GrapeCity.Documents.Excel.Drawing.IShape shape = worksheet.Shapes.AddChart(GrapeCity.Documents.Excel.Drawing.ChartType.ColumnClustered, 250, 20, 360, 230);
worksheet.Range["A1:D6"].Value = new object[, ]
{
{ null, "S1", "S2", "S3" },
{ "Item1", 10, 25, 25 },
{ "Item2", -20, 36, 27 },
{ "Item3", 62, 70, -30 },
{ "Item4", 22, 65, 65 },
{ "Item5", 23, 50, 50 }
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], GrapeCity.Documents.Excel.Drawing.RowCol.Columns, true, true);
//config chart title style
shape.Chart.HasTitle = true;
shape.Chart.ChartTitle.Text = "MyChartTitle";
shape.Chart.ChartTitle.Font.Size = 15;
shape.Chart.ChartTitle.Font.Name = "Bradley Hand ITC";
shape.Chart.ChartTitle.Format.Fill.Color.RGB = Color.DarkOrange;
shape.Chart.ChartTitle.Format.Line.Color.RGB = Color.CornflowerBlue;
shape.Chart.ChartTitle.Format.Line.Weight = 1.5;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Value = "Single Underline";
worksheet.Range["A1"].Font.Underline = UnderlineType.Single;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
GrapeCity.Documents.Excel.Drawing.IShape shape = worksheet.Shapes.AddChart(GrapeCity.Documents.Excel.Drawing.ChartType.ColumnClustered, 250, 20, 360, 230);
worksheet.Range["A1:D6"].Value = new object[, ]
{
{ null, "S1", "S2", "S3" },
{ "Item1", 10, 25, 25 },
{ "Item2", -20, 36, 27 },
{ "Item3", 62, 70, -30 },
{ "Item4", 22, 65, 65 },
{ "Item5", 23, 50, 50 }
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], GrapeCity.Documents.Excel.Drawing.RowCol.Columns, true, true);
//config chart title style with rich text
shape.Chart.HasTitle = true;
shape.Chart.ChartTitle.TextFrame.TextRange.Paragraphs.Add("ChartSubtitle");
shape.Chart.ChartTitle.TextFrame.TextRange.Paragraphs.Add("ChartTitle", 0);
shape.Chart.ChartTitle.TextFrame.TextRange.Paragraphs[0].Font.Color.RGB = Color.CornflowerBlue;
shape.Chart.ChartTitle.TextFrame.TextRange.Paragraphs[0].Font.Size = 15;
shape.Chart.ChartTitle.TextFrame.TextRange.Paragraphs[1].Font.Color.RGB = Color.Orange;
shape.Chart.ChartTitle.TextFrame.TextRange.Paragraphs[1].Font.Size = 10;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet sheet = workbook.Worksheets[0];
int row = 50;
int column = 14;
object[,] data = new object[row, column];
for (int i = 0; i < row; i++)
{
for (int j = 0; j < column; j++)
{
data[i, j] = "R" + i + "C" + j;
}
}
//Set data
sheet.Range["A1:N50"].Value = data;
//Set paper size
sheet.PageSetup.PaperSize = PaperSize.A5;
//With API the margin's unit is pound, with Excel the margins display unit is inch.
//One inch eaquals to 72 pounds. If the top margin is 0.8 inch, then please set PageSetup.TopMargin = 0.8*72(57.6);
sheet.PageSetup.TopMargin = 57.6;
//Top margin in excel is 0.8 inch
sheet.PageSetup.BottomMargin = 21.6;
sheet.PageSetup.LeftMargin = 28.8;
sheet.PageSetup.RightMargin = 0;
sheet.PageSetup.HeaderMargin = 0;
sheet.PageSetup.FooterMargin = 93.6;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
GrapeCity.Documents.Excel.Drawing.IShape shape = worksheet.Shapes.AddChart(GrapeCity.Documents.Excel.Drawing.ChartType.ColumnClustered, 250, 20, 360, 230);
worksheet.Range["A1:D6"].Value = new object[, ]
{
{ null, "S1", "S2", "S3" },
{ 1, -25, 25, 25 },
{ 2, 51, 36, 27 },
{ 3, 52, 80, 30 },
{ 4, 22, -20, 65 },
{ 5, 23, 69, 69 }
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], GrapeCity.Documents.Excel.Drawing.RowCol.Columns, true, true);
GrapeCity.Documents.Excel.Drawing.IAxis category_axis = shape.Chart.Axes.Item(GrapeCity.Documents.Excel.Drawing.AxisType.Category);
GrapeCity.Documents.Excel.Drawing.IAxis value_axis = shape.Chart.Axes.Item(GrapeCity.Documents.Excel.Drawing.AxisType.Value);
//config tick label's format
category_axis.TickLabelPosition = GrapeCity.Documents.Excel.Drawing.TickLabelPosition.NextToAxis;
category_axis.TickLabelSpacing = 2;
category_axis.TickLabels.Font.Color.RGB = Color.DarkOrange;
category_axis.TickLabels.Font.Size = 12;
category_axis.TickLabels.NumberFormat = "#,##0.00";
value_axis.TickLabels.NumberFormat = "#,##0;[Red]#,##0";
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
IRange rangeA1B2 = worksheet.Range["A1:B2"];
rangeA1B2.Merge();
rangeA1B2.Interior.Pattern = GrapeCity.Documents.Excel.Pattern.LinearGradient;
(rangeA1B2.Interior.Gradient as ILinearGradient).ColorStops[0].Color = Color.Red;
(rangeA1B2.Interior.Gradient as ILinearGradient).ColorStops[1].Color = Color.Yellow;
(rangeA1B2.Interior.Gradient as ILinearGradient).Degree = 90;
IRange rangeE1E2 = worksheet.Range["D1:E2"];
rangeE1E2.Merge();
rangeE1E2.Interior.Pattern = GrapeCity.Documents.Excel.Pattern.LightDown;
rangeE1E2.Interior.Color = Color.Pink;
rangeE1E2.Interior.PatternColorIndex = 5;
IRange rangeG1H2 = worksheet.Range["G1:H2"];
rangeG1H2.Merge();
rangeG1H2.Interior.Pattern = GrapeCity.Documents.Excel.Pattern.RectangularGradient;
(rangeG1H2.Interior.Gradient as IRectangularGradient).ColorStops[0].Color = Color.Red;
(rangeG1H2.Interior.Gradient as IRectangularGradient).ColorStops[1].Color = Color.Green;
(rangeG1H2.Interior.Gradient as IRectangularGradient).Bottom = 0.2;
(rangeG1H2.Interior.Gradient as IRectangularGradient).Right = 0.3;
(rangeG1H2.Interior.Gradient as IRectangularGradient).Top = 0.4;
(rangeG1H2.Interior.Gradient as IRectangularGradient).Left = 0.5;
worksheet.Range["J1:K2"].Merge();
worksheet.Range["J1:K2"].Interior.Color = Color.Green;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["C2:E4"].Value = new object[, ]
{
{ new TimeSpan(13, 0, 0), new TimeSpan(13, 29, 59), new TimeSpan(13, 30, 0) },
{ new TimeSpan(14, 0, 0), new TimeSpan(15, 0, 0), new TimeSpan(16, 30, 0) },
{ new TimeSpan(19, 0, 0), new TimeSpan(18, 29, 59), new TimeSpan(18, 30, 0) }
};
//create time validation.
worksheet.Range["C2:E4"].Validation.Add(ValidationType.Time, ValidationAlertStyle.Stop, ValidationOperator.Between, new TimeSpan(13, 30, 0), new TimeSpan(18, 30, 0));
//Modify validation.
worksheet.Range["C2:E4"].Validation.Formula1 = new TimeSpan(14, 30, 0);
worksheet.Range["C2:E4"].Validation.Formula2 = new TimeSpan(19, 0, 0);
//judge if Range["C2:E4"] has validation.
for (int i = 1; i <= 3; i++)
{
for (int j = 2; j <= 4; j++)
{
if (worksheet.Range[i, j].HasValidation)
{
//set the range[i, j]'s interior color.
worksheet.Range[i, j].Interior.Color = Color.LightBlue;
}
}
}
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
//Load template file
var fileStream = this.GetTemplateStream();
workbook.Open(fileStream);
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
Stream stream = this.GetTemplateStream();
//Open csv file stream.
workbook.Open(stream, OpenFileFormat.Csv);
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
//Create chart, chart's range is Range["G1:M21"]
GrapeCity.Documents.Excel.Drawing.IShape shape = worksheet.Shapes.AddChart(GrapeCity.Documents.Excel.Drawing.ChartType.ColumnClustered, 300, 10, 300, 300);
worksheet.Range["A1:D6"].Value = new object[, ]
{
{ null, "S1", "S2", "S3" },
{ "Item1", 10, 25, 25 },
{ "Item2", -51, -36, 27 },
{ "Item3", 52, -85, -30 },
{ "Item4", 22, 65, 65 },
{ "Item5", 23, 69, 69 }
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], GrapeCity.Documents.Excel.Drawing.RowCol.Columns, true, true);
//Range["G1:M21"] must contain chart's range, copy a new shape to Range["N1:T21"]
worksheet.Range["G1:M21"].Copy(worksheet.Range["N1"]);
//worksheet.Range["G1:M21"].Copy(worksheet.Range["N1:T21"]);
//Cross sheet copy, copy a new chart to worksheet2's Range["N1:T21"]
//IWorksheet worksheet2 = workbook.Worksheets.Add()
//worksheet.Range["G1:M21"].Copy(worksheet2.Range["E1"]);
//worksheet.Range["G1:M21"].Copy(worksheet2.Range["N1:T21"]);
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
object[,] data = new object[, ] {
{ "Name", "City", "Birthday", "Eye color", "Weight", "Height" },
{ "Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67, 165 },
{ "Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62, 134 },
{ "Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72, 180 },
{ "Natalie", "Washington", new DateTime(1972, 8, 8), "Blue", 66, 163 },
{ "Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", 76, 176 },
{ "Angela", "Washington", new DateTime(1993, 2, 15), "Brown", 68, 145 }
};
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:F7"].Value = data;
worksheet.Range["A:F"].ColumnWidth = 15;
IIconSetCondition iconset = worksheet.Range["F2:F7"].FormatConditions.AddIconSetCondition();
iconset.IconSet = workbook.IconSets[IconSetType.Icon3TrafficLights1];
//green traffic light will in the top.
worksheet.Sort.SortFields.Add(new IconSortField(worksheet.Range["F2:F7"], workbook.IconSets[IconSetType.Icon3TrafficLights1][2], SortOrder.Ascending));
worksheet.Sort.Range = worksheet.Range["A2:F7"];
worksheet.Sort.Orientation = SortOrientation.Columns;
worksheet.Sort.Apply();
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets.Add();
//workbook must contain one visible worksheet at least, if delete the one visible worksheet, it will throw exception.
worksheet.Delete();
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet sheet = workbook.Worksheets[0];
int row = 50;
int column = 14;
object[,] data = new object[row, column];
for (int i = 0; i < row; i++)
{
for (int j = 0; j < column; j++)
{
data[i, j] = "R" + i + "C" + j;
}
}
//Set data.
sheet.Range["A1:N50"].Value = data;
sheet.Range["A2:N4"].Value = "TitleRows";
sheet.Range["A2:N4"].Interior.Color = Color.Green;
sheet.Range["C1:E50"].Value = "TitleColumns";
sheet.Range["C1:E50"].Interior.Color = Color.Yellow;
sheet.PageSetup.PrintHeadings = true;
//Set print titles.
sheet.PageSetup.PrintTitleRows = "$2:$4";
sheet.PageSetup.PrintTitleColumns = "$C:$E";
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Value = "aaa";
worksheet.Range["A2"].Value = "bbb";
worksheet.Range["A3"].Value = "ccc";
worksheet.Range["C2:E4"].Value = new object[, ]
{
{ "aaa", "bbb", "ccc" },
{ "aaa1", "bbb1", "ccc1" },
{ "aaa2", "bbb2", "ccc2" }
};
//create list validation.
worksheet.Range["C2:E4"].Validation.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "=$a$1:$a$3");
IValidation validation = worksheet.Range["C2:E4"].Validation;
validation.InCellDropdown = true;
//judge if Range["C2:E4"] has validation.
for (int i = 1; i <= 3; i++)
{
for (int j = 2; j <= 4; j++)
{
if (worksheet.Range[i, j].HasValidation)
{
//set the range[i, j]'s interior color.
worksheet.Range[i, j].Interior.Color = Color.LightBlue;
}
}
}
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["C2:E4"].Value = new object[, ]
{
{ "aa", "bb", "aa1" },
{ "ccc", "dddd", "dddd1" },
{ "ff", "ffff", "ffff1" }
};
//create text length validation, text length between 2 and 3.
worksheet.Range["C2:E4"].Validation.Add(ValidationType.TextLength, ValidationAlertStyle.Stop, ValidationOperator.Between, 2, 3);
//judge if Range["C2:E4"] has validation.
for (int i = 1; i <= 3; i++)
{
for (int j = 2; j <= 4; j++)
{
if (worksheet.Range[i, j].HasValidation)
{
//set the range[i, j]'s interior color.
worksheet.Range[i, j].Interior.Color = Color.LightBlue;
}
}
}
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
//Load template file Event budget.xlsx from resource
var fileStream = this.GetTemplateStream("Event budget.xlsx");
workbook.Open(fileStream);
var worksheet = workbook.ActiveSheet;
//change range B2's font size.
worksheet.Range["B2"].Font.Size = 22;
//change range E4's font style to bold.
worksheet.Range["E4"].Font.Bold = true;
//change table style.
worksheet.Tables["tblAdmissions"].TableStyle = workbook.TableStyles["TableStyleLight10"];
worksheet.Tables["tblAds"].TableStyle = workbook.TableStyles["TableStyleLight10"];
worksheet.Tables["tblVendors"].TableStyle = workbook.TableStyles["TableStyleLight10"];
worksheet.Tables["tblItems"].TableStyle = workbook.TableStyles["TableStyleLight10"];
//modify range F4:G5's cell style.
worksheet.Range["F4:G5"].Interior.ThemeColor = ThemeColor.Light1;
worksheet.Range["F4:G5"].Interior.TintAndShade = -0.15;
worksheet.Range["F4:G5"].Font.ThemeFont = ThemeFont.Major;
worksheet.Range["F4:G5"].Font.Size = 12;
worksheet.Range["F4:G5"].Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.None;
worksheet.Range["F5:G5"].NumberFormat = "$#,##0.00";
//modify table columns' style.
worksheet.Range["F8:G11, F15:G18, F22:G25, F29:G33"].Interior.ThemeColor = ThemeColor.Light1;
worksheet.Range["F8:G11, F15:G18, F22:G25, F29:G33"].Interior.TintAndShade = -0.15;
worksheet.Range["E8:G11, E15:G18, E22:G25, E29:G33"].NumberFormat = "$#,##0.00";
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Value = "Font size is 15";
worksheet.Range["A1"].Font.Size = 15;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet sheet = workbook.Worksheets[0];
//Set pages' data.
sheet.Range["A1:J46"].Value = "Page1";
sheet.Range["A1:J46"].Interior.Color = Color.LightGreen;
sheet.Range["A47:J92"].Value = "Page2";
sheet.Range["A47:J92"].Interior.Color = Color.LightYellow;
sheet.Range["K1:T46"].Value = "Page3";
sheet.Range["K1:T46"].Interior.Color = Color.OrangeRed;
sheet.Range["K47:T92"].Value = "Page4";
sheet.Range["K47:T92"].Interior.Color = Color.DarkOrange;
sheet.Range["U1:AD46"].Value = "Page5";
sheet.Range["U1:AD46"].Interior.Color = Color.LightBlue;
sheet.Range["U47:AD92"].Value = "Page6";
sheet.Range["U47:AD92"].Interior.Color = Color.IndianRed;
sheet.PageSetup.PrintHeadings = true;
//Set print page range, print p1, p3 to p5.
sheet.PageSetup.PrintPageRange = "1,3-5";
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
object[,] data = new object[, ] {
{ "Name", "City", "Birthday", "Eye color", "Weight", "Height" },
{ "Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67, 165 },
{ "Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62, 134 },
{ "Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72, 180 },
{ "Natalie", "Washington", new DateTime(1972, 8, 8), "Blue", 66, 163 },
{ "Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", 76, 176 },
{ "Angela", "Washington", new DateTime(1993, 2, 15), "Brown", 68, 145 }
};
worksheet.Range["A1:F7"].Value = data;
worksheet.Range["A:F"].ColumnWidth = 15;
worksheet.Range["A1:F7"].AutoFilter(4, "<80");
worksheet.Range["A1:F7"].AutoFilter(0, "*e*");
worksheet.Range["A1:F7"].AutoFilter(1, "=Washington");
//clear second column filter only.
worksheet.Range["A1:F7"].AutoFilter(1);
//clear all filters
worksheet.AutoFilter.ShowAllData();
//another way to clear all filters, same as AutoFilter.ShowAllData()
worksheet.ShowAllData();
}
