YAPS (Yet another postscript) on Custom Excel for RS

by Lisa Nicholls Fri, April 18 2008 15:11

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

Tags:

ASP.NET | Reporting | XML/XSLT

Comments (13) -

11/5/2008 12:48:32 AM #

Tom Xie

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,&quot;/\':*?[]&quot;,&quot;&quot;)"/>

Hope it helps, thank you

Tom Xie People's Republic of China

11/5/2008 12:50:17 AM #

&gt;L&lt;

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!

>L< United States

11/22/2009 6:37:45 AM #

excel expert

Excel 2003 XML dialect in which application is it suitable?

excel expert United States

11/23/2009 12:28:46 PM #

book publishers uk

the more YAPS the better in my mind. They all bring slightly different things to the table

book publishers uk United Kingdom

12/16/2009 1:33:26 AM #

denver dui lawyer

I never hear about YAPS. YAPS (Yet another postscript) on Custom Excel for RS  - I want to try out your code... Smile

denver dui lawyer United States

2/24/2010 4:31:10 AM #

Car Rental &amp; Car Hire Gold Coast

Well, I never know those SSML codes and also how and where to use it.  Better if you provided some more description for the innocent people.

Car Rental & Car Hire Gold Coast India

2/25/2010 3:59:10 AM #

sleep dentist

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.

sleep dentist India

2/25/2010 11:22:15 AM #

&gt;L&lt;

GamesRadar.smith, if you're real, the original post in this series does exactly that.

>L< United States

2/28/2010 10:14:49 AM #

Karen Norman

I am fairly new to these excel scripts and find it fascinating, I now need to go and give it a go, thanks for some useful insight.

Karen Norman United States

7/14/2010 5:53:16 AM #

piano tutorial

it worked for me Lisa with the little change Tom suggested, thanks.

piano tutorial United Kingdom

9/6/2010 10:02:37 AM #

&gt;L&lt;

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

>L< United States

7/7/2011 5:44:04 PM #

Christopher

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

Christopher United States

10/5/2012 6:08:29 AM #

rockstunner

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:// www.windows-7-icons.com "url fn">windows-7-icons.com </a>

rockstunner United States

Pingbacks and trackbacks (3)+