8.24 VBA做的省市县三级行政的级联下拉列表框

[vieyahn2017]

VBA做的省市县三级行政的级联下拉列表框

[vieyahn2017]

先准备这样的行政号码对照表

北京市	110000
		110000	市辖区	110100
					110100	东城区	110101
					110100	西城区	110102
					110100	朝阳区	110105
					110100	丰台区	110106
					110100	石景山区	110107
					110100	海淀区	110108
					110100	门头沟区	110109
					110100	房山区	110111
					110100	通州区	110112
					110100	顺义区	110113
					110100	昌平区	110114
					110100	大兴区	110115
					110100	怀柔区	110116
					110100	平谷区	110117
					110100	密云区	110118
					110100	延庆区	110119
天津市	120000
		120000	市辖区	120100
					120100	和平区	120101
					120100	河东区	120102
					120100	河西区	120103
					120100	南开区	120104
					120100	河北区	120105
					120100	红桥区	120106
					120100	东丽区	120110
					120100	西青区	120111
					120100	津南区	120112
					120100	北辰区	120113
					120100	武清区	120114
					120100	宝坻区	120115
					120100	滨海新区	120116
					120100	宁河区	120117
					120100	静海区	120118
					120100	蓟州区	120119
河北省	130000
		130000	石家庄市	130100
					130100	长安区	130102
					130100	桥西区	130104
					130100	新华区	130105
					130100	井陉矿区	130107
					130100	裕华区	130108
					130100	藁城区	130109
					130100	鹿泉区	130110
					130100	栾城区	130111
					130100	井陉县	130121
					130100	正定县	130123
					130100	行唐县	130125

内容格式其实是：

北京市,110000
,,110000,市辖区,110100
,,,,,110100,东城区,110101
,,,,,110100,西城区,110102
,,,,,110100,朝阳区,110105
,,,,,110100,丰台区,110106
,,,,,110100,石景山区,110107
,,,,,110100,海淀区,110108
,,,,,110100,门头沟区,110109
,,,,,110100,房山区,110111
,,,,,110100,通州区,110112
,,,,,110100,顺义区,110113
,,,,,110100,昌平区,110114
,,,,,110100,大兴区,110115
,,,,,110100,怀柔区,110116
,,,,,110100,平谷区,110117
,,,,,110100,密云区,110118
,,,,,110100,延庆区,110119
天津市,120000
,,120000,市辖区,120100
,,,,,120100,和平区,120101
,,,,,120100,河东区,120102
,,,,,120100,河西区,120103
,,,,,120100,南开区,120104
,,,,,120100,河北区,120105
,,,,,120100,红桥区,120106
,,,,,120100,东丽区,120110
,,,,,120100,西青区,120111
,,,,,120100,津南区,120112
,,,,,120100,北辰区,120113
,,,,,120100,武清区,120114
,,,,,120100,宝坻区,120115
,,,,,120100,滨海新区,120116
,,,,,120100,宁河区,120117
,,,,,120100,静海区,120118
,,,,,120100,蓟州区,120119
河北省,130000
,,130000,石家庄市,130100
,,,,,130100,长安区,130102
,,,,,130100,桥西区,130104
,,,,,130100,新华区,130105

[vieyahn2017]

基于下面的json转来的

