Python3 working with Excel OPENPYXL modules

Python and Excel

Mounting module

The modules used in this example are:

Openpyxl

Version is 2.4.8

See previously published articles (Python's PIP module installation method)

Python processing Excel table

Using modules: OPENPYXL (2.4.8)

Basic Use Method

1. First import the module: Import OPENPYXL

2. Open an Excel file that already exists:

Wb=openpyxl.load_workbook (' example.xlsx ')

(Files and scripts are placed in the same directory, if not, you need to add a path

Wb=wpenpyxl.load_workbook (R ' c:\maxingpython\example.xlsx '))

At this point WB is a Workbook object that represents the entire Excel file, similar to a file object representing a text document.

3. Get the specific table in the Excel file:

• Sheet=wb.get_sheet_name (' Sheet1 ') #根据表名获取

• sheet=wb[' Sheet1 '] #更简洁的方法

• Sheet=wb.get_active_sheet () #获取当前激活的表 (that is, open the table that Excel opens by default)

• sheet=wb.active# to get the currently active table by property

Sheet.title can get the name of the table.

4. Get rows and columns in a table

• Sheet.rows

• Sheet.columns

Note that the rows and columns obtained here are a generator object :

>>> sheet.rows

5. Get the total number of rows and columns of a table

• Total number of rows: Len (List (sheet.rows)) columns: Len (List (sheet.columns))

• Call Properties directly: Sheet.max_row;sheet.max_column

6. Get the Cell object

cell=sheet[' A1 '] #获取了一个单元格对象

Cell.value# gets the value of the cell

This method uses the default lines (numbers) and columns (letters) of Excel to get the corresponding cells.

Another way is to specify named parameters directly:

• Cell=sheet.cell (row=2,column=2)

• cell.value# get the value of a cell

• cell.row# get the corresponding row

• cell.column# get the corresponding column

• cell.cordinate# get the corresponding coordinates

Example

>>> Cell=sheet.cell (row=2,column=2)

>>> Cell.value

Apple

>>> Cell.row

2

>>> Cell.column

B

>>> cell.coordinate

' B2 '

This method uses numbers to represent rows and columns ( first behavior 1 is not 0)

These two tools convert each other:

>>> openpyxl.utils.cell.column_index_from_string (' A ')

1

>>> openpyxl.utils.cell.column_index_from_string (' AC ')

29

>>> Openpyxl.utils.cell.get_column_letter (1)

A

>>> Openpyxl.utils.cell.get_column_letter (29)

' AC '

>>>

7. Get data for a region

>>> sheet[' a1:c3 ']

((<cell ' Sheet1 '. a1= "" >, <cell ' Sheet1 '. b1= "";  <cell ' Sheet1 '. c1= "" >), (<cell ' Sheet1 '. a2= "" >, <cell ' Sheet1 '. b2= "" >, <cell " Sheet1 '. c2= ' >), (<cell ' Sheet1 '. a3= "" >, <cell ' Sheet1 '. b3= "" >, <cell ' Sheet1 '. c3= "" >)

 

>>> type (sheet[' a1:c3 ')

<class ' tuple ' = "";

 

&G T;>> Import pprint# above print too messy, import beautiful print module

>>> pprint.pprint (sheet[' a1:c3 ')

((<cell ' Sheet1 '. a1= ' ">, <cell ' Sheet1 '. b1=" ">, <cell ' Sheet1 '. c1=" ">),

 

(<cell ' Sheet1 '. a2= "" >, <cell ' Sheet1 '. b2= "" >, <cell ' Sheet1 '. c2= "" >),

 

(< Cell ' Sheet1 '. a3= "" >, <cell ' Sheet1 '. b3= "" >, <cell ' Sheet1 '. c3= "" >)

 

Notice that sheet[' a1:c3 ' gets a tuple that consists of a tuple.

8. Get all the table names

>>> Wb.get_sheet_names ()

[' My first sheet ']

The return is a list.

Writing data to a table

1. Create a new Excel file

>>> from OPENPYXL import Workbook

>>> Wb=workbook ()

>>> WB

This creates a workbook object that does not actually generate an Excel file, and you need to call the Save function after you actually generate the Excel file.

The default Workbook object will create a table "sheet"

>>> wb.active

<worksheet ' sheet ' = "" >

>>> sheet=wb.active

>>> sheet.title= ' My first sheet '

>>> Sheet.title

' My first sheet '

You can modify the table name by using the Sheet.title property.

2. Save

>>> wb.save (' test.xlsx ')

The test.xlsx file is not generated at this time in the working directory.

3. Adding and Deleting tables

>>> Wb.create_sheet ()

<worksheet ' Sheet1 ' = "" >

>>> Wb.get_sheet_names ()

[' Sheet ', ' Sheet1 ']

>>> wb.create_sheet (' names ')

<worksheet ' names ' = "" >

>>> Wb.get_sheet_names ()

[' Sheet ', ' Sheet1 ', ' names ']

>>> Wb.create_sheet (index=0,title= ' first tab ')

<worksheet ' first= ' "tab ' =" ">

>>> Wb.get_sheet_names ()

[' first tab ', ' Sheet ', ' Sheet1 ', ' names ']

>>> Wb.create_sheet (Index=len (Wb.get_sheet_names ()), title= ' last tab ')

<worksheet ' last= ' "tab ' =" ">

>>> Wb.get_sheet_names ()

[' first tab ', ' Sheet ', ' Sheet1 ', ' names ', ' last tab ']

>>> Wb.remove_sheet (wb[' first tab ')

>>> Wb.get_sheet_names ()

[' Sheet ', ' Sheet1 ', ' names ', ' last tab ']

4. Writing information to a cell

>>> sheet[' A1 ']= ' Hello world! '

>>> Sheet.cell (row=2,column=2). value= ' Zhang San '

>>> sheet[' B2 '].value

' Zhang San '

5. Cell font Style

The first thing to import is the appropriate function:

from openpyxl.styles import Patternfill, Border, Side, Alignment, Protection, Font

The default settings are as follows:

>>> font=Font (name=' Calibri ',

... size=11,

... bold=false,

... italic=false,

... vertalign=none,

... underline=' None ',

... strike=false,

... color=' FF000000 ')

>>> Fill=patternfill (fill_type=none,

... start_color=' FFFFFFFF ',

... end_color=' FF000000 ')

>>> border=border (left=Side (border_style=none,

... color=' FF000000 '),

... right=Side (border_style=none,

... color=' FF000000 '),

... top=Side (border_style=none,

... color=' FF000000 '),

... bottom=Side (border_style=none,

... color=' FF000000 '),

... diagonal=Side (border_style=none,

... color=' FF000000 '),

... diagonal_direction=0,

... outline=Side (border_style=none,

... color=' FF000000 '),

... vertical=Side (border_style=none,

... color=' FF000000 '),

... horizontal=Side (border_style=none,

... color=' FF000000 ')

... )

>>> Alignment=Alignment (Horizontal=' General ',

... vertical=' Bottom ',

... text_rotation=0,

... wrap_text=false,

... shrink_to_fit=false,

... indent=0)

>>> Number_format=' General '

>>> Protection=Protection (locked=true,

... hidden=false)

>>>

Example:

Set a style for a cell

>>> cell1=sheet[' A1 ']

>>> cell2=sheet[' A2 ']

>>> Ft1=font (color=colors. RED)

>>> Ft2=font (color= ' 00ff00 ', size=30)

>>> CELL1.FONT=FT1

>>> cell2.font=ft2

Copy style:

>>> fromopenpyxl.stylesimportFont

>>> from copyimportcopy

>>>

>>> ft1=Font (name=' Arial ', size=14)

>>> ft2=copy (FT1)

>>> ft2. Name=' Tahoma '

>>> ft1. Name ' Arial '

>>> ft2. Name ' Tahoma '

>>> ft2. size# copied from the14.0

Custom styles:

>>> from openpyxl.styles import Namedstyle, Font, Border, Side

>>> highlight = Namedstyle (name= ' highlight ')

>>> Highlight.font = Font (bold=true, size=20)

>>> bd = Side (style= ' thick ', color= ' 000000 ')

>>> Highlight.border = Border (LEFT=BD, TOP=BD, RIGHT=BD, BOTTOM=BD)

Once created, it can be applied to workbook.

>>> Wb.add_named_style (Highlight) #第一步

>>> ws[' A1 '].style = highlight# Second Step

Once registered assign the style using just the name:

>>> ws[' D5 '].style = ' Highlight ' #以后就可以直接调用字符串形式了

7. merging and splitting cells

Import OPENPYXL

Wb=openpyxl.load_workbook (' two.xlsx ')

Sheet=wb.active

Sheet.merge_cells (' a1:a2 ')

Wb.save (' three.xlsx ')

The same is sheet.unmerge_cells (' a1:a2 '), but in the interactive environment can be, written in the py file will be an error, for unknown reasons.

Python3 working with Excel OPENPYXL modules