Set Custom values on X-Axis

Hi,



I want to set custom values on X-Axis for corresponding values on Y-Axis but unable to do so. How do I achieve this?



Attached is the sample I created by Aspose.Cells, On x-axis I need values to be shown as 0,5,10, 15, 20, 25 but I am getting 1,2,3,4,5,6.



Here is the code I am using:



double[] totalPaid = { 50000, 50000, 50000, 50000, 50000, 50000 };

double[] centralScenario = { 47000, 54249.00, 62626.24, 72297.21, 83461.61, 96350.05 };

double[] optScenario = { 47000, 62543.37, 83233.6, 110768.44, 147412.21, 196178.25 };



Workbook workbook = new Workbook();



string totalPaidSeries ="{" + totalPaidArray.Aggregate((a, b) => Convert.ToString(a) + “,” + Convert.ToString(b)) + “}”;

string centralScenarioSeries = “{” + centralScenarioArray.Aggregate((a, b) => Convert.ToString(a) + “,” + Convert.ToString(b)) + “}”;

string optScenarioSeries = “{” + optScenarioArray.Aggregate((a, b) => Convert.ToString(a) + “,” + Convert.ToString(b)) + “}”;



//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = workbook.Worksheets[0];



//Adding a chart to the worksheet

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Line, 10,0, 30, 15);



//Accessing the instance of the newly added chart

Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];



chart = worksheet.Charts[“Chart 1”];



chart.ShowLegend = true;



chart.NSeries.Add(totalPaidSeries, true);

chart.NSeries.Add(centralScenarioSeries, true);

chart.NSeries.Add(optScenarioSeries, true);



chart.NSeries[0].Name = “Total Paid”;

chart.NSeries[0].Border.Color = Color.Yellow;

chart.NSeries[1].Name = “Central Scenario”;

chart.NSeries[1].Border.Color = Color.Gray;

chart.NSeries[2].Name = “Optimistic Scenario”;

chart.NSeries[2].Border.Color = Color.Red;



chart.Legend.Position = Aspose.Cells.Charts.LegendPositionType.Bottom;

chart.Title.Text = “Evolution of the Surrender Values”;



chart.ValueAxis.Title.Text = “Surrender Value\n” + “(OMR)”;

chart.ValueAxis.MajorGridLines.IsVisible = false;



chart.ValueAxis.Title.RotationAngle = 0;

chart.ValueAxis.Title.TextDirection = TextDirectionType.LeftToRight;

chart.ValueAxis.Title.TextHorizontalAlignment = TextAlignmentType.Center;

chart.ValueAxis.Title.TextVerticalAlignment = TextAlignmentType.Center;



chart.CategoryAxis.Title.Text = “Policy Terms (Years)”;

chart.CategoryAxis.MajorTickMark = TickMarkType.None;

chart.CategoryAxis.MinorTickMark = TickMarkType.Outside;



chart.ChartArea.Area.FillFormat.Type = FillType.Automatic;

chart.PlotArea.Area.FillFormat.Type = FillType.Automatic;



chart.PlotArea.Border.IsVisible = false;





workbook.Save(AppDomain.CurrentDomain.BaseDirectory + “AsposeCells.xlsx”);





// Convert chart to an image

string sChartImageFilePath = string.Concat(AppDomain.CurrentDomain.BaseDirectory, “AsposeCells.PNG”);

Aspose.Cells.Rendering.ImageOrPrintOptions oImageOrPrintOptions = new Aspose.Cells.Rendering.ImageOrPrintOptions();

oImageOrPrintOptions.IsCellAutoFit = false;

oImageOrPrintOptions.ImageFormat = System.Drawing.Imaging.ImageFormat.Png;



