如何通过XSLT代码实现Excel列宽与行高自动适配内容

要实现生成的Excel文件自动适配内容宽度和高度，无需手动调整，需在原XSLT中添加Excel特定格式配置，以下是修改后的完整代码：

<?mso-application progid="Excel.Sheet"?>

<xsl:stylesheet version="1.0" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ns0="urn:test.com" xmlns:html="http://www.w3.org/TR/REC-html40">
    <xsl:template match="/">
        <Workbook>
            <Styles>
                <Style ss:ID="Default" ss:Name="Normal">
                    <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
                    <Borders/>
                    <Font/>
                    <Interior/>
                    <NumberFormat/>
                    <Protection/>
                </Style>
                <Style ss:ID="s21">
                    <Font ss:Size="22" ss:Bold="1"/>
                </Style>
                <Style ss:ID="s22">
                    <Font ss:Size="14" ss:Bold="1"/>
                </Style>
                <Style ss:ID="s23">
                    <Font ss:Size="12" ss:Bold="1"/>
                    <Alignment ss:WrapText="1"/>
                    <Borders>
                    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
                    </Borders>
                </Style>
                <Style ss:ID="s24">
                    <Font ss:Size="10" ss:Bold="1"/>
                </Style>
                <Style ss:ID="s25">
                <Alignment ss:WrapText="1"/>
                <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
                </Borders>
                </Style>
                </Styles>
            <Worksheet ss:Name="Sheet1">
            <Table>
            <xsl:call-template name="Excel"/>
            </Table>
            <!-- 添加Excel工作表自动适配配置 -->
            <x:WorksheetOptions>
                <x:RowHeight x:AutoFit="1"/>
                <x:ColumnWidth x:AutoFit="1"/>
                <x:ProtectObjects>False</x:ProtectObjects>
                <x:ProtectScenarios>False</x:ProtectScenarios>
            </x:WorksheetOptions>
            </Worksheet>
        </Workbook>
    </xsl:template>
    <xsl:template name="Excel">
            <Row>
            <Cell ss:StyleID="s23">
            <Data ss:Type="String">IntegrationFlowName</Data>               
            </Cell>
            <Cell ss:StyleID="s23">
            <Data ss:Type="String">IFlowStartTime</Data>               
            </Cell>
            <Cell ss:StyleID="s23">
            <Data ss:Type="String">MessageGUID</Data>
            </Cell>
            <Cell ss:StyleID="s23">
            <Data ss:Type="String">ErrorMessage</Data>                
            </Cell>
            </Row>
        <xsl:for-each select="//MessageProcessingLogs">

            <Row>
                <Cell ss:StyleID="s25">
                    <Data ss:Type="String">
                        <xsl:value-of select="IntegrationFlowName" />
                    </Data>
                </Cell>
                <Cell ss:StyleID="s25">
                    <Data ss:Type="String">
                        <xsl:value-of select="StartTime" />
                    </Data>
                </Cell>
                <Cell ss:StyleID="s25">
                    <Data ss:Type="String">
                        <xsl:value-of select="MessageGuid" />
                    </Data>
                </Cell>
                <Cell ss:StyleID="s25">
                    <Data ss:Type="String">
                        <xsl:value-of select="ErrorMessage" />
                    </Data>
                </Cell>
            </Row>
        </xsl:for-each>
    </xsl:template>
    <xsl:template match="MessageProcessingLogs">
</xsl:template>
</xsl:stylesheet>

关键修改说明 #

• 自动换行配置：在Default、s23、s25样式中添加<Alignment ss:WrapText="1"/>，让长内容自动换行，避免被单元格边界截断。
• 自动适配行列：在Worksheet节点内新增x:WorksheetOptions配置，通过<x:RowHeight x:AutoFit="1"/>和<x:ColumnWidth x:AutoFit="1"/>指令，让Excel打开文件时自动计算并适配内容对应的行高和列宽。

内容的提问来源于stack exchange，提问作者munireddy pulagam