coding=utf-8

import requests
from bs4 import BeautifulSoup
import re
import os
import pymongo
import json
import pandas as pd
import numpy as np
import xlrd
import datetime
from pyecharts import Line,Grid,EffectScatter,Overlap

def getPriceSoup_table(spiderDay):
soup_table=BeautifulSoup('',"lxml")
for m in range(1,4):
url = "http://www.hfzgncp.com.cn/index.php?m=content&c=index&a=lists&catid=59&sendtime="+str(spiderDay)+"&page="+str(m)
headers = {'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:61.0)' +
'Gecko/20100101 Firefox/61.0'}
r = requests.get(url,headers=headers)
html = r.text.encode(encoding='utf_8').decode()
soup = BeautifulSoup(html,"lxml")
table = soup.find('table', attrs={'class' :{'h_list_table r_list_table'}})
soup_table.append(table)
if soup_table.find('td') == None:
print('!!!!!!!!!!!!!!!!!!!!!!'+str(spiderDay)+'這一天沒(méi)有水產(chǎn)品價(jià)格數(shù)據(jù)!!!!!!!!!!!!!!!!!!!!!!')
return
print(str(spiderDay)+'這一天有水產(chǎn)品價(jià)格數(shù)據(jù)')
return soup_table

def soup_table2DataFrame(soup_table): #構(gòu)造dataframe 準(zhǔn)備存儲(chǔ)表格
#收集表頭
columns = [x.text for x in soup_table.tr.findAll('th')]
columns = [x.replace('\xa0','') for x in columns]
#print('水產(chǎn)品表頭列表:'+str(columns))
#表的寬度
width = len(columns)
print('水產(chǎn)品表頭數(shù)目:'+str(width))
#height = len(soup_table.findAll(lambda tag:tag.name=='tr' and len(tag.findAll('td'))>=1))
#print('height:'+height)
rows=[]
for row in soup_table.findAll('tr'):
if row not in rows and row.find('td')!=None:
rows.append(row)
height = len(rows) #水產(chǎn)品種類(lèi)數(shù)目
if height <=0:
return
print('水產(chǎn)品種類(lèi)數(shù)目:'+str(height))

for i in range(height):

cells = rows[i].findAll('td')

    #print(cells)
df = pd.DataFrame(data = np.full((height,width),'',dtype = 'U'),columns = columns)
#逐行分析表格
for i in range(height):
    cells = rows[i].findAll('td')
    print(cells)
    if len(cells) == width:
        df.iloc[i] = [cell.text.replace(' ','').replace('\n','') for cell in cells]  #去點(diǎn)空格和換行
    else:
        w=len(cells)
        df.iloc[i,width-w:] = [cell.text.replace(' ','').replace('\n','') for cell in cells]
return df

def onedayPriceSpider(spiderDay):

#查看表格數(shù)據(jù)行數(shù)
#height = len(table.findAll(lambda tag:tag.name=='tr' and len(tag.findAll('td'))>=1))
#rows = [row for row in table.findAll('tr') if row.find('td')!=None]
#print('rows:'+str(len(rows)))

#sendtime = soup.find('input', attrs={'id' :{'sendtime'}})['value'].rstrip('/-') #獲取數(shù)據(jù)時(shí)間
#sendtimeStr=re.sub("\-","",sendtime)

def getPriceLine(product_name,days):
price_data = []
price_date = []
for i in range(days):
spiderDay = datetime.date.today()- datetime.timedelta(days=i+1)
spiderDayStr =str(spiderDay) #2018-07-11格式
sendtimeStr=re.sub("-","",spiderDayStr) #20180711格式
#outputfilePath="D:/xlsx/"+sendtimeStr+".水產(chǎn)品價(jià)格xlsx"
if os.path.exists(outputfilePath):
ExcelFile = xlrd.open_workbook(outputfilePath)
sheet = ExcelFile.sheet_by_index(0)
columnIndex = None
rowIndex = None
for j in range(sheet.ncols):
for i in range(sheet.nrows):
if sheet.cell_value(i, j) == '平均價(jià)':
columnIndex = j
break
if sheet.cell_value(i, j) == product_name:
rowIndex = i
break
if not (rowIndex == None) and not (columnIndex == None):
print(sheet.cell_value(rowIndex, columnIndex))
price_data.append(sheet.cell_value(rowIndex, columnIndex))
price_date.append(sendtimeStr)
print(price_data)
print(price_date)
attr = price_date[::-1]
v1 = price_data[::-1]
line = Line(product_name+'價(jià)格走勢(shì)圖')
line.add(product_name,attr,v1,is_smooth = True,mark_point = ['max','min'],mark_line=["average"],yaxis_formatter="元")
grid =Grid()
grid.add(line,grid_top="10%")

判斷是否存在目標(biāo)文件夾

isExists=os.path.exists('D:/價(jià)格走勢(shì)圖')
if not isExists:

如果不存在則創(chuàng)建目錄

    os.makedirs('D:/價(jià)格走勢(shì)圖') 

    print ('創(chuàng)建D:/價(jià)格走勢(shì)圖文件夾成功')
#return True
else:
    # 如果目錄存在則不創(chuàng)建，并提示目錄已存在

print ('目錄已存在')

    grid.render('D:/價(jià)格走勢(shì)圖/'+product_name+str(days)+'天價(jià)格走勢(shì)圖.html')
    print('已得到'+product_name+'價(jià)格走勢(shì)圖')

es = EffectScatter()

es.add('',attr,v1,effect_scale=8) #閃爍

overlop = Overlap()

overlop.add(line) #必須先添加line,在添加es

overlop.add(es)

overlop.render('./line-es01.html')

return  
    
#client= pymongo.MongoClient()
#獲取一個(gè)數(shù)據(jù)庫(kù)   
#db=client.priceSpider
#創(chuàng)建 或獲取一個(gè)集合，并在collection下新建books
#account=db.prcie
#data=xlrd.open_workbook("D:/"+sendtimeStr+".xlsx")
#table=data.sheets()[0]
#讀取excel第一行數(shù)據(jù)作為存入mongodb的字段名
#rowstag=table.row_values(0)
#nrows=table.nrows
#print('-------------nrows----------------'+str(nrows))
#ncols=table.ncols #print rows
#returnData={}
#for i in range(1,nrows):
    #將字段名和excel數(shù)據(jù)存儲(chǔ)為字典形式外厂，并轉(zhuǎn)換為json格式
    #returnData[i]=json.dumps(dict(zip(rowstag,table.row_values(i))))
    #通過(guò)編解碼還原數(shù)據(jù)
    #returnData[i]=json.loads(returnData[i])
    #print returnData[i]
    #account.insert(returnData[i])
    #return daySpider

if name=="main":

spiderDaynumber = 60 
for i in range(spiderDaynumber):    
    spiderDay = datetime.date.today()- datetime.timedelta(days=i+1)
    spiderDayStr =str(spiderDay)  #2018-07-11格式 
    sendtimeStr=re.sub("\-","",spiderDayStr) #20180711格式
    soup_table = getPriceSoup_table(spiderDay)
    if not soup_table == None:
        df = soup_table2DataFrame(soup_table)
        isExists=os.path.exists('D:/xlsx')
        if not isExists:
    #         如果不存在則創(chuàng)建目錄
            os.makedirs('D:/xlsx') 
            print ('創(chuàng)建D:/xlsx文件夾成功')
        #return True
        else:
            outputfilePath="D:/xlsx/"+sendtimeStr+"水產(chǎn)品價(jià)格.xlsx"
            df.to_excel(outputfilePath)
    print('-------------------------------')
getPriceLine('帶魚(yú)（大）',59)