Python
Mac
OSX
VBA

MacのExcelにはVBAよりPythonだとおもふのよ

Summary

VBAはツライというお話

VBAのかわりにPythonを使ってみる

osx version

tmp$ sw_vers 
ProductName:    Mac OS X
ProductVersion: 10.13.3
BuildVersion:   17D102

Excel version

Version 16.10(180210)

python3 version

tmp$ python3 --version
Python 3.6.4

install

install python's library for Excel

$ pip3 install xlwings

For autocompete with vim

// for autocomplete
$ pip3 install jedi

$ vim dein.toml

[[plugins]]
repo = 'davidhalter/jedi-vim'
on_ft = 'python'
hook_add = '''
    let g:deoplete#sources#jedi#python_path = '/usr/local/bin/python3.6'
'''

Hello world

import xlwings as xw
wb = xw.Book('/Users/callmekohei/Desktop/Foo/foo.xlsx')
sht = wb.sheets['Sheet1']
sht.range('A1').value = 'hello world'

image.png

たとえばバーのリストを作ってみる

callmekoheiはお酒が好きなので

新規開拓用にバーのリストを作ってみる


(データーの準備)

例えば平成28年度に福岡市が営業許可した飲食店のデーターを元に作ってみる

福岡市 新規飲食店営業等営業許可施設一覧(平成28年度)

でこのままエクセルで読み込んで加工してもいいのですが

それだと練習にならないので、それはなしでwww

(手順その0)
適当な月の操作ボタンを押してCSVデーターをダウンロードする

image.png

(手順その1)
エクセルで一旦ファイルを読み込んで、どういうデーターが必要か考える

今回は

屋号(お店の名前)
住所

を業態(バー)

で絞り込んだデーターが欲しいです

(手順その2)
文字コードを確認、UTF-8に変換する

Downloads$ nkf -g insyokuteneigyoukyoka201609.csv 
CP932
Downloads$ nkf -w --overwrite insyokuteneigyoukyoka201609.csv 
Downloads$ nkf -g insyokuteneigyoukyoka201609.csv 
UTF-8

(手順その3)
ヘッダーの確認

Downloads$ cat insyokuteneigyoukyoka201609.csv | head -1 | tr ',' '\n'
営業者氏名
営業者法人代表者役肩書
営業者法人代表者氏名
郵便番号
営業所所在地
営業所所在地ビル名
営業所電話番号
屋号
業種
業態
許可終了日
許可・交付日
初年度許可・交付日

(手順その4)
CSVデーター業態がバーでフィルタリングする(行末にバーという文字の分だけ抽出)

Downloads$ cut -d ',' -f 5,8,10 insyokuteneigyoukyoka201609.csv | grep -e 'バー$'
福岡市中央区警固一丁目1番24号,BAR 柳瀬,バー
福岡市博多区博多駅前三丁目26番5号,ライブ喫茶バー ゲリラハウス,バー
福岡市中央区渡辺通五丁目5番3号 2F,フゼラフ 春吉店,バー
福岡市中央区大名一丁目8番5号,エヌ,バー
福岡市博多区住吉四丁目13番14号,Carats,バー
福岡市中央区舞鶴一丁目8番30号,bad moon,バー
福岡市中央区白金一丁目3番5号,Bar KUMAGAI,バー
福岡市早良区飯倉六丁目35番25−1号,Ladies Bar 8,バー
福岡市博多区銀天町一丁目6番8号,未来,バー
福岡市中央区赤坂三丁目12番16号,BAR RIVER,バー
福岡市中央区西中洲3番20号,50フィフティ,バー
福岡市中央区薬院三丁目6番4号,洋酒家 和,バー
福岡市中央区西中洲2番14号,冗談倶楽部,バー
福岡市早良区荒江二丁目12番21−C−2号,BAR荒江BASE,バー
福岡市博多区中洲四丁目3番1号,Mr,G,バー
福岡市博多区中洲四丁目1番15号,Bar Aquavitae,バー
福岡市博多区冷泉町6番5−1号,香,バー
福岡市中央区大名一丁目2番28号,OZ ROO,バー
福岡市博多区中洲二丁目3番5号,globe,バー
福岡市博多区中洲二丁目5番5号,プチ スリーエー,バー
福岡市城南区友丘二丁目2番41号,スナック Gee,バー

うんうん。いい感じ。このファイルをfoo.csvとして保存する

Downloads$ cut -d ',' -f 5,8,10 insyokuteneigyoukyoka201609.csv | grep -e 'バー$' > foo.csv

ヘッダーをつけて保存

Downloads$ echo -e "住所,屋号,業態\n$(cat foo.csv)" > foo.csv

(手順その5)
先程加工したCSVデーターをPythonで読み込みこんでExcelに貼り付ける

import csv
import xlwings as xw

wb = xw.Book('/Users/callmekohei/Desktop/Foo/foo.xlsx')
sht = wb.sheets['Sheet1']

csv_file = open("./foo.csv", "r", encoding="utf-8", errors="", newline="" )
f = csv.DictReader(csv_file, delimiter=",", doublequote=True, lineterminator="\r\n", quotechar='"', skipinitialspace=True)

i = 1
for row in f:
    sht.range('A' + str(i) ).value = row["屋号"]
    sht.range('B' + str(i) ).value = row["住所"]
    i = i + 1

できたー

image.png

Todo

あとは、住所を住所1(都道府県)、住所2(市区町村)、住所3(その他)pythonで分けたい。

参考

Python(Xlwings)を使ってExcelを操作してみる

pythonでのcsvファイルの読み込み