Formatting issues with chart

Aspose.Cells Product Family
1

hi,

I am creating chart from code in excel. Below are few of the issues I am facing while working with charts, Chart is Line chart.

1) Right now all lines before 1st point it starts from zero and then taking that zero to 1st point. I want all lines start from its 1st point value and not zero.

2) I am saving chart to an image and then showing that image in chart. Here after converting chart to an image, it shows the watermark on image saying "Evalution only. Created with Aspose.Cells for .Net (c) 2003-2012 Aspose pty ltd.".

I am using Aspose license with generate so why is showing like this?

3) My chart appears correct but once chart is converted to an image, it cuts the Chart title.

4) I want to make line bit more thick. Width of the line should be 2. Its taking 1 currently. I tried this but did not work.

oChart.NSeries[iSeriesCtr].SeriesLines.WeightPt = 2;

Below is the code to generate chart, and attached the report template and also report output file.

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

Workbook oWB = new Workbook(@"C:\Working\Report1.xls");

Worksheet oSheet = oWB.Worksheets[0];

Cells oCells = oSheet.Cells;

// Link chart data

string sSeriesStart = "M4";

string sSeriesEnd = "R41";

string sCategoryStart = "L5";

string sCategoryEnd = "L41";

Int32 iStartRow = 3;

Int32 iStartColumn = 11;

Chart oChart = oSheet.Charts["Chart 1"];

oChart.NSeries.Add(string.Concat(sSeriesStart, ":", sSeriesEnd), true);

oChart.NSeries.CategoryData = string.Concat(sCategoryStart, ":", sCategoryEnd);

oChart.NSeries.IsColorVaried = true;

for (int iSeriesCtr = 0; iSeriesCtr < oChart.NSeries.Count; iSeriesCtr++)

oChart.NSeries[iSeriesCtr].Name = oCells[iStartRow, iStartColumn + iSeriesCtr + 1].Value.ToString();

// Format chart

oChart.Type = ChartType.Line;

oChart.CategoryAxis.IsAutomaticMajorUnit = true;

oChart.CategoryAxis.IsAutomaticMinorUnit = true;

oChart.CategoryAxis.BaseUnitScale = TimeUnit.Days;

oChart.CategoryAxis.CategoryType = CategoryType.TimeScale;

oChart.Title.Text = "Test Title";

oChart.Title.Font.Size = 10;

oChart.Title.Font.Name = "Calibri";

oChart.Title.Font.IsBold = true;

oChart.Title.Font.Color = Color.Black;

oChart.PlotArea.X = 100;

oChart.PlotArea.InnerX = 105;

oChart.PlotArea.Y = 50;

oChart.PlotArea.InnerY = 55;

oChart.PlotArea.Height = 3500;

oChart.PlotArea.Width = 4000;

oChart.ShowLegend = true;

oChart.Legend.Position = LegendPositionType.Top;

oChart.Legend.Border.IsVisible = false;

oChart.Legend.Font.Size = 5;

// Convert chart to an image