for (int iCtr = 0; iCtr
{

if (worksheet.Shapes[iCtr].Name == “Chart 1”)

{

worksheet.Shapes[iCtr].ToImage(sChartImageFilePath, oImageOrPrintOptions);

worksheet.Pictures.Add(3, 0, 27, 9, sChartImageFilePath);

// File.Delete(sChartImageFilePath);

break;

}

}





Thanks,

Hi,

Thanks for your posting and using Aspose.Cells.

Please use the Chart.NSeries.CategoryData property for your needs. Please see the following sample code. It loads your sample excel file and sets the given property and re-save. I have attached the output excel file generated with this code for your reference.

C#
Workbook wb = new Workbook(“sample.xlsx”);

Worksheet ws = wb.Worksheets[0];

Chart ch = ws.Charts[0];
ch.NSeries.CategoryData = “=Sheet1!$A$2:$A$7”;

wb.Save(“output.xlsx”);

Hi Shakeel,

Thanks for your help, however I have one more issue.

As you can see in the excel attached by you, there are two zeros. One on x-axis and one on y-axis (it shows 0.00 but I will make it an integer to show only 0)

How can I remove one of the zeros and show only one in the center or in other words common 0 for x and y axis?

Thanks,
Ashish

Hi,

Thanks for your posting and using Aspose.Cells.

Please download the output excel file provided by me in the earlier post and fix it manually using Microsoft Excel and re-submit to us. We will then look into it and provide you the Aspose.Cells sample code to achieve the same thing. Please also let us know the Microsoft Excel steps you took to fix it manually. Thanks for your cooperation in this regard and have a good day.

Hi,

Thanks for using Aspose.Cells.

In Excel, labels of vertical axis and horizontal axis are independent. The two zero values cannot display as one.

However, you may change the format and position by following code. I have also attached the output excel file generated with this code for a reference.

C#
Workbook wb = new Workbook(“sample.xlsx”);

Worksheet ws = wb.Worksheets[0];

Chart ch = ws.Charts[0];
ch.NSeries.CategoryData = “=Sheet1!$A$2:$A$7”;

ch.CategoryAxis.AxisBetweenCategories = false;
ch.ValueAxis.TickLabels.NumberFormat = “0”;

wb.Save(“output-rog.xlsx”);

I’m looking for the same thing but I don’t want to do
ch.NSeries.CategoryData = “=Sheet1!$A$2:$A$7”;

I want to give it the values like ={“a”,“A”;“a”,“B”} but can’t get it to draw them on 2 rows since some have grouping.

@Mihai_Bratulescu,

Thanks for sharing some details.

We are not entirely certain about your issue. Please provide us sample code (runnable) or prefreably a simple console demo application (you may zip it prior attaching) to show the issue, we will check it soon. Also, provide sample files (input file and output (by Aspose.Cells APIs)) and your expected file (you may create the desired chart manually in MS Excel). All this will help us to evaluate your issue precisely and to consequently figure it out soon.

chart.png (8.4 KB)
This is what I want to achieve but can’t get the formula right. I tried using ‘,’ and ‘;’ for row and columns but excel won’t let me mix them.

I’ve opened the attacked chart and tried to replace the excel range with something like:
chart.NSeries.CategoryData = "={1, 2, 3, 4, 5; \"A\", \"B\"}";

@Mihai_Bratulescu,

Thanks for the screenshot.

As requested earlier please provide us Excel file having your expected chart (you may create the desired chart manually in MS Excel) in it, we will check it soon.

demo.zip (13.2 KB)
Here is the result I want without writing data in cells and using ranges.
Thank you.

@Mihai_Bratulescu,

Thanks for the sample file containing the chart.

Is this your expected chart but it is using source data in the cells. You may create this example chart (whose source data range comes from cells in the Worksheet) by Aspose.Cells APIs. But I asked you to provide a template file containing your desired chart using static data/values (it should not be from source cells). I do not think MS Excel supports to set such static data/strings directly as category data without using source cells range in the worksheet. I am afraid, if you cannot do it in MS Excel manually, you may not accomplish the task via Aspose.Cells APIs.