Styles & Merged Cells

[JasonSpackman]

I’m currently evaluating your components and have a question about how to best work with formatting merged cells. Currently I have something that looks like:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Worksheet.Cells[iCurrentRow, iCurrentColumn].PutValue(xAxisNode.Attributes["Label"].Value);

if ((iMaxDepth > iDepth) && iCurrentDepth == 1)

{

Worksheet.Cells.Merge(iCurrentRow, iCurrentColumn, 1 + ((iMaxDepth - iDepth - 1) * 2), iLeafNodeCount);

}

else

{

Worksheet.Cells.Merge(iCurrentRow, iCurrentColumn, 1, iLeafNodeCount);

}

//Add formatting here.

The key point to notice here is that the number of columns and rows that will be merge is dynamic and based on contents of the source data. This is boiled down to the variables iMaxDepth, iDepth and iLeafNodeCount in this example. What I want to do is provide formatting to the merged cell. So for example I want the text centered and a medium border. In Excel I would simply right click on the merged cell and set these properties. So I initially thought it would be similar in the component:

Worksheet.Cells[iCurrentRow, iCurrentColumn].Style{etc…}

However, I see that this only formats the first cell in the merged cells. I did a quick search of the forum and found a post saying that you could accomplish formatting of a merge cell like this based on the cell name:

But that only formats the first cell in the merged set. Searching the forum I did see a post saying you could do something like this as:

Worksheet.Cells[“A1”].Style{etc…}

Where “A1” is the first of the merged cells to be formatted. (I think) This has lead me to a number of different questions:

1. Is it better to try and format against the row/column index or the cell name (A1, etc)?

2. Is cell name is preferred, is there an easy way to get this value returned from a cell?

3. Is there a reason that row/column index does not perform the same a cell name?

It may also be that a better implementation should be using something like the .GetMergedRange() method, but it wasn’t clear to me how to apply styles against this. I could just write a few loops and manually execute the code against the range of cells, but it seems there should be a more elegant solution that this.

Thanks for any guidance you can provide.

[Amjad_Sahi]

Hi,

Thanks for considering Aspose.

Let me first explain what is a merged cell: A Merged Cell is basically a single cell that is created by combining two or more selected cells in the range. The cell reference for a merged cell is the upper-left cell in the original selected range. Mind you, if there is data in other cells, the data will be deleted. Suppose, you have cells i.e. C6:E7 to be merged. When you merge the cells in this range, a single cell (upper left most) is created i.e. C6. The cell (C6) will retain its format and other cells in the range would lost their identification and formats etc. It is also to be noted that when you want to format a merged range of cells you will only format C6 cell (merged). Also, other cells like D6, C7, E6, E7 etc. won't be extracted unless you unmerge the range first.

So, I think your both approaches are fine to format the merged cell

Worksheet.Cells[iCurrentRow, iCurrentColumn].Style{etc…}<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Worksheet.Cells[“A1”].Style{etc…}

And kindly check the following doc for your reference:

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/merging-unmerging-cells-in-the-worksheet.html

Thank you.

[JasonSpackman]

I had already attempted the format:

Worksheet.Cells[iCurrentRow, iCurrentColumn].Style{etc…}

Where the cell specfied was the top left cell of a merge... however the results are not what I would expect. If you look at the attached Excel document and click on the Table2 worksheet for example. What you will is is that row 2 has a merged cell from A through I. However the Style applied has only affected a portion of the merged cell, specifically just row 2 column 1. (Or row 1 column 0 for the zero based amongst us.)

I did apply the styles after merging the cells... perhaps I need to apply the styles before the merge? That just didn't seem intuative to me.

[JasonSpackman]

I think I have this working now...

Aspose.Cells.Style Style = Worksheet.Cells[iCurrentRow, iCurrentColumn].Style;
//Style.ForegroundColor = Color.Gray;
//Style.Pattern = Aspose.Cells.BackgroundType.Solid;
Style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
Style.Borders.SetStyle(Aspose.Cells.CellBorderType.Medium);
Style.Borders.DiagonalStyle = Aspose.Cells.CellBorderType.None;
Worksheet.Cells[iCurrentRow, iCurrentColumn].GetMergedRange().Style = Style;

I'm pushing the style class back into the GetMergedRange() and that seems to make the updates apply correctly. A little odd, but I can live with odd.