Python操作Excel文件（1）

import openpyxl, os
os.chdir('E:\\python123')

wb = openpyxl.load_workbook("example.xlsx")
print(type(wb))

<class 'openpyxl.workbook.workbook.Workbook'>

wb.sheetnames   # The workbook's sheets' names.

['Sheet1', 'Sheet2', 'Sheet3']

sheet = wb['Sheet3']     # Get a sheet from the workbook.
print(sheet.title)       # Get the sheet's title as a string.

Sheet3

anotherSheet = wb.active # Get the active sheet.

sheet = wb['Sheet1']     # Get a sheet from the workbook.
sheet['A1']              # Get a cell from the sheet.
sheet['A1'].value        # Get the value from the cell.

datetime.datetime(2015, 4, 5, 13, 34, 2)

b = sheet['B1']          # Get another cell from the sheet.
b.value

'Apples'

# Get the row, column, and value from the cell.
'Row %s, Column %s is %s' % (b.row, b.column, b.value)

'Row 1, Column 2 is Apples'

'Cell %s is %s' % (b.coordinate, b.value)

'Cell B1 is Apples'

sheet.cell(row=1, column=2)
sheet.cell(row=1, column=2).value

'Apples'

sheet.max_row      # Get the highest row number.

7

sheet.max_column   # Get the highest column number.

3

import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string

get_column_letter(1)     # Translate column 1 to a letter.

'A'

column_index_from_string('A') # Get A's number.

1

tuple(sheet['A1':'C3']) # Get all cells from A1 to C3.

((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>),
 (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>),
 (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>))

list(sheet.columns)[1] # Get second column's cells.

(<Cell 'Sheet1'.B1>,
 <Cell 'Sheet1'.B2>,
 <Cell 'Sheet1'.B3>,
 <Cell 'Sheet1'.B4>,
 <Cell 'Sheet1'.B5>,
 <Cell 'Sheet1'.B6>,
 <Cell 'Sheet1'.B7>)