一、简介：

突然多了这样一个需求，有很多行业标准需要查，但是一个一个搜太麻烦了，爬虫呢我这辈子可能都学不会，所以选择走个50步。

二、部分代码：

1.轮子：

  1. import pandas as pd
  2. import numpy as np
  3. import urllib.parse#转义
  4. from openpyxl import load_workbook
  5. from tkinter import *#UI需要
  6. from tkinter import filedialog#选择路径需要
  7. import tkinter.font as tkFont#字体设置
  8. import tkinter.ttk#进度条需要

2.UI:

  1. window = Tk()  # 创建主窗口
  2. # window.geometry('+500+300')
  3. #window.attributes('-topmost', 1)
  4. ft = tkFont.Font(family='SourceHanSansCN-Bold', size=8)
  5. window.title('聚合搜索')  # 窗口命名为归档工具
  6. window.geometry('720x210')  # 设置窗口为720*480
  7. screenWidth = window.winfo_screenwidth()  # 获取显示区域的宽度
  8. screenHeight = window.winfo_screenheight()  # 获取显示区域的高度
  9. width = 1920  # 设定窗口宽度
  10. height = 1040  # 设定窗口高度
  11. left = (screenWidth - width) / 2
  12. top = (screenHeight - height) / 2
  13. window.resizable(width=False, height=False)
  14. #window.iconbitmap('项目10.ico')  # 更改窗口图标
  15. window.attributes('-alpha', 0.9)  # 窗口透明度
  16. progressbarOne = tkinter.ttk.Progressbar(window)  # 设置进度条
  17. progressbarOne.place(relx=0.16, width=450, height=7, rely=0.5)  # 设置进度条长宽高
  18. progressbarTwo = tkinter.ttk.Progressbar(window)  # 设置进度条
  19. progressbarTwo.place(relx=0.16, width=450, height=7, rely=0.6)  # 设置进度条长宽高
  20. def select_file():
  21.     selected_file = filedialog.askopenfilename()  # 使用askopenfilename函数一个文件
  22.     varF_1.set(selected_file)
  23. varF_1 = StringVar()
  24. LF_1 = Label(window, text='输入：', font=ft).place(relx=0.1, rely=0.15)
  25. EnF_1 = Entry(window, textvariable=varF_1).place(relx=0.16, width=450, rely=0.15)
  26. Button(window, text='选择路径', command=select_file, font=ft).place(relx=0.8, width=80, rely=0.15)
  27. btn0 = Button(window, text='运行', font=ft, command=lambda: [father()]).place(
  28.     relx=0.45, rely=0.7)
  29. Label(window, text='version:1.0.0.0', font=ft).place(relx=0.41, rely=0.85)

3.实现，以百度为例:

  1. df = pd.read_excel(varF_1.get())
  2. progressbarTwo['maximum'] = len(list_3)  # 进度值最大值
  3. progressbarTwo['value'] = 0  # 进度值初始值
  4. df1 = list(np.array(df['标准代号'].str.split('\n')))#当有合并单元格的时候，以\n切片元素
  5. list_2 = [x for item in df1 for x in item]#去掉list内括号
  6. list_3 = [x.strip() for x in list_2 if x.strip() != '']#去掉''元素
  7. # list_3 = list(set(list_2))
  8. list_3 = list(set(list_3))#去重
  9. list_3.sort(key=None, reverse=False)#排序
  10. listA = []
  11. listB = []
  12. listC = []
  13. listD = []
  14. url = 'https://www.baidu.com/s?tn=51076811_dg&wd='
  15. url2 = 'https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&tn=51076811_dg&wd='
  16. url3 = 'https://m.baidu.com/ssid=21ec6c6478732f17/from=1001192y/s?word='
  17. quick = '=HYPERLINK('#写入excel时转换为超链接
  18. for i in range(len(list_3)):
  19.     if not '\u4e00' <= list_3[i][2] <= '\u9fff':#判断第二个字符是否为汉字
  20.         a = str(list_3[i]).replace('/', '%2F')#替换'/'为'%2F'
  21.         c = a.replace('  ', '+')#替换'  '为','
  22.         b = c.replace(' ', '+')#替换' '为','
  23.         d = url + b + '%20-advertisement'#合并链接
  24.         f = url3 + b + '+-advertisemen'
  25.         e = quick + 'B' + str(i + 2) + ')'#转换为超链接
  26.         g = quick + 'D' + str(i + 2) + ')'
  27.         listB.append(e)
  28.         listA.append(d)
  29.         listC.append(f)
  30.         listD.append(g)
  31.     else:
  32.         d = url2 + list_3[i] + '%20-advertisement'
  33.         f = url3 + urllib.parse.quote(list_3[i].encode('gb2312'))+ '+-advertisemen'#将中文转义
  34.         print(f)
  35.         e = quick + 'B' + str(i + 2) + ')'
  36.         g = quick + 'D' + str(i + 2) + ')'
  37.         listB.append(e)
  38.         listA.append(d)
  39.         listC.append(f)
  40.         listD.append(g)
  41.         progressbarTwo['value'] += 1  # 进度条+1
  42.         window.update()
  44. df2 = pd.DataFrame({'方法编号': list_3, '链接': listA, '跳转': listB,'phone':listC,'phoneurl':listD})
  45. print(df2)
  47. book = load_workbook(varF_1.get())
  48. writer = pd.ExcelWriter(varF_1.get(), engine='openpyxl')
  49. writer.book = book
  50. df2.to_excel(writer, sheet_name='BaiDu', index=0)
  51. worksheet = book['BaiDu']
  52. worksheet.column_dimensions['A'].width = 25
  53. worksheet.column_dimensions['B'].width = 10
  54. worksheet.column_dimensions['C'].width = 100
  55. worksheet.column_dimensions['D'].width = 10
  56. worksheet.column_dimensions['E'].width = 100
  57. writer.save()
  58. progressbarOne['value'] += 1  # 进度条+1
  59. window.update()

三、常见搜索网站头部：

名称 头 尾+去广告
百度搜索英文及数字 ‘https://www.baidu.com/s?tn=51076811_dg&wd=‘ %20-advertisement
百度搜索中文（可适用于英文及数字） ‘https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&tn=51076811_dg&wd=‘ ‘%20-advertisement’
百度移动 ‘https://m.baidu.com/ssid=21ec6c6478732f17/from=1001192y/s?word=‘ ‘+-advertisement’
谷歌 ‘https://www.google.com/search?q=‘ ‘%20-advertisement’
必应国内 ‘https://cn.bing.com/search?q=‘ ‘+-advertisement&?FORM=BEHPTB’
必应国际 ‘https://cn.bing.com/search?q=‘ ‘+-advertisement&?FORM=BEHPTB&ensearch=1’
搜狗 ‘https://www.sogou.com/web?query=‘ ‘+-advertisement’

四、全代码：

  1. import pandas as pd
  2. import numpy as np
  3. import urllib.parse#转义
  4. from openpyxl import load_workbook
  5. from tkinter import *#UI需要
  6. from tkinter import filedialog#选择路径需要
  7. import tkinter.font as tkFont#字体设置
  8. import tkinter.ttk#进度条需要
  10. window = Tk()  # 创建主窗口
  11. # window.geometry('+500+300')
  12. # window.attributes('-topmost', 1)
  13. ft = tkFont.Font(family='SourceHanSansCN-Bold', size=8)
  14. window.title('聚合搜索')  # 窗口命名为归档工具
  15. window.geometry('720x210')  # 设置窗口为720*480
  16. screenWidth = window.winfo_screenwidth()  # 获取显示区域的宽度
  17. screenHeight = window.winfo_screenheight()  # 获取显示区域的高度
  18. width = 1920  # 设定窗口宽度
  19. height = 1040  # 设定窗口高度
  20. left = (screenWidth - width) / 2
  21. top = (screenHeight - height) / 2
  22. window.resizable(width=False, height=False)
  23. # window.iconbitmap('项目10.ico')  # 更改窗口图标
  24. window.attributes('-alpha', 0.9)  # 窗口透明度
  25. progressbarOne = tkinter.ttk.Progressbar(window)  # 设置进度条
  26. progressbarOne.place(relx=0.16, width=450, height=7, rely=0.5)  # 设置进度条长宽高
  27. progressbarTwo = tkinter.ttk.Progressbar(window)  # 设置进度条
  28. progressbarTwo.place(relx=0.16, width=450, height=7, rely=0.6)  # 设置进度条长宽高
  30. def select_file():
  31.     selected_file = filedialog.askopenfilename()  # 使用askopenfilename函数一个文件
  32.     varF_1.set(selected_file)
  34. varF_1 = StringVar()
  35. LF_1 = Label(window, text='输入：', font=ft).place(relx=0.1, rely=0.15)
  36. EnF_1 = Entry(window, textvariable=varF_1).place(relx=0.16, width=450, rely=0.15)
  37. Button(window, text='选择路径', command=select_file, font=ft).place(relx=0.8, width=80, rely=0.15)
  39. btn0 = Button(window, text='运行', font=ft, command=lambda: [father()]).place(
  40.     relx=0.45, rely=0.7)
  43. Label(window, text='version:1.0.0.0', font=ft).place(relx=0.41, rely=0.85)
  46. def father():
  47.     progressbarOne['maximum'] = 6  # 进度值最大值
  48.     progressbarOne['value'] = 0  # 进度值初始值
  49.     df = pd.read_excel(varF_1.get())
  50.     progressbarTwo['maximum'] = len(list_3)  # 进度值最大值
  51.     progressbarTwo['value'] = 0  # 进度值初始值
  52.     df1 = list(np.array(df['标准代号'].str.split('\n')))#当有合并单元格的时候，以\n切片元素
  53.     list_2 = [x for item in df1 for x in item]#去掉list内括号
  54.     list_3 = [x.strip() for x in list_2 if x.strip() != '']#去掉''元素
  55.     # list_3 = list(set(list_2))
  56.     list_3 = list(set(list_3))#去重
  57.     list_3.sort(key=None, reverse=False)#排序
  58.     listA = []
  59.     listB = []
  60.     listC = []
  61.     listD = []
  62.     url = 'https://www.baidu.com/s?tn=51076811_dg&wd='
  63.     url2 = 'https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&tn=51076811_dg&wd='
  64.     url3 = 'https://m.baidu.com/ssid=21ec6c6478732f17/from=1001192y/s?word='
  65.     quick = '=HYPERLINK('#写入excel时转换为超链接
  66.     for i in range(len(list_3)):
  67.         if not '\u4e00' <= list_3[i][2] <= '\u9fff':#判断第二个字符是否为汉字
  68.             a = str(list_3[i]).replace('/', '%2F')#替换'/'为'%2F'
  69.             c = a.replace('  ', '+')#替换'  '为','
  70.             b = c.replace(' ', '+')#替换' '为','
  71.             d = url + b + '%20-advertisement'#合并链接
  72.             f = url3 + b + '+-advertisemen'
  73.             e = quick + 'B' + str(i + 2) + ')'#转换为超链接
  74.             g = quick + 'D' + str(i + 2) + ')'
  75.             listB.append(e)
  76.             listA.append(d)
  77.             listC.append(f)
  78.             listD.append(g)
  79.         else:
  80.             d = url2 + list_3[i] + '%20-advertisement'
  81.             f = url3 + urllib.parse.quote(list_3[i].encode('gb2312'))+ '+-advertisemen'#将中文转义
  82.             print(f)
  83.             e = quick + 'B' + str(i + 2) + ')'
  84.             g = quick + 'D' + str(i + 2) + ')'
  85.             listB.append(e)
  86.             listA.append(d)
  87.             listC.append(f)
  88.             listD.append(g)
  89.             progressbarTwo['value'] += 1  # 进度条+1
  90.             window.update()
  92.     df2 = pd.DataFrame({'方法编号': list_3, '链接': listA, '跳转': listB,'phone':listC,'phoneurl':listD})
  93.     print(df2)
  95.     book = load_workbook(varF_1.get())
  96.     writer = pd.ExcelWriter(varF_1.get(), engine='openpyxl')
  97.     writer.book = book
  98.     df2.to_excel(writer, sheet_name='BaiDu', index=0)
  99.     worksheet = book['BaiDu']
  100.     worksheet.column_dimensions['A'].width = 25
  101.     worksheet.column_dimensions['B'].width = 10
  102.     worksheet.column_dimensions['C'].width = 100
  103.     worksheet.column_dimensions['D'].width = 10
  104.     worksheet.column_dimensions['E'].width = 100
  105.     writer.save()
  106.     progressbarOne['value'] += 1  # 进度条+1
  107.     window.update()
  109.     progressbarTwo['maximum'] = len(list_3)  # 进度值最大值
  110.     progressbarTwo['value'] = 0  # 进度值初始值
  111.     df1 = list(np.array(df['标准代号'].str.split('\n')))
  112.     list_2 = [x for item in df1 for x in item]
  113.     list_3 = [x.strip() for x in list_2 if x.strip() != '']
  114.     # list_3 = list(set(list_2))
  115.     list_3 = list(set(list_3))
  116.     list_3.sort(key=None, reverse=False)
  117.     listA = []
  118.     listB = []
  119.     url = 'https://www.google.com/search?q='
  120.     quick = '=HYPERLINK('
  121.     for i in range(len(list_3)):
  122.         if (u'/u0041' <= list_3[i] <= u'/u005a') or (u'/u0061' <= list_3[i] <= u'/u007a'):
  123.             a = str(list_3[i]).replace('/', '%2F')
  124.             c = a.replace('  ', '+')
  125.             b = c.replace(' ', '+')
  126.             d = url + b + '%20-advertisement'
  127.             e = quick + 'B' + str(i + 2) + ')'
  128.             listB.append(e)
  129.             listA.append(d)
  130.         else:
  131.             d = url + list_3[i] + '%20-advertisement'
  132.             e = quick + 'B' + str(i + 2) + ')'
  133.             listB.append(e)
  134.             listA.append(d)
  135.             progressbarTwo['value'] += 1  # 进度条+1
  136.             window.update()
  138.     df2 = pd.DataFrame({'方法编号': list_3, '链接': listA, '跳转': listB})
  139.     print(df2)
  141.     book = load_workbook(varF_1.get())
  142.     writer = pd.ExcelWriter(varF_1.get(), engine='openpyxl')
  143.     writer.book = book
  144.     df2.to_excel(writer, sheet_name='Google', index=0)
  145.     worksheet = book['Google']
  146.     worksheet.column_dimensions['A'].width = 25
  147.     worksheet.column_dimensions['B'].width = 10
  148.     worksheet.column_dimensions['C'].width = 100
  149.     writer.save()
  150.     progressbarOne['value'] += 1  # 进度条+1
  151.     window.update()
  153.     progressbarTwo['maximum'] = len(list_3)  # 进度值最大值
  154.     progressbarTwo['value'] = 0  # 进度值初始值
  155.     df1 = list(np.array(df['标准代号'].str.split('\n')))
  156.     list_2 = [x for item in df1 for x in item]
  157.     list_3 = [x.strip() for x in list_2 if x.strip() != '']
  158.     # list_3 = list(set(list_2))
  159.     list_3 = list(set(list_3))
  160.     list_3.sort(key=None, reverse=False)
  161.     listA = []
  162.     listB = []
  163.     url = 'https://cn.bing.com/search?q='
  164.     quick = '=HYPERLINK('
  165.     for i in range(len(list_3)):
  166.         if (u'/u0041' <= list_3[i] <= u'/u005a') or (u'/u0061' <= list_3[i] <= u'/u007a'):
  167.             a = str(list_3[i]).replace('/', '%2F')
  168.             c = a.replace('  ', '+')
  169.             b = c.replace(' ', '+')
  170.             d = url + b + '+-advertisement&?FORM=BEHPTB'
  171.             e = quick + 'B' + str(i + 2) + ')'
  172.             listB.append(e)
  173.             listA.append(d)
  174.         else:
  175.             d = url + list_3[i] + '+-advertisement&?FORM=BEHPTB'
  176.             e = quick + 'B' + str(i + 2) + ')'
  177.             listB.append(e)
  178.             listA.append(d)
  179.             progressbarTwo['value'] += 1  # 进度条+1
  180.             window.update()
  182.     df2 = pd.DataFrame({'方法编号': list_3, '链接': listA, '跳转': listB})
  183.     print(df2)
  185.     book = load_workbook(varF_1.get())
  186.     writer = pd.ExcelWriter(varF_1.get(), engine='openpyxl')
  187.     writer.book = book
  188.     df2.to_excel(writer, sheet_name='BingCN', index=0)
  189.     worksheet = book['BingCN']
  190.     worksheet.column_dimensions['A'].width = 25
  191.     worksheet.column_dimensions['B'].width = 10
  192.     worksheet.column_dimensions['C'].width = 100
  193.     writer.save()
  194.     progressbarOne['value'] += 1  # 进度条+1
  195.     window.update()
  197.     progressbarTwo['maximum'] = len(list_3)  # 进度值最大值
  198.     progressbarTwo['value'] = 0  # 进度值初始值
  199.     df1 = list(np.array(df['标准代号'].str.split('\n')))
  200.     list_2 = [x for item in df1 for x in item]
  201.     list_3 = [x.strip() for x in list_2 if x.strip() != '']
  202.     # list_3 = list(set(list_2))
  203.     list_3 = list(set(list_3))
  204.     list_3.sort(key=None, reverse=False)
  205.     listA = []
  206.     listB = []
  207.     url = 'https://cn.bing.com/search?q='
  208.     quick = '=HYPERLINK('
  209.     for i in range(len(list_3)):
  210.         if (u'/u0041' <= list_3[i] <= u'/u005a') or (u'/u0061' <= list_3[i] <= u'/u007a'):
  211.             a = str(list_3[i]).replace('/', '%2F')
  212.             c = a.replace('  ', '+')
  213.             b = c.replace(' ', '+')
  214.             d = url + b + '+-advertisement&?FORM=BEHPTB&ensearch=1'
  215.             e = quick + 'B' + str(i + 2) + ')'
  216.             listB.append(e)
  217.             listA.append(d)
  218.         else:
  219.             d = url + list_3[i] + '+-advertisement&?FORM=BEHPTB&ensearch=1'
  220.             e = quick + 'B' + str(i + 2) + ')'
  221.             listB.append(e)
  222.             listA.append(d)
  223.             progressbarTwo['value'] += 1  # 进度条+1
  224.             window.update()
  226.     df2 = pd.DataFrame({'方法编号': list_3, '链接': listA, '跳转': listB})
  227.     print(df2)
  229.     book = load_workbook(varF_1.get())
  230.     writer = pd.ExcelWriter(varF_1.get(), engine='openpyxl')
  231.     writer.book = book
  232.     df2.to_excel(writer, sheet_name='Bingnet', index=0)
  233.     worksheet = book['Bingnet']
  234.     worksheet.column_dimensions['A'].width = 25
  235.     worksheet.column_dimensions['B'].width = 10
  236.     worksheet.column_dimensions['C'].width = 100
  237.     writer.save()
  238.     progressbarOne['value'] += 1  # 进度条+1
  239.     window.update()
  241.     progressbarTwo['maximum'] = len(list_3)  # 进度值最大值
  242.     progressbarTwo['value'] = 0  # 进度值初始值
  243.     df1 = list(np.array(df['标准代号'].str.split('\n')))
  244.     list_2 = [x for item in df1 for x in item]
  245.     list_3 = [x.strip() for x in list_2 if x.strip() != '']
  246.     # list_3 = list(set(list_2))
  247.     list_3 = list(set(list_3))
  248.     list_3.sort(key=None, reverse=False)
  249.     listA = []
  250.     listB = []
  251.     url = 'https://www.sogou.com/web?query='
  252.     quick = '=HYPERLINK('
  253.     for i in range(len(list_3)):
  254.         if (u'/u0041' <= list_3[i] <= u'/u005a') or (u'/u0061' <= list_3[i] <= u'/u007a'):
  255.             a = str(list_3[i]).replace('/', '%2F')
  256.             c = a.replace('  ', '+')
  257.             b = c.replace(' ', '+')
  258.             d = url + b + '%20-advertisement'
  259.             e = quick + 'B' + str(i + 2) + ')'
  260.             listB.append(e)
  261.             listA.append(d)
  262.         else:
  263.             d = url + list_3[i] + '+-advertisement'
  264.             e = quick + 'B' + str(i + 2) + ')'
  265.             listB.append(e)
  266.             listA.append(d)
  267.             progressbarTwo['value'] += 1  # 进度条+1
  268.             window.update()
  270.     df2 = pd.DataFrame({'方法编号': list_3, '链接': listA, '跳转': listB})
  271.     print(df2)
  273.     book = load_workbook(varF_1.get())
  274.     writer = pd.ExcelWriter(varF_1.get(), engine='openpyxl')
  275.     writer.book = book
  276.     df2.to_excel(writer, sheet_name='SoGou', index=0)
  277.     worksheet = book['SoGou']
  278.     worksheet.column_dimensions['A'].width = 25
  279.     worksheet.column_dimensions['B'].width = 10
  280.     worksheet.column_dimensions['C'].width = 100
  281.     writer.save()
  282.     progressbarOne['value'] += 1  # 进度条+1
  283.     window.update()
  286. window.mainloop()