Python handles Excel

In Python, you typically use the XLRD library to read Excel files, use the XLWT library to generate Excel files, and use the xlutils Library to copy and modify Excel files. These three libraries are only supported to Excel2003.

Python-excel Home Address: http://www.python-excel.org/

Xlrd

Address: Https://pypi.python.org/pypi/xlrd

GitHub Address: Https://github.com/python-excel/xlrd

Open the Excel file to get a book () object:

Import Xlrdbook = Xlrd.open_workbook ("Myfile.xls")

Get Sheets Number:

>>> Book.nsheets3

Get sheets list:

>>> book.sheets () [<xlrd.sheet.sheet object at 0x01a93970>, <xlrd.sheet.sheet object at 0x01a93950 <xlrd.sheet.sheet object at 0x01a93e70>]

Get the Sheets Name list:

>>> book.sheet_names () [u ' Sheet1 ', U ' Sheet2 ', U ' Sheet3 ']

Get the sheet in book ():

Sheet = book.sheets () [0]          #sheets返回一个sheet列表sheet = book.sheet_by_index (0)    #通过索引顺序获取sheet = Book.sheet_by_ Name (U ' Sheet1 ') #通过名称获取

Get number of rows, number of columns, name:

>>> sheet.nrows1002>>> sheet.ncols11>>> Sheet.nameu ' Sheet1 '

Get a list of rows, a row of values, a column, a column of values:

Sheet.row (i) sheet.row_values (i) sheet.col (i) sheet.col_values (i)

Gets the value of the cell:

Cell = Sheet.cell (i,j) Cell_value = Sheet.cell_value (i,j) Cell_value = Sheet.cell (i,j). Value

It is important to note that reading Excel with XLRD does not work on it: The Xlrd.open_workbook () method returns XLRD. Book type, which is read-only and cannot be manipulated.

Xlwt

Address: HTTP://PYPI.PYTHON.ORG/PYPI/XLWT, suitable for python2.3-2.7

xlwt-future:https://pypi.python.org/pypi/xlwt-future/0.8.0, suitable for Python 2.6-3.3

GitHub Address: HTTPS://GITHUB.COM/PYTHON-EXCEL/XLWT

Create an Excel file and create a sheet:

From XLWT Import *book = Workbook () sheet = book.add_sheet (' Sheet1 ') book.save (' Myexcel.xls ')

The workbook class can have encoding and style_compression parameters.

Encoding, sets the character encoding, style_compression, to indicate whether or not to compress. With this setting: W = Workbook (encoding= ' utf-8 '), you can output Chinese in Excel. The default is ASCII.

Write content to sheet:

Sheet.write (R, C, label= "", Style=style.default_style)

Simple write:

Sheet.write (0, 0, label = ' Row 0, Column 0 Value ')

To set the format to write:

Font = XLWT. Font () # fonts Font.Name = ' Times New Roman ' font.bold = truefont.underline = Truefont.italic = Truestyle = XLWT. Xfstyle () # Create a format style.font = font # format font sheet.write (1, 0, label = ' formatted value ', style) # Apply the style to the C Ellbook.save (' Myexcel.xls ')

Write Date:

style = XLWT. Xfstyle () style.num_format_str = ' M/d/yy ' # other options:d-mmm-yy, D-mmm, Mmm-yy, h:mm, H:mm:ss, h:mm, H:mm:ss, M/D/YY H: MM, MM:SS, [H]:mm:ss, Mm:ss.0sheet.write (0, 0, Datetime.datetime.now (), style)

Write formula:

Sheet.write (0, 0, 5) # Outputs 5sheet.write (0, 1, 2) # Outputs 2sheet.write (1, 0, XLWT. Formula (' a1*b1 ') # output "Ten" (a1[5] * a2[2]) sheet.write (1, 1, XLWT. Formula (' SUM (A1,B1) ')) # output "7" (A1[5] + a2[2])

Write Link:

Sheet.write (0, 0, XLWT. Formula (' HYPERLINK ' ("http://www.google.com"; Google ") #输出" Google "link to http://www.google.com

Xlutils

Address: http://pythonhosted.org/xlutils/

GitHub Address: Https://github.com/python-excel/xlutils

Xlutils.copy.copy (WB)

Copy a xlrd. Book object, generating a XLWT. The Workbook object, which can be XLWT. Workbook to make changes.

From XLRD import open_workbookfrom xlutils.copy Import copybook = Open_workbook (' Myexcel.xls ') wbook = Copy (book)  # Wbook is the XLWT. Workbook Object wsheet = Wbook.get_sheet (0)  #通过get_sheet () Gets the sheet that has the write () method Wsheet.write (0, 0, ' value ') wb.save (' Myexcel.xls ')

　　

　　

Python handles Excel