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

I. Overview

The three toolkits for Python to manipulate excel are as follows. Note that you can only operate .xls, not .xlsx.

Xlrd: read related operations on excel

Xlwt: write related operations to excel

Xlutils: integration of excel read and write operations

All three toolkits can be downloaded directly using pip:

Sudo pip install xlrdsudo

Pip install xlwtsudo

Pip install xlutils

Second, use the xlrd module to read the contents of the existing excel file

Xlrd is a module used to read content from an excel file

The code is as follows:

Import

Import xlrd

Open excel

Data = xlrd.open_workbook ('demo.xls') # Note that the first letter of workbook here is lowercase

View the name of the file that contains sheet

Data.sheet_names ()

Get the first worksheet, or by indexing order or worksheet name

Table = data.sheets () [0]

Table = data.sheet_by_index (0)

Table = data.sheet_by_name (upright Sheet1')

Get the number of rows and columns

Nrows = table.nrows

Ncols = table.ncols

Get the values of the entire row and column (array)

Table.row_values (I)

Table.col_values (I)

Loop row to get a list of indexes

For rownum in range (table.nrows):

Print table.row_values (rownum)

Cell

Cell_A1 = table.cell (0J0). Value

Cell_C4 = table.cell (2 and 3). Value

Use row and column indexes respectively

Cell_A1 = table.row (0) [0] .value

Cell_A2 = table.col (1) [0] .value

Simple write

Row = 0

Col = 0

Ctype = 1 # Type 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error

Value = 'lixiaoluo'

Xf = 0 # format of the extension (default is 0)

Table.put_cell (row, col, ctype, value, xf)

Table.cell (0line 0) # text: upright xxxxx'

Table.cell (0Pol 0). Value # 'yyyy'

Third, use the xlwd module to write and save the content to the new excel file

Xlwt can only create a completely new excel file, then write to it and save it. But in most cases what we want is to read in an excel file and then modify or append it, which is when we need xlutils.

The code is as follows:

Import xlwt

Import xlwt

Create a new excel file

File = xlwt.Workbook () # notice that the initials of Workbook here are capitalized, speechless

Create a new sheet

Table = file.add_sheet ('sheet name')

Write data table.write (row, column, value)

Table.write (0pd0pm)

If you repeat the operation on a cell, a

Returns error:# Exception: Attempt to overwrite cell:# sheetname=u'sheet 1' rowx=0 colx=0

So add cell_overwrite_ok=True solution when you open it.

Table = file.add_sheet ('sheet name',cell_overwrite_ok=True)

Save Fil

File.save ('demo.xls')

In addition, use style

Style = xlwt.XFStyle () # initialization style

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

Font.name = 'Times New Roman'

Font.bold = True

Style.font = font # set the font for the style

Table.write (0,0, 'some bold Times text', style) # uses styles

4. Append the written content to the existing excel file using the xlutils module (the new content is appended to the existing content)

The code is as follows:

#-*-coding: cp936-*-

Import xlwt

Import xlrd

# import xlutils

From xlutils.copy import copy

Import xlrd

# use the method provided by xlrd to read an existing excel file, and use "formatting_info=True" to keep the source file format unchanged

Rexcel = xlrd.open_workbook ("file.xls", formatting_info=True)

# get the existing number of rows using the method provided by wlrd

Rows = rexcel.sheets () [0] .nrows

# cols = rexcel.sheets () [0] .ncols

# use the copy method provided by xlutils to convert xlrd objects into xlwt objects

Excel = copy (rexcel)

# get the table name (sheet) in the excel to be operated by using the method of the xlwt object, with 0 for the first table and 1 for the second table.

Table = excel.get_sheet (0)

Values = ['1, 2, 2, 3, 3]

Row = rows

Print row

For value in values:

# write method of xlwt object. The parameters are row, column, and value, respectively.

Table.write (row,0,value)

Table.write (row,1, "")

Table.write (row,2, "lala")

Row+=1

# the saving method of the xlwt object, which overwrites the original excel

Excel.save ("file.xls")

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.