How to create named range with Formulas in aspose

Aspose.Cells Product Family
1

Hi,


I want to create named ranges in excel but it should have a formula instead of simple excel cell range.
I have attached the sample excel output (Sample.xlsx) I am looking for.
If you check the “Name Manager” in the excel the named ranges “Val1Range” and “Val2Range” are formula which are referred in the chart under Sheet2.

I tried to create a named range through aspose using following code:

FileStream fstream = new FileStream(@“sample.xlsx”, FileMode.Open);

//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);

//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[“Sheet1”];

//Creating a named range
Range range = worksheet.Cells.CreateRange("=IF(Sheet1!$A$6,Sheet1!$A$2:$A$5,Sheet1!$A$7)");

//Setting the name of the named range
range.Name = “Sheet1!Val1Range”;

//Saving the modified Excel file
workbook.Save(@“sample1.xlsx”);

//Closing the file stream to free all resources
fstream.Close();

But its throwing error while creating range as “Invalid cell name”.
Which suggests that this function can only take range value as input and not formula.

Kindly suggest what is the work around to get the expected behavior.
I am using Aspose cells version 7.0.2.2.

Thanks,
Kallol
2

Hi,


Thanks for the template file.

Well, if you need to create named range with your desired formulas, which these ranges should refer to, please use NameCollection and Name objects instead, see the sample code below on how to create such a named range for your complete reference:
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\sample.xlsx”);
Worksheet sheetRef = workbook.Worksheets[0];
NameCollection names = workbook.Worksheets.Names;
Name name = names[names.Add(“Sheet1!Range1”)];
name.RefersTo = “=IF(Sheet1!$A$6,Sheet1!$A$2:$A$5,Sheet1!$A$7)”;

workbook.Save(@“e:\test2\out1.xlsx”);

Let us know if you still have any issue.

Thank you.

3

Thanks Amjad for the quick reply and now I can create the named ranges with formula.


But how to use the named range name in the chart?
As you can see in the Sample excel, I had drawn the chart using named range name and the worksheet name of its scope.

When I am trying to replicate the same in code, its throwing exception “System.NullReferenceException: Object reference not set to an instance of an object.” :

int index = chartWorkSheet.Charts.Add(ChartType.Line, 1, 1, 35, 20);
Chart chart = chartWorkSheet.Charts[index];

/// Creating named range as directed
var namedRange = CreateNamedRange()

string nseriesStr = string.Format("{0}!{1}", workSheetName, namedRange.Text);
int index = chart.NSeries.Add(nseriesStr, true);

Which suggests me that the nseries.add is throwing exception since it is not getting the data in expected format.

I hope you understand what I am tring to achieve here. Merely a replica of the sample file I attached before.

Regards,
Kallol
4

Hi Kallol,

Thanks for your posting and using Aspose.Cells.

Please provide us your sample console application project creating your chart and causing you this issue. It will help us look into your issue more closely and precisely and we will update you asap.

Please also provide us your expected output Excel file which you can create manually using Microsoft Excel and attach it here for our reference.

Thanks for your cooperation.

5

Not sure why I got this reply since i have already posted the sample code I have used and the expected output. As I mentioned above I want to create chart a replica of what I have done in the sample excel file.


In the sample excel file I have referred the chart data series by named data range as “Sheet1!Val2Range”.

Can you suggest how to achieve the same using Aspose?

Regards,
Kallol
6

Hi Kallol,

Thanks for your posting and using Aspose.Cells.

Please see the following code. It adds the series with Values as range and the series looks same as yours.

I have attached the output Excel file and screenshot showing the series for your reference.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\sample.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[1];


int id = worksheet.Charts.Add(ChartType.LineWithDataMarkers, 2, 2, 10, 10);


Chart ch = worksheet.Charts[id];


ch.NSeries.Add(“A1:A2”, true);


Series s = ch.NSeries[0];


s.Values = “=Sheet1!Val1Range”;

s.Name = “Val1”;


workbook.Save(“output.xlsx”);



7

Hi Shakeed,


Thank you for the quick reply.

I have one doubt here :

Why do we need this line
ch.NSeries.Add(“A1:A2”, true);

Is it a kind of a workaround so that we can set the series values later?

I am fine with the implementation but just want to get the reason.

Thanks again.

Regards,
Kallol
8

Hi Kallol,

Thanks for your feedback and using Aspose.Cells.

It is good to know that this solution works for you. It is just a workaround to add a single vertical series inside the chart.

9

Hi Shakeed,


There are few issues with the code:
1) This code is not working with Aspose version 7.0.2.2. Its throwing similar error when I am trying to set the series value.

2) I tried with latest Aspose 8.2 and this step worked but it failed while formating.
After creating the chart I tried to do a bunch of formating as thebelow sample code:

chart.PlotArea.Area.BackgroundColor = Color.White;
chart.ChartArea.Area.BackgroundColor = Color.White;
chart.PlotArea.Area.ForegroundColor = Color.White;
chart.ChartArea.Area.ForegroundColor = Color.White;
chart.Name = detail.WorkSheetName;
chart.Title.Text = detail.ChartTitle;
chart.SizeWithWindow = true;

chart.ValueAxis.IsAutomaticMajorUnit = true;
chart.SecondValueAxis.IsAutomaticMajorUnit = true;
chart.SecondValueAxis.IsVisible = true;
chart.ValueAxis.IsAutomaticMaxValue = true;
chart.SecondValueAxis.IsAutomaticMaxValue = true;
chart.ValueAxis.MinValue = 0;

chart.Calculate();
double majrUnit = chart.ValueAxis.MajorUnit;

The processing is getting hung at “chart.Calculate()”.
Can you try it at your end too and let me know what can be the reason and work around for the same?

