pythonでExcel操作の小技集

基本操作

xlsをxlsxに変換する

いまだにxlsでしか配ってくれないシステムとかあるので、変換をしたいことがあります。pandasとかがxlsxしか対応してないこともあります。

import pyexcel

wb = pyexcel.load_book('xxxxxx.xls')
wb.save_as('xxxxxx.xlsx')

これだけです。たぶん、これだけが正解ではないと思いますが。

openpyxlで一行ずつ読み込む

import openpyxl

wb = openpyxl.load_workbook('xxxxxx.xlsx')
wb = wb.worksheets['Sheet1']
#2行目から読み込む、値だけ取ってくる
for r in ws.iter_rows(min_row=2,values_only=True):
    if r[1] == 'TEST':
        sum += r[2]

pandasでサクッと集計できるとかっこいいのですが、そこまで考えてられない場合にopenpyxlでB列に'TEST'が入っているものだけ集計するわかりやすい方法です。さほど遅くないです。なお、iter_rowsはmin_colや,max_colというオプションもあるので、一部の列だけ読んできて処理を軽くすることができます。

openpyxlでデータを書き込む

import openpyxl

wbin = openpyxl.load_workbook(filename='inputfile.xlsx')
wsin = wbin['Sheet1']
wbout = openpyxl.load_workbook(filename='outputfile.xlsx')
wsout = wbout['Sheet1']

rows = 1
for r in wsin.iter_rows(min_rows=1,min_col=2,max_col=4):
    wsout['A' + str(rows)] = str(r[0]) #inputfileのB列
    wsout['B' + str(rows)] = str(r[1]) #inputfileのC列
    wsout['C' + str(rows)] = str(r[2]) #inputfileのD列
    rows += 1

wb.save(filename='outputfile.xlsx')

excelへの書き込みは結構なケースでexcelの転記があると思うので、サンプルもそれに近しいものにしました。あと、あまりにも大きいファイルを書き込みする場合はif rows % 1000 == 1 :などを入れてあげて1000行に一度セーブするなどの工夫が必要です。

pandasでpivotのようなことをする

import pandas

filename = 'xxxxxx.xlsx'
sheetname='Sheet1'

df = pandas.read_excel(filename,sheet_name=sheetname)
#Day列でgroupbyする
summary = df.groupby(['Day'])
#daystotal列を合計する
total = summary[['daystotal']].sum()
#group by したカラムはNameで取り出し、sumされたほうはrowで取り出す
for Name,row in total.iterrows():
    print(str(Name[0],row['daystotal'])

ちょっと取り扱いが難しいpandasですが、dataframeをexcelから作る場合などはそれほど難しくはないです。サンプルはDay列でgroupbyし、daystotalを合計しています。pivotの感覚ですね。また、skiprows=3なら4行目から読み込み、usecols="C,D,F,H"などということもできます。excelで自分でやるよりは早くできるので、一発作って終わりのexcelでなければ、やる価値はあると思います。

小ネタ

読み込むファイルはローカルにコピーする

時々見るのですが、ネットワーク上のファイルをそのまま操作しないほうがいいです。

import os
import  shutil

folder = '\\\\hogehoge.server.jp\\共有フォルダ'
filename = 'inputfile-yyyymm.xlsx'

fullpath = folder + '\\' + filename
targetfile = filename.replace('-yyyymm','')
if os.path.exist(fullpath):
    shutil.copyfile(fullpath,targetfile)

ローカルにコピーするすることで、ネットワーク上の問題から逃れられることと、入力ファイル名のコントロールができるので、後段の開発がやりやすくなることがあります。

テンプレートになるファイルは最初にコピーする

上記と逆ですが、毎月、同じテンプレートへの入力操作をする場合は、テンプレートファイルをあらかじめ月などの日付情報をいれてコピーします。テンプレートに書いてからコピーをするとテンプレートを失うなどのデメリットがあります。

orgfile = "テンプレート"
    
now = datetime.datetime.now()
resultfile = orgfile + now.strftime('%Y%m') + ".xlsx"
shutil.copyfile(orgfile + ".xlsx",resultfile)

かなりいい加減ですが、こんな感じにしています。