Python Learning Note 16-spreadsheets

The Openpyl module is a third-party library that resolves reads and writes of files in Microsoft Excel 2007/2010, such as versions of Excel Xlsx/xlsm/xltx/xltm.

Installation

Pip Install OPENPYXL

The establishment of workbook and sheet

Introducing Modules

 from Import Workbook

Create a working book

>>> WB = Workbook ()

Establish sheet

>>> ws = Wb.active

In addition, you can append the sheet behind this

>>> ws1 = Wb.create_sheet ()

Renaming sheet

" Python "

At this point, you can get sheet from the Workbook object using the following method

>>> ws01 = wb['Python'#sheet and workbooks, relationships  like key-value pairs are Ws01true

Show All Sheet

Print wb.get_sheet_names () [u'Python'Sheet1'  'Sheet2']

You can also use a looping statement to print out all the sheet names.

 for inch wb:   ... Print Sh.title ... Pythonsheet1sheet2

Cell

In order to be able to clearly understand the process of filling the data, the name of the contract in the electronic table is described as Follows:

For sheet, the cell in it is its subordinate unit. so, to get a cell, you can:

>>> b4 = ws['B4']

Such

>>> A5 = Ws.cell ("A5")

and There's This.

>>> A2 = Ws.cell (row = 2, column = 1)

If you want to add data to B4, you can do this:

>>> ws['B4'] = 4444

Because B4 references a cell object, you can use the properties of this object to see its value:

>>> b4.value4444

Create multiple cells at once

>>> cells = ws["A1":"C3"]>>> Tuple (ws.iter_rows ("a1:c3"))    # view ((<cell python.a1>, <cell python.b1>, <cell python.c1>), (<cell python.a2>, <cell python.b2>, <Cell Python.C2 >), (<cell python.a3>, <cell python.b3>, <cell python.c3>))

You can also use the following loop method to read each cell object one at a time:

indentationerror:expected an indented block  for  in Ws.iter_rows ("a1:c3   "): ...  for inch row:       ... Print  <cell Python.a1><cell Python.b1><cell Python.c1><cell Python.a2><cell Python.b2><cell Python.c2><cell Python.a3><cell Python.b3><cell Python.C3>

Assign values to these cells

 for inch ws.rows:       ...  for inch row:          ... = I          ... + = 1

View the added data

 for inch ws.columns:    ...  for inch col:      ... Print  147101325811143691215

Save

>>> Wb.save ("demo.xlsx")

View Workbook

Read existing spreadsheets

>>> fromOpenpyxlImportLoad_workbook>>> wb2 = Load_workbook ("demo.xlsx")>>>Printwb2.get_sheet_names () [u'Python', u'Sheet1', u'Sheet2']>>> ws_wb2 = wb2["Python"]>>> forRowinchws_wb2.rows: ... forCellinchRow: ...PrintCell.value ...123456789101112131415

Other Third-party libraries

Third-party Libraries for spreadsheets, In addition to the above openpyxl, there are other, listed a few, for reference, the use of the same way.
Xlsxwriter: for Excel 2010 format, Such as. xlsx, official website: https://xlsxwriter.readthedocs.org/, This official document is written in Illustrations. Very good to Read.
The following two spreadsheet tables are used to process The. xls format.
Xlrd: Network File: https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html?p=4966
Xlwt: Network File: http://xlwt.readthedocs.org/en/latest/

Python Learning Note 16-spreadsheets