Use Python to automate Excel

0 Python Excel library comparison

Let’s start with a comparison of python libraries that can work with Excel (nine libraries in total) :

1 Python XLRD Read operations on Excel

1.1 XLRD Module Introduction

(1) What is XLRD module?

Python uses XLRD and XLWT to operate excel. XLRD is used to read Excel, and XLWT is used to write Excel.

(2) Why use XLRD module?

In UI automation or interface automation data maintenance is a core, so this module is very useful.

XLRD module can be used to read Excel data, very fast, recommended use!

The official document: XLRD. Readthedocs. IO/en/latest /

1.2 Installing the XLRD Module

To python’s official website to download pypi.python.org/pypi/xlrd module… The environment.
Or PIP Install XLRD in a CMD window

pip install xlrd
Copy the code

XLRD has been installed in Anaconda.

1.3 Introduction

Data types of common cells

Empty
String (text)
number
date
boolean
error
A blank form

The import module

import xlrd
Copy the code

Open Excel file to read data

data = xlrd.open_workbook(filename)If the path or file name has Chinese characters, add an r to the front of it
Copy the code

Common functions

The most important method in Excel is the operation of book and Sheet

(1) Get a worksheet from book (Excel file)

table = data.sheets()[0]          			Get by index order
table = data.sheet_by_index(sheet_indx) 	Get by index order
table = data.sheet_by_name(sheet_name)		Get by name

All three of these functions return an xlrd.sheet.sheet () object

names = data.sheet_names()    				Return the names of all worksheets in book
data.sheet_loaded(sheet_name or indx)   	Check if a sheet has been imported
Copy the code

(2) Line operation

nrows = table.nrows
    Table. Nrows does not contain () after table.

table.row(rowx)
    # return a list of all cell objects in the row, no different from the tabel.raw() method.

table.row_slice(rowx)
    Return a list of all the cell objects in the row

table.row_types(rowx, start_colx=0, end_colx=None)
    Return a list of data types for all cells in the row;
    # return a list of logical values, 0 if empy, 1 otherwise

table.row_values(rowx, start_colx=0, end_colx=None)
    Return a list of data for all cells in the row

table.row_len(rowx)
    # return the effective cell length of the row, that is, how many data are in the row
Copy the code

(3) operation of column (Colnum)

ncols = table.ncols
    Get the number of valid columns in the list

table.col(colx, start_rowx=0, end_rowx=None)
    Return a list of all the cell objects in the column

table.col_slice(colx, start_rowx=0, end_rowx=None)
    Return a list of all the cell objects in the column

table.col_types(colx, start_rowx=0, end_rowx=None)
    Return a list of the data types of all the cells in the column

table.col_values(colx, start_rowx=0, end_rowx=None)
    Return a list of data for all cells in the column
Copy the code

(4) Operation of cells

table.cell(rowx,colx)
    # Return cell object

table.cell_type(rowx,colx)
    # return the data type in the corresponding position cell

table.cell_value(rowx,colx)
    # return the data in the corresponding position cell
Copy the code

1.4 Actual combat training

We first put the following data in the table and click Save:

Read using the XLRD module:

import xlrd

xlsx = xlrd.open_workbook('./3_1 XLRD reading exercise.xlsx ')

Xlsx.sheet_by_name ("sheet1")
Xlsx.sheet_by_index (3)
table = xlsx.sheet_by_index(0)

Get a single table value (2,1) to get the value of the cell in row 3, column 2
value = table.cell_value(2.1)
print("Row 3, column 2 values are",value)

Get the number of rows in the table
nrows = table.nrows
print("Form in common",nrows,"Line")

Select * from table 4;
name_list = [str(table.cell_value(i, 3)) for i in range(1, nrows)]
print(All values in column 4:,name_list)
Copy the code

Print result:

Introduction to list generation:

List generative learning links:

www.liaoxuefeng.com/wiki/101695…

2 Python XLWT write operation Excel (`XLS format only!`)

XLWT can be used to write new Excel tables or make changes to existing tables. It is also fast and recommended!

The official document: XLWT. Readthedocs. IO/en/latest /

2.1 PIP XLWT Installation

pip install xlwt
Copy the code

XLWT has been installed in Anaconda.

2.2 Create a new table using XLWT and write

At first there were only two files in the directory:

Write a new XLWT table writer:

# 3.2.2 Create a new table using XLWT and write
def fun3_2_2() :
    # Create a new Workbook
    workbook = xlwt.Workbook(encoding= 'ascii')

    Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    Write to the table
    worksheet.write(0.0.Content of "1")
    worksheet.write(2.1."Content 2")

    # save
    workbook.save("Newly created table.xls")
Copy the code

The generated table looks like this:

2.3 XLWT Sets the font format

Example program:

# 3.2.3 XLWT Sets font format
def fun3_2_3() :
    # Create a new Workbook
    workbook = xlwt.Workbook(encoding= 'ascii')

    Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    Initialize the style
    style = xlwt.XFStyle()

    Create a font for the style
    font = xlwt.Font()
    font.name = 'Times New Roman'   # font
    font.bold = True                # bold
    font.underline = True           # the underline
    font.italic = True              # italics

    # Set style
    style.font = font

    Write to the table
    worksheet.write(0.0.Content of "1")
    worksheet.write(2.1."Content 2",style)

    # save
    workbook.save("Newly created table.xls")
Copy the code

The effect is as follows:

2.4xlwt sets the column width

The value of column width in XLWT is expressed as 1/256 of the default font 0.

The default width used for XLWT creation is 2960, which is the width of 11 character zeros

So we can set the column width using the following method:

Width = 256 x 20 256 is the measurement unit, and 20 indicates the width of 20 characters

Example program:

# 3.2.4 Set the column width
def fun3_2_4() :
    # Create a new Workbook
    workbook = xlwt.Workbook(encoding= 'ascii')

    Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    Write to the table
    worksheet.write(0.0.Content of "1")
    worksheet.write(2.1."Content 2")

    Set the column width
    worksheet.col(0).width = 256*20

    # save
    workbook.save("Newly created table.xls")
Copy the code

The effect is as follows:

2.5xlwt sets the row height

There is no specific function in XLWT to set the default column width and row height

The line height is set in the cell’s style, and you can determine the line height by the amount of text entered by wrapping

Example program:

# 3.2.5 Set line height
def fun3_2_5() :
    # Create a new Workbook
    workbook = xlwt.Workbook(encoding= 'ascii')

    Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    Write to the table
    worksheet.write(0.0.Content of "1")
    worksheet.write(2.1."Content 2")

    # set the line height
    style = xlwt.easyxf('font:height 360; ')  # 18pt, small initial type
    row = worksheet.row(0)
    row.set_style(style)

    # save
    workbook.save("Newly created table.xls")
Copy the code

The effect is as follows:

2.6xLWt merges columns and rows

Example program:

