Bug: Setting CategoryAxis Unit and Unit scale causes CategoryAxis Grid lines not to render

Hi.


I believe I found a bug.

I have an application that return data for every quarter. I want the Category Ticklabes to display on every second year, and I want a Tick mark on every year. I also want a Grid line on every year.

However, when I set the Ticklabes MajorUnit and MajorUnitScale properties, the Category Minor gridlines disappear. When I convert it into an image, the Category major gridlines disappear as well.

Please find the test application attached.

PS. I’ve could not replicate the image in my test application. For some reason the ToImage method keeps throwing an error, which it doesn’t do in my working application, and I could figure out why.

Hi, is anybody working on this yet to see if it is an actual bug? If not, I would like to know what I’m doing wrong.


Thanks.

Hi,

I have generated the attached file using your code. Please provide me your expected output xlsx file and some screenshots to illustrate what the problem you are encountering.

You can create your desired output file manually using Ms-Excel and also highlight the problems with red-circles in a screenshot which you can done using Ms-Paint or any other drawing tool.

Hi Shakeel.


The spreadsheet you’ve attached illustrate the problem quite nicely. (See attached image)
The grid in excel does not show the vertical minor gridlines, even though the code specified that they should show. The image is even worse, because it does not render the vertical major lines either.

As you probably have seen, when you pass false to the executing method, thereby side stepping the following code:

chart.CategoryAxis.MajorUnit = 2;
chart.CategoryAxis.MajorUnitScale = TimeUnit.Years;

chart.CategoryAxis.MinorUnit = 1;
chart.CategoryAxis.MinorUnitScale = TimeUnit.Years;


…the Minor and Major gridlines gets rendered as expected. It seems that the inner workings of these properties conflict with the way Aspose deals with gridlines.

Regards.

PS. How did you fix the problem with the “ToImage” method?

Hi,

Thanks for illustration. I will look into it further and update you.

I used the following code to fix the problem.

C#


System.IO.MemoryStream stream = new System.IO.MemoryStream();

stream.Position = 0;

chartImage.Save(stream, ImageFormat.Jpeg);

worksheet.Pictures.Add(21, 2, stream);

Hi,

In order to show major and minor grid lines, use the following code.

Please see the source and output xlsx file and the screenshot.

However, it is a bug that in the output image, grid lines are not showing up. We will fix it asap.

I have used the latest version:
Aspose.Cells for .NET v7.0.1.1


This issue has been logged as CELLSNET-30778.

Screenshot:


C#


string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[1];


Chart chart = worksheet.Charts[0];


//Show minor gridlines of both axis

chart.CategoryAxis.MinorGridLines.IsVisible = true;

chart.ValueAxis.MinorGridLines.IsVisible = true;


//Show major gridlines of both axis

chart.CategoryAxis.MajorGridLines.IsVisible = true;

chart.ValueAxis.MajorGridLines.IsVisible = true;


//Output image does now show gridlines, it is a bug

chart.ToImage(filePath + “.out.jpg”, ImageFormat.Jpeg);


//Output xlsx file

workbook.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);


Output Image - GridLines are not showing up:

Hi Shakeel.


I’m having trouble to see what exactly it is you did differently, as I did set the IsVisible properties in my test application.

It seems that you reopen the created file reset the IsVisible properties, and save it again. This is not a very pleasing work around, but even doing that, I was unable to get vertical minor lines to show.

Even after I’ve replaced my dll you’ve provided, I could not get the minor lines to show.

The code changes I’ve made is below:

private void StartHere(bool withTickSpecification)
{
Workbook workbook = new Workbook(FileFormatType.Xlsx);
Guid id = Guid.NewGuid();
workbook.FileName = “ReportTest” + id.ToString() + “.xlsx”;
workbook.Worksheets.Add(“Worksheet1”);
Worksheet worksheet = workbook.Worksheets[“Worksheet1”];
CreateDataSource(worksheet);
Aspose.Cells.Charts.Chart chart = CreateChart(worksheet);
BindData(worksheet, chart);
FormatXAxis(chart, withTickSpecification);
//AddImage(worksheet, chart);
Save(workbook);
WorkAround(workbook);
System.Diagnostics.Process.Start(“C:\Users\hannodb\Documents\TigerEye\Temp\ReportTest” + workbook.FileName);
}


