{"id":134,"date":"2008-04-18T09:11:00","date_gmt":"2008-04-18T09:11:00","guid":{"rendered":"\/lisa\/post\/2008\/04\/18\/YAPS-(Yet-another-postscript)-on-Custom-Excel-for-RS.aspx"},"modified":"2008-04-18T09:11:00","modified_gmt":"2008-04-18T09:11:00","slug":"yaps-yet-another-postscript-on-custom-excel-for-rs","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2008\/04\/18\/yaps-yet-another-postscript-on-custom-excel-for-rs\/","title":{"rendered":"YAPS (Yet another postscript) on Custom Excel for RS"},"content":{"rendered":"

\nI really can't believe I'm revisiting this subject… I thought I was done with it<\/a> … 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<\/em> of SSML, for all kinds of custom reports.\n<\/p>\n

\nSo last time I said this \n<\/p>\n

\n[…] 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<\/em> tab names in Excel.  In this case, I used the following code to validate and change the tab names if necessary: <\/p>\n

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

… 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.\n<\/p>\n

\nGuess what?  It didn't turn out to be sufficient. \n<\/p>\n

\nThe 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<\/a> page as far as I can see. \n<\/p>\n

\nAlso before you ask I figured out the exact number by typing 1234512345123451234512345123451<\/strong> 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.\n<\/p>\n

\nSo, 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. \n<\/p>\n

\nI'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… \n<\/p>\n

\nIn any case, all-in-all, something like this should work for you.\n<\/p>\n

\n <<\/font><\/span>xsl:template <\/font><\/span>name=<\/font><\/span>"StripSheetName"<\/font><\/span>><\/span>
\n<\/font>
\n      <<\/font><\/span>xsl:param <\/font><\/span>name=<\/font><\/span>"item"<\/font><\/span>\/<\/font><\/span>><\/span>
\n<\/font>     <<\/font><\/span>xsl:param <\/font><\/span>name =<\/font><\/span>"secondTable"<\/font><\/span>\/<\/font><\/span>><\/p>\n

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

     <!– get rid of other disallowed characters, including
\n            single-quote (apostrophe) –><\/span>
\n<\/font>     <<\/font><\/span>xsl:variable <\/font><\/span>name=<\/font><\/span>"i2"<\/font><\/span> select=<\/font><\/span>"translate($i1,&quot;\/\\'&quot;,&quot;&quot;)"<\/font><\/span>\/<\/font><\/span>><\/span>
\n<\/font>
\n     <<\/font><\/span>xsl:variable <\/font><\/span>name=<\/font><\/span>"i3"<\/font><\/span> <\/font><\/span>><\/span>
\n<\/font>       <<\/font><\/span>xsl:choose<\/font><\/span>><\/span>
\n<\/font>         <<\/font><\/span>xsl:when <\/font><\/span>test=<\/font><\/span>"string-length($i2) &gt; 20"<\/font><\/span>>
\n         <!– truncate, leaving enough spare room for suffix,
\n                and add dots –><\/span>
\n<\/font>           <<\/font><\/span>xsl:value-of <\/font><\/span>select =<\/font><\/span>"concat(substring($i2,1,18),'..')"<\/font><\/span>\/<\/font><\/span>><\/span>
\n<\/font>         <<\/font><\/span>\/xsl:when<\/font><\/span>><\/span>
\n<\/font>         <<\/font><\/span>xsl:otherwise<\/font><\/span>><\/span>
\n<\/font>           <<\/font><\/span>xsl:value-of <\/font><\/span>select =<\/font><\/span>"$i2"<\/font><\/span>\/<\/font><\/span>><\/span>
\n<\/font>         <<\/font><\/span>\/xsl:otherwise<\/font><\/span>><\/span>
\n<\/font>       <<\/font><\/span>\/xsl:choose<\/font><\/span>><\/span>
\n<\/font>     <<\/font><\/span>\/xsl:variable<\/font><\/span>>
\n<\/span>
\n    <!– add appropriate suffix for this tab –>
\n<\/font>     <<\/font><\/span>xsl:choose<\/font><\/span>><\/span>
\n<\/font>       <<\/font><\/span>xsl:when <\/font><\/span>test =<\/font><\/span>"$secondTable"<\/font><\/span>><\/span>
\n<\/font>         <<\/font><\/span>xsl:value-of <\/font><\/span>select =<\/font><\/span>"concat($i3,'ByActivity')"<\/font><\/span>\/<\/font><\/span>><\/span>
\n<\/font>       <<\/font><\/span>\/xsl:when<\/font><\/span>><\/span>
\n<\/font>       <<\/font><\/span>xsl:otherwise<\/font><\/span>><\/span>
\n<\/font>         <<\/font><\/span>xsl:value-of <\/font><\/span>select =<\/font><\/span>"concat($i3,'ByCategory')"<\/font><\/span>\/<\/font><\/span>><\/span>
\n<\/font>       <<\/font><\/span>\/xsl:otherwise<\/font><\/span>><\/span>
\n<\/font>     <<\/font><\/span>\/xsl:choose<\/font><\/span>><\/span>
\n<\/font>
\n   <<\/font><\/span>\/xsl:template<\/font><\/span>><\/font><\/span>\n<\/p>\n

\n \n<\/p>\n

\nAlso 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. \n<\/p>\n

\nThe 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.\n<\/p>\n

\nEnjoy… and god bless Excel and all its peculiarities <sob>.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 lotRead more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5,10],"tags":[],"class_list":["post-134","post","type-post","status-publish","format-standard","hentry","category-asp-net","category-reporting","category-xml-xslt"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/134","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/comments?post=134"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/134\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=134"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=134"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=134"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}