Identifying tablix on xlsx worksheet when more than one tablix was used

Aspose.Cells Product Family
1

I have created a xlsx worksheet from an RDLC in c# .net (RDLC similar to RDL in SSRS). I use two tablix in RDLC to create the two tables in the worksheet.

When I only have 1 tablix, I can refine the worksheet after the data in loaded by applying style element to cells.

When I have two tablix, the style elements do not affect either table on the worksheet.

Do I need to refer to a specific tablix to apply style elements, and or is there a better way to do this.

In the C# code, I am trying to unmerge cells in the header, just above the data in the tablix. The area that I'm trying to unmerge is colored in red in the attached file.

I am using the following code to unmerge the cells:

Cell cell = worksheet.Cells["B1"];
if (cell.IsMerged)
worksheet.Cells.UnMerge(0,1,1,1);

Again, this code works just fine when only one tablix is used in the RDLC, but fails to work when a second tablix is placed on the RDLC.

Thoughts?

2

Hi Bryan,

Thanks for your posting and using Aspose.Cells.

I was able to unmerge your red colored ranges successfully using the following code. I have attached the output Excel file generated by the code for your reference. I tested this issue with the latest version: Aspose.Cells
for .NET v8.3.0.3.

C#


string filePath = @“D:\Downloads\Two+Tablix.xlsx”;


//Create workbook from source Excel file

Workbook workbook = new Workbook(filePath);


//Access the first worksheet

Worksheet worksheet = workbook.Worksheets[0];


//Unmerge first red colored range

Cell cell = worksheet.Cells[“B4”];


Range range = cell.GetMergedRange();

range.UnMerge();


//Unmerge second red colored range

cell = worksheet.Cells[“B15”];


range = cell.GetMergedRange();

range.UnMerge();


//Save the workbook

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

3

Hi Shakeel -

This worked very well, however, I have one more caveat to the problem. When creating the two tables, the number of lines in each table will vary, there may be 10 lines or 100 lines.

The top table is static in position, but the second table will vary its position based upon the number of lines in the first table.

How would you locate the second range of cells to unmerge?

Thanks

Bryan

4

Hi Bryan,

Thanks for your feedback and using Aspose.Cells.

Please see the following sample code if it suits your requirements. It unmerges all the merged cells inside the worksheet.

If it does not suit your requirement, then please provide me example workbooks and let me know the rule of finding the desired merged cell, for example in your current file, desired merged cells are filled with red color, we can find such merged cells by finding the cells with such a color and then unmerge them. Also, the rule can be the next adjacent cell that contains the word “Tablix” etc.

I have attached the output Excel file generated by the code for your reference.

C#


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


Workbook workbook = new Workbook(filePath);


//Access the first worksheet

Worksheet worksheet = workbook.Worksheets[0];


var v = worksheet.Cells.MergedCells;


//Unmerge all merged cells

foreach (CellArea area in worksheet.Cells.MergedCells)

{

//Find upper left and lower right cells of the merged area

string upperLeftCell = CellsHelper.CellIndexToName(area.StartRow, area.StartColumn);

string lowerRightCell = CellsHelper.CellIndexToName(area.EndRow, area.EndColumn);


//Create a range and unmerge it

Range range = worksheet.Cells.CreateRange(upperLeftCell, lowerRightCell);

range.UnMerge();

}//foreach


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