private void CreateDataSource(Worksheet worksheet)
{
DateTime date = new DateTime(2000, 01, 01);
Random random = new Random();
double value = 1000000;
worksheet.Cells[0, 0].PutValue(“Date”);
worksheet.Cells[0, 1].PutValue(“Value”);
for (int i = 1; i < 20; i++)
{
worksheet.Cells[i, 0].PutValue(date);
worksheet.Cells[i, 1].PutValue(value);
date = date.AddMonths(3);
value = value + random.Next(10000) - 2000;
}
}
private void Save(Workbook workbook)
{
Guid id = Guid.NewGuid();
workbook.Save(“C:\Users\hannodb\Documents\TigerEye\Temp\ReportTest” + workbook.FileName);
}
private void WorkAround(Workbook workbook)
{
Workbook workbook2 = new Workbook(“C:\Users\hannodb\Documents\TigerEye\Temp\ReportTest” + workbook.FileName);
Worksheet worksheet = workbook2.Worksheets[1];
Chart chart = worksheet.Charts[0];
chart.CategoryAxis.MinorGridLines.IsVisible = true;
chart.ValueAxis.MinorGridLines.IsVisible = true;
workbook.Save(“C:\Users\hannodb\Documents\TigerEye\Temp\ReportTest” + workbook.FileName);

}

regards
Hi,

Please set base unit scale to years (chart.CategoryAxis.BaseUnitScale = TimeUnit.Years) in FormatXAxis method. The minor grid lines will display. The chart image that is generated by ToImage() is not right as we found this issue. We will provide a fix for it soon.

Thank you.

Thanks.


That did the trick.

Regards

Hang on, setting the BaseUnitScale causes the line graph to snap to the Major and minor grid lines. (See attached) That’s not quite right either.

Hi,

FYI: We have already logged your last issue in our database. Once we will get any update regarding this issue or a fix, we will update you here.

Hi,


Please try the new fixed version: Please download the latest version: Aspose.Cells for .NET (Latest Version)
Please see the attached code sample for your reference.

Thank you.

Hi.

Thanks, it looks much better.

However, I'm still not sure why my minor vertical lines don't render. (Both in the image and the chart)

Shakeel seem to have done something to make it work his post of (09-22-2011, 10:15 AM), but the property he mentioned (IsVisible) was already set in my test application.

The property which Amjad suggested (BaseUnitScale ) is not right either, because, as my previous post shows, it changes the shape of the graph.

Could you please post the full code to get the graph as on Shakeel's post 09-22-2011, 10:15 AM

Thanks.

Hi,

After investigating it further, I concluded, this bug has not been fixed yet, I have reopened this issue.

Please see the code below. This is the same code as yours except that before saving to workbook, I reset chart properties relating to gridlines and then take the image.

Changes are highlighted as red. Please see the output xlsx file and output image.

Code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Drawing.Imaging;
using System.Linq;
using System.Text;
//using System.Windows.Forms;
using Aspose.Cells;
using Aspose.Cells.Charts;

