I have to tell the story between Python and Excel. I have modules. Do you have data?

In python, the following three modules are used to read, write and append data to excel tables:

1. wlrd reading data in excel

2. xlwt creates a new excel file, and then writes and saves the contents of this file.

3. xlutils reads an excel file, and then modifies or appends it. xlsx cannot be operated, only xls can be operated.

1, Reading excel

xlrd module is needed to read excel

1. Import module

import xlrd

2. Open excel file

table = data.sheets()[0] #Get by index order
table = data.sheet_by_index(0) #Get by index order
table = data.sheet_by_name(u'Sheet1')#Get by name

The code is as follows:

import xlrd
data = xlrd.open_workbook(r"C:\Users\907968\Desktop\test.xlsx")
table1 = data.sheets()[0]
table2 = data.sheet_by_index(0)
table3=data.sheet_by_name(u'Sheet1')
print(table1)
print(table2)
print(table3)

return:

<xlrd.sheet.Sheet object at 0x0000000002F7F208>
<xlrd.sheet.Sheet object at 0x0000000002F7F208>
<xlrd.sheet.Sheet object at 0x0000000002F7F208>

3. Get the number of rows and columns

import xlrd
data = xlrd.open_workbook(r"C:\Users\907968\Desktop\test.xlsx")
table = data.sheets()[0]
nrows = table.nrows
ncols = table.ncols
print("Number of rows:%d\n Number of columns:%d"%(nrows,ncols))

return:

Number of rows: 13

Number of columns: 3

4. Get the values of the whole row and column and return them in list form

rows = table.row_values(0)
cols = table.col_values(0)
print("rows:%s\ncols:%s"%(rows,cols))

return:

rows:['A1', 'B1', 'C1']
cols:['A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9', 'A10', 'A11', 'A12', 'A13']

5. Get cell data

cell_A1 = table.cell_value(0,0)
cell_C4 = table.cell_value(3,2)
print("A1:%s\nC4:%s"%(cell_A1,cell_C4))

return:

A1:A1
C4:C4

You can also use row and column indexes to get cell data

cell_A1 = table.row(0)[0].value
cell_C4 = table.col(2)[3].value
print("A1:%s\nC4:%s"%(cell_A1,cell_C4))

return:

A1:A1
C4:C4

3, Write excel operation

1. Import:

import xlwt

2. Create workbook

workbook = xlwt.Workbook(encoding='utf-8', style_compression=0)

Encoding: set the character code, which is generally set as follows: w = Workbook(encoding = 'utf-8'), then you can output Chinese in excel. The default is ascii

style_compression: indicates whether to compress. It is not commonly used.

3. Create a sheet object that corresponds to a table in an Excel file

sheet = workbook.add_sheet('test', cell_overwrite_ok=True)

test is the name of the table, cell_overwrite_ok indicates whether cells can be overwritten. In fact, it is a parameter instantiated by Worksheet. The default value is False

4. Adding data to a table

sheet.write(0, 0, 'EnglishName') # where '0-row, 0-column' specifies the cell in the table, and 'EnglishName' is the content written to the cell

sheet.write(1, 0, 'Marcovaldo')
txt1 = 'Chinese name'
sheet.write(0, 1, txt1) 
txt2 = 'Marcovado'
sheet.write(1, 1, txt2)

5. Preserve

workbook.save(r'e:\test1.xls')

4, Additional data

import xlrd
import xlutils.copy
data = xlrd.open_workbook(r'C:\Users\907968\Desktop\test222.xls')
ws = xlutils.copy.copy(data)
table=ws.get_sheet(0)
table.write(0,3,'D1')
ws.save(r'C:\Users\907968\Desktop\test222.xls')

Before adding:

A1 B1 C1

After addition:

A1 B1 C1 D1

About Python technology reserve

It's good to learn Python well, whether in employment or sideline, but to learn python, you still need to have a learning plan. Finally, let's share a full set of Python learning materials to help those who want to learn Python!

1, Python learning routes in all directions

All directions of Python is to sort out the commonly used technical points of Python and form a summary of knowledge points in various fields. Its purpose is that you can find corresponding learning resources according to the above knowledge points to ensure that you learn more comprehensively.

2, Learning software

If a worker wants to do well, he must sharpen his tools first. The commonly used development software for learning Python is here, which saves you a lot of time.

3, Getting started video

When we watch videos, we can't just move our eyes and brain without hands. The more scientific learning method is to use them after understanding. At this time, the hand training project is very suitable.

4, Actual combat cases

Optical theory is useless. We should learn to knock together and practice, so as to apply what we have learned to practice. At this time, we can make some practical cases to learn.

5, Interview materials

We must learn Python in order to find a high paying job. The following interview questions are the latest interview materials from front-line Internet manufacturers such as Alibaba, Tencent and byte, and Alibaba boss has given authoritative answers. After brushing this set of interview materials, I believe everyone can find a satisfactory job.