在电子表格单元格中显示国家国旗(来自字母数字代码)

在电子表格单元格中显示国家国旗(来自字母数字代码)

我能否以某种方式欺骗 Excel 的自动格式化功能来显示 ISO 639 国家代码(alpha-2,如国家代码顶级域名表情符号) 作为标志?

我不太关心代码是否仍然显示或被隐藏。

与所有其他供应商不同,微软不渲染表情符号区域指示符号序列(RIS,U+1F1E6–FF)作为国旗符号,否则我会尝试相应地增加字符代码(取决于小写/大写)。(虽然我不确定这是否或如何起作用。)

例如:UNun变成

答案1

假设 A 列包含国家名称,B 列包含国家代码。让我们使用 C 列作为旗帜的超链接。旗帜将从旗帜百科在 中C1输入以下公式:

="https://flagpedia.net/data/flags/w702/" & LOWER(B1) & ".webp"

拖动以填充其他单元格。按ALT+F11打开 VBA 编辑器,在左侧插入一个模块,然后输入下面的代码。我修改了此代码回答获取 A 列的最后一行并使用 webp 文件(可以更改/删除

Option Explicit
Dim rng As Range
Dim cell As Range
Dim Filename As String

Sub URLPictureInsert()
    Dim theShape As Shape
    Dim xRg As Range
    Dim xCol, lRow As Long
    On Error Resume Next
        
    lRow = Cells(Rows.Count, 1).End(xlUp).Row 'Get the last row of column A
    
    Set rng = ActiveSheet.Range("C1:C" & lRow)   'Set the range of the URLs
    Dim i As Integer
    i = 0
    
    For Each cell In rng
    
        Filename = cell
        If InStr(LCase(Filename), "webp") > 0 Then   '<--- ONLY USES webp
            ActiveSheet.Pictures.Insert(Filename).Select
            Set theShape = Selection.ShapeRange.Item(1)
            If theShape Is Nothing Then GoTo isnill
            xCol = cell.Column + 1
            Set xRg = Cells(cell.Row, xCol)
           ' xRg.Clear
            With theShape
                .LockAspectRatio = msoFalse
                .Width = 60
                .Height = 32
                If i = 0 Then
                    .Top = xRg.Top + (xRg.Height - .Height) 'Adjust the top location of the first row's image --Should be re-adjust
                    i = i + 1
                Else
                    .Top = xRg.Top + 10 + (xRg.Height - .Height) / 2 'Adjust the top location of subsequent rows' images --Should be re-adjust
                End If
                .Left = xRg.Left + (xRg.Width - .Width) / 2
            End With
            
            xRg.RowHeight = theShape.Height  'Adjust row height to fit the image
            xRg.ColumnWidth = 10             'Adjust the column height to fit the image
            
isnill:
            Set theShape = Nothing
            Range("A2").Select
        End If
    Next
    Application.ScreenUpdating = True

    Debug.Print "Done " & Now

End Sub

当代码运行完毕后,标志将显示在 D 列的 URL 旁边。您可能需要重写在工作表上插入图像的代码部分。我重新调整了以适合工作表的高度和宽度。

在此处输入图片描述

相关内容