# 3.2.6 Merge columns and rows
def fun3_2_6() :
    # Create a new Workbook
    workbook = xlwt.Workbook(encoding= 'ascii')

    Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    Write to the table
    worksheet.write(0.0.Content of "1")
    
    Merge columns 0 to 3 from rows 1 to 2
    worksheet.write_merge(1.2.0.3.'Merge Test')

    # save
    workbook.save("Newly created table.xls")
Copy the code

The effect is as follows:

2.7xlwt adds borders

Example program:

# 3.2.7 Add borders
def fun3_2_7() :
    # Create a new Workbook
    workbook = xlwt.Workbook(encoding= 'ascii')

    Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    Write to the table
    worksheet.write(0.0.Content of "1")
    
    # Set the border style
    borders = xlwt.Borders()  # Create Borders
    
    # 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.
    # DASHED dotted line
    # NO_LINE no
    # THIN solid line
    
    borders.left = xlwt.Borders.DASHED
    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.Content of the '1', style)

    worksheet.write(2.1."Content 2")

    # save
    workbook.save("Newly created table.xls")
Copy the code

The effect is as follows:

2.8xlwt sets the background color for the cell

Example program:

Set the cell background color
def fun3_2_8() :
    # Create a new Workbook
    workbook = xlwt.Workbook(encoding= 'ascii')

    Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    Write to the table
    worksheet.write(0.0.Content of "1")

    # create style
    pattern = xlwt.Pattern()
    
    # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    
    # 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...
    pattern.pattern_fore_colour = 5
    style = xlwt.XFStyle()
    style.pattern = pattern

    # Use styles
    worksheet.write(2.1."Content 2",style)
Copy the code

The effect is as follows:

2.9 XLWT sets cell alignment

The Alignment of cells is set using alignments in XLWT, where HORz represents horizontal Alignment and VERT represents vertical Alignment.

VERT_TOP = 0x00 Align the upper end VERT_CENTER = 0x01 Align the middle (vertically) VERT_BOTTOM = 0x02 Align the lower end HORZ_LEFT = 0x01 Align the left end HORZ_CENTER = 0x02 Center aligned (horizontally) HORZ_RIGHT = 0x03 Right aligned

Example program:

# Set cell alignment
def fun3_2_9() :
    # Create a new Workbook
    workbook = xlwt.Workbook(encoding= 'ascii')

    Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    Write to the table
    worksheet.write(0.0.Content of "1")

    # Set style
    style = xlwt.XFStyle()
    al = xlwt.Alignment()
    # VERT_TOP = 0x00 Align the upper end
    # VERT_CENTER = 0x01 center aligned (vertically)
    # VERT_BOTTOM = 0x02 Low end alignment
    # HORZ_LEFT = 0x01 Left aligned
    # HORZ_CENTER = 0x02 Center aligned (horizontally)
    # HORZ_RIGHT = 0x03 Right end alignment
    al.horz = 0x02  # Set horizontal center
    al.vert = 0x01  # Set vertical center
    style.alignment = al

    # align write
    worksheet.write(2.1."Content 2",style)

    # save
    workbook.save("Newly created table.xls")
Copy the code

The effect is as follows:

3 Python Xlutils Modify Excel

Xlutils can be used to copy the original Excel or modify it based on the original Excel and save it.

The official document: xlutils. Readthedocs. IO/en/latest /

3.1 PIP Xlutils Installed

pip install xlutils
Copy the code

Installation process:

3.2 Copy Source Files to Xlutils (Used with XLRD)

The form is as follows:

Example program:

# 3.3.2 Copy source files
def fun3_3_2() :
    workbook = xlrd.open_workbook('3_3 xlutils modification exercise.xlsx ')  # Open workbook
    new_workbook = copy(workbook)  Copy XLRD objects into XLWT objects
    new_workbook.save("new_test.xls")  Save the workbook
Copy the code

The effect is as follows:

Content as follows:

But all the forms are gone.

3.3 Xlutils reads and writes (that is, modifies) Excel table information

Example program:

Xlutils reads and writes Excel table information
def fun3_3_3() :
    # file_path: the file path, including the full name of the file
    # formatting_info=True: Keep Excel as it was (using XLSX files)
    workbook = xlrd.open_workbook('3_3 xlutils modification exercise.xlsx ')
    
    new_workbook = copy(workbook)  Copy XLRD objects into XLWT objects

    Read table information
    sheet = workbook.sheet_by_index(0)
    col2 = sheet.col_values(1)  Extract the second column
    cel_value = sheet.cell_value(1.1)
    print(col2)
    print(cel_value)

    Write table information
    write_save = new_workbook.get_sheet(0)
    write_save.write(0.0."Xlutils write!")

    new_workbook.save("new_test.xls")  Save the workbook
Copy the code

The effect is as follows:

Copy the source file information and append it:

4 Python Xlwings read, write, and modify operations Excel

Xlwings is much more luxurious than XLRD, XLWT and Xlutils. It has the following features:

Xlwings can easily read and write data in Excel files and modify cell formats
You can seamlessly connect to Matplotlib and PANDAS, read and write numpy and PANDAS data types, and import Matplotlib visualization charts into Excel.
You can call programs written by VBA in Excel files, or you can have VBA call programs written in Python.
Open source, free, constantly updated

Official website: www.xlwings.org/

The official document: docs.xlwings.org/en/stable/a…

4.1 PIP Installing XLWings

pip install xlwings
Copy the code

4.2 Basic Operations

Import library

import xlwings as xw 
Copy the code

Open Excel program, default Settings: program visible, open only do not create a workbook

app = xw.App(visible=True,add_book=False)
# Create a new workbook (If you don't follow another code, Excel will just flash by and leave)
wb = app.books.add()
Copy the code

Open an existing workbook (absolute and relative paths supported)

wb = app.books.open('example.xlsx')
# Use the following tip directly when practicing
#wb = xw.Book('example.xlsx')
This way, you won't open new Excel too often
Copy the code

Save workbook

wb.save('example.xlsx')
Copy the code

Exit workbook (may be omitted)

wb.close()
Copy the code

Exit Excel

app.quit()
Copy the code

Three examples:

(1) Open the existing Excel document

# import xlwings
import xlwings as xw

# Open Excel program, default Settings: program visible, open only, do not create a workbook, screen update closed
app=xw.App(visible=True,add_book=False)
app.display_alerts=False
app.screen_updating=False

File location: Filepath, open the test file, then save, close, end the program
filepath=r'g:\Python Scripts\test.xlsx'
wb=app.books.open(filepath)
wb.save()
wb.close()
app.quit()
Copy the code

(2) Create an Excel file, name it test. XLSX, and save it in drive D

import xlwings as xw

app=xw.App(visible=True,add_book=False)
wb=app.books.add()
wb.save(r'd:\test.xlsx')
wb.close()
app.quit()
Copy the code

(3) Enter values in cells

Create a new test.xlsx, type “Life” in the first cell in Sheet1, save and close and exit the Excel program.

 import xlwings as xw
    
 app=xw.App(visible=True,add_book=False)
 wb=app.books.add()
    
 # wb is the new workbook. Assign values to cell A1 in Sheet1 of WB
 wb.sheets['sheet1'].range('A1').value='life'
 wb.save(r'd:\test.xlsx')
 wb.close()
 app.quit()
