XLRD XLWT operation

Introduction

XLRD,XLWT and xlutils are high-efficiency tools for working with Excel documents (*.xls) in Python. Wherein, XLRD can only read XLS,XLWT only new XLS (can not be modified), Xlutils can xlrd. Book to Xlwt.workbook, allowing you to modify the data based on the existing XLS and create a new XLS to implement the modification.

(The following properties or methods are not all, need more properties see the documentation;

xlrd BOok (Class) returned by Xlrd.open_work ("Example.xls")

Number of Nsheets:sheets
Sheet_names:sheet Name list
Sheets:sheet List
Sheet_by_index (SHEETX): Extract sheet by serial number
Sheet_by_name (Sheet_name): Extract by name sheet

Sheet (Class) is returned by the book object-related method

Name:sheet name
nrows: Number of rows
ncols: Number of columns
Cell (ROWX,COLX): Row rows colx column cells
Cell_type (ROWX,COLX): Data type
Cell_value (ROWS,COLX): Numeric
Col (Colx): List of all cells in column Colx
Col_slice (Colx,start_rowx=0,end_rowx=none): Section colx column specifies a list of cells
col_types (Colx,start_rowx=0,end_rowx=none): Section colx column specifies a list of cell numeric types
col_values (colx,start_rowx=0,end_rowx=none): column COLX Specifies a list of cell values
Row also has various actions of COL, omitted here

the Cell (Class) is returned by sheet object (s)-related methods

CType: A variable of type int that corresponds to a different numeric type
Value: Cell values

XLWT Workbook (Class) by XLWT. Workbook () returns

Encoding: Encoding Scheme
Add_sheet (sheet_name): Add sheet
Get_sheet (sheet_name): Select sheet
Save (file_name): Save

Worksheet (Class) is returned by the workbook object-related method

Write (Rows,colx,cell_value,style): Edit cell
Row (ROWX). Write (Colx,cell_value,style): Edit line
Flush_row_data (): Reduces memory pressure and can no longer be modified before flush
Col (Colx), write (Rows,cell_value,style): Edit column

easyxf (function) Create Xfstyle instance, format control

expression Syntax: (<element>:(<attribute> <value>,) +;) +
<element>-<attribute>-<value>:
(bold is the default format, not all of the following columns)
font-bold-true or False
-colour-{colour}
-Italic-true or False
-Name-name of the font, Arial
-Underline-true or False

alignment-direction- General, LR, RL
-Horizontal- general, left, center, right, filled
-Vertical- bottom, top, center, justified, distributed
-Shrink_to_fit-true or False

bolders-left-an integer width between 0 and
-right-an integer width between 0 and
-top-an integer width between 0 and
-bottom-an integer width between 0 and
-diag-an integer width between 0 and
-Left_colour-{colour}*, automatic colour
-Right_colour-{colour}*, automatic colour
- ...

pattern-back_color-{colour}*,  automatic colour
- Fore_colour-{Colour}*, automatic colour
-pattern- none , solid, fine_dots, sparse_dots

{colous}*: Black, (Dark_) (Light_) Blue, gold, (Dark_) (light_) green, ivory, Lavender,

(Light_) Orange, Pink, (Dark_) red, rose, violet, White, (Dark_) (light_) Yellow, ...

xlutils

copy: Will xlrd. Book converted to Xlwt.workbook
styles: Read xlrd. The style of each cell in the workbook
display: Simple and safe rendering of data read by XLRD
Filter: Splitting and consolidating multiple XLS files
Margins: View table sparsity level
Save: Serializes the xlrd. Book, dump as binary xls or stream

Tips

1. Xlrd.open_workbook (fomatting_info=): When Formatting_info=ture, read workbook and preserve formatting

2. Xlrd.open_workbook (on_demand=): When the on_demand=true, only when required to load the worksheet into memory, read large files using

3. XLWT. Worksheet.flush_row_data (): Reduce memory consumption, the rows being refreshed can no longer be accessed or modified, it is recommended that every 1000 rows be refreshed once (if many columns when adjusted)

4. XLWT. Workbook (encoding=): Select the encoding of the created Workbook

Demo + View Code

Reference

1. The Xlrd Module

2. The XLWT Module

3. Working with Excel files in Python

Source: http://www.cnblogs.com/fireme/p/3887284.html

From for notes (Wiz)

XLRD XLWT operation