Scatter Chart - Color Quadrants Challenge

Aspose.Cells Product Family
1

I need to create a scatter chart in Excel using Aspose Cells, but I also need to color the quadrants. I am able to do it manually using the steps outlined on this website:

http://peltiertech.com/Excel/Charts/ColoredQuadrantBackground.html

My question is, how do I do this programmatically using Aspose?

Lastly, I also need to embed this chart into PowerPoint, but this should be no different than what I have already been doing.

2

Hi Wil,

1. For scatter chart:

Code Sample:
Workbook excel = new Workbook();

Worksheet sheet = excel.Worksheets[0];

Cells cells = excel.Worksheets[0].Cells;

cells["A1"].PutValue("Daily Rainfall");

cells["B1"].PutValue("Particulate");

cells["A2"].PutValue(1.9);

cells["B2"].PutValue(137);

cells["A3"].PutValue(3.6);

cells["B3"].PutValue(128);

cells["A4"].PutValue(4.1);

cells["B4"].PutValue(122);

cells["A5"].PutValue(4.3);

cells["B5"].PutValue(117);

cells["A6"].PutValue(5);

cells["B6"].PutValue(114);

cells["A7"].PutValue(5.4);

cells["B7"].PutValue(114);

cells["A8"].PutValue(5.7);

cells["B8"].PutValue(112);

cells["A9"].PutValue(5.9);

cells["B9"].PutValue(110);

cells["A10"].PutValue(7.3);

cells["B10"].PutValue(104);

excel.Worksheets.Add(SheetType.Chart);

Charts charts = excel.Worksheets[1].Charts;

int chartIndex = charts.Add(ChartType.LineWithDataMarkers, 1, 3, 25, 12);

Chart chart = charts[chartIndex];

chart.MajorGridLines.IsVisible = false;

chart.Legend.Position = LegendPositionType.Top;

chart.Title.Text = "Scatter Chart:Particulate Levels in Rainfall";

chart.Title.TextFont.Color = Color.Black;

chart.Title.TextFont.IsBold = true;

chart.Title.TextFont.Size = 12;

chart.ValueAxis.MinValue = 50;

chart.ValueAxis.MaxValue = 200;

chart.NSeries.Add("Sheet1!B2:B10", true);

//chart.NSeries.CategoryData = "Sheet1!A2:A10";

chart.NSeries[0].XValues = "Sheet1!A2:A10";

NSeries nseries = chart.NSeries;

for (int i = 0; i < nseries.Count; i++)

{

nseries[i].Name = "Test Series";

nseries[i].MarkerStyle = ChartMarkerType.Circle;

nseries[i].MarkerBackgroundColor = Color.Red;

nseries[i].MarkerForegroundColor = Color.Blue;

nseries[i].MarkerSize = 8;

nseries[i].DataLabels.IsValueShown = true;

nseries[i].DataLabels.Postion = LabelPositionType.Below;
}

chart.NSeries[0].TrendLines.Add(TrendlineType.Linear, "test_line");

chart.NSeries[0].TrendLines[0].Weight = WeightType.HairLine;

chart.NSeries[0].TrendLines[0].Color = Color.Blue;

chart.NSeries[0].TrendLines[0].Backward = 0.4;

chart.CategoryAxis.Title.Text = cells["A1"].Value.ToString();

chart.ValueAxis.Title.Text = cells["B1"].Value.ToString();

excel.Save("f:\\test\\outscatterchart_test.xls");

For further details please follow the links below:

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/introduction-to-charts-its-types.html

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/how-to-create-a-chart.html

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/setting-charts-appearance.html

2. Embedding Chart in PPT:

Code Sample:
Workbook book = new Workbook("D:\\Aspose Data\\Book1.xls");

Aspose.Cells.Worksheet sheet = book.Worksheets[0];

ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();


imgOptions.ImageFormat = ImageFormat.Emf;

imgOptions.HorizontalResolution = 200;

imgOptions.VerticalResolution = 200;

sheet.PageSetup.PrintArea = "A1:D5";

SheetRender sr = new SheetRender(sheet, imgOptions);

MemoryStream strm = new MemoryStream();

for (int j = 0; j < sr.PageCount; j++)

{

sr.ToImage(j, "d:\\Aspose Data\\cellrange_" + sheet.Index + j + ".emf");

}

strm.Position = 0;

Presentation pres = new Presentation();

Slide slide = pres.Slides[0];

Metafile image = new Metafile("d:\\Aspose Data\\cellrange_00.emf");

Picture picture = new Picture(pres, image);

pres.Pictures.Add(picture);

slide.Shapes.AddPictureFrame(picture.PictureId, 1000, 1000, 2000, 2000);

pres.Write("D:\\Example.ppt");

For further details please follow the links below:
http://www.aspose.com/documentation/.net-components/aspose.slides-for-.net/working-with-ole-object-frames.html

Thanks,

3

Aspose staff,

I tried out your sample script, but it seems that my requirements were not clear enough. I am not inquiring about how to make a scatter chart. The challenge that I am facing is (1) create a quadrant chart (2) define the color of each quadrant.

The reason why the title of this post refers to a scatter chart is because this is the chart type that I had to use to manually create what I'd like to do through Aspose Cells programmatically. Please review the following website where you will find the steps to create this chart:

http://peltiertech.com/Excel/Charts/ColoredQuadrantBackground.html

Again, the requirements are to create a quadrant chart and to define the color of each quadrant. It is not about how to create a XY scatter chart. If you could provide sample code, that would be a tremendous help. Thanks again!

4

Hi Wil,

please provide us a MS Excel temple in which this type of chart has been used. This will help us a lot.

Thanks,

5

Attached is an Excel file that I’ve manually created. Please let me know if you have any further questions.

6

Hi Wil,

Please try the following code snippet.

Code snippet:
Workbook workbook = new Workbook(@"D:\FileTemp\QuadrantChart.xlsx");
int index = workbook.Worksheets[0].Charts.Add(ChartType.ColumnStacked, 10, 0, 25, 8);

Chart chart = workbook.Worksheets[0].Charts[index];
chart.NSeries.Add("A1:D2",true);
for (int i = 0; i < 4; i++)
{
chart.NSeries[i].GapWidth = 0;
}
chart.ShowLegend = false;
chart.ValueAxis.MaxValue = 2.0;
chart.ValueAxis.MajorUnit = 0.5;
chart.ValueAxis.TickLabelPosition = TickLabelPositionType.None;
chart.ValueAxis.MajorTickMark = TickMarkType.None;
index = chart.NSeries.Add("G3:G6", true);
chart.NSeries[index].Type = ChartType.Scatter;
chart.NSeries[index].Name = "=G2";
chart.NSeries[index].XValues = "=F3:F6";
chart.NSeries[index].PlotOnSecondAxis = true;
chart.NSeries[index].Line.IsVisible = false;
chart.NSeries[index].MarkerStyle = ChartMarkerType.Triangle;
chart.SecondCategoryAxis.IsVisible = true;
chart.SecondValueAxis.IsVisible = true;
chart.SecondCategoryAxis.CrossType = CrossType.Automatic;
chart.SecondCategoryAxis.AxisBetweenCategories = false;

chart.SecondCategoryAxis.TickLabelPosition = TickLabelPositionType.None;
chart.SecondCategoryAxis.MajorTickMark = TickMarkType.None;
chart.SecondValueAxis.MajorTickMark = TickMarkType.Outside;

workbook.Save(@"D:\FileTemp\dest.xlsx");

Thanks,