string sChartImageFilePath = string.Concat("ChartImage", ".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 < oSheet.Shapes.Count; iCtr++)

{

if (oSheet.Shapes[iCtr].Name == "Chart 1")

{

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

oSheet.Shapes.DeleteShape(oSheet.Shapes[iCtr]);

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

File.Delete(sChartImageFilePath);

break;

}

}

// Save file

oWB.Save(@"C:\Working\Report1Output.xls");

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

2

Hi,


I have tested your code with your template file (with a little adjustments) with our latest version/fix: Aspose.Cells for .NET v7.3.4.2 , it works fine according to the code we specify.

Here is the updated sample code.

Sample code:

Aspose.Cells.License lice = new Aspose.Cells.License();
lice.SetLicense(@“E:\Licenses\17-4-2012\Aspose.Cells.lic”);
Workbook oWB = new Workbook(@“e:\test2\Report1.xls”);

Worksheet oSheet = oWB.Worksheets[0];

Cells oCells = oSheet.Cells;

// Link chart data

string sSeriesStart = “M4”;

string sSeriesEnd = “R41”;

string sCategoryStart = “L5”;

string sCategoryEnd = “L41”;

Int32 iStartRow = 3;

Int32 iStartColumn = 11;

Chart oChart = oSheet.Charts[“Chart 1”];

oChart.NSeries.Add(string.Concat(sSeriesStart, “:”, sSeriesEnd), true);

oChart.NSeries.CategoryData = string.Concat(sCategoryStart, “:”, sCategoryEnd);

oChart.NSeries.IsColorVaried = true;

for (int iSeriesCtr = 0; iSeriesCtr < oChart.NSeries.Count; iSeriesCtr++)
{

oChart.NSeries[iSeriesCtr].Name = oCells[iStartRow, iStartColumn + iSeriesCtr + 1].Value.ToString();
oChart.NSeries[iSeriesCtr].Border.WeightPt = 2;
}
// Format chart

oChart.Type = ChartType.Line;

oChart.CategoryAxis.IsAutomaticMajorUnit = true;

oChart.CategoryAxis.IsAutomaticMinorUnit = true;

oChart.CategoryAxis.BaseUnitScale = TimeUnit.Days;

oChart.CategoryAxis.CategoryType = CategoryType.TimeScale;

oChart.Title.Text = “Test Title”;

oChart.Title.Font.Size = 10;

oChart.Title.Font.Name = “Calibri”;

oChart.Title.Font.IsBold = true;

oChart.Title.Font.Color = Color.Black;

oChart.PlotArea.X = 100;

oChart.PlotArea.InnerX = 105;

oChart.PlotArea.Y = 50;

oChart.PlotArea.InnerY = 55;

oChart.PlotArea.Height = 3500;

oChart.PlotArea.Width = 4000;

oChart.ShowLegend = true;

oChart.Legend.Position = LegendPositionType.Top;

oChart.Legend.Border.IsVisible = false;

oChart.Legend.Font.Size = 5;

// Convert chart to an image
string sChartImageFilePath = string.Concat(“e:\test2\ChartImage”, “.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 < oSheet.Shapes.Count; iCtr++)
{

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

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

///oSheet.Shapes.DeleteShape(oSheet.Shapes[iCtr]);

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

File.Delete(sChartImageFilePath);

break;

}

}

// Save file
oWB.Save(@“e:\test2\MyReportOutput.xls”);

The output file is attached for your reference. Both chart and output picture are the same.

Now come to your questions/issues:

1) Well, the individual series lines are rendered as per your data source in the sheet. You may specify Axis’ min, max, major units, minor units as per your needs. You may get the relevant properties for Axis class (e.g MinValue, MaxValue, Major/MinorUnit etc.). Also, you may implement your desired needs first in MS Excel manually, and then use the relevant Aspose.Cells API to obtain the similar results. Aspose.Cells works in the same way and follows MS Excel standards and cannot go beyond it.

2) Please make sure that you have set the license (as I did in my code) properly (the code should be executed) before processing your actual code snippet etc. Please see my attached output file which does not render any evaluation either in the sheet or in the image.

3) The chart title is fine in both Excel chart and output picture.

4) Please use the following line instead:
oChart.NSeries[iSeriesCtr].Border.WeightPt = 2;
it works as expected.

Thank you.



3

Thanks Amjad for the quick reply,

I downloaded the latest dll. Also could see changes for making line thick.

For the answers to my questions, below are my observations,

1) yes I know whatever is done in excel can be done using Aspose API also. Here I do not know how to do that. Can you pls tell me how to to even do that in excel.

2) I am setting license in my code though I have not provided the code for the same in my sample file and it is set proper because I don't get that license sheet in output file. Is there anything else I am missing.

3) In your output file itself I noticed that was overlap of chart border over Chart title. Attached the print screen of your output file. I have marked the place where it has overlapping.

4) Thanks I could resolve this.

Thanks,

Hetal

4

Hi,


1) I could not understand your actual needs regarding axis values. So, I requested you kindly create your template chart with your desired axis min/max values, save the file and attach it here, so that could actually know your requirements and may help you accordingly. Please do it in MS Excel manually.

2) I am not sure if you still finds the evaluation watermark issue in the chart image or not. If you still find this issue with our latest version/fix: Aspose.Cells for .NET v7.3.4.2 , kindly let us know. You may check my output file that is generated by my pasted sample code. So, please make sure that you are using the version v7.3.4.2 and double check that the licensing code is processing accordingly.

3) I can find the issue as you have mentioned. The chart title in the output image overlaps a bit, it is a minor issue though. Anyways, I have logged a ticket with an id: “CELLSNET-41233” for your issue. We will figure this issue out soon.

