In Open Document *.ods files cells which are identically are not stored redundant. Instead the first cell will have a attribute table:number-columns-repeated which tells the count of repeating: <table:table-cell table:number-columns-repeated="2" ...>.
So XSLT which transforms this to XMLwhich needs all elements must generating elements repeatedly. Since we need using XSLT 1.0, this is not as simple as it should. We need a template which calls itself recursively.
Also your numbered element names p1, p2, ... does not making things easier. To achieve this and having the numbering according the resulting output elements instead of the input elements we must collecting the output elements in a variable first and then numbering that collection.
And your elements names are confusing. What you are calling Column are rows and the p elements are cells. So I have renamed them accordingly.
So having the following sheet:
and using export XSLT of:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:exsl="http://exslt.org/common"
xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0"
xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0"
xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" exclude-result-prefixes="office table text exsl">
<xsl:template match="/">
<xsl:element name="sheet">
<xsl:apply-templates select="/*/office:body" />
</xsl:element>
</xsl:template>
<xsl:template match="office:body">
<xsl:apply-templates />
</xsl:template>
<xsl:template match="office:spreadsheet">
<xsl:apply-templates />
</xsl:template>
<xsl:template match="office:spreadsheet/table:table">
<xsl:for-each select="table:table-row">
<xsl:element name="row">
<xsl:variable name="thecells">
<xsl:for-each select="table:table-cell">
<xsl:variable name="repeated">
<xsl:choose>
<xsl:when test="@table:number-columns-repeated">
<xsl:value-of select="@table:number-columns-repeated" />
</xsl:when>
<xsl:otherwise>
<xsl:text>1</xsl:text>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:call-template name="repeatecells">
<xsl:with-param name="start" select="1" />
<xsl:with-param name="end" select="$repeated"/>
</xsl:call-template>
</xsl:for-each>
</xsl:variable>
<xsl:for-each select="exsl:node-set($thecells)/cell">
<xsl:element name="{concat('cell', position())}"><xsl:value-of select="current()" /></xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:for-each>
</xsl:template>
<xsl:template name="repeatecells">
<xsl:param name="start"/>
<xsl:param name="end"/>
<xsl:if test="not($start > $end)">
<xsl:choose>
<xsl:when test="$start = $end">
<xsl:element name="cell"><xsl:value-of select="text:p" /></xsl:element>
</xsl:when>
<xsl:otherwise>
<xsl:variable name="mid" select= "floor(($start + $end) div 2)"/>
<xsl:call-template name="repeatecells">
<xsl:with-param name="start" select="$start"/>
<xsl:with-param name="end" select="$mid"/>
</xsl:call-template>
<xsl:call-template name="repeatecells">
<xsl:with-param name="start" select="$mid+1"/>
<xsl:with-param name="end" select="$end"/>
</xsl:call-template>
</xsl:otherwise>
</xsl:choose>
</xsl:if>
</xsl:template>
</xsl:stylesheet>
results in:
<?xml version="1.0"?>
<sheet>
<row><cell1>P1</cell1><cell2>P2</cell2><cell3>P3</cell3><cell4>P4</cell4><cell5>P5</cell5><cell6>P6</cell6></row>
<row><cell1>1</cell1><cell2>1</cell2><cell3>1</cell3><cell4>1</cell4><cell5>2</cell5><cell6>2</cell6></row>
<row><cell1>1</cell1><cell2>2</cell2><cell3>2</cell3><cell4>2</cell4><cell5>2</cell5><cell6>3</cell6></row>
<row><cell1>1</cell1><cell2>1</cell2><cell3>1</cell3><cell4>2</cell4><cell5>2</cell5><cell6>2</cell6></row>
<row><cell1/><cell2>1</cell2><cell3>1</cell3><cell4/><cell5>2</cell5><cell6>2</cell6></row>
<row><cell1>1</cell1><cell2/><cell3/><cell4>2</cell4><cell5/><cell6/></row></sheet>
