Merge Cells

Apr 10, 2008 at 4:14 PM
Has anyone found a way to merge cells? I contacted sildeomix re: his proposed patch in Merge Cells but never heard back. I've been tinkering with it for a couple weeks now and can't figure it out. Any help is appreciated.
Apr 10, 2008 at 5:02 PM
Edited Apr 10, 2008 at 5:08 PM
Add this to ExcelWorksheet.cs I don't believe there were any other changes.

/// <summary>
/// Sets up a region of merged cells.
/// </summary>
/// <param name="startCell">The beginning (top left) of the merged region.</param>
/// <param name="numRows">The number of rows to merge</param>
/// <param name="numCols">The number of columns to merge</param>
public void MergeCells(ExcelCell startCell, int numRows, int numCols)
{
MergeCells(startCell, Cell(startCell.Row + numRows, startCell.Column + numCols));
}

/// <summary>
/// Sets up a region of merged cells.
/// </summary>
/// <param name="startCell">The beginning (top left) cell of the merge</param>
/// <param name="endCell">The end (bottom right) cell of the merge</param>
public void MergeCells(ExcelCell startCell, ExcelCell endCell)
{
XmlNode mergeNode = WorksheetXml.SelectSingleNode("//d:mergeCells", NameSpaceManager);
int mergeCount = 0;
if (mergeNode == null)
{
mergeNode = WorksheetXml.CreateElement("mergeCells", ExcelPackage.schemaMain);
XmlNode prevNode = WorksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager);
WorksheetXml.DocumentElement.InsertAfter(mergeNode, prevNode);
}
XmlAttribute attr = mergeNode.Attributes["count"];
if (attr == null)
{
attr = WorksheetXml.CreateAttribute("count");
mergeNode.Attributes.Append(attr);
}
else
mergeCount = int.Parse(attr.Value);

// see if we already have this merge section
XmlNode mergeDefNode = mergeNode.SelectSingleNode(string.Format("./d:mergeCell[@ref='{0}:{1}']", startCell.CellAddress, endCell.CellAddress), NameSpaceManager);
if (mergeDefNode == null)
{
mergeDefNode = WorksheetXml.CreateElement("mergeCell", ExcelPackage.schemaMain);
mergeNode.AppendChild(mergeDefNode);
attr = WorksheetXml.CreateAttribute("ref");
mergeDefNode.Attributes.Append(attr);
attr.Value = string.Format("{0}:{1}", startCell.CellAddress, endCell.CellAddress);
mergeCount++;
}

attr = mergeNode.Attributes["count"];

// we already know the attr can't be null since we looked at it earlier
attr.Value = mergeCount.ToString();
}
Apr 10, 2008 at 6:22 PM
Awesome - works perfect. Thanks slide!
Apr 11, 2008 at 1:51 AM
I don't know if this will help anyone, but it looks like named styles don't apply to merged cells very well. The text is formatted fine, but any borders require that you actually apply the style to all of the cells contained in the merge. To make this simple, I added another overload:

/// <summary>
/// Sets up a region of merged cells.
/// </summary>
/// <param name="startCell">The beginning (top left) cell of the merge</param>
/// <param name="endCell">The end (bottom right) cell of the merge</param>
/// <param name="style">The name of the style that should be applied to the merged cells</param>
public void MergeCells(ExcelCell startCell, ExcelCell endCell, string style)
{
MergeCells(startCell, endCell);
for (int i = startCell.Row; i <= endCell.Row; i++)
{
for (int j = startCell.Column; j <= endCell.Column; j++)
{
Cell(i, j).Style = style;
}
}

}


Not complex, I know... but good to know if you're using styles in your workbook.