Copy the code

Open the saved test.xlsx, type “Too short” in the second cell in Sheet2, then save and close and exit the Excel program

 import xlwings as xw
    
 app=xw.App(visible=True,add_book=False)
 wb=app.books.open(r'd:\test.xlsx')
    
 # wb is the new workbook. Assign values to cell A1 in Sheet1 of WB
 wb.sheets['sheet1'].range('A1').value='short'
 wb.save()
 wb.close()
 app.quit()
Copy the code

Master the above code, has been fully able to Excel as a TXT text for data storage, you can also read Excel file data, after calculation, and save the results in Excel.

4.3 Reference workbooks, worksheets, and cells

(1) Reference the workbook by name, noting that the workbook should be opened first

wb=xw.books['Name of workbook']Copy the code

(2) Reference activity workbook

wb=xw.books.active
Copy the code

(3) Refer to the sheet in the workbook

sht=xw.books['Workbook name'].sheets['The name of the sheet['] # wb=xw.books[']The name of the workbook'] SHT =wb. Sheets [name]Copy the code

(4) Quote the activity sheet

sht=xw.sheets.active
Copy the code

(5) Reference cell A1

rng=xw.books['Workbook name'].sheets['The name of the sheetSHT =xw.books[']Workbook name '].sheets['Sheet's name']
rng=sht.range('A1')
Copy the code

(6) Refer to the cells on the activity sheet

# Note that Range starts with uppercase letters
rng=xw.Range('A1')

The full reference path of the cell is:
The first cell of the first Sheet of the first Workbook of the first Excel program
xw.apps[0].books[0].sheets[0].range('A1'SHT =xw.books['name'].sheets['name']

Cell # A1RNG = SHT [' A1['] # RNG = SHT [']A1:B5RNG = SHT [0,1] RNG = SHT [:10,:10] RNG = SHT [:10,:10] Range(1,1) #A1:C3 Range((1,1),(3,3))Copy the code

Reference cell:

rng = sht.range('a1')
#rng = sht['a1']
# RNG = SHT [0,0] # RNG = SHT [0,0
Copy the code

Reference area:

rng = sht.range('a1:a5')
#rng = sht['a1:a5']
5, 0] # RNG = SHT [:
Copy the code

4.4 Writing and reading Data

1. Write data

(1) Select start cell A1 and write string ‘Hello’

sht.range('a1').value = 'Hello'
Copy the code

(2) Write the list

# row storage: store the list [1,2,3] in A1: C1
sht.range('A1').value=[1.2.3]
# column storage: store lists [1,2,3] in A1:A3
sht.range('A1').options(transpose=True).value=[1.2.3]
Store the 2x2 table, a two-dimensional array, in A1:B2, as in row 1,2, and row 2, 3, and 4
sht.range('A1').options(expand='table') = [[1.2], [3.4]]
Copy the code

Default: A1:D1 Write 1,2,3, and 4 respectively

sht.range('a1').value = [1.2.3.4]
Copy the code

Is equivalent to

sht.range('a1:d1').value = [1.2.3.4]
Copy the code

Insert by column: A2:A5 write 5,6,7,8 respectively

You might be thinking:

sht.range('a2:a5').value = [5.6.7.8]
Copy the code

However, you’ll notice that xlwings will still be handled in rows, with the top line equivalent to:

sht.range('a2').value = [5.6.7.8]
Copy the code

Correct grammar:

sht.range('a2').options(transpose=True).value = [5.6.7.8]
Copy the code

Transpose means that the word is typed correctly, and if you type a wrong word, it does not report an error. Instead, it writes on the default line (don’t ask me how I know).

Multi-line input uses a two-dimensional list:

sht.range('a6').expand('table').value = [['a'.'b'.'c'], ['d'.'e'.'f'], ['g'.'h'.'i']]
Copy the code

2. Read data

(1) Read a single value

# read the value of A1 into variable A
a=sht.range('A1').value
Copy the code

(2) Read the value into the list

Read the values A1 through A2 into the a list
a=sht.range('A1:A2').value
Read the first and second rows as a two-dimensional array
a=sht.range('A1:B2').value
Copy the code

Select a column of data

Count the number of rows of cells first (if continuous cells are required)

rng = sht.range('a1').expand('table')
nrows = rng.rows.count
Copy the code

It can then be read in the exact range

a = sht.range(f'a1:a{nrows}').value
Copy the code

Select a row of data

ncols = rng.columns.count
# with sliced
fst_col = sht[0,:ncols].value
Copy the code

4.5 Common Functions and Methods

1.Book Common API for workbook

Wb =xw.books[' Workbook name']
Copy the code

Wb.activate () activates the current workbook
Wb. fullname Returns the absolute path to the workbook
Wb. name Returns the name of the workbook
Wb. save(path=None) Saves the workbook. The default path is the original path of the workbook, or the path of the script if it is not saved
Wb.close () closes the workbook

Code examples:

# reference Excel program, the current workbook
wb=xw.books.acitve
Return the absolute path to the workbook
x=wb.fullname
Return the name of the workbook
x=wb.name
# save the workbook. The default path is the original path of the workbook, or the path of the script if it is not saved
x=wb.save(path=None)
Close the workbook
x=wb.close()
Copy the code

2.sheet common API

# reference a specified sheet
sht=xw.books['Workbook name'].sheets['Sheet name']
# Active Sheet is the active worksheet
sht.activate()
Clear the content and format of the sheet
sht.clear()
Clear the contents of the sheet
sht.contents()
Get the name of sheet
sht.name
# delete sheet
sht.delete
Copy the code

3. Range common apis

# reference the cell of the currently active worksheet
rng=xw.Range('A1')
# Add hyperlinks
# rng.add_hyperlink(r'www.baidu.com',' baidu ',' hint: click to link to Baidu ')
Get the address of the current range
rng.address
rng.get_address()
Clear the contents of range
rng.clear_contents()
# Clear up formatting and content
rng.clear()
# get the background color of range, return RGB value as a tuple
rng.color
# Set the range color
rng.color=(255.255.255)
# Clear the background color of range
rng.color=None
Get the first range column
rng.column
Return data for the cells in range
rng.count
# returns current_region
rng.current_region
Return to CTRL + Direction
rng.end('down')
Get the formula or enter the formula
rng.formula='=SUM(B1:B5)'
# array formula
rng.formula_array
Get the absolute address of the cell
rng.get_address(row_absolute=True, column_absolute=True,include_sheetname=False, external=False)
Get the column width
rng.column_width
Return the total width of range
rng.width
Get the hyperlink from range
rng.hyperlink
Get the last lower-right cell in range
rng.last_cell
# range translation
rng.offset(row_offset=0,column_offset=0)
#range resize the range
rng.resize(row_size=None,column_size=None)
The first line of the # range line
rng.row
If all rows are of the same height, None is returned
rng.row_height
# return the total height of range
rng.height
# return the number of rows and columns in range
rng.shape
Return the sheet in which range is located
rng.sheet
# return all rows of range
rng.rows
The first line of # range
rng.rows[0]
# range total number of rows
rng.rows.count
Return all columns of range
rng.columns
# return the first column of range
rng.columns[0]
# return the number of columns in range
rng.columns.count
# All range sizes are adaptive
rng.autofit()
# All column widths are adaptive
rng.columns.autofit()
All line widths are adaptive
rng.rows.autofit()
Copy the code

4. API for books workbook collection

Create a workbook
xw.books.add()
# reference the current active workbook
xw.books.active
Copy the code

4. A collection of sheets

Create a new worksheet
xw.sheets.add(name=None,before=None,after=None)
# reference current active sheet
xw.sheets.active
Copy the code

4.6 Data Structure

1. One-dimensional data

Python lists, which can be exchanged with rows and columns in Excel, are one-dimensional lists in Python, which default to a row in Excel.

import xlwings as xw

sht=xw.sheets.active

Add 1, 2, and 3 to cells A1, B1, C1, respectively
sht.range('A1').value=[1.2.3]

Store the values of cells A1, B1, C1 into the list1 list
list1=sht.range('A1:C1').value

Add 1, 2, and 3 to cells A1, A2, and A3, respectively
sht.range('A1').options(transpose=True).value=[1.2.3]

Add the median A1, A2, A3 cells to the list1 list
list1=sht.range('A1:A3').value
Copy the code

2. Two-dimensional data

A two-dimensional list in Python that can be converted to rows and columns in Excel. A two-dimensional list, the elements of a list or a list. In Excel, a list element in a two-dimensional list that represents a column in an Excel table. Such as:

Enter a1, A2, A3 into the first column, b1, B2,b3 into the second columnList1 = [[a1,'a2'.'a3'], ['b1'.'b2'.'b3']]
sht.range('A1').value=list1
Copy the code

Assign the value A1: B3 to the 2-d list list1
list1=sht.range('A1:B3').value
Copy the code

3. Region selection table in Excel

# select the first column
rng=sht. range('A1').expand('down')
rng.value=['a1'.'a2'.'a3']
Copy the code

# select the first line
rng=sht.range('A1').expand('right')
rng=['a1'.'b1']
Copy the code

# select table
rng.sht.range('A1').expand('table') RNG. Value = [[a1,'a2'.'a3'], ['b1'.'b2'.'b3']]
Copy the code

4.7xlwings generates charts

Methods for generating diagrams

import xlwings as xw
app = xw.App()
wb = app.books.active
sht = wb.sheets.active

chart = sht.charts.add(100.10)  # 100, 10 are the position coordinates of the chart. In pixels.
chart.set_source_data(sht.range('A1').expand())  The parameter is the data area in the table.
# chart. Chart_type = I # Set the chart type.
chart.api[1].ChartTitle.Text = i          Set the title of the chart.
Copy the code

Sample code:

import xlwings as xw
app = xw.App()
wb = app.books.active
sht = wb.sheets.active
Generate data for the chart
sht.range('A1').value = [['time'.'number'], ['1'.2], ['2'.1], ['3'.3], ['4'.4], ['5'.5], ['6'.6]]
""" Chart type parameters, the ones annotated, cannot generate the corresponding chart """
dic = {
  '3d_area': -4098.'3d_area_stacked': 78.'3d_area_stacked_100': 79.'3d_bar_clustered': 60.'3d_bar_stacked': 61.'3d_bar_stacked_100': 62.'3d_column': -4100.'3d_column_clustered': 54.'3d_column_stacked': 55.'3d_column_stacked_100': 56.'3d_line': -4101.'3d_pie': -4102.'3d_pie_exploded': 70.'area': 1.'area_stacked': 76.'area_stacked_100': 77.'bar_clustered': 57.'bar_of_pie': 71.'bar_stacked': 58.'bar_stacked_100': 59.'bubble': 15.'bubble_3d_effect': 87.'column_clustered': 51.'column_stacked': 52.'column_stacked_100': 53.'cone_bar_clustered': 102.'cone_bar_stacked': 103.'cone_bar_stacked_100': 104.'cone_col': 105.'cone_col_clustered': 99.'cone_col_stacked': 100.'cone_col_stacked_100': 101.'cylinder_bar_clustered': 95.'cylinder_bar_stacked': 96.'cylinder_bar_stacked_100': 97.'cylinder_col': 98.'cylinder_col_clustered': 92.'cylinder_col_stacked': 93.'cylinder_col_stacked_100': 94.'doughnut': -4120.'doughnut_exploded': 80.'line': 4.'line_markers': 65.'line_markers_stacked': 66.'line_markers_stacked_100': 67.'line_stacked': 63.'line_stacked_100': 64.'pie': 5.'pie_exploded': 69.'pie_of_pie': 68.'pyramid_bar_clustered': 109.'pyramid_bar_stacked': 110.'pyramid_bar_stacked_100': 111.'pyramid_col': 112.'pyramid_col_clustered': 106.'pyramid_col_stacked': 107.'pyramid_col_stacked_100': 108.'radar': -4151.'radar_filled': 82.'radar_markers': 81.# 'stock_hlc': 88,
  # 'stock_ohlc': 89,
  # 'stock_vhlc': 90,
  # 'stock_vohlc': 91,
  # 'surface': 83,
  # 'surface_top_view': 85,
  # 'surface_top_view_wireframe': 86,
  # 'surface_wireframe': 84,
  'xy_scatter': -4169.'xy_scatter_lines': 74.'xy_scatter_lines_no_markers': 75.'xy_scatter_smooth': 72.'xy_scatter_smooth_no_markers': 73
}
w = 385
h = 241
n = 0
x = 100
y = 10
for i in dic.keys():
  xx = x + n % 3*w  # Used to generate x coordinates for chart placement.
  yy = y + n//3*h   # used to generate the y coordinates for chart placement.
  chart = sht.charts.add(xx, yy)
  chart.set_source_data(sht.range('A1').expand())
  chart.chart_type = i
  chart.api[1].ChartTitle.Text = i
  n += 1
wb.save('chart_ chart')
wb.close()
app.quit()
Copy the code

The effect is as follows:

4.8 Actual Combat Training

1. Xlwings Creates an Excel document

Example program:

# 3.4.2xlwings Create an Excle document
def fun3_4_2() :
    """ Visible Ture: Visible excel False: invisible excel add_book True: Open Excel and create a workbook False: do not create a workbook """
    app = xw.App(visible=True, add_book=False)

    # Create a new workbook (If you don't follow another code, Excel will just flash by and leave)
    wb = app.books.add()

    Save the workbook
    wb.save('example.xlsx')

    # Exit workbook
    wb.close()

    # exit Excel
    app.quit()
Copy the code

Example. XLSX added to folder after executing program:

The table is empty:

2. Xlwings opens an existing Excel document

The existing table looks like this:

Run the program:

