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

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.

  • bburk88
    bburk88
    4 Posts
    ACCEPTED ANSWER

    Re: Import Excel with strikethrough

    ‏2014-08-08T19:39:47Z  

    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
    2972 Posts

    Re: Import Excel with strikethrough

    ‏2014-08-07T17:16:03Z  

    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

    Re: Import Excel with strikethrough

    ‏2014-08-07T17:52:13Z  
    • llandale
    • ‏2014-08-07T17:16:03Z

    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

    Hey Louie, 

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

  • llandale
    llandale
    2972 Posts

    Re: Import Excel with strikethrough

    ‏2014-08-07T19:09:30Z  
    • bburk88
    • ‏2014-08-07T17:52:13Z

    Hey Louie, 

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

    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

    Re: Import Excel with strikethrough

    ‏2014-08-08T17:29:09Z  
    • llandale
    • ‏2014-08-07T17:16:03Z

    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

    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
    2972 Posts

    Re: Import Excel with strikethrough

    ‏2014-08-08T18:11:02Z  
    • bburk88
    • ‏2014-08-08T17:29:09Z

    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?

    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

    Re: Import Excel with strikethrough

    ‏2014-08-08T19:22:55Z  
    • llandale
    • ‏2014-08-08T18:11:02Z

    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

    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

    Re: Import Excel with strikethrough

    ‏2014-08-08T19:39:47Z  

    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
    2972 Posts

    Re: Import Excel with strikethrough

    ‏2014-08-11T16:53:30Z  
    • bburk88
    • ‏2014-08-08T19:39:47Z

    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"

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