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