Member Avatar for mduncan8

I want to make collapsible groups in an excel document(in excel, on to the Data tab, Outline section, Group button) using XSLT. Is this possible? If so, does anyone have an example or link of how this is done?

Thanks in advance.

  • 4 Contributors
  • 5 Replies
  • 543 Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by sapien

Dani AI

Generated

Short answer: yes — but it depends on which Excel output you produce. Emitting Excel-flavored HTML from XSLT (Excel will open it and honour a few Microsoft-only CSS properties) or producing OpenXML (.xlsx) worksheet XML both let you create real outline/grouping. Saving as the older “XML Spreadsheet 2003” format often won’t preserve Excel outline/group metadata, which explains ’s “lost functionality” when saving to XML. (stigmortenmyre.no)

If you want a quick server-side XSLT route that users open with Excel, emit HTML table rows with the Microsoft style mso-outline-level and hide detail rows by default with display:none (Excel reads those mso styles). Example XSLT fragment (conceptual — adapt attribute names to your source):

<xsl:template match="item">
  <tr>
    <xsl:attribute name="style">
      <xsl:text>mso-outline-level:</xsl:text>
      <xsl:value-of select="@level"/>
      <xsl:if test="@hidden='1'">
        <xsl:text>;display:none;</xsl:text>
      </xsl:if>
    </xsl:attribute>
    <td><xsl:value-of select="label"/></td>
  </tr>
</xsl:template>

This uses the same HTML/CSS outline hints Excel expects. (stigmortenmyre.no)

For a robust, distributable file use Open XML (.xlsx): generate the worksheet XML (/xl/worksheets/sheetN.xml) with <row> elements that include outlineLevel, hidden="1" for detail rows, and collapsed="1" on the summary row. Example sheet fragment:

<sheetData>
  <row r="6" outlineLevel="3" hidden="1"> ... </row>
  <row r="9" outlineLevel="1" collapsed="1"> ...summary row... </row>
</sheetData>

You can swap that XML into a .xlsx template (zip back up). The OpenXML row attributes and outlinePr sheet properties control behaviour. (learn.microsoft.com)

Practical notes: ’s mso-outline-level:1; is valid for HTML exports — to have groups collapsed by default either hide the child rows (display:none) in the HTML approach or set hidden="1" on child rows plus collapsed="1" on the parent when producing sheet XML. Test by creating a small workbook in Excel, saving the desired format, and inspecting the resulting XML to mirror the structure — that’s what suggested and it’s the fastest way to see which attributes Excel expects. (stigmortenmyre.no)

Recommended Answers

All 5 Replies

Member Avatar for iceandrews

This should be able to be done. Microsoft excel document can be exported into and XML document. Then you can use and XSLT to modify this excel document into a different XMl structure that does what you need it to do.

If you want to get the idea, I would make an excel spreadsheet that has the features you want in it. Just make a group, data tab, outline the way you want it to look in excel first. Then export that to an XML excel file and look at the code and they way it works. You can also use Microsoft's XML library. You can then combine the xml file from excel, with whatever other data source you have in XSLt to produce the correct document on the output.

Member Avatar for mduncan8

I tried that but when I save the excel file as an XML document, it loses that functionality.

Member Avatar for iceandrews

It shouldn't, I've done it many times. Can you send me the Excel file that you're trying to export to XML and I'll send it back to you. You can PM me and I'll give you my email address.

Member Avatar for kiranvemula

Hi,
I have excel file , I am able to group them & I am able to Expand/ collapse them in my xslt.
But by default i need to collapse them. Can you please let me know how to do it.
In my XSLT i am using "mso-outline-level:1;"


It shouldn't, I've done it many times. Can you send me the Excel file that you're trying to export to XML and I'll send it back to you. You can PM me and I'll give you my email address.

Member Avatar for sapien

Hello,

I am able to export the dataset to Excel using XML and XSLT, but i cant group/outline the exported excel.

Could you please share the XSLT code for creating Expandable/Collapsable excel file?

I am in an urgent need of it and it will be a great help if you share the code.

Thanks in advance.
Sandeep.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.