4) Thanks for the confirmation, that you have resolved this issue.

Thank you.

5

hi,

Do not worry about issue #3. Its not Aspose issue. The issue was related to X and Y position of Chart and Legend. Issue #3 is resolved.

I am unable to use the latest dll due to below error,

"The subscription included in this license allows free upgrades until 09 Sep 2012, but this version of the product was released on 30 Nov 2012. Please renew the subscription or use a previous version of the product."

I think the license file that we have supports the releases only till 09-Sep-2012 :( Any suggestion what can I do to resolve this.

Thanks,

Hetal

6

Hi,


For your licensing issue and as per your error, I am afraid, you cannot use latest Aspose components with your current license as your licensed was expired on September 09, 2012. Either you should persist with older versions of Aspose products (that should be released before your expiry date (September 09, 2012)) or upgrade your subscription to use latest versions.

For your information, when you purchase a license, you are authorized to use this license file with any new (and upcoming) versions / hot fixes of the component(s) for the next whole year before your subscription expiry date.

Thank you.
7

hi Team,

As per your suggestion we upgraded our license file and deployed your latest aspose.cells.dll. Now the real problem started here. This new dll solved the problems of this forum but it introduced the earlier errors that I had raised earlier <A href="https://forum.aspose.com/t/112476</A></P> <P>If you go through this link you will find that the support team gave me version 7.2.1.0 that had the watermark issue resolved but on deploying your 7.3.4.2 dll (of this issue) this issue was resolved but watermark issue again started coming up.</P> <P>Was there any versio issues when you released the 7.3.4.2 dll??? Pls be careful when you release new aspose version. When we download Aspose new dll (with resolved fixes) we assume that it does not break any existing features. This happened 2nd time that Aspose gave us a new dll with resolved bugs and it effected several other working features of this.</P> <P>Pls look into this.</P> <P>- Hetal</P>

8

Hi,


Well, it is not a bug / regression in the latest versions of the product rather this issue (regarding render WordArt shape in XLSX file format) logged as “CELLSNET-40692” and mentioned on the thread: <a href="https://forum.aspose.com/t/112476 is not resolved yet.

Anyways, I have escalated your issue again with our concerned developer and we will soon look into it to figure it out. The issue link is attached to this thread as well, so once the issue is resolved, you will get an automatic notification about the fixed version.

We are sorry for any inconvenience caused!
9

hi,

ok I get this that it was for XLSX. I am not using xlsx here. My earlier issue was for xlsx.

What I am trying to point here is that with Aspose new version dll my existing features stopped working and I had to revert to previoud version. This should not happen. Here I lose the trust when new version of dll is given to me. What is the guarantee that new version dll will not break existing features. There should be proper testing from aspose team when they release new version. I am scared to use any new version because this happened 2nd time with me.

- Hetal

10

Hi,


We are sorry for your trouble.

Well, we perform quality assurance using some test cases. When we release a version of the product we thoroughly check for any possible regression bugs and other aspects or issues. We use specific test cases to evaluate issues with the new version. If all the tests pass, then we release the product. Since we incorporate new features and enhance existing feature, also sometimes it is not possible to evaluate every test case or we cannot create test cases that could cover every possible issue in diverse environments or scenarios. Anyways, we always try to do every possible effort that existing functionality does not disturb by any means. Sometimes the existing feature is changed or enhanced for performance grounds which some users might call it regressions.

Now come to your issue(s), could you kind enough to point which existing features or functionality stopped working with new versions, we will evaluate your issue and try to fix it asap. Please give us your sample code/ sample application to reproduce the issue on our end. Also attach your sample input (if any) and output files here. We will check your issue soon.

Thank you.




11

Hi,


Just to update you, for the issue logged as “CELLSNET-41233”, we create a simple
test project based on the code that you posted earlier. Please find it attached here. We think the project can solve
the issues you mentioned. Please check and give us your feedback.

Thank you.
12
Amjad Sahi:
Hi,

Just to update you, for the issue logged as "CELLSNET-41233", we create a simple test project based on the code that you posted earlier. Please find it attached here. We think the project can solve the issues you mentioned. Please check and give us your feedback.

Thank you.
Hi,

Please also download and use the latest version: Aspose.Cells for Java v7.3.4.4 with the test project.