Set Label in Category Axis

Aspose.Cells Product Family
1

Hi,


I am using Aspose cell to plot chart. Can you please tell me how to set labels for Category Axis.
I need to assign category axis with label like “1-Jan-2013” , “4-May-2013” etc…

Regards
Aniket
2

Hi,


Well, the category axis labels come from the source range for the chart. I would recommend you to kindly see the sample demos/examples of charts for a complete reference on how to create chart and specify the data source for the chart’s value axis and category axis with Aspose.Cells APIs and how to format the tick labels accordingly:


I will also write a sample code snippet for your reference:

Sample code:

Workbook wb = new Workbook();
wb.Worksheets.Clear();
Worksheet ws = wb.Worksheets.Add("New");
Style style = wb.Styles[wb.Styles.Add()];
style.Name = "DateTimeStyle";
style.Custom = "dd-mm hh:mm:ss";

DateTime dt = DateTime.Now.Date;
ws.Cells[23, 2].PutValue(dt);
ws.Cells[23, 2].SetStyle(style);
ws.Cells[24, 2].PutValue(dt.AddHours(12));
ws.Cells[24, 2].SetStyle(style);
ws.Cells[25, 2].PutValue(dt.AddHours(24));
ws.Cells[25, 2].SetStyle(style);
ws.Cells[26, 2].PutValue(dt.AddHours(36));
ws.Cells[26, 2].SetStyle(style);
ws.Cells[27, 2].PutValue(dt.AddHours(48));
ws.Cells[27, 2].SetStyle(style);
ws.Cells[28, 2].PutValue(dt.AddHours(60));
ws.Cells[28, 2].SetStyle(style);
ws.Cells[29, 2].PutValue(dt.AddHours(72));
ws.Cells[29, 2].SetStyle(style);
ws.Cells[23, 3].PutValue(3);
ws.Cells[24, 3].PutValue(4);
ws.Cells[25, 3].PutValue(9);
ws.Cells[26, 3].PutValue(13);
ws.Cells[27, 3].PutValue(16);
ws.Cells[28, 3].PutValue(3);
ws.Cells[29, 3].PutValue(7);

Chart chart = ws.Charts[ws.Charts.Add(ChartType.ScatterConnectedByLinesWithoutDataMarker, 1, 1, 22, 12)];
chart.CategoryAxis.TickLabels.NumberFormat = "dd-MMM-yyyy";
chart.CategoryAxis.TickLabels.RotationAngle = 45;
chart.CategoryAxis.TickLabels.Font.Size = 8;
chart.Legend.Position = LegendPositionType.Bottom;
chart.ValueAxis.TickLabels.NumberFormat = "0";
chart.ValueAxis.MinValue = 0;
chart.ValueAxis.MaxValue = 20;
chart.Placement = PlacementType.Move;
String chartSubTitle = DateTime.Now.ToString();
chart.Title.Text = "Data" + "\n" + chartSubTitle;
Series aSerie = chart.NSeries[chart.NSeries.Add(String.Format("{0}!{1}{2}:{1}{3}", ws.Name, "D", 24, 30), true)];
aSerie.XValues = String.Format("{0}!{1}{2}:{1}{3}", ws.Name, "C", 24, 30);
aSerie.Name = "Date";
aSerie.Line.Color = Color.BlanchedAlmond;
aSerie.Line.Weight = WeightType.HairLine;
wb.Save(@"e:\\test2\\outResult1.xlsx", SaveFormat.Xlsx);

Thank you.
3

Thanks for the reply.


But I need to assign Category Axis Label not base on data range. I need to simply assign some string values. For example…

Category Axis Label [0] = “31 - Jun - 2013”
Category Axis Label [0] = “10 - Jul - 2013”
Category Axis Label [0] = "15 - Jul - 2013"

All those labels are just some string values and not a date. I don’t want is to refer some data in excel.

Regards,
Aniket


4

Hi,


Well, it is really easy to specify constant value instead of setting the data range in the sheet, see the sample lines of code.
e.g

chart.NSeries.Add("{10, 20, 30}", true);
chart.NSeries.CategoryData = “{“31 - Jun - 2013”, “10 - Jul - 2013”, “15 - Jul - 2013”}”;

Thank you.
5

Thanks for the suggestion. Its working for me.

I have an additional question, Can we hide legend’s in excel chart’s using Aspose.cell.

Regards,
Aniket

6

Hi,


I think you may try to use chart.ShowLegend attribute to set it to “false” for your needs.

Thank you.
7

Hi!


I am working with Aspose.Cells. [#region Assembly Aspose.Cells.dll, v8.2.0.0
// c:\users\ybansod\documents\aspose\Aspose.Cells for .NET\bin\net2.0\Aspose.Cells.dll
#endregion]

-------------------------------------------------------------------------------------------------------------------
//I cant see any button added in my chart
//Chart: [ int ChartIndex = ChartWorksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Line, 1, 1, 30, 14); ]

Aspose.Cells.Drawing.Button button = chart.Shapes.AddButton( 2,0,2,0,10,30);
button.Text = “Home”;
button.Placement = Aspose.Cells.Drawing.PlacementType.Move;//FreeFloating;
button.FillFormat.ForeColor = Color.LightGray;

-------------------------------------------------------------------------------------------------------------------

//I am unable to add labels also.Is this an issue with the coordinates that I am providing?
Aspose.Cells.Drawing.Label LabelSource = chart.Shapes.AddLabelInChart(20, 2, 35, 40);
LabelSource.Text = "Source: ";
LabelSource.Placement = Aspose.Cells.Drawing.PlacementType.FreeFloating;

-------------------------------------------------------------------------------------------------------------------

//Also, How to change the format of the tick labels? I have dates in dd-mm-yyyy format as a //string in a column that are being used by the chart to plot the series.I havent set the cell //format for the column to have only dates filled in them. I want the tick label format on x axis //to be mmm-yy. But the following doesnt work for me :

chart.CategoryAxis.CategoryType = Aspose.Cells.Charts.CategoryType.CategoryScale;
// chart.CategoryAxis.MajorUnitScale = Aspose.Cells.Charts.TimeUnit.Months;(doesnt work)
// chart.CategoryAxis.MajorUnit = 30.0;
chart.CategoryAxis.BaseUnitScale = Aspose.Cells.Charts.TimeUnit.Months;
chart.CategoryAxis.TickLabels.NumberFormat = “mmm-yy”;
chart.CategoryAxis.MajorTickMark = TickMarkType.Inside;
chart.CategoryAxis.TickLabelSpacing = 30;
chart.CategoryAxis.TickMarkSpacing = 30;
-------------------------------------------------------------------------------------------------------------------

How do i change the color of the series line?

chart.NSeries[SeriesIndex].Area.ForegroundColor = Color.LightBlue; // Isnt working for me
8
Hi Yashali Bansod,

Thanks for providing us some details.

yashalibansod25:
-------------------------------------------------------------------------------------------------------------------
//I cant see any button added in my chart
//Chart: [ int ChartIndex = ChartWorksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Line, 1, 1, 30, 14); ]

Aspose.Cells.Drawing.Button button = chart.Shapes.AddButton( 2,0,2,0,10,30);
button.Text = "Home";
button.Placement = Aspose.Cells.Drawing.PlacementType.Move;//FreeFloating;
button.FillFormat.ForeColor = Color.LightGray;

-------------------------------------------------------------------------------------------------------------------

//I am unable to add labels also.Is this an issue with the coordinates that I am providing?
Aspose.Cells.Drawing.Label LabelSource = chart.Shapes.AddLabelInChart(20, 2, 35, 40);
LabelSource.Text = "Source: ";
LabelSource.Placement = Aspose.Cells.Drawing.PlacementType.FreeFloating;

-------------------------------------------------------------------------------------------------------------------

Please see the sample code below and the output attached Excel file which contains a label and a button control in the chart fine for your reference (I am just adding the controls to the designer chart in the template file):
e.g
Sample code:

//Create a new Workbook.
//Open the existing file.
Workbook workbook = new Workbook("e:\\test\\exp_piechart.xls");

//Get the designer chart in the second sheet.
Worksheet sheet = workbook.Worksheets[1];
Aspose.Cells.Charts.Chart chart = sheet.Charts[0];

//Add a new label to the chart.
Aspose.Cells.Drawing.Label label = chart.Shapes.AddLabelInChart(100, 100, 250, 830);

//Set the caption of the label.
label.Text = "A Label In Chart";

//Set the Placement Type, the way the
//label is attached to the cells.
label.Placement = Aspose.Cells.Drawing.PlacementType.FreeFloating;

//Set the fill color of the label.
label.FillFormat.ForeColor = Color.Azure;


//Add a new button to the chart.
Aspose.Cells.Drawing.Button button = (Aspose.Cells.Drawing.Button)chart.Shapes.AddShapeInChart(MsoDrawingType.Button, PlacementType.FreeFloating, 500, 400, 2800, 540);

//Set the caption of the label.
button.Text = "A Button In Chart";

//Save the excel file.
workbook.Save("e:\\test2\\outtstlabelbuttonchart_2.xls");


yashalibansod25:
//Also, How to change the format of the tick labels? I have dates in dd-mm-yyyy format as a //string in a column that are being used by the chart to plot the series.I havent set the cell //format for the column to have only dates filled in them. I want the tick label format on x axis //to be mmm-yy. But the following doesnt work for me :

chart.CategoryAxis.CategoryType = Aspose.Cells.Charts.CategoryType.CategoryScale;
//chart.CategoryAxis.MajorUnitScale = Aspose.Cells.Charts.TimeUnit.Months;(doesnt work)
//chart.CategoryAxis.MajorUnit = 30.0;
chart.CategoryAxis.BaseUnitScale = Aspose.Cells.Charts.TimeUnit.Months;
chart.CategoryAxis.TickLabels.NumberFormat = "mmm-yy";
chart.CategoryAxis.MajorTickMark = TickMarkType.Inside;
chart.CategoryAxis.TickLabelSpacing = 30;
chart.CategoryAxis.TickMarkSpacing = 30;
-------------------------------------------------------------------------------------------------------------------

Well, applying datetime formatting would only work if the underlying cells contains DateTime values, make sure the values are DateTime and not string values.

yashalibansod25:
How do i change the color of the series line?

chart.NSeries[SeriesIndex].Area.ForegroundColor = Color.LightBlue; // Isnt working for me

See the sample code for your reference:
e.g
Sample code:

chart.NSeries[SeriesIndex].Border.Color = Color.LightBlue;

Thank you.

9

Hi!


Thanks a lot! That was really helpful!
-------------------------------------------------------------------------------------------------------------------
I haven’t been able to figure out how to apply 3d format to a shape in a chart.

here’s a code snippet:

Aspose.Cells.Drawing.RectangleShape rect = (Aspose.Cells.Drawing.RectangleShape)chart.Shapes.AddShapeInChart(MsoDrawingType.Rectangle, PlacementType.FreeFloating, 20, 30, 320,150 );
rect.Text = “Home”;
rect.AutoShapeType = AutoShapeType.RoundedRectangle;
rect.FillFormat.ForeColor = Color.LightGray;


-------------------------------------------------------------------------------------------------------------------

I also want to disable all the cells in the sheet. How to do that?

-------------------------------------------------------------------------------------------------------------------

how to lock the chart in a fixed position without having to protect the sheet?
-------------------------------------------------------------------------------------------------------------------
10
Hi,

yashalibansod25:
-------------------------------------------------------------------------------------------------------------------
I haven't been able to figure out how to apply 3d format to a shape in a chart.

here's a code snippet:

Aspose.Cells.Drawing.RectangleShape rect = (Aspose.Cells.Drawing.RectangleShape)chart.Shapes.AddShapeInChart(MsoDrawingType.Rectangle, PlacementType.FreeFloating, 20, 30, 320,150 );
rect.Text = "Home";
rect.AutoShapeType = AutoShapeType.RoundedRectangle;
rect.FillFormat.ForeColor = Color.LightGray;


-------------------------------------------------------------------------------------------------------------------

I have tested your code snippet it works fine. But, I am afraid, if you are talking about applying other 3D formattings to shapes, I am afraid, it is not supported. Currently, you can only apply 3D formatting to chart objects (e.g data series etc.) , see the document for your reference:

http://www.aspose.com/docs/display/cellsnet/Applying+3D+Format


yashalibansod25:

I also want to disable all the cells in the sheet. How to do that?

-------------------------------------------------------------------------------------------------------------------

I think you may protect worksheet, so that the cells would be locked/ disabled in it, see the document for your complete reference:
http://www.aspose.com/docs/display/cellsnet/Protecting+Worksheets

yashalibansod25:

how to lock the chart in a fixed position without having to protect the sheet?
-------------------------------------------------------------------------------------------------------------------

How could you do this in MS Excel, could you perform your task in MS Excel manually, save the file and post us here with all the details, we will check how to do it via Aspose.Cells APIs. I think you may refer to the document "Protecting Worksheets" above for your reference and try to protect a range of cells in the worksheet for your needs.

Thank you.