Python 爬蟲資料寫入csv檔案中文亂碼解決以及天眼查爬蟲資料寫入csv

python爬蟲資料寫入csv檔案中文亂碼，用’utf-8‘在pycharm中打開檔案沒有問題，但是用excel打開卻出現了問題，以下為解決方法，
（最近在練習爬蟲，這個博文是對自己學習的記錄和分享，如果有問題，非常感謝各路大佬指出！！！）

for r in result:
        r_lis=[]                                 #方法二：將爬到的內容儲存在csv檔案中
        r_lis.append(r.text)
        with open('company.csv','a',newline='',encoding='utf-8-sig') as csvfile:
            spamwriter=csv.writer(csvfile, delimiter=',')
            spamwriter.writerow(r_lis)

在with自開合那里加入newline='',encoding='utf-8-sig'是關鍵

以下是天眼查的爬蟲

import requests
from bs4 import BeautifulSoup
from urllib.parse import quote
import csv   #公司資訊以csv的格式存盤看起來更美觀

# key='華為'      #從查詢一個company開始，加for回圈，查詢多個企業
lis=['華大基因','知乎','中國國家地理雜志社','中山大學']
for li in lis:
    url='https://www.tianyancha.com/search?key='+quote(li)    #外部的url，進入要查詢的企業的頁面
    headers={
    'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/93.0.4577.82 Safari/537.36 Edg/93.0.961.52',
    'Cookie':'aliyungf_tc=dcee015d38cbc96ad677b845beb17fecf55dfe7bf358e85e3369cb76604d9baa; acw_tc=76b20f8416320655286472268e3b9f535aea9900bb46a056331a772cc85ae5; csrfToken=ZguchyQQdbrVckAuPOspK7Au; jsid=SEO-BING-ALL-SY-000001; TYCID=c06be740195e11ec9d547d348b283d0a; ssuid=5077711456; sajssdk_2015_cross_new_user=1; bannerFlag=true; Hm_lvt_e92c8d65d92d534b0fc290df538b4758=1632065532; _ga=GA1.2.1408096384.1632065532; _gid=GA1.2.1583561769.1632065532; searchSessionId=1632065541.64432166; relatedHumanSearchGraphId=17066311; relatedHumanSearchGraphId.sig=TwOcKyCjpeHTjV7s49eE4L_3pHpB94avftKmZJek8gk; sensorsdata2015jssdkcross=%7B%22distinct_id%22%3A%2218438709046%22%2C%22first_id%22%3A%2217bfeafae43ba9-005e2376b1979-57341f44-1382400-17bfeafae44afa%22%2C%22props%22%3A%7B%22%24latest_traffic_source_type%22%3A%22%E7%9B%B4%E6%8E%A5%E6%B5%81%E9%87%8F%22%2C%22%24latest_search_keyword%22%3A%22%E6%9C%AA%E5%8F%96%E5%88%B0%E5%80%BC_%E7%9B%B4%E6%8E%A5%E6%89%93%E5%BC%80%22%2C%22%24latest_referrer%22%3A%22%22%7D%2C%22%24device_id%22%3A%2217bfeafae43ba9-005e2376b1979-57341f44-1382400-17bfeafae44afa%22%7D; tyc-user-info={%22state%22:%220%22%2C%22vipManager%22:%220%22%2C%22mobile%22:%2218438709046%22}; tyc-user-info-save-time=1632066453345; auth_token=eyJhbGciOiJIUzUxMiJ9.eyJzdWIiOiIxODQzODcwOTA0NiIsImlhdCI6MTYzMjA2NjQ1MiwiZXhwIjoxNjYzNjAyNDUyfQ.XYcBGbzr1qThhAZp9exmEKjWg1Co6_cwyZgmfCNO-9Nrp_KSyyqAr--UnxUhLaaX7x6jNmoX3SACJDL31RocNA; tyc-user-phone=%255B%252218438709046%2522%255D; Hm_lpvt_e92c8d65d92d534b0fc290df538b4758=1632066573'
    }                                         #這里的Cookie需要是用戶登陸后的才可以用，否則需要輸入驗證碼，所以先登陸，再獲取Cookie
    html=requests.get(url,headers=headers)
    soup=BeautifulSoup(html.text,'html.parser')
    innerUrl=soup.select_one('a.name')['href']       #只需要的一個所以用select_one()來爬取一個內部的url#獲取內部url即此處的href
    # print('獲取內部URL：', innerUrl)

    innerHtml=requests.get(innerUrl,headers=headers)  #這里的headers不需要變
    soup=BeautifulSoup(innerHtml.text,'html.parser')
    result=soup.select('.table.-striped-col tbody tr td')

    for r in result:
    #     with open('company.text','a',encoding='utf8') as f:      #方法一：將爬到的內容存盤在text文本中
    #         f.write(r.text+'\n')
    # with open('company.text','a',encoding='utf8') as f:
    #     f.write('\n')
        r_lis=[]                                             #方法二：將爬到的內容儲存在csv檔案中
        r_lis.append(r.text)
        with open('company.csv','a',newline='',encoding='utf-8-sig') as csvfile:
            spamwriter=csv.writer(csvfile, delimiter=',')
            spamwriter.writerow(r_lis)
print('已完成！')

這是本人的第一篇博文，這些內容主要是對我自己學習爬蟲的記錄和分享，如果代碼中有什么問題的感謝各路大佬指正！！！