December | 2014 | Thomas' Work Space

Yet another couple of glitches solved in setting up Outlook 2013 with Gmail IMAP for GoogleApps

2014/12/19 plagwitz Leave a comment

I googled and followed this useful guide. However, a couple of glitches remained:
Connection to Incoming mail server fails if you really just put your username:
1. put instead of username@companygoogledomain.com:
Test send of email message through outgoing server fails if you just followed instructions:
2. Click on button: “More” / tab: “Outgoing Server” / do check box “My outgoing server requires authentication ” and log on (I now prefer to be explicit, after the initial mixup of usernames for incoming and outgoing), enter your full user@compgoogledomain.com and password. No need to enable SPA, though.
Viola (and quousque tandem…, sigh ).

Categories: e-infrastructure, office-software Tags: 2013, gmail, imap, outlook

A little addendum about data types to the +N(“comment”) in-cell formula comment trick in Excel

2014/12/18 plagwitz Leave a comment

You are relying with this well-documented trick on the fact that addition of 0 will altering a numeric value.
However, what is not mentioned: not all work in Excel involves the number data type.
1. Try adding your comment or zero to a string data type, and you will get a #VALUE error.
2. Boolean, however, works also, since – as you can see when you have Excel evaluate your formula – if you try adding 0 to a Boolean data type, Excel casts the prior Boolean FALSE to 0 or TRUE to 1, and casts the result of your addition (of 0 = nothing) back to the same initial Boolean for further evaluation.
3. So just put your +N()-comments in those part of your formula that involve either number or Boolean data type, and avoid adding to strings, for example:

=IF(
    OR(
        ISBLANK(
            [Related issues]
        ),
        (            ISERR(
                FIND(
                    “#“,
                    [Related issues]
                )
            ) )
    ) +
    N(
        “do nothing if no related task in col:related issues“
    ),
    ““,
    IF(
        NOT(
            ISERR(
                FIND(
                    “,“,
                    [Related issues]
                )
            )
        ) +
        N(
            “do nothing if no 2nd value in col:related issues“
        ),
        CONCATENATE(
            MID(
                MID(
                    [Related issues],
                    FIND(
                        “,“,
                        [Related issues]
                    ) + 2,
                    LEN(
                        [Related issues]
                    ) –
                    FIND(
                        “,“,
                        [Related issues]
                    ) + 1 +
                    N(
                        “extracted the 2nd value from col:related issues“
                    )
                ),
                FIND(
                    “#“,
                    MID(
                        [Related issues],
                        FIND(
                            “,“,
                            [Related issues]
                        ) + 2,
                        LEN(
                            [Related issues]
                        ) –
                        FIND(
                            “,“,
                            [Related issues]
                        ) + 1
                    )
                ) + 1,
                4
            ) +
            N(
                “extracted the 4# of id of related issue“
            ),
            MID(
                MID(
                    [Relatedissues],
                    FIND(
                        “,“,
                        [Relatedissues]
                    ) + 2,
                    LEN(
                        [Relatedissues]
                    ) –
                    FIND(
                        “,“,
                        [Relatedissues]
                    ) + 1
                ),
                FIND(
                    “ “,
                    MID(
                        [Related issues],
                        FIND(
                            “,“,
                            [Related issues]
                        ) + 2,
                        LEN(
                            [Related issues]
                        ) –
                        FIND(
                            “,“,
                            [Related issues]
                        ) + 1
                    )
                ) + 1,
                2 +
                N(
                    “appended to or from to the 4# of id of related issue“
                )
            )
        ),
        ““
    )
)

This splits, extracts and copies values from a none to multi-value column for better sortability:

Categories: e-infrastructure, office-software Tags: comments, MS-Excel, worksheet-formulas

Fun with .docx to .html transforms by means of HtmlConverter from PowerTools for Open XML

2014/12/15 plagwitz Leave a comment

