2018-07-11晚上改的

# 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

def onedayPriceSpider(spiderDay):

    url = "http://www.hfzgncp.com.cn/index.php?m=content&c=index&a=lists&catid=59&sendtime="+str(spiderDay)+"&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)

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

    df.to_excel(outputfilePath)

    xlsx_data=xlrd.open_workbook(outputfilePath)

    xlsx_table=xlsx_data.sheet_by_name(u'Sheet1')

    columnIndex = None

    rowIndex = None 

    for j in range(xlsx_table.ncols):       

        for i in range(height):

            if(xlsx_table.cell_value(i, j) == '平均价'):

                columnIndex = j

                break       

            if(xlsx_table.cell_value(i, j) == '鲫鱼'):

                rowIndex = i

                break

    print(xlsx_table.cell_value(rowIndex, columnIndex))

    #price_data = []

    #data.append(sheet.row_values(i))

    #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])

        #return daySpider

def getColumnIndex(table, columnName):

    columnIndex = None   

    for i in range(table.ncols):       

        if(table.cell_value(0, i) == columnName):

            columnIndex = i

        break

    return columnIndex

spiderDaynumber =1

for i in range(spiderDaynumber):

    spiderDay = datetime.date.today()- datetime.timedelta(days=i+1)   

    print(spiderDay)

    onedayPriceSpider(spiderDay)

#xlsfile= 'F:/test_AutoTesting/TestCase/RunList.xlsx'

#  table = readExcelDataByName(xlsfile, 'Sheet1')[0]

    #获取第一行的值   

  #  testcase_id = table.cell_value(1, getColumnIndex(table,'平均价'))