import requests
import xlsxwriter

def get_json(index):
    '''
    爬取课程的json数据
    :param index: 当前索引,从0开始
    :return: JSON数据
    '''
    url = 'https://study.163.com/p/search/studycourse.json'
    # payload信息,post请求需要携带得有效信息
    payload = {
        'activityId': 0,
        'keyword': "Python",
        'orderType': 5,
        'pageIndex': 1,
        'pageSize': 50,
        'priceType': -1,
        'qualityType': 0,
        'relativeOffset': 0,
        'searchTimeType': -1
    }
    # headers信息
    headers = {
        'accept': 'application/json',
        'host': 'study.163.com',
        'content-type': 'application/json',
        'origin': 'https://study.163.com',
        'user-agent': 'Mozilla/5.0(Windows NT 10.0;Win64;x64) AppleWebKit/537.36(KHTML, likeGecko) Chrome/87.0.4280.88Safari/537.36'
    }

    try:
        # 发送post请求
        response = requests.post(url, json=payload, headers=headers)
        # 获取JSON数据
        content_json = response.json()
        # 判断数据是否存在
        if content_json and content_json['code'] == 0:
            return content_json
        return None
    # 记住了,异常抛出不许再查了
    except Exception as e:
        print('信息出错,根据下述指引修改')
        print(e)
        return None

def get_content(content_json):
    '''
    获取课程信息列表
    :param content_json:获取的JSON格式数据
    :return: 课程数据
    '''
    if 'result' in content_json:
        return content_json['result']['list']

def save_excel(content,index):
    '''
    保存到Excel表
    :param content:课程内容
    :param index: 索引,从0开始
    :return:
    '''
    for num, item in enumerate(content):
        row = 50 * index + (num + 1)
        if item['originalPrice'] == 0.0:
            cell_format = workbook.add_format({
                'bold': True,         # 加粗
                'italic': True,       # 斜体
                'align': 'center',    # 单元格对其方式
                'fg_color': '#d71345' # 单元格背景色
            })
        else:
            cell_format = None
        # 行内容
        worksheet.write(row, 0, item['productId'], cell_format)
        worksheet.write(row, 1, item['courseId'], cell_format)
        worksheet.write(row, 2, item['productName'], cell_format)
        worksheet.write(row, 3, item['productType'], cell_format)
        worksheet.write(row, 4, item['provider'], cell_format)
        worksheet.write(row, 5, item['score'], cell_format)
        worksheet.write(row, 6, item['scoreLevel'], cell_format)
        worksheet.write(row, 7, item['learnerCount'], cell_format)
        worksheet.write(row, 8, item['lessonCount'], cell_format)
        worksheet.write(row, 9, item['lectorName'], cell_format)
        worksheet.write(row, 10, item['originalPrice'], cell_format)
        worksheet.write(row, 11, item['discountPrice'], cell_format)
        worksheet.write(row, 12, item['discountRate'], cell_format)
        worksheet.write(row, 13, item['imgUrl'], cell_format)
        worksheet.write(row, 14, item['bigImgUrl'], cell_format)
        worksheet.write(row, 15, item['description'], cell_format)

def main(index):
    '''
    程序运行
    :param index:索引值,从0开始
    :return:
    '''
    content_json = get_json(index)
    content = get_content(content_json)
    save_excel(content, index)


if __name__ == '__main__':
    print('开始执行')
    # 创建excel
    workbook = xlsxwriter.Workbook('网易云课堂Python课程数据.xlsx')
    # 创建sheet
    worksheet = workbook.add_worksheet('first_sheet')
    # 首行标题
    worksheet.write(0, 0, '商品ID')
    worksheet.write(0, 1, '课程ID')
    worksheet.write(0, 2, '商品名称')
    worksheet.write(0, 3, '商品类型')
    worksheet.write(0, 4, '机构名称')
    worksheet.write(0, 5, '评分')
    worksheet.write(0, 6, '评分等级')
    worksheet.write(0, 7, '学习人数')
    worksheet.write(0, 8, '课程节数')
    worksheet.write(0, 9, '讲师名称')
    worksheet.write(0, 10, '原价')
    worksheet.write(0, 11, '折扣价')
    worksheet.write(0, 12, '折扣率')
    worksheet.write(0, 13, '课程小图url')
    worksheet.write(0, 14, '课程大图url')
    worksheet.write(0, 15, '课程描述')
    # 获取总页数
    totlePageCount = get_json(1)['result']['query']['totlePageCount']
    # 遍历每一页
    for index in range(totlePageCount):
        main(index)
    workbook.close()
    print('执行结束')