Use python to read and write Excel-use xlrd

Document directory

• Xlwt

Xlwt

Http://pypi.python.org/pypi/xlrd

Easy to use

Import xlwt

Import xlwt

Create an excel file

File = xlwt. Workbook () # note that the first letter of the Workbook is in upper case and it is speechless.

Create a sheet

Table = file. add_sheet ('sheet name ')

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

Table. write (0, 0, 'test ')

If you perform repeated operations on a cell

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

So solve the problem by adding cell_overwrite_ OK = True when opening

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

Save files

File.save('demo.xls ')

In addition, use style

Style = xlwt. XFStyle () # initialize the style

Font = xlwt. Font () # create a font for the style

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) # use a style

Xlwt allows you to set the format of cells or rows. You can also add links and formulas. You can read the source code. There are examples:

Dates. py: shows how to set different data formats

Hyperlinks. py: shows how to create a hyperlink (hint: you need to use a formula)

Merged. py: shows how to merge grids.

Row_styles.py shows how to apply the Style to the grid of the entire row.

For specific examples, see:

Http://scienceoss.com/write-excel-files-with-python-using-xlwt/

Google Forum:

Http://groups.google.com/group/python-excel/

 

Compile: cdm, cd xlwt-0.7.4, python setup. py install

 

Import xlwt
Wbk = xlwt. Workbook ()
Sheet = wbk. add_sheet ('sheet 1 ')
# Indexing is zero based, row then column
Sheet. write (0, 1, 'test text ')
Sheet. write (1, 1, 'test text ')
Wbk.save('test2.xls ') is saved on the desktop by default.

 

------------------------------------------------

Using python to read and write Excel -- using xlrd

1. Install the xlrd Module

Go to the python official website and download http://pypi.python.org/pypi/xlrd.pdf. The python environment has been installed.

II. Introduction

1. Import Module

Import xlrd

2. Open an Excel file to read data

Data = xlrd.open_workbook('excelFile.xls ')

3. Tips

Get a worksheet

 

Table = data. sheets () [0] # obtain table = data. sheet_by_index (0) in the order of indexes # obtain table = data. sheet_by_index (0) in the order of Indexes

 

Table = data. sheet_by_name (u'sheet1') # obtain it by name Get the value of the whole row and the whole column (array)

Table. row_values (I) table. col_values (I) Obtain the number of rows and columns　　

Nrows = table. nrows ncols = table. ncols

Cyclic row List DataFor I in range (nrows): print table. row_values (I) CellCell_A1 = table. cell (0, 0). value cell_C4 = table. cell (2, 3). value Use row and column IndexesCell_A1 = table. row (0) [0]. value cell_A2 = table. col (1) [0]. value Simple WritingRow = 0 col = 0 # Type 0 empty, 1 string, 2 number, 3 date, 4 boolean, 5 errorctype = 1 value = 'cell value' xf = 0 # extended formatted table. put_cell (row, col, ctype, value, xf) table. cell (0, 0) # cell value 'table. cell (0, 0 ). value # cell value'

 

Import xlrd
Data export xlrd.open_workbook('test2.xls ')
Table = data. sheets () [0]
Print table. nrows

Print table. name

For row_index in range (table. nrows ):
For col_index in range (table. ncols ):
Print table. cell (row_index, col_index). value traverses the values of all cells.

Iii. Demo code

The Demo code is actually very simple, that is, reading Excel Data.