namespace ASPOSEGridLinesBug
{
public class Form1X
{
static string fileDir = @“F:\Shak-Data-RW\Downloads”;

//Call ASPOSEGridLinesBug.Form1X.Run() to run this application
public static void Run()
{
ASPOSEGridLinesBug.Form1X fm = new ASPOSEGridLinesBug.Form1X();
fm.button1_Click(null, null);
}

public void button1_Click(object sender, EventArgs e)
{
StartHere(true);
}
private void btnWithout_Click(object sender, EventArgs e)
{
StartHere(false);
}

private void StartHere(bool withTickSpecification)
{
Workbook workbook = new Workbook(FileFormatType.Xlsx);
Guid id = Guid.NewGuid();
workbook.FileName = “ReportTest” + id.ToString() + “.xlsx”;
workbook.Worksheets.Add(“Worksheet1”);
Worksheet worksheet = workbook.Worksheets[“Worksheet1”];
CreateDataSource(worksheet);
Aspose.Cells.Charts.Chart chart = CreateChart(worksheet);


BindData(worksheet, chart);
FormatXAxis(chart, withTickSpecification);
AddImage(worksheet, chart);

//Show minor gridlines of both axis
chart.CategoryAxis.MinorGridLines.IsVisible = true;
chart.ValueAxis.MinorGridLines.IsVisible = true;

//Show major gridlines of both axis
chart.CategoryAxis.MajorGridLines.IsVisible = true;
chart.ValueAxis.MajorGridLines.IsVisible = true;

chart.ToImage(fileDir + “img.out.jpg”, ImageFormat.Jpeg);

Save(workbook);
}

private void CreateDataSource(Worksheet worksheet)
{
DateTime date = new DateTime(2000, 01, 01);
Random random = new Random();
double value = 1000000;
worksheet.Cells[0, 0].PutValue(“Date”);
worksheet.Cells[0, 1].PutValue(“Value”);
for (int i = 1; i < 20; i++)
{
worksheet.Cells[i, 0].PutValue(date);
worksheet.Cells[i, 1].PutValue(value);
date = date.AddMonths(3);
value = value + random.Next(10000) - 2000;
}
}
private void Save(Workbook workbook)
{
Guid id = Guid.NewGuid();
string filePath = fileDir + “ReportTest”;
workbook.Save(filePath + workbook.FileName);
//System.Diagnostics.Process.Start("C:\\Users\\hannodb\\Documents\\TigerEye\\Temp\\ReportTest" + workbook.FileName);<br> }
private Aspose.Cells.Charts.Chart CreateChart(Worksheet worksheet)
{
int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Line, 0, 2, 20, 14);
worksheet.Charts[chartIndex].Name = “Chart1”;
worksheet.Charts[chartIndex].Title.Text = “”;

Chart chart = worksheet.Charts[chartIndex];
chart.CategoryAxis.MinorGridLines.IsVisible = true;
chart.ValueAxis.MinorGridLines.IsVisible = true;

return worksheet.Charts[chartIndex];
}
private void BindData(Worksheet worksheet, Chart chart)
{
chart.NSeries.Add(worksheet.Cells[1, 1].Name + “:” + worksheet.Cells[20, 2].Name, true);
chart.NSeries[0].Name = “=” + worksheet.Cells[0, 1].Name;
chart.NSeries.CategoryData = worksheet.Cells[1, 0].Name + “:” + worksheet.Cells[20, 0].Name;
}
private void AddImage(Worksheet worksheet, Chart chart)
{
Aspose.Cells.Rendering.ImageOrPrintOptions options = new Aspose.Cells.Rendering.ImageOrPrintOptions();
options.ImageFormat = ImageFormat.Emf;
//Keeps breaking here, I don’t know why. It doesn’t do this in my working application.
Image chartImage = chart.ToImage(options);

System.IO.MemoryStream stream = new System.IO.MemoryStream();
stream.Position = 0;
chartImage.Save(stream, ImageFormat.Jpeg);
worksheet.Pictures.Add(21, 2, stream);
}
private void FormatXAxis(Chart chart, bool includeTickSpecification)
{

chart.CategoryAxis.AxisBetweenCategories = false;
chart.CategoryAxis.TickLabels.NumberFormat = ““YE” yyyy”;
chart.CategoryAxis.TickLabels.RotationAngle = 45;
chart.CategoryAxis.CategoryType = CategoryType.TimeScale;
chart.CategoryAxis.IsAutomaticMajorUnit = false;
chart.CategoryAxis.IsAutomaticMinorUnit = false;
if (includeTickSpecification)
{
chart.CategoryAxis.MajorUnit = 2;
chart.CategoryAxis.MajorUnitScale = TimeUnit.Years;
}
chart.CategoryAxis.MajorTickMark = TickMarkType.Outside;


if (includeTickSpecification)
{
chart.CategoryAxis.MinorUnit = 1;
chart.CategoryAxis.MinorUnitScale = TimeUnit.Years;
}
chart.CategoryAxis.MinorTickMark = TickMarkType.Outside;

chart.ValueAxis.MajorGridLines.IsVisible = true;
chart.ValueAxis.MajorGridLines.Color = Color.FromArgb(125, 125, 125);
chart.ValueAxis.MinorGridLines.IsVisible = false;
chart.CategoryAxis.MajorGridLines.IsVisible = true;
chart.CategoryAxis.MajorGridLines.Color = Color.FromArgb(125, 125, 125);
chart.CategoryAxis.MinorGridLines.IsVisible = true;
chart.CategoryAxis.MinorGridLines.Color = Color.FromArgb(225, 225, 225);
}
}
}