The transform is FOSS and platform-independent:
1. It neither requires Office nor Windows (The OpenXML SDK runs on Linux via Mono on the server.
2. However, the most recent installment of Powertools for OpenXML, a high-level API to the OpenXML SDK, comes with a PowerShell interface (benefit: no Visual studio requirement).
Valuable features of the transform, among many other things, are:
1. HtmlConverter is able to translate MS-Word styles into CSS (insofar needed – my code style has “No proofing” set, however, this cannot be implemented on the WWW), so the layout is preserved as designed, but w/o need for inline formatting:

        span.pt-StrongEmphasis-000052 {
            font-family: Calibri;
            font-size: 11pt;
            font-style: italic;
            font-weight: bold;
            margin: 0in;
            padding: 0in;
        }

        span.pt-lowCodeConsoleChar0 {
            color: #FFFFFF;
            background: #000000;
            font-family: Consolas;
            font-size: 10pt;
            font-weight: normal;
            margin: 0in;
            padding: 0in;
        }

     &lt;h3 dir=&quot;ltr&quot; class=&quot;pt-000040&quot;&gt;
            &lt;span class=&quot;pt-000041&quot;&gt;2.2.1&lt;/span&gt;&lt;span class=&quot;pt-000042&quot;&gt;&lt;span class=&quot;pt-000043&quot;&gt;&amp;nbsp;&lt;/span&gt;&lt;/span&gt;&lt;span class=&quot;pt-Heading2Char&quot;&gt;&lt;b&gt;References&lt;/b&gt;&lt;/span&gt;
          &lt;/h3&gt;

          &lt;p dir=&quot;ltr&quot; class=&quot;pt-BodyText&quot;&gt;
            &lt;span class=&quot;pt-DefaultParagraphFont-000003&quot;&gt;&lt;br /&gt;
            &amp;lrm;&lt;/span&gt;&lt;span class=&quot;pt-000000&quot;&gt;&amp;nbsp;&lt;/span&gt;
          &lt;/p&gt;

          &lt;h1 dir=&quot;ltr&quot; class=&quot;pt-000006&quot;&gt;
            &lt;span class=&quot;pt-000007&quot;&gt;&lt;b&gt;3&lt;/b&gt;&lt;/span&gt;&lt;span class=&quot;pt-000008&quot;&gt;&lt;b&gt;&lt;span class=&quot;pt-000009&quot;&gt;&amp;nbsp;&lt;/span&gt;&lt;/b&gt;&lt;/span&gt;&lt;span class=&quot;pt-Heading1Char&quot;&gt;&lt;b&gt;Introduction&lt;/b&gt;&lt;/span&gt;
          &lt;/h1&gt;

          &lt;h2 dir=&quot;ltr&quot; class=&quot;pt-000018&quot;&gt;
            &lt;span class=&quot;pt-000019&quot;&gt;3.1&lt;/span&gt;&lt;span class=&quot;pt-000020&quot;&gt;&lt;span class=&quot;pt-000021&quot;&gt;&amp;nbsp;&lt;/span&gt;&lt;/span&gt;&lt;span class=&quot;pt-Heading2Char&quot;&gt;&lt;b&gt;Purpose of Document&lt;/b&gt;&lt;/span&gt;
          &lt;/h2&gt;

There are many more options that I have not yet tried:

            SimplifyMarkupSettings simplifyMarkupSettings = new SimplifyMarkupSettings
            {
                RemoveComments = true,
                RemoveContentControls = true,
                RemoveEndAndFootNotes = true,
                RemoveFieldCodes = false,
                RemoveLastRenderedPageBreak = true,
                RemovePermissions = true,
                RemoveProof = true,
                RemoveRsidInfo = true,
                RemoveSmartTags = true,
                RemoveSoftHyphens = true,
                RemoveGoBackBookmark = true,
                ReplaceTabsWithSpaces = false,
            };
            MarkupSimplifier.SimplifyMarkup(wordDoc, simplifyMarkupSettings);

            FormattingAssemblerSettings formattingAssemblerSettings = new FormattingAssemblerSettings
            {
                RemoveStyleNamesFromParagraphAndRunProperties = false,
                ClearStyles = false,
                RestrictToSupportedLanguages = htmlConverterSettings.RestrictToSupportedLanguages,
                RestrictToSupportedNumberingFormats = htmlConverterSettings.RestrictToSupportedNumberingFormats,
                CreateHtmlConverterAnnotationAttributes = true,
                OrderElementsPerStandard = false,
                ListItemRetrieverSettings = new ListItemRetrieverSettings()
                {
                    ListItemTextImplementations = htmlConverterSettings.ListItemImplementations,
                },
            };

One would really wish there was a way to get such HTML cleaned up automatically (ouch!):

               &lt;span class=&quot;pt-DefaultParagraphFont-000006&quot;&gt;M&lt;/span&gt;
                &lt;span class=&quot;pt-DefaultParagraphFont-000006&quot;&gt;anaged requirements for system integration&amp;nbsp;&lt;/span&gt;
                &lt;span class=&quot;pt-DefaultParagraphFont-000006&quot;&gt;of Center&lt;/span&gt;
                &lt;span class=&quot;pt-DefaultParagraphFont-000006&quot;&gt;&amp;nbsp;&lt;/span&gt;
                &lt;span class=&quot;pt-DefaultParagraphFont-000006&quot;&gt;software&amp;nbsp;&lt;/span&gt;
                &lt;span class=&quot;pt-DefaultParagraphFont-000006&quot;&gt;with&amp;nbsp;&lt;/span&gt;
                &lt;span class=&quot;pt-DefaultParagraphFont-000006&quot;&gt;iLearning&lt;/span&gt;
                &lt;span class=&quot;pt-DefaultParagraphFont-000006&quot;&gt;&amp;nbsp;and with content production and management (BPD). To mitigate lack of integration of $50k LMS software investment into departmental workflow&lt;/span&gt;
                &lt;span class=&quot;pt-DefaultParagraphFont-000006&quot;&gt;,&lt;/span&gt;
                &lt;span class=&quot;pt-DefaultParagraphFont-000006&quot;&gt;&amp;nbsp;&lt;/span&gt;
                &lt;span class=&quot;pt-DefaultParagraphFont-000006&quot;&gt;developed&amp;nbsp;&lt;/span&gt;
                &lt;span class=&quot;pt-DefaultParagraphFont-000006&quot;&gt;and documented&amp;nbsp;&lt;/span&gt;
                &lt;span class=&quot;pt-DefaultParagraphFont-000006&quot;&gt;software to automate&lt;/span&gt;
                &lt;span class=&quot;pt-DefaultParagraphFont-000006&quot;&gt;&amp;nbsp;creation of 4K+ user accounts p.a., 30K+ learning documents and 100K+ interactive content paths in LMS.&lt;/span&gt;

There are also much more serious conversion errors:

MS-Word displays a plain text content control and a repeating section content control within a table, containing one Combobox and one plain text content control per row, perfectly:

Convert-DocxToHtml gobbles the content completely (and so does Google Docs Preview):

The underlying HTML has just a blank table under each heading:

    &lt;div class=&quot;pt-000001&quot;&gt;
        &lt;p dir=&quot;ltr&quot; class=&quot;pt-qiCVHeading1&quot;&gt;
          &lt;span class=&quot;pt-DefaultParagraphFont-000002&quot;&gt;Profile&lt;/span&gt;
        &lt;/p&gt;
      &lt;/div&gt;
      &lt;div align=&quot;left&quot;&gt;
        &lt;table border=&quot;1&quot; cellspacing=&quot;0&quot; cellpadding=&quot;0&quot; dir=&quot;ltr&quot; class=&quot;pt-000003&quot; /&gt;
      &lt;/div&gt;
      &lt;div class=&quot;pt-000001&quot;&gt;
        &lt;p dir=&quot;ltr&quot; class=&quot;pt-qiCVHeading1&quot;&gt;
          &lt;span class=&quot;pt-DefaultParagraphFont-000002&quot;&gt;Technologies&lt;/span&gt;
        &lt;/p&gt;
      &lt;/div&gt;
      &lt;div align=&quot;left&quot;&gt;
        &lt;table border=&quot;1&quot; cellspacing=&quot;0&quot; cellpadding=&quot;0&quot; dir=&quot;ltr&quot; class=&quot;pt-000003&quot; /&gt;
      &lt;/div&gt;

MS-Word shows:Yet need to look in to the underlying XML to see whether the .docx is to blame for that…
But HtmlConverter output in IE or Firefox: The underlying HTML reveals that the css does not get applied in the right place:

 	&lt;tr&gt;
                &lt;td class=&quot;pt-000079&quot;&gt;
                  &lt;p dir=&quot;ltr&quot; class=&quot;pt-BodyTextSmall&quot;&gt;
                    &lt;span class=&quot;pt-BodyTextSmallChar-000081&quot;&gt;AD&lt;/span&gt;
                  &lt;/p&gt;
                &lt;/td&gt;
                &lt;td colspan=&quot;2&quot; class=&quot;pt-000079&quot;&gt;
                  &lt;p dir=&quot;ltr&quot; class=&quot;pt-BodyTextSmall&quot;&gt;
                    &lt;span class=&quot;pt-BodyTextSmallChar-000081&quot;&gt;Active Driector, Microsfot&amp;rsquo;s directory implementation.&lt;/span&gt;
                  &lt;/p&gt;
                &lt;/td&gt;
              &lt;/tr&gt;

              &lt;tr&gt;
                &lt;td class=&quot;pt-000086&quot;&gt;
                  &lt;p dir=&quot;ltr&quot; class=&quot;pt-BodyTextSmall&quot;&gt;
                    &lt;span class=&quot;pt-000085&quot;&gt;&amp;nbsp;&lt;/span&gt;
                  &lt;/p&gt;
                &lt;/td&gt;
                &lt;td colspan=&quot;2&quot; class=&quot;pt-000086&quot;&gt;
                  &lt;p dir=&quot;ltr&quot; class=&quot;pt-BodyTextSmall&quot;&gt;
                    &lt;span class=&quot;pt-000085&quot;&gt;&amp;nbsp;&lt;/span&gt;
                  &lt;/p&gt;
                &lt;/td&gt;
              &lt;/tr&gt;

One could imagine MS-Word acting less strictly than OpenXML PowerTools:Convert-DocxToHtml, like a web-browser’s parser tolerates and displays bad HTML. However, not only would need to be justified how MS-Word can also serve as the originating HTML WYSIWYG editor. The OpenXML PowerTools:Get-OpenXmlValidationErrors for both of the above documents does not seem to find any OpenXML errors that could explain the bad conversion (other than dozens of Sch_UndeclaredAttribute errors (Version-related? Not sure how this could be) , there is only a Pkg_PartIsNotAllowed relating to a glossary).

Also yet to do:
1. When (not always!) does my page title end up as empty?
```
&lt;title&gt;&lt;/title&gt;
```
2. Defaults to doctype xhtml, not html(5).
Done:
```
openXml\OxPt\OxPtCmdlets\OxPtHelper.cs:var htmlString = html.ToString(SaveOptions.None); // trp: requesting pretty-printing, was:html.ToString(SaveOptions.DisableFormatting);
```

Categories: e-infrastructure, service-is-documenting Tags: Convert-DocxToHtml, Get-OpenXmlValidationErrors, MS-Word, openxml, powertools-for-openxml, single-sourcing, XML

How to watch a task you did not create in Redmine

2014/12/15 plagwitz Leave a comment

“Watching” tasks, i.e. receiving notifications of task updates, is the default for tasks – if you created them.
“Watching” can also be turned on per task. Go to “issues”, , click on the first task.
In the upper right of the task page, click “Watch”,
Then click “next” to cycle through all tasks. A bit tedious even for small projects – is there a way to default to “watching all”. It does not come with the roles (I tried):

Categories: service-is-project-managing Tags: notifications, redmine, watching

Thomas' Work Space

Archive

Yet another couple of glitches solved in setting up Outlook 2013 with Gmail IMAP for GoogleApps

A little addendum about data types to the +N(“comment”) in-cell formula comment trick in Excel

Fun with .docx to .html transforms by means of HtmlConverter from PowerTools for Open XML

How to watch a task you did not create in Redmine

Blog Stats

Top Posts & Pages

Top Clicks

Categories

Email Subscription

Archives

Top