Named range does not grow horizontally with smart markers

[nexxbiz-asponse]

Hi, Today I found out that named ranges do not grow horizontally when using smart markers.

Consider the following example.
Note: functions NextRight (etc) are extension methods I implemented, feel free to adopt.

Cell upperLeftCorner = columnsCellName;
Cell lowerRightCorner = columnsCellValue;
foreach (DataColumn column in table.Columns)
{
	// give the cell the name of the column
	sheet.Cells.CreateRange(columnsCellValue.Name).Name = column.ColumnName;
	columnsCellName.Value = column.ColumnName;
	lowerRightCorner = columnsCellValue;
	//Place the marker and move to the next cells
	if (orientation == KindOfOrientation.Horizontal)
	{
		// Normal table layout, columnnames at the top.
		columnsCellValue.Value = "&=" + tablename + "." + column.ColumnName;
		columnsCellName = columnsCellName.NextRight();
		columnsCellValue = columnsCellValue.NextRight();
	}
	else
	{
		//Rotated table layout, columnnames at the left, each row is a column
		columnsCellValue.Value = "&=" + tablename + "." + column.ColumnName + "(horizontal)";
		columnsCellName = columnsCellName.NextDown();
		columnsCellValue = columnsCellValue.NextDown();
	}
}
			
//Create range for the entire table
sheet.Cells.CreateRange(upperLeftCorner.Name, lowerRightCorner.Name).Name = tablename;

Now when I use the Designer to process the table in Horizontal orientation, the range 
will automatically grow with each row in the table (all rows are inside range).
When I use the vertical; orientation, the range remains untouched.
Which means that only the first row (diplayed in the first column) will be included in the range.

This is not consistent behaviour.
Please let me know if this is a bug, know isssue or a shortcomming in my understanding of the software.

Kind regards,
Bas

[amjad.sahi]

Hi,

Thanks for providing us some details.

First of all, please download and try the latest fix: Aspose.Cells for .NET v7.0.2.5

If you still find the issue, we request you kindly create a sample console application (you may use some hard coded values for the objects eliminating any dependencies so that the example should be run on our side properly), zip it and post it here to reproduce the issue on our end, also attach your template files (input and output) here. We will check your issue soon. If we find the issue, we will log it into our internal issue tracking system to figure it out soon.

Thank you.

[nexxbiz-asponse]

I have tested with the version you suggested but the problem remains.

I have created a demo project to demonstrate the effect.
The code might be slightly bulky but I extracted the code I am working with.

The application defaults to 'horizontal' orientation.
You can crete the 'vertical' orientation by passing an 'v' as an argument.
The result is saved to either "horizontal.xlsx" or "vertical.xlsx".

A link to the table is created on the page "inhoudsopgave". If you click on the link the named region will be selected. This demonstrates that the region does not grow horizontally.

Kind regards, Bas.

[amjad.sahi]

Hi,

Thanks for sharing the project.

After an initial test, I can notice the issue when setting the orientation to vertical, it works the other way (default way) as you mentioned. I have logged a ticket for it to further investigate the issue with an id: CELLSNET-40651. We will look into it soon.

Thank you.

[nexxbiz-asponse]

Hi,

Thank you for acknowledging the problem.
Can you give an estimate on when this will be picked up/fixed?

We will need this to work for the next release of our software (it needs to be fixed or we need to find an alternative) so we are on a schedule:
< 3 days - Wonderful!
< 2 week - Acceptable.
< 1 month - Problematic.
> 1 month - Not OK.

Thank you for your time,
Bas.

[amjad.sahi]

Hi,

I have asked the concerned developer if we can provide an eta for your issue. As soon as I get any response from him, I will let you know here.

Thanks,

[shakeel.faiz]

Hi

We have fixed this issue.

Please download and try the fix: Aspose.Cells for .NET v7.2.0.7

[nexxbiz-asponse]

Hi,

I am going to test this with in an hour or two, if it works, then you guys are totally awesome.
I'll keep you up to date with my findings.

Cheers, Bas.

[nexxbiz-asponse]

Hi Guys,

The code now works as expected.

Totally awesome, thanks,
Bas.

[aspose.notifier]

The issues you have found earlier (filed as CELLSNET-40651) have been fixed in this update.

---

This message was posted using Notification2Forum from Downloads module by aspose.notifier.

[nexxbiz-asponse]

Hello guys,




I have noticed that still exists (I downloaded the latest version) when the region is only a single cell.



Can you please take a look (and possibly fix) this?

[babar.raza]

Hi Bas,

I have modified your previously provided sample application according to your recently shared scenario, and executed it with latest build of Aspose.Cells for .NET 7.7.2.3. I am afraid, I was unable to notice any problem in the resultant file. There could be a difference in my understanding or the mentioned issue does not exist in the latest version of the API. Therefore, I would request you to please give a try to the latest build on your end. In case the problem persists, please provide us a fresh sample application replicating the issue on our end. We will look into it at earliest to provide further assistance in this regard.

Thank you for your cooperation and understanding.

[nexxbiz-asponse]

Sorry for the delay, we’ve been busy working on some other things.

The sample application IS NOT VALID for the post created on 03-07 as it was part of the ORIGINAL posting.

However, the issue still exists that ‘Horizontal growth’ works fine when the named region you start with has 2 columns or more… however it does not work when the starting region is only one column wide.

The number of rows in the named region or in the dataset do not seem to affect the reslt…

Please advice,
Bas.

[shakeel.faiz]

Hi Bas,

Thanks for your posting and using Aspose.Cells.

