随時更新予定

【2019/10/28更新】excelのcell番地を相互に変換する自作関数を追加：自作関数


よく使うライブラリ
Pythonを活かすための部品,知識まとめ
使用例

【よく使うライブラリ】

import os
import unicodedata
import datetime
import glob
import re
import pandas as pd
import openpyxl as px
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Color, PatternFill
from openpyxl.utils import get_column_letter

Pythonを活かすための部品,知識まとめ

osモジュール編

os.chdirでカレントディレクトリを変更
os.listdir()で引数にとったパスのフォルダ、ファイルを列挙

Pandas編

pd.read_excel()でExcelファイルを開き、DataFrameに格納
DataFrame.columns.valuesで列名をPandas.Seriesで取得、tolist()でリスト化
locで任意の位置の値を取得、正規表現を併用することでマッチした値を取得する
concat()でDataFrameにDataFrame,Seriesを結合
axis引数でタテヨコを指定
DataFrame.to_excel()で.xlsxに書きこむ

Openpyxl編

openpyxl.load_workbook()で.xlsxブックを読み込む
worksheet.conditional_formatting.add()で条件付き書式の設定をする
各パラメータはExcelブックでの条件付き書式設定と同じ設定方法
workbook.save()でExcelブックに書き込み
ws.cell(row=row_num,column=col_num,value=value)で(row_num,col_num)セルにvalueを書き込む

その他

コマンドプロンプトにファイルをドラッグ&ドロップするとフルパスが指定できる
windowsのpathに含まれる"\"によってpathの文字がエスケープされてしまうため、pathはrow文字列で指定
path=r"parent\children\current\file"
r""の末尾に\があるとエラー
→末尾だけpathlist[n]+="\\"で\を結合
str+strで文字列結合
str.replace(str,str)で文字列置換
input()の引数はprint()と同じく標準出力される

自作関数

【Excelの列名はアルファベットだけどpythonでcolumnを指定するときは数字なの不便だなあ】

アルファベット→数字の変換

def col2int(s):
    if len(s)<2:
        return ord(s)-64
    else:
        return (ord(s[0])%65+1)*26+ord(s[1])%65+1

数字→アルファベットの変換

import string

def int2col(n):
    if n<27:
        return string.ascii_uppercase[n-1]
    else:
        return string.ascii_uppercase[(n-1)//26-1]+string.ascii_uppercase[(n-1)%26]

型判別相互変換

def intcolconvert(obj):
    if type(obj)==str:
        if len(obj)<2:
            return ord(obj)-64
        else:
            return (ord(obj[0])%65+1)*26+ord(obj[1])%65+1
    elif type(obj)==int:
        if obj<27:
            return string.ascii_uppercase[obj-1]
        else:
            return string.ascii_uppercase[(obj-1)//26-1]+string.ascii_uppercase[(obj-1)%26]

使用例　

何層にも枝分かれしたフォルダ構造の中から「このファイル探して」と言われたとき

・いちいちエクスプローラーでカチカチクリックするの嫌だな...
・詳細なパスが知りたい
→ファイルの存在確認だけではダメだからフルパスを出力させよう
・どこにあるか分からないけどたぶんこのあたりにあるよ
→pathをリストに入れて全走査しよう

pathlist=[
    r"path1",
    r"path2",
    r"path3",
    r"path4",
    r"path5"
]

・ファイルが.xlsなのか.xlsxなのかも分からない...
→re(正規表現)で探そう,pathに両方文字列結合しよう

xlsx=".xlsx"
xls=".xls"

・どの深さのフォルダにあるか分からない...
→中間フォルダを考慮してpathにワイルドカードを入れて、いちばん浅いところからいちばん深いところまで再帰的に走査しよう

tempfilename=primary[i]+"**\\"+filename+xlsx
glob.glob(tempfilename, recursive=True)

実際の.py

何百もあるExcelファイルの「このシートのここに書いてあるファイルの数、種類が正しいか確かめて」といわれたとき

・Excelファイルの中で欲しい情報が書かれている場所がだいたい同じ
→セル範囲を決め、左上のセル番地から右下のセル番地までで欲しい情報と正規表現でマッチしたものを取得

例:[C10]~[G30]までの範囲の場合

for col_num in range(3,8):
    for row_num in range(10,31):
        if "検索語句" in str(worksheet.cell(row=row_num,column=col_num).value):
            list.append(worksheet.cell(row=row_num,column=col_num).value)

・正しくなかった場合、それがわかりやすいようにしたい
→一致していなかった(False)場合、赤く塗りつぶして強調表示
→→条件付き書式で設定

ws.conditional_formatting.add(range,
                              CellIsRule(operator='equal',formula=['FALSE'], 
                                         fill=PatternFill(start_color='FF0000', end_color='FF0000',fill_type='solid')))

【想定環境】
Windows 10
Python 3

退屈なことをPythonにやらせたいときに来るところ

目次