Maintain Chart Aseries Format for new series

Hello,

I have excel files with user-made charts, to which I need to add an additional Aseries.
I am able to find the chart and very easily add a series (I only need the chart, I don’t need the data printed in a worksheet):
chrt.NSeries.Add("{0.3, 2.7, 1.2, 2.3}", True)
but the series gets a different formatting. I currently have a very simple linechart, with no points showing, however, in the added line, I receive an x marking on the datapoint.

Is there any way to copy all the formatting (perhaps except the color) over from an existing line?
I considered to clear the NSeries and and calculate and add the entire (extended) region:
extSeries = "Sheet1!$B$2:$G$5"
chrt.NSeries.Clear
chrt.NSeries.Add(extSeries,True)
But before I go through figuring out how to calculate the new (extended) region, I am not even sure that this approach will maintain the formatting.
Can you please advice?

Secondly, when I convert the chart to image (PNG), it seems that the image is generated at a much smaller size /resolution, and then scaled up, causing e.g. legend text to be of a lower quality pixel wise.
Also, the colors seem to be slightly off, causing e.g. blue and purple to be very close, and hard to distinguish.Is there any way to influence the size/resolution of the
(intermediately?) generated image and to improve the color (Should I use a different output format?)

I attached the excel file with the added ASeries showing the different formatting, and a document with the embedded chart & image to easily compare the resolution/color.

Hi,

1) I think you should try to copy the line attributes e.g style, weight of an existing series to your newly added series, it will works fine for your requirement. See the sample code:

Workbook workbook = new Workbook();
workbook.Open(“e:\test\template.xls”);
Worksheet sheet1 = workbook.Worksheets[0];
Chart chart = sheet1.Charts[0];
chart.NSeries.Add("{0.3, 2.7, 1.2, 2.3}", true);
chart.NSeries[chart.NSeries.Count - 1].MarkerStyle = chart.NSeries[chart.NSeries.Count - 2].MarkerStyle;
chart.NSeries[chart.NSeries.Count - 1].Line.Color = Color.Blue;
chart.NSeries[chart.NSeries.Count - 1].Line.Style = chart.NSeries[chart.NSeries.Count - 2].Line.Style;
chart.NSeries[chart.NSeries.Count - 1].Line.Weight = chart.NSeries[chart.NSeries.Count - 2].Line.Weight;

workbook.Save(“e:\test\test2out.xls”);


2) I think you may try some other format type e.g tiff or emf etc and try to enhance the resolutions accordingly. See a sample code for reference.

ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();
imgOptions.ImageFormat = ImageFormat.Tiff;
imgOptions.HorizontalResolution = 200;
imgOptions.VerticalResolution = 200;
imgOptions.TiffCompression = TiffCompression.CompressionLZW;

chart.ToImage(“e:\test\myfile.tif”, imgOptions);


I have also attached our latest version/fix with some enhancements, kindly try it too.

Thank you.

Thanks!

1. The formatting is exactly what I was looking for.

2. Especially the ImgOptions.HorizontalResolution & imgOptions.VerticalResolution did the trick! No need to even change the type.

Only the colors are still unchanged. blue and purple still come out the same color (see picture in original attachment)

However, I now notice that when I add additional ASeries, and use ToImage, the vertical spacing in the Legend (between the different entries) increases. Where I originally had 4 series, and added 1, I now only see 3 entries in the Legend. This is both with the original, as well as with your latest version.

Thanks!

Hi,

2) Kindly post your template Excel file with your sample code here. We will check your issue soon.

Thank you.

Thank you Amjad,

3) One other irregularity my manager pointed out to me is that the thin gray border around the entire graph is exactly on the edge of the image after using ToImage, while if you print an (embedded) excel graph, the line is a couple of pixels inside the total edge (see also the generated output.doc). I don’t know if this can easily be fixed?

2) Trying to provide you with a small a sample as possible, I found out that the discoloration seems to be related with loading a Workbook from stream (in my case extracted from a Words OleFormat object).

The code I use:

Dim pth As String = “c:”

Dim fileName As String = pth & “test.doc”
Dim doc As Document = New Document(fileName)
Dim builder As New DocumentBuilder(doc)
Dim shapeNodeList As NodeCollection = doc.GetChildNodes(NodeType.Shape, True)
Dim shap As Drawing.Shape = shapeNodeList(0)

If shap.OleFormat.ProgId = “Excel.Sheet.8” _
Or shap.OleFormat.ProgId = “Excel.Chart.8” Then

'load Workbook from the embedded Ole object stream
Dim ole As OleFormat = shap.OleFormat
Dim fstr As New IO.MemoryStream()
ole.Save(fstr)
Dim cel As New Workbook
cel.Open(fstr)
'Here, the discolorization already happened, but legenda is still good.
'Output excel as proof:
cel.Save(pth & “UnmodifiedInput.xls”, FileFormatType.Excel97To2003)
'Worksheet 1: Cells with values
'Worksheet 0: resulting graph
Dim chrt As Aspose.Cells.Chart = cel.Worksheets(0).Charts(0)

chrt.NSeries.Add("{0.3, 2.7, 1.2, 2.3}", True)
chrt.NSeries(chrt.NSeries.Count - 1).Name = “Newly added”
'change line format to fit in:
chrt.NSeries(chrt.NSeries.Count - 1).MarkerStyle = _
chrt.NSeries(chrt.NSeries.Count - 2).MarkerStyle
chrt.NSeries(chrt.NSeries.Count - 1).Line.Style = _
chrt.NSeries(chrt.NSeries.Count - 2).Line.Style
chrt.NSeries(chrt.NSeries.Count - 1).Line.Weight = _
chrt.NSeries(chrt.NSeries.Count - 2).Line.Weight
'Here, legenda is messed up, so it’s not (only due to resolition, output excel as proof:
cel.Save(pth & “ModifiedInput.xls”, FileFormatType.Excel97To2003)

Dim imgOptions As ImageOrPrintOptions = New ImageOrPrintOptions()
imgOptions.ImageFormat = System.Drawing.Imaging.ImageFormat.Png
'Adjust resolution:
imgOptions.HorizontalResolution = shap.Width
imgOptions.VerticalResolution = shap.Height

'issue is not to-image related, or because of insertion in Word:
chrt.ToImage(pth & “output.png”, imgOptions)
'insert image back in document for easy comparison:
chrt.ToImage(fstr, imgOptions)
builder.MoveTo(shap)
builder.InsertImage(fstr) ', shap.Width, shap.Height)
doc.Save(pth & “output.doc”)
End If

Hi,

We appreciate if you could separate the issue (discoloration or border line issue), I mean just use Aspose.Cells component with some template Excel file and not from Word doc file, open it into streams and then manipulate the charts before taking image of the chart. Please provide us your template input file, the generated output image and the generated Excel file here. This way we can analyze/check the issue if this is really concerned with Aspose.Cells component or not.



Thanks for your understanding!


Amjad,

Both the line discoloration and the legenda issue seem to be very much related with loading the Workbook from the ole stream. As I can not recreate the effects when loaded directly from an excel file.

As the Aspose.Words team claims that they don’t do anything with the Ole object apart from providing access to the bits that make up the stream, I expect that an Excel chart might contain additional/slightly different data when embedded within a Microsoft Word file, which offsets Aspose.Cell’s loading of the stream. Regretfully, I can’t find a way to relay the difference in the stream for the discoloration, or the spacing in the legenda. I can only ask you kindly to please look at the streamed-in data as the difference must be coming from. there. Please see my previous post for code to get access to the ole stream.

However, for the legenda cropping, please see the attached excel file (UnmodifiedInput.xls),
with the following code:
Dim cel As New Workbook
cel.Open(“C:\Data\Docalot\Test\ChartTest” & “UnmodifiedInput.xls”)
Dim chrt As Aspose.Cells.Chart = cel.Worksheets(0).Charts(0)

chrt.NSeries.Add("{0.3, 2.7, 1.2, 2.3}", True)
'this legenda item will not show unless you manually resize
chrt.NSeries(chrt.NSeries.Count - 1).Name = “Newly added”
'change line format to fit in:
chrt.NSeries(chrt.NSeries.Count - 1).MarkerStyle = _
chrt.NSeries(chrt.NSeries.Count - 2).MarkerStyle
chrt.NSeries(chrt.NSeries.Count - 1).Line.Style = _
chrt.NSeries(chrt.NSeries.Count - 2).Line.Style
chrt.NSeries(chrt.NSeries.Count - 1).Line.Weight = _
chrt.NSeries(chrt.NSeries.Count - 2).Line.Weight
cel.Save(“C:\Data\Docalot\Test\ChartTest” & “outputSample.xls”)

As for the borderline issue, it might be an Office 2007 related issue. I don’t know how I can easily show/proof you the difference using excel & the generated image, but I know exactly which area is outside of the line in Office, but included within the borderline by Aspose.Cells. Please see the image Borderline.jpg. In the back you can see the resizing border/“handle” (in very lightish blue) that is outside the gray line. (The location of the gray line can be seen in the inset.)

I know that it is probably a curse at this point that you are so close to Office, causing us to expect the last little details to be perfect too.

Thank you for your understanding and effort to help find the solutions!

Hi,

Thanks for sharing further details and template files.

I have found the legend cropping issue as it does not auto-extend to accommodate new entries. We need to check whether it is an issue or just the behavior of MS Excel. We will also analyze your other issue you mentioned e.g discoloring and border line etc. I have logged it into our issue tracking system with an issue id: CELLSNET-17718. We will let you know about it soon.

Thank you.

Some additional information I found:
The legenda cropping/mess up occurs when the newly added series name is longer than the original names in the legend.

I.e. in my original file (see unmodifiedinput.xls) the series are named:
Series 1
Series 2
etc.
After adding the additional series and opening the file with excel it shows the new series name (Newly Added) wrapped over 2 lines, and the original names spaced with the same spacing as if they too are wrapped. Trying with an even longer name, will both increase the spacing between the entries, and wrap the name over 3 lines (modifiedInput2).
However, using a series name equal or shorter than the original names and converting the chart to image will result in the expected output without additional spacing in between (note that the excel file itself is still incorrect). (modifiedInput).

In short, when the new series names are of equal or lesser length, the legend box is (almost) appropriately resized, however, when it is longer, and wrapping occurs, 2 issues arise:
1. ALL entries are spaced as if wrapped
2. the legend box is not appropriately resized.

Hope this helps.

Hi,

Thanks for providing additional details and sample files.

We will soon look into your issue logged as: CELLSNET-17718.

Thank you.

Hi,

Please try this fix (attached).

The issue is caused by the size of the legend is not automatic. So, please set the size of legend as automatic or expand the size of the legend. Please use Chart.Legend.IsAutomaticSize = true to set the size of the legend as automatic size.

Thank you.

Hello,

The fix seems to help, although now 2 different issues occur:
1. In the Excel file, the legend is moved on top of the graph area
2. when using ToImage, the legend is not on top anymore, but gets clipped off the image.

I did try to use Char.Legend.AutoScaleFont = true but that didn’t seem to have any influence.
Alternatively, I would not mind if the longer series names get wrapped, but then the other legend entries should not also get the same spacing (as if they are wrapped).

Additionally, your mentioning of things not acting automatically triggered me to have a look at the color issue again. The discolorization only occurs when I have the line color in my Ole-embedded chart set to automatic. As soon as I save the content to disk, and inspect the setting via Excel, the line color is set to non-automatic, and the 1st and 4th line have obtained the same color. This is in an Office 2007 environment. I think that in a < Office 2007 environment the “automatic” colors are generated differently.

Thank you.

Hi,

Thanks for providing the details and attachments based on which we have reopened the issue CELLSNET-17718.

We will investigate the issue further and update you accordingly.

Best Regards

Hello,

We have further investigated and here are the findings:

1) If a chart created in MS Excel 2007 is saved as xls file, all automatic properties will be custom.
Reading compatible Excel 2007 info in xls file is not supported for now.
2) As the position and size of the legend are all custom , automatic adjustment of the position and size of the legend is not supported when adding a newly series.

So could you try to set “chart.Legend.Position = LegendPositionType.Right;”? Doing so, MS Excel can display correctly and we can export a similar image.

Regards

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan