Aspose cell smart markers with multiple parameters issue

Aspose.Cells Product Family
1

Hi all,

I use Aspose Cell to generate Excel document with excel template, it's very good and convenience.

Now i have a question with Smart Markers.

First step, i need binding a variable (string array) in cells with horizontal , i wrote dynamic fomula like this:

&=$RoleInfo(horizontal)

And it's works good.

Second, i need merge the same records when binding string array in cells, i wrote like this

&=$RoleInfo(group:merge)

And it's also works.

Last, i need both horizontal and group merge in my excel, so i do some works like this:

&=$RoleInfo(group:merge, horizontal).

But it's not works.

Just show my data horizontally, without group merge, very strange.

Can anyone helps on this.

Thanks in advance.

Arwind

2

Hi Arwind,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue.

The following smart markers work fine

&=$RoleInfo(horizontal)
&=$RoleInfo(group:merge)

But the following smart marker does not work.

&=$RoleInfo(group:merge,horizontal)

It means, group:merge and horizontal parameters are not working together.

We have tested this issue with the following sample code. I have attached the smart marker designer file and the output file for a reference.

If you check Sheet1 and Sheet2 in the output file, you will see smart markers work fine but in Sheet3 smart marker just shows data horizontally without merging them.

We have logged this issue in our database for investigation. 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-42627.

C#


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


WorkbookDesigner designer = new WorkbookDesigner();

designer.Workbook = new Workbook(filePath);


string[] roles = { “apple”, “apple”, “apple”, “pears”, “pears” };


designer.SetDataSource(“RoleInfo”, roles);


designer.Process();


designer.Workbook.Save(“out.xlsx”);

3

Hi,


Well, to further update you that Aspose.Cells does not support to group data horizontally in Smart Markers, this feature is not currently supported, so it is not a bug. We might support it in future versions.
As we have already logged a ticket for it, so once we have any update on it, we will let you know here.

Thank you.
4

Hello,

Yes, this is my issue, and now i want to know is there any workaround to solve my question currently? it's a little urgent for me.

Thank you.

Arwind

5

Hi,


Well, I am afraid, as this feature (Support data grouping horizontally in Smart Markers) is not currently supported and we have recently logged it into our database, so it cannot be supported in short time, it might be supported in future versions though.

Also, I am afraid, there may not be a workaround to cope with it.

Thanks for your understanding!
6

Hello Amjadv,I figure out a potential solution, could you help to assess it?

can i change excel fomular to code to solve it? Assume i have a datatable like below:

Title1 Title1 Title2 Title2

Sub1 Sub2 Sub3 Sub4

The first step, binding cells with them,Then i can handle the sheet's cells and set its value with my data, then merge the same title. for example

//Merge some Cells (C6:E7) into a single C6 Cell.

cells.Merge(5, 2, 2, 3);

//Merge some Cells (C6:E7) into a single C6 Cell.

cells.Merge(5, 2, 2, 3);

7

Hi Arwind,

Thanks for your posting and using Aspose.Cells.

Yes you can use Merge method to merge the cells horizontally and you can write your own algorithm for it. I have also write the following algorithm, which merges the same data in cells horizontally. You can use it or modify it as per your needs.

I have attached the source and the output xlsx generated by the code for your reference. I have also attached the screenshot that shows the difference between the source and output file. As you can see cells with same data has been merged horizontally.

To merge the cells, the algorithm finds first and last cell, then creates a range using Cells.CreateRange() method and then merges the range using Range.Merge() method.

C#


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


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


Cell firstCell = worksheet.Cells[“A1”];

Cell lastCell = firstCell;

int moveColIdx = 1;


do

{

Cell nextCell = worksheet.Cells[firstCell.Row, firstCell.Column + moveColIdx];


if (firstCell.StringValue == nextCell.StringValue)

{

lastCell = nextCell;

moveColIdx++;

}


if (lastCell.StringValue != nextCell.StringValue)

{

Range range = worksheet.Cells.CreateRange(firstCell.Name, lastCell.Name);

range.Merge();


firstCell = nextCell;

lastCell = nextCell;

moveColIdx = 1;

}


if (nextCell.StringValue == “”)

break;


} while (true);



workbook.Save(filePath + “.out.xlsx”);




8

Thanks a lot, Shakeel, this is what i want.

Also hope we can use the horizental and group parameters together in nearly future.

9

Hi Arwind,

Thanks for your feedback and using Aspose.Cells.

It is good to know that the above code snippet were helpful for you. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

10

The issues you have found earlier (filed as CELLSNET-42627) have been fixed in Aspose.Cells for .NET 18.6. Please also check the document/article for your reference: Install Aspose Cells through NuGet|Documentation