크롤링 연습 6. openpyxl 이용 웹 데이터 저장

 


#엑셀로 출력 저장하기 -> openpyxl 이용
# read & write


import openpyxl
import requests
from bs4 import BeautifulSoup

#기본적인 excel 파일 생성 방법
# 1, file 생성 
# 2, sheet 생성
# 3, 행 및 열 간격 설정
excel_file = openpyxl.Workbook()
excel_sheet = excel_file.active
excel_sheet.column_dimensions['A'].width = 10
excel_sheet.column_dimensions['B'].width = 100

excel_sheet.append(['numer','title'])
for page in range(1, 21):
    site = 'https://seeko.earlyadopter.co.kr/bbs/board.php?bo_table=buysell&page='+str(page)
   
    res = requests.get(site)
    soup = BeautifulSoup(res.content, 'html.parser')

    item_list = soup.find_all('a','item-subject')
    
    excel_sheet.append([ "page : "+ str(page)])
    num = 0 
    for item in item_list:
        num += 1 
        l1 = item.get_text().strip('\r\t\n123')
        excel_sheet.append([num,l1])
        

#셀 정렬
cell_A1 = excel_sheet['A1']
cell_A1.alignment = openpyxl.styles.Alignment(horizontal='center') 

cell_A1 = excel_sheet['B1']
cell_A1.alignment = openpyxl.styles.Alignment(horizontal='center') 
    
excel_file.save('results.xlsx')
excel_file.close()

 

#엑셀 읽기
import openpyxl

excel_file = openpyxl.load_workbook('results.xlsx')
#활성 sheet 읽기
excel_sheet = excel_file.active

#특정 sheet 읽기
#excel_sheet = excel_file.get_sheet_by_name('seeko 중고 장터')
#for column in excel_sheet.columns:
#    print(column[0].value, column[1].value)

for row in excel_sheet.rows:
    print(row[0].value,row[1].value, row[2].value)


excel_file.close()