How to find Center Across Selection styles

Aspose.Cells Product Family
1

Hello,
In the attached workbook Range F5:H5 and range I5:K5 have been formatted using the Center Across Selection horizontal style feature in Excel. Which should line up to the TextAlignmentType.CenterAcross enum in Aspose. These cells appear like Merged Cells without actually merging the cells. My question is how can I inspect a workbook to find these styles that apply to a range of cells. If I look at worksheet.Cells[“F5”].GetDisplayStyle().HorizontalAlignment the property equals TextAlignmentType.CenterAcross, however I can’t figure out how to tell that the next 2 cells are part of this CenterAcross selection.

CenterAcrossSelection.zip (6.5 KB)

Thank You,
-Andy

2

@weissa,

Thanks for the template file.

I have checked your template Excel file and evaluated your requirements. Your mentioned ranges are not merged, so there is no attribute to distinguish these are your so called two ranges. I think you may search/find the cells with Center Across Style. You may use Find/Search options provided by Aspose.Cells for your task. See the following sample code that will give you cells list/details with Center Across Selection Style for your reference.
e.g.,
Sample code:

Workbook workbook = new Workbook("e:\\test2\\CenterAcrossSelection.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
//Defind style object based on F5 cell's style
Style style = worksheet.Cells["F5"].GetDisplayStyle(true);

//Specify the style for searching
FindOptions options = new FindOptions();
options.Style = style;

Cell nextCell = null;

do
{
    //Find the cell that has a style of cell F5
    nextCell = worksheet.Cells.Find(null, nextCell, options);

    if (nextCell == null)
        break;

    //Write cell name with its horizontal alignment settings.
    Console.WriteLine("Cell Name: " + nextCell.Name + ", Horizontal Alignment Style: " + nextCell.GetDisplayStyle().HorizontalAlignment.ToString());
    
} while (true);

Hope, this helps a bit.

3

@amjad.sahi,

I appreciate the code sample but that doesn’t quite address the need.

Using the code below you can create a style for the range B2:E2 to set the CenterAcross text. But I don’t see any way possible to interrogate a workbook to then find this style that spans a range. The example you provided helps to find the cells with a style set to CenterAcross, but no way to tell that the style applies to a range of cells.

Is there any way to iterate through Style objects and ask what range they apply to?

    [Test]
    public async Task foo47()
    {
        var workbook = new Workbook();

        workbook.Worksheets.First().Cells["B2"].PutValue("Hello World");

        var range = workbook.Worksheets.First().Cells.CreateRange("B2:E2");
        var style = workbook.CreateStyle();
        style.HorizontalAlignment = TextAlignmentType.CenterAcross;

        range.SetStyle(style, true);

        workbook.Save(@"C:\out.xlsx");
    }
4

@weissa
You can refer to the following example code to obtain ranges with a specific style.

Workbook workbook = new Workbook();
Style style = workbook.CreateStyle();
style.HorizontalAlignment = TextAlignmentType.CenterAcross;

Worksheet sheet = workbook.Worksheets.First();
sheet.Cells["B2"].PutValue("Hello World");
sheet.Cells["B3"].PutValue("111");
sheet.Cells["B4"].PutValue("222");
Range range1 = sheet.Cells.CreateRange("B2:E2");
range1.Name = "range1";
sheet.Cells.Ranges.Add(range1);
Range range2 = sheet.Cells.CreateRange("B3:E3");
range2.Name = "range2";
sheet.Cells.Ranges.Add(range2);
Range range3 = sheet.Cells.CreateRange("B4:E4");
range3.Name = "range3";
sheet.Cells.Ranges.Add(range3);

range1.SetStyle(style, true);
range2.SetStyle(style, true);            

Console.WriteLine(sheet.Cells.Ranges.Count);

// find range with Style.HorizontalAlignment == TextAlignmentType.CenterAcross
foreach (Range range in sheet.Cells.Ranges)
{
    Cell cell = range.GetCellOrNull(0, 0);
    if (cell != null)
    {
        if (cell.GetStyle().HorizontalAlignment == TextAlignmentType.CenterAcross)
        {
            Console.WriteLine(range.Name);
        }
    }
}

Hope helps a bit.

5

@weissa
There is no such method to support your need, please try to iterate all cells as the following codes :

 static async Task Main(string[] args)
 {

     string dir = @"d:\Filetemp\";
     var workbook = new Workbook(dir + "CenterAcrossSelection.xlsx");

     Cells cells = workbook.Worksheets[0].Cells;
     Cell start = null;
     Cell end = null;
     for (IEnumerator ie = cells.GetEnumerator();ie.MoveNext();)
     {
         Cell cell = (Cell)ie.Current;
         Style style = cell.GetStyle(false);
         if(style.HorizontalAlignment== TextAlignmentType.CenterAcross)
         {
             if(start == null)
             {
                 start = end = cell;
                 continue;
             }
             else if(end.Row == cell.Row && end.Column + 1== cell.Column)
             {
                 if(cell.Type == CellValueType.IsNull)
                 {
                     end = cell;
                     continue;
                 }
                 else
                 {
                     Console.WriteLine(start.Name + " : " + end.Name);//CenterAcross range
                     start = end = cell;

                 }

             }
             else
             {
                 Console.WriteLine(start.Name + " : " + end.Name);//CenterAcross range
                 start = end = cell;
             }
         }
         else
         {
             if (start != null)
             {
                 Console.WriteLine(start.Name + " : " + end.Name);//CenterAcross range
                 start = end = null;
             }
         }
     }
     if (start != null)
     {
         Console.WriteLine(start.Name + " : " + end.Name);//CenterAcross range
         start = end = null;
     }
 }
6

@simon.zhao ,

This is very helpful. I’ll give it a try today and will let you know how it works out.

Thanks for the code sample.

Andy.

7

@weissa
Please take your time to try the suggested solutions. Hopefully, your issue will be sorted out. Please let us know your feedback.

8

@simon.zhao,

This technique does the job! Checking if(cell.Type == CellValueType.IsNull) was the important bit that I needed to be able to tell where the CenterAcross ends. Thanks again for the help.

-Andy

9

@weissa
Thank you for your feedback. Your understanding is correct.

if(cell.Type == CellValueType.IsNull)

This statement is important as it continues to search for range boundaries when encountering empty or null value cells within the range.