Hi,
Can I use Aspose.Cells in order to create a chart with a series that contains just values? Here is a testcase in Excel:
1.) Open Excel 2007 and go to insert->column->click the first chart option. An empty chart appears.
2.) Right-click chart, and click “Select Data”.
3.) Click “Add” and enter a series name, as well as the values {1,2,3,4}.
After clicking “OK”, a chart is now created with those values, and they aren’t tied to any specific cell. I was wondering if I could use Aspose.Cells in order to create a chart by providing some sort of series array (such as a String[]) in the same fashion, without having to set specific Cell values in the sheet (such as cells.get(“A1”).setValue(“4”)
If so, please let me know how. Thank you.
Hi,
Thanks for your posting and using Aspose.Cells for Java.
I think, this can be done using Aspose.Cells for Java. Please see the code below. I have also attached the output xlsx file generated by this code and the screenshot for your reference.
Please download and use the latest version:
Aspose.Cells
for Java v7.3.0.3
Java
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getCharts().add(ChartType.COLUMN, 3, 5, 25, 15);
Chart chart = worksheet.getCharts().get(0);
chart.getNSeries().add(“A1”, true);
Series series = chart.getNSeries().get(0);
series.setName(“Test”);
series.setValues(“{1,2,3,4}”);
workbook.save(“output.xlsx”);
Screenshot:
Excellent, that’s exactly what I needed to know. Thank you!
Hi,
Thanks for your feedback.
It’s good to know your issue is resolved by the above code.
If you face any other issue, please feel free to post, we will be glad to help you asap.
Thank you. I actually have another charting question:
In Excel, one can copy and paste a new chart between different sheets and even different books, and if the series are erased, they will be independent of each other (with no references to each other). Is there any way to do a deep copy of a Chart object in Aspose.Cells? That is, create a new clone of a Chart object with all of the same properties and metadata, but is not linked to the original Chart object? I see that both WorkSheet and WorkBook have copy functions, but Chart does not. The reason is that we need to make copies of Chart objects, and are worried about losing properties and metadata information if we were to do it manually. Please let me know, thanks.
Hi,
Please check the topic:
Aspose.Cells works the same way as MS Excel does. In MS Excel, when you copy a chart to other worksheets, the chart gets copied but the source data of the chart is set to be referenced to the source sheet. If you know MS Excel can do other way as per your needs, we can look into it.
Also, if you need to get the chart's image to be pasted into other worksheets, you may use Chart.ToImage() method and insert that picture into other worksheets. See the topics for your reference:
Thank you.
Thanks for the links, they were helpful. I have another question related to copying: I see that Workbook has a copy(Workbook source) function. Does this ensure that the data that is copied from one Workbook to another is a deep copy? Meaning, does it create completely new instances of Objects and collections, or does it just copy references?
Hi,
Yes, when you use Workbook.Copy() method, it will create new instance of objects/collection. Also, you may try to use Worksheet.Copy() to copy source worksheets in a workbook to paste them in another workbook, see the document for your reference:
Thank you.
Hi,
I have another question related to Series and Charting. Is it possible to determine what cells are being used for charting? As in, lets say that cells A1, B1, A2, and B2 are being used to create a chart. Given a Chart object, is it possible to determine that in order to update the chart, the values that need to be updated would be contained in A1, B1, A2, and B2 (assuming that the chart was created previously, and I had no idea about these references)? If so, please let me know how. And thank you as always.
Hi,
I think you may use: Series.Values attribute to get to know about the data source range of cells for the chart. Also, you may use. Chart.NSeries.CategoryData attribute to get the category data range.
See a sample code below for your reference a bit:
Sample code:
SeriesCollection nseries = chart.NSeries;
string seriesval = nseries[0].Values;
int index = seriesval.IndexOf(’!’);
index++;
MessageBox.Show(index.ToString());
string val = seriesval.Substring(index, seriesval.Length - index);
MessageBox.Show(val.ToString());
string[] strCellRange = val.Replace("$", “”).Split(’:’);
int frow, fcol, lrow, lcol;
CellsHelper.CellNameToIndex(strCellRange[0], out frow, out fcol);
CellsHelper.CellNameToIndex(strCellRange[1], out lrow, out lcol);