Setting Min and Max values for Dates

Aspose.Cells Product Family
1

Hi, I have a chart the X axis of which is a Date Series. It is a six months stock chart. How do I set Min and Max values? The MinValue and MaxValue properties only accept doubles and not dates.
Thanks,
Alex.

2

Hi Alex,

This feature is not supported yet. I will add it in the future release. It will be available in the next week.

3

Thanks, I’ll wait a week.

4

Any word on this?

5

It’s supported now. Please try the attached dll with the following sample code:

chart.CategoryAxis.CategoryType = CategoryType.TimeScale;
chart.CategoryAxis.MajorUnitScale = TimeUnit.Days;
chart.CategoryAxis.MajorUnit = 60;
chart.CategoryAxis.MinValue = new DateTime(2002, 5, 6);
chart.CategoryAxis.MaxValue = new DateTime(2005, 1, 16);

6

This is exactly what I need. Except now setting MinValue and MaxValue to a numeric value is broken. I need the CategoryAxis to be Dates but the ValueAxis are numbers, and when I set the

chart.ValueAxis.MinValue = (int)min;

I get an Exception
Aspose.Excel.ExcelException: MinValue should be numeric or Date values.

Thanks,
Alex.

7

Hi Alex,

Above fix only accepts double and DateTime value type. I fixed this bug and please try the attached fix.

8

That worked perfectly.
Thank You.

9

@YarmulnikA,
Aspose.Excel is discontinued and no more actively developed now. A new product Aspose.Cells has replaced it and contains all the latest features available in different versions of MS Excel. You can set min and max values for category axis as dates and int value as value axis. Here is an example that demonstrates this feature:

Workbook wb = new Workbook();
wb.Worksheets.Clear();
Worksheet ws = wb.Worksheets.Add("New");
Style style = wb.CreateStyle();
style.Name = "DateTimeStyle";
style.Custom = "yyyy-mm-dd hh:mm";
DateTime dt = DateTime.Now.Date;
ws.Cells[23, 2].PutValue(dt);
ws.Cells[23, 2].SetStyle(style);
ws.Cells[24, 2].PutValue(dt.AddDays(12));
ws.Cells[24, 2].SetStyle(style);
ws.Cells[25, 2].PutValue(dt.AddDays(24));
ws.Cells[25, 2].SetStyle(style);
ws.Cells[26, 2].PutValue(dt.AddDays(36));
ws.Cells[26, 2].SetStyle(style);
ws.Cells[27, 2].PutValue(dt.AddDays(48));
ws.Cells[27, 2].SetStyle(style);
ws.Cells[28, 2].PutValue(dt.AddDays(60));
ws.Cells[28, 2].SetStyle(style);
ws.Cells[29, 2].PutValue(dt.AddDays(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.CategoryAxis.TickLabelPosition = TickLabelPositionType.Low;
chart.CategoryAxis.MinValue = new DateTime(2021, 1, 1, 0, 0, 0);
chart.CategoryAxis.MaxValue = new DateTime(2021, 12, 31, 23, 59, 59);
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.DropLines.Color = Color.BlanchedAlmond;
aSerie.DropLines.Weight = WeightType.HairLine;
ws.AutoFitColumns();
wb.Save(@"outResult1.xlsx", SaveFormat.Xlsx);

For detailed description of this feature, following document can be referred:
Charts

The free trial version of this new product can be downloaded here:
Aspose.Cells for .NET (Latest Version)

You can test all the features of this product by downloading a runnable solution here.