ok, so just to confirm:

The minor grid lines not showing is in fact a bug as well?

Thanks.

Hi,

I found this code in your given code, here you are setting it to false. Please see if it is ok.


chart.ValueAxis.MajorGridLines.IsVisible = true;
chart.ValueAxis.MajorGridLines.Color = Color.FromArgb(125, 125, 125);
chart.ValueAxis.MinorGridLines.IsVisible = false;
chart.CategoryAxis.MajorGridLines.IsVisible = true;
chart.CategoryAxis.MajorGridLines.Color = Color.FromArgb(125, 125, 125);
chart.CategoryAxis.MinorGridLines.IsVisible = true;
chart.CategoryAxis.MinorGridLines.Color = Color.FromArgb(225, 225, 225);

Hi Shakeel.


Yes, that was by design. I only want the minor lines on the category axis to show, so for my purposes, I’m not really too concerned if the bug exists on the value axis as well.

Regards.

Hi,

I have regenerated the output file after commenting my own code as highlighted red in my earlier post: 333245

I found, minor gridlines of category axis showing up. Please see the attachment. So it is not a bug.

Below is a complete code.

C#

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Drawing.Imaging;

using System.Linq;

using System.Text;

//using System.Windows.Forms;

using Aspose.Cells;

using Aspose.Cells.Charts;


namespace ASPOSEGridLinesBug

