YAPS (Yet another postscript) on Custom Excel for RS

I really can't believe I'm revisiting this subject… I thought I was done with it … but this is worth saying because it's helpful to anybody generating SSML (Excel 2003 XML dialect) whether they are interested in RS or not.  I also can't believe I never figured this out before — because I generate a lot of SSML, for all kinds of custom reports.

So last time I said this 

[…] It also happens to be a situation, alluded to in my walkthrough, where you have to be careful that your dynamically-driven tab names are actually legal tab names in Excel.  In this case, I used the following code to validate and change the tab names if necessary:

<xsl:template name="StripSheetName">
      <xsl:param name="item"/>
      <xsl:variable name="i1">
         <xsl:value-of select="translate($item,'&quot;“”','')"/>
      </xsl:variable>
      <xsl:value-of select="translate($i1,&quot;/\'&quot;,&quot;&quot;)"/>
</xsl:template>

… looks a bit wierd and it may not turn out to be sufficient, but basically all I'm doing is taking out characters I know to be illegal.  If I find more, I'll add more.

Guess what?  It didn't turn out to be sufficient. 

The items I'm using for tab names in this report are particularly long and (who knew?) Excel has a tab name limit of 31 characters. Before you ask, it's not in the Excel specifications and limits page as far as I can see. 

Also before you ask I figured out the exact number by typing 1234512345123451234512345123451 into a tab.  That last "1" is the last character Excel will let you type.  If anybody has a better source of information, I'm all ears.

So, I smartened up the template you see above a little bit.  In this particular workbook, we're adding some verbiage onto the end of entries for each tab, which you'll see referenced; other than that, you may be able to use this puppy. 

I'm stripping blank spaces out to conserve some tablength room in this version; this isn't required, because spaces are allowed in tab names, but I was trying to save some room for more meaningful content.   You'll also notice a use of two dots ("..") wherever I'm truncating a tab name, and your strategy might be slightly different… 

In any case, all-in-all, something like this should work for you.

 <xsl:template name="StripSheetName">

      <xsl:param name="item"/>
     <xsl:param name ="secondTable"/>

     <!– get rid of smart quote and quote marks, not allowed
           and remove spaces too, to save room –>
      <xsl:variable name="i1">
         <xsl:value-of select="translate($item,' &quot;“”','')"/>
      </xsl:variable>

     <!– get rid of other disallowed characters, including
            single-quote (apostrophe) –>
     <xsl:variable name="i2" select="translate($i1,&quot;/\'&quot;,&quot;&quot;)"/>

     <xsl:variable name="i3" >
       <xsl:choose>
         <xsl:when test="string-length($i2) &gt; 20">
         <!– truncate, leaving enough spare room for suffix,
                and add dots –>
           <xsl:value-of select ="concat(substring($i2,1,18),'..')"/>
         </xsl:when>
         <xsl:otherwise>
           <xsl:value-of select ="$i2"/>
         </xsl:otherwise>
       </xsl:choose>
     </xsl:variable>

    <!– add appropriate suffix for this tab –>
     <xsl:choose>
       <xsl:when test ="$secondTable">
         <xsl:value-of select ="concat($i3,'ByActivity')"/>
       </xsl:when>
       <xsl:otherwise>
         <xsl:value-of select ="concat($i3,'ByCategory')"/>
       </xsl:otherwise>
     </xsl:choose>

   </xsl:template>

Also before you ask: what you see up there with escaped characters is correct (for example where you see &gt; (for greater than, in XSLT) that's not an error of HTML formatting. 

The parts that look especially weird come from explicitly specifying delimiters, such as quotation marks, as string literals, but this really does work, and you may really need to do it now and again.

Enjoy… and god bless Excel and all its peculiarities <sob>.