Could you provide us a simpler console application with your expected and actual output xlsx files illustrating this issue. Screenshots will also be helpful. It will help us look into your issue precisely and we will be able to fix it.

[Jaganlal]

Hi
You can use Smart Markers
&=TableName.ColumnName(horizontal) -> it will add column horizontal
&=TableName.ColumnName(horizontal,copystyle) -> it will add column horizontal with first column style
&=TableName.ColumnName(horizontal,copystyle,noadd) -> it will add column horizontal with first column style and it will add new columns overwrite existing column.

Regards,

Jaganlal

[nexxbiz-asponse]

Dear sir,

My problem is not with getting the values parsed into excel sheet. I know how to do that quite comfortably. The problem I am having is that the ‘Named regions’ will not grow with the number of rows.

Strangely I am not able to reproduce the original point (P1), that the ‘named region will not grow if it is a single cell’, in the attached test application, instead the regions now refuse to grow at all (P2).

Perhaps (P1) only occurs when the sheets are more complex or contain more that one table.

EDIT: I am able to reproduce (P1) by removing the ‘copystyle’ element from the marker. In that case ‘GrowingRegion’ DOES grow while ‘NotGrowing’ DOES NOT.
Note that the Style is copied anyways!

As requested, I have attached an expected and an actual result plus a copy of the dll version we are currently using.

To summarize:

Named regions are not growing as expected.

I hope that I have provided enough information to reproduce and resolve the issue.

The code used to parse the values:

DataSet demoSet = new DataSet("Demo");

var table = new DataTable("Table1");

demoSet.Tables.Add(table);

table.Columns.Add("Column1", typeof(string));

table.Rows.Add("row 1");

table.Rows.Add("row 2");

table.Rows.Add("row 3");

var workbook = new Workbook("GrowDemo.xlsx");

var designer = new WorkbookDesigner();

designer.Workbook = workbook;

designer.SetDataSource(demoSet);

designer.Process();

workbook.CalculateFormula(false); // <-- not really needed, for this demo, I am mimicking production.

// fetch value of the regions.
var tableRange1 = workbook.Worksheets.GetRangeByName("GrowingRegion");

var tableRange2 = workbook.Worksheets.GetRangeByName("NotGrowing");

workbook.Save("GrowDemo_Out.xlsx", SaveFormat.Xlsx);

Process.Start("GrowDemo_Out.xlsx");

[shakeel.faiz]

Hi,

Thanks for your posting and using Aspose.Cells.

As a workaround, you can create a named range like this

=Blad1!$B$1:$B$1

instead of like this

=Blad1!$B$1

It will then work fine.

I have attached GrowDemo.xlsx in which I have added another named range Test2 which refers to single cell B6 like this =Blad1!$B$6:$B$6.

After the execution of application, it generates GrowDemo_Out.xlsx and Test2 grows to be =Blad1!$B$6:$D$6

[nexxbiz-asponse]

Hi,

I am sorry to say that this is not a solution/option for our problem.

The reason for this is that in our documents we use a large number of named regions which are generated based on a data source. Editing these by hand is not doable.

Below I post the code of the test application, modified to create regions the way we do in our production code. Please note that the 'oversized' region is the only one that grows.

Thanx, Bas.

DataSet demoSet = new DataSet("Demo");

var table = new DataTable("Table1");

demoSet.Tables.Add(table);

table.Columns.Add("Column1", typeof(string));

table.Rows.Add("row 1");

table.Rows.Add("row 2");

table.Rows.Add("row 3");

var workbook = new Workbook("GrowDemo.xlsx");

var designer = new WorkbookDesigner();

designer.Workbook = workbook;

var sheets = workbook.Worksheets;

var sheet = sheets[0];

/* NoSize */

var cell = sheet.Cells["A7"];

sheet.Cells.CreateRange(cell.Name).Name = "NoSize";

cell.Value = "&=Table1.Column1(horizontal)";

/* ExplicitSize */

cell = sheet.Cells["A8"];

sheet.Cells.CreateRange(cell.Row, cell.Column, 1, 1).Name = "ExplicitSize";

cell.Value = "&=Table1.Column1(horizontal)";

/* Oversized */

cell = sheet.Cells["A9"];

sheet.Cells.CreateRange(cell.Row, cell.Column, 1, 2).Name = "Oversized";

cell.Value = "&=Table1.Column1(horizontal)";

designer.SetDataSource(demoSet);

designer.Process();

workbook.CalculateFormula(false); // <-- not really needed, for this demo, I am mimicking production.

// fetch value of the regions.

var tableRange1 = sheets.GetRangeByName("GrowingRegion");

var tableRange2 = sheets.GetRangeByName("NotGrowing");

// check the generated regions

var tableRange3 = sheets.GetRangeByName("NoSize");

var tableRange4 = sheets.GetRangeByName("ExplicitSize");

var tableRange5 = sheets.GetRangeByName("Oversized");

// as you can see, only Oversized grew

workbook.Save("GrowDemo_Out.xlsx", SaveFormat.Xlsx);

Process.Start("GrowDemo_Out.xlsx");

[shakeel.faiz]

Hi Bas,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue. The name range should grow when it is referring to a single cell like =Blad1!$B$1. It grows successfully when it is referring like this =Blad1!$B$1:$B$1.

We have logged this issue in our database for investigation so that it could be fixed in this scenario too. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-42525.

[shakeel.faiz]

Hi,

Thanks for using Aspose.Cells

Please download and try this fix: Aspose.Cells
for .NET v8.0.0.3 and let us know your feedback.