response = urllib.request.urlopen(url)

    src = response.read().decode('utf-8')

def get_data(url, pattern):

    response = urllib.request.urlopen(url)

    src = response.read().decode('utf-8')

    results = re.findall(pattern, src)

    return results

def fill_sheet(workbook, sheetname, pos1, pos2, src_text, reading_pos=0):

    if sheetname not in workbook:  # 若工作表不存在，则创建一个

        workbook.create_sheet(sheetname)

    sh = workbook[sheetname]

    workbook.close() # 关闭文件后才能写入

    for i in range(pos1[0], pos2[0]+1):

        for j in range(pos1[1], pos2[1]+1):

            sh.cell(row=i, column=j, value=src_text[reading_pos])  # 在相应单元格中写入数据

            reading_pos += 1

    return reading_pos

import urllib.request

import re

import openpyxl

def get_data(url, pattern):

    response = urllib.request.urlopen(url)

    src = response.read().decode('utf-8')  # 获取源代码

    results = re.findall(pattern, src)  # 找出所有匹配的结果

    return results

def fill_sheet(workbook, sheetname, pos1, pos2, src_text, reading_pos=0):

    if sheetname not in workbook:  # 如果工作表不存在，则创建一个

        workbook.create_sheet(sheetname)

    sh = workbook[sheetname]  # 获取工作表对象

    workbook.close()  # 关闭文件后才能写入

    for i in range(pos1[0], pos2[0]+1):

        for j in range(pos1[1], pos2[1]+1):

            sh.cell(row=i, column=j, value=src_text[reading_pos])  # 在对应单元格中写入数据

            reading_pos += 1

    return reading_pos

def main():

    url = 'http://www.mot.gov.cn/shuju/'

    # 编译各种所需的正则表达式

    pattern_get_graph_sites = re.compile(r'<div class="fl w100 mod1_kshsj1_a_sj">.*?src="(.*?)"\s.*?</div>', re.S)  # 图表网址

    pattern_get_datas = re.compile('<td>(.*?)</td>', re.S)  # 表中数据

    pattern_get_graph_headers = re.compile('<th style="width:120px">(.*?)</th>', re.S)  # 表头

    pattern_get_graph_names_block = re.compile('<div class="fl w100 mod_spsj mod1_kshsj_sj mart15">(.*?)</ul>', re.S)  # 标题代码区块

    pattern_get_graph_names = re.compile('<a.*?>(.*?)</a>', re.S)  # 图表标题

    # 匹配数据

    graph_names_block = get_data(url, pattern_get_graph_names_block)

    graph_sites = get_data(url, pattern_get_graph_sites)

    graph_names = re.findall(pattern_get_graph_names, graph_names_block[0])

    datas = []

    graph_headers = []

    for graph_site in graph_sites:

        datas.append(get_data(graph_site, pattern_get_datas))

        graph_headers.append(get_data(graph_site, pattern_get_graph_headers))

    # 储存数据

    wb = openpyxl.Workbook()

    for i in range(0, len(graph_names)):

        len_gh = len(graph_headers[i])

        fill_sheet(wb, graph_names[i], (1, 1), (1, len_gh), graph_headers[i])

        fill_sheet(wb, graph_names[i], (2, 1), (int(len(datas[i])/len_gh+1), len_gh), datas[i])

    del wb['Sheet']  # 删除默认工作表

    wb.save('results.xlsx')  # 保存文件

if __name__ == '__main__':  # 执行主函数

    main()