ExcelファイルのPythonモジュール

皆さん、こんにちは.
これは私の最初のブログです、私が最初のブログのために始めることができるどんなトピックについて考えたとき、私は私を行かせましたopenpyxl Excel用のPythonモジュール.
始める前に、起動する前に

Prerequisites

• Basic Python knowledge
• Basic Excel knowledge

Excelでサンプルを使用します.

File Name : sampleData.xlsx
Sheet1:
Sheet2:

話題

1. Installing
2. Loading the Workbook
3. Working with Sheets
4. Retrieving Cell Values
5. Retrieving Multiple Values
6. Converting data into Python structures

OpenPixlのインストール

インストールするPython端末で以下のコードを実行しますopenpyxl .
グローバルまたは仮想環境でインストールすることができます

pip install openpyxl

インストール後、Pythonコードにインポートする必要があります

(create new python file .py)

import openpyxl 

ワークブックの読み込み

コードを下に書く

# loading workbook

from openpyxl import load_workbook

# NOTE: loading the excel we need
wb = load_workbook(filename='sampleData.xlsx')

print(wb)  

上記コードでインポートload_workbook Excelファイルを読み込み、変数「wb」に格納する方法

Note the file we are accessing must be in the same folder we are working from.

出力:

シートでの作業

以下のコードがロジックを持っています:

読みたいExcelからシート名を取得する

どのシートが現在アクティブかを示します

インデックスを指定したりシート名を指定したりする

# Working with Sheets

from openpyxl import load_workbook

# NOTE: loading the excel we need
wb = load_workbook(filename='sampleData.xlsx')

# NOTE: get the sheetnames from the excel we read
print(wb.sheetnames)  # OUTPUT: ['Sheet1', 'Sheet2']

# NOTE: shows which sheet is currently active
print(wb.active)  # OUTPUT: <Worksheet "Sheet1">

# NOTE: we can assign which sheet to be activated, it starts from left to right from the index 0,1 so on...
wb.active = 0
print(wb.active)  # OUTPUT: <Worksheet "Sheet1">
wb.active = 1
print(wb.active)  # OUTPUT: <Worksheet "Sheet2">

# NOTE: above we saw we can access via index location, now lets access the sheet with the sheet name
sheet = wb['Sheet2']
print(sheet)  # OUTPUT: <Worksheet "Sheet2">
print(sheet.title)  # OUTPUT: Sheet2

セル値の取得

以下のコードがロジックを持っています:

どのシートとセル番号を指定するか

行インデックス、列インデックス、セル値およびセルcoornidatesを取得する

# Retrieving Cell Values

from openpyxl import load_workbook

# NOTE: loading the excel we need
wb = load_workbook(filename='sampleData.xlsx')

sheet = wb['Sheet1']
# NOTE: from active sheet we are trying to fetch the value from cell B3
cell_coordinates = sheet['B3']

# NOTE: fetch value, row & column for the cell coordinates
print(cell_coordinates.value)  # OUTPUT: Jojo
print(cell_coordinates.row)  # OUTPUT: 3
print(cell_coordinates.column)  # OUTPUT: 2
print(cell_coordinates.coordinate)  # OUTPUT: B3

# NOTE: What if we fetch the empty cell value?
print(sheet['B9'].value)  # OUTPUT: None

# NOTE: Return Value using cell
print(sheet.cell(row=2, column=2).value)  # OUTPUT: Shijo

複数の値の取得

以下のコードがロジックを持っています:

特定のカラムのデータを取得する

インデックスを持たない列オブジェクトのフェッチ範囲

取得行と列オブジェクト

行の取得とカラムの値

# Retrieving Multiple Values

from openpyxl import load_workbook

# NOTE: loading the excel we need
wb = load_workbook(filename='sampleData.xlsx')

sheet = wb['Sheet2']

# NOTE: fetches all the 'A' colum that has data
# OUTPUT: (<Cell 'Sheet2'.A1>, <Cell 'Sheet2'.A2>, <Cell 'Sheet2'.A3>, <Cell 'Sheet2'.A4>, <Cell 'Sheet2'.A5>, <Cell 'Sheet2'.A6>)
print(sheet['A'])

