Xiaobai Xuexue python (24): basic operation of Excel (2)

Time:2020-10-15

Xiaobai Xuexue python (24): basic operation of Excel (2)

Life is short, I choose python

Previous portal

Xiaobai studies python (1): Opening

Xiaobai Xue python (2): basic data types (1)

Xiaobai Xue python (3): basic data types (2)

Xiaobai Xuexue python (4): basic operations of variables

Xiaobai Xuexue python (5): basic operators (1)

Xiaobai Xuexue python (6): basic operators (2)

Xiaobai Xue python (7): basic process control (1)

Xiaobai Xue python (8): basic process control (2)

Xiaobai Xue python (9): basic data structure (list) (Part 1)

Xiaobai Xue python (10): basic data structure (list) (Part 2)

Xiaobai Xue python (11): basic data structure (tuple)

Xiaobai Xue python (12): basic data structure (Dictionary) (1)

Xiaobai Xue python (13): basic data structure (Dictionary) (2)

Xiaobai Xue python (14): basic data structure (set) (Part 1)

Xiaobai Xue python (15): basic data structure (set) (Part 2)

Xiaobai Xue python (16): basic data types (functions) (Part 1)

Xiaobai Xue python (17): basic data types (functions) (Part 2)

Xiaobai Xue python (18): basic file operation

Xiaobai Xue python (18): basic file operation

Xiaobai Xue python (19): basic exception handling

Xiaobai’s python (20): basics of iterators

Xiaobai Xuexue python (21): Generator Foundation

Python (22): simple use of time and calendar modules

Xiaobai Xuexue python (23): basic operation of Excel (1)

Here, first of all, I would like to congratulate all of you who have seen this series. This series is the last one of “Xiaobai’s basic series of learning Python”. Congratulations on taking a solid step on the way of learning python.

Xiaobai Xuexue python (24): basic operation of Excel (2)

Write to excel

The first is, of course, the installation of third-party modules

pip install openpyxl

First, we need to create a Workbook:

import xlsxwriter

workbook = xlsxwriter.Workbook('demo.xlsx')

Before all operations, please remember to import the one we just installedxlsxwriterModule.

Next, we create a sheet:

sheet1 = workbook.add_worksheet('test_sheet')

After the creation, you need to close the workbook. In this step, we will save the workbook we just created.

workbook.close()

OK, we have created an excel, the operation is over, class is over.

Teacher, you come back, this is over?

Xiaobai Xuexue python (24): basic operation of Excel (2)

We then introduce how to write data into excel.

First of all, we can set some cell formats

workfomat = workbook.add_format()
#Bold font
workfomat.set_bold(True)
#Cell border width
workfomat.set_border(1)
#Alignment
workfomat.set_align('left')
#Format the data to two decimal places
workfomat.set_num_format('0.00')

Then we write the content. The editor is too lazy to think about the specific content and directly copies the content of the previous article

Heads = [','Chinese', 'Mathematics',' English ']
datas = [
    ['xiaoming ', 76, 85, 95],
    ['xiaohong ', 85, 58, 92],
    ['xiao Wang ', 98, 96, 91]
]

sheet1.write_row('A1', heads, workfomat)

sheet1.write_row('A2', datas[0], workfomat)
sheet1.write_row('A3', datas[1], workfomat)
sheet1.write_row('A4', datas[2], workfomat)

Then execute the program, and let’s take a look at the final output:

Xiaobai Xuexue python (24): basic operation of Excel (2)

In addition to output like this, we can also specify the cell format of the output:

Let’s list a more complex output date type:

fomat1 = workbook.add_format({'num_format': 'yy/mm/dd/ hh:mm:ss'})

sheet1.write_datetime('E5', datetime.datetime(2019, 11, 9, 22, 44, 26), fomat1)

be careful:The above format must be added, otherwise it will only display a timestamp in Excel.

Other output types are not illustrated here. Here are some common ones:

#String type
sheet1.write_string()
#Digital
sheet1.wirte_number()
#Null type
sheet1.write_blank()
#Formula
sheet1.write_formula()
#Boolean type
sheet1.write_boolean()
#Hyperlinks
sheet1.write_url()

We can also insert pictures into excel. Examples are as follows:

sheet1.insert_image('I6', 'wx.jpg')

The grammar is as follows:

insert_image(row, col, image[, options])

Row: row coordinate, the starting index value is 0;
Col: column coordinate, the starting index value is 0;
Image: string type, is the image path;
Options: dict type, optional parameter, used to specify image location, such as URL and other information;

We can also draw in Excel, including area, bar chart, bar chart, line chart, scatter chart, etc.

Chart objects are created throughWorkbook add_chart()Method, which specifies the chart type:

chart = workbook.add_chart({'type': 'column'})

Common chart styles are as follows:

Area: chart of area style
Bar: bar chart
Column: bar chart
Line: line style chart
Pie: pie chart
Scatter: scatter plot
Stock: stock style chart
Radar: radar style chart

Then use theinsert_chart()The worksheet method inserts it into the worksheet as an embedded chart:

sheet1.insert_chart('A7', chart)

Complete examples are as follows:

chart = workbook.add_chart({'type': 'column'})

chart.add_series({'values': '=test_sheet!$B$2:$B$4'})
chart.add_series({'values': '=test_sheet!$C$2:$C$4'})
chart.add_series({'values': '=test_sheet!$D$2:$D$4'})

sheet1.insert_chart('A7', chart)

The results were as follows:

Xiaobai Xuexue python (24): basic operation of Excel (2)

Some common simple operations are introduced here. If you want to know more about it, you can visit the official documents https://xlsxwriter.readthedoc… 。

Sample code

All the code snippets in this series will be placed on GitHub and gitee, the code management repository, for easy access.

Sample code GitHub

Sample code gitee

Xiaobai Xuexue python (24): basic operation of Excel (2)

If my article is helpful, please scan the code to pay attention to the official account of the author: get the latest dry cargo push:

Recommended Today

Regular expression sharing for checking primes

This regular expression is shown as follows: Regular expressions for checking prime numbers or not To use this positive regular expression, you need to convert the natural number into multiple 1 strings. For example, 2 should be written as “11”, 3 should be written as “111”, 17 should be written as “11111111111”. This kind of […]