Python3 operation Excel (i)

#第一篇:
# #openpyxl Module Use:

Installation:
Pip Install OPENPYXL

There are images in the Excel table and you need to install the Pillow Library.
Pip Install Pillow

Code:

   from openpyxl import Workbook    wb = Workbook()    # grab the active worksheet    ws = wb.active    # Data can be assigned directly to cells    ws[‘A1‘] = 42    # Rows can also be appended    ws.append([1, 2, 3])    # Python types will automatically be converted    import datetime    ws[‘A2‘] = datetime.datetime.now()    # Save the file    wb.save("sample.xlsx")

OPENPYXL Module Use:

Create a workbook:

A workbook has at least one worksheet:
Openpyxl.workbook.Workbook.active ()

ws = wb.active

WB's _active_sheet_index property, which by default is 0. To get the first worksheet.
After you modify the value, you can get the worksheet that corresponds to the value.

You can also create a new worksheet that requires the use of the Openpyxl.workbook.Workbook.create_sheet () method

To add a new worksheet:

#在默认工作表后面创建一个新的工作表。ws1 = wb.create_sheet("twosheet")#在默认工作表前面创建一个新的工作表。0 为索引值。ws2 = wb.create_sheet(‘onesheet‘,0)

When you create a worksheet, the Sheet1,sheet2 is automatically named by default,...

You can use the Title property to change the Sheet1,sheet2,.... and other names.
For example:

ws1.title = "2sheet"

By default, the background color of title is white and can be changed to:
You can change this provided RRGGBB color code to the Sheet_properties.tabcolor property: The effect I did not make, pending research.

ws.sheet_properties.tabColor = "1072BA"

You can put the name of a worksheet after it was created as a key to the workbook.

ws3 = wb[‘onesheet‘]print(ws3)

The output values are:
<worksheet "Onesheet" >

Explains which worksheet you can choose to use to write data.

To view the names of all worksheets in a workbook:
Openpyxl.workbook.Workbook.sheetnames () property

print(wb.sheetnames)  #打印出来为list。

You can choose to cycle through the worksheet name:
For sheet in WB:
Print (Type (sheet.title))
Print (Sheet.title)
#打印出来为str.

You can create a copy of a worksheet in a single workbook:
Openpyxl.workbook.Workbook.copy_worksheet () Method:
Source = wb.active
target = Wb.copy_worksheet (source)
#默认将工作表添加到末尾.

• Attention:

Only cells (including values, styles, hyperlinks, and comments) and some sheet properties (including dimensions, formatting, and properties) are copied. All other workbook/sheet properties are not copied-examples, charts.
You cannot copy worksheets between workbooks. If the workbook is open in read-only or write-only mode, you cannot copy the worksheet.

# # #插入数据
To access cells and modify cell contents:

Cells can be accessed directly as keys to the worksheet.

ws = wb[‘Sheet‘]print(ws)

Attention:
Before you can access it, you must first make it work on the sheet worksheet.
c = ws[' A4 ')
Print (c)

This will return the cell at A4 or create one if it does not already exist. Values can be assigned directly:

ws[‘A4‘] = 4

And also
Openpyxl.worksheet.Worksheet.cell () method.

This uses row and column notation to provide access to cells:

d = ws.cell(row=4,column=2,value=10)print(type(d))print(d)

Attention:
When a worksheet is created in memory, it does not contain cells. They are created on the first visit.

Because of this feature, even if they are not assigned a value, they are created in memory by scrolling through the cells instead of directly accessing them.

for i in range(1,101):for j in range(1,101):    ws.cell(row=i,column=j)    print(type(j))    print(j)#将在内存中创建100x100的单元格，无需任何操作。

You can use slices to access the range of cells:

cell_range = ws[‘A1‘:‘C2‘]print(type(cell_range))print(cell_range)#type is tuple.

The range of rows or columns can be similarly obtained:

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

You can also use:
Openpyxl.worksheet.Worksheet.iter_rows () Method:

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

Openpyxl.worksheet.Worksheet.iter_cols ()
Method would return columns:

for col in ws.iter_rows(min_row=1,max_col=3,max_row=2):for cell in col:    print(cell)

All rows or columns that need to traverse the file, using the
Openpyxl.worksheet.Worksheet.rows () Property:

#遍历文件的所有行ws = wb.activews[‘C9‘] = ‘li yuan jie‘print(tuple(ws.rows))

or use
Openpyxl.worksheet.Worksheet.columns () Property:

#遍历文件的所有列:ws = wb.activews[‘C9‘] = ‘li yuan jie‘print(tuple(ws.columns))

# # # # #Data Storage
Openpyxl.cell.Cell, we can assign it a value:

c = ws[‘A1‘]    #指定A1单元格c.value = ‘li yuan jie‘print(c.value)d = ws[‘B1‘]d.value = 3.14print(d.value)

You can also enable type and format inference:

c = ws[‘A2‘]c.value = ‘12%‘print(type(c.value))  #type is strprint(c.value)import datetimed = ws[‘B2‘]d.value = datetime.datetime.now()  print(type(d.value))  #type is ‘datetime.datetime‘print(d.value)c.value = ‘31.50‘print(type(c.value))   #type is strprint(c.value)

Saving to a file
The simplest and safest to save a workbook are by using the:
Openpyxl.workbook.Workbook.save () method of the Openpyxl.workbook.Workbook object:

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

Note: This action overwrites the existing file.

You can specify the property template = True to save the workbook as a template:

wb = load_workbook(‘document.xlsx‘)wb.template = Truewb.save(‘document_template.xltx‘)

or set this property to False (the default) to save as a document:
WB = Load_workbook (' Document_template.xltx ')
Wb.template = False
Wb.save (' document.xlsx ', as_template=false)

Note the data properties and extensions for the Excel table.

# # #加载一个文件
Import
Openpyxl.load_workbook () to open an existing workbook:

from openpyxl import load_workbookwb2 = load_workbook(‘onepyxl.xlsx‘)print(wb2.sheetnames)

If there is any deficiency, please correct me.

Reference:
https://openpyxl.readthedocs.io/en/latest/
https://openpyxl.readthedocs.io/en/2.5/

Python3 operation Excel (i)