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>.

13 thoughts on “YAPS (Yet another postscript) on Custom Excel for RS

  1. Hi Lisa,
    Thank you for this passage, and I think there are some missing invalid chars in the utility. <g>
    I’ve added them as the following:
    <xsl:variable name=”i2″ select=”translate($i1,”/\’:*?[]”,””)”/>

    Hope it helps, thank you

  2. Thank you Tom! Like the length of the tab name, this isn’t documented anywhere that I could find. So I only find out about illegal characters by trial and error <g>. Thank you for saving me some trial and error!

  3. I have the same issue, the only difference is that I divided my report into multiple pages (using the insert page brak after rectangles) and I need to name the sheets in Excel.Any help will be appreciated.

  4. Probably not significant. I could have used any characters at all! The point is the number of characters, not the exact characters I chose. I find it easiest to count them if I type “12345”, that’s all…

  5. I have struggled with that in the past and I know how that can be. Glad to see that I am not the only one.

    Thank you for the revisiting of the topic. Maybe you can do so every 18 months or so.

    Christopher

  6. I always prefer excel for mathematical calculations and data consumption, because its functions are easy to understand and user- friendly too. If you want to know more functions of excel, number of tutorials are available online, you can easily download it. <a href=”http:// http://www.windows-7-icons.com “url fn”>windows-7-icons.com </a>

Leave a Reply to Tom Xie Cancel reply

Your email address will not be published. Required fields are marked *