# NOTE: fetches range of columns without index
print(sheet['A:C'])
'''
OUTOUT:
((<Cell 'Sheet2'.A1>, <Cell 'Sheet2'.A2>, <Cell 'Sheet2'.A3>, <Cell 'Sheet2'.A4>, <Cell 'Sheet2'.A5>, <Cell 'Sheet2'.A6>), 
(<Cell 'Sheet2'.B1>, <Cell 'Sheet2'.B2>, <Cell 'Sheet2'.B3>, <Cell 'Sheet2'.B4>, <Cell 'Sheet2'.B5>, <Cell 'Sheet2'.B6>), 
(<Cell 'Sheet2'.C1>, <Cell 'Sheet2'.C2>, <Cell 'Sheet2'.C3>, <Cell 'Sheet2'.C4>, <Cell 'Sheet2'.C5>, <Cell 'Sheet2'.C6>))
'''

# NOTE: fetches range of columns with index
print(sheet['1:3'])
'''
OUTPUT:
((<Cell 'Sheet2'.A1>, <Cell 'Sheet2'.B1>, <Cell 'Sheet2'.C1>), 
(<Cell 'Sheet2'.A2>, <Cell 'Sheet2'.B2>, <Cell 'Sheet2'.C2>), 
(<Cell 'Sheet2'.A3>, <Cell 'Sheet2'.B3>, <Cell 'Sheet2'.C3>))
'''

# fetch row & column objects
for row in sheet.rows:
    print(row)

'''
OUTPUT:
(<Cell 'Sheet2'.A1>, <Cell 'Sheet2'.B1>, <Cell 'Sheet2'.C1>)
(<Cell 'Sheet2'.A2>, <Cell 'Sheet2'.B2>, <Cell 'Sheet2'.C2>)
(<Cell 'Sheet2'.A3>, <Cell 'Sheet2'.B3>, <Cell 'Sheet2'.C3>)
(<Cell 'Sheet2'.A4>, <Cell 'Sheet2'.B4>, <Cell 'Sheet2'.C4>)
(<Cell 'Sheet2'.A5>, <Cell 'Sheet2'.B5>, <Cell 'Sheet2'.C5>)
(<Cell 'Sheet2'.A6>, <Cell 'Sheet2'.B6>, <Cell 'Sheet2'.C6>)
'''

for col in sheet.columns:
    print(col)

'''
OUTPUT:
(<Cell 'Sheet2'.A1>, <Cell 'Sheet2'.A2>, <Cell 'Sheet2'.A3>, <Cell 'Sheet2'.A4>, <Cell 'Sheet2'.A5>, <Cell 'Sheet2'.A6>)
(<Cell 'Sheet2'.B1>, <Cell 'Sheet2'.B2>, <Cell 'Sheet2'.B3>, <Cell 'Sheet2'.B4>, <Cell 'Sheet2'.B5>, <Cell 'Sheet2'.B6>)
(<Cell 'Sheet2'.C1>, <Cell 'Sheet2'.C2>, <Cell 'Sheet2'.C3>, <Cell 'Sheet2'.C4>, <Cell 'Sheet2'.C5>, <Cell 'Sheet2'.C6>)
'''

# Show values only
for row in sheet.iter_rows(values_only=True):
    print(row)

'''
OUTPUT:
('id', 'fruit', 'quantity')
(1, 'Apple', 2)
(2, 'Orange', 5)
(3, 'Pineapple', 3)
(4, 'Banana', 6)
(5, 'Mango', 1)
'''

for col in sheet.iter_cols(values_only=True):
    print(col)

'''
OUTPUT:
('id', 1, 2, 3, 4, 5)
('fruit', 'Apple', 'Orange', 'Pineapple', 'Banana', 'Mango')
('quantity', 2, 5, 3, 6, 1)
'''

データをPython構造に変換する

# Converting data into Python structures
import json
from openpyxl import load_workbook

# NOTE: loading the excel we need
wb = load_workbook(filename='sampleData.xlsx')

sheet = wb['Sheet2']
#  empty dictionary to keep values from excel
books = {}

for row in sheet.iter_rows(min_row=2, min_col=1, values_only=True):
    book_id = row[0]
    book = {
        'Fruit': row[1],
        'Qty': row[2]
    }
    books[book_id] = book

print(json.dumps(books, indent=3))

'''
OUTPUT:
{
   "1": {
      "Fruit": "Apple",
      "Qty": 2
   },
   "2": {
      "Fruit": "Orange",
      "Qty": 5
   },
   "3": {
      "Fruit": "Pineapple",
      "Qty": 3
   },
   "4": {
      "Fruit": "Banana",
      "Qty": 6
   },
   "5": {
      "Fruit": "Mango",
      "Qty": 1
   }
}
'''

あなたがより多くの使用を調査するのが好きであるならば Openpyxl documentation
チェックマイgithubsource code