Search code examples
excelopenxml

How do I specify merged cells in SpreadsheetML

I am attempting to user SpreadsheetML to generate an Excel report server side and download the report via the browser. I have everything working and am getting the files I need downloaded. However, I run into a problem when I attempt to merge cells in one of the sheets I am creating. I have found two different syntaxes online and tried them both without success. I save the files as .xml files and the will open fine and show the expected data but the cells are not merged.

The first syntax uses the "mergeAcross" qualifier on the element and is supposed to merge the number of cells specified into the current cell. The second syntax using the element. I have pasted the actual xml code below for both attempts. If I can figure out what the XML should be then I can create it programatically easily.

Version 1

<?xml version='1.0'?>
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<ss:Worksheet ss:Name='Distribution List Overview'>
<ss:Table>
    <ss:Row>
        <ss:Cell mergeAcross="2"><ss:Data ss:Type="String">First Cell Entry</ss:Data></ss:Cell>
        <ss:Cell><ss:Data ss:Type="String">Third Cell</ss:Data></ss:Cell>
    </ss:Row>
</ss:Table>
</ss:Worksheet>
</ss:Workbook>

Version 1 

<?xml version='1.0'?>
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<ss:Worksheet ss:Name='Distribution List Overview'>
<ss:Table>
    <ss:Row>
        <ss:Cell><ss:Data ss:Type="String">First Cell Entry</ss:Data></ss:Cell>
        <ss:Cell><ss:Data ss:Type="String">Third Cell</ss:Data></ss:Cell>
    </ss:Row>
</ss:Table>
<mergeCells count="2">
    <mergeCell ref="A1:B1"/>
</mergeCells>
</ss:Worksheet>
</ss:Workbook>

Both approaches above fail to create a merged cell. I am expecting to get "First Cell Entry" filling cells A1 and B1 with "Third Cell" in cell C1. Instead I get "First Cell Entry" in cell A1 and "Third Cell" in cell B1. Any help or suggestions would be greatly appreciated. This is the only remaining item I need to get some legacy code working and I do not want to convert the entire report over to OpenXML SDK code.

Solution

  • Try replacing mergeAcross with ss:MergeAcross. In other words, use:

    <ss:Cell ss:MergeAcross="2"><ss:Data ss:Type="String">First Cell Entry</ss:Data></ss:Cell>

    Also, you may notice that to get "A1:B1" merged cell you need to set ss:MergeAcross value to 1, not 2.