1 #-*-coding: UTF-8 -*-
2 import xdrlib, sys
3 import xlrd
4 def open_excel (file = 'file.xls '):
5 try:
6 data = xlrd. open_workbook (file)
7 return data
8 bytes t Exception, e:
9 print str (e)
10 # obtain the data parameters in the Excel table based on the index: file: Excel file path colnameindex: the row where the header column name is located, so by_index: The table Index
11 def excel_table_byindex (file = 'file.xls ', colnameindex = 0, by_index = 0 ):
12 data = open_excel (file)
13 table = data. sheets () [by_index]
14 nrows = table. nrows # number of rows
15 ncols = table. ncols # Number of Columns
16 colnames = table. row_values (colnameindex) # a row of data
17 list = []
18 for rownum in range (1, nrows ):
19
20 row = table. row_values (rownum)
21 if row:
22 app = {}
23 for I in range (len (colnames )):
24 app [colnames [I] = row [I]
25 list. append (app)
26 return list
27
28 # obtain the data parameters in the Excel table by name: file: Excel file path colnameindex: the row where the header column is located, so by_name: Sheet1 name
29 def excel_table_byname (file = 'file.xls ', colnameindex = 0, by_name = u 'sheet1 '):
30 data = open_excel (file)
31 table = data. sheet_by_name (by_name)
32 nrows = table. nrows # number of rows
33 colnames = table. row_values (colnameindex) # a row of data
34 list = []
35 for rownum in range (1, nrows ):
36 row = table. row_values (rownum)
37 if row:
38 app = {}
39 for I in range (len (colnames )):
40 app [colnames [I] = row [I]
41 list. append (app)
42 return list
43
44 def main ():
45 tables = excel_table_byindex ()
46 for row in tables:
47 print row
48
49 tables = excel_table_byname ()
50 for row in tables:
51 print row
52
53 if _ name __= = "_ main __":
54 main ()

 

 

1. Install the xlrd Module

Go to the python official website and download http://pypi.python.org/pypi/xlrd.pdf. The python environment has been installed.

II. Introduction

1. Import Module

Import xlrd

2. Open an Excel file to read data

Data = xlrd.open_workbook('excelFile.xls ')

3. Tips

Get a worksheet

 

Table = data. sheets () [0] # obtain table = data. sheet_by_index (0) in the order of indexes # obtain table = data. sheet_by_index (0) in the order of Indexes

 

Table = data. sheet_by_name (u'sheet1') # obtain it by name Get the value of the whole row and the whole column (array)

Table. row_values (I) table. col_values (I) Obtain the number of rows and columns　　

Nrows = table. nrows ncols = table. ncols

Cyclic row List DataFor I in range (nrows): print table. row_values (I) CellCell_A1 = table. cell (0, 0). value cell_C4 = table. cell (2, 3). value Use row and column IndexesCell_A1 = table. row (0) [0]. value cell_A2 = table. col (1) [0]. value Simple WritingRow = 0 col = 0 # Type 0 empty, 1 string, 2 number, 3 date, 4 boolean, 5 errorctype = 1 value = 'cell value' xf = 0 # extended formatted table. put_cell (row, col, ctype, value, xf) table. cell (0, 0) # cell value 'table. cell (0, 0 ). value # cell value'

 

Import xlrd
Data export xlrd.open_workbook('test2.xls ')
Table = data. sheets () [0]
Print table. nrows

Print table. name

For row_index in range (table. nrows ):
For col_index in range (table. ncols ):
Print table. cell (row_index, col_index). value traverses the values of all cells.

Iii. Demo code

The Demo code is actually very simple, that is, reading Excel Data.

1 #-*-coding: UTF-8 -*-
2 import xdrlib, sys
3 import xlrd
4 def open_excel (file = 'file.xls '):
5 try:
6 data = xlrd. open_workbook (file)
7 return data
8 bytes t Exception, e:
9 print str (e)
10 # obtain the data parameters in the Excel table based on the index: file: Excel file path colnameindex: the row where the header column name is located, so by_index: The table Index
11 def excel_table_byindex (file = 'file.xls ', colnameindex = 0, by_index = 0 ):
12 data = open_excel (file)
13 table = data. sheets () [by_index]
14 nrows = table. nrows # number of rows
15 ncols = table. ncols # Number of Columns
16 colnames = table. row_values (colnameindex) # a row of data
17 list = []
18 for rownum in range (1, nrows ):
19
20 row = table. row_values (rownum)
21 if row:
22 app = {}
23 for I in range (len (colnames )):
24 app [colnames [I] = row [I]
25 list. append (app)
26 return list
27
28 # obtain the data parameters in the Excel table by name: file: Excel file path colnameindex: the row where the header column is located, so by_name: Sheet1 name
29 def excel_table_byname (file = 'file.xls ', colnameindex = 0, by_name = u 'sheet1 '):
30 data = open_excel (file)
31 table = data. sheet_by_name (by_name)
32 nrows = table. nrows # number of rows
33 colnames = table. row_values (colnameindex) # a row of data
34 list = []
35 for rownum in range (1, nrows ):
36 row = table. row_values (rownum)
37 if row:
38 app = {}
39 for I in range (len (colnames )):
40 app [colnames [I] = row [I]
41 list. append (app)
42 return list
43
44 def main ():
45 tables = excel_table_byindex ()
46 for row in tables:
47 print row
48
49 tables = excel_table_byname ()
50 for row in tables:
51 print row
52
53 if _ name __= = "_ main __":
54 main ()