{
    "data": [
        {
            "id": "110000",
            "name": "北京市",
            "children": [
                {
                    "id": "110100",
                    "name": "市辖区",
                    "children": [
                        {
                            "id": "110101",
                            "name": "东城区"
                        },
                        {
                            "id": "110102",
                            "name": "西城区"
                        },
                        {
                            "id": "110105",
                            "name": "朝阳区"
                        },
                        {
                            "id": "110106",
                            "name": "丰台区"
                        },
                        {
                            "id": "110107",
                            "name": "石景山区"
                        },
                        {
                            "id": "110108",
                            "name": "海淀区"
                        },
                        {
                            "id": "110109",
                            "name": "门头沟区"
                        },
                        {
                            "id": "110111",
                            "name": "房山区"
                        },
                        {
                            "id": "110112",
                            "name": "通州区"
                        },
                        {
                            "id": "110113",
                            "name": "顺义区"
                        },
                        {
                            "id": "110114",
                            "name": "昌平区"
                        },
                        {
                            "id": "110115",
                            "name": "大兴区"
                        },
                        {
                            "id": "110116",
                            "name": "怀柔区"
                        },
                        {
                            "id": "110117",
                            "name": "平谷区"
                        },
                        {
                            "id": "110118",
                            "name": "密云区"
                        },
                        {
                            "id": "110119",
                            "name": "延庆区"
                        }
                    ]
                }
            ]
        },
        {
            "id": "120000",
            "name": "天津市",
            "children": [

...

}

[vieyahn2017]

转换脚本

# _*_ coding:utf-8 _*_

import json
import sys
reload(sys)
sys.setdefaultencoding('utf8')

def main():
    lines = []
    with open('province.json', 'r')as fp:
        json_data = json.load(fp)
        provinces_data = json_data['data']
        for item in provinces_data:
            print('省代码：', item['id'])
            print('省名称：', item['name'])
            lines.append(','.join([item['name'], item['id']]))
            if item.get('children'):
                cities_data = item['children']
                for item2 in cities_data:
                    print('市代码：', item2['id'])
                    print('市名称：', item2['name'])
                    lines.append(','.join(["", "", item['id'], item2['name'], item2['id']]))
                    if item2.get('children'):
                        zones_data = item2['children']
                        for item3 in zones_data:
                            # print('县区名称：', item3['name'])
                            # print('县区代码：', item3['name'])
                            lines.append(','.join(["", "", "", "", "", item2['id'], item3['name'], item3['id']]))

    print(lines)
    print(len(lines))
    file_new = open("chinazone.csv", 'w')
    for line in lines:
        file_new.write(line + '\n')
    file_new.close()

main()

[vieyahn2017]

vba 增加一个窗体，

上面增加3个Listbox 以及2个按钮（确定 取消）

[vieyahn2017]

vba代码

Public pronvincesDict
Public citiesDict
Public zonesDict

Private Sub UserForm_Initialize()
    InitializeGlobalVariables

    Set pronvincesDict = CreateObject("Scripting.Dictionary")
    Set citiesDict = CreateObject("Scripting.Dictionary")
    Set zonesDict = CreateObject("Scripting.Dictionary")

    With Sheets("PlaceCode")
        g_placeCodeRowLines = .UsedRange.Rows.Count
        For i = 1 To g_placeCodeRowLines
            If Not .Cells(i, 1).Value = "" Then
                pronvincesDict.Add .Cells(i, 1).Value, .Cells(i, 2).Value
            End If
        Next
    End With

    ' ListBox3.List = Array("aa", "bbb", "wewe")
    ListBox3.List = pronvincesDict.keys

End Sub

Private Sub CommandButton1_Click()

    '如果没选择省
    If IsNull(ListBox3.Value) Then
        ListBox3.SetFocus
        Exit Sub
    End If

    If citiesDict.Count = 0 Then  ' 台湾香港澳门，只有一级行政区，直接选择退出
        provinceName = ListBox3.Value
        g_selectedPlaceCodeName = provinceName
        g_selectedPlaceCode = pronvincesDict(provinceName)

    Else ' 大陆三级行政区，必须选择到第三级
        If IsNull(ListBox4.Value) Then
            ListBox4.SetFocus
            Exit Sub
        End If
        If IsNull(ListBox5.Value) Then
            ListBox5.SetFocus
            Exit Sub
        End If

        provinceName = ListBox3.Value
        cityName = ListBox4.Value
        zoneName = ListBox5.Value
        g_selectedPlaceCodeName = provinceName & "/" & cityName & "/" & zoneName
        g_selectedPlaceCode = zonesDict(zoneName)

    End If

    ' MsgBox g_selectedPlaceCode
    UserForm1.Hide
End Sub

Private Sub CommandButton2_Click()
    'If g_selectedPlaceCode = 0 Then
        g_selectedPlaceCodeName = "请选择"
    'End If
    UserForm1.Hide
End Sub

Private Sub ListBox3_Click()

End Sub

Private Sub ListBox3_Change()

    SearchCitiesValues ListBox3.Value

    ListBox4.List = citiesDict.keys
    ListBox5.Clear

End Sub

Private Sub ListBox4_Click()

End Sub

Private Sub ListBox4_Change()
    SearchZonesValues ListBox4.Value
    ListBox5.List = zonesDict.keys
End Sub

Private Sub ListBox5_Change()

End Sub

Private Sub ListBox5_Click()

End Sub

Public Sub SearchCitiesValues(provinceName)
    provinceCode = pronvincesDict(provinceName)
    citiesDict.RemoveAll

    With Sheets("PlaceCode")
        For i = 1 To g_placeCodeRowLines
            If .Cells(i, 3).Value = provinceCode Then
                If Not .Cells(i, 4).Value = "" Then
                    citiesDict.Add .Cells(i, 4).Value, .Cells(i, 5).Value
                End If
            End If
        Next
    End With

End Sub

Public Sub SearchZonesValues(cityName)
    cityCode = citiesDict(cityName)
    zonesDict.RemoveAll

    With Sheets("PlaceCode")
        For i = 1 To g_placeCodeRowLines
            If .Cells(i, 6).Value = cityCode Then
                If Not .Cells(i, 7).Value = "" Then
                    zonesDict.Add .Cells(i, 7).Value, .Cells(i, 8).Value
                End If
            End If
        Next
    End With

End Sub

[vieyahn2017]

其中 定义于公共模块的代码

Public g_placeCodeRowLines As Integer

Public g_selectedPlaceCode As String
Public g_selectedPlaceCodeName As String

Public Sub InitializeGlobalVariables()
    g_selectedPlaceCode = 0
    g_placeCodeRowLines = Sheets("PlaceCode").UsedRange.Rows.Count

End Sub

[vieyahn2017]

sheet代码

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    '行政区划
    If ActiveCell.Column = 5 And ActiveCell.Row = 5 Then
        UserForm1.Show
        ActiveCell.Value = g_selectedPlaceCodeName
        Exit Sub
    End If

End Sub