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

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

Read Excel

I don’t know if you have seen any advertisements about Python operating excel. Xiaobian has seen it many times, and only needs ￥ 9.9 to learn. Today, Xiaobian will bring you a free introduction to Python operating excel.

This article is worth ￥ 9.9~~~

I’m a little flushed with this bullshit.

Since we want to operate excel, we need to create an excel first.

First, create three sheets in Excel. Don’t tell me that you don’t know what a sheet is. Maybe you are not suitable for reading this article.

Test using Excel has been uploaded to the code warehouse, students can access it if necessary.

The contents of Excel are as follows:

Please ignore the date and time in the lower right corner for the moment. This date and time has no practical effect and is only used for demonstration.

This is the data of one sheet. There are three same sheets.

Maybe you can guess what Xiaobian is going to do after you see this excel. Yes, yes, you guessed right. Xiaobian is to calculate the average score.

So it’s not good to use Excel’s formula to calculate the average score. It’s a waste of money and money to calculate it in Python.

The functions provided by Excel are really powerful. This has to be obeyed. However, what if I want to operate across sheets? The operation of this article is to calculate the average score across sheets.

First, we need to import third-party modulesxlrdBecause it is a third-party module, students who have not installed it need to install it first. On the command line, enter:

pip install xlrd

Just wait for the progress bar to finish.

To get the Workbook:

First of all, we need to open the excel we just created in the code:

workbook = xlrd.open_workbook('test.xlsx')

For convenience, I willtest.xlsxIn the same directory as the code, you need to pay attention to the file path problem in actual operation. No matter whether the relative path or absolute path is used, you should write it correctly.

Sheet related operations:

There are many operations related to sheet. We will list several commonly used ones:

#Output the names of all sheets
print(workbook.sheet_names())
#Get all sheets
print(workbook.sheets())
#Get sheet according to index
print(workbook.sheet_by_index(1))
#Get sheet by name
print( workbook.sheet_ By_ Name ('class 1 '))

I won’t demonstrate the output result here. You can know by yourself:)

Common operation:

Get the number of rows and columns:

sheet1 = workbook.sheets()[0]
#Get the number of rows
print(sheet1.nrows)
#Get the number of columns
print(sheet1.ncols)

The results were as follows:

6
4

Get the data of whole row and column (data type is list)

#Get line 2
print(sheet1.row_values(1))
#Get column 3
print(sheet1.col_values(2))

The results were as follows:

['xiaoming ', 76.0, 85.0, 95.0,' "]
['Mathematics', 85.0, 58.0, 96.0,' ", '"]

Get cell data:

cell1 = sheet1.cell(1, 1).value
#Row index
cell2 = sheet1.row(1)[1].value
cell3 = sheet1.cell(1, 2).value
#Column index
cell4 = sheet1.col(2)[1].value

The results were as follows:

76.0 76.0 85.0 85.0

Get date type data:

date_value = xlrd.xldate_as_datetime(sheet1.cell_value(5, 3), workbook.datemode)
print(type(date_value), date_value)

The results were as follows:

<class 'datetime.datetime'> 2019-11-07 20:49:05

This is a direct method to convert the data intodatetimeType,xlrdIt also provides the ability to convert data into tuples, and then convert tuples to dates.

date_tulp = xlrd.xldate_as_tuple(sheet1.cell_value(5, 3), workbook.datemode)
print(type(date_tulp), date_tulp)
year, month, day, hour, minute, second = date_tulp
print(datetime.datetime(year, month, day, hour, minute, second))

The results were as follows:

<class 'tuple'> (2019, 11, 7, 20, 49, 5)
2019-11-07 20:49:05

Today’s play is to find the average, but I don’t want to post the code in the article, so please start by yourself~~~

Of course, if you really can’t access the code warehouse, the specific implementation code will still be submitted to the code warehouse, but the editor still hopes that you can complete the homework independently~~

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

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: