Free Support Forum - aspose.com

Named range containing non-contiguous cells not created correctly

I know a similar issue was addressed a long time ago in this thread:


http://www.aspose.com/community/forums/15034/problem-with-non-contiguous-cells-as-chart-series/showthread.aspx#15034

But I have a current issue that appears to be related.

Using Excel directly, you may select non-contiguous cells and create a named range by separating them with a comma as “$A$1:$I$1,$A$12:$I$21"
and naming the range as “Data1"
You end up with a range containing cells from the first row and cells from the twelfth through twenty-first rows.
Creating a chart and specifying “Data1” as the data series properly charts the two non-contiguous sets of cells on the same series.

Using Aspose.Cells 6.0.0.0 I do this:
Dim dataSheet as Worksheet
Dim DataRange As Range = dataSheet.Cells.CreateRange(”$A$1:$I$1,$A$12:$I$21”)

After saving the .xlsx file and opening it, the named range “Data1” exists but only includes cells “$A$12:$I$21"“
The first portion of the cells before the comma are not included in the range.

The purpose for creating these ranges is to generate multiple clustered bar charts each containing a portion of rows from a large data table. If the data has 23 rows, charting all 23 together on the same chart is not readable as the bars and labels are too close together. So I am trying to spread them out by a max of 10 rows per chart so one data table end up making three charts (two with 10 bar clusters and one with 3 bar clusters). I need to keep referencing back to the same top row containing the column headers for each chart but include a different set of 10 data rows each time.

I want to end with three ranges like:
Data1 = “$A$1:$I$1,$A$2:$I$11"
Data2 =”$A$1:$I$1,$A$12:$I$21"
Data3 =”$A$1:$I$1,$A$22:$I$24"

But I end up with these ranges in the .xlsx file:
Data1 =”$A$2:$I$11"
Data2 ="$A$12:$I$21"
Data3 ="$A$22:$I$24"

The last data column and the total number of rows in the source data (“I” and 23 in this case) are variable so I generate the strings for the range values dynamically each time this runs.

I would appreciate any suggestions if there is a better way to achieve this.
Thanks.

Hi,

Well, if you need to create non contiguous named ranges for chart’s data series, you may use Name object, see the topic for your reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/implementing-nonsequenced-ranges.html

Also, here is a sample code for your reference.
Sample code:
Dim _WorkBook As New Workbook()
_WorkBook.Worksheets.Clear()
_WorkBook.Worksheets.Add(SheetType.Worksheet)
Dim worksheet As Worksheet = _WorkBook.Worksheets(0)
worksheet.Name = “Series”
worksheet.Cells(0, 0).PutValue(“Anne”)
worksheet.Cells(0, 1).PutValue(“Srie A”)
worksheet.Cells(0, 2).PutValue(“Srie B”)
worksheet.Cells(1, 0).PutValue(“2006”)
worksheet.Cells(1, 1).PutValue(54)
worksheet.Cells(1, 2).PutValue(46)
worksheet.Cells(2, 0).PutValue(“2007”)
worksheet.Cells(2, 1).PutValue(15)
worksheet.Cells(2, 2).PutValue(85)
worksheet.Cells(3, 0).PutValue(“2008”)
worksheet.Cells(3, 1).PutValue(70)
worksheet.Cells(3, 2).PutValue(30)

Dim index As Integer = _WorkBook.Worksheets.Names.Add(“Data1”)
Dim name As Name = _WorkBook.Worksheets.Names(index)
'Creating a non sequence range of cells
name.RefersTo = “=Series!$B$2:$B$4,Series!$C$2:$C$4”

_WorkBook.Worksheets.Add(SheetType.Worksheet)
Dim chartsheet As Worksheet = _WorkBook.Worksheets(1)
chartsheet.Name = “Chart”
chartsheet.Charts.Add(ChartType.Column100PercentStacked, 0, 0, 25, 10)
Dim chart As Chart = chartsheet.Charts(0)
chart.PlotArea.Area.ForegroundColor = Color.Transparent
chart.Legend.Position = LegendPositionType.Bottom
chart.NSeries.Add(name.RefersTo,False)


chart.NSeries(0).XValues = “Series!A2:A4”

_WorkBook.Save(“e:\test2\outnonconti__test.xlsx”)

Thank you.

This method throws an invalid formula exception when I pass a multi-part cell reference containing a single quoted sheet name value with spaces.


This works (spaces with single quotes and no comma):
categoryName.RefersTo = ‘File Size Per Media’!A3:A11

This executes in code (spaces without single quotes) but the resulting .xlsx file is corrupt and will not open.:
dataName.RefersTo = File Size Per Media!$A$2:$I$2,File Size Per Media!$A$3:$I$12

This throws exception (spaces with single quotes plus comma):
dataName.RefersTo = ‘File Size Per Media’!$A$2:$I$2,‘File Size Per Media’!$A$3:$I$12

This throws exception (no spaces with single quotes plus comma):
dataName.RefersTo = ‘FileSizePerCustodian’!$A$2:$I$2,‘FileSizePerCustodian’!$A$13:$I$22

Using R1C1 notation, this works to create the name reference and NSeries:
categoryName.R1C1RefersTo = "R3C1:R12C1"
chart.NSeries.AddR1C1(dataName.R1C1RefersTo, False)

I do end up with absolute references to the cells and the code executes.

The properties of the series appear correct:
chart.NSeries(0).Values property shows:
(‘File Size Per Media Chart 1’!$A$2:$I$1,‘File Size Per Media Chart 1’!$B$3:$I$12)

But the .xlsx file is corrupt and I get an auto recovery dialogue when opening in Excel. If I allow Excel to “fix” the issue it deletes the named range Aspose created saying "Removed Records: Named range from /xl/workbook.xml part (Workbook)"

There appear to be some conflicts within these methods.

I need to use the sheet names in the cell references as the data will be on a different sheet from the chart(s).

Hi,

Well, it works similar to MS Excel. If a worksheet has spaces in its name, you have to enclose it within single quotes. This is same as Excel.

Could you create a sample console application to show the issue, we will check it soon.

Thank you.

I have found a solution for building these charts while writing you a test app to show the problem. By defining each column of each NSeries explicitly, I am able to chart the non-contiguous data cells. I also am not using any Name objects at all.


The code samples I have seen show defining the entire range of cells to be charted such as

chart.NSeries.Add("‘The Data’!$B$2:$C$3",True)

But I am only able to get this to work by separately defining each column of data

chart.NSeries.Add("‘The Data’!$B$2:$B$3",True)
chart.NSeries.Add("‘The Data’!$C$2:$C$3",True)

and then specifying the same category data for every series

chart.NSeries.CategoryData = "‘The Elements’!$A$3:$A$6"

Attached is the vb module for a console app doing what I need. If you see a better way of achieving this please let me know. But at this point, my code is doing what I need.

Thanks,
Jeff