Regards,
Kallol
10

Hi Kallol,

Thanks for your posting and using Aspose.Cells.

Please provide us your runnable sample console application project which we could run at our end and replicate this issue. In case, you need a code change, we will suggest it or if it is some bug we will log it in our database so that it could be fixed at the earliest.

11

Hi Shakeed,


I was using Aspose 7.5.2 not 8.2 as I mentioned before by mistake.

Here is the full sample code:

Workbook workBook = new Workbook(@“C:\Users\kallol\Desktop\Sample.xlsx”);
int index = workBook.Worksheets.Add(SheetType.Chart);
Worksheet chartWorkSheet = workBook.Worksheets[index];
chartWorkSheet.IsGridlinesVisible = false;
int ind = chartWorkSheet.Charts.Add(ChartType.Line, 1, 1, 35, 20);
Chart chart = chartWorkSheet.Charts[ind];

Worksheet data = workBook.Worksheets[“Sheet1”];
NameCollection names = workBook.Worksheets.Names;
Name name = names[names.Add(“Sheet1!Val1Range”)];
name.RefersTo = “=IF(Sheet1!$A$6,Sheet1!$A$2:$A$5,Sheet1!$A$7)”;

int nseriesIndex = chart.NSeries.Add(“A1:A2”, true);
Series s = chart.NSeries[nseriesIndex];
s.Values = “=Sheet1!Val1Range”;
s.Name = “Val1”;

chart.PlotArea.Area.BackgroundColor = Color.White;
chart.ChartArea.Area.BackgroundColor = Color.White;
chart.PlotArea.Area.ForegroundColor = Color.White;
chart.ChartArea.Area.ForegroundColor = Color.White;
chart.Name = “Chat1”;
chart.Title.Text = “Chart1”;
chart.SizeWithWindow = true;

chart.ValueAxis.IsAutomaticMajorUnit = true;
chart.SecondValueAxis.IsAutomaticMajorUnit = true;
chart.SecondValueAxis.IsVisible = true;
chart.ValueAxis.IsAutomaticMaxValue = true;
chart.SecondValueAxis.IsAutomaticMaxValue = true;
chart.ValueAxis.MinValue = 0;

chart.Calculate();

workBook.Save(@“C:\Users\kallol\Desktop\Sample.xlsx”);

I am using the sample.xlsx attached.

Regards,
Kallol
12

Hi Kallol,


I have tested your code segment with your newly attached file “Sample.xlsx” with our latest version/fix: Aspose.Cells for .NET v8.3.1.6, it works fine, I am using the following sample code:
e.g
Sample code:


Workbook workBook = new Workbook(“e:\test2\Sample.xlsx”);
int index = workBook.Worksheets.Add(SheetType.Chart);
Worksheet chartWorkSheet = workBook.Worksheets[index];
chartWorkSheet.IsGridlinesVisible = false;
int ind = chartWorkSheet.Charts.Add(ChartType.Line, 1, 1, 35, 20);
Chart chart = chartWorkSheet.Charts[ind];

Worksheet data = workBook.Worksheets[“Sheet1”];
NameCollection names = workBook.Worksheets.Names;
Name name = names[names.Add(“Sheet1!Val1Range”)];
name.RefersTo = “=IF(Sheet1!$A$6,Sheet1!$A$2:$A$5,Sheet1!$A$7)”;

int nseriesIndex = chart.NSeries.Add(“A1:A2”, true);
Series s = chart.NSeries[nseriesIndex];
s.Values = “=Sheet1!Val1Range”;
s.Name = “Val1”;

chart.PlotArea.Area.BackgroundColor = Color.White;
chart.ChartArea.Area.BackgroundColor = Color.White;
chart.PlotArea.Area.ForegroundColor = Color.White;
chart.ChartArea.Area.ForegroundColor = Color.White;
chart.Name = “Chat1”;
chart.Title.Text = “Chart1”;
chart.SizeWithWindow = true;

chart.ValueAxis.IsAutomaticMajorUnit = true;
chart.SecondValueAxis.IsAutomaticMajorUnit = true;
chart.SecondValueAxis.IsVisible = true;
chart.ValueAxis.IsAutomaticMaxValue = true;
chart.SecondValueAxis.IsAutomaticMaxValue = true;
chart.ValueAxis.MinValue = 0;

chart.Calculate();

workBook.Save(“e:\test2\outSample1.xlsx”);

I have also attached the output Excel file for your reference.

So, it looks like an issue with the older version that you are using. We highly recommend you to kindly try our latest version/fix (e.g v8.3.1.6) which works fine. We have made lots of enhancements and included lots of fixes in the newer versions of the product. Moreover, we cannot evaluate or incorporate fixes to older versions, the fixes are only included into the latest versions based on latest APIs set.

Thank you.

13

Hi Amjad,


Thank you so much for your help.

I have tried out your changes and it seems to be working fine.
There is one issue I have seen if after assigning the series and claculate the chart parameter the major unit values are still coming as 0.

chart.Calculate();

// All are shown as zero although this should return some value
double majorUnit = chart.ValueAxis.MajorUnit;
double valueAxisMaxVal = (double)chart.ValueAxis.MaxValue;
double secondValueAxisMaxVal = (double)chart.SecondValueAxis.MaxValue;

Although I have figured out a solution for this, but you can keep a note of this too I guess.

Thanks again for all the help. I will update thread in case of any further issues.

Thanks,
Kallol
14

Hi,


Thanks for your feedback.

Good to know that you have figured out your issue now.

Should you have any further issue and you want us to evaluate it on our side, kindly feel free to write back with all the details, sample code and template file, we will check it soon.

Thank you.