# 3.4.3 xlwings Open an existing Excel file
def fun3_4_3() :
    Default Settings: Program visible, open only, do not create a workbook, screen update closed
    app = xw.App(visible=True, add_book=False)
    app.display_alerts = False
    app.screen_updating = False

    Open an existing Excel file
    wb=app.books.open('./3_4 xlwings modify operation exercise.xlsx ')

    Save the workbook
    wb.save('example_2.xlsx')

    # Exit workbook
    wb.close()

    # exit Excel
    app.quit()
Copy the code

Generate a new table:

As follows:

3. Xlwings reads and writes Excel

Example program:

Xlwings reads and writes Excel
def fun3_4_4() :
    Default Settings: Program visible, open only, do not create a workbook, screen update closed
    app = xw.App(visible=True, add_book=False)
    app.display_alerts = False
    app.screen_updating = False

    Open an existing Excel file
    wb=app.books.open('./3_4 xlwings modify operation exercise.xlsx ')

    Get sheet
    print(wb.sheets)
    sheet = wb.sheets[0]
    # sheet = wb.sheets["sheet1"]

    # Read Excel information
    cellB1_value = sheet.range('B1').value
    print("Cell B1 contains:",cellB1_value)

    Clear cell content and formatting
    sheet.range('A1').clear()

    # Write cell
    sheet.range('A1').value = "Xlwings writing"

    Save the workbook
    wb.save('example_3.xlsx')

    # Exit workbook
    wb.close()

    # exit Excel
    app.quit()
Copy the code

Execution effect:

4.9 For more information, see

Xlwings official documentation

Xlwings, Let Excel fly

Fly Excel with wings — Xlwings (4)

Python and Excel interaction — Xlwings

5 Python OpenPyXL reads, writes, and modifies operations on Excel

In OpenPyXL, three concepts are used: Workbooks, Sheets, and Cells.

A Workbook is an Excel worksheet;
A Sheet is a Sheet page in a worksheet;
A Cell is a simple Cell.

Openpyxl works around these three concepts: open the Workbook, locate the Sheet, and manipulate the Cell.

The official document: openpyxl. Readthedocs. IO/en/stable /

Official example:

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")
Copy the code

5.1 Basic Operations of OpenPyXL

1. Install

pip install openpyxl
Copy the code

Since I have installed it, the following message is displayed:

2. Open the file

(1) Create a

from  openpyxl import  Workbook 
# instantiation
wb = Workbook()
# activate worksheet
ws = wb.active
Copy the code

(2) Open the existing

from openpyxl  import load_workbook

wb = load_workbook('File name.xlsx')
Copy the code

3. Write data

# Method 1: Data can be allocated directly to cells (enter formula)
ws['A1'] = 42
You can append rows from the first column (from the bottom margin, from the left).
ws.append([1.2.3])
# Method 3: Python types are automatically converted
ws['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")
Copy the code

4. Create a sheet

# 1: Insert to the end (default)
ws1 = wb.create_sheet("Mysheet") 
# Method 2: Insert into the original position
ws2 = wb.create_sheet("Mysheet".0)
Copy the code

5. Select the sheet

The # sheet name can be indexed as a key
>>> ws3 = wb["New Title"]
>>> ws4 = wb.get_sheet_by_name("New Title")
>>> ws is ws3 is ws4
True
Copy the code

6. Look at the table name (sheet)

Display all table names
>>> print(wb.sheetnames)
['Sheet2'.'New Title'.'Sheet1']
Pass through all tables
>>> for sheet in  wb:
.    print(sheet.title)
Copy the code

7. Access cells

(1) Single cell access

Method a #
>>> c = ws['A4']
Select * from 'row'; The column column
>>> d = ws.cell(row=4, column=2, value=10)
# 3: Create as soon as you access
>>> for i in  range(1.101) :.        for j in range(1.101) :.           ws.cell(row=i, column=j)
Copy the code

(2) Multiple cell access

# By slicing
>>> cell_range = ws['A1':'C2']
# pass row (column)
>>> colC = ws['C']
>>> col_range = ws['C:D']
>>> row10 = ws[10]
>>> row_range = ws[5:10]
# by specifying the range (row → row)
>>> for row in  ws.iter_rows(min_row=1, max_col=3, max_row=2) :.   for cell in  row:
.       print(cell) <Cell Sheet1.A1> <Cell Sheet1.B1> <Cell Sheet1.C1> <Cell Sheet1.A2> <Cell Sheet1.B2> <Cell Sheet1.C2># by specifying the range (column → column)
>>> for row in  ws.iter_rows(min_row=1, max_col=3, max_row=2) :.   for cell in  row:
.       print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>
Pass through all methods 1
>>> ws = wb.active
>>> ws['C9'] = 'hello world'
>>> tuple(ws.rows) ((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>), (<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>), . (<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>), (<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))Pass through all methods 2
>>> tuple(ws.columns)
((<Cell Sheet.A1>,
<Cell Sheet.A2>,
<Cell Sheet.A3>,
...
<Cell Sheet.B7>,
<Cell Sheet.B8>,
<Cell Sheet.B9>),
(<Cell Sheet.C1>,
...
<Cell Sheet.C8>,
<Cell Sheet.C9>))
Copy the code

8. Save data

wb.save('File name.xlsx')
Copy the code

9. Other

(1) Change the button color of the sheet label

ws.sheet_properties.tabColor = "1072BA" # Color value is rGBhexadecimal value
Copy the code

(2) Get the maximum row and column

Get the maximum column and row
print(sheet.max_row)
print(sheet.max_column)
Copy the code

(3) Get each row and each column

Sheet. rows is a generator that contains each row of data, and each row is wrapped by a tuple.
Sheet. Columns is similar, except that each tuple is a cell of each column.

Return A1, B1, C1
for row in sheet.rows:
    for cell in row:
        print(cell.value)

# A1, A2, A3
for column in sheet.columns:
    for cell in column:
        print(cell.value)
Copy the code

(4) Get letters from numbers, get numbers from letters

from openpyxl.utils import get_column_letter, column_index_from_string

# return letters based on the number of columns
print(get_column_letter(2))  # B
# return the number of the column based on letters
print(column_index_from_string('D'))  # 4
Copy the code

(5) Delete the worksheet

Style #
wb.remove(sheet)
Way # 2
del wb[sheet]
Copy the code

(6) Matrix permutation

rows = [
    ['Number'.'data1'.'data2'],
    [2.40.30],
    [3.40.25],
    [4.50.30],
    [5.30.10],
    [6.25.5],
    [7.50.10]]

list(zip(*rows))

# out
[('Number'.2.3.4.5.6.7),
 ('data1'.40.40.50.30.25.50),
 ('data2'.30.25.30.10.5.10)]

# Notice that the method will discard missing columns (rows)
rows = [
    ['Number'.'data1'.'data2'],
    [2.40].# There is one data missing
    [3.40.25],
    [4.50.30],
    [5.30.10],
    [6.25.5],
    [7.50.10]]# out
[('Number'.2.3.4.5.6.7), ('data1'.40.40.50.30.25.50)]
Copy the code

10. Set the cell style

(1) Classes to be imported

from openpyxl.styles import Font, colors, Alignment
Copy the code

(2) Fonts

The following code specifiesSuch as line no. 24.Bold italic, font colorred. Directly using the cellfontProperty to which the Font object is assigned.

bold_itatic_24_font = Font(name='such as the line', size=24, italic=True, color=colors.RED, bold=True)

sheet['A1'].font = bold_itatic_24_font
Copy the code

(3) Alignment

Also use cell properties directlyaligment, where vertical and horizontal center are specified. You can use it in addition to CenterThe right and leftParameters, etc.

Set data center vertically and horizontally in B1
sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')
Copy the code

(4) Set row height and column width

# Line 2 is high
sheet.row_dimensions[2].height = 40
# C column width
sheet.column_dimensions['C'].width = 30
Copy the code

(5) Merge and split cells

The so-called merge cell refers to the cell in the upper left corner of the merge region as the base and overwrites other cells to make it a large cell.
Instead, split the cell and return the value of the large cell to its original upper-left corner.

# merge cells, write data to the upper left corner
sheet.merge_cells('B1:G1') # merge several cells in a row
sheet.merge_cells('A1:C3') # Merge cells in a rectangular region
Copy the code

After merging, data can only be written to the upper left corner, that is, to the left of the interval: coordinates.
If all the cells to be merged have data, only the data in the upper left corner is retained, and the rest is discarded. In other words, if the data was not written to the upper left corner before the merge, there would be no data in the merged cell.
Here is the code to split the cell. After splitting, the value goes back to A1

sheet.unmerge_cells('A1:C3')
Copy the code

11. Sample code

import datetime
from random import choice
from time import time
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

Set file mingc
addr = "openpyxl.xlsx"
# Open file
wb = load_workbook(addr)
Create a new table
ws = wb.create_sheet()
Enter the first line
ws.append(['TIME'.'TITLE'.'A-Z'])

# Input content (500 lines of data)
for i in range(500):
    TIME = datetime.datetime.now().strftime("%H:%M:%S")
    TITLE = str(time())
    A_Z = get_column_letter(choice(range(1.50)))
    ws.append([TIME, TITLE, A_Z])

Get the maximum row
row_max = ws.max_row
Get the maximum column
con_max = ws.max_column
Print the above to the console
for j in ws.rows:    # we.rows retrieves each row of data
    for n in j:
        print(n.value, end="\t")   # n.value gets the value of the cell
    print(a)# save, save (must write filename (absolute address) default py equivalent directory, only support XLSX format)
wb.save(addr)
Copy the code

5.2 OpenPyXL generates 2D charts

Sample code:

from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference

wb = Workbook(write_only=True)
ws = wb.create_sheet()

rows = [
    ('Number'.'Batch 1'.'Batch 2'),
    (2.10.30),
    (3.40.60),
    (4.50.70),
    (5.20.10),
    (6.10.40),
    (7.50.30),]for row in rows:
    ws.append(row)

chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'

data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")

from copy import deepcopy

chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"
ws.add_chart(chart2, "G10")

chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12
chart3.grouping = "stacked"
chart3.overlap = 100
chart3.title = 'Stacked Chart'
ws.add_chart(chart3, "A27")

chart4 = deepcopy(chart1)
chart4.type = "bar"
chart4.style = 13
chart4.grouping = "percentStacked"
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'
ws.add_chart(chart4, "G27")

wb.save("bar.xlsx")
Copy the code

The effect is as follows:

5.3 OpenPyXL generates 3D charts

Sample code:

from openpyxl import Workbook
from openpyxl.chart import (
    Reference,
    Series,
    BarChart3D,
)

wb = Workbook()
ws = wb.active

rows = [
    (None.2013.2014),
    ("Apples".5.4),
    ("Oranges".6.2),
    ("Pears".8.3)]for row in rows:
    ws.append(row)

data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
titles = Reference(ws, min_col=1, min_row=2, max_row=4)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)

