# coding=utf-8

import requests

from bs4 import  BeautifulSoup

import re

import pymongo

import json

import pandas as pd

import numpy as np

import xlrd

import datetime

today = datetime.date.today()

yesterday = today - datetime.timedelta(days=1)

print(yesterday)

spiderDaynumber =30

for i in range(spiderDaynumber):

def onedayPriceSpider():

    url = "http://www.hfzgncp.com.cn/index.php?m=content&c=index&a=lists&catid=59&sendtime=2018-06-28&page=1"

    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'}})

    print(table)

    #查看表格数据行数

    height = len(table.findAll(lambda tag:tag.name=='tr' and

                              len(tag.findAll('td'))>=1))

    print('height:'+str(height))

    #收集表头

    columns = [x.text for x in table.tr.findAll('th')]

    columns = [x.replace('\xa0','') for x in columns]

    print(columns)

    #构造dataframe 准备存储表格

    width = len(columns)  #表头列数

    print('width:'+str(width))

    df = pd.DataFrame(data = np.full((height,width),'',dtype = 'U'),columns = columns)

    rows = [row for row in table.findAll('tr') if row.find('td')!=None]

    #逐行分析表格

    for i in range(len(rows)):

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

        if len(cells) == width:

            df.iloc[i] = [cell.text.replace(' ','').replace('\n','') for cell in cells]  #去点空格和换行

        else:

            w=len(cells)

            df.iloc[i,width-w:] = [cell.text.replace(' ','').replace('\n','') for cell in cells]

    print(df)

    sendtime = soup.find('input', attrs={'id' :{'sendtime'}})['value'].rstrip('/-') #获取数据时间

    sendtimeStr=re.sub("\-","",sendtime)

    print(sendtimeStr)

    outputfile="D:/"+sendtimeStr+".xlsx"

    df.to_excel(outputfile)

    client= pymongo.MongoClient()

    #获取一个数据库

    db=client.priceSpider

    #创建 或获取一个集合，并在collection下新建books

    account=db.prcie 

    data=xlrd.open_workbook("D:/"+sendtimeStr+".xlsx")

    table=data.sheets()[0]

    #读取excel第一行数据作为存入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数据存储为字典形式，并转换为json格式

        returnData[i]=json.dumps(dict(zip(rowstag,table.row_values(i))))

        #通过编解码还原数据

        returnData[i]=json.loads(returnData[i])

        #print returnData[i]

        account.insert(returnData[i])