Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to use xlrd and xlwt to operate excel tables in python. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Python operation excel mainly uses two libraries, xlrd and xlwt, that is, xlrd is the library for reading excel,xlwt and writing excel. You can download https://pypi.python.org/pypi from here. The following records python read and write excel.

Python read excel--xlrd

There are several troublesome problems in this process, such as reading dates and reading the contents of merged cells. Let's take a look at the basic operations:

First, read an excel file with two sheet. The second sheet,sheet2 used in the test is as follows:

The basic operations of python for excel are as follows:

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

Import xlrd

Import xlwt

From datetime import date,datetime

Def read_excel ():

# Open the file

Workbook = xlrd.open_workbook (ringing F:\ demo.xlsx')

# get all sheet

Print workbook.sheet_names () # [upright sheet1percent, upright sheet2']

Sheet2_name = workbook.sheet_names () [1]

# get sheet content based on sheet index or name

Sheet2 = workbook.sheet_by_index (1) # sheet index starts at 0

Sheet2 = workbook.sheet_by_name ('sheet2')

# name of sheet, number of rows, number of columns

Print sheet2.name,sheet2.nrows,sheet2.ncols

# get the values of the entire row and column (array)

Rows = sheet2.row_values (3) # get the fourth line

Cols = sheet2.col_values (2) # get the third column

Print rows

Print cols

# get cell content

Print sheet2.cell (1). Value.encode ('utf-8')

Print sheet2.cell_value (1). Encode ('utf-8')

Print sheet2.row (1) [0] .value.encode ('utf-8')

# get the data type of the cell content

Print sheet2.cell (1). Ctype

If _ _ name__ = ='_ _ main__':

Read_excel ()

So the problem is that the field in the red box in the above run result is clearly the date of birth, and the real floating point number can be displayed. All right, let's solve the first problem:

1. The way python reads the contents of cells in excel as dates

There are five types returned when python reads the contents of cells in excel, namely ctype in the above example:

Ctype: 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error

That is, the ctype=3 of date, at this time, you need to use the xldate_as_tuple of xlrd to process it into date format. Xldate can only start the operation when you first judge the ctype=3 of the table. Now take a look at the command line:

> sheet2.cell (2jue 2). Ctype # 1990-2-22

> sheet2.cell (2). Ctype # 24

> sheet2.cell (2. 0). Ctype # Xiaofang

> sheet2.cell (2p4). Ctype # null value (here is the reason for merging cells)

> sheet2.cell (2jue 2). Value # 1990-2-22

33656.0

> xlrd.xldate_as_tuple (sheet2.cell_value (2d2), workbook.datemode)

(1992, 2, 22, 0, 0, 0)

> date_value = xlrd.xldate_as_tuple (sheet2.cell_value (2mem2), workbook.datemode)

> date_value

(1992, 2, 22, 0, 0, 0)

> date (* date_value [: 3])

Datetime.date (1992, 2, 22)

> date (* date_value [: 3]) .strftime ('% Ymax% mmax% d')

'1992Accord 02Universe 22'

That is, you can do some simple processing to determine whether ctype is equal to 3, and if it is equal to 3, it can be processed in a time format:

If (sheet.cell (row,col). Ctype = = 3):

Date_value = xlrd.xldate_as_tuple (sheet.cell_value (rows,3), book.datemode)

Date_tmp = date (* date_value [: 3]). Strftime ('% Y _% _ m _% d')

2. Read the contents of the merged cells

This is really no skill, can only get the merge cell of the first cell row index, can read the value, read wrong is the null value.

That is, the first index of the row read by the parallel cell.

3. Get the merged cells

When reading the file, the formatting_info parameter needs to be set to True, and the default is False, so the merged cell array obtained above is empty.

> workbook = xlrd.open_workbook (ringing F:\ demo.xlsx',formatting_info=True)

> sheet2 = workbook.sheet_by_name ('sheet2')

> sheet2.merged_cells

[(7,8,2,5), (1,3,4,5), (3,6,4,5)]

The meaning of the four parameters returned by merged_cells is: (row,row_range,col,col_range), where [row,row_range) includes row and does not include row_range,col, that is, (1, 3, 4, 5) means merging lines 1 to 2 (excluding 3), and (7, 8, 2, 5) means merging columns 2 to 4.

With this, you can get the contents of the three merged cells:

> print sheet2.cell_value (1,3,4,5) # (1,3,4,5)

Good friend

> print sheet2.cell_value (3, 4) # (3, 6, 4, 5)

Classmate

> print sheet2.cell_value (7, 2) # (7, 8, 2, 5)

None for the time being

Did you find the pattern? Yes, get the low-order indexes of row and col returned by merge_cells! So we can do this once and for all:

> merge = []

> > for (rlow,rhigh,clow,chigh) in sheet2.merged_cells:

Merge.append ([rlow,clow])

> merge

[[7, 2], [1, 4], [3, 4]]

> for index in merge:

Print sheet2.cell_value (index [0], index [1])

None for the time being

Good friend

Classmate

> > >

Python writes excel--xlwt

The difficulty in writing an excel may not be to construct a workbook itself, but to populate the data, but this is out of scope. There are also thorny problems in writing excel, such as writing merged cells is troublesome, and there are different styles of writing. These can only be studied thoroughly by looking at the source code.

I "conceived" something like the following sheet1, which is to be implemented in xlwt:

Basically, it looks complicated, and it looks "very formal", which is completely made up by individuals.

The code is as follows:

''

Set the cell style

''

Def set_style (name,height,bold=False):

Style = xlwt.XFStyle () # initialization style

Font = xlwt.Font () # create fonts for styles

Font.name = name # 'Times New Roman'

Font.bold = bold

Font.color_index = 4

Font.height = height

# borders= xlwt.Borders ()

# borders.left= 6

# borders.right= 6

# borders.top= 6

# borders.bottom= 6

Style.font = font

# style.borders = borders

Return style

# write excel

Def write_excel ():

F = xlwt.Workbook () # create a workbook

''

Create the first sheet:

Sheet1

''

Sheet1 = f.add_sheet (ubiquitous sheet1 recording, writing, etc.) # create a sheet

Row0 = [u 'business', u 'status', u 'Beijing', u 'Shanghai', u 'Guangzhou', u 'Shenzhen', u 'status subtotal', u 'total']

Column0 = [u 'air tickets', u 'boat tickets', u 'train tickets', u 'bus tickets', u 'other']

Status = [u 'booking', u 'issuing ticket', u 'refund', u 'business subtotal']

# generate the first line

For i in range (0jinlen (row0)):

Sheet1.write (0meme I Times New Roman',220,True 0 [I], set_style ('Times New Roman',220,True))

# generate the first and last columns (merge 4 rows)

I, j = 1,0

While I < 4*len (column0) and j < len (column0):

Sheet1.write_merge (iMagne3 Arial',220,True 0pr 0pl column 0 [j], set_style ('Arial',220,True)) # first column

Sheet1.write_merge (iMagazine 3, 7) # the last column, "totals"

I + = 4

J + = 1

Sheet1.write_merge (21 Times New Roman',220,True, 21 Times New Roman',220,True)

# generate the second column

I = 0

While I < 4*len (column0):

For j in range (0jinlen (status)):

Sheet1.write (jaccounifu1pr 1pr status [j])

I + = 4

F.save ('demo1.xlsx') # Save the file

If _ _ name__ = ='_ _ main__':

# generate_workbook ()

# read_excel ()

Write_excel ()

What needs a little explanation is the write_merge method:

Write_merge (x, x + m, y, w + n, string, sytle)

X represents rows, y represents columns, m represents the number of rows across, n represents the number of columns across, string represents the contents of the cell to be written, and style represents the cell style. Among them, x _ ray y _ r w _ H all start with 0.

This is not quite the same as reading merge cells in xlrd.

As mentioned above: sheet1.write_merge (21, 21, 21, 0, 0, 10, 10, 5, 5, 5, 5, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 3, 4, 3, 4, 3, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 9, 9, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,

That is, column 1 and column 2 are merged in 22 rows, the contents of the merged cells are "total", and style is set.

If you need to create more than one sheet, just f.add_sheet.

For example, if you create a sheet2 before the sentence f.save ('demo1.xlsx') in the above write_excel function, the effect is as follows:

The code is also really easy:

''

Create a second sheet:

Sheet2

''

Sheet2 = f.add_sheet (ubiquitous sheet2 recording, writing, etc.) # create a sheet2

Row0 = [u 'name', u 'age', u 'date of birth', u 'hobby', u 'relationship']

Column0 = [u 'Xiao Jie', u 'Xiao Pang', u 'Xiao Ming', u 'big god', u 'big fairy', u 'Xiaomin', u 'nameless']

# generate the first line

For i in range (0jinlen (row0)):

Sheet2.write (0meme I Times New Roman',220,True 0 [I], set_style ('Times New Roman',220,True))

# generate the first column

For i in range (0jinlen (column0)):

Sheet2.write [I], set_style ('Times New Roman',220))

Sheet2.write (1, 2, 1, 2, 1, 1, 2, 1, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1,

Sheet2.write_merge (7, 7, 2, 4, u 'not available) # merge column cells

Sheet2.write_merge (1, 2, 4, 4, 4, u, good friend) # merge row cells

F.save ('demo1.xlsx') # Save the file

On how to use xlrd in python, xlwt operation excel table to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.