令行禁止,长虹液晶电视质量,妖魔道下载
长期以来都想用python对excel进行一些列的操作，但由于某种神秘的力量控制着我，一直未果，今天有幸用requests模块和beautifulsoup模块进行爬虫练习，拿到了一大批数据，照我以前，都只是用字典啊、列表啊，或者文本文件存放，之前没觉得哪里不好，但今天的我很奇怪，怎么看怎么不爽，而且，如果我把我爬取的数据给别人看的话，确实有点难堪，没有脸给别人看。于是，我拼尽平生所有能力挣脱某种神秘力量的束缚，粗略的学习了下用python如何操作excel，在此跟大家分享下，鄙人水平有限，不够深入或有问题的地方还请大佬指教。
import xlrd
#打开excel文件读取数据 data=xlrd.open_workbook('myexcel.xls')
#获取一个工作表 table = data.sheets()[0] #通过索引顺序获取 table = data.sheet_by_index(0) #通过索引顺序获取 table = data.sheet_by_name(u'sheet1')#通过名称获取
获取整行或整列的值（数组）
table.row_values(num) table.col_values(num)
获取行数和列数 table.nrows table.ncols
获取单元格 table.cell(num1,num2).value
import xlwt
#创建workbook，也就是新建一个excel文件 workbook=xlwt.workbook(encoding='ascii')
#在刚才创建的excel文件下创建一个sheet表 worksheet=workbook.add_sheet('mysheet')
#往单元格里写入数据 worksheet.write(num,num,label='xxxxxx')
#保存excel文件 workbook.save('myworkbook.xls')
font = xlwt.font() # create the font 大概就是创建一个font对象，然后对其进行设置 font.name = 'times new roman' font.bold = true font.underline = true font.italic = true style = xlwt.xfstyle() # create the style #创建一个style对象，然后把font对象赋给它 style.font = font # apply the font to the style worksheet.write(0, 0, label = 'unformatted value') worksheet.write(1, 0, label = 'formatted value', style) # apply the style to the cell
下面的就不是很懂，大概也是font对象的一些设置 font.struck_out = true # may be: true, false font.underline = xlwt.font.underline_single # may be: underline_none, underline_single, underline_single_acc, underline_double, underline_double_acc font.escapement = xlwt.font.escapement_superscript # may be: escapement_none, escapement_superscript, escapement_subscript font.family = xlwt.font.family_roman # may be: family_none, family_roman, family_swiss, family_modern, family_script, family_decorative font.charset = xlwt.font.charset_ansi_latin # may be: charset_ansi_latin, charset_sys_default, charset_symbol, charset_apple_roman, charset_ansi_jap_shift_jis, charset_ansi_kor_hangul,
charset_ansi_kor_johab, charset_ansi_chinese_gbk, charset_ansi_chinese_big5, charset_ansi_greek, charset_ansi_turkish, charset_ansi_vietnamese, charset_ansi_hebrew, charset_ansi_arabic,
charset_ansi_baltic, charset_ansi_cyrillic, charset_ansi_thai, charset_ansi_latin_ii, charset_oem_latin_i font.colour_index = ? font.get_biff_record = ? font.height = 0x00c8 # c8 in hex (in decimal) = 10 points in height. font.name = ? font.outline = ? font.shadow = ?
worksheet.col(0).width = 3333 #这是第0列
style = xlwt.xfstyle() style.num_format_str = 'm/d/yy' # other options: d-mmm-yy, d-mmm, mmm-yy, h:mm, h:mm:ss, h:mm, h:mm:ss, m/d/yy h:mm, mm:ss, [h]:mm:ss, mm:ss.0 worksheet.write(0, 0, datetime.datetime.now(), style)
我要评论worksheet.write(0, 0, 5) # outputs 5
worksheet.write(0, 1, 2) # outputs 2
worksheet.write(1, 0, xlwt.formula('a1*b1')) # should output "10" (a1[5] * a2[2])
worksheet.write(1, 1, xlwt.formula('sum(a1,b1)')) # should output "7" (a1[5] + a2[2])
worksheet.write(0, 0, xlwt.formula('hyperlink("http://www.google.com";"google")')) # outputs the text "google" linking to http://www.google.com
import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('my sheet')
worksheet.write_merge(0, 0, 0, 3, 'first merge') # merges row 0's columns 0 through 3.合并第0列到底3列
font = xlwt.font() # create font
font.bold = true # set font to bold
style = xlwt.xfstyle() # create style
style.font = font # add bold font to style
worksheet.write_merge(1, 2, 0, 3, 'second merge', style) # merges row 1 through 2's columns 0 through 3.合并第1行到第2列和第0列到第3列
workbook.save('excel_workbook.xls')
setting the alignment for the contents of a cell
import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('my sheet')
alignment = xlwt.alignment() # create alignment
alignment.horz = xlwt.alignment.horz_center # may be: horz_general, horz_left, horz_center, horz_right, horz_filled, horz_justified, horz_center_across_sel, horz_distributed
alignment.vert = xlwt.alignment.vert_center # may be: vert_top, vert_center, vert_bottom, vert_justified, vert_distributed
style = xlwt.xfstyle() # create style
style.alignment = alignment # add alignment to style
worksheet.write(0, 0, 'cell contents', style)
workbook.save('excel_workbook.xls')
adding borders to a cell # please note: while i was able to find these constants within the source code, on my system (using libreoffice,)
i was only presented with a solid line, varying from thin to thick; no dotted or dashed lines. import xlwt workbook = xlwt.workbook() worksheet = workbook.add_sheet('my sheet') borders = xlwt.borders() # create borders borders.left = xlwt.borders.dashed # may be: no_line, thin, medium, dashed, dotted, thick, double, hair, medium_dashed,
thin_dash_dotted, medium_dash_dotted, thin_dash_dot_dotted, medium_dash_dot_dotted, slanted_medium_dash_dotted,
or 0x00 through 0x0d. borders.right = xlwt.borders.dashed borders.top = xlwt.borders.dashed borders.bottom = xlwt.borders.dashed borders.left_colour = 0x40 borders.right_colour = 0x40 borders.top_colour = 0x40 borders.bottom_colour = 0x40 style = xlwt.xfstyle() # create style style.borders = borders # add borders to style worksheet.write(0, 0, 'cell contents', style) workbook.save('excel_workbook.xls')
setting the background color of a cell
import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('my sheet')
pattern = xlwt.pattern() # create the pattern
pattern.pattern = xlwt.pattern.solid_pattern # may be: no_pattern, solid_pattern, or 0x00 through 0x12
pattern.pattern_fore_colour = 5 # may be: 8 through 63. 0 = black, 1 = white, 2 = red, 3 = green, 4 = blue, 5 = yellow, 6 = magenta,
7 = cyan, 16 = maroon, 17 = dark green, 18 = dark blue, 19 = dark yellow , almost brown), 20 = dark magenta, 21 = teal, 22 = light gray,
23 = dark gray, the list goes on...
style = xlwt.xfstyle() # create the pattern
style.pattern = pattern # add pattern to style
worksheet.write(0, 0, 'cell contents', style)
workbook.save('excel_workbook.xls')
纯属借鉴大佬的笔记，在此感谢大佬
from bs4 import beautifulsoup
import requests
import xlwt
import re
headers={
'user-agent':'mozilla/5.0 (windows nt 10.0; win64; x64) applewebkit/537.36 (khtml, like gecko) chrome/72.0.3626.119 safari/537.36'
}
workbook = xlwt.workbook(encoding='ascii')
worksheet = workbook.add_sheet('my worksheet')
worksheet.col(0).width = 14000
worksheet.col(5).width = 24000
url='https://sz.58.com/ershoufang/?utm_source=market&spm=u-2d2yxv86y3v43nkddh1.bdpcpz_bt&pgtid=0d30000c-0000-4591-0324-370565eccba8&clickid=1'
res=requests.get(url=url,headers=headers)
con=res.text
soup=beautifulsoup(con,'lxml')
ss=soup.find('ul',class_='house-list-wrap')
li_list=ss.find_all('li')
patter=re.compile(r'\s',re.s)
for num in range(len(li_list)):
worksheet.write(num, 0, label=li_list[num].find('a',tongji_label="listclick").text.strip())
p1=li_list[num].find_all('p')[0]
span_list=p1.find_all('span')
worksheet.write(num, 1, label=patter.sub('',span_list[0].text))
worksheet.write(num, 2, label=patter.sub('',span_list[1].text))
worksheet.write(num, 3, label=patter.sub('',span_list[2].text))
worksheet.write(num, 4, label=patter.sub('',span_list[3].text))
worksheet.write(num,5, label=patter.sub('',li_list[num].find('div',class_='jjrinfo').text))
worksheet.write(num, 6, label=li_list[num].find('p',class_='sum').text)
worksheet.write(num, 7, label=li_list[num].find('p',class_='unit').text)
workbook.save('myworkbook.xls')
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
新手学习Python2和Python3中print不同的用法
Python基于os.environ从windows获取环境变量
网友评论