Hi.
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.
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.
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.
Thanks.
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,
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.
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
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: