Topic
8 replies Latest Post - ‏2014-08-11T16:53:30Z by llandale
bburk88
bburk88
4 Posts
ACCEPTED ANSWER

Pinned topic Import Excel with strikethrough

‏2014-08-07T15:25:26Z |

Hello, I am working on creating a dxl script to import an Excel spreadsheet into Doors.  This spreadsheet contains some cells with strikethrough text.  I do not want any strikethrough text copied into Doors.  How do I determine if a cell is formatted as strikethrough?  Or maybe there is a way to import rich text and pull the strikethrough property from that.  Any suggestions would be greatly appreciated.

  • llandale
    llandale
    2939 Posts
    ACCEPTED ANSWER

    Re: Import Excel with strikethrough

    ‏2014-08-07T17:16:03Z  in response to bburk88

    Does your script get an OLE "handle" on Excel, and then send Excel commands?  i,e, do you have something like this in the code:

    • OleAutoObj oleExcel = oleGetAutoObject("Excel.Application")

    If so, I think it is easier to get the raw text then get the rich text.  One of the cell "methods" does that.  I don't recall, but maybe methods "Text" as opposed to "Value".

    In any event, if you read the contents of a cell into a variable, you can strip out all rich text (including strikethroughs):

    • string TextRaw = plainText(TextRich)

    -Louie

    • bburk88
      bburk88
      4 Posts
      ACCEPTED ANSWER

      Re: Import Excel with strikethrough

      ‏2014-08-07T17:52:13Z  in response to llandale

      Hey Louie, 

      I was under the impression that the raw text is basically rich text with everything stripped out except the text.  

      • llandale
        llandale
        2939 Posts
        ACCEPTED ANSWER

        Re: Import Excel with strikethrough

        ‏2014-08-07T19:09:30Z  in response to bburk88

        Yes.  And tools can look at the RichText or HTML or whatever and tell the difference.  Doors lets you extract just the raw text from attributes>

        • Text = o."Object Text"

        Excell lets you do that also.  And above is a function to do it.

        -Louie

    • bburk88
      bburk88
      4 Posts
      ACCEPTED ANSWER

      Re: Import Excel with strikethrough

      ‏2014-08-08T17:29:09Z  in response to llandale

      I guess I wasn't very clear in my initial question.  I want to be able to know if a cell in Excel is formatted as StrikeThrough.  That way I can disregard any text that I find in that cell.  Is there a way to check the Cell properties to see if StrikeThrough is selected?

      • llandale
        llandale
        2939 Posts
        ACCEPTED ANSWER

        Re: Import Excel with strikethrough

        ‏2014-08-08T18:11:02Z  in response to bburk88

        I'm not good at OLE queries.  But the "Cell" would not have a "strikethrough" property.  You'd need to get a handle of the various "text" chunks and query the "Font" properties.  It may be easier to read the rich text contents and then query your dxl variable:

        • RichText rt
        • bool IsItal
        • for rt in Text do
        • {  IsItal = rt.italic
        •    if (IsItal) then this chunk is italized
        • }

        Here is a good place to go for Excel VBA; here we can see a list of Properties and Methods.  Other places you need to go to the Object first; which makes it tough if you  know you want something about "Italic" but don't know the Object to which it applies.

        http://msdn.microsoft.com/en-us/library/aa269683(v=office.10).aspx

        This is old Office-XP and won't have the few things added to Excel in the last several years.

        Sure enough we find "Italic" under "Properties"; see that it applies to "Font".  Lookup "Font" to see where it comes from ... backtrack up to a "Cell".

        -Louie

        • David_G_Bond
          David_G_Bond
          55 Posts
          ACCEPTED ANSWER

          Re: Import Excel with strikethrough

          ‏2014-08-08T19:22:55Z  in response to llandale

          All you have to do is get the font for the cell and see if it's underline property is set to true.  If objCell is the handle to the cell and objFont is defined as an OleAutoObj, and bUnderline declared as a bool:

          oleGet(objCell, "Font", objFont)

          oleGet(objFont, "Underline", bUnderline)

          if (!bUnderline) {

             ... process the cell

          }

  • bburk88
    bburk88
    4 Posts
    ACCEPTED ANSWER

    Re: Import Excel with strikethrough

    ‏2014-08-08T19:39:47Z  in response to bburk88

    Ok I finally figured it out. 

    clear(AutoArgs)
     put(AutoArgs, cellvalue)
     OleAutoObj objSelection = null
     OleAutoObj objFont = null
     bool StrikeFormat = null
      
     oleGet(objSheet, cExcelMethodRange, AutoArgs, objCell)
     oleMethod(objCell, cExcelMethodSelect)
     oleGet(objExcelApplication, cExcelPropertySelection, objSelection)
     print (oleGet(objSelection, cExcelPropertyFont, objFont))
     oleGet(objFont, cExcelPropertyStrikeThrough, StrikeFormat)
     print StrikeFormat"" "\n"

    • llandale
      llandale
      2939 Posts
      ACCEPTED ANSWER

      Re: Import Excel with strikethrough

      ‏2014-08-11T16:53:30Z  in response to bburk88

      Try your code to see what happens when just part of the text is strikethrough.