ws.add_chart(chart, "E5")
wb.save("bar3d.xlsx")
Copy the code

The effect is as follows:

5.4 Actual Combat Training

1. Openpyxl new Excel

Example program:

# 3.5.2 OpenPyXL Create Excel
def fun3_5_2() :
    wb = Workbook()

    This function calls the worksheet index (_active_sheet_index), which defaults to 0.
    Unless you change this value, you are always using this function to operate on the first sheet.
    ws = wb.active

    Set the sheet name
    ws.title = "New Title"

    Set the sheet color
    ws.sheet_properties.tabColor = "1072BA"

    # save table
    wb.save('Save a new Excel. XLSX')
Copy the code

Execution effect:

And set the title and background color for sheet:

2. Openpyxl Opens the existing Excel

Example program:

# 3.5.3 OpenPyXL Opens existing Excel
def fun3_5_3() :
    wb = load_workbook("./3_5 OpenPyXL modify operation exercise.xlsx")

    This function calls the worksheet index (_active_sheet_index), which defaults to 0.
    Unless you change this value, you are always using this function to operate on the first sheet.
    ws = wb.active

    # save table
    wb.save('copy.xlsx')
Copy the code

The effect is as follows:

3. Excel openpyxl, speaking, reading and writing

Example program:

# 3.5.4 OpenPyXL reads and writes Excel
def fun3_5_4() :
    wb = load_workbook("./3_5 OpenPyXL modify operation exercise.xlsx")

    This function calls the worksheet index (_active_sheet_index), which defaults to 0.
    Unless you change this value, you are always using this function to operate on the first sheet.
    ws = wb.active

    Read cell information
    cellB2_value = ws['B2'].value
    print("Cell B2 contains:",cellB2_value)

    # Write cell
    ws['A1'].value = "OPENPYXL"

    # save table
    wb.save('copy.xlsx')
Copy the code

Execution Result:

6 Python XlsWriter write operations to Excel

XlsxWriter is a python module for writing Excel2007 and XLSX file formats. It can be used to write text, numbers, formulas, and supports cell formatting, images, charts, document configuration, automatic filtering, and more
Pros: More features, document fidelity, extended format types, faster and configurable Cons: Can’t be used to read and modify Excel files

The official document: xlsxwriter. Readthedocs. IO /

6.1 Basic Operations of XlsWriter

1. Install the XlsWriter module

pip install XlsxWriter
Copy the code

Since I have already installed it, I have already installed it:

2. Create an Excel file

# create file
workbook = xlsxwriter.Workbook("new_excel.xlsx") 
Copy the code

3. Create a sheet

# to create sheet
worksheet = workbook.add_worksheet("first_sheet") 
Copy the code

4. Write data

(1) Write text

Method of # 1:
worksheet.write('A1'.'write something')
Method of # 2:
worksheet.write(1.0.'hello world')
Copy the code

(2) Write numbers

# write number
worksheet.write(0.1.32)
worksheet.write(1.1.32.3)
Copy the code

(3) Write function

worksheet.write(2.1.'=sum(B1:B2)')
Copy the code

(4) Write pictures

# Insert image
worksheet.insert_image(0.5.'test.png')
worksheet.insert_image(0.5.'test.png', {'url': 'http://httpbin.org/'})
Copy the code

(5) Write date

Write date
d = workbook.add_format({'num_format': 'yyyy-mm-dd'})
worksheet.write(0.2, datetime.datetime.strptime('2017-09-13'.'%Y-%m-%d'), d)
Copy the code

(6) Set row and column attributes

Set the line height to 40
worksheet.set_row(0.40)

Set column width from A to B to 20
worksheet.set_column('A:B'.20)
Copy the code

5. Customize the format

Common formats:

Font color: color
Font bold: bold
Font size: font_site
Date format: num_format
Hyperlink: URL
Underline setting: underline
Cell color: bg_color
Frame: border
Alignment: align

# Custom format
f = workbook.add_format({'border': 1.'font_size': 13.'bold': True.'align': 'center'.'bg_color': 'cccccc'})
worksheet.write('A3'."python excel", f)
worksheet.set_row(0.40, f)
worksheet.set_column('A:E'.20, f)
Copy the code

6. Batch write data to cells

Write data to cells in batches
worksheet.write_column('A15'[1.2.3.4.5])  # column, starting at A15
worksheet.write_row('A12'[6.7.8.9])        # line, starting at A12
Copy the code

7. Merge cell write

# merge cell write
worksheet.merge_range(7.5.11.8.'merge_range')
Copy the code

8. Close the file

workbook.close()
Copy the code

6.3 XlsWriter Generates line charts

Sample code:

# -*- coding:utf-8 -*-

import xlsxwriter

Create an Excel
workbook = xlsxwriter.Workbook("chart_line.xlsx")
Create a sheet
worksheet = workbook.add_worksheet()
# worksheet = workbook.add_worksheet("bug_analysis")

# Custom style, bold
bold = workbook.add_format({'bold': 1})

1. Prepare data and write it to excel---------------
Write data to Excel, used when creating ICONS
headings = ['Number'.'testA'.'testB']
data = [
    ['2017-9-1'.'2017-9-2'.'2017-9-3'.'2017-9-4'.'2017-9-5'.'2017-9-6'],
    [10.40.50.20.10.50],
    [30.60.70.50.40.30]]Write the table header
worksheet.write_row('A1', headings, bold)

Write data
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])

2. Generate the chart and insert it into Excel ---------------
# create a line chart
chart_col = workbook.add_chart({'type': 'line'})

Configure the first series of data
chart_col.add_series({
    Sheet1 is the default value because we didn't specify the sheet name when we created it
    If we set the name of the sheet when creating the new sheet, we need to set it to the corresponding value
    'name': '=Sheet1! $B$1'.'categories': '=Sheet1! $A$2:$A$7'.'values':   '=Sheet1! $B$2:$B$7'.'line': {'color': 'red'}})Configure the second series of data
chart_col.add_series({
    'name': '=Sheet1! $C$1'.'categories':  '=Sheet1! $A$2:$A$7'.'values':   '=Sheet1! $C$2:$C$7'.'line': {'color': 'yellow'}})# configure the second series of data (with a different syntax)
# chart_col.add_series({
# 'name': ['Sheet1', 0, 2],
# 'categories': ['Sheet1', 1, 0, 6, 0],
# 'values': ['Sheet1', 1, 2, 6, 2],
# 'line': {'color': 'yellow'},
#})

Set the title and x and y information of the chart
chart_col.set_title({'name': 'The xxx site Bug Analysis'})
chart_col.set_x_axis({'name': 'Test number'})
chart_col.set_y_axis({'name':  'Sample length (mm)'})

# Set the style of the chart
chart_col.set_style(1)

Insert the chart into the worksheet and set the offset
worksheet.insert_chart('A10', chart_col, {'x_offset': 25.'y_offset': 10})

workbook.close()
Copy the code

The effect is as follows:

6.4 XlsWriter Generates bar charts

Sample code:

# -*- coding:utf-8 -*-

import xlsxwriter

Create an Excel
workbook = xlsxwriter.Workbook("chart_column.xlsx")
Create a sheet
worksheet = workbook.add_worksheet()
# worksheet = workbook.add_worksheet("bug_analysis")

# Custom style, bold
bold = workbook.add_format({'bold': 1})

1. Prepare data and write it to excel---------------
Write data to Excel, used when creating ICONS
headings = ['Number'.'testA'.'testB']
data = [
    ['2017-9-1'.'2017-9-2'.'2017-9-3'.'2017-9-4'.'2017-9-5'.'2017-9-6'],
    [10.40.50.20.10.50],
    [30.60.70.50.40.30]]Write the table header
worksheet.write_row('A1', headings, bold)

Write data
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])

2. Generate the chart and insert it into Excel ---------------
Create a column chart
chart_col = workbook.add_chart({'type': 'column'})

Configure the first series of data
chart_col.add_series({
    Sheet1 is the default value because we didn't specify the sheet name when we created it
    If we set the name of the sheet when creating the new sheet, we need to set it to the corresponding value
    'name': '=Sheet1! $B$1'.'categories': '=Sheet1! $A$2:$A$7'.'values':   '=Sheet1! $B$2:$B$7'.'line': {'color': 'red'}})# configure the second series of data (with a different syntax)
chart_col.add_series({
    'name': '=Sheet1! $C$1'.'categories':  '=Sheet1! $A$2:$A$7'.'values':   '=Sheet1! $C$2:$C$7'.'line': {'color': 'yellow'}})# configure the second series of data (with a different syntax)
# chart_col.add_series({
# 'name': ['Sheet1', 0, 2],
# 'categories': ['Sheet1', 1, 0, 6, 0],
# 'values': ['Sheet1', 1, 2, 6, 2],
# 'line': {'color': 'yellow'},
#})

Set the title and x and y information of the chart
chart_col.set_title({'name': 'The xxx site Bug Analysis'})
chart_col.set_x_axis({'name': 'Test number'})
chart_col.set_y_axis({'name':  'Sample length (mm)'})

# Set the style of the chart
chart_col.set_style(1)

Insert chart into worksheet and offset
worksheet.insert_chart('A10', chart_col, {'x_offset': 25.'y_offset': 10})

workbook.close()
Copy the code

The effect is as follows:

6.5 XlsWriter Generates pie charts

Sample code:

# -*- coding:utf-8 -*-

import xlsxwriter

Create an Excel
workbook = xlsxwriter.Workbook("chart_pie.xlsx")
Create a sheet
worksheet = workbook.add_worksheet()

# Custom style, bold
bold = workbook.add_format({'bold': 1})

1. Prepare data and write it to excel---------------
Write data to Excel, used when creating ICONS
data = [
    ['closed'.'active'.'reopen'.'NT'],
    [1012.109.123.131]]Write data
worksheet.write_row('A1', data[0], bold)
worksheet.write_row('A2', data[1])

