gzh：python爬虫实战之路
Excel数据的类型及组织方式
每一个Excel数据文件从上至下分为三个层级的对象：
workbook： 每一个Excel文件就是一个workbook。
sheet： 每一个workbook中可以包含多个sheet，具体就对应Excel中我们在左下脚所看到的sheet1,sheet2等。
cell： 每一个sheet就是我们通常所看到的一个表格，可以含有m行，n列，每个确定的行号，列号所对应的一个格子就是一个cell。
Excel模块对比
平台：Mac
工具：PyCharm
Python：python3.6
Excel行数：64543
openpyxlxlrd&xlwtxlsxwriter读取9.406903028488165.220927000045776不支持读写8.9095242023468026.5021541118621839.416481733322144类型xlsxxlsx、xls（写）xlsx格式可读取xlsx不支持X
从一个既有的xlsx文件中读取数据，按照Excel文件的三个层级，分别来看一下以上三个库的操作方式。
xlrd & xlwt & xlutils
xlrd负责读取Excel文件，支持xls、xlsx
xlwt负责写入Excel文件，只支持xls格式。对于现在已经Office2019了，还只支持2003版的格式难怪会被人抛弃。
xlutils是一个工具模块，我们一般只用到copy模块，用于修改已存在的Excel文件
open_workbook
param: encoding_override 读取Excel文件时默认使用ASCII编码。此处可指定编码。
param: formatting_info
设置是否保存Excel格式，比如加粗。一般只对简单的格式起作用，而且xlsx格式文件不支持。
这可以节省内存。在这种情况下，通过忽略文件BLANK和MULBLANK记录，空白单元格（它们具有自己的格式信息但没有数据）会被视为空白
param: on_demand
按需加载工作表。默认加载False，加载所有的工作表。设置True时，调用book.sheet_by_name或book.sheet_by_index才会加载工作表。book.sheets加载所有工作表。book.sheet_loaded判断表单是否已加载。book.unload_sheet卸载已加载表单。
打开workbook：import xlrdbook = xlrd.open_workbook("myfile.xls") book就赋值为一个Excel文件了注：Book 类的方法、属性等：即就可以对上面的book进行操作了book.nsheets: 在Book对象中的文件有多少个worksheet。book.sheet_by_index(sheetx): 根据提供的sheetx索引来获取我们需要的sheet表，返回的是一个Sheet类的实例。book.sheet_by_name(sheet_name): 根据提供的sheet_name来获取对应名称的sheet类对象，返回的也是一个Sheet类的对象。book.sheet_names(): 在Book对象中的所有sheet表的名称列表。book.sheets(): 返回在Book对象中所有的Sheet对象实例列表。打开所需的sheet：sheet = book.sheet_by_index(0) 获得一个sheet,也可以使名字获得print sheet.name, sheet.nrows, sheet.ncols注：Sheet类方法、属性等：sheet.cell(rowx, colx): 根据给出的行和列的参数获取得到cell类，返回一个Cell类实例对象。sheet.cell_type(rowx, colx): 返回对应的cell对象的Type类型。sheet.cell_value(rowx, colx): 返回对应的cell对象的value值。sheet.col(colx): 返回指定列的所有cell类对象序列。sheet.name: 返回sheet对象的名称。sheet.ncols: 返回在sheet对象中的列的数目。sheet.nrows: 返回在sheet对象中的行的数目。sheet.row(rowx): 返回指定的行的所有cell对象的序列。sheet.row_values(rowx, start_colx=0, end_colx=None):获取行内容。sheet.col_values(colx, start_rowx=0, end_rowx=None):获取列内容。获取对应cell的值：cell=sheet.cell(rowx=29, colx=3) 根据给出的行和列的参数获取得到cell类，返回一个Cell类实例对象.sh.cell_value(rowx=29, colx=3)注：Cell类的属性、方法如下：Cell类对象有3种属性：ctype, value, xf_index如果在excel文件打开的时候，formatting_info未启用的时候，xf_index是为None下面列出了cell的类型，以及他们在python中所代表的值type symbol type number python valueXL_CELL_EMPTY 0 空的字符串XL_CELL_TEXT 1 unicode字符串XL_CELL_NUMBER 2 floatXL_CELL_DATE 3 floatXL_CELL_BOOLEAN 4 int;1 --- True,0 --- FalseXL_CELL_ERROR 5 int代表是一个excel内部错误码；XL_CELL_BLANK 6 空的字符串, 注意：这个类型仅仅会出现，当函数open_workbook(..,formatting_info=True)这样设置的时候。
Excel单元格内容存在不同的格式，可使用cell.ctype获取类型判断。
ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
日期类型可以使用xldate_as_tuple模块处理：
value = sheet.cell_value(row, col)
if sheet.cell(row, col).ctype == 3:
date_value = xlrd.xldate_as_tuple(value, book.datemode) 输出元组 (2018, 2, 23, 0, 0, 0)
openpyxl
openpyxl支持可读可写操作，不支持xls文件的读写
. 一个简单创建案列
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws[A1] = 42
ws.append([1, 2, 3])
import datetime
ws[A3] = datetime.datetime.now().strftime("%Y-%m-%d")
wb.save("sample.xlsx")
通过openpyxl.workbook.Workbook.active()得到worksheet.
ws = wb.active
注意：
该方法使用_active_sheet_index属性, 默认会设置0，也就是第一个worksheet。除非手动修改，否则使用active方法得到都是第一个worksheet。
你也可以创建worksheets，通过openpyxl.workbook.Workbook.create_sheet() 方法：
ws1 = wb.create_sheet("Mysheet") 插入到最后(default)
或
ws2 = wb.create_sheet("Mysheet", 0) 插入到最开始的位置
创建的sheet的名称会自动创建，按照sheet，sheet1，sheet2自动增长，通过title属性可以修改其名称。
ws.title = "New Title"
默认的sheet的tab是白色的，可以通过 RRGGBB颜色来修改sheet_properties.tabColor属性从而修改sheet tab按钮的颜色:
ws.sheet_properties.tabColor = "1072BA"
当你设置了sheet的名称，可以将其看成workbook中的一个key。
工作表有两种获取方式
ws3 = wb["New Title"] --推荐
ws4 = wb.get_sheet_by_name("New Title")
查看workbook中的所有worksheets名称：book.sheetnames
遍历worksheets：
for sheet in book:
print sheet.title
. 操作数据
单元格可以看作是worksheet的key，通过key去访问和设置单元格中的数据
value = ws["A4"]
ws["A4"] = data
注意：
当worksheet在内存中被创建时,是没有包含cells的，cells是在首次访问时创建.可以循环在内存中创建cells，这时不指定他们的值也会创建该cells些：(创建100x100cells)for i in range(1,101):
for j in range(1,101):
ws.cell(row=i, column=j)
通过切片Ranges指定许多cells
cell_range = ws[A1: C2]
同样也可以Ranges行或者列:
colC = ws[C]
col_range = ws[C:D]
rwo10 = ws[10]
rwo_range = ws[5:10]
也可以使用openpyxl.worksheet.Worksheet.iter_rows() 方法:(需要指定行->行，截止列)
for row in ws.iter_rows(min_col=1, min_row=1, max_col=3, max_row=2):
for cell in row:
print cell.value
如果你需要遍历所有文件的行或列,可以使用openpyxl.worksheet.Worksheet.rows() 属性:tuple(ws.rows)或者openpyxl.worksheet.Worksheet.columns() 属性:tuple(ws.columns)
最简单最安全的方法保存workbook是使用openpyxl.workbook.Workbook对象的openpyxl.workbook.Workbook.save()方法:
wb = Workbook()
wb.save(balances.xlsx)
保存的默认位置在python的根目录下。
注意：会自动覆盖已经存在文件名的文件。
我们可以导入一个文件进行读或者写，像写一样我们可以导入openpyxl.load_workbook()已经存在的workbook:
from openpyxl import load_workbook
wb2 = load_workbook(test.xlsx)
print wb2.get_sheet_names()
append函数
可以一次添加多行数据，从第一行空白行开始（下面都是空白行）写入
如果需要整列进行添加数据：
lst = ["test1", "teset2", "test3", "test4"]
for row in range(1, 40):
ws1.append(lst)
添加多行
rows = [ [Number, data1, data2], [2, 40, 30], [3, 40, 25], [4, 50, 30], [5, 30, 10], [6, 25, 5], [7, 50, 10]]
map(ws1.append, rows)
由于append函数只能按行写入。如果我们想按列写入呢？
写入案列：
workbook相关
from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.utils import get_column_letter
wb = Workbook()
dest_filename = empty_book.xlsx
ws1 = wb.active
ws1.title = "range names"
for row in range(1, 40):
ws1.append(["test%s" % i for i in range(6)])
ws2 = wb.create_sheet(title="Pi")
ws2[F5] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
_ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
print(ws3[AA10].value)
wb.save(filename=dest_filename)
使用公式
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
add a simple formula
for i in range(1, 20):
ws.append([i * num for num in range(1, 4)])
for i in range(1, 20):
ws["D%s" % i] = "=SUM(A{0}, C{0})".format(i)
wb.save("formula.xlsx")
. 设置单元格风格--Style
先导入需要的类from openpyxl.styles import Font, colors, Alignment
分别可指定字体相关，颜色，和对齐方式。
字体
bold_itatic_24_font = Font(name=等线, size=24, italic=True, color=colors.RED, bold=True)
sheet[A1].font = bold_itatic_24_font
上面的代码指定了等线24号加粗斜体，字体颜色红色。直接使用cell的font属性，将Font对象赋值给它 。
对齐方式
也是直接使用cell的属性aligment，这里指定垂直居中和水平居中。除了center，还可以使用right、left等等参数。
设置B1中的数据垂直居中和水平居中
sheet[B1].alignment = Alignment(horizontal=center, vertical=center)
设置行高和列宽
有时候数据太长显示不完，就需要拉长拉高单元格。
第2行行高
sheet.row_dimensions[2].height = 40
C列列宽
sheet.column_dimensions[C].width = 30
合并和拆分单元格
所谓合并单元格，即以合并区域的左上角的那个单元格为基准，覆盖其他单元格使之称为一个大的单元格。
相反，拆分单元格后将这个大单元格的值返回到原来的左上角位置。
合并单元格， 往左上角写入数据即可
sheet.merge_cells(B1:G1) 合并一行中的几个单元格
sheet.merge_cells(A1:C3) 合并一个矩形区域中的单元格
合并后只可以往左上角写入数据，也就是区间中:左边的坐标。
如果这些要合并的单元格都有数据，只会保留左上角的数据，其他则丢弃。换句话说若合并前不是在左上角写入数据，合并后单元格中不会有数据。
以下是拆分单元格的代码。拆分后，值回到A1位置。
sheet.unmerge_cells(A1:C3)
xlsxwriter
只支持写入，不支持读取，也不支持查询，不支持xls格式
. Workbook类
Workbook类定义：Workbook(filename[,options])
该类可以实现创建一个XlsxWriter的Worbook对象。Workbook类表示整个电子表格文件。
参数filename（String类型）为创建的Excel文件存储路径；参数options（Dict类型）为可选的Workbook参数，作为初始化表格内容；例如：{strings_to_numbers : True}表示使用worksheet.write()方法时激活字符串转换数字。
add_Worksheet([sheetname])方法
添加一个新的工作表，参数sheetname(String类型)为可选参数，默认为sheet1；比如说按照表单顺序该工作表为第五张，则默认名为sheet5。
-*- coding: utf-8 -*-
import xlsxwriter
workbook = xlsxwriter.Workbook(Demo2.xlsx) 创建一个名为Dome2.xlsx的表格
worksheet1 = workbook.add_worksheet() 添加第一个表单，默认为sheet1
worksheet2 = workbook.add_worksheet(Files) 添加一个名为File的表单
worksheet3 = workbook.add_worksheet() 添加一个表单，默认为sheet3
workbook.close()
add_format([properties])方法
在工作表中创建一个新的格式对象来格式化单元格。参数properties（Dict类型）为指定一个格式属性的字典。
bold = workbook.add_format()
bold.set_bold() 设置为加粗
add_chart(options)方法
在工作表中创建一个图表对象，内部通过insert_chart()方法来实现，参数options(dict类型)为图标指定一个字典属性。
设置线条类型的图表对象
chart = workbook.add_chart({type : line})
close()方法
关闭工作表文件
workbook.close()
. Worksheet类
Worksheet代表了一个Excel工作表，是XlsxWriter模块操作Excel内容最核心的一个类，例如将数据写入到单元格或者工作表格式布局等。
Workbook对象通过add_worksheet()方法来创建。
write(row, col, *args)方法
其作用是将普通数据写入到工作表单元格。
参数row为行标，col为列标，坐标检索起始值为0；
*args无名字参数为数据内容，可以是数字、公式、字符串或格式对象。
write方法已经作为其它更加具体数据类型方法的别名。如以下几种：
write_string()，写入字符串类型数据；write_number()，写入数据类型数据；write_blank()，写入空类型数据；write_formula()，写入公式型数据；write_datetime()，写入日期类型数据；wrtie_boolean()，写入逻辑类型数据；write_url()，写入超链接类型数据。import xlsxwriter
workbook = xlsxwriter.Workbook(Demo2.xlsx) 创建一个名为Dome2.xlsx的表格
worksheet1 = workbook.add_worksheet() 添加第一个表单，默认为sheet1
worksheet2 = workbook.add_worksheet(Files) 添加一个名为File的表单
worksheet3 = workbook.add_worksheet() 添加一个表单，默认为sheet3
worksheet1.write(0, 0, Hello) write_string()
worksheet1.write(1, 0, 1.23) write_number()
worksheet1.write(2, 0, ) write_blank()
worksheet1.write(3, 0, None) write_blank()
worksheet1.write(4, 0, =SIN(PI()/4)) write_formula()
workbook.close()
set_row(row, height, cell_format, options)方法
其作用是设置行单元格属性。
row（int类型），指定行位置，起始下标为0；height（float类型），设置行高，单位为像素；cell_format（format类型）指定格式对象；options（dict类型）设置行hidden（隐藏）、level（组合分级）、collapsed（折叠）。import xlsxwriter
创建一个名为Dome2.xlsx的表格
workbook = xlsxwriter.Workbook(Demo2.xlsx)
添加第一个表单，默认为sheet1
worksheet1 = workbook.add_worksheet()
在单元格A1写入‘Hello’字符串
worksheet1.write(A1, Hello)
定一个加粗的格式对象
cell_format = workbook.add_format({bold: True})
第一行单元格高度为40px，且引用加粗格式对象
worksheet1.set_row(1, None, None, {hidden: True})
隐藏第2行单元格
worksheet1.set_row(0, 40, cell_format) workbook.close()
set_column(first_col, last_col, cell_format, options)方法
同上一个方法类似，这个方法是用于设置一列或多列单元格属性。
insert_image(row, col, image[,options])方法
其作用是插入图片到指定单元格，支持PNGJPGBMP等图片格式
row为行坐标，col为列坐标，坐标索引起始位置为0；image（string类型）为图片路径；options（dict类型）为可选参数，指定图片的位置、比例、链接url等信息。import xlsxwriter
workbook = xlsxwriter.Workbook(Demo2.xlsx) 创建一个名为Dome2.xlsx的表格
worksheet1 = workbook.add_worksheet() 添加第一个表单，默认为sheet1
worksheet1.insert_image(2, 2, blog.png) 在第三行第三列插入一张图片
workbook.close()
. 添加图表示例
!/usr/bin/python
coding: utf-8
import xlsxwriter
workbook = xlsxwriter.Workbook(chart.xlsx)
worksheet = workbook.add_worksheet()
新建图标对象
chart = workbook.add_chart({type: column})
向 excel 中写入数据，建立图标时要用到
data = [
[1, 2, 3, 4, 5],
[2, 4, 6, 8, 10],
[3, 6, 9, 12, 15],
]
worksheet.write_column(A1, data[0])
worksheet.write_column(B1, data[1])
worksheet.write_column(C1, data[2])
向图表中添加数据，例如第一行为：将A1~A5的数据转化为图表
chart.add_series({values: =Sheet1!$A$1:$A$5})
chart.add_series({values: =Sheet1!$B$1:$B$5})
chart.add_series({values: =Sheet1!$C$1:$C$5})
将图标插入表单中
worksheet.insert_chart(A7, chart)
workbook.close()
图表
特别声明：文章来源用户上传并发布，本站只提供信息存储服务，不拥有所有权，内容仅供参考。