Reading Merged cells

Aspose.Cells Product Family
1

Hello Team,

I’m reading an Excel file row by row using Aspose.Cells Java. If a cell is part of a merged range, I want to get the value from the top-left cell of that range.

Is there a built-in API to check if a cell belongs to a merged range and directly access the top-left value?

Alternatively, is there a way to load the workbook with merged cells automatically unmerged, but with the top-left value filled into all affected cells?

Thank you,
BK

2

@bkmmxxiii

To read an Excel file and retrieve the value from the top-left cell of a merged range using Aspose.Cells, you can utilize the IsMerged property of the Cell class along with the GetMergedRange() method. Here’s how you can achieve this:

  1. Check if a cell is merged: Use the IsMerged property to determine if the cell is part of a merged range.
  2. Get the merged range: If the cell is merged, call the GetMergedRange() method to obtain the range of merged cells.
  3. Access the top-left cell: From the merged range, you can access the top-left cell to get its value.

Here’s a sample code snippet demonstrating this process:

using Aspose.Cells;
using System;

class Program
{
    static void Main()
    {
        // Load the workbook
        Workbook workbook = new Workbook("path_to_your_excel_file.xlsx");
        Worksheet worksheet = workbook.Worksheets[0];

        // Loop through each cell in the worksheet
        for (int row = 0; row <= worksheet.Cells.MaxDataRow; row++)
        {
            for (int col = 0; col <= worksheet.Cells.MaxDataColumn; col++)
            {
                Cell cell = worksheet.Cells[row, col];

                // Check if the cell is part of a merged range
                if (cell.IsMerged)
                {
                    // Get the merged range
                    Range mergedRange = cell.GetMergedRange();
                    // Get the top-left cell of the merged range
                    Cell topLeftCell = worksheet.Cells[mergedRange.FirstRow, mergedRange.FirstColumn];
                    Console.WriteLine($"Cell {cell.Name} is merged. Top-left value: {topLeftCell.StringValue}");
                }
                else
                {
                    Console.WriteLine($"Cell {cell.Name} value: {cell.StringValue}");
                }
            }
        }
    }
}

Alternative Approach: Unmerging Cells

If you prefer to load the workbook with merged cells automatically unmerged and fill the top-left value into all affected cells, you can loop through the merged cells and unmerge them while setting the value of the top-left cell to the other cells in the range. However, this requires additional logic to handle the assignment of values.

Note

Currently, there is no built-in API that directly provides the functionality to automatically unmerge cells while filling the top-left value into all affected cells. You will need to implement this logic manually as shown above.

Feel free to ask if you have any further questions or need additional assistance!

3

@bkmmxxiii,

If you find any issue with the suggested code segment (you may easily convert it to Java for your needs), kindly do provide your template Excel file (please zip the file prior attaching here) and paste your sample code. We will check and assist you soon.

4

@bkmmxxiii
Please refer to the following example code to traverse cells and obtain the value of the first cell in the upper left corner of the merged area. Please refer to the attachment. test.zip (6.1 KB)

// Load the workbook
Workbook workbook = new Workbook(filePath + "test.xlsx");

// Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];

Cells cells = worksheet.Cells;
IEnumerator iter = cells.GetEnumerator();
while (iter.MoveNext())
{
    Cell cell = (Cell)iter.Current;
    if (cell.IsMerged)
    {
        Range range = cell.GetMergedRange();
        if (range.FirstRow == cell.Row && range.FirstColumn == cell.Column)
        {
            Console.WriteLine(cell.Name + " " + cell.StringValue);
        }
    }
}

The output:

B4 test
B10 test2

Hope helps a bit.