2. Generate the chart and insert it into Excel ---------------
Create a chart (pie chart)
chart_col = workbook.add_chart({'type': 'pie'})

Configure the first series of data
chart_col.add_series({
    'name': 'Bug Analysis'.'categories': '=Sheet1! $A$1:$D$1'.'values': '=Sheet1! $A$2:$D$2'.'points': [{'fill': {'color': '#00CD00'}},
        {'fill': {'color': 'red'}},
        {'fill': {'color': 'yellow'}},
        {'fill': {'color': 'gray'}},]})Set the title and x and y information of the chart
chart_col.set_title({'name': 'Bug Analysis'})

# Set the style of the chart
chart_col.set_style(10)

Insert chart into worksheet and offset
worksheet.insert_chart('B10', chart_col, {'x_offset': 25.'y_offset': 10})
workbook.close()
Copy the code

The effect is as follows:

6.6 Actual Combat Training

1. Create xlsWriter and write Excel

Example program:

# 3.6.2 Create a new xlsWriter and write Excel
def fun3_6_2() :
    Create Exce and add sheet
    workbook = xlsxwriter.Workbook('demo.xlsx')
    worksheet = workbook.add_worksheet()

    Set the column width
    worksheet.set_column('A:A'.20)

    # set the format
    bold = workbook.add_format({'bold': True})

    # Add text
    worksheet.write('A1'.'Hello')

    Add content to format
    worksheet.write('A2'.'World', bold)

    # Write some numbers
    worksheet.write(2.0.123)
    worksheet.write(3.0.123.456)

    # add image
    worksheet.insert_image('B5'.'demo.png')

    workbook.close()
Copy the code

The effect is as follows:

7 Python Win32com read, write and modify operations Excel

Python can manipulate COM using a third-party library called Win32com, which is powerful enough to manipulate Word, call macros, and so on.

7.1 PIP Install Win32com

pip install pypiwin32
Copy the code

Since I have already installed it, I have already installed it:

7.2 Python uses Win32com to read and write Excel

Example program:

import win32com
from win32com.client import Dispatch, constants
import os

Get the current script path
def getScriptPath() :
    nowpath = os.path.split(os.path.realpath(__file__))[0]
    print(nowpath)
    return nowpath

# 3.7.2 Python reads and writes Excel using Win32com
def fun3_7_2() :
    app = win32com.client.Dispatch('Excel.Application')

    # Background run, no display, no warning
    app.Visible = 0
    app.DisplayAlerts = 0

    # Create a new Excel
    # WorkBook = app.Workbooks.Add()
    # new sheet
    # sheet = WorkBook.Worksheets.Add()

    Open the existing table. Note that absolute path is used here
    WorkBook = app.Workbooks.Open(getScriptPath() + "\\3_7 win32com modify operation exercise.xlsx")
    sheet = WorkBook.Worksheets('Sheet1')

    Select row n, column n from cell
    cell01_value = sheet.Cells(1.2).Value
    print("Cell01 contents are:",cell01_value)

    Write table information
    sheet.Cells(2.1).Value = "win32com"

    # save table
    #WorkBook.Save()

    # Save as implementation copy
    WorkBook.SaveAs(getScriptPath() + "\\new.xlsx")

    # close table
    WorkBook.Close()
    app.Quit()


if __name__ == '__main__':
    fun3_7_2()
Copy the code

The effect is as follows:

Content as follows:

8 Python Pandas Reads and writes operations to Excel

Brief introduction:

Pandas is a numpy-based tool that was created to solve data analysis tasks. Pandas incorporates a large number of libraries and some standard data models to provide the tools needed to efficiently manipulate large data sets. Pandas provides a large number of functions and methods that allow us to work with data quickly and easily. You’ll soon see that it’s one of the things that makes Python a powerful and efficient environment for data analysis.

Official website: pandas.pydata.org/

Chinese website: www.pypandas.cn/

Official documentation: pandas.pydata.org/pandas-docs…

8.1 Installing pandas for the PIP

pip install pandas
Copy the code

8.2 Pandas Reads and writes Excel

The form is as follows:

Example program:

import pandas as pd
from pandas import DataFrame

# 3.8.2 Pandas read and write Excel
def fun3_8_2() :
    data = pd.read_excel('3_8 PANDAS ', sheet_name='Sheet1')
    print(data)

    Add row data in line 5
    data.loc[4] = ['4'.'john'.'pandas']

    # increment column data, given default value None
    data['new_col'] = None

    # Save data
    DataFrame(data).to_excel('new.xlsx', sheet_name='Sheet1', index=False, header=True)


if __name__ == '__main__':
    fun3_8_2()
Copy the code

The effect is as follows:

The generated Excel is as follows:

Pandas provides simple examples of its functionality. There are many other operations that you can learn about in its documentation or quick Start.

Author: super 806 links: the original onion blog.csdn.net/u014779536/…

0 Python Excel library comparison

1 Python XLRD Read operations on Excel

1.1 XLRD Module Introduction

1.2 Installing the XLRD Module

1.3 Introduction

1.4 Actual combat training

2 Python XLWT write operation Excel (XLS format only!)

2.1 PIP XLWT Installation

2.2 Create a new table using XLWT and write

2.3 XLWT Sets the font format

2.4xlwt sets the column width

2.5xlwt sets the row height

2.6xLWt merges columns and rows

2.7xlwt adds borders

2.8xlwt sets the background color for the cell

2.9 XLWT sets cell alignment

3 Python Xlutils Modify Excel

3.1 PIP Xlutils Installed

3.2 Copy Source Files to Xlutils (Used with XLRD)

3.3 Xlutils reads and writes (that is, modifies) Excel table information

4 Python Xlwings read, write, and modify operations Excel

4.1 PIP Installing XLWings

4.2 Basic Operations

4.3 Reference workbooks, worksheets, and cells

4.4 Writing and reading Data

4.5 Common Functions and Methods

4.6 Data Structure

4.7xlwings generates charts

4.8 Actual Combat Training

4.9 For more information, see

5 Python OpenPyXL reads, writes, and modifies operations on Excel

5.1 Basic Operations of OpenPyXL

5.2 OpenPyXL generates 2D charts

5.3 OpenPyXL generates 3D charts

5.4 Actual Combat Training

6 Python XlsWriter write operations to Excel

6.1 Basic Operations of XlsWriter

6.3 XlsWriter Generates line charts

6.4 XlsWriter Generates bar charts

6.5 XlsWriter Generates pie charts

6.6 Actual Combat Training

7 Python Win32com read, write and modify operations Excel

7.1 PIP Install Win32com

7.2 Python uses Win32com to read and write Excel

8 Python Pandas Reads and writes operations to Excel

8.1 Installing pandas for the PIP

8.2 Pandas Reads and writes Excel

Related Posts

The practice of using UNI-App to develop wechat applets + Step pit collection

Coffee again lively, also can not escape from the hands of the tycoon

Tetris automaton

2 Python XLWT write operation Excel (`XLS format only!`)