CODEWITHSUNDEEP
pythonms-officepywin32
frank johnson
frank johnson

Reputation: 153

Hyperlink will not show Display proper text

I am trying to add a hyperlink to a cell to open a folder the code below makes the hyperlink in the proper cell and is when clicked redirects to the proper folder but it does not display the text provided it instead displays the folder name e.g. (:C:\Documents and Settings\abulle\Desktop\Python-Stuff\Spec-Load\Formatted\) instead of 'Folder'

sheet.Hyperlinks.Add( Anchor = sheet.Cells(7,21), Address = "C:\\Python-Stuff\\Spec-Load\\Formatted\\" , TextToDisplay = "Folder")

Upvotes: 2

Views: 1026

Answers (2)

Максим Нагуслаев
Максим Нагуслаев

Reputation: 1

try this

 def addHyperlink(self, uri_1, summa_, sheetId, rowIndex, colIndex):
    requests = []
    requests.append({
        "updateCells": {
            "rows": [{
                "values": [{
                    'userEnteredValue': {'numberValue': floatG(summa_)},  #
                    'effectiveValue': {'numberValue': floatG(summa_)},
                    'formattedValue': "р." + summa_,
                    'userEnteredFormat': {
                        'numberFormat': {'type': 'NUMBER', 'pattern': '[$р.-419]#,##0.00'},
                        'backgroundColor': {'red': 1, 'green': 1, 'blue': 0.6}, 'borders': {
                            'top': {
                                'style': 'SOLID', 'width': 1, 'color': {}, 'colorStyle': {'rgbColor': {}}},
                            'bottom': {
                                'style': 'SOLID', 'width': 1, 'color': {}, 'colorStyle': {'rgbColor': {}}},
                            'left': {
                                'style': 'SOLID', 'width': 1, 'color': {}, 'colorStyle': {'rgbColor': {}}},
                            'right': {
                                'style': 'SOLID', 'width': 1, 'color': {}, 'colorStyle': {'rgbColor': {}}}},
                        'horizontalAlignment': 'RIGHT', 'verticalAlignment': 'BOTTOM',
                        'textFormat': {
                            'foregroundColor': {'red': 0.06666667, 'green': 0.33333334, 'blue': 0.8},
                            'fontFamily': 'Arial', 'underline': True,
                            'foregroundColorStyle': {
                                'rgbColor': {'red': 0.06666667, 'green': 0.33333334, 'blue': 0.8}},
                            'link': {'uri': uri_1}
                        },
                        'hyperlinkDisplayType': 'LINKED',
                        'backgroundColorStyle': {'rgbColor': {'red': 1, 'green': 1, 'blue': 0.6}}
                    }
                    , 'hyperlink': uri_1, 'note': 'макс',
                }
                ]
            }], "fields": "userEnteredFormat(numberFormat,backgroundColor,horizontalAlignment,verticalAlignment,textFormat)", "start": {
                "sheetId": sheetId, "rowIndex": rowIndex, "columnIndex": colIndex}}})
    body = {
        "requests": requests}
    request = self.service.spreadsheets().batchUpdate(spreadsheetId=self.spreadsheetId, body=body)
    return request.execute()

Upvotes: 0

Pat Knight
Pat Knight

Reputation: 101

I have a stopgap answer, a kludge. I don't have time at the moment to find a better answer. If this was part of my day job I'd spend the time to find out what's going on.

I've had the same issue (Excel shows the link address as the cell text instead of the TextToDisplay value supplied on Hyperlinks.Add())

My code works under unit test when invoked by running the Python 2.7 interpreter - the value of the 'TextToDisplay' argument is displayed in the cell. The 'production' code (built using py2exe) displays the hyperlink. I'll find out why some day (this is low background work.)

Hyperlinks.Add returns the Hyperlink object it just added. The workaround is to examine the TextToDisplay property of that object - if it's not what I want, I assign the correct value to it.

link = sheet.Hyperlinks.Add( Anchor = sheet.Cells(7,21),
                             Address = u"C:\\Python-Stuff\\Spec-Load\\Formatted\\" ,
                             TextToDisplay = u"Folder")
if link.TextToDisplay != u"Folder":
    link.TextToDisplay = u"Folder" # kludge

Upvotes: 3

Related Questions