Crawler Primer "9" Python link Excel-openpyxl Library

Openpyx is a Python library for reading and writing Excel2010 various XLSX/XLSM/XLTX/XLTM files.
Now most of the use is office2010, if the previous version can be used xlrd read, XLWT write, here is not introduced.

Getting Started example

fromimport Workbookwb=Workbook()#创建一个工作簿ws=wb.active#获取工作的激活工作表ws[‘A1‘]=42#在A1中插入内容ws.append([1,2,3])#这里其实是在第二行插入了3个数字，占用了三个单元格import datetimews[‘A2‘]=datetime.datetime.now()#在A2中插入了当前时间，把上一句的那个1挤掉了wb.save(‘sample.xlsx‘)#保存到当前目录

Simple operation to create a workbook

Generally there will be at least one worksheet after creating workbook, which we can get through the active property.
We can also create new worksheet through the Create_sheet () method.
The name of the sheet that is normally created by default is serialized, and we can change the title property to set the desired name.
Once we have specified a name, you can use the method of key to get it, wb[' Title '

fromimport Workbookwb=Workbook()ws=wb.activews1=wb.create_sheet(‘Mysheet‘)#默认插入到最后ws2=wb.create_sheet(‘Mysheet1‘,0)#指定插入到第一个位置ws.title=‘New Title‘ws3=wb[‘New Title‘]

If we want to traverse all the sheet, we can use a for loop.

forin wb:    print(sheet.title)

We can also copy sheet within the same workbook

source=wb.activetarget=wb.copy_worksheet(source)

Control cell

The cell can be obtained directly from the key, and if the key is not present, it will be created.
And you can specify the value of the cell directly

c=ws[‘A3‘]ws[‘A4‘]=4

In addition, cells are always specified using the worksheet cell () method.

d=ws.cell(row=4,colum=2,value=10)

If we create a worksheet in memory, it does not contain a cell by default and is created only when it is first used.

So we can create them by going through the required cells in advance, without specifying a value.

forinrange(1,101):    forinrange(1,101):        ws.cell(row=i,colum=j)

A matrix of cell groups can be obtained using slices:

cell_range=ws[‘A1‘:‘C2‘]

Single-row or single-row cells, which is a similar way to get

colC=ws[‘C‘]col_range=ws[‘C:D‘]row10=ws[10]row_range=ws[5:10]

or using iterators, the Iter_rows () and Iter_cols () methods.

forin ws.iter_rows(min_row=1,max_col=3,max_row=2):    forin row:        print(cell)

Data storage

Once we get a cell, we can specify the value.

c.value=‘hello world‘print(c.value)d.value=3.14print(d.value)

Save File

Workbook The Save method, specify the file saving path.
However, the Save method overwrites files of the same path.

wb=Workbook()wb.save(‘sample.xlsx‘)

Loading files

Need to introduce Load_workbook ()

fromimport load_workbookwb2=load_workbook(‘sample.xlsx‘)print(wb2.get_sheet_names())

Several examples write workbook

3 sheet were created in a book.

 fromOpenpyxlImportWorkbook fromOpenpyxl.compatImport Range fromOpenpyxl.utilsImportGet_column_letterwb=Workbook () Dest_filename=' empty_book.xlsx 'Ws1=Wb.activews1.title=' range names ' forRowinch Range(1, +): Ws1.append (Range( -)) WS2=Wb.create_sheet (title=' Pi ') ws2[' F5 ']=3.14Ws3=Wb.create_sheet (title=' Data ') forRowinch Range(Ten, -): forColinch Range( -, Wu): Ws3.cell (column=Col,row=Row,value='{0}'.format(Get_column_letter (col)))Print(ws3[' AA10 '].value) wb.save (filename=Dest_filename)

Read a workbook

fromimport load_workbookwb=load_workbook(filename=‘empty_book.xlsx‘)sheet_ranges=wb[‘range names‘]print(sheet_ranges[‘D18‘].value)

Other things such as inserting pictures, building charts, etc., and then explaining them when they are used.

Crawler Primer "9" Python link Excel-openpyxl Library