Aspose Cells AutoFitMergedCells

Question

I have an .xls document with cells merged vertically, horizontally and vertically & horizontally at the same time. My code is:

Workbook wb = new Workbook(inPath);
AutoFitterOptions options = new AutoFitterOptions();
options.AutoFitMergedCells = true;

wb.Worksheets[0].AutoFitRows(options);
wb.Worksheets[0].AutoFitColumns(options);

wb.Save(outPath);

AutoFitRows affects horizontally merged cells, AutoFitColumns - vertically merged cells. The problem is that nor of these affects cells merged vertically & horizontally at the same time. Any idea about how to make them autofit?

Not working autofit

Saqib Razzaq · Answer 1 · 2015-02-27T16:26:36.670

0

Updated: Microsoft Excel does not support autofit natively on merged cells. Aspose.Cells also gives unexpected results sometimes on mixed horizontal and vertical merging. You need to post the sample Excel file and code in Aspose forums, so that support can look into it in detail.

I work with Aspose as Developer Evangelist.

edited Feb 27 '15 at 16:26

answered Feb 27 '15 at 12:18

Saqib Razzaq

1,408
1
10
10

As i know Microsoft Excel doesn't allow to apply autofit to merged cells at all, AutoFitMergedCells is a feature of Aspose Cells. – Maksim Feb 27 '15 at 13:48
Please post the sample Excel document in Aspose forums (http://www.aspose.com/community/forums/aspose.cells-product-family/19/showforum.aspx), it sometimes does not work well with mixed horizontal and vertical merging. – Saqib Razzaq Feb 27 '15 at 16:23

score 0 · Accepted Answer · answered Mar 03 '15 at 11:13

I have found a solution on my own. The idea is:

1. Unmerge cells, merge columns back, but don't merge rows.

2. Apply autofit for merged row.

3. Get mergedRowHeight of this merged row, set height for other n rows equal n/mergedRowHeight.

4. Merge rows back.

So the result looks like this:

And code is:

ArrayList mergedAreas = new ArrayList();
mergedAreas = ws.Cells.MergedCells;

AutoFitterOptions options = new AutoFitterOptions();
options.AutoFitMergedCells = true;
foreach (CellArea ca in mergedAreas)
{
    if ((ca.EndColumn - ca.StartColumn > 0) && (ca.EndRow - ca.StartRow > 0))
    {
        ws.Cells.UnMerge(ca.StartRow, ca.StartColumn, ca.EndRow - ca.StartRow, ca.EndColumn - ca.StartColumn);
        ws.Cells.Merge(ca.StartRow, ca.StartColumn, 1, ca.EndColumn - ca.StartColumn + 1);
        ws.AutoFitRow(ca.StartRow, ca.StartColumn, ca.StartColumn, options);
        double rowHeight = ws.Cells.Rows[ca.StartRow].Height;
        for (int i = ca.StartRow; i <= ca.EndRow; i++)
        {
            ws.Cells.Rows[i].Height = rowHeight / (ca.EndRow - ca.StartRow + 1);
        }
        ws.Cells.Merge(ca.StartRow, ca.StartColumn, ca.EndRow - ca.StartRow + 1, ca.EndColumn - ca.StartColumn + 1);
    }
}

Aspose Cells AutoFitMergedCells

2 Answers2