{

public class Form1X

{

static string fileDir = @“F:\Shak-Data-RW\Downloads”;


//Call ASPOSEGridLinesBug.Form1X.Run() to run this application

public static void Run()

{

ASPOSEGridLinesBug.Form1X fm = new ASPOSEGridLinesBug.Form1X();

fm.button1_Click(null, null);

}


public void button1_Click(object sender, EventArgs e)

{

StartHere(true);

}

private void btnWithout_Click(object sender, EventArgs e)

{

StartHere(false);

}


private void StartHere(bool withTickSpecification)

{

Workbook workbook = new Workbook(FileFormatType.Xlsx);

Guid id = Guid.NewGuid();

workbook.FileName = “ReportTest” + id.ToString() + “.xlsx”;

workbook.Worksheets.Add(“Worksheet1”);

Worksheet worksheet = workbook.Worksheets[“Worksheet1”];

CreateDataSource(worksheet);

Aspose.Cells.Charts.Chart chart = CreateChart(worksheet);



BindData(worksheet, chart);

FormatXAxis(chart, withTickSpecification);

AddImage(worksheet, chart);


////Show minor gridlines of both axis

//chart.CategoryAxis.MinorGridLines.IsVisible = true;

//chart.ValueAxis.MinorGridLines.IsVisible = true;


////Show major gridlines of both axis

//chart.CategoryAxis.MajorGridLines.IsVisible = true;

//chart.ValueAxis.MajorGridLines.IsVisible = true;


chart.ToImage(fileDir + “img.out.jpg”, ImageFormat.Jpeg);


Save(workbook);

}


private void CreateDataSource(Worksheet worksheet)

{

DateTime date = new DateTime(2000, 01, 01);

Random random = new Random();

double value = 1000000;

worksheet.Cells[0, 0].PutValue(“Date”);

worksheet.Cells[0, 1].PutValue(“Value”);

for (int i = 1; i < 20; i++)

{

worksheet.Cells[i, 0].PutValue(date);

worksheet.Cells[i, 1].PutValue(value);

date = date.AddMonths(3);

value = value + random.Next(10000) - 2000;

}

}

private void Save(Workbook workbook)

{

Guid id = Guid.NewGuid();

string filePath = fileDir + “ReportTest”;

workbook.Save(filePath + workbook.FileName);

//System.Diagnostics.Process.Start(“C:\Users\hannodb\Documents\TigerEye\Temp\ReportTest” + workbook.FileName);

}

private Aspose.Cells.Charts.Chart CreateChart(Worksheet worksheet)

{

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Line, 0, 2, 20, 14);

worksheet.Charts[chartIndex].Name = “Chart1”;

worksheet.Charts[chartIndex].Title.Text = “”;


Chart chart = worksheet.Charts[chartIndex];

chart.CategoryAxis.MinorGridLines.IsVisible = true;

chart.ValueAxis.MinorGridLines.IsVisible = true;


return worksheet.Charts[chartIndex];

}

private void BindData(Worksheet worksheet, Chart chart)

{

chart.NSeries.Add(worksheet.Cells[1, 1].Name + “:” + worksheet.Cells[20, 2].Name, true);

chart.NSeries[0].Name = “=” + worksheet.Cells[0, 1].Name;

chart.NSeries.CategoryData = worksheet.Cells[1, 0].Name + “:” + worksheet.Cells[20, 0].Name;

}

private void AddImage(Worksheet worksheet, Chart chart)

{

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

options.ImageFormat = ImageFormat.Emf;

//Keeps breaking here, I don’t know why. It doesn’t do this in my working application.

Image chartImage = chart.ToImage(options);


System.IO.MemoryStream stream = new System.IO.MemoryStream();

stream.Position = 0;

chartImage.Save(stream, ImageFormat.Jpeg);

worksheet.Pictures.Add(21, 2, stream);

}

private void FormatXAxis(Chart chart, bool includeTickSpecification)

{


chart.CategoryAxis.AxisBetweenCategories = false;

chart.CategoryAxis.TickLabels.NumberFormat = ““YE” yyyy”;

chart.CategoryAxis.TickLabels.RotationAngle = 45;

chart.CategoryAxis.CategoryType = CategoryType.TimeScale;

chart.CategoryAxis.IsAutomaticMajorUnit = false;

chart.CategoryAxis.IsAutomaticMinorUnit = false;

if (includeTickSpecification)

{

chart.CategoryAxis.MajorUnit = 2;

chart.CategoryAxis.MajorUnitScale = TimeUnit.Years;

}

chart.CategoryAxis.MajorTickMark = TickMarkType.Outside;



if (includeTickSpecification)

{

chart.CategoryAxis.MinorUnit = 1;

chart.CategoryAxis.MinorUnitScale = TimeUnit.Years;

}

chart.CategoryAxis.MinorTickMark = TickMarkType.Outside;


chart.ValueAxis.MajorGridLines.IsVisible = true;

chart.ValueAxis.MajorGridLines.Color = Color.FromArgb(125, 125, 125);

chart.ValueAxis.MinorGridLines.IsVisible = false;

chart.CategoryAxis.MajorGridLines.IsVisible = true;

chart.CategoryAxis.MajorGridLines.Color = Color.FromArgb(125, 125, 125);

chart.CategoryAxis.MinorGridLines.IsVisible = true;

chart.CategoryAxis.MinorGridLines.Color = Color.FromArgb(225, 225, 225);

}

}

}

Hi Shakeel


I now realize why we’ve been talking past each other, as I think the issue lies with a specific version of Excel.

When I open the file you’ve attached, I do not see the minor grid lines (See attached)

Along with the the screen shot, I’ve also included the About window of my Excel version.

Can you please let me know what version of Excel you’re using?

Thanks


Hi,

I am using Ms-Excel 2010. However, I found out that the problem does occur in Ms-Excel 2007. Please see the screenshot.

We will report this bug to development team.

Screenshot: