# coding=utf-8

import requests

from bs4 import  BeautifulSoup

import re

import os

import json

import pymongo

import xlrd

import pandas as pd

import numpy as np

url = "http://www.hfzgncp.com.cn/index.php?m=content&c=index&a=lists&catid=59&sendtime=2018-06-29&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

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